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.

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

Keep records that contain certain attributes

Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"

suggestions?

thansk in advance

km

Hi km

I am having a bit of trouble understanding what data you are working with.
Once this is clear perhaps I can help.
Does your data-set look like the following?

Primary Key | Problem
-
1 Drugs
1 Alcohol
1 Vandalism
2 Theft
2 Drugs

And you want to display all rows with PrimaryKey = 1 (since it includes alcohol), but not any rows with Primary Key 2?

If that is the case it will definitely be easiest to do this in you query itself, not in the report.
Something like:

Select * From Table WHERE PrimaryKey IN
(SELECT PrimaryKey From Table WHERE Problem = 'Alcohol'

Cheers
Mark
|||

thanks Mark

Yes this is similiar to my data found out that there is a unique key for each atttribute so all is resolved

thanks for your response

km

Keep records that contain certain attributes

Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"

suggestions?

thansk in advance

km

Hi km

I am having a bit of trouble understanding what data you are working with.
Once this is clear perhaps I can help.
Does your data-set look like the following?

Primary Key | Problem
-
1 Drugs
1 Alcohol
1 Vandalism
2 Theft
2 Drugs

And you want to display all rows with PrimaryKey = 1 (since it includes alcohol), but not any rows with Primary Key 2?

If that is the case it will definitely be easiest to do this in you query itself, not in the report.
Something like:

Select * From Table WHERE PrimaryKey IN
(SELECT PrimaryKey From Table WHERE Problem = 'Alcohol'

Cheers
Mark
|||

thanks Mark

Yes this is similiar to my data found out that there is a unique key for each atttribute so all is resolved

thanks for your response

km