Get source table names used in Cube

Swathi Garudasu 1 Reputation point
2021-10-06T20:55:41.367+00:00

I am able to get the Measures Definition in Analysis Cube by using this query
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
[MEASURE_IS_VISIBLE],[EXPRESSION] AS Definition,*
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME ='Cube'
ORDER BY [MEASUREGROUP_NAME]

I am able to get the Cube Table List by using this below query'
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION],*
FROM $system.MDSchema_Dimensions
WHERE CUBE_NAME ='Cube'
AND DIMENSION_CAPTION <> 'Measures'
ORDER BY DIMENSION_CAPTION

Can someone help me with the query which will give me source table name/Query for each Cube table? Also how do I join the above two queries?

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
479 questions
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator
    2021-10-07T21:12:24.847+00:00

    Hello @Swathi Garudasu ,
    Thanks for the ask and using Microsoft Q&A platform .

    I am sorry I am not sure , I undertstand that ask , but you can use the below query to get all the tables which store the meta data .

    SELECT Table_name FROM $System.DBSchema_Tables
    WHERE TABLE_TYPE = 'SCHEMA'
    ORDER BY TABLE_NAME ASC

    The table name which get from the above table can be used in the below query

    select * from $System.TMSCHEMA_MODEL

    Please do let me know how it goes .
    Thanks
    Himanshu

    -------------------------------------------------------------------------------------------------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.