Many-to-Many Dimensions – ETL to Cube
Presenter: Lisa J Phillip
A few years ago, while re-architecting our Data Warehouse we found that, in many circumstances, performance of reports based on cubes was far superior to those based on tables. 80% of our reports now run off Analysis Services (2008) cubes. This meant ensuring complex relationships with fact tables were handled well at the cube as well as the database level. Using the example of a sale that can be attributed to many departments, this session will demonstrate best practices for handling some of these complex relationships:
1. Using the Group dimension table design strategy, I have found to be the best way to model these relationships
2. One strategy is to have the group dimension acts as a “fact” in order to build out measure groups in your cube design, and relate any dimension in other measure groups to it. This allows you to filter by Sales where the Sales department AND the Marketing department contributed. This strategy comes with its own performance issues.
3. Another strategy allows you to filter where Sales NOT Marketing contributed. To accomplish this I had to utilize not only the group bridge, but pivoting as well as finding a way to add attributes within a dimension without having to reprocess the entire cube. An “NOT” filter is rarely ever achieved while browsing an Analysis Services cube.
About the Speaker
Lisa Phillip is currently a Senior Data Warehouse Engineer with Quicken Loans. She has been involved in building out and maintaining very large Data Warehouses for the past 12 years. Her current responsibilities include everything from administration and maintenance, performance tuning, cube and dashboard building in a SQL Server 2008 environment. She also has experience leading a project to architect and rollout a massive Disaster Recovery strategy. She attended The Data Warehouse Institute summit in San Diego (2010), where she and her Director of Business Intelligence accepted the Best Practices Award for Operational BI.