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 Systems analysis actively factors out “derived data”. Only unique data fields 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 and to promote user safety 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”

9. Solution? Reporting Dimensions • Provide commonly used abstractions 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 “time” dimension 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. Avoid overloading Measures Implement 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 • Period or Cumulative • Balance

13. Example – P&L report?

14. Implementation

15. Good design 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”

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