I have an MDX formula that uses PREVMEMBER to find a previous balance:

[Measures].[Sched Bal Lag] =

iif(
IsEmpty(
([As Of].PrevMember,[Measures].[Prin Sched Bal])
),[Measures].[Prin Purch Bal],
([As Of].PrevMember,[Measures].[Prin Sched Bal])
)

The way the formula should work is if the [PRIN SCHED BAL] is empty in
the previous period, which it will be for the first period, it
substitutes the [PRIN PURCH BAL] measure. If it finds a valid
previous period balance, it uses that.

The trouble comes when it totals up. The Total or "ALL" level of the
[As Of] dimension is always empty using this formula. What is should
have is the sum of the values for each individual month in the formula
above.

Here is an example to reference:

Product As Of Sched Bal Lag Prin Sched Bal Prin Purch Bal
DEF 2000 Feb $8,778,779.67 $8,774,155.57 $8,778,779.67
2000 Mar $8,774,155.57 $12,450,786.81 $12,456,847.83
2000 Apr $12,450,786.81 $14,522,066.55 $14,533,627.49
2000 May $14,522,066.55 $11,447,702.50 $11,452,522.32
2000 Jun $11,447,702.50 $11,183,100.02 $11,190,271.07
2000 Jul $11,183,100.02 $10,918,630.15 $10,926,758.25
2000 Aug $10,918,630.15 $14,823,417.89 $14,826,938.82
2000 Sep $14,823,417.89 $11,672,437.54 $11,682,275.59
2000 Oct $11,672,437.54 $12,779,545.01 $12,787,061.04
2000 Nov $12,779,545.01 $10,367,048.60 $10,376,141.45
2000 Dec $10,367,048.60 $17,236,820.26 $17,245,968.54
2001 Jan $17,236,820.26 $19,298,177.13 $19,384,218.86
2001 Feb $19,298,177.13
Total $155,641,410.93 $155,473,888.03 $155,641,410.93

Notice the "Sched Bal Lag" total of $155,641,410.93. This is the same
as the [Prin Purch Bal] total. This is because the formula determines
this cell as "EMPTY", and substitutes the value. However, the cell
should not be empty because it is merely the total of the values
within that grouping.

Also notice that the first value in the grouping = [Prin Purch Bal] as
well. This is because there was no previous [Prin Sched Bal], i.e.
EMPTY.

How do I fix my "Totaling" problem using MDX?

BTW, the grouping I show here is by Product. This could be any other
dimension in practice and the formula needs to be flexible in that
respect.

Thanks

Kory

It's a little known fact that calcs can be recursive. Here a possible
solution for [Sched Bal Lag]:

IIF( [Prin Sched Bal].CurrentMember IS [Prin Sched Bal].DefaultMember,
SUM( [Prin Sched Bal].Children, Measures.[Sched Bal Lag]),
<your formula here> )

public @ the domain below
www.tomchester.net

"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

Thanks Tom. I think I understand what you're getting at here.

Only, I think in the formula you suggested, it should read:

IIF( [As Of].CurrentMember IS [As Of].DefaultMember,
SUM( [As Of].Children, Measures.[Sched Bal Lag]),
<your formula here> )

However, the "Default Member" in the [As Of] dimension may not be the
"total" because I have an MDX formula that locates the last month that
has data in it (using the Tail function).

So, I thought I could substitute [As Of].DefaultMember with [As
Of].[(All)] but that doesn't work...

Kory

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

Kory, the problem is that (All) is not a member -- it is the name of the
level that holds the all member. Use the member name, which by default is
All followed by dimension name, i.e. [All As Of].

public @ the domain below
www.tomchester.net

"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

Yes, that worked like a charm. Thanks for the quick response.

Tom, in your experience, would 250 partitions and 800 aggregations per
partition be a poor design for a cube? I have a cube that sources
from a fact table of 120 million records, and I partition by
year-month (eg. 200212) The cube has 45 dimensions (all shared), 12
measures, and 6 calc measures.

I recently put in a support call to Microsoft because when I modified
a calculated member formula (the one you helped me with below), when
attempting to save the cube, it took over 10 hours to save! MMC.exe
showed constant CPU activity during this time, and because I have a
processing log file associated with this cube, I inspected it and it
appeared that it was opening, refreshing the partition metadata, and
closing the partition file. It was taking roughly 2 minutes per
partition. The server has 2 933Mhz procs (non-Xeon) and 4GB RAM.

I also read a whitepaper somewhere that Microsoft claims more
partitions allows better scalability, and higher aggregation obviously
provides better performance, but 10 hours to save a small formula
change? I could only make 1 change per day. I am still awaiting PSS
to call me back, hopefully with a hotfix.

Kory

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

Kory, I've not experienced the long save problem, though the most partitions
I've ever created was "only" 60 or so. (It makes no sense for the partitions
to be the cause...?) I've no opinion one way or the other re 250 partitions
/ 800 aggs, other than a general observation that over-aggregating is a
common mistake. (In the sense of causing processing to take too long, with
marginal runtime benefit.)

Be aware that the primary scalability benefit of partitions is processing,
not runtime. Though there can be small runtime benefits.

public @ the domain below
www.tomchester.net

"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

FYI:

PSS confirmed this behavior is by design. They were curious why I
chose 865 aggregations for each partition, as they felt the
performance I may receive would be only marginal, and choosing less
aggregations (I removed all aggregations from every partition and was
able to save in only 2 minutes) would improve the "save" behavior and
not impact the query performance significantly.

Also, they noted that I had partitioned by Month, and the dimension
count for the dimension I partition by was 2000+, which should be set
to 1 because only a single member would exist per partition, and would
yield inaccurate aggregation designs, so I am in the process of
correcting that design issue.

They indicated that improving the time to save the cubes would be too
complex and not worth it for AS2K, and more improvements will be
coming in Yukon.

Kory

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote