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