February 11, 2016 at 1:50 am
Hi everbody,
i have a mdx-query which works on model level without any problems.
But if i switch to unit-level (round about 50 Mio rows) the query runs always in a timeout.
Is there maybe a query bottleneck which can be optimized?
with member [Measures].[QtyTotal] as [Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
Member [Measures].[QtyInWarrantyLastDayOfMonth] AS
(
[Dim Calendar].[Calendar].CurrentMember.LASTCHILD
,[Measures].[QtyInWarranty]
)
Member [Measures].[QtyInExtensionLastDayOfMonth] AS
(
[Dim Calendar].[Calendar].CurrentMember.LASTCHILD
,[Measures].[QtyInExtension]
)
SELECT
{[Measures].[QtyInWarrantyLastDayOfMonth],
[Measures].[QtyInExtensionLastDayOfMonth],
[Measures].[QtyInWarranty],
[Measures].[QtyInExtension],
[Measures].[QtyStdIn],
[Measures].[QtyStdOut],
[Measures].[QtyExtIn],
[Measures].[QtyExtOut], [Measures].[QtyTotal]} on 0,
Filter(NONEMPTY(CROSSJOIN(
{[v Dim Unit Model 4IB].[Id Unit].[Id Unit].members},
{[Dim Country].[Id Country].[Id Country].members},
{[Dim Calendar].[Calendar].[Id Calendar Month].members}
),
[Measures].[QtyTotal]
),
[Measures].[QtyTotal] > 0) on 1
FROM [InstalledBaseCS_Serial]
WHERE [Dim Calendar].[Year].&[2015]
Thanks
Armin
February 12, 2016 at 10:34 am
Without more details about the cube it's composition and aggregations etc. and also what you are trying to achieve here, it is almost impossible to help you optimize the query.
If you're trying to return 50 million rows, isn't it pretty obvious why the query is timing out?
February 18, 2016 at 6:25 am
Hi everybody,
the query is adjusted but now i have hopefully only one problem.
Result at the moment is shown at the attached screenshot.
I need only the yellow lines - on which position works a filter with QtyTotal>0 in my Statement:
WITH
MEMBER [Measures].[QtyTotal] AS
[Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
SELECT
NON EMPTY
{
[Measures].[QtyStdOut]
,[Measures].[QtyInExtension]
,[Measures].[QtyStdIn]
,[Measures].[QtyInWarranty]
,[Measures].[QtyTotal]
} ON COLUMNS
,NON EMPTY
{
NonEmpty
(
[DimModel].[ModelNoShort].[ModelNoShort].ALLMEMBERS
*
[DimModel].[ModelUnitMapping].[Id Unit].ALLMEMBERS
,[Measures].[QtyTotal]
)
} ON ROWS
FROM
(
SELECT
{
[DimModel].[ModelUnitMapping].[ModelNoShort].&[ABAB]
} ON COLUMNS
FROM
(
SELECT
{[Dim Calendar].[Calendar].[Month4report].&[2015/01]} ON COLUMNS
FROM [ModelCalculation]
)
)
WHERE
[Dim Calendar].[Calendar].[Month4report].&[2015/01]
February 22, 2016 at 7:46 pm
How about this?
WITH
MEMBER [Measures].[QtyTotal] AS
[Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
SELECT
NON EMPTY
{
[Measures].[QtyStdOut]
,[Measures].[QtyInExtension]
,[Measures].[QtyStdIn]
,[Measures].[QtyInWarranty]
,[Measures].[QtyTotal]
} ON COLUMNS
,NON EMPTY
{
Filter
(
[DimModel].[ModelNoShort].[ModelNoShort].ALLMEMBERS
*
[DimModel].[ModelUnitMapping].[Id Unit].ALLMEMBERS
,[Measures].[QtyTotal] > 0
)
} ON ROWS
FROM
(
SELECT
{
[DimModel].[ModelUnitMapping].[ModelNoShort].&[ABAB]
} ON COLUMNS
FROM
(
SELECT
{[Dim Calendar].[Calendar].[Month4report].&[2015/01]} ON COLUMNS
FROM [ModelCalculation]
)
)
WHERE
[Dim Calendar].[Calendar].[Month4report].&[2015/01]
February 22, 2016 at 11:21 pm
The query works also - but my problem isn't solved.
I need all models with the units in warranty in maybe one year or one month.
The two queries delivers only the infos for one model:
[DimModel].[ModelUnitMapping].[ModelNoShort].&[ABAB]
If i changed the subselect to
[DimModel].[ModelUnitMapping].[Model No Short].ALLMEMBERS
i don't get any result.
February 26, 2016 at 6:57 am
Remove the SubSelect then as it serves no purpose if you're not wanting to filter on a particular model:
WITH
MEMBER [Measures].[QtyTotal] AS
[Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
SELECT
NON EMPTY
{
[Measures].[QtyStdOut]
,[Measures].[QtyInExtension]
,[Measures].[QtyStdIn]
,[Measures].[QtyInWarranty]
,[Measures].[QtyTotal]
} ON COLUMNS
,NON EMPTY
{
Filter
(
[DimModel].[ModelNoShort].[ModelNoShort].ALLMEMBERS
*
[DimModel].[ModelUnitMapping].[Id Unit].ALLMEMBERS
,[Measures].[QtyTotal] > 0
)
} ON ROWS
FROM
[ModelCalculation]
WHERE
[Dim Calendar].[Calendar].[Month4report].&[2015/01]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply