For testing purposes I deployed:
The test was meant to compare the speed of the queries against the Azure SQL DB with those against the Tabular Model.
The Tabular Model in the tests consists of 4 dimensions but only 2 of those dimensions are used in the queries. I suppose queries against a Tabular Model cannot handle more than 2 dimensions ?
The queries are run from a .NET console application running on a local computer. The queries against the Tabular Model use the ADOMD.NET client library and are written in DAX (a language I have no experience with) and come from the design tool in SSMS. The queries against the SQL DB use the ADO.NET client library (containing an aggregate function, 7 inner joins and some "where clause" parameters).
The test consisted of 10 queries for each system with a waiting time of 500ms between each query. The time of each query plus overhead from the console app executing the client library is measured with a System.Diagnostics.Stopwatch. The average duration of the queries for the Tabular Model was twice as long (957,6ms) compared to the SQL SB queries (529,1ms).
I expected the queries to the Tabular Model to be faster because of Analysis Services being optimized for such analytical queries containing aggregates and joins.
Can anyone explain why it doesn't perform better ? Or why one would use Tabular Models as opposed to running SQL queries directly on the relational DB ?
The amount of time the queries will take executing on SQL DB should be roughly the same unless your hand-crafted SQL is particularly poor performing. The time taken by Analysis Services to fit the data coming back from SQL DB to your semantic model is where the extra delay is coming from.
The value of using Direct Query here is that you can provide the user with a semantic model that is more intuitive to them since it is expected the user will not be a DBA. On top of this, the semantic model will in all likelihood include calculations, measures, KPIs, etc.
If you do not need to provide a business focused semantic model, and you are happy doing all calculations and aggregations in the SQL query then you may not need Analysis Services.
Of course the other advantage of using Analysis Service with Direct Query mode off is that you can store data in-memory rather than on disk to improve query performance times. Another major benefit is that you can point the semantic model at multiple data sources so your model can be a centralized source of data for a business user.
Finally, there is no limit to the number of dimensions the Tabular Model can use...
The Tabular Model in the tests consists of 4 dimensions but only 2 of those dimensions are used in the queries. I suppose queries against a Tabular Model cannot handle more than 2 dimensions ?