Calculate measure from a dimension by creating measure group from dimension

Question

Hi all,

I have a dimension called 'Member ID' with unique ids linking to a fact table(FactMember). Also, the dimension table has a column 'Salary'. One member has one salary only.

The fact table contains repeated Member ID keys in the rows. 

Now, i want to create a measure called 'Average Salary' which will be the sum of salary of individual members(salary of one member counted only once if the member comes into data of consideration)  divided by the total members count(can be obtained by distinct count from fact table, FactMember).

Please note that i could easily calculate this measure populating the salary column in the fact table joining on unique member id's and applying the logic to consider only one salary for a member(maybe taking MAX() of the salary for individual member) and dividing by member count). This result in a very low performance.

So, i want to know if it is possible to calculate this measure from the dimension column itself.

Please help!

Thank you all in advance,

Anish Tuladhar

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 1

If Salary is a property of the Member ID in the dimension, you can create a calc member [Salary] that is defined as

CREATE MEMBER CURRENTCUBE.[Measures].[Salary] AS
[Member ID].[Member ID].Properties("Salary", TYPED);

Then you can use it in the average salary calculation

hth

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 2

with

MEMBER [Measures].[salary11] AS  [Member ID].[Member ID].Properties("salary", TYPED)

SELECT {
[MEASURES].[salary11]

 } ON COLUMNS ,      

{ [Cost Band].[Cost Band Rolling 12 Months].[All Cost Bands].CHILDREN } ON ROWS        

FROM [Cube] 

This does not work at all. NULL values are displayed in the measure 'salary11'. Please note that the dimension Member ID has only one attribute Member ID and i have created the hidden attribute for using properties to get salary.

Please respond.

Anish

                  

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 3

In the query above, the default member of Member ID dimension is being used, and that is the All member. The Salary attribute cannot be determined at that level. Try this query and you should see the salary values

with MEMBER [Measures].[salary11] AS  [Member ID].[Member ID].Properties("salary", TYPED)

SELECT {[MEASURES].[salary11] } ON COLUMNS ,      

{ [Member ID].[Member ID].[Member ID].MEMBERS } ON ROWS        

FROM [Cube] 

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 4

Naveen,

Yes, the salary seems visible when Member ID dimension is selected. But, my calculated measure should work for all the dimensions, even though member id is not selected.

The member IDs under slice condition should automatically be considered, if this measure needs to be calculated.

Is this possible?

Thanks for all your help.

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 5

To do that, in your calc member for the [Average Salary], you will have to SUM the [salary11] measure across the Member ID dimension to get the numerator

Something like

SUM(EXISTING [Member ID].[Member ID].[Member ID].MEMBERS, [Measures].[salary11])

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 6

 

Hi Naveen, 

I have created the calculated member as :

MEMBER [Measures].[Salary11] as
 SUM(EXISTING [Member ID].[Member ID].MEMBERS, [Measures].[tempsalary])

Here, tempsalary is the SUM measure of the salary column in the dimension Member ID.

But, this is still not working. Seems like dimension Cost band has no link with the measure tempsalary. The All level value is displayed across all Cost band dimension members.

Please let me know your thoughts regarding this.

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 7

The dimension Cost band need not have any link to the measure. They are related via the fact table.

Try this and see if it works

MEMBER [Measures].[Salary11] as
 SUM(NonEmpty([Member ID].[Member ID].MEMBERS, <<DistinctCount Measure>>), [Measures].[tempsalary])

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 8

The dimension Cost band need not have any link to the measure. They are related via the fact table.

Try this and see if it works

MEMBER [Measures].[Salary11] as
 SUM(NonEmpty([Member ID].[Member ID].MEMBERS, <<DistinctCount Measure>>), [Measures].[tempsalary])

---

-Remember to mark as helpful/the answer if you agree with the post.

Thanks Naveen!

This was a bang !!! It works. Thanks for all your help.

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 9

Hi Naveen,

I am working on a big data set now. So, the logic to select the members in

MEMBER [Measures].[Salary11] as
 SUM(NonEmpty([Member ID].[Member ID].MEMBERS, <<DistinctCount Measure>>), [Measures].[tempsalary]) is giving me performance issues. How can i remove this nonempty function and  [Measures].[tempsalary] from here??

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

Answer 10

Try using the EXISTS function, and use a measure other than the DistinctCount measure. That should help with the performance issue

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 11

Hi Naveen,

First of all thanks for your support.

But, my issue does not seem to be resolved this easily. The solution you proposed as:

MEMBER [Measures].[Salary11] as SUM(NonEmpty([Member ID].[Member ID].MEMBERS, <<DistinctCount Measure>>), [Measures].[tempsalary])

works but is quite low in case of performance, which i am really concerned of. This is because the <<DistinctCount Measure>> or any simple measure will need to scan the big table FactMember either ways. I traced the query and found this happen due to which performance is higher than the previous technique. So, i am thinking of decreasing the rows of this fact by taking the distinct values for each member and calculate average age as simple average. This might result in data loss ... because a member needs to have only one row ... but this is the one i have now.

Thank you,

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)