Limitations of BI Semantic Tabular Model
As a new craze of BI developers BI Semantic Tabular Model is replacing SSAS multidimensional cube. Tabular Model is highlighted as strong and easy to create Analysis Services Database. Its new and powerful. It is based on PowerPivot approach for creation and providing a more rigid cube.
We can talk about Tabular Model a lot, but I would like to mention few limitations (w.r.t. Multidimensional SSAS Cube) as per my past experience on this Technology:
1. It’s difficult to implement Many-to-Many relationships in Tabular Model. We can create few measures using few DAX expressions with functions like CALCULATE, CALCULATETABLE etc. to get this behaviour.
Note: Measures created with logic of Many-to-Many relationship will consume a lot of memory (RAM) when browsed or called in any report.
2. Calculated Columns consume a lot of memory.
As we know queries on Tabular Model are running on its Vertipaq engine. This engine accommodate all the required data from different tables while running the queries, but still values for calculated column are calculated on fly. So it consumes a lot of Memory while getting values for calculated column. We should avoid Calculated Columns
3. Huge data in the Tabular Model can degrade both processing and performance.
If we are having few security logic and complex DAX expressions for the calculated measures, that can become big obstacle in processing and performance of Tabular Model cube.
4. I faced some issues with losing Tabular Model cube with error saying database is not found after each Service Restart of Tabular Instance.
This issue was prominent after increase of size of Tabular Model data. A possible reason was that when Service Restart it basically loads Tabular Model cubes in Memory and at that point some faults may cause losing of model.
To avoid some pain of 2 and 3 point mentioned above, we should always go with a mart level data warehouse (call it as Data Mart) on top of system’s Data warehouse. It will help us to avoid calculated columns and measures on Tabular Model. We can create directly these calculations in data mart with SQL and thus no extra usage of RAM will be required from Tabular Model Cube.