topic Re: Common dimension with multiple facts? in QlikView

Common dimension with multiple facts?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Currently we are using link table model and which has performance issue (Data is growing at a faster ). is it possible to convert below model ( link table) to star schema.?

all below 3 facts are using common dimension. and our current link table has performance issue planned to optimist it

issue is here: Sales and Po is daily data (of current week)

and soh is the next week data that we need to change to current week in the model and concatenate it

DailySalesFact:

LOAD Barcode as SalesBarcode,//Key

ShopNo as SalesShopNo,//Key

Date as SalesDate,//Key

ShopNoDate,//Key

Barcode & '|' & ShopNo & '|' & Date(Floor(Date)) & '|' & 'Sales' as item_shop_date_Type, //composite key

SoldQty,

TotalLandedCost,

TotalGrossPrice,

TotalNetPrice,

'Sales' as SalesType //Sales fact idendify eky

FROM $(vQVDPath)\Sales.QVD (qvd);

PurchaseOrder:

Load

Barcode as POBarcode,//Key

ShopNo as POShopNo, //Key

Date(Create_date) AS PODate, //Key

PO_Type as Type_ID, //fact dimension -required

Delivery_status, //fact dimension -required

OrderQty, //Po Measure

'PO' as POType,//PO fact idendify eky

Barcode & '|' & ShopNo & '|' & Date(Floor(Create_date)) & '|' & 'PO' as item_shop_date_Type //composite key

FROM $(vQVDPath)\PurchaseOrder.QVD (qvd);

Stock:

LOAD soh_shop, //key

soh_item,//key

soh_date,//key

'SOH' as sohType,//soh fact idendify eky

soh_item & '|' & soh_shop & '|' & week(soh_date) & '|' & year(soh_date) & '|' & 'SOH' as item_shop_week_year_Type, //composite key

soh

FROM $(vQVDPath)\MaterialStockOnHand.QVD (qvd);

Re: Common dimension with multiple facts?

marcus_sommer — Tue, 25 Apr 2017 11:06:07 GMT

I think you could just concatenate your fact-tables by renaming the item/shop/date key-fields and adjusting the date by 'SOH' with date(floor(weekstart(soh_date))).

- Marcus

Re: Common dimension with multiple facts?

devarasu07 — Tue, 25 Apr 2017 12:34:48 GMT

Hi Marcus,

how to map with date with master calendar?

sales table (date renamed as SalesDate)

soh table (date renamed as SOHDate)

PO table (date renamed as PODate)

Re: Common dimension with multiple facts?

marcus_sommer — Tue, 25 Apr 2017 12:43:26 GMT

It's just the opposite:

SalesDate as Date

SOHDate as Date

PODate as Date

This meant you will have only one Date-field within the whole datamodel (the same with Item and Shop).

- Marcus

Re: Common dimension with multiple facts?

devarasu07 — Tue, 25 Apr 2017 22:48:41 GMT

Hi Marcus,

It works, Tks