Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Does anyone know how to get a List of all Calculated COLUMNS in a Data Model?
Very easily done if you have a PowerPivot Model using the Power Pivot Utilities - Tool
Dax Studio gets me all the Measures (but I'm trying to either get only the COLUMNS or Both Measures and COLUMNS)
Steps described here or here don't seem to work...
SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY
WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY [EXPRESSION] ASC
This returns a blank table (0 rows)
If I run the following
select * from $SYSTEM.TMSCHEMA_COLUMNS
I get info on all columns, including formulas used in the [Expression] column which is pretty cool.
Perhaps filter to Type = 2?
@Phil_Seamark Thanks!
I wish I could get Table Names instead of Table IDs but I'll take what I can get...
select TableID, ExplicitName, Expression from $SYSTEM.TMSCHEMA_COLUMNS where [Type] = 2 order by TableID
Maybe @MattAllington could get us another tip from Mike Rudzinski about this like he did here about the Measures
http://exceleratorbi.com.au/getting-started-dax-studio/
select MEASUREGROUP_NAME, MEASURE_NAME, EXPRESSION from $SYSTEM.MDSCHEMA_MEASURES where MEASURE_AGGREGATOR = 0 order by MEASUREGROUP_NAME
Also as you see here on @cwebb's blog
https://blog.crossjoin.co.uk/2011/09/17/documenting-dependencies-between-dax-calculations/
and the link he provides
https://msdn.microsoft.com/en-us/library/gg471590(v=sql.110).aspx
select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY
should give us both COLUMNS and Measures - shouldn't it? Or am I missing something?
Top part of picture is from @cwebb's blog and then what I get a table with 0 rows
Thanks!
Does anyone know why this works in DAX Studio when connecting to a PowerPivot Model but it does NOT work with a PBI Model?
SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY
WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY [EXPRESSION] ASC
Perhaps @marcorusso can tell us? Thanks!
Looking around I also found this interesting Idea by @avisingh that is kind of related.
It will not solve the above issue but would be a nice feature to have!
Measure Dependency View (similar to the Query Dependency View)
Thanks!
Have you looked at my new Local Host template? This extrats table data from Power BI Desktop direct into Excel
Looks like the Table names are in here
select * from $SYSTEM.TMSCHEMA_TABLES
I had a look at a model I have and the ID column from that query matches the TableID from the $SYSTEM.TMSCHEMA_COLUMNS DMV
I'm guessing the TM prefix is short for Tabular Model where as DMVs that begin with MD are more for Multidimentional. So wondering if the TM Tables are more relevant.
I just poked around the list of DMV's in the DAX Studio editor, but definitely looks like you can query up some handy info for documentation. I'm speaking at an SQL Saturday next week so may include this in my presso, because I think its pretty cool. 🙂
So just playing around with this a bit more
This seems to give me all calcuated columns including the forumla as well as cool info like the 'sort by column' (leave off the where clause if you want to see/document info about all your columns).
select * from $SYSTEM.TMSCHEMA_COLUMNS where [Type] = 2;
and for calculated measures
select * from $SYSTEM.TMSCHEMA_MEASURES
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |