This project has moved. For the latest updates, please go here.

Selective Querying of $SYSTEM.MDSCHEMA_FUNCTIONS

Mar 12, 2015 at 7:43 AM
I want to select 6 or 7 columns from this table. It works ok except when I try to include the DESCRIPTION column. How can I explicitly reference this column? It looks like there is some conflict with DESCRIPTION as a reserved word.
Mar 12, 2015 at 7:47 AM
Apologies this should refer to $SYSTEM.MDSCHEMA_MEASURES and not Functions!
Coordinator
Mar 12, 2015 at 8:40 AM
You just need to escape it by wrapping it in square brackets

eg.
SELECT Measure_Name,[Description] 
FROM $SYSTEM.MDSCHEMA_MEASURES
Mar 12, 2015 at 9:14 AM

Many thanks.

Mar 16, 2015 at 8:29 AM

I am trying to select only the relevant columns and rows from the Model using the following:

SELECT MEASUREGROUP_NAME, MEASURE_NAME, [DESCRIPTION], EXPRESSION, DATA_TYPE, DEFAULT_FORMAT_STRING, MEASURE_IS_VISIBLE FROM $SYSTEM.MDSCHEMA_MEASURES

WHERE DATA_TYPE > 1

ORDER BY MEASUREGROUP_NAME

The above is in a linked connection to an Excel Worksheet;

Is it possible to modify the syntax to only select where MEASURE_IS_VISIBLE equals True? I have tried a number of syntax expressions … and failed!

This is not a show stopper as I can filter the required data in the Worksheet.

Thanks,

Ted

Coordinator
Mar 16, 2015 at 11:18 AM
Because MEASURE_IS_VISIBLE is already a boolean you can include it in the WHERE clause directly

SELECT MEASUREGROUP_NAME , MEASURE_NAME, [DESCRIPTION], EXPRESSION, DATA_TYPE, DEFAULT_FORMAT_STRING, MEASURE_IS_VISIBLE
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE MEASURE_IS_VISIBLE AND DATA_TYPE > 1
ORDER BY MEASUREGROUP_NAME
Mar 16, 2015 at 3:40 PM

That’s just what I wanted.

Many thanks … I’ve just got very basic SQL knowledge.

Ted.

Coordinator
Mar 16, 2015 at 7:23 PM
Actually.... the DMV queries are DMX, which is very similar to SQL, but has a few differences. I had to experiment and read the documentation online to figure this one out myself.