Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How traditional Relational Database differs from ROLAP?
- Thread starter Semik
- Start date
On Line Analytical Processing (OLAP) is an evolving discipline, like the rest of IT.<br>
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).<br>
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.<br>
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.<br>
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.<br>
As with much else, it comes down to horses for courses, one solution does not usually fit all.
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).<br>
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.<br>
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.<br>
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.<br>
As with much else, it comes down to horses for courses, one solution does not usually fit all.
WHArchitect
MIS
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.
<br>
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.<br>
<br>
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.<br>
<br>
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.
ROLAp uses relation structures to store multidimensional data. Generally, a star or Snowflake schemsa is used to accomplish this.<br>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.<br>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.<br><br>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. <br><br>i.e. case sales in Jan = 10 Feb = 15 and Mar = 12 the fact table record associated case sales Qtr1 = 37.<br><br>Accronyms.<br>OLAP On Line Analytic Processing.<br>Rolap Relational OLAP (uses relational tables to store the data. It can currently hold the most data, but is slower.)<br><br>Molap Multi-Dimensional OLAP (uses a propritary data store to store the data. It is also generally the fastest type)<br><br>Holap a combination of ROLAP and MOLAP (used to address data size issues)<br><br>DOLAP Desktop OLAP MS OLAP uses pivot table services for this other vendors use other methods.<br><br><br>
Hi<br><br>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.<br><br>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.<br><br>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.<br><br>I hope this helps and is not too far off the mark !
- Status
Similar threads
- Locked
- Question