Hello:

I have a Parent-Child Dimension called [Customers]
that is pretty large (roughly 200,000 members).
I also have a calculated measure called [Measures].[How Many].

I would like to have the number of customers that have
the [Measures].[How Many] > 0.

I have tried various expression but most are pretty slow
due to the PC [Customer] dimension and the size of the cube.

Does anyone have an idea what would be the most effective
way to calculate this?

Best regards,
Mario

I would put the conditional counting into a view that underlies your
parent child dimension. If how many is greater than zero then output
a 1 else a 0 inside your view. Then you could create a new cube that
consisted of just your parent-child dimension and summed up this new
conditionally counted measure. You could then via virtual cubes tie
this cube and your other cube together. In the virtual cube, you would
need to create a calculated measure that wrapped your new measure in
the ValidMeasure function so that it can be displayed even when it's
sliced my dimension that are not in its base cube.

This should give you excellent performance as the measure is now
completely aggregatable.

Hi OLAPMonkey

Your idea is excellent but I also have a Time dimension. Our data
is at the daily level. If I did as you suggested, and a user were then
to aggregate to monthly levels, for example, the sum would
include the same customer many times (one for each that we have data).

I would then need a distinct count...

Mario

"OLAPMonkey (http://olapmonkey.blogspot.c" wrote:

Hi Mario,


Can you explain the the calculated [Measures].[How Many] - is it related
to cube base measures, in a way that the condition: [Measures].[How
Many] > 0 could be derived?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak:

The calculated [Measures].[How Many] is a ValidMeasure() coming from a base
cube. The meaning behind it, is it contains "how many" of a certain product
the given customer has in stock. This data comes in on a daily basis.

The idea is to be able to count the number of customers that have a certain
product(s) in stock for an arbitrary time period (or inversely, which the
number
of customers without the product).

Regards,
Mario

"Deepak Puri" wrote:

Hi Mario,

Assuming that you need closing stock (ie. Last Non Empty logic), then
here is an approach for faster response:

- Add a computed binary column to fact table of base cube
(can be via view), that indicates "InStock"; and add "InStock" (Yes/No)
dimension to base cube.

- Suppose base measure of base cube is "StockCount", then define a
calculated measure like "StockedCustomers":
Tail(NonEmptyCrossJoin({[Customer].CurrentMember},
{[Product].CurrentMember},
Descendants([Time].CurrentMember, [Time].[Day]),
{[Measures].[StockCount]}, 3))), {[InStock].[Yes]}, 1))
This will count all customers with stock of the selected product at the
close of the selected time period.

Of course, this will only work with single-member selections on
[Product] and [Time] dimensions.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***