Convert CellSet object to JSON result

Question

I want to convert a CellSet object (which is the result of an MDX query in SSAS) into a valid JSON format which will empower some client side charts.

Following is the sample query .

SELECT
[Measures].[Sales Amount - Reseller Sales] ON COLUMNS,
NON EMPTY
    GENERATE (
        [Sales Territory].[Sales Territory Country].MEMBERS,
        [Sales Territory].[Sales Territory Country].CURRENTMEMBER
            *
        TOPCOUNT(
            [Product].[Category].[Category].MEMBERS,
            5,
            [Measures].[Sales Amount - Reseller Sales]
        )
    )
ON ROWS FROM [Analysis Services Cube]

Following is the output of the query.

MDX Query Result How do i convert the above result to a JSON object? I am confused how to form a key/value pair from a multi-dimensional result set.

Thanks for any help.

Answer 1

(This info is to try to help - I can give you some ideas on the structure of an MDX script. I've not tried implementing an JSON implementation so this isn't a dirct answer)

MDX has this hierarchical structure:

• Dimension
• Hierarchy
• Level
• Member

i.e. Each member in a script is of this structure:

[Dimension].[Hierarchy].[Level].[Member]

Initially you could extract all the possible values for each of the above.

Now a single script (usually) has just 3 axes:

• Rows
• Columns
• Slicer

Theoretically it can have over 100 axes.

Each axis acts independently i.e. there is never interdedendency between axes, so could these be your initial keys? Each one would then have a inner dictionary.