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

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

Business Objects vs. Analysis Services and IT Bullies

Hello,

I've been working on implementing an SSAS solution for my department. The Proof of Concept is working very well and I am ready to implement it. However, some bullies from IT have come in and said that cubes are not the correct way to go about this (even though it is working) and that we need to call Business Objects and have them come in and tell us what they can provide.

To create my solution, I am using SSIS, SSRS, and SSAS. It is a great package that has everything in it and, frankly, I don't want to have to redo all my work.

When Business Solutions comes in I want to be able to ask them questions (objectively and fairly) about what they can provide us. If they have a better solution, then I should take it, but if it is sort of borderline, I want to be able to "win" with Microsoft and SQL Server 2005. One quick example that I know of is that when sending a report from Business Objects to Excel, the format is not always pretty and/or easy to deal with.

Any suggestions about a comparison list or feature by feature list of the two? Or maybe just some pointed questions about can Business Objects do X? Maybe questions about speed, stability, "plays well with others (Oracle)", etc.

Thank you for the help.

-Gumbatman

Hello! Here is my list:

Does BO have their own relational database and ETL tool?

Why should you pay for BO when you have SSAS, SSRS, SSIS, SQL Server 2005 bundled and without extra license cost?|||

Hi,

I implemeted a DW project last year, using SSIS then both Business Objects and SSAS on top of that. Have to say that the amount of time spent developing the BO universe and reports took almost as long as the ETL process (generally reporting times are far shorter).

From a support and development aspect, the extra level of design which is built into BO is a nightmare to support. You will end up needing people who are experts in both SQL and BO, not many, and those are all probably contracting.

Did notice that if your reports get complicated or the BO Universe does magical things, the performance hit was on the SQL Database. If the database is a transactional one rather than a DW you could encounter problems with performance. I don't think it has quite the same query performance as SSAS, although BO could do some very clever things that SSAS couldn't or would be very difficult to do (more in business modelling).

It also depends how many people are using the reports, more users probably more hits on the underlying database; I don't think there is any caching like in SSAS, so if someone runs the same report it will have to query the database again rather than hit the cache.

Don't think there are aggregations in BO, we didn't use any. Think we create table functions to aggregate things up to differnt levels of granularity.

In my opinion if you have a BO team in the company, it is a route you could use as well as SSAS. Allows you to migrate away from BO into a SSAS world..

The improvements of Excel 2007, reporting services in 2008 and proclarity all make BO look basic and dated.

Sure if you contact Microsoft and asked them the question, they would list a load of things. Probably have a fact file on the matter.

Sorry for the long reply!

Hope that helps

Matt

|||

Matt and Thomas,

Thank you for the answers. This is great information and will go a long way for me.

-Gumbatman

Business Objects vs. Analysis Services and IT Bullies

Hello,

I've been working on implementing an SSAS solution for my department. The Proof of Concept is working very well and I am ready to implement it. However, some bullies from IT have come in and said that cubes are not the correct way to go about this (even though it is working) and that we need to call Business Objects and have them come in and tell us what they can provide.

To create my solution, I am using SSIS, SSRS, and SSAS. It is a great package that has everything in it and, frankly, I don't want to have to redo all my work.

When Business Solutions comes in I want to be able to ask them questions (objectively and fairly) about what they can provide us. If they have a better solution, then I should take it, but if it is sort of borderline, I want to be able to "win" with Microsoft and SQL Server 2005. One quick example that I know of is that when sending a report from Business Objects to Excel, the format is not always pretty and/or easy to deal with.

Any suggestions about a comparison list or feature by feature list of the two? Or maybe just some pointed questions about can Business Objects do X? Maybe questions about speed, stability, "plays well with others (Oracle)", etc.

Thank you for the help.

-Gumbatman

Hello! Here is my list:

Does BO have their own relational database and ETL tool?

Why should you pay for BO when you have SSAS, SSRS, SSIS, SQL Server 2005 bundled and without extra license cost?|||

Hi,

I implemeted a DW project last year, using SSIS then both Business Objects and SSAS on top of that. Have to say that the amount of time spent developing the BO universe and reports took almost as long as the ETL process (generally reporting times are far shorter).

From a support and development aspect, the extra level of design which is built into BO is a nightmare to support. You will end up needing people who are experts in both SQL and BO, not many, and those are all probably contracting.

Did notice that if your reports get complicated or the BO Universe does magical things, the performance hit was on the SQL Database. If the database is a transactional one rather than a DW you could encounter problems with performance. I don't think it has quite the same query performance as SSAS, although BO could do some very clever things that SSAS couldn't or would be very difficult to do (more in business modelling).

It also depends how many people are using the reports, more users probably more hits on the underlying database; I don't think there is any caching like in SSAS, so if someone runs the same report it will have to query the database again rather than hit the cache.

Don't think there are aggregations in BO, we didn't use any. Think we create table functions to aggregate things up to differnt levels of granularity.

In my opinion if you have a BO team in the company, it is a route you could use as well as SSAS. Allows you to migrate away from BO into a SSAS world..

The improvements of Excel 2007, reporting services in 2008 and proclarity all make BO look basic and dated.

Sure if you contact Microsoft and asked them the question, they would list a load of things. Probably have a fact file on the matter.

Sorry for the long reply!

Hope that helps

Matt

|||

Matt and Thomas,

Thank you for the answers. This is great information and will go a long way for me.

-Gumbatman

business intelligence projects visual studio 2008

Ok, I installed VS2008 and open my solution built with 2005 which contains a
business intelligence project for reporting services and I get a nice error
message that says this version does not support this project type
(.rptproj). Real nice.
Does anyone know how I can get this working or if VS2008 will even support
this at all?
Or will I have to work on part of my solution in VS2008 and use the SQL
Server Business Intelligence Development Studio for the other part? that
would be a bit of a pain.
Thanks.Assuming everything works as before then what will happen is that when you
install SQL Server 2008 BI tools they will integrate with VS 2008. Note that
you have have RS 2000 report designer (integrated with VS 2003), RS 2005
report designer integrated with VS 2005 and RS 2008 Report Designer to be
integrated with VS 2008.
If you are planning on using the VS winform or webform reportviewer control
then I would do a quick test to see if the 2008 version will work with RS
2005 server. It might not. VS 2005 controls required RS 2005 server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DK" <bushido101@.hotmail.com> wrote in message
news:B8A0B74B-58FE-4581-A64D-D4F013C06447@.microsoft.com...
> Ok, I installed VS2008 and open my solution built with 2005 which contains
> a business intelligence project for reporting services and I get a nice
> error message that says this version does not support this project type
> (.rptproj). Real nice.
> Does anyone know how I can get this working or if VS2008 will even support
> this at all?
> Or will I have to work on part of my solution in VS2008 and use the SQL
> Server Business Intelligence Development Studio for the other part? that
> would be a bit of a pain.
> Thanks.