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.

The solution was to create a database view that had the optimized SQL code containing the granular calculations and then within the DSV, replace the Fact table with the view. The following screen print displays the menu that appears by right clicking on the table to be replaced within the DSV and then navigating to the Replace Table\With Other Table option:

The view also provided a benefit for the SSAS partitions that were created by being able to setup each partition as a SELECT * from VIEW WHERE condition. The SELECT * allowed for more fields to be added to the view without having to go back into each SSAS partition and recreate the SQL that made up the partition for the additional field. Originally, all of the SSAS cube partitions had the non-optimized SQL code as the source and any additions to the FACT table would require going back to each partition and adding the new field to each and every partition created. Considering were had 150 partitions, this was not an enviable position to be going forward.

However, one property within the DSV needed to be changed to ensure that the DSV did not lose all of the relationships that were previously defined when originally using the Fact table itself. The warehouse had FK relationships amongst the Fact table and the many dimensions that joined to the Fact table. When the DSV was originally created, the relationships were read from the data source and automatically created. When replacing the table with a view, the relationships are not pointing to the view within the database and when you hit OK, you see all of your relationships disappear!

The fix for this problem is to change the DSV RetrieveRelationships property to False. The plus, you do not lose your relationships already defined in the DSV. The minus, you must manually create any new relationships that exist going forward, which is an acceptable task considering the many relationships that would have been lost and any future refreshing of the DSV would eliminate the relationships that were recreated.

The following screen print displays the location of the RetrieveRelationships property to update:

The final result of changing the Fact table from the warehouse with Named Calculations to a SQL Database View was being able to update the processing time for each partition from the SSAS generated query that was taking minutes to return to retrieving the same data within seconds by using the SQL Database View. In addition, we were able to improve the maintenance of the partitions for field updates from a painstaking manual update procedure and then a cube process to simply reprocessing the cube when field changes were made.