Saturday, December 19, 2015

SQL Server Brain Basher of the Week #036 - Analysis Services Dimensions

Here is this week Brain Basher. This is based on Analysis Services Multidimensional model which allows you to create models based on dimension and fact tables. Biggest advantage with multidimensional model is, it creates aggregates (once enabled) based on the hierarchies defined and stores them for improving the performance of data retrieval from the model. The creation of aggregation is done during processing and it extracts data from sources (preferably from the RDW), creates aggregates if it is enabled and then transfers and stores dimension and fact data (row level - based on grain level) including aggregates in the model. Now the question is;

Which dimension type does not calculate aggregates based on the hierarchies defined when the cube is processed?

Calculating aggregates is not applicable for all dimension types. If the dimension is Parent-Child dimension which is a imbalance and changing dimension, the hierarchy structure is calculated at run time, not during processing. This makes the dimension write-enabled, means that records in the dimension table can be updated and updates can be seen in the model without processing the dimension.

Remember, even though this reduces the processing time, it increases the time it takes for retrieving data. If you see this as a disadvantage, you need to handle the dimension as a regular dimension.

No comments: