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 Dimension Member Problem

Hi,

I have a problem that I think I can solve by creating a calculated member in my time dimension, but I am struggling to get it to work.

My time dimension has a hierarchy of Year, Month, Week. The years go from 2002 to 2007, with an additional 'Pre 2002' year (having one month and one week). What I need to do is create an additional year which is the combination of 2002 and 'Pre 2002', which will then be called 'Pre 2003'. I have tried the following:

Code Snippet

CREATE MEMBER CURRENTCUBE.[Time].[Time].[All].[Pre 2003]

AS [Time].[Time].[Year].&[2002] + [Time].[Time].[Year].&[2001], -- (2001 is my name for the pre 2002 member)

VISIBLE = 1 ;

This builds ok, but when I browse the dimension, or use it in Excel 2003, the calculated member is nowhere to be seen.

Any ideas?

Dave

Your formula should be OK. An equivalent calc member for Adventure Works is the following.

create member currentcube.[Date].[Fiscal].[All Periods].[Pre 2004] as

[Date].[Fiscal].[Fiscal Year].&[2002] + [Date].[Fiscal].[Fiscal Year].&[2003];

In order to see your new calculated member you need to make sure you have deployed the changed calculation script to the server. When your said "this builds ok", it gave me an idea that you work in project mode (there is also possibility to work explicitly connected to the server). In the project mode the change you made is related only to the local files on your drive. When you use Build menu item it means that a validation has happened and a deployment script has been created but it does not mean the server already knows your changes. You need to use Deploy menu item in order Visual Studio to send your changes to the server. After that you should see your calculated member in the *new* browser window. If you already had it open you need to select Reconnect button for the change to be visible in the browser window.

In the server online mode (when you work directly with the server) you would just hit Save button and reconnect the browser.

Also, make sure you browse your Time hierarchy of Time dimension. A dimension can have more than one hierarchy. Similarly in Adventure Works i see the member above only when i browse Fiscal hierarchy of the Date dimension but Calendar hierarchy does not have the member.

|||

Andrew,

I am definitely deploying the changes to the server, as any other calculated measures appear correctly.

I have tried using the Adventure Work example you gave, and I have the same problem. In the dimension browser the new period is nowhere to be seen.

|||

Ok,

I can now see the calculated member. I was looking in the dimension browser instead of the cube browser!! Now I have another problem. I am connecting to the cube from Excel 2003, and when I drag in the hierarchy I cannot see the new member. In Excel 2007 there is a pivot table property "show calculated members from OLAP server" When I set this I can see the member fine. However, I need to use Excel 2003 for this project. I have found a pivot table property "ViewCalculatedMembers" which I have set to true using VBA. However, the member still does not appear.

Any ideas?

Calculate Difference?

I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:

Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

RobTry something like:

select a.truck, a.reading - b.reading
from (select truck, reading from table where period = '2/28/03') b
inner join table a
on a.truck = b.truck
where a.period = '3/31/2003'|||Thank you for the quick response rnealejr, but this won't work for a table with 4 years of data. I am trying to do the following, but it is not quite right

select [Month] = t.period,
[Value] = t.reading - ISNULL(t2.reading, 0)
from mytable t
left join (select DATEADD(m,-1,period) X, reading from mytable) AS t2 ON t2.X = t.period

I think the answer is to create a udf that subtracts 1 from the day until the month changes, so I always get the last day of the month. Does this sound like the correct solution?

Rob|||select a.truck, a.period as enddate, a.milage - b.milage as milesrun
from test1 a join test1 b on a.truck = b.truck
where a.period = dateadd (mm, 1, b.period)
or b.period = dateadd (mm, -1, a.period)

I think you just missed the join on truckID. Also, since february 28 + 1 month is March 28, I decided to try it both ways (up and down). Hope this helps.|||OK - I finally got it. I ended up doing the following;

I first created a udf that looked like this

ALTER function PMonth(@.dt DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.ret DATETIME
SET @.ret = @.dt
WHILE DATEPART(m, @.dt) = DATEPART(m, @.ret)
SET @.ret = DATEADD(d,-1,@.ret)
RETURN @.ret
END

Then I wrote my sp like this:

SELECT [Truck] = m.truck,
[Period] = m.period,
[Value] = m.reading - ISNULL(m2.reading, 0)
FROM mytable m
LEFT JOIN (SELECT period, truck, reading FROM mytable) AS m2 ON m2.period = dbo.PMonth(m.period) AND m2.truck = m.truck

I am betting its not the most efficient solution (it takes 4 seconds), so if anyone has suggestions, please let me know.

Thanks,

Rob|||select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman|||Originally posted by blindman
select CurrentMonth.Truck, CurrentMonth.Reading - isnull(PriorMonth.Reading, 0)
from YourTable CurrentMonth
left outer join YourTable PriorMonth
on CurrentMonth.Truck = PriorMonth.Truck
and convert(Char(7), CurrentMonth.Period, 120) = convert(Char(7), dateadd(m, 1, PriorMonth.Period), 120)

blindman

Wow - this works much faster. Thank you very much blindman.

Rob|||Make sure you only have one entry per truck per month!

C# UDF project call C++ model (SQL Server 2005)?

I have some legacy C++ code and I am creating a C# project for UDF function
and another project for C++ classes. I always got error message when I am
trying to add reference to the class lib project:
A reference to 'classModel' could not be added. SQL Server projects can
reference only other SQL Server projects.
I tried to create the C++ project as SQL Server project too and the error
message is the same.examnotes <nick@.discussions.microsoft.com> wrote in
news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:

> I have some legacy C++ code and I am creating a C# project for UDF
> function and another project for C++ classes. I always got error
> message when I am trying to add reference to the class lib project:
> A reference to 'classModel' could not be added. SQL Server projects
> can reference only other SQL Server projects.
That is because the VS SQL Server Project doesn't allow you to reference
any other project types (or assemblies already defined in the database).
You can instead use my project type for this:
http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
I wonder if that is the real problem, see below.

> I tried to create the C++ project as SQL Server project too and the
> error message is the same.
OK, so is the C++ project managed code? If not you can not use it inside
SQL Server. In that case you have to either do COM interop against the
C++ classes, or P/Invoke.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||IC, thanks.
I avoid to create the UDF in C++(Managed) because not much example, support
information about C++ user defined function programming. And I am not famila
r
with managed C++ syntax.
"Niels Berglund" wrote:

> examnotes <nick@.discussions.microsoft.com> wrote in
> news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
>
> That is because the VS SQL Server Project doesn't allow you to reference
> any other project types (or assemblies already defined in the database).
> You can instead use my project type for this:
> http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
> 26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
> I wonder if that is the real problem, see below.
>
> OK, so is the C++ project managed code? If not you can not use it inside
> SQL Server. In that case you have to either do COM interop against the
> C++ classes, or P/Invoke.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

By Account Aggregation Function

Hello All,

I have tried creating a sample SSAS Project, using the AdventureWorksDW DB.

I have created an account parent-child dimension using an account dimension type.

I'm using the measure Amount from the FactFinance table, and used the ByAccount aggregateFunction property for that measure.

The problem is, when deploying that project I get the following error:

Aggregation function NONE specified for mapping account type Statistical is not supported for ByAccount semiadditive measure.

The Statistical account type aggregation function is set by the business intelligence wizard to None. I see no way to change it.

Does anyone see what's the problem here?

Hi,

The aggregation functions can be changed by selecting Database -> Edit Database in the AS project in BIDS.

-Morten

Business Objects and Db Performance

I'm new to Business Objects, and I have a question to ask,

Does creating universe in the designer actually creates indexes in the DB? Or universe is just a reference model for the BO app.

If so, won't poor designed universe slow down DB performance?

For those who have experience in BO, please advise. Thanks.In my experience BO does not create any indexes in the DB. And yes, universe design is very important as poor universe design will lead to poor performance.. I am using BO 5.1.?, I'm not sure what version you are using.

Any more questions please let me know.

Thanks,

Originally posted by Patrick Chua
I'm new to Business Objects, and I have a question to ask,

Does creating universe in the designer actually creates indexes in the DB? Or universe is just a reference model for the BO app.

If so, won't poor designed universe slow down DB performance?

For those who have experience in BO, please advise. Thanks.

Business Object XI Variable Help

Hi All!

I am creating a report that lists every prescription a person is taking and the "Disease State" it is in. In the Group Header I need to list all the Disease States that this person is taking a prescription for. However, I cannot figure out how to do it.

In the details section, I have a formula for disease state. The person can be taking prescriptions for the same disease states. Therefore, the detail section could look like:
Diabetes
Diabetes
CHF
Diabetes

In the Group Header I need to output:
Diabetes, CHF

Does anyone know how to do this?
Thanks.
DanielleProbably the easiest way is to create a manual running total that will create a string containig unique disease states for each person. Then you can hide details and display person name and disease states in group footer.