SQL Bits 8

1. SQL Bits 8 Dimensional Modelling for a Flexible Cube John Stevens-Taylor john.stevens-taylor@contemporary.co.uk @JStevensTaylor http://uk.linkedin.com/in/johnstevenstaylor http://throughadarkglassclearly.blogspot.com/ http://www.contemporary.co.uk

2. What is a ‘Flexible Cube’ • Each Reseller may have a different way of measuring sales • Each Reseller can be compared to a number of others • When Resellers are compared they should be anonymous • Each Reseller may have different buckets with boundaries at any point • When Resellers are compared they should use the measures and buckets of the ‘main’ Reseller

3. Each Reseller may have a different way of measuring sales • We need to define each method of calculation a measure that any reseller uses This is done through a number of views which are combined into a single view using a UNION statement. *

4. Each Reseller may have a different way of measuring sales We need to create a table to hold which measure method each reseller uses This will hold… • The reseller key to join back to the Dim Reseller table • The measure group A key for the measure calculation method • The measure group name and the measure description to assist maintenance through self documentation *

5. Each Reseller may have a different way of measuring sales We also need to extend the Reseller dimension to provide a reference to the appropriate measure method This is done using a view onto the dimension table and the configuration table. *

6. Each Reseller can be compared to a number of others When Resellers are compared they should be anonymous • We need to create a table to map each of the resellers that is valid as a comparison for each reseller. This holds the key for each reseller plus the text we want to be displayed. *

7. Each Reseller can be compared to a number of others When Resellers are compared they should be anonymous We need to extend the Reseller dimension again to provide a reference comparison resellers with the display text This is done using a view onto the dimension table and the configuration table. *

8. Each Reseller may have different buckets with boundaries at any point • We need new tables to define the boundaries of our buckets for each data type we want to analyse. • These contain the upper and lower limits, the text to display and a key. • This allows us to use an INNER JOIN to obtain the correct bucket key in the fact views *

9. Each Reseller may have different buckets with boundaries at any point These new table are referenced in views to create sources for bucket dimension. These use the reseller dimension joined on the bucket definition table and the bucket configuration table. *

10. Each Reseller may have different buckets with boundaries at any point We create a new table to map the bucket definitions to the resellers This also needs to map the buckets to the appropriate measure groups *

11. When Resellers are compared they should use the measures and buckets of the ‘main’ Reseller This is achieved by joining the fact rows on the comparison reseller key in the view but selecting the view to use based upon the main reseller key *

12. A Flexible Cube • Building the flexible cube from these views is quite standard • The dimensions are drawn from the reseller and bucket views and the facts from the UNIONed fact view • These need to use a composite key in the relationships between fact and dimension *

13. Questions Dimensional Modelling for a Flexible Cube John Stevens-Taylor john.stevens-taylor@contemporary.co.uk @JStevensTaylor http://uk.linkedin.com/in/johnstevenstaylor http://throughadarkglassclearly.blogspot.com/ http://www.contemporary.co.uk