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

View previous topic View next topic Go down

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

Post  suhridghosh.01 on Tue Feb 09, 2016 5:04 am

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

suhridghosh.01

Posts : 9
Join date : 2015-12-31
Age : 29
Location : Bangalore India

View user profile

Back to top Go down

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

Post  ron.dunn on Tue Feb 09, 2016 6:50 am

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.


ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

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

Post  suhridghosh.01 on Tue Feb 09, 2016 7:00 am

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 ?"

suhridghosh.01

Posts : 9
Join date : 2015-12-31
Age : 29
Location : Bangalore India

View user profile

Back to top Go down

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

Post  BoxesAndLines on Tue Feb 09, 2016 12:08 pm

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).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

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

Post  suhridghosh.01 on Tue Feb 09, 2016 11:18 pm

Hello,

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

Thanks,
Suhrid Ghosh

suhridghosh.01

Posts : 9
Join date : 2015-12-31
Age : 29
Location : Bangalore India

View user profile

Back to top Go down

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

Post  ron.dunn on Tue Feb 09, 2016 11:31 pm

Here is a two good starting points:

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

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

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

Post  ngalemmo on Wed Feb 10, 2016 2:34 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  suhridghosh.01 on Wed Feb 10, 2016 7:45 am

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

suhridghosh.01

Posts : 9
Join date : 2015-12-31
Age : 29
Location : Bangalore India

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum