SSAS - should I use views or underlying tables?
I have a set of views set up in SQL Server which output exactly the results that I would like to include in a SQL Server Analysis Services cube, including the calculation of a number of dimensions (such as Age using DATEDIFF, business quarter using DATENAME etc.). What I would like to know is whether it makes sense to use these views as the data source for a cube, or whether I should use the underlying tables to reproduce the logic in SSAS. What are the implications of going either route?
My concerns are:
- the datasets are massive, but we need quick access to the results, so I would like to have as much of the calculations that are done in the views persisted within the SSAS data warehouse
- Again, because the datasets are massive I want the recalculation of any cubes to be a fast as possible
Answer
Many experts actually recommend using views in your data source view in SSAS. John Welch (Pragmatic Works, Microsoft MVP, Analysis Services Maestro) spoke on how he preferred using views in the DSV this year at SQL Rally Dallas. The reason being is that it creates a layer between the cube and the physical table.
Calculating columns in the view will take a little extra time and resources during cube processing. If processing time is ok, leave the computations in the view. If it's an issue, you can always add a persisted computed column directly to the fact table so that the calculation is done during the insert / update of the fact table. The disadvantage of this is that you'll have to physically store the columns in the fact table. The advantage is that they don't have to be computed every time the cube gets processed. These are the tradeoffs that you'll need to weigh to decide which way to go.
Just make sure you tune the source queries to be as efficient as possible. Views are fine for DSVs.