Simple Star Schema Question re: nested facts

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
olapnewbie
Posts: 1
Joined: Fri Aug 05, 2011 7:58 pm
OLAP Product: SQL Server Analysis Services
Version: 2008
Excel Version: 2007

Simple Star Schema Question re: nested facts

Post by olapnewbie »

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
Marcus Scherer
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

Post by Marcus Scherer »

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.
Post Reply