Hello all,
I'm in trouble with an Analysis Services inventory cube with three
dimensions (stores, productc, time) and one measure (quantity)
In my fact table I have sparse information about quantity in stock. Only
when quantity change the system record a row with the new quantity in stock.

For example (S=Store, P=Product, T=TimeCode):
S P T Quantity
1 A 1 1000
1 B 1 1000
1 C 1 1000
1 A 2 2000
1 B 3 2000
1 A 4 1000
1 C 4 2000

From the system should present the following quantity in stock:
T Quantity
1 3000
2 4000
3 5000
4 5000

Supposing that my time dimensions is flat (All, TimeCode), I would like to
get at (All) level the last child (4) which is last quantity in stock.

I already solved this problem and empty cells problem with a calculated
member like the following:
QuantityNotEmpty:
IIF([Time].CurrentMember.Level is [Time].[TimeCode],
([Measures].Quantity,
Tail(Filter({OpeningPeriod([Time].CurrentMember.Level,[Time].[All
Time]):[Time].CurrentMember}, Not IsEmpty([Measures].Quantity))).Item(0)),
([Measures].QuantityNotEmpty, [Time].LastChild))

(pay attention because if you use the CoalesceEmpty technique you can get,
like me, the bug with stack max size as from KB article Q311528)

With QuantityNotEmpty I can drill to the cube leaf level (store, product and
time leaf level) and found all cells with the correct quantity in stock.
For example I can read for product A, store 1 this values:
P T QuantityNotEmpty
A 1 1000
A 2 2000
A 3 2000 <-- this value is virtual, computed by the calculated member
A 4 1000

Now the problem:
QuantityNonEmpty is based on Quantity measure and when it is not executed on
the cube leaf level (store, product and time leaf) it gets Quantity from an
aggregate. This is incorrect because for example Quantity at All Products
level for period 2 and store 1 is 2000 instead of the right value 4000 which
comes from summarizing QuantityNotEmpty at the leaf level.

I tried to solve this problem adding Custom Rollup Formulas in Products and
Stores not leaf levels like RollUpChildren([Products].CurrentMember,[+]) but
I suppose this is too resource consuming because I'm still waiting the
result from the query.

I tried to solve the problem staring from the fact table creating a pointers
table for empty cells giving to the cube data for every timecode but as you
can understand this is both time consuming on the OLTP system to create the
pointers table and on the OLAP cube which becames too big.

Consider also these are example data, in production I have 40 milions rows
fact table with 500000 products and 200 stores detailed on a weekly base.
Dimensions will also be much more.

I hope someone founded a performant solution to this problem, anyway I think
this post can help someone who is starting to do a similar job.

Thank you for your help in advance.

Stefano

I solved the problem transforming fact data from snapshot to delta. In the
first period of the year I have snapshot data for all existing combination
of products and store and in all other periods only delta information.
In such a way Quantity measure can be used also at aggregated levels.
I used a recursive Calculated member to display snapshot information for
every period; this calculated member recursively sum data with its
predecessor only if its predecessor is in the same year. An optimizied
version of this calculated member is also able to sum data by jumping on
hierarchy like in this example:
second week of dicember is: second week + first week + november + october +
third quarter + first semester

I hope this will be helpfull to someone

Stefano
MCSE, MCDBA, MCT


"Stefano Dell'Orto" <stefano.dellorto (AT) email (DOT) it> ha scritto nel messaggio
news:OVcG6m5AEHA.2576 (AT) TK2MSFTNGP11 (DOT) phx.gbl...