lessons in cube design
Skip this Video
Download Presentation
Lessons in Cube Design

Loading in 2 Seconds...

play fullscreen
1 / 16

Lessons in Cube Design - PowerPoint PPT Presentation

  • Uploaded on

Lessons in Cube Design. Does your cube support your users reporting needs?. Andrew Wiles: iT-Workplace Limited. Biography of an Olap Dinosaur. “Precambrian period - Practitioner” Data warehousing and reporting at Rolls-Royce (System W, APL, SAS PROC Summary)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Lessons in Cube Design' - elijah-erickson

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lessons in cube design

Lessons in Cube Design

Does your cube support your users reporting needs?

Andrew Wiles: iT-Workplace Limited

biography of an olap dinosaur
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)

Reporting needs don’t affect database design


to change

your mind


Systems analysis actively factors out

“derived data”. Only unique data fields

are captured within the warehouse.

end user issues
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


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

some other considerations
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


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”


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
time is relative
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
avoid overloading measures
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.
utility dimensions
Utility dimensions

Just make life easy...

  • Units
  • Period or Cumulative
  • Balance
good design
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”

[email protected]