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