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 you
What 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].[March].[25] }'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]} '
SELECT
{[OrgUnit].&[1-990], [OrgUnit].&[1-990].CHILDREN} DIMENSION
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
>

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

Optimization Plan Grows a Database?

I have inherited a database from another site. It starts
out at approximately 10GB for the data file. If I run a
maintenance plan with the default settings for the
Optimizations the data file grows from 10GB to 17GB GB and
the log file grows from 255MB to 6 GB.
Anyone have any explanation why this grows so big?
I assume the maintenance reindexes the databases (reorganize data and index
pages on the second tab of the maintenance plan wizard) and the database
runs under the full recovery model, so all the reindexing operations are
logged.
Jacco Schalkwijk
SQL Server MVP
"Bill Holland" <hollandwl@.state.gov> wrote in message
news:2e9801c45dc0$d96f4ab0$3a01280a@.phx.gbl...
> I have inherited a database from another site. It starts
> out at approximately 10GB for the data file. If I run a
> maintenance plan with the default settings for the
> Optimizations the data file grows from 10GB to 17GB GB and
> the log file grows from 255MB to 6 GB.
> Anyone have any explanation why this grows so big?
|||I assume the maintenance reindexes the databases (reorganize data and index
pages on the second tab of the maintenance plan wizard) and the database
runs under the full recovery model, so all the reindexing operations are
logged.
Jacco Schalkwijk
SQL Server MVP
"Bill Holland" <hollandwl@.state.gov> wrote in message
news:2e9801c45dc0$d96f4ab0$3a01280a@.phx.gbl...
> I have inherited a database from another site. It starts
> out at approximately 10GB for the data file. If I run a
> maintenance plan with the default settings for the
> Optimizations the data file grows from 10GB to 17GB GB and
> the log file grows from 255MB to 6 GB.
> Anyone have any explanation why this grows so big?
|||The Optimization runs a DBREINDEX which is logged.
Another option is to consider DBCC INDEXDEFRAG. However, you
don't need to defrag indexes unless they are fragmented to
start with. The updated Books Online DBCC SHOWCONTIG topic
has a script which will defrag only where you actually have
some fragmentation.
You should read the following article for guidelines on
defragging:
http://www.microsoft.com/technet/pro...ss2kidbp.mspx.
-Sue
On Tue, 29 Jun 2004 03:07:13 -0700, "Bill Holland"
<hollandwl@.state.gov> wrote:

>I have inherited a database from another site. It starts
>out at approximately 10GB for the data file. If I run a
>maintenance plan with the default settings for the
>Optimizations the data file grows from 10GB to 17GB GB and
>the log file grows from 255MB to 6 GB.
>Anyone have any explanation why this grows so big?
|||The Optimization runs a DBREINDEX which is logged.
Another option is to consider DBCC INDEXDEFRAG. However, you
don't need to defrag indexes unless they are fragmented to
start with. The updated Books Online DBCC SHOWCONTIG topic
has a script which will defrag only where you actually have
some fragmentation.
You should read the following article for guidelines on
defragging:
http://www.microsoft.com/technet/pro...ss2kidbp.mspx.
-Sue
On Tue, 29 Jun 2004 03:07:13 -0700, "Bill Holland"
<hollandwl@.state.gov> wrote:

>I have inherited a database from another site. It starts
>out at approximately 10GB for the data file. If I run a
>maintenance plan with the default settings for the
>Optimizations the data file grows from 10GB to 17GB GB and
>the log file grows from 255MB to 6 GB.
>Anyone have any explanation why this grows so big?