Hi all,



I have a calculated member that returns the "Property Value". The
scenario is that we have Properties and Accounts. One Property can be
tied to multiple accounts and one account can have multiple properties.



The problem is that when the user looks at an Account, the "Property
Value" measure must return the sum of Property Values for all properties
related to that Account.



When the user selects two Accounts (and both of them could have some
common properties), then the total Propery value related to those
accounts must be displayed (ofcourse without double counting).



My measure is:

Sum( Distinct([Property].Children), [Measures].[Current Property Value]/
IIF( [Measures].[Current Property Value Count] = 0, 1,
[Measures].[Current Property Value Count]))



Note: My fact table is at the Property and Account level. But since
a property value cannot be divided, I am storing it in the
fact table as it is (i.e. without bringing it down to the
Account level)



This thing takes forever to come back... Can someone suggest a way to
optimize this calc. ? Or may be a different way to solve the problem. I
can give more details if needed. All solutions that I have tried always
result in incorrect results. I can give more details if needed.



Thanks,

Amir


--
Posted via http://dbforums.com

Hi,

I would start with doing a test with the query log on Write to once per 1
query, and then look into the query log which aggregations it uses. I have
seen some cases where there was no aggregation for this particular
calculated member. You can add this aggregation then manully with for
example the "Partion Aggregation Utility" from SSABI or you can find a copy
of this tool at:

http://www.mosha.com/msolap/util.htm

I also sugest to look into this article that could help you:

INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY
WGID:223
ID: 304137.KB.EN-US
http://support.microsoft.com/default...b;en-us;304137

HTH,
Bas

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

Bas,



Thank you for that reply. I could not find the utility on the
http://www.mosha.com/msolap/util.htm



webpage. Can you tell me under what heading and name it appears?



I did implement the second suggestion from the article that you
provided. Thanks for that.



Is there a way to create an aggregation for a calculated member? I
thought that aggregations were created only for regular measures.





Thanks,


--
Posted via http://dbforums.com

No, there is no way to create aggs for calculated members.

Is [Measures].[Current Property Value] a physical measure (versus a calc)?
How many children might a given member of Property have? Why is Distinct
needed?

tom @ the domain below
www.tomchester.net


"ac_786" <member44944 (AT) dbforums (DOT) com> wrote

Tom,



Yes. [Measures].[Current Property Value] is a physical measure. The
Property dimension has approximately 3000 members. Distinct is what
kills the query, but the problem is that I need it.



The reason for that is multiple accounts can have the Properties that
are common and the Property Value should be a total of the distinct
Properties.



So for eg. you have Account A1 that has Properties P1, P2 and P3. And
you have Account A2 that has Properties P2, P3, P4.



The account A1 and A2 have a parent M1. When someone selects M1, they
should see P1, P2, P3, P4 and their values without double counting.
Similiarily there are 12 other dimensions that can be selected along
with the Property dimension and this same rule holds true.



Eg. Manager. A manager can manage 10 accounts. Now when someone looks at
the manager and the properties, they should again not see duplicates and
the Aggregation of the Property Value should be correct.



Let me know if there is another way to accomplish this. Any feedback is
greatly appreciated. Thanks,



Amir


--
Posted via http://dbforums.com

This is a problem with the architecture of MSOLAP. It is not meant to handle dynamic queries that rely on aggregating the data from the leaf cells at each aggregation, and because MSOLAP does not cache intermediate aggregated values from a calculated member, it ends up calculating decendents for decendents for decendents for decendents and it takes forever.

How I had to solve the problem, though, I will admit it is not pretty, is to back into the calculation from a different angle. For example:

If you want to calculate Revenue from Price * Volume, rather than storing Price and Volume in the cube, store Volume and Revenue, use your client application to calculate what Revenue should be placed in the cube, and use the cube to calculate the Price.

This solves several problems:
1. Performance is extremely FAST
2. You don't have to worry about calculating average price, because it is intrinsic in the basic formula.

However, this approach is not without problems. It forces decentralization of business rules and it complicates the overall application, especially when the price gets used in multiple measures. OUCH!

In the vicious triangle when performance reigns, this approach does work!



----- ac_786 wrote: -----


Tom,



Yes. [Measures].[Current Property Value] is a physical measure. The
Property dimension has approximately 3000 members. Distinct is what
kills the query, but the problem is that I need it.



The reason for that is multiple accounts can have the Properties that
are common and the Property Value should be a total of the distinct
Properties.



So for eg. you have Account A1 that has Properties P1, P2 and P3. And
you have Account A2 that has Properties P2, P3, P4.



The account A1 and A2 have a parent M1. When someone selects M1, they
should see P1, P2, P3, P4 and their values without double counting.
Similiarily there are 12 other dimensions that can be selected along
with the Property dimension and this same rule holds true.



Eg. Manager. A manager can manage 10 accounts. Now when someone looks at
the manager and the properties, they should again not see duplicates and
the Aggregation of the Property Value should be correct.



Let me know if there is another way to accomplish this. Any feedback is
greatly appreciated. Thanks,



Amir


--
Posted via http://dbforums.com

Hi,

With setting the aggregations for this calculated member did I mean by
using it with a query like selecting it in excel as a measure (data field).
You can optimise this with adding aggregations manually. Sorry i thought
that the tool was available at http://www.mosha.com/msolap/util.htm. But
that is an outdated version the 1.1 you should use 1.2 from SSABI you can
download SSABI from http://www.microsoft.com/sql/ssabi/ if you still want
to look at this. The tool is then available under \Microsoft SQL server
Accelarator for BI\tools\partaggutil.exe

HTH,
Bas

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

Steven,



I don't think that this will solve my problem. The problem in my case is
that I do not know what the measure Property Value should contain for an
Account. It depends on the number of properties that are related to that
accout. And worse, the Account dimension has a 4 level deep hierarchy.
In that case, when someone selects say the Top most level, I have to
find out all the accounts under that level and then all the distinct
properties related to those accounts, and then their values and then the
sum of those values.





I could not possible store the Property Value in a dimension, as I do
not know what the value is. I can see how it would work when the formula
is simple (Revenue = Price * volume), but in this case I don't think it
will work.



Let me know if I am not understanding you correctly here...



Thanks.

amir





Originally posted by Steven Garno


--
Posted via http://dbforums.com