KPI measure and dimension

Hi Folks,

i have big problem. my measure is call "VALUE".

and the Dimension "TYPE" (TYPE a,b)

The kpi should compare VALUE Type a and TYPE b.

How it works?

Can everyone help me. THX Greg

It depends on what you mean by comparing the two values, but here's an example of an MDX expression (in a calculated measure, but you could easily use it inside a KPI) that compares two values - Internet Sales Amount for Fridays and Saturdays - on the same dimension:

Code Snippet

with member measures.test as

iif( ([Measures].[Internet Sales Amount], [Date].[Day Name].&[6])

>

([Measures].[Internet Sales Amount], [Date].[Day Name].&[7])

, "Friday is greater", "Saturday is greater")

select [Date].[Calendar Year].members on 0,

[Product].[Category].members on 1

from [Adventure Works]

where(measures.test)

HTH,

Chris

|||

Hi Greg,

Edit your cube in BIDS and then go to KPI's tab.

Create a new KPI and complete the form with MDX script you need for Value, Goal, Status and Trend.

There you can also set Status indicator e Trend indicator (traffic light, gauge, standard arrow and so on).

Here you can find some instructions:

http://msdn2.microsoft.com/en-us/library/ms181183.aspx

|||

thx

this code help me

([Measures].[VALUE], ( [DIM].[NAME].[MEMBER] ))

KPI Goals from dimension attributes.

Hi,

Currently I'm busy creating a Cube and I'd like to use dimension attributes as goals in my KPI's. I'm creating a cube that has facts that are related to agreements. In the agreements dimension I've stored the goals that are defind within the actual agreements with the customers. So when creating a KPI I'd like to use those goals from the dimension.

I've tried something like this: [Agreement].[Norm Percentage].CURRENTMEMBER as my Goal Expression, but it's not working as expected.

Really hope that one of you can explain me how to do it.

Thanks in advance,

The .CurrentMember function will return a member, what you should be looking at is using the .MemberValue function

eg.

[Agreement].[Norm Percentage].MemberValue

The thing you will need to think about is what you what to see when multiple or All "Norm Percentages" are in context. I don't know enough about your data to know if an average would make sense, but If it did, something like the following may be a more robust expression.

AVG(EXISTING [Agreement].[Norm Percentage].[Norm Percentage], [Aggrement].[Norm Percentage].MemberValue)

|||

Darren thanks for your reply.

I've tested you're solution and it is looking better then what I got the first time. But unfortunately the averages are not what I expected.

I'll look into it today. So I'll post a new reply at the end the day with my results.

KPI Goals from dimension attributes.

Hi,

Currently I'm busy creating a Cube and I'd like to use dimension attributes as goals in my KPI's. I'm creating a cube that has facts that are related to agreements. In the agreements dimension I've stored the goals that are defind within the actual agreements with the customers. So when creating a KPI I'd like to use those goals from the dimension.

I've tried something like this: [Agreement].[Norm Percentage].CURRENTMEMBER as my Goal Expression, but it's not working as expected.

Really hope that one of you can explain me how to do it.

Thanks in advance,

The .CurrentMember function will return a member, what you should be looking at is using the .MemberValue function

eg.

[Agreement].[Norm Percentage].MemberValue

The thing you will need to think about is what you what to see when multiple or All "Norm Percentages" are in context. I don't know enough about your data to know if an average would make sense, but If it did, something like the following may be a more robust expression.

AVG(EXISTING [Agreement].[Norm Percentage].[Norm Percentage], [Aggrement].[Norm Percentage].MemberValue)

|||

Darren thanks for your reply.

I've tested you're solution and it is looking better then what I got the first time. But unfortunately the averages are not what I expected.

I'll look into it today. So I'll post a new reply at the end the day with my results.

KPI for each member of a dimension

All the examples I have seen have set KPI's against all the data in the cube. Is it possible to do this for each member of a dimension?

Specifically, I have an application which carries out calculations for a series of 'what if' scenarios. For each Scenario I have a warning level and a trigger level. These levels are held as attrbutes for the Scenario dimension.

It seems to me that I could create a report that calculates the values for each dimension via an MDX query, but I think this should be a calculated member somewhere but I'm at a loss as to the best way to do this.

Does anyone have any ideas as to how best to approach this?

Looking into this further I think I can use the cube calculation tab to add a calculated member to the dimension. Am trying this now... watch this space...

sql

Kimball Templates

When using the Kimball templates for designing Dimension tables, there are 3
columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers.
Is the RowIsCurrent just a better faster way to find the most current recor
ds instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCu
rrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 YOn Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are
3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers
. Is the RowIsCurrent just a better faster way to find the most current rec
ords instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIs
Current
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005
N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999
Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999
Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com

Kimball Templates

When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
On Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com

key columns heeeeeeelp

hi i need some help,, i have a dimension like this:

dimgeo

fiDWHgeoid as my primary key
and these attributes

ficanalid
fidivisionid
figciaid

with thier description fields:
fcdesccanal
fcdescdivision
fcdescgcia

the problem: how can i make a unique key that includes the three id`s i listed in only one and unique key so when i browse my cube it will display the correct match.. i'm really really new in this, i saw a property which is key columns should i use it and it so.. how? please i need some help.

You can try and modify the KeyColumns property for your "fiDWHgeoid " attribute to include more than a single column into this.

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

|||

I did what you said but at the moment f processing it it comes this error:

Error 1 Cube 'Aforecubo' > Measure Group 'Fact Comite' > Dimension 'Dim Geo Afore' > Measure Group Attribute 'Fc Desc Division Afore' : Same count of key columns as in dimension attribute is required. This measure group attribute has 1 key columns, the dimension attribute has 2.

|||

This error means that you have 2 columns your dimension key based upon. But when looking at relationships to the measure group, you still have a single attribute mentioned there.

Now, you've modified the dimension key, you need to make sure you can connect your dimension to the measure group.

I will have to take back my suggestion to modify key columns. You probably need to go back to relational database and solve the problem of unique keys there.

You can make intermediate attribute keys unique by adding more columns into the Key columns collection, but for the key attribute of the dimension you need pay attention to the way data in measure group is referenced correctly.

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

|||

1.-when you create the dimension select the 3 keys columns for example A,B,C

2.-Save your dimension

3.-Edit your dimension

4.-From de data source view drag the same 3 columns that you have selected before to the hierarchy pane to create a hierarchy

5.- See in Attributes pane that now you have a 3 key atributte key and this 3 atributtes individually

This is all