Modeling Related Fact Tables

View previous topic View next topic Go down

Modeling Related Fact Tables

Post  amattas on Mon Jul 09, 2012 9:52 pm

I'm working on modeling a star schema which has related facts and ultimately will be turning it into a SSAS cube. I want to make sure I'm thinking about this right.


  • Everything starts off with a product inquiry (factProductInquiry)
  • Some inquiries spawn a complaint (factComplaint)
  • Some complaints spawn an investigation (factInvestigation)
  • Some complaints spawn a recall (factRecall)


Atleast that is the general idea, because of the wierd sometimes relationships I've made a few "different" design decisions around some of the data which would traditionally be a degenerate dimension for the ability to more easily tie the measure groups together in the SSAS cube. There is likely a little more cost during the ETL process, but my thought is the cube will process more quickly and will be a more user friendly experience.


  • I created a dimension to store information on product inquiries (dimProductInquiry) including the product inquiry id and short reason for the inquiry. It's a small dimension which as I mentioned before I typically would make a degenerate, however in this case it's an attribute on all four fact tables.
  • I did the same for complaints (dimComplaint) and the dimension is related to factComplaint, factInvestigation, and factRecall
  • For factInvestigation I did the more traditional approach and made the investigation id and short descriptions degenerates
  • And did the same for factRecall


The other interesting thing is I have some measures that are calculated accross fact tables when different facts are in different states, I've opted to keep those fact values as NULL until the records are in the correct state to not throw off aggregations and allow SSAS to handle the nulls appropriately (as nulls instead of 0).

Does anyone see any glaring issues with this approach?


Last edited by amattas on Mon Jul 09, 2012 10:04 pm; edited 1 time in total (Reason for editing : Added comment about null measures)

amattas

Posts : 4
Join date : 2011-11-16
Location : Portage, MI

View user profile http://www.mattas.net

Back to top Go down

Re: Modeling Related Fact Tables

Post  amattas on Mon Jul 09, 2012 10:32 pm

Another thought is I also have with the "process sensative" measures is to make them calculated measures in the cube that way I'm not updating fact records.

amattas

Posts : 4
Join date : 2011-11-16
Location : Portage, MI

View user profile http://www.mattas.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum