CODEWITHSUNDEEP
ssisssasfact-table
Paul
Paul

Reputation: 3

Business intelligence solutions with many to many data sources

I have unified fact and dimension tables along with SSAS cubes for a web traffic data warehouse and now I am trying to see how particular campaigns affect traffic over various dimensions.

How do I go about this process in terms of general approach and data models for the ETL as I see it as a many to many relationship. With my current ETL solution, a web request comes from a single device in a single country etc so I simply apply the appropriate dimension keys when constructing the fact table. A campaign may be directed to target several platforms and also several campaigns would be active at the same time.

Basically, what I am looking for is to be able use the current table views where I can slice/dice traffic by dimensions but then overlay this with a side by side list of all campaigns that may have affected this traffic.

Upvotes: 0

Views: 460

Answers (1)

Vinnie
Vinnie

Reputation: 3929

I recommend reading the Many to Many whitepaper from SQLBI.com: http://www.sqlbi.com/articles/many2many/

I recommend starting with a named query in the DSV that will contain only the keys for the fact and dimensions you want to connect. For example, if a Campaign could include multiple platforms, you would have create a fact table within your DSV that join the Campaign dimension with the Platform dimension. The result is known as a factless fact table. You bring the factless fact into the cube, but hide any measures from it. Then, in the dimension attribute relationships, create a regular dimension relationship from the Campaign and the Platform to the factless fact group, then a Many to Many relationship for the remaining measures groups across those dimensions.

The whitepaper does a great job talking through the scenarios and sharing examples. It's worth a cover to cover read.

A few additional links:

http://msdn.microsoft.com/en-us/library/ms170463.aspx

http://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx

Upvotes: 1

Related Questions