How to access KPI Value hidden measures via ADOMD.NET?

Question

I'm trying to access the KPI metadata via ADOMD.NET (using MDSCHEMA rowsets) with SQL Server/SSAS 2008. When I get a given KPI VALUE field, for example in Adventure Works, it returns what I believe is the hidden measure associated with the KPI (e.g., [Measures].[Net Income Value]). But the Value has areal MDX expression associated with it.

Unfortunately I can't find this measure anywhere in the cube. I've tried getting back the list of measures (MDSCHEMA_MEASURES) that are not visible (restriction used is MEASURE_VISIBILITY=2) via ADOMD, but I still don't get back any of the KPI hidden measures.

So how do I get those hidden KPI measures so that I can get the actual MDX expressions backing the KPI Value/Trend/Goal/etc...? If you take a look at the Adventure Works cube you can see that most of the KPI Values have MDX expressions, but I can't seem to retrieve them.

Answer 1

You can't get at the definition of the KPIs through any of the Schema rowsets. You would need to use a DISCOVER_XML_METADATA call and navigate through the returned XML.

Answer 2

You can indeed get the KPIs using DISCOVER_XML_METADATA but the returned data will be huge since the KPIs are just a subset of the data returned by DISCOVER_XML_METADATA.

You can use the MDSCHEMA_KPIS rowset which will return KPIs only.

Hope this helps.