Derived fact tables, aggregation and views

View previous topic View next topic Go down

Derived fact tables, aggregation and views

Post  remiby on Tue Apr 10, 2012 8:39 am

I have a transaction grain fact table containing all the transactions executed on bank accounts. Now I need a fact table on specific transactions such as withdrawals and deposits.
Requesting the whole base fact table could lead to performance issues. What would you suggest in this case?
* Create a new physical table containing a subset of data from the base fact table? To be filled by the ETL process.
* Create a View on this table.
* Use aggregation (via third party tool)
* You think only one big table with good partitions and indexes is enough.
Thank you for your highlights.


remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Derived fact tables, aggregation and views

Post  ngalemmo on Tue Apr 10, 2012 9:36 am

Unless you are referring to a materialized view, a view does nothing for you.

Everything you listed are viable options. I prefer optimizing the physical structure as the first choice, then consider extending the schema, such as building aggregates, as a secondary option.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Derived fact tables, aggregation and views

Post  remiby on Thu Apr 12, 2012 6:21 am

Yes I meant materialized view. When is it a good practice to use materialized view?

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Derived fact tables, aggregation and views

Post  ngalemmo on Thu Apr 12, 2012 1:45 pm

Best practice is to do it when you have to.

You never 'need' aggregates or table subsets. You create such things if there are performance issues that need to be resolved and you have done what you could tuning the physical structures.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Derived fact tables, aggregation and views

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