Lessons in cube design
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

Lessons in Cube Design PowerPoint PPT Presentation


  • 53 Views
  • Uploaded on
  • Presentation posted in: General

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)

Download Presentation

Lessons in Cube Design

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)


Objective

Objective

Reporting needs don’t affect database design

Designed

to change

your mind


Interlude

Interlude

Systems analysis actively factors out

“derived data”. Only unique data fields

are captured within the warehouse.


Example p l report

Example – P&L report?


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

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


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


  • Interlude1

    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”


    Solution

    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


    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


    Example p l report1

    Example – P&L report?


    Implementation

    Implementation


    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”


    Questions

    Questions

    [email protected]

    http://andrewwiles.spaces.live.com

    http://www.it-workplace.com


  • Login