Forum Discussion

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

List ALL Calculated COLUMNS in a Data Model

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 th...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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
  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Phil_Seamark  Thanks! :smileyhappy:

    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! :smileyhappy:

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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.  :)

       

       

       

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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? :smileyhappy:  Thanks!

      https://docs.microsoft.com/en-us/sql/analysis-services/schema-rowsets/xml/discover-calc-dependency-rowset

       

      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!  :smileyhappy: