Inventory stock - semiadditive misures

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Stefano Dell'Orto (stefano.dellorto_at_email.it)
Date: 03/06/04 

Date: Sat, 6 Mar 2004 17:40:24 +0100

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

Relevant Pages

  • Re: Execution Location=3 cause AS to shutdown / restart???
    ... Overall, the size of this leaf level, the fact that you have virtual ... these dimensions?) ... and the fact you have 20 dimensions in your cube, ... me think you're not going to be hitting any aggregations with your queries. ...
    (microsoft.public.sqlserver.olap)
  • Re: Drilling Down on Details
    ... up to the leaf level of your dimensions. ... if in your cube you have only the years, then the user can't drilldown to the month or day. ... so when a user ask for the sales by year, instead of summing on the fly the days, the server will use the existing aggregation, which result in a huge performance improvement. ...
    (microsoft.public.sqlserver.olap)
  • Re: Execution Location=3 cause AS to shutdown / restart???
    ... Currently its difficult for me to know what dimensions are used or not. ... > Overall, the size of this leaf level, the fact that you have virtual ... > dimensions in your cube (have you changed the AggregationUsage properties ... >> aggregations really used in my cube. ...
    (microsoft.public.sqlserver.olap)
  • Re: oprimizing read performance from write enabled cubes
    ... So we had a couple of other small parent-child dimensions too. ... the "All" level and the leaf level. ... Reading the data from cube is a pain. ...
    (microsoft.public.sqlserver.olap)
  • Re: Dell to Sell Computers at Wal-Mart
    ... Yes, but Dimensions will outsell emachines hands down, which is exactly what ... penetration into thousands of stores all at once. ... Dell's own mail order prices for equivalent configurations. ...
    (alt.sys.pc-clone.dell)