Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Struggling with this Data Mart Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 9:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:39 PM
Points: 325, Visits: 382
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.


  Post Attachments 
Combine ExhibitPart.PNG (12 views, 106.77 KB)
Post #1496207
Posted Thursday, September 19, 2013 7:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:04 AM
Points: 57, Visits: 613
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.
Post #1496383
Posted Thursday, September 19, 2013 8:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:39 PM
Points: 325, Visits: 382
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.
Post #1496412
Posted Friday, September 20, 2013 10:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:04 AM
Points: 57, Visits: 613
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.
Post #1496956
Posted Saturday, September 21, 2013 10:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:39 PM
Points: 325, Visits: 382
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.
Post #1497180
Posted Saturday, September 21, 2013 10:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:39 PM
Points: 325, Visits: 382
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.
Post #1497182
Posted Monday, September 23, 2013 8:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:04 AM
Points: 57, Visits: 613
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)
Post #1497432
Posted Monday, September 23, 2013 9:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 461, Visits: 1,684
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.
Post #1497441
Posted Monday, September 23, 2013 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:39 PM
Points: 325, Visits: 382
larskandersen, I can't thank you enough for the information.

kl25, thanks for the book reference. I'll certainly check it out.
Post #1497464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse