Friday, March 30, 2012

optimizing MDX?

Has anyone any knowledge of optimizing MDX?
I have several MDX queries I have inherited, which take (some) in excess of
a minute to return.
I have been through Spofford's book and others. None seem to address a best
practices approach.
Any suggestions.
Thank youWhat do you have?
there are some practices what to avoid...
"rick" <Cadian31@.Earthlink.net> wrote in message
news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> Has anyone any knowledge of optimizing MDX?
> I have several MDX queries I have inherited, which take (some) in excess
of
> a minute to return.
> I have been through Spofford's book and others. None seem to address a
best
> practices approach.
> Any suggestions.
> Thank you
>|||Here is an example.
The date dimension I know is baddly designed without any true unique members
at any level. This needs to change.
When I remove the filter for the sum > 0 the query is quite quick.
Otherwise it is sluggish....
any suggestions regarding the mdx structure. I am looking for a short term
improvement until I can re-design the cube.
Thanks in advance...
WITH
SET [DateRangeSpan] AS
'{ [DueDate].[YQMD].[All Dates].[2004].[Quarter 1].
[February].[26]
:[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[Ma
rch].[25] }'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]} '
SELECT
{[OrgUnit].&[1-990], [OrgUnit].&[1-990].CHILDREN} DIMEN
SION
PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FILTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [Activity].[Activity].Members,
[DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores
Complete],[Measures].[% Complete]})
, Sum({Axis(0)}, [Measures].[Raw Stores
Included]) > 0
)
, 0, 150
)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID],
[Activity].[Activity].[Process ID], [Activity].[Activity
].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity
].[Process
Name],
[Activity].[Activity].[Activity Due Date],
[Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]
"Andrej Hudoklin" <andrej.hudoklin@.add.si> wrote in message
news:OlgIkR3IEHA.3556@.TK2MSFTNGP10.phx.gbl...
> What do you have?
> there are some practices what to avoid...
>
> "rick" <Cadian31@.Earthlink.net> wrote in message
> news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> of
> best
>sql

No comments:

Post a Comment