Wednesday, September 20, 2017

SQL Server - Considerations while opting SSAS cube for existing OLTP application

​The following points must be considered while opting for the SSAS cube:
1) Implementing the SSAS cube will change the architecture for data processing, data storage, & reporting.
2) Need to change the database modelling to dimensional modelling.
3) Need to change the database in the de-normalized form.
4) Developing a cube that will involve creation of calculated members for all the statistics defined in the reports/ portal.
5) If all the reports are using data sets written in T-SQL. All the datasets need to be changed to MDX queries.
6) Need to maintain a separate instance of SSAS.
7) Additional ETL for processing the cube.
8) Additional storage for aggregations.

The following recommendations must be considered while using the multi-dimensional cube over tabular modelling:
1) The multi-dimensional model is suggested when the volume of data is high. The tabular model is suggested when we have a smaller volume of data.
2) The multi-dimensional model is suggested when we require complex modelling or have a large dataset.
3) Actions, Writeback, Namedsets features are not available in the tabular model.
4) Tabular is a memory-dependent solution. The more memory, the better the performance. If you do not have enough memory, the tabular model will fail.

No comments:

Post a Comment