Re: Performance problem
- From: "Debbus" <debbus@xxxxxxxxx>
- Date: Thu, 28 Dec 2006 18:22:00 +0100
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_@xxxxxxxxxxxxxx> wrote in message news:eNONNgoKHHA.5000@xxxxxxxxxxxxxxxxxxxxxxx
can you post the MDX query of your calculated member?
how the cube is aggregated?
"Debbus" <debbus@xxxxxxxxx> wrote in message
news:OcgirkmKHHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Performance problem
- From: Jéjé
- Re: Performance problem
- References:
- Performance problem
- From: Debbus
- Re: Performance problem
- From: Jéjé
- Performance problem
- Prev by Date: RE: The attribute key cannot be found
- Next by Date: Re: Performance problem
- Previous by thread: Re: Performance problem
- Next by thread: Re: Performance problem
- Index(es):
Relevant Pages
|
