OLAP vs Relational Data Sources in Reporting

Published 22nd January 2016

The way that organisations store and model data is an important element of Business Analytics. Before any company can perform any analysis on its activities it needs the data to be gathered from all relevant sources, transformed and stored in a meaningful structure. The two most common structures are:

 

Relational database

  • tabular data organised in rows or OLAP cubes

Dimensional data

  • organised in cubes

The difference in these structures is significant with the implications far reaching so we have listed them below;

Relational

  • Generally an ERP’s database or a data warehouse forms a ‘relational’ source
  • Relational data source for ‘analysis’ is generally in the form of a ‘star schema’
  • Containing Fact & Dimension tables (suitable for populating OLAP cubes)
  • Relational data source from an ERP package might contain thousands of tables with complex joins, making reporting complex
  • Generally contains data at a lower granular level than an OLAP data source
  • Used for ‘detailed’ reporting, as well as summary reporting & analysis
  • Modern reporting software (e.g. Cognos Report Studio) can perform drill-down, but may be slower than if done through an OLAP source
  • Historically better at reporting ‘real-time’, up-to-the-minute data than OLAP, though with ‘trickle-feeding’ OLAP sources can report near real-time as well.
  • Historically, much larger volumes of data can be held in a relational source than OLAP, though reporting will be slower.
  • Relational data bases are extremely stable and are suitable for serious archiving and backup

OLAP (or ‘Dimensional’ reporting … or reporting from a ‘cube’)

  • Presents pre-aggregated or ‘summarised’ data
  • Performance (response times) should be much better than reporting from a relational source
  • Much more powerful analytic capabilities than relational
  • Facilitates ‘drill-down’ & ‘slice-n-dice’
  • Provides insights for organisations that readily lead to actions & decisions
  • providing opportunities for productivity & revenue gains & cost savings
  • Generally data is fed from a relational data source
  • More used at Senior management level than Admin or clerical level

When to use each …

Relational:         

  • When detailed or transaction-level reporting is required
  • When ‘real time’ reporting is required
  • When OLAP is not available

OLAP:

  • When high-performance, summary or analytical reporting is required
  • When crosstab reporting is required
  • When slice-n-dice or drill-down functionality is required

What should I use? As a rule of thumb. If you are reporting for analysis, then you should use dimensional reporting. Dimensions give us the “who”, the “what”, the “where” and the “when” for powerful analysis. Dimensions can be relational, if you are reporting transactions there will inarguably come from a non-dimensional relational data source.

So, in short, “relational” can be dimensional but “OLAP” is always dimensional.

These two techniques are not exclusive competitors; they are tools and they need to fit the needs of business and its users. Typically, relational modelling is more practical and efficient for transactional reporting but when you need a flexible system to perform powerful analysis, a dimensionally modelled database augments capabilities.