Showing posts with label amo. Show all posts
Showing posts with label amo. Show all posts

Tuesday, March 27, 2012

Calculated Measures using AMO

Hi,

Stuck again......

I am creating Dimensions, Cubes, MeasureGroups, Measures, Partitions using AMO dynamically. What I need now is to create Calculated Measures using AMO, which would use some aggregation between one or two measures of the cube.

I did not find any object that would enable me to work with Calculated Measures in AMO. It would be really helpful if I can get some code snippets on this.

Thanks and Regards

Vijay R

MdxScripts is what you're looking for. Here's a short discussion on the topic which I think will help.

Multiple MdxScripts and Commands in Cubes

A cube can contain multiple MdxScipts, however only one is active at a given time.Which script is active can be controlled by setting the DefaultScript>false</DefaultScript> XMLA tag.(The default value of <DefaultScript> is true so omitting this tag is equivalent to setting it to true.However, since only one script can be the default, the first script with <DefaultScript> omitted or explicitly set to true wins and all other scripts considered non-default regardless of their setting.This means that the first script encountered without <DefaultScript>false</DefaultScript> is the real default script.)The UI will only work with the active script.

Each MdxScript can contain multiple Command objects, all of which are active (provided the script is active).Each Command object can contain an arbitrary number of calculation statements within its text body.Normally a cube will only contain one MdxScript with one Command which contains all the calculations.If you have multiple commands in the active script, the UI will merge them into one when they are displayed and if you make any changes in the UI, they will be saved as one command.(The value of having multiple commands is that it is possible for some to be completely unparsable and yet the others still work.This is useful in migration since it preserves this capability.However, for new cubes it is recommended that your calculations should at least parse, and the UI will help ensure this, so multiple commands is of little value.)

The CalculationProperties collection contains special XMLA properties of calculations contained in Command objects. Such properties are associated with script elements by a CalculationReference which matches the name used in the creation of a set, calculated member, or calc cell in the script.CalculationProperties are generally just used for Display Folder, Associated Measure Group, and Translation as these three properties cannot be set in the script itself. (In the UI you can see Display Folder and Associated Measure Group calculation properties in the Calculation Properties dialog which can be launched from the Calculations tab of the cube editor.)

|||

HI,

Thanks a lot, that gave some insight, and helped me investigate further.

This leads to the obvious question, What MDX should I write in the command, so that it would create a calculated measure, with some formula. meaning what would be the general syntax of the MDX that we would have to write.

I created a calculated measure from the BI studio and then used AMO to get the cube object, I then browsed the cube object: Cube > MDXScripts > Commands

Command Text was: string CmdTxt =
CREATE MEMBER CURRENTCUBE.[MEASURES].CalTestMeasure
AS [Measures].[M1] * 5,
FORE_COLOR = 6776628 /*R=52, G=103, B=103*/,
VISIBLE = 1;

Now I suppose this code must do the same programmatically?

AMO.MdxScript MdxScript = NewCube.MdxScripts.Add("test", "test");
AMO.Command comd = new Microsoft.AnalysisServices.Command(CmdTxt);

MdxScript.Commands.Add(comd);

I will try this with some more modifications, but this is surely going to create a problem in the future. Now imagine I want to modify the calculated measures, their calculations programmatically ..etc, What happenes is that all the calculated measures are in one huge string of MDX!!!!!!!, How do i work on it? string manipulation? that makes it very very error prone.

so we have no other way to do this? other than using MDX scripts?

|||

