Showing posts with label measures. Show all posts
Showing posts with label measures. Show all posts

Thursday, March 29, 2012

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )


select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mmTongue Tieds

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

You need to use CREATE MEMBER syntax instead of WITH MEMBER. Otherwise, it should work.sql

Calculated member for the current month

Hi,

I am trying to create Calculated Member for the current month
as below
WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]}
select
{[Measures].[SESSION Count]} ON COLUMNS
from Ecgkpi where StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") .

When I run this as MDX query in Mgmt studio it is giving the count ,
but when I am trying create the new Calculated Member I am getting the
error as the script contains the statement which is not allowed.

Can any one throw whether the above one is correct or not?

Thanks

If you are trying to create a measure using the query it will not work as a calculation is an expression, not the result of a query.

I think what you want is an expression something like the following, which will give you the session count for the month.

(StrToMember("[Time].[Month].["+ Format(now(), "yyyy/MM") + "]"), [Measures].[SESSION Count])|||

Darren,

Thanks for your quick response. Yes I have tried that way aswell.

But when I use that expression in calculated member its not returning the value, where as I query it in mgmt studio I am getting

the value. Any idea? or any otherway that I can get the measure for the current month.

Your help is appreciated.

Thanks

|||

The calculation should be the same as the query. You should note that you are not using the calculation in the sample you posted. You are defining it in the WITH MEMBER section, but then you do not reference it anywhere else in the query

Something like the following should show you the currentMonth measure (which should only be for the current month as defined by the clock on the server) and the [SESSION Count] measure which should be at some sort of aggregated level like the total for all time. It depends on how your time dimension is configured.

Code Snippet

