Hello all, I'm a newbie. I need to realize a model that link together two or more fact tables.
For example Movements and Orders.
Both the facts have in common the SKU Dimension, and each have one or more specific dimensions:
MOVEMENTS_TBL |
---|
MOV_ID |
MOV_DATE |
MOV_REASON_COD |
SKU_COD |
MOV_QTY |
ORDERS_TBL |
---|
ORD_ID |
ORD_DATE |
ORD_REASON_COD |
SKU_COD |
ORD_QTY |
SKU_TBL |
---|
SKU_COD |
SKU_DESCR |
BRAND_COD |
BRAND_DES |
What are the best way to proceed?
Thanks a lot.
At which granularity do you want to create that join? And how are movements and orders related to each other?
As you can see, both tables have in common only the column SKU_COD.
The goal I want to reach is a dashboard where I can mix Mov Qty and Ord Qty together, maybe at different level of details.
For example if in the visualization there will be Brand Cod, Mov Qty and Ord Qty, I’d love to see the following querys:
INSERT INTO TEMP_TBL1
select brand_cod, sum(mov_qty) as MOV_QTY
from MOVEMNTS_TBL mov
left join sku_tbl sku
on mov.sku_cod=sku_tbl.sku_cod
group by brand cod;
INSERT INTO TEMP_TBL2
select brand_cod, sum(ord_qty) as ORD_QTY
from ORDERS_TBL ord
left join sku_tbl sku
on ord.sku_cod=sku_tbl.sku_cod
group by brand_cod
;
select brand_cod, MOV_QTY, ORD_QTY
from TBL1
FULL OUTER JOIN TBL2
ON TBL1.BRAND_COD=TBL2.BRAND_COD
Second Example: sku_cod, Mov Qty and Ord Qty
INSERT INTO TEMP_TBL1
select sku_cod, sum(mov_qty) as MOV_QTY
from MOVEMNTS_TBL mov
group by sku_cod;
INSERT INTO TEMP_TBL2
select sku_cod, sum(ord_qty) as ORD_QTY
from ORDERS_TBL ord
group by sku_cod
;
select sku_cod, MOV_QTY, ORD_QTY
from TBL1
FULL OUTER JOIN TBL2
ON TBL1.sku_cod=TBL2.sku_cod
How can I reach that goal?
Thanks.
Marco
Have you tried the SQL runner in Looker?
You could execute that query there and save the result as a derived table, which you can add as a view in your Looker project.
yes, I tried it. I thought that Looker was able to automatically generate the above queries, once the sku Dimension was joined to both facts Tables.
hey @Marco , I'm curious, is derived table worked for you?
Hi Marco,
I am also curious to know if the derived table approach worked for you.
I am facing the same challenge which I overcame by creating a big denormalized fact table