hi,

I have a requirement to study a huge tabular data model which has more then 25 tables inside it (like these I have 4 different models). I want to understand each table and get the TSQL query behind each table. I am able to access Dimensions and measure details (DAX formulae) thru DAX Studio but I not able to go into the TSQL scripts\views\stored procedures inside these tabular model. Is there a way thru DAX Studio or SSMS? any help would be great!

thanks in Advance,
Loknath

Hi Loknath,

According to your description, you want to access T-SQL query in DAX Studio/SSMS. Right?

In Analysis Services, since the DAX formula and T-SQL query are processed under different engine, we can't execute -T-SQL query without connecting database engine. It's same thing, you can't parse the DAX expression without connecting tabular model database.

In this scenario, a workaround is connecting the database engine in SSMS, then you can use a linked server connecting to tabular and access the dimensions and measures via DAX formula. Please refer to an article about combing T-SQL and MDX together below:

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

Best Regards,

thank you Simon for your reply! really appreciated.

I think I haven't formulated my question properly. Let me try to explain with example. Lets say, I have a "Employee Tabular model", Where I have a "Salary" table which is populated thru a TSQL statement like "SELECT * FROM EMP_SALARY". Now without opening my "Employee Tabular model" in SSDT\or VS, I would like to know the TSQL associated with "Salary" table thru DAX Studio. I know in DAX Studio there is a way to find out some details of the measures in tabular model like "select * from $SYSTEM.MDSCHEMA_MEASURES". so are there any way I could connect to Tabular model from this tool and get the TSQL query associated with each tables??

Hi Loknath,

DAX studio can only run DAX queries against Tabular model or Powerpivot model. It can't execute the T-SQL query related to those tables.

Regards,

He wants to get the partition source query programmatically

It's not possible using the DMVs to my knowledge.

You'll either need to use the AMO (c# code) to loop through the cube

or manually go to each table in the cube, right click partitions... select each partition and click properties and copy the source query from there.

He wants to get the partition source query programmatically

It's not possible using the DMVs to my knowledge.

You'll either need to use the AMO (c# code) to loop through the cube

or in SSMS manually go to each table in the cube, right click partitions... select each partition and click properties and copy the source query from there.

thanks for your help! I will follow manual process by going into partitions and get my query.