If the amounts of your data aren't quite huge (of course related to the resources of your environment) I would probably go with option 2 but avoiding the synthetic key by renaming some of the fields, for example Center into [Center Sales] and [Center Usage] and doubling the dimension-tables to them if really necessary.
Option 1 is in general possible because asymetric fact-tables aren't a problem in Qlik but your fact-tables seems to have far more differences than similarities so that it doesn't look very sensible. Are your data huge and/or you experienced performance issues you need to reduce the number of tables and to merge as many data as possible in one big fact-table - but in this case with mapping/joining and conatenating the tables.
Quite common is also to create a link-table between the fact-tables which contained their common fields - but in my opinion it makes the things not really easier and link-table models have often a poor performance.
Hello Marcus, Thanks a lot for your answer.
About Option 1:
I have many records corresponding to usages of the card, that is, mainly the largest table is the Table “Usages”.
I cannot test the performance issues because I'm testing my model with a small part of the data in order to be able to verify what works and what does not and to see if it makes sense and the data is displayed correctly.
The three fact tables are different because they are not the same kind of fact. I thought of it as "Actions / Facts on the card", with fields in common:
- What kind of action is carried out (FaktTyp)
- Where the action is carried out (Center)
- Exactly from which terminal the action ist carried out (Terminal)
- When the action occurs (Date)
- On which card (Card-id)
But it is also true that there are other fields that have nothing to do because they are specific to each kind/typ of facts
Option 2 and other alternative Options for Option 1:
we have model with 3 fact-tables
- Example 1: (Link-table)
- Example 2: (mapping)
we can also have model with 1 fact-table, with Join-table or Concat-Table
I have 3 fact-Tables that are different und sharing some dimensions but no others und the 3 fact-tables are related.
- Doppel Dimension-Tables sind optimal?
- Facktable with a lot of columns is optimal?
- with only 1 Facttable can i make relations between the files of the table?
- If I keep the 3 tables without grouping it is better to make a link table or put the data of the dimensions in the fact tables?
It's quite difficult to estimate which approach might be the most suitable in your case. I would tend in the direction of option 2 although it looked easier to connect the tables with some combined-keys (concat of several key-fields) or to create a link-table.
Each of the methods have their benefits and disadvantages and unfortunately there is no optimal datamodel: A perfect datamodel?
If I get unknown data with certain requirements which I couldn't really judge at the moment I would develop two or even three datamodels parallel - starting with a small dataset and loading the tables in the easiest way and then using some pivot-tables and tableboxes to check if the data look plausible and then trying to apply the most challenging view which is required - and this quite roughly without implementing a nice looking gui or integrating it in the environment-structures.
If I get the needed results I increase the amount of data to the real one and if it is performant enough I build a real application. If it don't work I need to adjust my doings and maybe switch to a different model.
It sounds a bit like trial & error and yes it is - but a systematically one. The more you understand the data and the requirements the more you could avoid the trials and just thinking some minutes/hours and to sketch the essential points on a white paper and afterwards is just some typing work (and tests of course).
Just for the first example, if the card is activated the first time that is used, why you don't create a flag in the usages fact table? It would be easier that use another fact table. (if not, you can create a map table and create the flag).
I always use one single fact table, and normally is a concatenation of several ones and I never had been problems to obtain the data.