Using Expression in Analysis Services Tabular Model
In this post, I display using expression module all step in Analysis Services with AdventureWorks2019 database sample. [1]. Expression model in SSAS use in order to solution encounter many to many relationship issues. Sales order detail and product comment table are used for this post. Aim is only demonstrate commented sales detail. Thus, expression module was benefit to solution this problem.
- Problem Definition
This study involve sales order detail table with product review. Firstly, I searched common column to two tables. I used product ID both two tables. But there is many to many relationship type. That relationship type is not best practices. Also, you can take a look this website detail of best practices. [2]. Therefore, in this study, I searched solution this many to many issues in Tabular Model.
2. Prepare Query For Expression
As you see below, I wrote query which is including join. Only commented sales order details called. Thus, right join used for this with Transact- SQL language. Two alias assigned represent for two table. As you know, these tables connected on product ID information.
3. Expression Module in SSAS
This chapter contain query add into expression as you see below pointed. Then, other steps are respectively this shown. Query>New Query>Blank Query. After that, you can add query into DAX function in blank Query.
In order to use Transact -SQL or MYSQL , you can use DAX function as below query. This function has token two parameters. First one is database name you see detail data source chapter on the right visual studio screen. (Upon expression in above screen)
Then go top left Query1(Table name) right click and create new table. Then, click import button top left. This screen will close after click import and save in tabular model.
4. Presentation Layer
Now this presentation layer contain demonstrate SalewithComment table which was created in tabular expression. I used live connection SSAS tabular with Microsoft PowerBI. If you want to get detail about live connection, you can visit my previous post. [3]. This tier has one table and two graphs for expression of data.
Conclusion
In this post, many to many issues reached to solution. Finally, commented sales displayed in Microsoft Power BI. Rate, Sales price and cost values shown in table and statistical graphs. One query table add from expression to tabular model steps. On the other hand, you can update table which was import and schedule deploy as daily. Also you can visit documentation. [4]
