Calculated Member banding

Hi,

I'm not sure if this is possible in analysis services or if i need to find dome other solution but i would like to implement a banding group on one of the measures in the analysis services cube i am developing.

I have a measue group counting visits and i can cross reference this with customers who are doing the visits. I'd like to be able to group customers in bands according to the amount of visits which they have done e.g.

300+ visits

200-299 visits

100-199 visits

<100 visits

Can this be done using a calculated measure or some other functionality within analysis services? If so can somebody suggest a solution.

All help is greatfully appreciated.

Cheers,

Grant

Hi,

I guess there is no nice and fast solution in cube.

Would you have a new dimension with the banding - or an attribute in the customer dimension or a seperate measure for the values?

I would recomend you add an column to your customer table and by ETL add the visits count. Based on this you could do a view or named query whith the band definition.

Other possible way is to add an datamining dimension with a cluster on the visits

Or do calculated mesasures for each band.

HANNES

|||

Thanks for the suggestions. I didn't think it was going to be a simple matter of doing this.

I don't think the value in the customer table will work too well in my case so i may look into a seperate dimension or calculated member for each band.

Your help is appreciated,

Grant

|||

Hi Again,

Sorry; this is probably a stupid question. How would i go about creating a seperate calculated measure for each banding group, what sort of format would it take?

Is it possible to create a calculated member consisting of a case statement which i could then use the results of the case statement to group measures on?

I may not be explaining this as well as i could but hopefully someone understands what i mean.

Many thanks,

Grant

|||

Hi,

from my perspective the calculation is the worst way because its the slowest and most complex and support the least flexibility I guess. I would not recomend if the cube is not small.

with calculation you could only create new measure for each band. I cannot create one measure and group by this.

The question as far as I remember - you need to have this band for each row in your fact data (leaf level at the cube) or for the customer on leaf only and all othere dimensions on the current node? (or root node?)

(if you need it on fact row level i would do it in the fact table query with a case .... - and use it as an fact dimension)

the calculation for customer only would be something like this

create member measures.customer100band;

scope(customer.key.members);

(measures.customer100band)=case when measure between x and y then measure else null end;

end scope;

This then uses the customer key and the current selection of all tothere dimensions for evaluation the formular. IT gets more dificult if you need this band not calculated by the current context.

Maybe someone else has a better solution for the calculation.

Best Regards

HANNES

|||

Thanks again for the response.

I'm having a little bit of a problem getting my head around what needs to be done. The problem initially is probably not completely understanding what i want to do. Taking your advice on board, i'll look into other methods aside from the calculated measure on the CustomerSite dimension. My fact table for visits has a default 1 column for basing the aggregation on so i cannot calculate in this table the bands.

If i have a seperate dimension; presumably this would consist of a key column and a column stating the band i.e.

Key Status

1 300+ visits

2 200 to 300 visits

.

.

.

etc...

The problem i have with getting my head around this method is where is link my dimension to; i cannot include the key in the fact table as i don't know at that stage how many visits are made to a specific customer for a selected date range. Do i not still need to link on the measure value that is returned at the chosen levels. I'm sorry if i'm not explaining this very well; this is a slightly steep learning curve at present.

Thanks again,

Grant

|||

As far as I understand you need your banding dependent on your selection (Time).

With this my calculation described above is the only method i would recommend.

As you already see - you cannot add by calculation new Attributes or Dimensions to the cube - cannot join your virtual dimension.

you only can create new members in existing dimensions / Attirubutes. Therefor you can only create new measures or new members in othere existing attributes.

Best Regards

HANNES

|||

I can see that adding a calculated member for each band will show the customers banding based on the selected date range. What i cannot see is how i would be able to slice the data based on the values in these bands. I don't see how i'd be able to get a count of customers for each of the visit bandings. Would this require another calculated member to work this out?

I'm starting to get to grips with this so thanks for your patience and help.

Grant

|||

Hi,

to slice and dice you need attributehierarchies in dimenions - with measure values associated to differnt members in this attributes.

Therefor you cannot slice and dice based on calculations (this is a singe member)

so you can do furthere calculations with your requirements or with one of my other sugestions.

Hannes

sql

Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?
Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>
|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>
|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>

Calculated member and Calculated Cells

What is the different between calcualted member and calculated cells?
Is there any guildeline in using calculated member and calculated cells?Calculated Members are simply formula that calculate values that do not
already exist in the cube. They do not take up any disk space as they
are calculated on the fly. A simple example would be if you had and
[income] and an [expenses] measure you could calculate profit by
subtracting expenses from income.
Nearly every cube will have some Calculated members.
Calculated cells on the other hand you do not see all that often. I
usually think of them as conditional overrides. Based on a subset of the
members from any of the dimensions in your cube and an optional
conditional statement, you can define an MDX expression that returns a
value which overrides the value that is actually stored in the cube.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> What is the different between calcualted member and calculated cells?
> Is there any guildeline in using calculated member and calculated cells?
>|||Do you have any simple real live example to help me to understand when I
should use Calculated Cells?
"Darren Gosbell" wrote:

> Calculated Members are simply formula that calculate values that do not
> already exist in the cube. They do not take up any disk space as they
> are calculated on the fly. A simple example would be if you had and
> [income] and an [expenses] measure you could calculate profit by
> subtracting expenses from income.
> Nearly every cube will have some Calculated members.
> Calculated cells on the other hand you do not see all that often. I
> usually think of them as conditional overrides. Based on a subset of the
> members from any of the dimensions in your cube and an optional
> conditional statement, you can define an MDX expression that returns a
> value which overrides the value that is actually stored in the cube.
> HTH
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <66CA27BC-9E0B-4383-BF63-1B2082A7DFAA@.microsoft.com>,
> Kam@.discussions.microsoft.com says...
>|||I used it once in an accounting situation.
The cube had one base measure that held the value of each of the
accounts. In the first month of the year there was an account called
"Retained Earnings" which was mean to display the net profit (or loss)
from the prior year. I used a Calculated Cell to override the measures
value when the user was looking at the retained earnings account member
for the first month in the year.
I could have achieved the same thing through creating a calculated
measure with an iif statement. (you can even set the base measure to
being not visible if you want)
But I can't think off the top of my head of any situations where
calculated cells could do something that could not be done using a
calculated member.
There was a thread running in the microsoft.public.sqlserver.olap
newsgroup called "Calculated Member solution (Dave, Deepak)" which might
give you another example.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <43339F67-CA70-4679-9F42-9B076A94EDEE@.microsoft.com>,
Kam@.discussions.microsoft.com says...
> Do you have any simple real live example to help me to understand when I
> should use Calculated Cells?
> "Darren Gosbell" wrote:
>
>

Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_ContaLiquidar_CRT7
AS
IIF(((ANCESTOR
(
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].CurrentMember,
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].[Carteira_ID]
),
[Measures].[CM_ContaLiquidar]))<0,0,[Measures].[CM_ContaLiquidar])
,
VISIBLE = 1;

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_ContaLiquidar_CRT7
AS
IIF(((ANCESTOR
(
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].CurrentMember,
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].[Carteira_ID]
),
[Measures].[CM_ContaLiquidar]))<0,0,[Measures].[CM_ContaLiquidar])
,
VISIBLE = 1;

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

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 member - please help (SQL Server 2000)

hi,

please if u could help with this:

i have a cube with 3 dimensions, Sales (fact table), Time and Accounts (general ledger accounts). Accounts dimension hierarchy is as follows:

1. Revenues
1.1 Income
1.1.1 number of account in General Ledger
1.1.2 number of account in GL etc.....
2. Expenses
2.1 Fixed costs
2.1.1 number of account in GL etc....
2.2 Operating costs
2.3 Overhead
3. Unsorted

now what i need to do is next: i need to calculate measure.salesamount in percentage of total sales amount for each category of accounts. for example i need measure.salesamount of each account number in Revenue/Income in percentage of the total salesamount of the category Revenue.

i hope i was clear enough in what i need.

please can anyone help me with this?

thank you in advance!

marko

try an expression something like the following in your calculation:

Measures.salesAmount / (Measures.salesAmount,Accounts.CurrentMember.Parent)

You can then either wrap the whole thing in brackets and mulitply by 100 or you should be able to use a percentage style format string - it depends a bit on how your front end works.

|||Hello Marko,

i am not 100% clear on ur pblm. but on what ever i understood i have given u a formula for calculating the calculated-member.

iif(
[revenue_dimension].currentmember.parent is null,1, ( [revenue_dimension].currentmember , [measures].[salesamount] ) / ([revenue_dimension].currentmember.parent , [measures].[salesamount] )

)
then make the data type of this calculated-member to percentage.

regards,
Rajiv.|||

Hi,

thank you both for your answers. both formulas work and give the same result.

Thank you once again for your help! Much appreciated!

best regards

marko