At the moment i work on a problem with an MDX Query.
The cube contains models and serials (units) and should show units in warranty for each year.
This is the a cube with this Dimensions/Measures:
20160219_CubeInfos.jpg
Now i would select all Serials which are in warranty for a special year.
The problem is that the whole Table (v Dim Unit Model 4IB Test) contains more than 50 Mio rows which results alsways to an QueryTimeout or sometimes to an MemoryException.
At the moment i have a mdx-qeury (see below) which works if i select special model. But i need the filter to all models.
Code:
WITH
MEMBER [Measures].[QtyTotal] AS
[Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
SELECT
NON EMPTY
{
[Measures].[QtyStdOut] ,[Measures].[QtyInExtension] ,[Measures].[QtyStdIn]
,[Measures].[QtyInWarranty] ,[Measures].[QtyTotal] ,[Measures].[SumStartWarranty]
} ON COLUMNS
,NON EMPTY
{
crossjoin(
[v Dim Unit Model 4IB Test].[ModelUnitMapping].[Id Unit].Members
,[Dim Country].[Id Country].[Id Country].members
,[Dim Calendar].[Calendar].[Month Name4report].members
)
} ON ROWS
FROM
(
SELECT
{
[v Dim Unit Model 4IB Test].[model no short].[Model No Short].&[SampleModel]
} ON COLUMNS
FROM
(
SELECT
{
[Dim Calendar].[Calendar].[Year].&[2015]
} ON COLUMNS
FROM [InstalledBaseCS_Serial]
)
)
Does anybody knows a tip to update the query to get all units for one year (round about 4 Mio rows)?