Forum Discussion
Modelling Advice: Fact or Dimension Table
Hello everyone,
Can you please give me a short input on what would be the best start to model the following screnario:
We have the following tables
1. "ticket": this holds information on the production of our items. Especially: ticket ID (unique), production start date, ticket creation date, product type, manufacturing location, machine used and the responsible manager, production status and so on
2. "time": this holds information on all time spend by the different employees per ticket. so it has the ticket id, the employee number and duration infomation, time entry number (unique)
3. "sales": we offer our products to different clients on a per ticket level: This means we can have various offers per ticket (of which one may be accepted): The db includes offer id (unique), ticket ID, offer price etc
4. "loations": this contains all the information on the locations
5. "managers": this includes information on the managers
6. "machines": this includes information on the machines
Generally speaking I would model 4,5,6 as dimension tables, 2,3 as fact tables but I struggle with 1.
We regularly need information on e.g. how many tickets have been processed in a location, by a manager etc, grouped by month etc.
We also need overviews comparing different managers on e.g. what was the average time spend per product type - similar for offers.
But we also regularly need information on how many tasks are in which production status and what is the amount of work spend.
Can you get me started please on how would you deal with the "ticket" table? Is this a Fact or a DImension Table? Should it be split somehow?
Thank you and kind regards
5 Replies
Thank you very much, Kurt, for your clear and swift answer.
Should I then have relationships between the "ticket" table and the time and sales tables? I read that there should never be relationships between fact tablesGandalf12345,
Since 1, 2 and 3 are all ticket level facts, you might consider joining them all into one fact table.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
If I understand that correctly, I would then have sth like the following
Ticket ID machine id location id manager id creation date offer ID Offer Price Time Entry ID Time Entry Person Duration (min) 1 M1 L1 E1 01/01/24 O1 100 T1 A1 60 1 M1 L1 E1 01/01/24 O1 100 T2 A1 30 1 M1 L1 E1 01/01/24 O2 50 T1 A1 60 1 M1 L1 E1 01/01/24 O2 50 T2 A1 30 Would not this create a really, really huge table then? Or did I misunderstand?
