Hi group,

I've been experiencing performance problems in one of my forecasting cubes.
The cube has 6 dimensions and one measure. The problem is associated with a
calculation involving price, quantity and revenue. Revenue is defined as an
account and needs to be calculated at leaf levels only (price x quantity),
and then aggregated upwards to non-leaf levels. I have created a calculated
cell definition to facilitate this processs by scoping for only leaf level
cells and then applying the calculation, however, the performance is
horrible. Analysis Manager just freezes for long periods.

Only two of the six dimensions have a significant number of members (15,000
and 800 respectively). The remaining four have less than 10 members each.

Does anyone have suggestions for this type of calculation? Is it even
feasible to do within MSAS2K, or do I need to stage it first and then write
only the resulting values back to the cube? The last option is not really
feasible for what I'm trying to do.

Thanks

AS 2005 has several dedicated optimizations for this kind of scenario, but
there are ways to achieve relatively good performance even in AS 2000.
Could you please include your calculated cell definition?

Another approach would be to change the fact table to a SQL view with a
computed column (serving as source for the Revenue measure), so that the
computation gets done very fast at cube processing time.

--
Hope this helps

Marius


"SH Woo" <SHWoo (AT) discussions (DOT) microsoft.com> wrote

Hi Marius,

Thanks for responding!

I am not ready to go to MSAS2005 yet, and using a derived measure is also
not an option since my tool is mainly for planning. The fact data for price
and volume does not exist yet until it is populated by the users. There is
an accounts dimension and only one measure.

Having said that, here is the MDX I've tried using with calculated cells
which has yielded very poor performance.

This is used to define the sub-cube for leaf only members:
{Descendants([Customer].CurrentMember, ,LEAVES},
{Descendants([Product].CurrentMember, ,LEAVES},
{Descendants([Promotion].CurrentMember, ,LEAVES},
{Descendants([Terms].CurrentMember, ,LEAVES},
{[Measures].[Value]},
{[Accounts].&[Rev]}

Then my calculation is simply:
[Accounts].&[PRICE]*[Accounts].&[QTY]

I'm thinking the aggregations for combinations of non-leaf members should
happen naturally after this calculation is applied. But it seems by using
this calculated cell definition, I can't even browse the cube-MSAS is
continously calculating. I'm hoping there is an easier way to do this.

Thanks,
SHW



"Marius Dumitru" wrote:

On AS 2000, please try something like this:

Create Cell Calculation x
For '([Customer].Members, [Product].Members, [Promotion].Members,
[Terms].Members, {[Measures].[Value]}, {[Accounts].&[Rev]})'
As Sum(
NonEmptyCrossjoin
(
Descendants([Customer], ,LEAVES),
Descendants([Product], ,LEAVES),
Descendants([Promotion], ,LEAVES),
Descendants([Terms], ,LEAVES),
{[Accounts].&[QTY]},
4
),
[Accounts].&[PRICE] * [Accounts].&[QTY]
)

(for AS 2005 there exists a faster and simpler approach)

--
Hope this helps

Marius


"SH Woo" <SHWoo (AT) discussions (DOT) microsoft.com> wrote

Thanks I will try it out.

But is it okay to use NONEMPTYCROSSJOIN if I have other calculated cells in
the cube? This is primarily a planning cube with an accounts dimension that
may contain other calculations. There is also a calculated member to
generate YTD numbers. Will the order of operations and logic be handled by
the engine automatically.

Thanks for your help.
SHW

"Marius Dumitru (MS)" wrote:

Indeed, NonEmptyCrossjoin doesn't take into accoutn other calculations
automatically.

You may want to carefully examine your calculation design to ensure the safe
use of NonEmptyCrossjoin, or consider migrating to SQL AS 2005.

--
Hope this helps

Marius


"SH Woo" <SHWoo (AT) discussions (DOT) microsoft.com> wrote

Hi Marius,

How come when I use NonEmptyCrossJoin in my write-enabled cube, there is a
problem with uncommitted writeback data? If I update a cell and then issue
an ADOMD.NET 8.0 command to requery the cell set I receive an error in the
ADOMDException:

"Some data has been updated since you began executing this query. - An error
occurred during attempt to get a cell value"

This problem goes away if I commit the changed values to the writeback
table. However, I don't get this error on cubes that don't use
NonEmptyCrossJoins.

Is there any way to fix this?

Thanks,
SHW

"Marius Dumitru (MS)" wrote:

That sounds unrelated to using NonEmptyCrossjoin - can't explain the
correlation you're seeing.

(By the way, I believe there are some fixes in the pipeline for a future
service pack)

--
Marius


"SH Woo" <SHWoo (AT) discussions (DOT) microsoft.com> wrote

Hi Marius,

Thanks for the feedback! Can you elaborate on the fixes? Are you referring
to a future update for Analysis Services 2000 or ADOMD.NET?

The correlation between NonEmptyCrossJoins and the query errors I'm
receiving are quite strong because if I use the same cube and remove the
calculated cells involving NonEmptyCrossJoin my error goes away during
writeback. If I don't commit the data then it's not in the writeback table,
only in PTS cache, so perhaps that causes a problem. Could that be the case?

Regards,
SHW

"Marius Dumitru (MS)" wrote: