MDX-Query - Query with to many rows. Set correct filter

Question

I work on a problem with an MDX Query.

The cube contains models and serials (units) and should show all units in warranty for each year.

This is the a cube with this Dimensions/Measures:

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 query (see below) which works if I select special model. But I need the filter to all models.

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)?

Is someone actually going to read all 4 million rows of the results? — Tab Alleman
@TabAlleman for that sort of volume I usually stick to realtional - how about you? — whytheq

whytheq whytheq · Accepted Answer · 2016-02-20T12:23:30

If you're trying to return the results to a visible grid in MDXstudio or SSMS then it may be timing out because there is quite a bit to render.

If you use OPENQUERY or the CLR OLAP Extensions then try the following:

Do not return the results to the screen but INSERT results into a table.
Simplifiy your script by taking away the custom measure. This can easily be calculated later as it is trivial: I have a feeling it is slowing down ssas.

Script

SELECT 
  NON EMPTY 
    {
     [Measures].[QtyStdOut] 
    ,[Measures].[QtyInExtension] 
    ,[Measures].[QtyStdIn]
    ,[Measures].[QtyInWarranty] 
    ,[Measures].[SumStartWarranty]
    } ON 0
 ,NON EMPTY 
    [v Dim Unit Model 4IB Test].[ModelUnitMapping].[Id Unit].Members
   *[Dim Country].[Id Country].[Id Country].members
   *[Dim Calendar].[Calendar].[Month Name4report].members
 ON 1
FROM 
(
  SELECT 
    [v Dim Unit Model 4IB Test].[model no short].[Model No Short].&[SampleModel] ON 0  
  FROM 
  (
    SELECT [Dim Calendar].[Calendar].[Year].&[2015] ON 0
    FROM   [InstalledBaseCS_Serial]
  )
);

MDX-Query - Query with to many rows. Set correct filter

1 Answers