![]() you want to slice by Year, Month, Year-Month and Date.The “Date” dimension has only four columns, Year, Month, Year-Month and Date.The measure is related to just one dimension, “Date” dimension.you have just one measure,, from ‘Internet Sales’ table.At the end I capture and store the results in a SQL Server temp table. The first step is to get all measures and their relevant dimensions, then I slice all the measures by all relevant dimensions and get the results. I want to generate and run DAX queries and capture the results. What I’m going to explain is very simple. ![]() SQL Server Analysis Services Tabular 2016 and later (Compatibility Level 1200 and higher).If you have a better idea it would be great to share it with us in the comments section below this post. Perhaps it is not perfect, but, it is a good starting point. I’m sure a similar process can be developed in SQL Server side, but, I leave that part for now. What I’m going to explain in this post is just one of many possible ways to generate and run DAX queries and store the results in SQL Server. While I will not cover the other side, the source or the data warehouse side, it is worth to automate that part too as you can save heaps of times. Straight away, this is going to be a long post, so you can make or take a cup of coffee while enjoying your reading. In this post I’d like to share with you a way to automate the DAX queries to be run on a Tabular model. If your source database is a SQL Server database, then you need to run T-SQL queries on the database side then match the results of both sides to prove the data in Tabular Model is correct. The other way is to run DAX queries on Tabular Model side. But, for how many measures and dimensions you can do the above test in Excel? There are many ways to test the system, you can browse your Tabular Model in Excel, connecting to your Data Warehouse in Excel and create pivot tables then compare the data coming from Tabular Model and the data coming from the Data Warehouse. So your test cases always have two sides, one side is your source database that can be a data warehouse and the other side is the Tabular Model. However it would be still a fairly time-consuming process to run many test cases on Tabular Model, then run similar tests on the data warehouse and compare the results. If you are running a Tabular Model on top of a proper data warehouse then your life would be a bit easier than when you build your semantic model on top of an operational database. In real world SSAS Tabular projects, you need to run many different testing scenarios to prove your customer that the data in Tabular model is correct.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |