Derived fact tables, aggregation and views
2 posters
Page 1 of 1
Derived fact tables, aggregation and views
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.
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.
Re: Derived fact tables, aggregation and views
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.
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.
Re: Derived fact tables, aggregation and views
Yes I meant materialized view. When is it a good practice to use materialized view?
Re: Derived fact tables, aggregation and views
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.
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.
Similar topics
» Materialized Views vs. Tables
» Aggregation of FACT during Many to Many relationship
» How to create fact table with measures derived from comparing two fact table rows
» Derived Fact table with additional measures / foreign keys ... ?
» Understanding Materialized Views as aggregate tables
» Aggregation of FACT during Many to Many relationship
» How to create fact table with measures derived from comparing two fact table rows
» Derived Fact table with additional measures / foreign keys ... ?
» Understanding Materialized Views as aggregate tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum