What is the best data model key?

[jfadderholdt]

Hi. I am building a data model for analyzing SAP material movements at a few plants. I am new to this type of activity, and though I have built an original model avoiding synthetic keys, I find that the selection of related plant data is tedious. If the user wishes to focus on one particular plant, they have to select that plant in each of the associated data tables. So I need some help with building the correct key. The diagram below shows the current design and one that I was thinking might work as an alternative. The user wished to be able to select one or a small group of materials (parts) and then select one particular plant location and have just the data related to that plant displayed in a table etc. I have a abbreviated table diagram shown below. It lists each table and the linking key and a short list of some of the related data (for context). Will the Proposed Design work as intended? Or do I need a more sophisticated linking table. What is the best approach. I could also easily include the contents of the "Material" table in the "Plant" table and eliminate it as a separate table. Thanks for your time.

[Oleg_Troyansky]

Hi John,

this is a huge question... In a nutshell, you can either link all of your separate facts with a Link Table that combines all common Key fields (in addition to Material and Plant, I suspect that you need to add a Date or at least a Period to connect your various transactions to a common time line), or you could concatenate all the facts into a single Concatenated Fact model.

The Link Table is easier to build with smaller data sets, while Concatenated Facts are performing better, especially with larger data sets. Both techniques involve some advanced learning. I describe both methods in my book QlikView Your Business, including several chapters dedicated specifically to inventory analysis. I'd highly recommend to check it out.

I'm also offering an advanced training class, largely based on the advanced techniques described in my book. It should become available soon as a public offering. Check it out if you see it advertised.

best,

Oleg Troyansky

[Anonymous]

Oleg has good advice.

Here is also a link that discusses concatenate vs link table.

Concatenate vs Link Table