Structuring a cube with paths from a fact table to a dimension via two alternative intermediate dimensions

Question

I'm unsure how to configure a cube in SSAS for a complex case that I can simplify as follows:

1. A fact table stores data about a Sale.
2. A dimension called Promotion records details of the marketing activity that generated the Sale
3. A dimension called Customer records details of the person who we sold to
4. We also have a table holding data about an Organisation
5. In some, but not all cases, a Promotion is targetted at an Organisation. There is an optional one-to-one relationship from Promotion to Organisation.
6. In some, but not all cases, a Customer is associated with an Organisation. There is an optional many-to-one relationship from Customer to Organisation.
7. We want to be able to analyse Sales by Organisation. For instance, if I report number of Sales by Organisation, the count for each Organisation should include both the sales through Promotions targetted at that Organisation and sales to Customers associated with that Organisation.

Note that with this data structure, each Sale may be associated with 0, 1 or 2 Organisations depending on the Promotion and the Customer. So if I report on number of sales by organisation, the grand total will not necessarily equal the total number of sales.

How would you structure the cube? I don't think it can work by simply setting up a referenced relationship from Sales->Promotion->Organisation and another from Sales->Customer->Organisation because SSAS won't know which path to use (and certainly won't know that it should aggregate across both paths together). Do I create two Organisation dimensions? Do I disconnect Organisation from the other dimensions and define some direct linkage between Organisation and Sales? Do I scrap the Organisation dimension and include organisation details as attributes in both Promotion and Customer?

Answer 1

You are correct in that SSAS won't be able to handle referencing the Organisation dimension through Promotion on one path, and through Customer on another path. This will give you an error when you try to build the cube.

Since each sale can be associated with 0, 1 or 2 organisations, I would recommend modelling this with a bridge-table (many-to-many) between the Organisation-dimension and the Sale-fact. This assumes that you have a unique ID on each Sale-transaction, so that you can create a fact-dimension on the Sale-fact (which need not be visible in the cube).

You construct the bridge-table in your ETL-flow. It should simply contain 2 columns, which relate the Organisation ID's with the Sale ID's. No Sale ID should have more than 2 Organisation ID's. Your final model should look something like this:

 DimCustomer <---.
                 |
              FactSale <---- BridgeSaleOrganisation ----> DimOrganisation
                 |
DimPromotion <---´

In the dimension-usage of SSAS, you set up a Many-to-many relation between FactSale and DimOrganisation using the BrdigeSaleOrganisation as the intermediary table. Once this is in place, filtering sales by the Organisation-dimension, will give you all sales belonging to that organisation via the bridge table, no matter whether they are through Promotion or Customer.

For more examples of many-to-many modelling, check out this excellent paper by "the gurus", Marco Russo and Alberto Ferrari.