SSAS Tabular Model Dynamic Management Views and stored procs

1
votes

There are several dynamic management views (DMVs) available for querying metadata in tabular models from SSMS (documentation here https://docs.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=sql-server-2017).

My problem is I need the underlying SQL query that defines the table, in fact I need to create a list of table_name, table_query for my tabular model and I'd like to not have to do it manually as I'm dealing with 100s of table and several models.

I'm aware of the analysis services stored procedures available in external assembly that flatten the XML returned from the DISCOVER_XML_METADATA. The project on github is called ASSP (github link)

My question is, specifically which of these stored procedures would enable me to generate the list I require with a table name and corresponding SQL view (in visual studio editor this is located under Table Properties and I was able to define it when I imported the table). Preferably, I'd like the query as well but it would be okay just to have the underlying data source view name for example:

TabularModel, TabularTable, DatabaseSourceView

Additional details:

Compatibility level : 1103 (SQL Server 2012/2014 SP1)

I don't want to go to the trouble of installing external assemblies if it won't give me the information I'm after but also don't want to have to parse an XML output so this seems to be the best option to generate this list of metadata but I'm not sure if there is a built in stored proc already or if I'd need to do extensive coding.

1
ssasssas-tabular
ASSP only works with Multidimensional models - GregGalloway
Hi Greg. Ah, well that blows my idea right out of the water. Is there any other way I could get this data besides writing some kind of XML parser? - MrL
You export the tabular model to a tabular project then you can shred the XML to get SQL statements out. - Nick.McDermaid
Hi @Nick.McDermaid is there any documentation you could point me to where I might find the schema of the model.bim file? - MrL
PowerQuery (in Excel or Power BI Desktop) is extremely useful for doing XML parsing. If you load the Model.bim as XML in PowerQuery, I bet you can locate your source queries in a matter of minutes, and output exactly the information you need for all tables in the model. - Dan

1 Answers

1
votes

I would use PowerQuery (aka. Get & Transform) in Excel to parse the XML of the Model.bim file, to get the information you need.

  1. Go to the data tab, click "Get Data" > "From File" > "From XML"

  2. Point the file selection dialog to your Model.bim file and click "Edit".

  3. Power Query will sometimes choose to import the file as Text instead of XML, so make sure to change it to XML by clicking on the little cog in the "Source" step and then choose "Xml Tables" in the "Open file as" dropdown: enter image description here
  4. Remove the "Changed type" step.
  5. Navigate down the XML structure by clicking within the cells until you reach the data you need. Along the way, you can expand columns as needed. See this gif: enter image description here

If you didn't catch it from the gif, here's the drill path:

  • ObjectDefinition
    • Database
      • Cubes
        • Cube
          • MeasureGroups
            • MeasureGroup (keep Name and Partitions column)
              • Expand Partitions (keep Name and Source)
                • Expand Source (keep QueryDefinition)

End result should be a list of tables in your Tabular Model, with each partition for each table and the query used within each partition.

If you have multiple models, you can place all the Model.bim files in one folder, and then let Power Query iterate all of them to combine everything in one list.

Good luck!