select
{[Measures].[SESSION Count], [Measures.CurrentMonth} ON COLUMNS
from Ecgkpi

If you do the following you will see why your original query was not quite right as currentMonth will return the same as the raw session count measure

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
{[Measures].[SESSION Count]

}
select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

Where as something like this should work the way you want, which is the same logic that we were putting in the calculated measure in the cube.

Code Snippet

WITH MEMBER [Measures].[currentMonth] as
([Measures].[SESSION Count], StrToMember("[Time].[Month].["+ Format(now(), "yyyy/
MM") + "]") )


select
{[Measures].[SESSION Count], Measures.[currentMonth]} ON COLUMNS,

{[Time].[Month].[200704].[Time[Month].[200705]} ON ROWS
from Ecgkpi

|||

Darren,

Thanks for your much handy information.

The expression for the current month

(StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now()-1, "yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count])

is working in the KPI expression.

But, I am trying to find it otherway aswell, Need your inputs again!.

My data source is from Oracle. I have the date information say the field Start in timestamp format YYYY-MM-DD hh:mmTongue Tieds

viz: Start

2006-10-17 13:48:24.603

2007-01-05 08:41:12.411

2007-06-04 16:24:31.839

Without time dimension I want to extract the measure for the current month like the expression

(StrToMember("[table].[Start].[Start].["+ Format(now(), "yyyy/MM") + "]"),[Measures].

[SESSION Count])

But it is giving null. Any idea how to extract current month from the timestamp. This is

much helpful working with Oracle timestamp.

Hope I am clear with this.

Thanks

|||

Darren,

Here is the clear picture. In the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(), "yyyy/MM") + "]"),

[Measures].[ECG GUI SESSION Count]) the filed [ECG GUI SESSION].[MONTH].[MONTH] is extracted as new named query using to_char

function. In this case only it is working

But when I have created server time dimension the expression (StrToMember("[ECG GUI SESSION].[MONTH].[MONTH].["+ Format(now(),

"yyyy/MM") + "]"),[Measures].[ECG GUI SESSION Count]) is giving null .

I am trying with different options. But extracting month from timestamp using to_char into a new field is not a correct solution. I

want to work out from the server dim or from the timestamp field directly.

Afore said code snippets are working when we are mentioning the month

[Time].[Month].&[2007-06-01T00:00:00] explictly.

But what I am thinking is if I use the expression like now(), the cube will be processed every month and it will update the measure

on monthly.

I hope I will get the right direction from expertisee.

Thanks

|||You need to figure out what the unique member name for your time dimension looks like. I don't normally use server time dimensions myself so I am not sure off the top of my head, but I don't think that they use "yyyy/MM" at the month level. The easiest thing to do is to open a new MDX query in SQL Server Management Studio and drag one of the month members onto the main window. This will show you the unique name for that member. Then you just need to replicate that format when you use the StrToMember() function.

Tuesday, March 27, 2012

Calculated Member (beginner)

Hi,

I'm building a calculated member which should only calculate the measures in the calculated member if specific measure is > 0 (otherwise the value should be Null)

For example:

If physicalStock > 0

[Measures].[Netweight] * [Measures].[Physical]

How do I build this in the calculated member...?

(Using Analysis2000)

Thanks in advance

Haakon

With AS2000 Mosha have a blog post of this subject here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/30/mdx_is_empty.aspx

HTH

Thomas Ivarsson

|||

Here is what I am trying....

Iif([Measures].[Physical] > 0), [Measures].[Netweight] / [Measures].[amount

_count] * [Measures].[Physical]

Which don't work....any ideas - what I should do...?

Thanks

|||

IIF(([Measures].[Physical] > 0, [Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

HTH

Thomas Ivarsson

|||

Thanks for your input - but it still seems to be causing problems:

I am getting the following error in the calculated member builder:

"Unable to update the calculated member. Formula error - Syntax error - token is not valid"

I am using the following suggested value expression:

Iif(([Measures].[Physical] > 0,[Measures].[Netweight] / [Measures].[amount_count] * [Measures].[Physical]),Null)

Your suggestions are appriciated,

Thanks,

|||

It is my error. Try this:

IIF([Measures].[Physical] > 0,[Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

Copy and paste this example above.

Regards

Thomas Ivarsson

sql

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 measures on Analysis server

Hi,

Is there anyway I can process calculated measures of the cube on server? As of now its getting processed on client and its taking lot of client memory. We have got a good configuration server. Is there anyway I can configure to get this processing done on OLAP server?

Any help/tip will be appreciated

Thanks,
SP

Are you alluding to MDX query processing, and are you using AS 2000 or AS 2005?|||

Hi Deepak,

We are usin AS2000. What exactly u mean by alluding by "alluding to MDX query processing"?

Thanks

|||

Just wanted to confirm that you weren't referring to cube processing time, rather than to query response time.

With AS 2000, you can (to some extent) increase server-side query execution by changing connection string parameters:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

>>

Microsoft SQL Server 2000 Analysis Services Performance Guide

Published: June 1, 2003

Summary: This paper describes techniques you can use to optimize query responsiveness and processing performance in Microsoft? SQL Server? 2000 Analysis Services.

...

The Execution Location and Default Isolation Mode properties are always used together.

The Execution Location property controls whether a query is executed on the client or on the Analysis server. By default, the execution of the query is split between the client and the server in order to distribute the processing load across multiple computers. Using an Execution Location setting of 3 in the connection string causes most queries to be executed on the Analysis server. This setting also reduces the amount of data that is returned to the client over a slow network connection. In most cases, only the final result is returned to the client, and the client processor resources are not used to help resolve the query. The value of the Execution Location property is set in the connection string when a session is established. It can be changed during the session. However, every client using this setting adds to the load on the Analysis server.

The Default Isolation Mode property controls the refreshing of the cache on the client. Using a Default Isolation Mode setting of 1 causes the cache to be invalidated whenever a query statement is executed. When this setting is used in conjunction with the Execution Location setting of 3, you can ensure that most queries are resolved on the Analysis server rather than at the client. By default, PTS will use data in the local cache to resolve queries when possible, which conflicts with the Execution Location setting if you are attempting to execute as many queries as possible on the Analysis server. The value of the Default Isolation Mode property is set in the connection string when a session is established. This value can also be changed during a session.

>>

|||

Hi Deepak,

Thanks a lot,

This is definitely goigng to help us in our approach

Regards,

SP

Calculated measures on Analysis server

Hi,

Is there anyway I can process calculated measures of the cube on server? As of now its getting processed on client and its taking lot of client memory. We have got a good configuration server. Is there anyway I can configure to get this processing done on OLAP server?

Any help/tip will be appreciated

Thanks,
SP

Are you alluding to MDX query processing, and are you using AS 2000 or AS 2005?|||

Hi Deepak,

We are usin AS2000. What exactly u mean by alluding by "alluding to MDX query processing"?

Thanks

|||

Just wanted to confirm that you weren't referring to cube processing time, rather than to query response time.

With AS 2000, you can (to some extent) increase server-side query execution by changing connection string parameters:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

>>

Microsoft SQL Server 2000 Analysis Services Performance Guide

Published: June 1, 2003

Summary: This paper describes techniques you can use to optimize query responsiveness and processing performance in Microsoft? SQL Server? 2000 Analysis Services.

...

The Execution Location and Default Isolation Mode properties are always used together.

The Execution Location property controls whether a query is executed on the client or on the Analysis server. By default, the execution of the query is split between the client and the server in order to distribute the processing load across multiple computers. Using an Execution Location setting of 3 in the connection string causes most queries to be executed on the Analysis server. This setting also reduces the amount of data that is returned to the client over a slow network connection. In most cases, only the final result is returned to the client, and the client processor resources are not used to help resolve the query. The value of the Execution Location property is set in the connection string when a session is established. It can be changed during the session. However, every client using this setting adds to the load on the Analysis server.

The Default Isolation Mode property controls the refreshing of the cache on the client. Using a Default Isolation Mode setting of 1 causes the cache to be invalidated whenever a query statement is executed. When this setting is used in conjunction with the Execution Location setting of 3, you can ensure that most queries are resolved on the Analysis server rather than at the client. By default, PTS will use data in the local cache to resolve queries when possible, which conflicts with the Execution Location setting if you are attempting to execute as many queries as possible on the Analysis server. The value of the Default Isolation Mode property is set in the connection string when a session is established. This value can also be changed during a session.

>>

|||

Hi Deepak,

Thanks a lot,

This is definitely goigng to help us in our approach

Regards,

SP

Calculated Measures not showing in SSRS.

Hi,
I have two cubes , One is fairly large Cube and the other one is Smaller one.
When I try to Use SSRS to report on these two cubes, i could see the calculated measures only in the Smaller Cube and there are no such things in the Large Cube.
I could Report on the Large Cube with base Measures.
I see the below message at the end of the Measures List.
"More Measures Exists than can Be shown."

Am i missing something?
Can anyone point me to some clue .

Thankyou
Vidya

Hi,

did you reprocess the large cube after,adding the calculated measures?

|||

I have not used SSRS yet, but I came accross a similar issue when I was creating a cube.

When you create a cube on SSAS, it automatically creates a script called "CALCULATE." You can view it on Caluculation tab on the cube design. It contains the following code:

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE

If this script is modified or deleted, you may not see your measures correctly in your cube.

|||Thankyou for the Reply,
But I Have that CALCULATE untouched.
Another intresting note is that i can see all the calculated measures with the values from EXCEL 2007.
When i try querying the cube for the Calculated Measure then i am getting the values ; Only problem is that i am not able to see them through SSRS.
I have tried re-processing the Cube but no luck.
Thankyou
vidya

Calculated measures in report builder

Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.

Any help is appreciated.

You can't. The Report Builder doesn't fetch dimension calculated members. For this reason, we abandoned dimension calculated members and defined them as calculated measures in the cube. I wasn't successful also in trying to define the calculated dimension members as regular members by implementing an utility dimension because cells that do not exists in the cube cannot be written to.|||

Thanks for your response, Teo.

Are you saying that you couldn't do it at all, or that you were able to convert the time calcs to calculated measures and you were then able to use them in Report Builder? At the moment I'm not sure how I would go about converting the time calcs to calculated measures.

BTW, I read your book. Nice work!

|||

Thanks. The calculated mesures approached worked but you will end up with as many calculated measures as the number of the time-based calculations (QTD, YTD, etc.). Another issue you will discover if you decide to take this road is that a date filter in the RB report won't overwrite the default member fo the Date dimension. This will force you to always bring the Date dimension in the report if the report uses the time calculated measures.

What I wasn't able to do is implement the Time intelligence dimension calculated members as regular members of an utility dimension.

It will be great if you could log this issue to connect.microsoft.com. The more it is asked for the more likely will be to get implemented.

|||

Hi,

I want to generate Ad-Hoc reports using report builder with cube created. But am not able to see any calculated measures in the report. Can anyone tell me how to solve this.

|||You need to assign the calculated measures to display folders using the Properties window on the Calculations tab.sql

Calculated Measures

We are presently having an issue where if we use calculated measures an MDX
query that takes about 1-2 seconds to run suddenly takes about 1.5 minutes
to run. These measures are not extremely complicated they are simple like
taking TotalDollars/TotalUnits. TotalDollars and TotalUnits are already
measures in the cube. Any ideas why this could be happening?
Thanks,
AlWe are having an issue with Calculated Members and CrossJoins. We have an
MDX query where when we get to a certain granular level we are taking a
considerable performance hit. Take the following query. It takes about 1.5
minutes to run.
WITH MEMBER [Measures].[Average Bill Rate2] AS
'Measures.[Total Revenue]/Measures.[Total Hours] '
SELECT NON EMPTY { [Measures].[Average Bill Rate2], [Measures].[Total
Cost],
[Measures].[Total Hours], [Measures].[Total Revenue] } ON COLUMNS,
NON EMPTY { (
[Profit Center Rollup].[Profit Center Rollups].[Profit Center Level 10].
ALLMEMBERS *
[Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS *
[Project Type].[Project Types].[Project Type].ALLMEMBERS *
[Transaction Type].[Transactions].[Transaction Type].ALLMEMBERS ) }
If I remove the line [Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS * from the query it takes 1-2 seconds to run.
Or if I remove "[Measures].[Average Bill Rate2]," from the query it again
takes 1-2 seconds to run. Do you know if there are any work arounds or
fixes to this issue? Also keep in mind that this query is originating in
SSRS. The issue here is that any changes made to the query directly means
that we can no longer use the query designer in SSRS.
Thanks,
Al

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.

calculated measure problem(ssas 2005)

I have the data like following, count and Amount are the measures.

BU Tiear Designation Count Amount
BU1 1 PM 10 200
BU1 2 SE 20 300
BU2 1 PM 40 300

i need to have calculated measure like following

(10*200)+(20*30)+(40*300)/ (10+20+40)

pls tel me any way to do that
tried like following. but there is some error.pls help me to solve this.

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[SumOfECount]
AS
sum
(
[Measures].[Employee Count]
) ,
VISIBLE = 1 ;
CREATE MEMBER CURRENTCUBE.[Measures].[multi]
AS

'[Measures].[Employee Count]*[Measures].[CPC Value]'
,
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[SumOfEMulti]
AS
sum
(
[Measures].[multi]
) ,

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[Calcula]
AS '[Measures].[SumOfEMulti]/[Measures].[SumOfECount]' ,
VISIBLE = 1 ;

Sunday, March 25, 2012

Calculated Measure error

Hi.
I am working on a cube for schools, precisely analysing student absences.

I have two measures belonging to two measure groups where each measure group is linked to all the dimensions. These measure are : count of absences ,number of periods.

The calculated measure i'm trying to do is : (count of absences /number of periods )where number of periods != 0.

case

when [measures].[number of periods]= 0

then 0

else ([measures].[count of absences]/[measures].[number of periods])

end

in the browser with school,class,student dimensions as rows and both [absence count] and [number of periods] as measures, the data is displayed correctly.

however when i drag the calculated measure into the cube browser i get dummy data where the joins between students/classes/schools are lost...as if the 3 dimensions are cross joined...so i get all the students of the dimension student belonging to each class .

i don't know why this is happening: each measure alone is working but when i combine them both into one measure the data is mixed up.

What could be the problem?

thanks for your help

Christina

it worked..!!!

i put the [number of periods] in the non-empty behaviour field...

i didn't know that it was that important....

thanks anyway

Christina

Calculated measure based on field in fact table that is not a measure in cube.

I have several fields in the fact table that are not measures in a cube. In this case I have a OrderEntryDateId, WantDateId, and InvoicedDateId. If the order has not invoiced the InvoiceDateId is 0 (zero).

My cube joins the fact table to the time dimension table on the wantDateId. The cube has measures for feet, dollars, pounds, material cost...

I want to create a calculated measure called "Invoiced Dollars" that is based on iff(invoiceDateId>0;TotalDollars,0) however invoiceDateId is not a measure nor is it linked to time in this cube.

Any ideas?

Hi. You could make the InvoiceDateID a member property of time dimension at the leaf level. You can then check the member property value in your calulated member. It might look something like this:

IIF(CINT(Time.Time.CurrentMember.Properties("InvoiceDateID")) > 0, TotalDollars, 0)

Drawbacks to this solution: (1) it only works at the leaf level of the time dimension, (2) Probably slow because of the conversion of the member property value.

Hope this helps - Paul Goldy

|||

I already played with this a bit this afternoon and it is not what I need.

I guess the best way to do this is to add a column to the fact table so it can work at all levels and be pre-aggregated.

Thanks!

Calculated Measure And Drill Through

I am getting the error below in Proclarity:

"Error Accessing Drill To Detail Information

xxxxxxx in dimension Measures is a calculated member."


Can AS2005 support drill through from a calculated member, if not is there a work around?

Thanks

The answer is pretty easy but you might not like it: The drillthough is not supported on the calculated measures or calculated members.

Not sure what would you expect from the drillthoguh into calculated measure or member. Calulated member could be totally unrelated to the facts in your cube. How would you drillthorouh into that? You can definitely can come up with simple case of calc measure being sum of two measures in the same measure group, but that is very narrow and not practical case.

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

Calculated Fields in an OLAP cube severly impact data retrieval time

I have a OLAP cube that I'm using for a data source that has several
calculated measures in it. When I add the calculated measures to my report
the retrieval time goes from ~1 second to ~4 minutes. I haven't been able
to locate any information about this issue. Is there something I'm doing
wrong, or is this a Reporting services issue?Nevermind. I hadn't realized that splitting a calculated field over an
unrelated dimension would have the same effect.
"Jeremy Farnham" <jeremy.farnham@.omya.com> wrote in message
news:esFYjweBIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I have a OLAP cube that I'm using for a data source that has several
>calculated measures in it. When I add the calculated measures to my report
>the retrieval time goes from ~1 second to ~4 minutes. I haven't been able
>to locate any information about this issue. Is there something I'm doing
>wrong, or is this a Reporting services issue?
>sql

Monday, March 19, 2012

calculate age from DOB Calculated measure

Hi All,

I am trying to calculate age from DOB and make it a calculated member.

With Member [Measures].[memberage] as

'datediff("yyyy",[Member].[DOB].[DOB],Now()) '

select {[Measures].[memberage]} on columns,

{[Member].[First Name]} on rows

from Cube

When i run this the memberage is returned as 2006 for all the DOB.

How do we calculate the age from DOB(datetime) and make it available as a calculated measure?

Thanks,

Awaiting your replies

Try

With Member [Measures].[memberage] as

'datediff("yyyy",[Member].[DOB].CurrentMember.MemberValue,Now()) '

select {[Measures].[memberage]} on columns,

{[Member].[First Name]} on rows

from Cube

|||

Hi

when i execute the above i get the following error

#Error Execution of the managed stored procedure datediff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..

|||You have to provide an argument of type Date to DateDiff. I don't know the exact type of your [DOB] attribute. If MemberValue doesn't work in your case, you can try something like CDate([DOB].CurrentMember.Name). You need to somehow extract a Date value from the currentmember of the [DOB] hierarchy and pass it to DateDiff.

Wednesday, March 7, 2012

Cache Warming

Hi,

I have a cube, which is having a lot of calculated measures.

There are some MDX queries which fetches data from the cube, and aggregates data through a parent -child hierarchy.

It is taking considerable amonut of time.

So , for improving the performance i have written a c# program which executes all the possible MDX queries just after the daily refresh of the cube. As expected it was really giving noticable performnace improvement.

But the problem started when i deployed the cube and the cache warmer into another server, where the cache warmer is executed by Sql Agent, with the service account which is a local admin on that box. The warmer program executed fine, but i am no longer able to find the performance improvement. I understand that , the global cache will be shared across the users only if the users are having same security role in the cube.

So i added the sqlagent service account to the role whiich execute the MDX in the application. But still it is not helping me Sad

Please help me.

Also i would like to know, whether clearing the cache everyday would help in any way in this scenario.

Regards,

Jiju

Somebody please tell me whether I am doing any blunder stuff here, at least Sad

Regards,

Jiju