Lessons in Cube Design

1. Lessons in Cube Design Does your cube support your users reporting needs? Andrew Wiles: iT-Workplace Limited

2. Biography of an Olap Dinosaur • “Precambrian period - Practitioner” • Data warehousing and reporting at Rolls-Royce (System W, APL, SAS PROC Summary) • “Paleozioc period – Olap Database Technology” • Ran Olap development team at SAS Institute in North Carolina (MDDB, CFO Vision) • “Mezozoic period – Financial Applications” • CTO for Danish start-up Corporate Information Partners (“Financial Intelligence” for SQL Server 2000 Analysis Services) • “Cenozoic period - Reporting” • Founded iT-Workplace (Intelligencia Reporting)

3. Objective Reporting needs don’t affect database design Designed to change your mind

4. Interlude Many warehouse developers come from a transactional database background. Traditional database data modelling techniques actively factors out “derived data”. Only unique data entities are captured within the warehouse.

5. Example – P&L report?

6. End user issues... • Need to create calculated rows/columns for common aggregations of data over time • It is counter-intuitive that the time aggregations are not created in the “Time” dimension • Scaling of large data values requires accommodation calculations Reaction... If the user perceives that it is taking too long/is too complicated to use the tools provided then they will copy the data to Excel and create a new data island

7. Some other considerations • Difficult to create historical views of data for prior periods • Not all values roll up the same way over time • Balance sheet • Inventory reporting • Want to select mixed financial and non-financial data

8. Interlude Ergonomics The scientific foundation, both in terms of data and methodology, for a user-centred approach to design; knowledge and methods that aim to develop product and environment to fit the user’s capabilities Human Nature The tendency, particularly when under time pressure, to choose the solution that is perceived to be the quickest and easiest. Designer challenge Make the “right way” the “easy way” SSAS Tools Naming conventions Display Folders Visibility

9. Solution? Reporting Dimensions A reporting dimension is a dimension provided purely to make report building easy. Reporting dimensions will not be discovered by traditional data modelling. • Provide commonly used abstractions and calculations required in report building • Intuitive and consistent “user interface” for non-technical users • Re-usable and tested solutions which improve speed of implementation and reduce calculation errors

10. Time is relative Cubes should have at least 2 time dimensions! • The dimension called “time” should include all aggregations which will be regularly used in reporting • Contains hierarchy of year/quarter/month etc • May contain just a single “year” • The “reporting period” dimension (and reporting year too ) make dynamic reporting easy • Dimensions are “flat” – i.e. They have no hierarchy • Used as pick lists on the filter axis to make historical reporting easy

11. Too many Measures • Avoid overloading Measures by implementing a Subject dimension instead • Use measures for different aggregation rules (e.g. Sum, average, count etc) • Separating time aggregations from subject calculations makes for simpler, re-usable implementation! • Implement attributes which affect aggregation behaviour • OCFA (Opening, Closing, Flow, Average) • CDI (Credit/Debit indicator) • Scaleable (affects data scaling behaviour) • Calculations between subjects are simple and intuitive. Not true if calculations between time and subject are mixed.

12. Utility dimensions Just make life easy... • Units, Thousands, Millions • Period or Cumulative • Accounting Balance • Unit of Measure – Distance • Unit of Measure – Volume • Unit of Measure - Weight

13. How to identify Utility Dimensions Look for common attributes in reports... Comment Provide users with “pick lists” for analyses. Picking from a list is always easier than creating a calculation. • Aggregations over time • Year to date • Quarter/half year • Rolling “n” months • Scenario comparisons • Budget/Plan/Actual variance • Current year vs. prior year • Scaling of data • Units • Thousands • Millions

14. Example – P&L report?

15. Complex Interactions Comment These are calculations that make use of member attributes and are therefore extremely difficult for an end-user to visualize and implement. • Example: Opening, Closing, Flow, Average • Typically occur where the aggregation of values over time differs. • Can require additional data to support, e.g. An “Account type” indicator. • Example: “Point in time” reporting • Removal of “future period” data • Re-statement of results (Data was entered in February that affects January) • Multiple time dimensions (Separate reporting period and year from “time”) • Example: Scaling of different data values • Money values available in units, thousands, millions. • Head count always in whole “units”

16. Implementation Re-usable, maintainable solution • Interaction between reporting dimensions is complex • MDX for time calculations is abstracted from the subject matter • Can be generalised • Implemented via stored procedures • Dimension renaming through “filter views”

17. Schema for Utility Dimensions The UtilityId column is a calculated column that is set to the value 1. Each Utility dimension has a .&[1] member which represents the base value in the fact table.

18. Implementation • Calculations are implemented in stored procedures that add MDX expressions to the dimension tables. Whilst it is easier to edit and test changes in the script the use of stored procedures provides: • The utility dimension can be shared between multiple cubes • Stored procedures can be saved as text files under source control • Tested utility calculations can be used across multiple projects

19. Implementation Standardised calculations can be specialized in the dimension views so as to remove the need to edit the tested calculation for a specific instance.

20. Implementation

21. Questions andrew.wiles@it-workplace.com http://andrewwiles.spaces.live.com http://www.it-workplace.com