Question

Model Multi - Fact

  • 31 March 2021
  • 4 replies
  • 39 views

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.


4 replies

Userlevel 1

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

Userlevel 1

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.

 

Reply