This project has moved and is read-only. For the latest updates, please go here.

Calculated member from SSAS stored procedure

Topics: Developer Forum, Project Management Forum, User Forum
Sep 11, 2012 at 5:21 PM

Hello,

I am trying to create a calculated member from analysis services stored procedure.

The below statement returns me the expected value without any error.

CALL ASSP.DiscoverXmlMetadataFull("\Server\Name")

But when this is part of MDX then there is an error saying

[Internal error: An unexpected exception occurred.]

MDX Query that generates error:

WITH

   MEMBER [Measures].[CubeServerName] AS

   ASSP.DiscoverXmlMetadataFull("\Server\Name")

SELECT

   [CubeServerName] on COLUMNS

   FROM [Adventure Works]

Any suggestions on this to make it workable will be helpful.

Regards,

Palash

<input id="93e7d13b-a234-4499-a5c2-f7216615b426_attachments" type="hidden" />
Sep 11, 2012 at 6:50 PM

CALL functions can't be executed from a calculated member.

You'll want to use the DiscoverSingleValue type functions which parse out a single scalar value from the discover results:
http://asstoredprocedures.codeplex.com/wikipage?title=DiscoverSingleValue&referringTitle=XmlaDiscover

Sep 12, 2012 at 8:13 AM

Thank You Furmangg! Below statement returned me the result as expected.

ASSP.discoverXmlMetaDataFullSingleValue("Name", "\Server")

Can we get the information of cube data source by the ASSP. For example I would like to know the server name of the SQL database that cube is pointing to and the SQL database name as the calculated member.

Regards,

Palash

Sep 12, 2012 at 4:14 PM

This will get you the connection string for the first data source. Then I suppose you could parse out the server name if you really wanted to:

Be sure to check the impersonation settings on ASSP depending on what user is running the MDX queries. Typical users won't be able to run these discover commands unless you set the impersonation settings to the service account or a higher permission user.

ASSP.discoverXmlMetaDataSingleValue("ConnectionString", "\Database\DataSources\DataSource")

Sep 12, 2012 at 5:13 PM

Thank You!