Structuring a cube with paths from a fact table to a dimension via two alternative intermediate dimensions
I am not sure how to set up a cube in SSAS for a complex case, which I can simplify as follows:
- The fact table stores sales data.
- A dimension called Promotion records the marketing activity that generated the sale.
- A dimension called a customer contains information about the person we sold
- We also have a table containing organization data
- In some, but not all cases, an ad campaign is aimed at an organization. There is an additional one-to-one relationship from Promotion to Organization.
- In some, but not all cases, the Client is associated with the Organization. There is an additional many-to-one relationship from client to organization.
- We want to be able to analyze Sales by Organization. For example, if I report the number of sales for an organization, the count for each organization should include both sales through promotions targeted at that organization and customers associated with that organization.
Please note that with this data structure, each sale can be associated with 0, 1 or 2 organizations, depending on the Promotion and the Customer. Therefore, if I report the number of sales by an organization, the total does not necessarily equal the total sales.
How would you structure the cube? I don't think it can work just by setting up the links related to Sales-> Promotion-> Organization and the other from Sales-> Customer-> Organization, because SSAS won't know which path to use (and of course not will know that it should merge both paths together). Create two sizes of organization? Do I exclude Organization from other dimensions and define some direct relationship between Organization and Sales? Do you de-dimension the organization and include the organization data as attributes for both the ad and the customer?
source to share
You are correct in that SSAS will not be able to handle references to the Organization dimension through progress in one path and through a client in the other path. This will give you an error when trying to build a cube.
Since each sale can be associated with 0, 1, or 2 organizations, I would recommend modeling this with a (many-to-many) bridge table between the Organizational dimension and the fact of the sale. This assumes that you have a unique ID in each Sale-transaction so that you can create a fact-size in the fact of the sale (which should not be visible in the cube).
You are building a bridge table on an ETL stream. It should just contain 2 columns that link the Organization ID to the Sales IDs. The sales ID must not contain more than two Organization IDs. Your final model should look something like this:
DimCustomer <---.
|
FactSale <---- BridgeSaleOrganisation ----> DimOrganisation
|
DimPromotion <---´
In dimensional use of SSAS, you establish a Many-to-many relationship between FactSale and DimOrganisation using BrdigeSaleOrganisation as an intermediate table. Once that happens, filtering sales by organization size will give you all sales that are owned by that organization through the bridge table, whether they go through a promotion or a customer.
For more examples of many-to-many modeling, check out this excellent article by the gurus, Marco Russo and Alberto Ferrari.
source to share