1

I'm currently working on an existing Tabular Model that has about 1.5M rows. It is driven by SSMS DB views and deploys to SSAS.

My question is which language processes better/faster?

When adding measures I can either create SQL case statements in the view or place the measure directly in the model. I'm just starting this project but the data grows pretty steadily and I'm concerned about long term performance as the model continues to scale up with new fields, measures and records.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
z33Will
  • 39
  • 1
  • 8

1 Answers1

3

I use the following rules of thumb:

  • Calculate all dimensional attributes in SQL. Never use any calculated columns in DAX.
  • Calculate the most complex and expensive metrics in SQL. These are usually very few, like 2-5% of all calculations. Leave the rest to DAX.

In the background, DAX is translated into SQL queries (you can see them using DAX Studio: https://www.sqlbi.com/tools/dax-studio/) . If DAX is written correctly, execution plans will be as efficient as SQL can be. Of course, if your DAX is wrong, it might translate into a very ineffective SQL, but that should be expected.

Finally, just to set expectations correctly: a Tabular model is considered "large" if it exceeds 1 Billion records. Models between 100M - 1B records are considered medium-sized and might need some optimization (basically, you must know what you are doing). Anything less than 100M records is a small model, and if you have performance issues, it's either your data model is wrong, or your DAX is incorrect/ineffective.

RADO
  • 7,733
  • 3
  • 19
  • 33
  • > "In the background, DAX is translated into SQL queries" I guess this is only true for DirectQuery models? In general, I would say models that import data (VertiPaq) have much better performance even in complex DAX expressions, compared to SQL expressions that would do the same thing. – Dan Aug 15 '18 at 07:35
  • @Dan: It's actually true for VertiPaq queries too. DAX is translated into an SQL flavor called xmSQL. For example, this DAX code: ADDCOLUMNS( VALUES(Product[Color]), "Sales", CALCULATE( SUM(Sales[Amount])) will be translated into xmSQL code: SELECT Product[Color], SUM(Sales[Amount]) FROM Sales LEFT OUTER JOIN Product ON Sales[Product_ID] = Product[Product_ID]. It's execution plan might be better or worse than a similar SQL query, depening on some other factors, but usually it's comparable. – RADO Aug 15 '18 at 08:33
  • I know about xmSQL, but arguing that DAX cannot be faster than SQL because DAX queries are translated to xmSQL does not make any sense. xmSQL is a very limited subset of SQL in general, that is used by the AS storage engine. For simple stuff, like aggregating the values in a column, it can actually be thousand of times faster than a T-SQL query in a relational DB. – Dan Aug 15 '18 at 08:35
  • I did not argue that it can't be - sure it can, if you compare xmSQL query to a columnar databse vs SQL query to a relational DB. Sometimes it's faster, sometimes it's slower, depending on the nature of the query. If both query a columnar database though, I don't see any advantages of DAX vs SQL. It's just a semantic layer on the top of SQL. Vertipaq itself might have some advantages over other columar DBs, but that's a different story. – RADO Aug 15 '18 at 08:57
  • VertiPaq has huge advantages over columnar DBs. It's in-memory, it includes relationships (semantics) between tables, etc. It's just that when I read your answer to the question, it looks like you're arguing that DAX is not generally faster than SQL, because DAX gets translated to SQL... and that is certainly not the case. – Dan Aug 15 '18 at 09:24