CODEWITHSUNDEEP
sql-serverssasdata-warehouseolap-cube
sam
sam

Reputation: 149

SSAS Cube design suggestion

I have a transaction(fact) table and two dimension tables in my data source view.

Fact table - fctSales

Dimension table1 - dimCustomers

Dimension table2 - dimCategories

The Sales fact table is linked with the Customers table on Customer_Skey, however, there is no relationship between fact and Categories table; sales are not recorded at the category level, just at the customers level. I have another table(not in the data source view yet) that acts as a link between customers and categories table but the issue is that each customer could be associated with more than one category. Users do not want to look at sales by category, but they need it in the cube for other reporting purposes - example, for looking at sales by customers but filtering for just one category etc.

Any idea how to deal with this situation? Thanks for your help in advance.

Upvotes: 0

Views: 75

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Just add your relationship table between customer and category to the DSV, and design a many-to-many relationship. An example can be found here: http://technet.microsoft.com/en-us/library/ms345139.aspx. Just replace the tables in the example with yours as follows:

  • Transaction in the example would be your fctSales table.
  • Account in the example would be your dimCustomers table.
  • CustomerAccount in the example would be your relationship table between customer and category.
  • And finally, Customer in the example would be your dimCategories table.

The example may be slightly confusing, since your customer table plays a different role (intermediate dimension) than the customer table in the example (many-to-many dimension).

Upvotes: 1

Related Questions