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.

Advertisements

Posted on July 4, 2012, in BI Semantic Tabular Model and tagged , , , , . Bookmark the permalink. 1 Comment.

  1. Good to know the limitations from your own experience. Wanted to make one point Tabular is not replacing conventional multidimensional analysis engine, still multidimensional would be the preferred choice for extremely large data sets. Here is the good reference of comparison and when to use what –
    http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: