Saiku Hierarchize function not returning expected cellset

Report
Question

Please briefly explain why you feel this question should be reported .

Report Cancel

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

solved 0
2 Answer 4 views 0

Answers ( 2 )

    1
    November 24, 2016 at 12:45 pm

    Please briefly explain why you feel this answer should be reported .

    Report Cancel

    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
      }
    
    Best answer
    0
    November 24, 2016 at 12:45 pm

    Please briefly explain why you feel this answer should be reported .

    Report Cancel

    Maybe try adding in the [ALL] member for Food to force the issue?

    This was my AdvWrks mock-up:

    SELECT 
      {
        [Product].[Category].&[4]
       ,[Product].[Category].&[1]
      } ON 0
     ,Hierarchize
      (
        Union
        (
          {
            [Customer].[Customer Geography].[Country].&[Australia]
           ,[Customer].[Customer Geography].[Country].&[Canada]
          }
         ,{
            [Customer].[Customer Geography].[Country].&[Australia].Children
           ,[Customer].[Customer Geography].[Country].&[Canada].[All]
          }
        )
      ) ON 1
    FROM [Adventure Works]
    WHERE 
      [Measures].[Internet Sales Amount];
    

    So in your scenario:

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

Leave an answer

Browse

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>