Struggling with this Data Mart

Question

Struggling with this Data Mart

Jacob Pressures

SSCertifiable

Points: 5804
More actions
September 18, 2013 at 9:48 pm

#279885

I'm doing dimensional modeling and i'm struggling with this data mart. Can someone help?
I'm building a data warehouse for a fictious Amusement Park. I've attached pictures of the data mart.
Now I'm wondering where I should put price in Exhibit Part or the Fact table? Based on the information that's found at this link, it seems that I should put it in the Fact table because it is to calculate the cost of the part. These prices could also change. They are not stable. So I'm thinking the fact table is best. http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/numeric%20-attribute-fact/
The next questions are much harder for me at least.
I'm also wondering about duplicate rows. Supposed there are 16 parts of the same type. I would have to show that i inspected each one of them individually giving them each a row. OR I could make the grain one PART TYPE and add a QTY column to state the number parts inspected, replaced or broken.
So there could be one row with 10 parts inspected, another row with 4 parts identified as broken, and another row for 2 parts that were replaced. That would save me 16 records. (At least right now i don't think the individual parts themselves are important. Parts wear out and need to be replaced. So why provide a record for each and every part?)
Also i notice, price is not addictive except on replaced parts. I'm not sure how i will handle that especially if i put it in the Fact table.
I was wondering how i could record how much it cost to maintain the exhibit.
This is a problem I have no clue what I should do--assuming the rest of my design is ok and I'm not fooling myself. 🙂
I'm open to ideas and suggestions.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

larskandersen Ten Centuries Points: 1232 More actions · Answer 1

Based on your description, I believe your problem is that you try to put everything into one fact table.

The inspection is one fact table, with the grain being one row per inspection. Don’t do a QTY column, you’re violating the grain. You can add include an inspection count with the constant value of 1 to make it easier to get the QTY inspected from the fact table.

Replaced parts is a separate fact table where the price should be part of fact, so you can add up the cost of all replaced parts.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 2

Thanks very much for the information. It sounds very good. It seems like a breath of fresh air. I'm new to this and I'm trying to think it over.

So in my Inspection Fact table, should I place a boolean flag to determine if it is broken or not? And i guess if I want to know if a broken part is severely broken and not operational, i could put another flag for not operational?

I'm still trying to understand what i should learn from this. What principle applies.

Thanks. I think i like this solution. I'm giving it more thought.

larskandersen Ten Centuries Points: 1232 More actions · Answer 3

Instead of creating multiple flags, create a new dimension describing the status or outcome of the inspection. This new dimension could have values like: New, Operational, Some wear, Schedule repair, Not operational, Broken. This could provide some insight to the lifespan of the part, since a part starts as New, gets inspected a couple of times and finally replaced.

You might want to consider a Reason dimension to the repair fact, describing the reason of the repair: Scheduled, Emergency, Broken.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

Thanks again for the very valuable information larskandersen!!! I did not see the notification that you had replied.

Thanks very, very much. I'm going to apply your suggestions. I went to the Kimball forum and I'm not getting anything. So i greatly appreciate the feedback.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 5

IF it is not too much can you tell me what was the clue to your realizing that i was trying to do too much with that data mart? I see it but i don't see it. I guess, one clue is that the design only worked part of the time. Maybe there are things that helped you to see that I needed a different data mart for inspection and repairs.

I'm just wondering if there are some indicators that I should learn. Maybe it will just come with time.

Thanks so very much. You've been a ton of help on this.

larskandersen Ten Centuries Points: 1232 More actions · Answer 6

In your fifth paragraph you mention: "inspected each one", this implies a business process. Then later "number parts inspected, replaced or broken", this implies that there is a Repair or Replace business process.

You should be looking for business processes.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/business-process/

Finally, you ask an excellent question: "I was wondering how i could record how much it cost to maintain the exhibit"

At this point, you just add up the cost from all your business processes (Inspection, Repair)

kl25 Hall of Fame Points: 3752 More actions · Answer 7

I'm fairly new to this, also. A reference I've found very helpful is Star Schema Complete Reference by Christopher Adamson. It's been a good companion to Kimball's Toolkit. I like Adamson's approach to identifying separate business processes which become separate fact tables.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 8

larskandersen, I can't thank you enough for the information.

kl25, thanks for the book reference. I'll certainly check it out.