AMO: Enumerate Calculated Members?

Hi,

Apologies if I'm missing something obvious here. I'm trying to enumerate the calculated members in a cube using AMO (as part of programmatically building perspectives), however I can't see an easy way to do this.

The collection of CalculationProperty objects on the cube's MdxScript object(s) would seem perfect (and indeed works for Adventure Works), but for my own hand-built cubes this collection is empty; CalculationProperty settings don't seem to be exposed anywhere in the BIDS UI so I assume they can only be set programmatically, which is of no particular use to me in the general case when operating on potentially manually-produced cubes.

Aside from using CalculationProperty the only way I can see to do this within AMO would be to parse the Command text associated with the MdxScript, but this is a heinous thing to have to do (by which I mean I'm surprised if this is required, rather than it being technically difficult). There are various ways I could get the information I need outside AMO, for example through ADOMDClient/ADOMDServer or firing off some MDX behind the scenes, but again I'd rather not unless necessary - surely it must be possible purely within AMO? How is BIDS doing it on the perspectives tab?

Many thanks for any suggestions,
Jon

Hi Jon,

I think I can answer half your question.

The calculationProperty are exposed in BIDS throught the calculation tab. There is a little properties button - 12th button from the left, just to the left of the script view button. This allows you to add/set all the CalculationProperty settings for all the calculated measures in your script.

I don't know how BIDS figures out what the calculated measures are. I can't see anything that might help in AMO.

|||Hi Darren,

Thanks for the helpful reply - it's funny, I've been switching back and forward using the Form View and Script View buttons in BIDS since the pre-beta days, and yet never noticed that Calculation Properties button right next to them :)

This does indeed partially solve my problem since at least I now have a non-hacky way of setting calculation properties for my own manually-built cubes (and hence can make use of CalculationProperty objects in AMO whenever I can mandate that calculation properties must be set in BIDS). However it still seems rather unsatisfactory that I have to require this surplus metadata to be added to the cube when all I want within AMO is to be able to get at calculated member names ... is this really the best I can do (if so then I still may resort to parsing the script command text to extract the names, rather than rely on metadata that cannot be guaranteed to be present)?

Thanks,
Jon|||Unfortunately, you do in fact need to parse through the MdxScript to enumerate calculcations from AMO. I agree that it's unfortunate. Fortunately, it's not too difficult - the script is nicely delimited.|||

You can parse the script with a Regular Expression too. The following seems to work reasonably well (although I am no RegEx expert so it could probably be improved upon)

(\bCREATE\s*MEMBER\s*.*\[?measures\]?[\n\r]*\.[\n\r]*\[?(?<CalcMeasure>[^\]]*)\]?\s*as\b|(?:\bCREATE\s*(\[(?<CalcMeasure>.*)\]|(?<CalcMeasure>[^\s]*))\s*\=))

I setup a sample project with some NUnit tests to check it was working properly here http://projectdistributor.net/Files/73/227/381_Source.zip

This expression works with both the "old" and "new" syntax for calculated measures.

I have an explanation of what the regex is doing that I plan to post on my blog, but my provider is having issues at the moment.

Cheers

Darren

|||Hi guys,

Kevin, thanks for confirming what I feared might be the case. Darren, thanks for the regex - I'd decided do go with a regex parse myself but hadn't actually got round to doing it yet, so it looks like you've saved me the effort.

At least the rest of AMO is pretty sensible from what I've seen ... don't get me started on the SSIS API though

Cheers,
Jon|||No worries - it was an interesting puzzle for my lunch break |||Some people go to the pub during their lunch break ... others write regexes

I'm glad you did though!

Jon|||

turns out there is an API for parsing the calc script. It's not "officially supported" but works great. See mention of Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c) at...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324224&SiteID=1

You might also vote for that connect issue mentioned at the end of the thread.

AMO: Enumerate Calculated Members?

Hi,

Apologies if I'm missing something obvious here. I'm trying to enumerate the calculated members in a cube using AMO (as part of programmatically building perspectives), however I can't see an easy way to do this.

The collection of CalculationProperty objects on the cube's MdxScript object(s) would seem perfect (and indeed works for Adventure Works), but for my own hand-built cubes this collection is empty; CalculationProperty settings don't seem to be exposed anywhere in the BIDS UI so I assume they can only be set programmatically, which is of no particular use to me in the general case when operating on potentially manually-produced cubes.

Aside from using CalculationProperty the only way I can see to do this within AMO would be to parse the Command text associated with the MdxScript, but this is a heinous thing to have to do (by which I mean I'm surprised if this is required, rather than it being technically difficult). There are various ways I could get the information I need outside AMO, for example through ADOMDClient/ADOMDServer or firing off some MDX behind the scenes, but again I'd rather not unless necessary - surely it must be possible purely within AMO? How is BIDS doing it on the perspectives tab?

Many thanks for any suggestions,
Jon

Hi Jon,

I think I can answer half your question.

The calculationProperty are exposed in BIDS throught the calculation tab. There is a little properties button - 12th button from the left, just to the left of the script view button. This allows you to add/set all the CalculationProperty settings for all the calculated measures in your script.

I don't know how BIDS figures out what the calculated measures are. I can't see anything that might help in AMO.

|||Hi Darren,

Thanks for the helpful reply - it's funny, I've been switching back and forward using the Form View and Script View buttons in BIDS since the pre-beta days, and yet never noticed that Calculation Properties button right next to them :)

