Should I Combine these Dimensions?

[jacobpressures]

I'm learning dimensional modeling and I i ran into a problem. I have 2 dimension tables for the same item but 2 different purposes. Should I combine them? The two dimensions are ExhibitParts and ExhibitSeats

I've built the models and attached the pictures.


I'm building a data warehouse for an Amusement Park. Exhibits have seats and seating capacity. I have an ExhibitSeat dimension. I call my fact table a ride althought this probably should change. (I could call it an Event or Experience i guess.)

Then I have an ExhibitPart dimension. It lists all the parts. Status means (inspected, broken, replaced). (I had a separate inspection table but i decided to combine it with the Exhibit Part dim. I'm not sure that was a good or bad idea. I just thought the Inspection dim was too small.) So for each part, there are 3 statuses in the ExhibitPart dim. The fact table is an Inspection Event. It lists the part, the employee that did the inspection, etc.

I know redundancy is not as big an issue with the dimensional model but I still wonder about having Exhibit name listed in several different dimensions. Plus a Seat is a type of part. Am I confusing different concepts? (I'm starting to think i should leave these separate.)

Another similar question is whether i should include Location dim in with the Exhibit dims? There are only 5 locations.

I have a few more questions after this.

Thanks guys

[ngalemmo]

I would keep them separate. It appears they serve different purposes and carry different information. There is no reason the parts dimension could contain seats as well from the point of view of doing maintenance on them.

[jacobpressures]

Thanks very much that was helpful!

Now I'm wondering where I should put price in Exhibit Part or the Fact table? Based on the information that's in the book and 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-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.

[jacobpressures]

I was given this basic advice from SQL Central. It sounds good and refreshing, but I still don't understand based on principle what is wrong with the current design.  How do i know when I'm trying to do too much? OR when I should simplify the design?

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.

I mean couldn't i do a list item and say there were 4 broken in QTY and another list item that said 2 were replaced? The grain would then be the list item. (I agree, it still seems more complicated than what i have below. But i'm trying to think this through.)

Based on this comment I could create 2 data marts One for Replaced Parts and the other for Inspection. I'm thinking that I could 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!

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

[Sponsored content]