jsonparseractivepivotmdx-query

ActivePivot: Parsing Multidimensional Cellset data into JSON for React-vis Charts

I have just started working with MDX queries so I am not an expert. We have been provided with MDX queries to be triggered from our front-end application through a web socket connection. The response received is a multi-dimensional dataset rather than a standard JSON which looks like this.

{
  "type": "cellSetData",
  "streamId": "cb6fdd98-d528-44fb-8f14-366970e574b5",
  "queryId": "cb6fdd98-d528-44fb-8f14-366970e574b5",
  "data": {
    "axes": [
      {
        "id":0,
        "hierarchies": [
            {
              "dimension": "Measures",
              "hierarchy": "Measures"
            }
        ],
        "positions": [
          [ { "namePath": [ "5-Day ADV" ] } ],
          [ { "namePath": [ "Target Value" ] } ],
          [ { "namePath": [ "Performance Vs VWAP (Targ. Val. W.A.)" ] } ]
        ],
        "maxLevelPerHierarchy": [1]
      },
      {
        "id":1,
        "hierarchies": [
          {
            "dimension": "Order",
            "hierarchy": "OrderId"
          }
        ],
        "positions": [
          [ { "namePath": [ "AllMember" ] } ],
          [ { "namePath": [ "AllMember", "20180829-142357889-114-29" ] } ],
          [ { "namePath": [ "AllMember", "20180829-142357896-775-32" ] } ],
          [ { "namePath": [ "AllMember", "20180829-142357897-394-35" ] } ]
        ],
        "maxLevelPerHierarchy": [2]
      }
    ],
    "cells": [
      {
        "ordinal": 0,
        "value": 1.8702095375E7
      },
      {
        "ordinal": 1,
        "value": 41461.2
      },
      {
        "ordinal": 2,
        "value": 0.0
      },
      {
        "ordinal": 3,
        "value": 1968021.375
      },
      {
        "ordinal": 4,
        "value": 17719.2
      },
      {
        "ordinal": 5,
        "value": 0.0
      },
      {
        "ordinal": 6,
        "value": 1043997.0
      },
      {
        "ordinal": 7,
        "value": 10328.4
      },
      {
        "ordinal": 8,
        "value": 0.0
      },
      {
        "ordinal": 9,
        "value": 1.5690077E7
      },
      {
        "ordinal": 10,
        "value": 13413.6
      },
      {
        "ordinal": 11,
        "value": 0.0
      }
    ]
  }
}

  1. Do we have any open-source parsers to help us get a JSON response as below:

{"Stock_Percentage":"1.8702095375E7","Stock_Quantity":"21997538","Stock_Price":"333"} {"Stock_Percentage":"1968021.375","Stock_Quantity":"17719.2","Stock_Price":"0.0"}

  1. Can we make tweaks at the MDX level to return a similar response irrespective of the aggregation levels.

  2. A query for the ActiveViam, how does Active Pivot Live which is React based parse this data. Any gitlinks or references are welcome.

  3. The only crude way I could think of was getting a count of "positions" and using it as a calculations through MOD function on the "Cells". This could be just a rambling feel free to skip this.

Any information is much appreciated!

Best Regards!!

Solution

  • MDX being designed for MultiDimensional analysis, results cannot look like a simple table, which could not reflect this multidimensional feature, particularly the hierarchies.

    As far as I know, there are no open-source parsers transforming such response into a table nor something approaching your format. However, you can try the REST call exporting any MDX query as a CSV http://server:port/pivot/rest/v4/cube/export/mdx/download. According to your JSON cellset, it would provide something like this:

    [Order].[OrderId].[OrderId];[Measures];VALUE
;"5-Day ADV";1.8702095375E7
;"Target Value";41461.2
;"Performance Vs VWAP (Targ. Val. W.A.)";0.0
20180829-142357889-114-29;"5-Day ADV";1.8702095375E7
20180829-142357889-114-29;"Target Value";41461.2
20180829-142357889-114-29;"Performance Vs VWAP (Targ. Val. W.A.)";0.0
...

    Finally, you can always parse the result by yourself. The idea is that cell ordinals represent given locations in your cube. They are computed by iterating over axis positions, starting from the axis with the higher id to the one with the lowest. For example,

    Cheers

    PS: I work at ActiveViam