This does indeed partially solve my problem since at least I now have a non-hacky way of setting calculation properties for my own manually-built cubes (and hence can make use of CalculationProperty objects in AMO whenever I can mandate that calculation properties must be set in BIDS). However it still seems rather unsatisfactory that I have to require this surplus metadata to be added to the cube when all I want within AMO is to be able to get at calculated member names ... is this really the best I can do (if so then I still may resort to parsing the script command text to extract the names, rather than rely on metadata that cannot be guaranteed to be present)?

Thanks,
Jon|||Unfortunately, you do in fact need to parse through the MdxScript to enumerate calculcations from AMO. I agree that it's unfortunate. Fortunately, it's not too difficult - the script is nicely delimited.|||

You can parse the script with a Regular Expression too. The following seems to work reasonably well (although I am no RegEx expert so it could probably be improved upon)

(\bCREATE\s*MEMBER\s*.*\[?measures\]?[\n\r]*\.[\n\r]*\[?(?<CalcMeasure>[^\]]*)\]?\s*as\b|(?:\bCREATE\s*(\[(?<CalcMeasure>.*)\]|(?<CalcMeasure>[^\s]*))\s*\=))

I setup a sample project with some NUnit tests to check it was working properly here http://projectdistributor.net/Files/73/227/381_Source.zip

This expression works with both the "old" and "new" syntax for calculated measures.

I have an explanation of what the regex is doing that I plan to post on my blog, but my provider is having issues at the moment.

Cheers

Darren

|||Hi guys,

Kevin, thanks for confirming what I feared might be the case. Darren, thanks for the regex - I'd decided do go with a regex parse myself but hadn't actually got round to doing it yet, so it looks like you've saved me the effort.

At least the rest of AMO is pretty sensible from what I've seen ... don't get me started on the SSIS API though

Cheers,
Jon|||No worries - it was an interesting puzzle for my lunch break |||Some people go to the pub during their lunch break ... others write regexes

I'm glad you did though!

Jon|||

turns out there is an API for parsing the calc script. It's not "officially supported" but works great. See mention of Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c) at...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324224&SiteID=1

You might also vote for that connect issue mentioned at the end of the thread.

AMO with VB.Net

Hi All,

I am working on a project that require me to build cubes programmatically i.e., using AMO object and VB.Net. I am trying to find a sample that builds cubes using VB.Net.

I can add further features to it based on the sample.

Can any one help me at their earliest?

Thanks

Sekhar PC

Check the following two links:

http://www.asptoday.com/Content.aspx?id=2351

and

http://sorosjing.cnblogs.com/archive/2005/12/08/292848.html

Have fun.

Yones

|||

Hi,

Thank you for your reply.

When I tried to create a cube by exactly following the sample, always it threw an error "Could not deploy the cube".

Is there a better sample that help to create cube using AMO and dotnet? A sample that get deployed though the source is AdventureWorks only.

Thanks

SekharPC

|||

Thanks for the reply.

When I tried to implement the steps given in the above listed website, everytime I got an error message saying "Could not create cubes". Then I copied and pasted and tried to create cubes using AdventureWorks database. Still the error continued.

Can a better sample be found which I can use to create my cubes dynamically?

An early reply will be of great help to me.

Thanks

SekharPC

|||

There is great C# sample in http://msdn2.microsoft.com/en-us/library/ms345091.aspx

it was very useful for me.

Mohsen

AMO with VB.Net

Hi All,

I am working on a project that require me to build cubes programmatically i.e., using AMO object and VB.Net. I am trying to find a sample that builds cubes using VB.Net.

I can add further features to it based on the sample.

Can any one help me at their earliest?

Thanks

Sekhar PC

Check the following two links:

http://www.asptoday.com/Content.aspx?id=2351

and

http://sorosjing.cnblogs.com/archive/2005/12/08/292848.html

Have fun.

Yones

|||

Hi,

Thank you for your reply.

When I tried to create a cube by exactly following the sample, always it threw an error "Could not deploy the cube".

Is there a better sample that help to create cube using AMO and dotnet? A sample that get deployed though the source is AdventureWorks only.

Thanks

SekharPC

|||

Thanks for the reply.

When I tried to implement the steps given in the above listed website, everytime I got an error message saying "Could not create cubes". Then I copied and pasted and tried to create cubes using AdventureWorks database. Still the error continued.

Can a better sample be found which I can use to create my cubes dynamically?

An early reply will be of great help to me.

Thanks

SekharPC

|||

There is great C# sample in http://msdn2.microsoft.com/en-us/library/ms345091.aspx

it was very useful for me.

Mohsen

AMO with VB.Net

Hi All,

