Customer Profitability

Customer Profitability

Hi How can I use MS SQL server 2005 analysis services for measuring the Customer Profitability Index

Pl suggest if you have any Data model and any special algorithm that can be used to measure the Customer Profitability

Thanks In Advance

Rajesh Ladda

Analysis Services is a product that serves as a platform for your analytical applications. Using Analysis Services you are able to model complex business rules of your organization.

Your question is a bit vague. If you are looking for examples of how to build multidimensional model and then use it to analyze your data, I suggest you first start from going through tutorial that comes with Analysis Services installation. Then take a look at the sample project: AdventureWorks and see how it implements calculations.

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

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Custom Semi-Additive Member using Analysis Service 2005 Standard Edition

[Edited For Clarity]

I’m using Analysis Service 2005 the standard edition:

I’m trying to create a custom semi-additive measure through MDX. My time dimension has month granularity with years on top of that. Basically, I’m trying to create an average the months while summing the averages across non time dimensions.

My best solution was using the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]))

the problem with the above code is that it does not include empty months of non empty years. That is, if an attribute exists for a month in 2004, I want to see all the months of 2004 as part of the denominator. However, if an attribute does not exist for any months in 2004 I want to see Null.

I also tried the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]), includeempty)

but the statement could not be parsed due to too many arguments in the avg function. Can anyone clarify what the problem is here?

Another way of doing this is using the count member. Basically I would count all the periods in the year and use that as a denominator to find the average:

sum([measures].[measure_to_aggregate]) / count (descendants ([time].[hierarchy].currentmember,[time].[hierarchy].[month], self))

The problem with this statement is that I can’t differentiate between empty years and non-empty years, so the code computes the average for all years in the dimension.I also tred using the nonempty function around descendants, but it excludes both months of empty and non-empty years.

Any ideas?

DNA,

You are saying that statement below is OK, and one problem is that it does not include empty cells.

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(measures.[adjusted headcount]))

Try to use CoalesceEmpty:

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

This will replace NULLs with 0.

Vidas Matelis

Edited: Added ,0

|||

Thanks for replying Vidas Matelis,

I have edited my post to make the previous post much more clear.

The issue with using Coalesce is that I have more years in my dimension than the cube itself.

Therefore I would not want to replace the null values of the addtional years in the dimension with zeros.

|||

Please note that includeempty is an option for Count(), but not for Avg(). How about using Count() with Filter(), like:

[measures].[measure_to_aggregate] / count(Filter(descendants(

[time].[hierarchy].currentmember, [time].[hierarchy].[month]),

Not IsEmpty(([measures].[measure_to_aggregate], [time].[hierarchy].Parent))))

|||

DNA,

I would assume that for years where you have no data, you would get result as 0. So what if you use IIF statement and replace 0 with NULL? Would that work?

IIF(avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0))) = 0

, NULL

,avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

)

Vidas Matelis

|||

Thanks for the replies and ideas. For clarification:

To Deepak Puri,

I tried using filter but it excluded all empty values, meaning the empty values for periods for the years within the cube were exclude as well. The way I wanted to approach this is by excluding all empty values for the years that are not in the cube while including the empty periods for the years that are in the cube.

To: Vidas Matelis,

And an iif statement works very similiar to case statements and coalesceempty. When I used the coalesceempty I had to make a seperate calculated measure to use it because there was an error saying too many arguments in the avg() function. I could certainly try using the code you have provided but I would think that this code would also exclude all empty values instead of selectively excluding values for certain years.

|||

Ultimately, the code will have to know somehow that some years are considered "empty" and some are not. You are the only one who can tell what the criteria is. It could be as simple as hardcoding 2004 as the first non-empty year, or, perhaps you will have more complex criteria, i.e. year in which all months are empty for Root() of everything else, or something else. Let's assume that you created named set with all the months from the years that you consider "non-empty", i.e. something like

CREATE SET NonEmptyMonths AS Exists([Time].[Month].[Month], [Time].[Year].[2004] : NULL )

I.e. here I hardcoded 2004 as the first "non-empty" year. After that you can take any solution proposed above and use

Intersect(NonEmptyMonths, Descendants([Time].[Hierarchy].CurrentMember, [Time].[Hierarchy].Month))

Instead of using Descendants directly. You won't have to filter empty cells anymore, of course, because Intersect will take care of it.

|||

Would there be a way without hardcoding it?

The reason being is that I want it to be automated and to account for changes in the time dimension (ie. additional years and periods being added).

|||Please see my previous message - uou have to tell us the criteria - which years are considered to be "populated". Is it a member property of the Year attribute ? Is it year which has at least one non-empty month at the Root ? Is it something else ? Once we know you business logic we can translate it into MDX, but without knowing it - not much we can do.|||Mosha, sorry for the late reply, the years are considered to be populated if there is at least one non-empty months.|||

OK, then the CREATE SET statement can look like something around the following theme:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty( [Time].[Hierarchy].[Year].MEMBERS ), [Time].[Hierarchy].[Month] )

|||

Something along the following lines:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

|||

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )