I have been working on this for a month and I have taken the data modeling course and I am still a little confused as to how I would make the data model work without adding in synthetic keys.
Sales and Purchase table has been concatenated into Sales and Purchases. There can be more than 1 particular record on the same date with more than one sales/purchase order. When I map the InventoryKey and the Warehouse to the Dailyinventorystatus table I get a synthetic key. I created an nventoryStatId which is the autoHasNumber using the warehouse, Transdate, and InventoryKey, but i am unsure how to link that to the SalesAndPurchase table. I know I should look at creating a fact table, but at the same time I do not want to create duplicate records. I.e if I do a left join on the Sales and Purchase Table, because there are multiple purchases/sales for the same item on the same day it will create a duplicate on the dailyinventorystatus table. Help would be immensely appreciated.
Also incase anyone is wondering the masterid maps to the InventoryKey. How do I create a data model. This has had me scratching my head for ages.
You already concatenated two fact tables into one concatenated fact. Now, you need to concatenate your third table, the Inventory Status table, into the same Fact table (not JOIN, but CONCATENATE). Then, you will be able to keep a single field TransactionDate and avoid any synthetic keys.
Now, some of your existing associations between Sales and Inventory will get broken. For example, you won't be able to easily show Inventories for the products purchased by a certain Customer. You will need to restore some of these "broken links" with so called generic link tables and other techniques.
For a detailed tutorial on advanced data modelling techniques, I can recommend my book QlikView Your Business. In the book, I describe with a lot of detail how to build a Concatenated Fact model and how to restore broken links.
We also teach advanced Data Modeling techniques at the Masters Summit for Qlik - coming soon to Milan, Italy and to Austin, TX!
Thanks! I have looked at your book and I have tried to create a fact table. But I'm not sure how it will work, as there are multiple purchases on the same day for the same item and in that case, it will crate a duplicate for the dailyinventorystatus when I do concatenate the table. I will be ofcourse concatenating the Warehouse, InventoryKey and the TransactionDate. If you can provide me a starting point that would help.
ASma, you might want to consider using 'generic keys' (linked below) because you have different dimensions for the different measures. Also, consider keeping Sales Order and Purchase Order data separate, but again connect them using Dimensional Link / Master Link tables discussed in the linked article.