restmdxmondriansaiku

Saiku Hierarchize function not returning expected cellset


Can someone explain me why this mdx query executed against Saiku rest api does not return expected cellset? This query is executed as mdx type ThinQuery.

SELECT 
  {
    [Location.CountryHierarchy].[Croatia]
   ,[Location.CountryHierarchy].[Serbia]
  } ON COLUMNS
 ,Hierarchize
  (
    Union
    (
      {
        [Product.ProductHierarchy].[Drinks]
       ,[Product.ProductHierarchy].[Food]
      }
     ,[Product.ProductHierarchy].[Drinks].Children
    )
  ) ON ROWS
FROM [Sales cube];

Expected output(tested with jpivot, pivot4j and Pentaho SchemaWorkbench/MDX explorer) ExpectedResult

Actual result rendered on Android OLAP client am working on - just to be sure I also checked returned json from saiku server and really cells are missing. Actual result


Solution

  • If you are executing using the execute endpoint and passing MDX or a Query Model in, you need to play with the properties section, try this:

    "properties": {
        "saiku.olap.query.automatic_execution": true,
        "saiku.olap.query.nonempty": true,
        "saiku.olap.query.nonempty.rows": true,
        "saiku.olap.query.nonempty.columns": true,
        "saiku.ui.render.mode": "table",
        "saiku.olap.query.filter": true,
        "saiku.olap.result.formatter": "flat",
        "org.saiku.query.explain": true,
        "org.saiku.connection.scenario": false,
        "saiku.olap.query.drillthrough": true
      }