Common dimension with multiple facts?

[devarasu07]

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);

[marcus_sommer · Accepted solution]

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

[devarasu07]

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)

[marcus_sommer]

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

[devarasu07]

Hi Marcus,

It works, Tks