Hi All
I do think I like to make life difficult sometimes.

I have put together a very useful OLAP application that calculates
future stock balances (quantity and value) based on the business supply
and demand forecasts.

Now I would like to filter the answer to classify the results based on
number of weeks stock cover.

I'm trying to construct everything in Analysis Services and deploy
through Excel.

I have a time (weeks) dimension and a products dimension. I have a
calculated measure [stock cover] equals [closing stock] / [average
weekly demand]. I want to group these results into 3 slices - Items
with stock cover under 5 weeks, those with stock cover over 12 weeks
and the rest.

I can partially achieve this by deriving separate measures for each
group. Here is my calculated measure for items under-stocked:

[Understocks]=
iif (isleaf([products].currentmember),
iif([stock cover]>0 and [stock cover]<5,[closing stock],null),
sum([products].currentmember.children,[Understocks]))

This filters the products to show just those items with a stock cover
of 5 weeks or under.

Performance is extremely slow. I have about 2000 products divided into
30 groups. A refresh of the view in Excel takes about 10 minutes and
brings the server to its knees.

There must be a better way!!! Any ideas please??

Regards
Ian Bamforth

Hi Ian,

Could expound a bit further on the underlying data schema (fact table,
etc), and on the [closing stock] and [averaqe weekly demand] measures?
Presumably the latter are calculated themselves, so their contribution
to query response time could be significant.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak
Yes you're right. They are calculated - but they are simply
aggregations of two or three other measures or cumulatives across time
- and calculate very quickly.
(note that my time dimension is relative to the present so I base
averages on a horizon 52 weeks from now)
Thus:
My fact table has two measures - quantity and value.
Dimension keys within the fact table are transaction type, product code
and week number
There are 40,000 rows in the fact table

[all transaction types] = [opening stock] + [supply] - [demand]
[closing stock] =
sum( periodstodate([time].[(all)],[time].currentmember),
([value],[all transaction types]) )

[average weekly demand] =
sum( periodstodate([time].[(all)],[all time].firstchild.lead(52) ),
([value],[demand]) ) / 52

[stock cover] = iif( [average weekly demand] = 0, null, [closing
stock] / [average weekly demand] )

I get reasonable results - (answer in 15 seconds) if I change my
earlier calculation to
[Understocks]=
iif (isleaf([products].currentmemb*er),
iif([stock cover]>0 and [stock cover]<5,[closing stock],null), 0)

Here, I allow the aggregations to produce zero results and generate
value totals in Excel...
If I leave the subtotals as null, Excel gets horribly confused and
dies...

Does this help you to see my problem more clearly?


Ian Bamforth

Hi Ian,

From the details you provided, one obvious optimization comes to mind:
since [average weekly demand] is always calculated based on the 1st 52
weeks of your (forward) time dimension, why not define a (52-week) year
level above the week level: (like [Year].[1], [Year].[2], etc)? Then,
[average weekly demand] could be simply defined as:

([value], [demand], [Time].[Year].[1]) / 52


Of course, [closing stock] may still be slow to compute, depending on
how far out in the future you typically look. And there are ways to
improve the performance of "Closing Balance" type measures which have
been discussed in this group (in case you wish to pursue that
possibility).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepack
The closing stock measure is fortunately fast enough to calculate.
Because I have a flat calendar with no groupings, closing balances and
periods to date are in effect the same.

It appears that this combination of leaf level and aggregation
calculations, which I use to filter and group my list of products, is
creating the frustration of 10 minute recalc times - not what you
expect from OLAP!

You will appreciate that I am trying to track the changes in status of
a product with the passage of future time - thus some items that are
over-stocked now will potentially become correctly stocked in future as
they are consumed by demand, whereas others remain overstocked.
Valuing and summing these items provides a potentially great tool for
financial planning.

It looks rather as if OLAP cannot provide the complete solution here.
A conventional pivot table just might provide a more dynamic solution.
What do you think?

Hi Ian,


I haven't done much reporting with regular pivot tables, but the should
be someone in the group who can comment.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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