Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve ID of Last Insert (Scope_Identity)

(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.

Session["snCoDeptRowID"] = SqlDataSource1.Select();

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT Scope_Identity"

</asp:SqlDataSource>

try

SelectCommand="SELECT Scope_Identity()"

|||

Thank you for the suggestion. Now I am having trouble with the following line of code which assigns the Scope_Identity() to a session variable. The error msg is: does not recognise the word "command". How can I get the value of the @.CoDeptRowID (Scope_Identity) into my session variable? Thanks

Session["snCoDeptRowID"] = Convert.ToInt32(e.command.parameters("@.CoDeptRowID").value);

|||

Check thate argument has a command object

only if its there in event argument you will be able to use it. And Check the Direction of the @.CoDeptRowId is set to Output in Command as well

|||

Hi,

There are many ways to get the SCOPE_IDENTITY() from stored procedure.

You can use SELECT SCOPE_IDENTITY() or RETURN SCOPE_IDENTITY() or SET @.PARAM = SCOPE_IDENTITY(). All is fine, but the way to get it is different.

SELECT will return the SCOPE_IDENTITY() as the first col and first row of a result set. RETURN will return is as a return parameter. While SET will set the value to a parameter and you have to specify it as an OUTPUT parameter when declaring it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!