Case 2 Join physical layers on shared dim

Queries.sql

-- Adding MMG in canvas
SELECT d_item.item_main_category AS item_main_category
  FROM star_analytical.d_item d_item
 GROUP BY 1

-- Adding gov,nov, goi in the canvas
-- 5758883
SELECT SUM(1)                    AS cnt:f_gov (sandbox_ap.f_gov)_e5fc330e677b4a4dba98eb5975edbddd:o    ,
       d_item.item_main_category AS item_main_category,
       SUM(f_gov.value)          AS sum:value (f_gov):ok
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_shop d_shop2 ON (f_gov.shop_skey = d_shop2.shop_skey)
       INNER JOIN star_analytical.
      d_calendar d_calendar2 ON (f_gov.date_skey = d_calendar2.date_skey)
       LEFT JOIN star_analytical.d_item d_item ON (f_gov.item_skey = d_item.item_skey)
 GROUP BY 2

--5758890
SELECT SUM(1)                    AS cnt:f_nov (sandbox_ap.f_nov)_d55f6e516db04c42a1dfff7f3b779fba:o  ,
       d_item.item_main_category AS item_main_category,
       SUM(f_nov.value)                  AS sum:value:ok
  FROM sandbox_ap.f_nov f_nov
       INNER JOIN star_analytical.d_shop d_shop ON (f_nov.shop_skey = d_shop.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar ON (f_nov.date_skey = d_calendar.date_skey)
       LEFT JOIN star_analytical.d_item d_item ON (f_nov.item_skey = d_item.item_skey)
 GROUP BY 2

--lets add date from gov logical data model 
SELECT SUM(1)                    AS cnt:f_gov (sandbox_ap.f_gov)_e5fc330e677b4a4dba98eb5975edbddd:o  ,
       d_item.item_main_category AS item_main_category,
       SUM(f_gov.value)         AS sum:value (f_gov):ok  ,
       d_calendar2.year          AS year (d_calendar2)
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_shop d_shop2 ON (f_gov.shop_skey = d_shop 2 . shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar2 ON (f_gov.date_skey = d_calendar2.date_skey)
       LEFT JOIN star_analytical.d_item d_item ON (f_gov.item_skey
      = d_item.item_skey)
 GROUP BY 2, 4

SELECT SUM(1)                AS cnt:f_nov (sandbox_ap.f_nov)_d55f6e516db04c42a1dfff7f3b779fba:o  ,
       t1.item_main_category AS item_main_category,
       t1.month(d_calendar2) AS month (d_calendar2)  ,
       SUM(f_nov.value)      AS sum:value:ok
  FROM sandbox_ap.f_nov f_nov
       INNER JOIN star_analytical.d_shop d_shop ON (f_nov.shop_skey = d_shop.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar ON (f_nov.date_skey = d_calendar.date_skey)
       INNER JOIN (SELECT f_nov.item_skey         AS item_skey,
                          MIN(d_item.
                              item_main_category) AS item_main_category,
                          t0.month(d_calendar2)   AS month (d_calendar2)
                     FROM sandbox_ap.f_nov f_nov
                          INNER JOIN star_analytical.d_shop d_shop ON (f_n ov . shop_skey  =  d_shop . shop_skey)
                          INNER JOIN star_analytical.d_calendar d_calendar ON (f_nov.date_skey = d_calendar.date_skey)
                          LEFT JOIN star_analytical.d_item d_ite
                         M
                       ON ( f_nov.item_skey = d_item.item_skey )
                           LEFT JOIN ( SELECT f_gov.item_skey AS item_skey ,
                           d_calendar2.month AS MONTH (d_calendar2)
                           FROM sandbox_ap.f_gov
                           f_gov
                           INNER JOIN star_analytical.d_shop d_shop2
                           ON ( f_gov.shop_skey = d_shop2.shop_skey )
                           INNER JOIN star_analytical.d_calendar d_calendar2 ON ( f_gov.date_skey
                           = d_calendar2.date_skey ) ) t0 ON ( d_item.item_skey = t0.item_skey )
                    GROUP BY 1, 3) t1
       ON (NVL(f_nov.item_skey, 0) = NVL(t1.item_skey, 0)) AND (NVL2(f_nov.item_skey, 0, 1) =
                                                                NVL2(t1.item_skey, 0, 1))
 GROUP BY 2, 3


-- lot of other queries fired as well