Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Creating a data model without syntehtic keys

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.

data model1.PNG

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.

5 Replies
MVP
MVP

Re: Creating a data model without syntehtic keys

Hi,

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!

cheers,

Oleg Troyansky

Not applicable

Re: Creating a data model without syntehtic keys

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.

vikasmahajan
Honored Contributor III

Re: Creating a data model without syntehtic keys

MVP
MVP

Re: Creating a data model without syntehtic keys

When you concatenate multiple Facts into one table, you do not introduce any duplication - that would happen if you used JOIN and joined multiple facts together.

When you concatenate, picture appending one spreadsheet at the bottom of another - there is no joining, no matching keys, and no duplication.

mhartono
Contributor

Re: Creating a data model without syntehtic keys

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.

Generic keys

Community Browser