Dimensional Relational vs. OLAP: The Final Deployment Conundrum - InformationWeek
IoT
IoT
Software // Information Management
News
4/27/2007
06:36 PM
50%
50%
RELATED EVENTS
Faster, More Effective Response With Threat Intelligence & Orchestration Playboo
Aug 31, 2017
Finding ways to increase speed, accuracy, and efficiency when responding to threats should be the ...Read More>>

Dimensional Relational vs. OLAP: The Final Deployment Conundrum

The choice between deploying relational tables or OLAP cubes is not a trivial matter. Weigh these 34 pros and cons of each approach early in the design of your extract-transform-load system.

OLAP Disadvantages

  • The big objection to OLAP is the proprietary, non-standard character of each vendor’s OLAP offering.
  • Don’t expect to port an OLAP implementation to another vendor’s product. Discard everything including all your application development
  • There is no accepted, universally implemented access language for OLAP, although Microsoft’s MDX is the closest thing to a standard access language. Significantly, Oracle has not embraced MDX, preferring to rely on SQL for all forms of database access.
  • MDX in its full glory may be too complex for IT personnel to write by hand, or understand a complex application. But to be fair, I have looked at incomprehensible SQL many times!
  • Historically there has been much less industry expertise in MDX than in SQL although Microsoft has successfully sponsored a cottage industry of training organizations.
  • OLAP application development expertise is fragmented by vendor.
  • OLAP cubes can be catastrophically invalidated if you are not careful, i.e. a Type 1 SCD change to a dimension may cause all the cubes using that dimension to rebuild!
  • OLAP cubes are not considered stable enough for serious archiving and backup: this is a strong reason for creating a set of dimensional relational tables duplicating the content of the cube for those purposes.
  • OLAP vendors have certain size limits not present in relational implementations including possibly the number of members in a dimension, the number of distinct values at various levels in a hierarchy and the overall size of the cube.
  • When you must rebuild a cube, it may be a VERY time consuming process.

    Equally Easy in Either Approach

  • The basic foundations of dimensional modeling, including slowly changing dimensions, the three fundamental grains of fact tables, and achieving integration through conformed dimensions, are equally easy to deliver with either approach, especially since these dimensional structures are carried to the very last step of the ETL pipeline.

    Making the Final Choice

    So how do senior management and the enterprise BI system designers resolve the final deployment conundrum: Dimensional relational or OLAP? As I hope you appreciate, there is no slam dunk answer since there are significant advantages and disadvantages for both approaches. But let’s consider two extremes. If you are a large distributed enterprise with a number of different database vendors and you are struggling to establish more commonality across your BI deployments, as well as creating an enterprise wide pool of BI development expertise without being beholden to any single vendor, then I recommend dimensional relational. On the other hand, if you are looking for the most potent single-subject solution with high performance and killer analytics, and you are confident that you can source the development expertise you need, then I recommend OLAP supported by underlying dimensional relational schemas. Otherwise, it depends.

    Ralph Kimball, founder of the Kimball Group, teaches dimensional data warehouse and ETL design through Kimball University and reviews large warehouses. He has four best-selling data warehousing books in print, including The Data Warehouse ETL Toolkit. Write to him at ralph@kimballgroup.com.

    Previous
    2 of 2
    Next
    Comment  | 
    Print  | 
    More Insights
  • Comments
    Newest First  |  Oldest First  |  Threaded View
    How Enterprises Are Attacking the IT Security Enterprise
    How Enterprises Are Attacking the IT Security Enterprise
    To learn more about what organizations are doing to tackle attacks and threats we surveyed a group of 300 IT and infosec professionals to find out what their biggest IT security challenges are and what they're doing to defend against today's threats. Download the report to see what they're saying.
    Register for InformationWeek Newsletters
    White Papers
    Current Issue
    IT Strategies to Conquer the Cloud
    Chances are your organization is adopting cloud computing in one way or another -- or in multiple ways. Understanding the skills you need and how cloud affects IT operations and networking will help you adapt.
    Video
    Slideshows
    Twitter Feed
    Sponsored Live Streaming Video
    Everything You've Been Told About Mobility Is Wrong
    Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.
    Flash Poll