Hi,

I've built some simple cubes, but now i'm stuck in SSAS 2005.
I'm trying to build a retail cube with 3 Dimensions and 1 Fact.
D1: Store -> Area -> Country
D2: Sku -> ProductLevel4 -> Level3 -> Level2 -> Level1
D3: Week -> Year
F: StockQuantity, SoldQuantity

Attribute relations on dimensions are set.

I need to calculate the following: Sum of last 4 weeks of sold quantity divided by the last 5 weeks of average stock quantity, for
every store/sku/week (all the leaves of the dimensions).
Based on that calculation, i want to count the number of times it is above some number (say >4).
The performance on my test database (5000 fact rows) is good, but in production (say 10 million rows) it sucks.

This is what i've tried:
Scenario1:
Added a create member (or create cell calculation) to the calculations tab of the cube-editor. Only to find out this is used at
query-time.

Scenario2:
Added a new named calculation to the Facttable in DataSourceView. But i cannot use MDX as the expression. At least not the
MDX-functions that i want to use.

Scenario3:
Create a view on SqlServer to do the calculation and use that in the cube. That works, but why let SqlServer do all the work that
should be done by AS?
Besides, MDX has some function (TopPercent) that will be dificult to implement in SQL.

Does anyone know this should be done in AS2005?
How can i add an (MDX) calculation on every row in the fact table at process-time and speed up query-time?

Debbus

can you post the MDX query of your calculated member?
how the cube is aggregated?

"Debbus" <debbus (AT) chello (DOT) nl> wrote

Sure

Calc1:
((Sum ( [dimDate].[Week].CurrentMember.Lag(3) : [dimDate].[Week].CurrentMember, [Measures].[SoldQuantity] ))
/
(Sum ( [dimDate].[Week].CurrentMember.Lag(4) : [dimDate].[Week].CurrentMember, [Measures].[StockQuantity]) / 4));

NumberItemsAbove4:
Count(
Filter(
NonEmptyCrossjoin(
Descendants([DimDate], ,LEAVES),
Descendants([DimSku], ,LEAVES),
Descendants([DimShop], ,LEAVES),
3
),
[Measures].[Calc1]>4
)
)

Debbus


"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

the formula is more like NumberItemsDaysShopsAbove4

well... nonemptycrossjoin... try to replace it by nonempty

nonempty(
Descendants([DimDate].currentmember, ,LEAVES) *
Descendants([DimSku].currentmember, ,LEAVES) *
Descendants([DimShop].currentmember, ,LEAVES)
, [Measures].[StockQuantity])

(or use the SoldQuantity measure)

maybe somebody else will have better formulas to share with us.


"Debbus" <debbus (AT) chello (DOT) nl> wrote

I don't think the formula is the problem (but i could change the name ;-) ).

This is what i've done:
- In DataSourceView created a named calculation 'calc1'.
- In the cube created a measure (sum) on 'calc1'.

First, i gave it a value of '1' in the named calculation. Processed the cube, and it worked great.
It gave me (instantly) the number of records.
(SELECT [Measures].[Calc1] ON COLUMNS, [Date].[ActualDate] ON ROWS FROM MyCube).
So far so good.

Next, i've tried to update the value of 'Calc1' during processing of the cube.
Because i want to understand the technique, i try updating the value from another measure.
I have another measure 'Stock' in my fact-table, so i want the number of items with Stock > 10.

This is what i tried:
CALCULATE;
SCOPE ([Measures].[Calc1]);
THIS = Count(
Filter(
NonEmptyCrossjoin(
Descendants([DimDate].CurrentMember, ,LEAVES),
Descendants([DimSku].CurrentMember, ,LEAVES),
Descendants([DimShop].CurrentMember, ,LEAVES),
3
),
[Measures].[Stock]>10
)
)
END SCOPE;

If i process the cube with 1 SKU, then it works. But with all the SKU's, no performance...
To me, this is strange, from the documentation i understood that this assignment is done during processing (checked with adding
breakpoints). So it should return an answer (even if the answer is wrong).

I'm missing something, but as i said, i have little experience with this (more a RDBMS-guy ;-) ).

Debbus


"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

have you tried with the nonempty keyword instead-of nonemptycrossjoin?
have you aggregated your cube correctly?

can you describe what is the purpose of the formula? (and what is the
definition of this formula (end user definition))
maybe we can found another way to produce the result.


"Debbus" <debbus (AT) chello (DOT) nl> wrote

Yes, i have tried the nonempty.

This is my problem:
The formula is to calculate the number of weeks that a product takes to sell its stock. We have several formula's for other
(different) indicators.
If this is above x weeks, than to product isn't selling good. It takes too long to sell the stock.
Below x weeks, then it does sell good.
A product is a SKU in a shop.
So i want to know the products that sell bad (> x weeks) for every 'aggregate' in the shop-dimension and product dimension.
If i select a shop or an sales-area (shop dimension) i can see if there are any shops that need attention.
If i select a sku or product-group (product dimension) i can see if there are any products/sku... you'll get the idea.
And of course any combination.
The time-dimension needless to say is to watch any trends.

My idea was that if i put on the 'leaves' a 1 for a bad product and a 0 for a good product, then i can simply sum/aggregate all the
'bad' products.

Debbus


"Jeje" <willgart (AT) hotmail (DOT) com> wrote

have you try to create a view which return 1 row by combination of week /
sku / store which meet your requirements and then create a measure (a count)
based on the view?

other solution:
if you have multiple threshold values to test, create a view which return
the combination of week / sku / store by rule and create a new dimension
"Threshold rule"
create a new column which return an Int or BigInt which is the combination
of the 3 keys:
Week + SKU + Store (called WeekSKUStoreUID)
like:
2006100001152300125
where 00125 is the store, 00011523 the SKU and 200610 the week.
return this only for the combination of values which meet your requirements
(so you create a complete SQL statement)

now create a new measure group based on the view and a measure based on the
WeekSKUStoreUID column and with the DCount aggregation. (or the count
aggregation)
these aggregated results provides the fastest response time, but the
flexibility is not here and the time to create the cube is longer.



"Debbus" <debbus (AT) chello (DOT) nl> wrote

Yes, i've tried the view. That works great.
But i wanted it to try to solve it in the cube. Mainly because there maybe situations that a view cannot be created. MDX has
features like TopPercent, that will be hard to implement by using SQL.
But you're right, in this case it is a solution.
(Maybe i can try to create something in CLR/.Net)

I'm also gonna try your second solution to see how it works.
Process-time should not be the problem. This is done once a week at night and it's not a problem if it runs a few hours.

Thnx for your help and time.
Debbus


"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote