Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?

Hello,

     Can any body explain me the major difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
     Also use cases for all the above with some examples, when to use what and for what kind of analysis ?

     I have two fact tables , one fact tables stores the voice call event carried out by subscribers and the other fact table stores the
sms event carried out by the subscribers. Both the facts are on the most granular level.

     I am a report developer currently using tableau as my BI tool, so for my dashboard development where i need to show no. of calls per day and no. of sms per day , which one should i refer from the above three ? Also keeping in mind my dashboard performance on getting the data out of the warehouse very quickly.

Also say my client who is a business user wants find no. of calls per day and no. of sms per day then from the above three which one should i be giving him so that he can find the above data.

Thanks,
Suhrid Ghosh

How many rows do you have in each fact table?

Which DBMS are you using?

If I didn't suspect that calls and SMSs might be VERY large tables, I'd say that you didn't need to use any of these techniques. Just make sure that you set the table definitions to use a live connection, so that Tableau will query the database rather than trying to hold all the rows in memory.

I don't think that an aggregate or rollup is going to solve your problem, because I suspect there are a number of other dimensions involved. You've mentioned a Date and a Subscriber, are there other dimensions?

An OLAP cube might be useful if you have a lot of hierarchies in the dimensions, but again, this decision might be influenced by your DBMS.

Thanks for looking into this .

I have more than 50 million rows in each fact table.

I am using HP vertica database.

All fact tables have transactional records i.e event driven.

Yes there are 7 - 8 dimensions relating to the fact , some of them are date dimension , subscriber dimension , rate plan , type of call etc.

Can you give some brief on OLAP cube with some example ?

Also you did not answer my main question i.e
    "Can any body explain me the major difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
     Also use cases for all the above with some examples, when to use what and for what kind of analysis ?"

Rollup and aggregation are synonyms. You rollup or aggregate a transaction fact table to improve performance. An OLAP cube is an implementation of a star in analysis services for example. You create aggregate fact tables when you want to improve performance. OLAP cubes can typically outperform a relational query in a database but they have other restrictions that may perform worse (e.g. distinct counts).

Hello,

Can you explain me with an example implementation of an OLAP cube ?

Thanks,
Suhrid Ghosh

Here is a two good starting points:

https://en.wikipedia.org/wiki/Online_analytical_processing
https://en.wikipedia.org/wiki/Comparison_of_OLAP_Servers

OLAP is more a method than a technology. It basically means the ability to interactively work with and manipulate data. An Excel spreadsheet can be considered an OLAP tool.

The are a vast number of tools that allow you to implement OLAP functionality over an existing database. Some tools have their own internal storage structures that are loaded using extracts from a source database. There are different designations for implementations of OLAP environments. ROLAP is used to indicate an relational database (i.e. one that supports SQL queries) is used as the data store. Vertica would be one such database. MOLAP is used to indicate a multi-dimensional database (MDX is often used to query, some implementations are proprietary) is used as the data store. HOLAP indicates a hybrid environment with a mix of relational and hybrid data stores. A common example is SSAS.

Hello ,

      I have gone through OLAP concepts in DWH , i see all three dimensional structures which represent the cube with data which we can rollup , drill down , slice , dice and pivot. I can easily understand the theory but practically how my data is stored with in the OLAP cube is my confusion. A simple example will be much better. Currently i have data in vertica in facts and dimensions which is a star schema. On top of this facts and dimensions we have derived our aggregates based on business need. Aggregates doesn't contain any kind of keys. Its something similar like the below :

Aggregation of subscriber who have done voice usage

DATE,SUBSCRIBER_NUMBER,NO_OF_CALLS,USAGE(MIN),REVENUE

Thanks,
Suhrid Ghosh