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

 

https://docs.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services

 

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)

 

Need Help

6 Replies

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