Can I expose tabular metadata on a report page?

[JenM]

My client works in a strict DEV/TST/PROD world. We're connecting to an SSAS tabular model (1200). The process of migrating/promoting our reports and tabular models from one environment to the next is very manual/painful. It would help us troubleshoot if we could expose on our About/Help report page as much data lineage information as possible. Ideally that would include:

1. What server is this PBIX pointing to?
2. What tabular model on that server is this PBIX pointing to?
3. What are the (SQL) data source(s) to which the tabular model points (server & DB)?

Re #s 1 & 2, it's not enough to hard-code self-identifying DAX measures into the tabular model, because those would have to be manually updated, and would themselves be suspect. What I want is access to what I typed in to this Data Source Settings dialog in the Desktop client.

[Anonymous]

Not sure if it's what you're after but Tabular Models have metadata about their contents available in TMSCHEMA views. I've used it to create automated documentation over my tabular models. I originally came accross the idea here:

https://datasavvy.me/2016/10/04/documenting-your-tabular-or-power-bi-model/

[dearwatson]

The only way I can think this might be possible is to parameterise each element in the query then expose those parameters to the report.

e.g for server you create a query parameter for server then create a blank query that parameter as its source and convert it to a 1 row table

[JenM]

Thanks, but I'm afraid this approach doesn't take into account that I'm using SSAS tabular instead of the built-in power query.

The selection/identification of which tabular model (and DB) I'm pointing to is metadata of the PBIX file itself, not the query (or in my case, the model).

[dearwatson]

You're welcome,

Sorry I can't think of another approach, SSAS Tabular connections can use parameters which I thought was a way to dynamically maintain the pbix metadata but it looks like it only works on imported models... since with SSAS you can't query live data and non-live data (e.g. unzip the pbix file somehow and read metadata) perhaps the only soplution is to maintain the connection details somehow in the Tabular model.

[JenM]

Thanks, I'll poke around further and see if I can gain access to the connection details in the tabular model.

[r_medina]

I used this M query - hope that it helps...

-Rene Medina

let
SSASCube = AnalysisServices.Database("Your server", "Your cube", [Query="

SELECT [PropertyName], [Value]
FROM $System.DISCOVER_PROPERTIES
WHERE PropertyName = 'Catalog'
OR PropertyName = 'ServerName'
ORDER BY PropertyName DESC
"])
in
SSASCube