Using a View for a Fact Table in SSAS Project
I recently was involved in building a custom data warehouse and 2005 SSAS cubes which contained many financial calculations off of the original data. Many of the calculations needed to be at the grain level of the warehouse before aggregations could be applied and therefore it made sense to perform these calculations as Named Calculations in the Data Source View (DSV). After the fields were added to the DSV, the new fields were added to the cube and then MDX calculated members were created in the cube for the final calculations to be consumed by the business users.
However, after many Named Calculations were added to the DSV, the performance of processing the SSAS partitions was becoming very poor and from looking at the SQL that the SSAS project was generating, it was clearly not optimized.
Tags: design