MSSQL query to retrieve DAX formulas from tabular model

Question

First I would like to stress that Im quite new with the Analysis Services, so please forgive me if my question is not adequate. I have received a task to display in Sharepoint list DAX formulas from existing tabular model and Im struggling to obtain this data using ms sql server. Is it possible?

Answer 1

Dynamic Management Views are your friend. :-)

There are DMVs that you can query to return this type of information. The query engine for DMVs is the Data Mining parser. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.

If you are just looking for calculated measures, you can use the following query and then filter out the rows where Expression is null

SELECT
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[MEASUREGROUP_NAME] AS MeasureGroup_Name,
[MEASURE_NAME] AS Measure_Name,
[MEASURE_Caption] AS Measure_Caption,
[MEASURE_IS_VISIBLE] AS Dimension_Visible,
[MEASURE_AGGREGATOR] AS Measure_Aggregator,
[DEFAULT_FORMAT_STRING] AS [Format_String],
[EXPRESSION] AS Calculated_Measure_Expression
FROM
$SYSTEM.MDSCHEMA_MEASURES
ORDER BY
[MEASURE_NAME]

If you are looking for both calculated measures and calculated columns there is another DMV for that:

SELECT DATABASE_NAME, OBJECT_TYPE, [TABLE], [OBJECT], [EXPRESSION] 
FROM $System.DISCOVER_CALC_DEPENDENCY
where OBJECT_TYPE = 'Measure' or OBJECT_TYPE = 'Calc_Column'
order by OBJECT_TYPE

The full list of DMVs for SSAS can be found at https://msdn.microsoft.com/en-us/library/hh230820.aspx