Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How traditional Relational Database differs from ROLAP?

How traditional Relational Database differs from ROLAP?

How traditional Relational Database differs from ROLAP?

OLAP implies the 'multidimensional' of the database, so why appears ROLAP to limit the dimensions and back to relational database?

RE: How traditional Relational Database differs from ROLAP?

On Line Analytical Processing (OLAP) is an evolving discipline, like the rest of IT.
Traditionally, OLAP meant extracting data from wherever it was stored into predefined multi-dimensional structures or hypercubes e.g. a time dimension (years, months, days), a product dimension (range, product, model), geography (country, state, town), measures(cost, revenue, ...), etc. Once in dimensional format, you could then do the slicing and dicing of the pre-calculated values whichever way you wanted. This form of OLAP is now termed Dimensional OLAP (DOLAP).
A problem with DOLAP is data expansion: a few kilobytes of data in relational form can quite easily turn into gigabytes once stored and aggregated to each level of each dimension; this is especially the case if data is sparse, i.e. if few of the many possible combinations of dimension values occur in 'real life', but the empty value in the hypercube still needs to be stored. It can also be very time consuming to load multidimensional databases and then recalculate all the values therein.
This is where Relational OLAP (ROLAP) comes in; this keeps the all-slicing, all-dicing front end concepts, but instead of pre-storing the data values, it uses a set of mappings defined within the tools metadata, to transform data in a relational database into a dimensional form for analysis and display. This technique allows access to much larger volumes of data, but has the drawback of moving the performance hit from load time to run time.
Many of the latest tools on the market try to combine the two approaches. Hybrid OLAP (HOLAP) allows access to data stored in both relational and multi-dimensional formats, and ideally allows the user to swap between the two with the minimum of inconvenience. For example you might want to have a data mart in multidimensional form for analysis of summarised account details, but have the ability to drill through to a relational data warehouse to look at detail of financial transactions for a particular account or time period; with a suitably configured HOLAP system, the user could do this within the same tool by simply clicking on the appropriate link or button.
As with much else, it comes down to horses for courses, one solution does not usually fit all.

RE: How traditional Relational Database differs from ROLAP?

The key fact about multidimensional OLAP tools is that the database engine is proprietary. This means that the only way to get at the data stored in a multidimensional database is through the database vendor's OLAP tool. This makes it difficult to develop ad hoc queries and extracts for data mining.

Relational OLAP (ROLAP) uses an analog of the multidimensional design, called a star schema, to store data in a relational database. In an open database architecture, the data is accessible to other reporting and data extraction tools.

Also, one of the problems with multidimensional databases earlier on was a lack of scalability. This has been alleviated in some products by pre-aggregating the data. Not all products have followed this course, however. Some, among them some of the most popular products, still aggregate on the fly. This puts you in a bind in high volume situations, forcing you to choose between detail and response. The hybrid features of many multidimensional tools carry limitations of their own. For instance, the immediate data source for the multidimensional cube must not change after the last update to the cube. In some cases, this means preserving the input files originally used to build the cube and processing them sequentially in order to report underlying detail. Once again, data volume can push response times into the red zone.

One thing about ROLAP. It is generally slower than its multidimensional cousin, but it is uniformly slow. ROLAP query tools suffer less in high volume applications. The same technique used in multidimensional databases, aggregation, easily alleviates these response problems. In addition, with ROLAP, aggregation is selective. It can be applied where needed in order to break specific bottlenecks. For this reason, ROLAP databases tend to be less voluminous than their pre-aggregated multidimensional counterparts.

RE: How traditional Relational Database differs from ROLAP?

ROLAp uses relation structures to store multidimensional data. Generally, a star or Snowflake schemsa is used to accomplish this.
The fact table is the basis for the star. It contains a key to each of the dimension tables. It also contains the "measures" or numbers associated with a particular data point.
Dimensional tables contain the hierachical structures used to analyze the data. Examples are time Hours, days, months, years. or a GLhierarchiy of accounts, sales, revenue etc.

The values in the measures of the fact table can be "detail" data or summary data depending on the dimensional members to which it is associated.

i.e. case sales in Jan = 10 Feb = 15 and Mar = 12 the fact table record associated case sales Qtr1 = 37.

OLAP On Line Analytic Processing.
Rolap Relational OLAP (uses relational tables to store the data. It can currently hold the most data, but is slower.)

Molap Multi-Dimensional OLAP (uses a propritary data store to store the data. It is also generally the fastest type)

Holap a combination of ROLAP and MOLAP (used to address data size issues)

DOLAP Desktop OLAP MS OLAP uses pivot table services for this other vendors use other methods.

RE: How traditional Relational Database differs from ROLAP?


Nobody seems to have mentioned the fact that ROLAP provides a degree of flexibility that MOLAP lacks.  Maybe I have misunderstood the applications of these technologies, but I was under the impression that one of the core questions to be asked is "How dynamic are my dimensions?".  If they are highly dynamic then ROLAP becomes favourable, if they are static then the scales are tipped in favour of MOLAP.

Ofcourse, there are the major issues as mentioned above that the performance is hindered with ROLAP, as it queries a "relational database".  These problems can also be overcome to some extent, by utilising Aggregation Tables.

I have had to create seperate aggregation tables, that are precalculated at load time, for several ROLAP solutions.  The "jumping" from table to table is hidden from the users by the tools used to view the data.

I hope this helps and is not too far off the mark !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close