Case 1 : Two Physical Layer and one logical layer

Drill across

------------------- Analysing One Fact table on months and mmg -----------------------

-- Adding MMG on the worksheet
SELECT d_item1.item_main_category AS item_main_category, d_calendar1.year AS year
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_item d_item1 ON (f_gov.item_skey = d_item1.item_skey)
       INNER JOIN star_analytical.d_shop d_shop1 ON (f_gov.shop_skey = d_shop1.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar1 ON (f_gov.date_skey = d_calendar1.date_skey)
 GROUP BY 1, 2

-- Expanding hierarchy from year to months
SELECT d_item1.item_main_category AS item_main_category (d_item1)  ,
       d_calendar1.month          AS month,
       d_calendar1.quarter        AS quarter,
       d_calendar1.year           AS year
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_item d_item1 ON (f_gov.item_skey = d_item1.item_skey)
       INNER JOIN star_analytical.d_shop d_shop1
       ON (f_gov.shop_skey = d_shop1.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar1 ON (f_gov.date_skey = d_calendar1.date_skey)
 GROUP BY 1, 2, 3, 4

-- Adding gov and count of orders in the worksheet
SELECT SUM(1) AS cnt:f_gov (sandbox_ap.f_gov)_624d9dccf91c45b08e75a2b585575c1e:o  ,
       d_item1.item_main_category AS item_main_category (d_item1)  ,
       d_calendar1  .  quarter   AS   quarter (d_calendar1)  ,
       SUM(f_gov.value) AS sum:value (f_gov):ok  ,
       d_calendar1.year AS year (d_calendar1),
       d_calendar1.month   AS   month (d_calendar1)  ,
  FROM sandbox_ap.f_gov f_gov
       INNER JOIN star_analytical.d_item d_item1
       ON (f_gov.item_skey = d_item1.item_skey)
       INNER JOIN star_analytical.d_shop d_shop1 ON (f_gov.shop_skey = d_shop1.shop_skey)
       INNER JOIN star_analytical.d_calendar d_calendar1 ON (f_gov.date_skey = d_calendar1.date_skey)
 GROUP BY 2, 3, 5, 6
 
 ----------------- joining other fact table in the view ----------------
 SELECT SUM(1) AS cnt:f_nov (sandbox_ap.f_nov)_31a5482dc03244d38b5c362b8acc14c8:o    , 
       t1.item_main_category(d_item1) AS item_main_category (d_item1)    , 
       t1.quarter(d_calendar1) AS quarter (d_calendar1)    ,
       SUM(f_nov.value) AS sum:value:ok    , 
       t1.year(d_calendar1) AS year (d_calendar1)
  FROM sandbox_ap.f_nov f_nov
       INNER JOIN star_analytical.d_calendar d_calendar ON (f_nov.date_skey = d_calendar.date_skey)
       INNER JOIN star_analytical.d_item d_item ON (f_nov.item_skey = d_item.item_skey)
       INNER JOIN star_analytical.d_shop d_shop ON (f_nov.shop_skey = d_shop.shop_skey)
       INNER JOIN (SELECT f_nov.date_skey                AS date_skey,
                          f_nov.shop_skey                AS shop_skey
        ,
                          f_nov.item_skey                AS item_skey,
                          t0.item_main_category(d_item1) AS item_main_category (d_item1)    ,
                          MIN(t0.quarter(d_calendar1))   AS quarter (d_calendar1)    ,
                          MIN(t0.year(d_calendar1))      AS year (d_calendar1)
                     FROM sandbox_ap.f_nov f_nov
                          INNER JOIN star_analytical.d_calendar d_calendar ON (f_nov.date_skey = d_calendar.date_skey)
                          INNER JOIN star_analytical.d_item d_item ON (f_nov.item_skey = d_item.item_skey)
                          INNER JOIN star_analytical.d_shop d_shop ON (f_nov.shop_skey = d_shop.shop_skey)
                          LEFT JOIN (SELECT f_gov.date_skey            AS date_skey,
                                            f_gov.shop_skey            AS shop_skey,
                                            f_gov.item_skey            AS item_skey,
                                            d_item1.item_main_category AS item_m ain_category (d_item1)    ,
                                            d_calendar1.quarter        AS quarter (d_calendar1)    ,
                                            d_calendar1.year           AS year (d_calendar1)
                                       FROM sandbox_ap.f_gov f_gov
                                            INNER JOIN star_analytical.d_item d_item1
                                         ON ( f_gov.item_skey = d_item1.item_skey ) INNER JOIN star_analytical.d_shop d_shop1 ON ( f_gov.shop_skey = d_shop1.shop_skey ) INNER JOIN
                                             star_analytical.d_calendar d_calendar1 ON ( f_gov.date_skey = d_calendar1.date_skey )) t0
                          ON ((f_nov.date_skey = t0.date_skey) AND (f_nov.shop_skey = t0.shop_skey)
                              AND (f_nov.item_skey = t0.item_skey))
                    GROUP BY 1, 4, 3, 2) t1
       ON ((f_nov.date_skey = t1.date_skey) AND (f_nov.shop_skey = t1.shop_skey) AND (f_nov.item_ skey = t1.item_skey))
 GROUP BY 2, 3, 5