View Measure definition (DAX) in excel

Question

I am fairly new to DAX and to SSAS-Tabular, so I hope that you'll forgive any ignorance.
We have an SSAS-Tabular cube and we're using it in Excel to see the data (pivot table). Is there a way for me to view the DAX behind a measure in Excel?

Thanks,

Eli

tysonwright tysonwright · Accepted Answer · 2018-01-04T01:25:21

In Excel, go to the 'Data' tab, click on 'New Query', select 'from Database', select 'from SQL Server Analysis Services database'.
Put in the name of the server, and the name of the database (name of the cube).
Click the dropdown next to "MDX or DAX query (optional)".
Add the following query (note that this is DMX, not MDX or DAX, but it will work - note that if you don't edit the catalog name to match your cube, it won't return any data):

SELECT 
    [MEASUREGROUP_NAME] AS  [Table Name],
    [MEASURE_CAPTION]   AS  [Measure Name],
    [DESCRIPTION]       AS  [Measure Description],
    [EXPRESSION]        AS  [Measure Logic]
FROM 
    $SYSTEM.MDSCHEMA_MEASURES
WHERE 
    [CUBE_NAME]  ='Model'
AND
    [MEASURE_IS_VISIBLE]
AND
    [CATALOG_NAME] = '<enter name of your cube here>'
ORDER BY 
    [MEASUREGROUP_NAME]

Click "Load".
You now have a page in the spreadsheet that functions as your data dictionary for measures. You can do the same thing, adding queries for dimenions with this code:

SELECT 
    [DIMENSION_UNIQUE_NAME] AS  [Table Name],
    HIERARCHY_CAPTION       AS  [Column Name],
    [DESCRIPTION]           AS  [Column Description]
FROM 
    $system.MDSchema_hierarchies
WHERE 
    [CUBE_NAME]  = 'Model'
AND 
    [HIERARCHY_ORIGIN] = 2
AND 
    [HIERARCHY_IS_VISIBLE]
AND
    [CATALOG_NAME] = '<enter name of your cube here>'
ORDER BY 
    [DIMENSION_UNIQUE_NAME]

And for tables, this code:

SELECT 
    [DIMENSION_CAPTION] AS  [Table Name],
    [DESCRIPTION]       AS  [Table Description]
FROM 
    $system.MDSchema_Dimensions
WHERE 
    [CUBE_NAME]  ='Model'
AND 
    [DIMENSION_CAPTION] <> 'Measures'
AND
    [CATALOG_NAME] = '<enter name of your cube here>'
ORDER BY 
    [DIMENSION_CAPTION]

And for hierarchies, this code:

SELECT 
    [DIMENSION_UNIQUE_NAME] AS  [Table Name],
    [HIERARCHY_CAPTION]     AS  [Hierarchy Name],
    [DESCRIPTION]           AS  [Hierarchy Description]
FROM 
    $system.MDSchema_hierarchies
WHERE 
    [CUBE_NAME]  = 'Model'
AND 
    [HIERARCHY_ORIGIN] = 1
AND
    [CATALOG_NAME] = '<enter name of your cube here>'
ORDER BY 
    [DIMENSION_UNIQUE_NAME]

If you edit the properties for these queries (use the 'Connections' button on the data tab), you can set this sheet to refresh every time the worksheet is opened (similar to how you probably have your pivot table connections set up), and now you have data dictionary tabs that automatically reflect the most current cube design. Hope this helps!

View Measure definition (DAX) in excel

1 Answers