Model Multi - Fact

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.

0 6 1,060
6 REPLIES 6

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.

Zid
Explorer

​ 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

Top Labels in this Space
Top Solution Authors