Building KPI in SSAS

Hi,

I am trying to build a Kpi, based on a table like this:

Mounth | Budget

August | 1234€

October | 123€

---

Having Budget as a Measure and Mounth as Dimension.

i would like to create a Kpi using Analysis Services where, on Value Expression, i would like to point the measure for value of budget in October, and not for the value corresponding to the sum of all budget values.

How should i have the expression?

Thanks.

You can build a calculated member like 'Aggregate({[Due Date].[CalendarYearDate].Currentmember},[Measures].[Bud Amount]).

After that you can build a second calculated member like 'Aggregate([Due Date].[CalendarYearDate].[Month].&&[2004],[Measures].[Bud Amount])

My examples use a time dimension-hiearchy from Adventure Works.

For the second one I do not now if you will use a special october like october 2005.

In a third calculated member you divide these two measures and use that calculated measure as a value expression in the KPI editor.

Regards

Thomas Ivarsson

|||

Hello Maia,

You can define the value expression for the KPI like this: ([Measures].[Budget - YourFactTable],[Mounth].[Mounth].&[10])

assuming that &[10] is the key of October in your Mounth dimension table.

Hope this helps,

Artur

|||

Artur you have been a fantastic help. It worked.

Regards.

Maia

Building attributes based on calculated values

I have a measure: "# of clicks". I have a calculated measure, which is "# of clicks in last 30 days". What I'd like to do is segment my users using this "# of clicks in the last 30 days" calculated measure so that I have three different segments: "No usage in last 30 days", "Some usage in last 30 days" and "Tons of usage in last 30 days".

Here's my question...what's the best way to create an attribute in my user dimension for these usage segments? The only thing I've been able to come up with is to pre-calculate the "# of clicks in last 30 days" when I build the warehouse and then put in the "case WHEN [# of clicks]" segmentation in the dsv. I don't like this method because it will like slow down the warehouse build dramatically.

It doesn't seem like there's a way to build that logic into cube or dimension. What am I missing? I thought about trying to define "sets", but if I do it that way I can't use the segment in reports as attributes to show.

Anyone have any advice? Thanks for any help can provide,

P

You could create calculated members in the calculation script of your cube to do this sort of thing. Although it will not slow down your build times, depending on your data, the performance could be slow at runtime.

I have assumed that getting the "last 30 days" is as simple as grabbing the last 30 nonempty days, in practice it is often not this simple, other approaches would include using date functions to determine the current system date and then lagging back 30 members, or some people even build their time dimensions as they are needed, so the last member in the dimension is the last day.

CREATE MEMBER CurrentCube.User.[tons of clicks] as AGGREGATE(
FILTER(Users.UserName.members
, SUM(TAIL(NONEMPTY([Date].[Days].Members),30)
Measures.[# of clicks]) > 100
)
)

You could also create a similar expression using a custom rollup formula in your dimension table.