Menu
Download Free Trial

How to use ‘subquery’ parameter for faster data loading

In the new build 2.304, we want to present you a new feature which can be extremely useful for those of our client who works with MS Analysis Services or icCube.

From now you can restrict the amount of data to load from OLAP cube by using ‘subquery‘ in the list of connection parameters.

Let’s say you have the cube with the date hierarchy and your task is to show reports only for the one specific year instead of loading all the data. To load all data from the cube your report object is the following:

{
    "dataSource": {
        "dataSourceType": "microsoft analysis services",
        "proxyUrl": "http://olap.flexmonster.com/olap/msmdpump.dll",
        "catalog": "Adventure Works DW Standard Edition",
        "cube": "Adventure Works"
    },
    "slice": {
        "rows": [ { "uniqueName": "[Delivery Date].[Calendar]" } ],
        "columns": [ 
            { "uniqueName": "[Product].[Category]" },
            { "uniqueName": "[Measures]" }
        ],
        "measures": [ { "uniqueName": "[Measures].[Order Count]" } ]
    }
}

And here is the result:

2016-12-03_1531


Now we run the report with ‘subquery’ parameter:

{
    "dataSource": {
        "dataSourceType": "microsoft analysis services",
        "proxyUrl": "http://olap.flexmonster.com/olap/msmdpump.dll",
        "catalog": "Adventure Works DW Standard Edition",
        "cube": "Adventure Works",
        "subquery": "select 
                       {[Delivery Date].[Calendar].[Calendar Year].&[2008]} on columns 
                     from [Adventure Works]"
    },
    "slice": {
        "rows": [ { "uniqueName": "[Delivery Date].[Calendar]" } ],
        "columns": [ 
            { "uniqueName": "[Product].[Category]" },
            { "uniqueName": "[Measures]" }
        ],
        "measures": [ { "uniqueName": "[Measures].[Order Count]" } ]
    }
}

and the updated report with the server-side filter applied:

subquery applied

‘Subquery’ parameter helps to decrease the size of the response from the OLAP cube and it takes less time to load and render.


What is the ‘subquery‘ parameter? Technically, it’s the part of MDX query that the component generates and sends to the server. In our sample, it looks like this:

Select NON EMPTY Crossjoin(
   Hierarchize({
      DrilldownLevel({[Product].[Category].[All Products]})
   }), 
   {[Measures].[Order Count]}
) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS , 
NON EMPTY Hierarchize({
   DrilldownLevel({[Delivery Date].[Calendar].[All Periods]})
}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE ON ROWS 
FROM (
   select {[Delivery Date].[Calendar].[Calendar Year].&[2008]} on columns from [Adventure Works]
) CELL PROPERTIES VALUE


Please try it with your own OLAP cube. We are waiting for your feedback.