I am working on a project that require me to build cubes programmatically i.e., using AMO object and VB.Net. I am trying to find a sample that builds cubes using VB.Net.

I can add further features to it based on the sample.

Can any one help me at their earliest?

Thanks

Sekhar PC

Check the following two links:

http://www.asptoday.com/Content.aspx?id=2351

and

http://sorosjing.cnblogs.com/archive/2005/12/08/292848.html

Have fun.

Yones

|||

Hi,

Thank you for your reply.

When I tried to create a cube by exactly following the sample, always it threw an error "Could not deploy the cube".

Is there a better sample that help to create cube using AMO and dotnet? A sample that get deployed though the source is AdventureWorks only.

Thanks

SekharPC

|||

Thanks for the reply.

When I tried to implement the steps given in the above listed website, everytime I got an error message saying "Could not create cubes". Then I copied and pasted and tried to create cubes using AdventureWorks database. Still the error continued.

Can a better sample be found which I can use to create my cubes dynamically?

An early reply will be of great help to me.

Thanks

SekharPC

|||

There is great C# sample in http://msdn2.microsoft.com/en-us/library/ms345091.aspx

it was very useful for me.

Mohsen

AMO with VB.Net

Hi All,

I am working on a project that require me to build cubes programmatically i.e., using AMO object and VB.Net. I am trying to find a sample that builds cubes using VB.Net.

I can add further features to it based on the sample.

Can any one help me at their earliest?

Thanks

Sekhar PC

Check the following two links:

http://www.asptoday.com/Content.aspx?id=2351

and

http://sorosjing.cnblogs.com/archive/2005/12/08/292848.html

Have fun.

Yones

|||

Hi,

Thank you for your reply.

When I tried to create a cube by exactly following the sample, always it threw an error "Could not deploy the cube".

Is there a better sample that help to create cube using AMO and dotnet? A sample that get deployed though the source is AdventureWorks only.

Thanks

SekharPC

|||

Thanks for the reply.

When I tried to implement the steps given in the above listed website, everytime I got an error message saying "Could not create cubes". Then I copied and pasted and tried to create cubes using AdventureWorks database. Still the error continued.

Can a better sample be found which I can use to create my cubes dynamically?

An early reply will be of great help to me.

Thanks

SekharPC

|||

There is great C# sample in http://msdn2.microsoft.com/en-us/library/ms345091.aspx

it was very useful for me.

Mohsen

AMO vs. ADOMD

I use AMO to programmatically create a complete database with datasource, datasourceview, dimensions, and cubes. I also have a webservice that uses ADOMD.NET to retrieve metadata about those objects (for security purposes and also for .NET 1.1 applications). However, I notice that using ADOMD's GetSchemaDataSet only return certain metadata. For example, for a dimension, it doesn't return the DimensionID or other properties I am able to set with AMO. I want to use ADOMD.NET because it returns the metadata in XML format already. With AMO, I have to serialize the dimension object. Is there any other way to retrieve additional metadata for AMO objects?

You can use "DISCOVER_XML_METADATA" with GetSchemaDataSet (this is the discover type AMO is using to get all the metadata for objects).

<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList>
<DatabaseID>DataWarehouse Project1</DatabaseID>
<DataSourceViewID>Budget 1</DataSourceViewID>
<ObjectExpansion>ObjectProperties</ObjectExpansion> // can also be ExpandFull, ExpandObject or ReferenceOnly
</RestrictionList>
</Restrictions>

Adrian Dumitrascu

|||Thanks Adrian!! That's exactly what I am looking for. I assume I can just deserialize the xml to get the AMO object back. My only concern is that AMO objects are pretty powerful and it's possible for the client to modify the cube structure. I may create my own readonly wrapper classes for each AMO object or inherit the AMO objects and override the update/process methods.|||

> I assume I can just deserialize the xml to get the AMO object back.

Yes, there is the Utils class with Serialize and Deserialize methods for this.

> I may create my own readonly wrapper classes for each AMO object or inherit the AMO objects and override the update/process methods.

You will need to create wrapper classes because most of the AMO classes are sealed. But what kind of additions/changes you want for the Update and Process ? Maybe they have some flags/parameters that would help.

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

You will need to create wrapper classes because most of the AMO classes are sealed. But what kind of additions/changes you want for the Update and Process ? Maybe they have some flags/parameters that would help.

Here's my scenario. I am deploying a click-once application to the end-user that allows them to query the cube by choosing a measure and 2 dimensions. Then they can save their configurations back to the server so next time they can open the configuration and view/edit them. I have a webservice that the click-once app uses to retrieve metadata about the cube. I was going to have the click-once app reference Microsoft.AnalysisServices dll so I can easily deserialize the XML to actual AMO objects I can use. However, since the click-once shouldn't need a live connection to the Analysis Server or make any changes to it, I don't really want to use AMO objects at all. Kind of an overkill. I was going to just create some simple classes that will parse the XML for the properties I want. The bad part is that I will have to create a class for all the AMO objects I want to use...not hard at all but just tedious.