Modelling Advice: Fact or Dimension Table

[Gandalf12345]

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

[Gandalf12345]

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 tables

[Wilson_]

Gandalf12345,

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.

[Gandalf12345]

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?

[Wilson_]

Looks like we're on the same page. What do you mean by a "really, really huge table"?

[Gandalf12345]

Hi Wilson_
we have ~10 million tickets, 20 million time entries and 13 million offers. If I have them in 1 table I would combine basically every time entry with every offer, wouldn't I? That would make the combined table enormous.

As an example: 1 ticket, 2 offers, 2 times entries would result in the table in the previous post (4 lines with the offers and time entries twice)

Having 3 offers and 3 time entries on that ticket would lead to 9 rows.

Am I missing something here?

Best regards

Gandalf