This section helps you to determine when to use the Multidimensional model and when to use the Tabular model. Several features can be used to evaluate both of these models. The following list shows the most important factors:
- The size of the dataset (a set of all data sources)
- Writeback support
- Ability to access many different data sources
- Need for complex modeling
- Easy to develop
NOTE One important factor is the use of the programming language. As you already know, you can use MDX exclusively with the Multidimensional model and DAX with the Tabular model. DAX is much easier to use and learn than MDX.
If the size of your dataset is extremely large, use the Multidimensional model. The reason is that the Multidimensional model is designed so that the huge volume of data is stored efficiently. (For this reason, the Multidimensional model is recommended for use of Corporate BI solutions, while the Tabular model should be used for Department and Team BI solutions.)
Among other things, Analysis Services is a tool that can modify the data you are analyzing. This capability is called writeback, and it is the enabling feature behind what-if analysis, forecasting, and financial planning in BI applications that use Analysis Services as the server. Processing writeback is possible only using the Multidimensional model.
NOTE The writeback process writes data back to Analysis Services rather than to the relational database system that provides the raw data. This feature is advantageous because when you write data back to a relational database, you have to wait until the cube is processed before the latest data becomes available. However, when you enable the writeback process, you can submit data into the cube in the current session, making it instantly visible to other users of the Analysis Services database.
If your solution has many external data sources that are different in their nature (tables, data files, etc.), the use of the Tabular model is recommended. The reason is that the Tabular model is based upon the relational data model, and the loading of external data for a relational database system is significantly faster than for multidimensional database systems.
If your solution requires complex modeling, choose the Multidimensional model. As you already know, BI solutions based on the Multidimensional model are designed using the dimensional model, while for the design of the Tabular model solutions, the ER model is used. The former model is easier to use in the case where many dimensions should be modeled. (See also the section “Data Warehouse Design” in Chapter 22.)
Development of BI solutions is easier if you use the Multidimensional model rather than a relational model, which is supported by the Tabular model.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.