Derived fact tables, aggregation and views

Go down

Derived fact tables, aggregation and views Empty 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

http://www.horus-df.com/

Back to top Go down

Derived fact tables, aggregation and views Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Derived fact tables, aggregation and views Empty 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

http://www.horus-df.com/

Back to top Go down

Derived fact tables, aggregation and views Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Derived fact tables, aggregation and views Empty Re: Derived fact tables, aggregation and views

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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