Friday, February 24, 2012

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

No comments:

Post a Comment