This is the old script versus object model dilema. A script is generally easier for people to read and maintain while an object model is easier to write code against. When you use the UI, all your calculations are placed in a single Command object so that it can be presented as one nice, editable script (there's a toolbar button that allows you to switch between form view and script view for your calculations). If you use a single command containing the entire calculation script as the UI does, then you will need to parse the string to find individual calculations. Regular expressions can help here, but it gets real hairy if you start dealing with invalid calculations in the script. However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript and no parsing will be necessary on your part. Just be sure not to make any changes in the UI or the UI will save them back into a single Command object containing all of the calculations.

Also, don't forget the "Calculate" command. Without this no aggregation of values will happen in your cube and leaf cells and cells you have explicitly assigned values to will have any data.

|||

Hi Matt,

This is exactly what I was looking for,

Quote > "......However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript......"

1. How do we create calculations from code?

2. How and where to use the "Calculate" command that you specified?

Please provide a sample line of code or a link to the same, It would help me to understand better.

Thanks a lot for all your help.

Regards

Vijay R

|||

1. Here's a very simple code sample:

Server srv = new Server();

srv.Connect( "localhost" );

Database db = srv.Databases[0];

Cube cb = db.Cubes[0];

MdxScript script = cb.MdxScripts[0];

// Append calc to existing command

script.Commands[0].Text = script.Commands[0].Text + "\nCREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;";

// Create calc in new command

script.Commands.Add( new Command( "CREATE MEMBER CurrentCube.Measures.MyCalcMember as 2;" ) );

script.Update();

srv.Dispose();

2. The Calculate command is added to the default script automatically be the UI and assumed by the engine if no script is present. This command is normally the very first command in the script and it is what causes the aggregation of leaf cells into non-leaf cells. (See http://msdn2.microsoft.com/en-us/library/ms145565.aspx)

|||

Whether or not this is intended to be public, I don't know... but it works...

new Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c)

That returns a Scripts object which gets you everything you need, I believe. I think you need a reference to C:\program files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Design.DLL

So you might play around with that. Sure would be nice if that were part of AMO, huh!

sql

Calculated Measure: Formula limitations

Hi,

I am using AMO to programmaticaly create the SSAS objects like Cubes, MeasureGroups, Measures, Calculated Measure, partitions .etc.

I pick the necessary information from an existing Oracle OLAP/datawarehouse. In the Oracle OLAP the calculated measure can contain any formulae which the oracle db can support. This means its a huge list....! for example, Log, Decode functions.

Though SQL Db provides these functions, they are not avaliable to use directly in the MDXScript of the Calculated Measure.

so now when I am synchronizing the Oracle OLAP to SSAS programmatically at runtime, how do I resolve these inconsistencies in the formulae?

Regards

Vijay R

I think you mixing several concepts.

You can still referece Oracle functions while in DSV. You can create Named query or Named caclulation referencing any feature avaliable in your relational database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I guess I did not explain it correctly, I will try and explain it again.

I have a Oracle OLAP with an J2EE web app. I can create measures, calculated measures and the likes. when i create calculated measures in the Oracle OLAP using the J2EE UI, I can provide any formulae which the Oracle core can understand.

so now for eg: I have defined 2 measures M1 and M2 in Oracle OLAP. Next I define a calculated measure in Oracle OLAP, Here I define a formula like (M1 + M2)/100. Now when i view the data I get three columns M1, M2 and Calculated Values (M1+M2)/100.

Now I have a AMO program in .NET that automatically picks up the new measures and their related table info from the Oracle Relational tables and creates the same ones in SSAS. This means it creates a cube, then creates a measure group, sets the source of the measure group to the Oracle relational table (fact table), then creates the measures and partitions. The source property for all these is still the oracle relational database. but the information as to which tables, columns to use is picked from the existing oracle OLAP.

finally I process the cube in SSAS, and it is browseable.

The problem is when i try to create the calculated measure that is there in Oracle OLAP. I retrive the formula of the measure as (M1 + M2)/100. Now what I do in AMO is add a MDX script that creates a Member and adds this formula to it. Now this becomes a calculated measure in SSAS. whose value is got on the leaf cells using the "CALCULATE" command in the MDX script.

This works fine for all operators like + , - , * , / and also for Avg, Sum. but when it gets a formula like decode .... or abs, the formula in the MDX script is not recognised by SSAS. This is because MSXScript does not have those functions. I cannot map them to any Oracle functions because they are all built in Oracle funtions. like you know 'decode' is a common Database function avaliable in SQL also.

I am still stuck with this. currently we are not syncronizing the measures which have such functions. I have written a filter for them :-). so how do we do this. I was thinking that we write a SQL CLR function and pass the formula to it, which converts the formula to a MDX normalized form or something. still not sure.

Okay, I forgot. I have not used names queries or named calcualtions. I thought that names queries are for a partition. I have a fixed SSAS datasource which is mapped to the oracle db. when i use AMO to create the partition i just specify the table name in the SSAS datasource. About named calculations, I have to frankly accept I have no idea about it. ( I will look at this)

today there was another problem while using aggregation functions like avg and sum in the calculated measures (see my new post ). Not sure what is happening.

Thanks for the reply.

Regards

|||

Several things.

One, please take a look at the named calculations. This allows you to create a new column in your DSV "pusihng" calculations to relational database.

Second, take a look at the Analysis Services stored procedures. See if you can write a stored procedure implementing the functionality you need. Later you will use stored procedure in your MDX query.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.