SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Struggling with this Data Mart


Struggling with this Data Mart

Author
Message
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
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.
Attachments
Combine ExhibitPart.PNG (15 views, 106.00 KB)
larskandersen
larskandersen
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1032
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
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
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
larskandersen
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1032
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
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
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
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
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
larskandersen
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1032
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
kl25
Say Hey Kid
Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)

Group: General Forum Members
Points: 684 Visits: 1875
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
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
larskandersen, I can't thank you enough for the information.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search