I'm new to star schemas and designing for an OLAP cube. The scenario is traditional sales analysis. I have customer, item, time, and sales person dimensions. Working on facts.
In the relational world, I have an order master table, a shipment master table, an order line-item table, and a shipment line-item table. Obviously, order line-items relate to orders and shipment line-items relate to shipments, and shipments relate to orders. Of course, the line-item tables relate to products, where as the "master" tables do not (they relate to customers and time, but not individual products).
Most facts (such as sales amount or shipped amount) can come from the item-detail tables and roll up as expected. I'd love to only model the detail-level facts. However, there are some measures (such as freight costs for a shipment) that exist on the shipment master but not on the item-detail. How do I model the fact table(s) to support a cube for this? Am I looking at four separate fact tables?
Sorry for such a basic question but I didn't have any luck because it is hard to search for and most star schema info talks generically about "facts" and "measures" but skips over the more detailed cardinality.
Best regards,
Newbie
Simple Star Schema Question re: nested facts
-
- Posts: 1
- Joined: Fri Aug 05, 2011 7:58 pm
- OLAP Product: SQL Server Analysis Services
- Version: 2008
- Excel Version: 2007
-
- Community Contributor
- Posts: 126
- Joined: Sun Jun 29, 2008 9:33 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2016
- Location: Karlsruhe
Re: Simple Star Schema Question re: nested facts
From SSAS 2005 onwards it is possible to create a cube with several measure groups and varying dimensionality based on one fact table each. The Adventure Works example is very good in this context and I suppose it comes close to your requirements.