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 created cube and Calculated Measures

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.

I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.

Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));

ms.Update();
cb.Update();
cb.Process();
srv.Dispose();

Thanks
John

Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.

John

AMO created cube and Calculated Measures

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.

I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.

Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));

ms.Update();
cb.Update();
cb.Process();
srv.Dispose();

Thanks
John

Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.

John

Alternative to cursor

Hello,
I would like to find and alternative to using a cursor and updating my
table with the calculated values. The following code snippet is from my
store procedure.
-- Calculate and Update LEWeightedTerm and LEWeightedRR
DECLARE LECursor CURSOR FOR
SELECT TransactionID, Term, BusinessUnitID, LEAlloc, CalcODRBefore
FROM stage_DealTransaction
WHERE COBDateID = @.COBDateID AND LEWeightedTerm is null AND
LEWeightedRR is null
OPEN LECursor
DECLARE @.tranID AS int
DECLARE @.term AS int
DECLARE @.LEAlloc AS float
DECLARE @.BUID AS varchar(15)
DECLARE @.ODR AS Varchar(2)
DECLARE @.LEWT AS float
DECLARE @.LEWRR AS float
DECLARE @.LEAllocSum AS float
DECLARE @.rate AS float
FETCH NEXT FROM LECursor
INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
While (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.LEAllocSum = 1
IF (@.BUID IS NOT NULL)
BEGIN
SELECT @.LEAllocSum = SUM(ISNULL(LEAlloc, 0)) FROM DealTransaction
WHERE BusinessUnitID = @.BUID AND COBDateID = @.COBDateID
END
IF (@.LEAllocSum = 0)
BEGIN
SET @.LEAllocSum = 1
END
IF (@.LEAlloc IS NULL)
BEGIN
SET @.LEAlloc = 0
END
SET @.LEWT = (@.LEAlloc * ISNULL(@.term, 0)) / @.LEAllocSum
SET @.rate = CONVERT(float, ISNULL(@.ODR, '41'))
SET @.LEWRR = (@.LEAlloc * @.rate) / @.LEAllocSum
UPDATE stage_DealTransaction SET LEWeightedTerm = @.LEWT ,
LEWeightedRR = @.LEWRR WHERE TransactionID = @.tranID AND COBDateID =
@.COBDateID
END
FETCH NEXT FROM LECursor INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
END
CLOSE LECursor
DEALLOCATE LECursor
I would like to find a way to optimize it so that the updates can be
done in bulk rather than one record at a time.
Any feedback would be highly appreciated.
Thank you,
Zubinezubine@.gmail.com wrote:
> Hello,
> I would like to find and alternative to using a cursor and updating my
> table with the calculated values. The following code snippet is from
> my store procedure.
>
Could you summarize what you need this to do rather than making us analyze
the cursor code?
It always help to provide DDL and sample data as well. See
www.aspfaq.com/5006 for suggestions
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 18 Nov 2005 07:04:41 -0800, zubine@.gmail.com wrote:

>Hello,
>I would like to find and alternative to using a cursor and updating my
>table with the calculated values. The following code snippet is from my
>store procedure.
Hi Zubine,
Try the following:
UPDATE s
SET LEWeightedTerm = COALESCE (s.LEAlloc * s.Term), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1),
LEWeightedRR = COALESCE (s.LEAlloc *
CAST(COALESCE(s.CalcODRBefore, '41') AS float)), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1)
FROM stage_DealTransaction AS s
LEFT JOIN (SELECT BusinessUnitID,
SUM(LEAlloc) AS SumLEAlloc
FROM DealTransaction
WHERE dt.COBDateID = @.COBDateID
GROUP BY BusinessUnitID) AS dt
ON dt.BusinessUnitID = s.BusinessUnitID
WHERE s.COBDateID = @.COBDateID
AND s.LEWeightedTerm IS NULL
AND s.LEWeightedRR IS NULL
Depending on how your tables and data look, you might be able to replace
the LEFT JOIN with an INNER JOIN, and that might speed up the execution
as well.
Note that the stattement above is untested. See www.aspfaq.com/5006 if
you prefer a tested reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)