Hello all,
what difference does it make, if I use an aggregate table instead of a view? As far as I understood this I can do everything with a view (or even more) that I can do with an aggregate table.
Sure I have a table object in the list that I can use as a table and I can create joins and so on. I can do the same with a view though.
Thanks for the clarification.
Johannes,
Assuming data volume is low, performance should be similar and effectively there should be no difference other than how you Index the table.
If you’re building Jet Professional reports off a view, you may struggle as the Data Dump requires a Primary Key on the table to create Record Key (which is why later versions of the JDM standard project use Physical Valid tables instead of views in the Data Warehouse.
Also views versus tables may be more of a consideration if you’re implementing slowly changing dimensions, or have SSAS cubes refreshing frequently during the day; however I suspect for.most use cases (assuming proficiency in SQL and manageable data volumes), it doesn’t matter whether you use an aggregate table or a view.
If you're considering a view for your report that's summarizing data, your users could potentially be waiting quite a while for SQL Server to aggregate the data on the fly. On the other hand, if the table has a fewer number of rows, the group by and aggregate functions in a view may not take very long at all.
Creation of an aggregate table in the Data Warehouse is really a trade-off. It may take a little more time to load that table each time the DWH is refreshed, but in return, the queries will be able to access pre-aggregated data and the reports will refresh more quickly.
I'd suggest running the view with yoru joins, filters, group by's etc. Make a note of the query duration and then try it once again with the aggregate table. If the query time savings is significant (several minutes) then I think you'll have your answer.. especially if this is going to support a report which users use often throughout the day.
As a Data Warehousing best practice, I do typically avoid creating aggregate tables because it results in redundant/duplicate data being stored, but if there's just a ton of data and report performance demands a solution that query optimization and indexing cannot resolve, then an aggregate table may be your best option for making your end-users happy.
In the staging area, an aggregate table would be a better option for pre-aggregating data once and then using those results many times as lookups.
Thank you both for your comments.
In my case, I'm talking about a table in the staging area. So query time shouldn't be a problem.
In the staging area I can use fields from a view in a similar way as lookup fields like with the table. Sure, with a table it's probably smoother and more comfortable, although I think that a view is more flexible (directly using SQL). Apart from that, I still cannot find any advantages.
Thank you for your answers.
