Questions and Answers about SSAS Tabular Models #ssas #tabular

I recently delivered the online session Create your first SSAS Tabular Model at 24HOP Pass Summit, which recording is now available here. I received many questions and did not have enough time, so I answer now in this blog post.

How do you prevent a user from aggregating certain measures where the result would be invalid (example: unit margin %)?
In Tabular you do not have the notion of “aggregation”. Every measure evaluates a DAX expression in a particular filter context. Imagine this as a SELECT statement in SQL that consider only the rows filtered by a WHERE condition. You might “remove” the visualization of a value in a measure by using DAX functions such as ISFILTERED, ISCROSSFILTERED and HASONEVALUE. Since you do not have the ability to “intersect” expressions in different dimensions as you can do in MDX using Tool dimensions, you do not have the problem of invalidate certain combinations (such as “Unit” and “Margin %”.

How do you incrementally refresh this new Tabular model that was created?
You can add data to an existing partition, create new partitions or reprocess existing partitions.

Can you connect to SSRS report from Power View to drill down to the actual data rows?
Currently this is not available. In general, you cannot add link to external URLs in the current version Power View.

Can you create those views within the model instead of in the database, like you can with cube creation?
No, in Tabular you do not have the notion of “Data Source View” like the one you have in Multidimensional. By the way, I do not consider a “best practice” embedding queries in a DSV in Multidimensional. If you can create views on the relational database, you simplify troubleshooting in case of data quality issues reported by end users (any DBA can check the views used, even without any knowledge about BI development tools).

Can many to many relationships work in tabular model?
In this version, you cannot create a many-to-many relationship directly in the data model, but you can apply many-to-many relationships in DAX formulas. The good news is that performance are usually faster than equivalent models in Multidimensional.

Any changes for Tabular model between 2012 and 2014 versions of SSAS?
No, there are no changes in Analysis Services between 2012 and 2014. SQL Server 2014 is a release that added new features only to the relational engine of SQL Server.

Can you give a few examples of benefits over using multi-dimensional cubes?
Tabular is easier to use, it is usually faster and it requires no maintenance (Multidimensional requires maintenance of aggregations as data volume grows and data distribution changes). Multidimensional has features not available in Tabular (e.g. custom rollup formulas, MDX Script, dynamic formatting for measures).

How about the role playing dimensions concept can work in Tabular model? Is there an equivalent of role-playing dimensions in Multidimensional in Tabular?
Role-playing dimensions are a usually a bad idea in Multidimensional, because you cannot rename hierarchies, attributes and member names. This result in confusing pivot table when you browse the data, considering that the only difference is the dimension name, which is not directly visible in the pivot table itself.
Tabular does not support role-playing dimensions, but you can overcome that limitation by using DAX, enabling inactive relationships for specific calculations (you can define multiple relationships between the same tables in Tabular). However, if you want to offer navigation in different role-playing dimensions to the user, the best practice for both Multidimensional and Tabular is to import the same table multiple times, renaming data and metadata.

Can you install regular SSAS and the Tabular model on the same server?
Yes, you can install several instances of SSAS on the same server. You run the setup multiple times and choose for each instance whether it has to run as Multidimensional or Tabular. You make this choice during the setup.

Can you use a server based Tabular model for O365 Power BI sheets? (on premise data – SharePoint cloud spreadsheets)
Not yet – at the moment (September 2014) you can only publish a Power Pivot workbook and then refresh it getting on-premise data through the Data Management Gateway.

Can I create a Tabular Model using a query as the source?
Yes, but remember that this is not a best practice. Creating SSAS Tabular or Multidimensional models, the best practice is getting data from SQL views, without modifying the query on SSAS side. In this way, the content of a Tabular table will match the result of a view in SQL. This simplifies the maintenance and the support operation. Any DBA can check the result of a SQL view without having to open an Analysis Services project just to figure out where actual data comes from.

How I can manage access of the data by level (For example: first group have access to 2014 year, but second group have access to all periods)?
Role-based security allows you to create row-level security for each table in the data model. Thus, you can filter the rows of the tables you want to hide to a certain group of user. You define filter conditions using logical DAX expressions that are evaluated when each user creates a connection to SSAS Tabular model.

Does the language M has something to do with the SQL server or it is used only in Excel?
The language “M” is used only by Power Query, which is used only in Excel and in Data Management Gateway today. The “M” language can produce transformations in SQL, but you cannot obtain an “M” version of a SQL query.

What happens if the source data of a Tabular model is not a star schema?
A star schema is the best data model for Tabular. However, you can have more complex data models, but keep in mind that more tables and relationships might cause slower performances at query time.

How do you handle multiple dates in a fact table that you want to attach to a date table?
This is a question similar to the role-dimension one. You can import the Date table multiple times (possibly by renaming columns and content, reflecting the “role” of the dimension in these names), or you can create multiple relationships between Date dimension and Fact table, activating one relationship for each measure through the USERELATIONSHIP function in a CALCULATE statement.