Analysis Service MDX Query Viewer web client

• Download MDXViewer.zip - 172.3 KB

Introduction

We want to be able to execute MDX queries, just like we do in SQL Server Management Studio but in a browser and without a direct access to the SSAS. Resulted cellset we want to display in a pleasing to an eye grid where selected ROWS and COLUMNS are nicely groupped up together. 

Source code

Source code can be found on GitHub https://github.com/goatdata/mdxviewer, feel free to pull request if you have any improvements, bug fixes. 

Background

Continuing on from Serializing CellSet Adodm MDX Query Result Set to JSON in this part of the build we will create a client application in Angular 8+ CLI, TypeScript that sends MDX query to a WebAPI created in .Net 4.7, which in turn executes the MDX query against SSAS (SQL Server Analysis Service), converts the resulting cellset into JSON, returns it to the client. Client application subsequently presents the cellset data in a grid with all the  bells and whistles you can expect from a modern, pretty looking and functional grid. 

We want to do it fast, we want it to look good and make sense to the end user. Therefore we will add couple of cherries on the cake, by MDX code highlighting text editor (Ace Editor) and off-the-shelf top of the range grid (agGrid Community). 

Topology

We have three parts at play here - naturally web client, that's just a whole bunch of javascript running in the browser. Server side, a WebAPI controller and SQL Server Analysis Service.

1. A user enters MDX Query using our pretty Ace Editor
2. Upon pressing Execute button, Viewer/Grid component passes the text of our MDX to Angular mdxapi Service. Who's role here is really just to send a request to WebAPI, nothing more.
3. WebAPI has a controller called MDX Controller, there is a Query method, we send our MDX query text to it as Post payload
4. Query method connects to SSAS and executes our MDX query as CellSet
5. While reading cellset we convert it into JSON
6. And once we have finished reading we return the JSON as a string resposne to the client
7. Here we have to do some wizardry. agGrid needs data in a specific format, therefore we have to organize it somewhat suitable for agGrid. We simply feed the whole JSON to CellSet.ts class which in turn does some internal magic and allows us to bind agGrid directly to AgGridData() and column definition to AgGridColDefs(). Although we actually assign variables for this purpose to prevent reparsing cellset during digestion. 
8. Thats it really

Walking through the code

Server Side WebAPI

Lets go through server side code first. We not going to dig into WebAPI project structure, its pretty standard one. We have a controller MDXController with a one simple HttpPost action Query. We need it to be a post because our payload will come in as JSON object in the body. 

[HttpPost]
[ActionName("Query")]
public HttpResponseMessage Query(MdxQuery MdxQuery)
{
    try
    {
       string output = "";
       using (AdomdConnection conn = new AdomdConnection(Helpers.SettingHelper.ConnectionString()))
       {
           conn.Open();
           AdomdCommand cmd = new AdomdCommand(MdxQuery.Query, conn);
           CellSet cs = cmd.ExecuteCellSet();
           output = Helpers.CellSetToJson.MdxToJson.MdxToJsonConvert(cs, false);
           conn.Close();
       }
       var response = this.Request.CreateResponse(HttpStatusCode.OK);
       response.Content = new StringContent(output, System.Text.Encoding.UTF8, "application/json");
       return response;
    }
    catch (Exception ex) {
        return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message);
    }
}

Not much going here, we simply opening up AdodmConnection to SSAS, creating a command with our MDX and executing CellSet. One point worth mentioning is that we do need CellSet result in order to properly render the result to the user. Another interesting observation is MdxToJson class with a very helpful method (yes, yes its static, I know... whatever), this method simply reads cellset data and converts it to JSON. Its described in my previous article Serializing CellSet Adodm MDX Query Result Set to JSON however one notable difference is how we serializing Members properties of the Tuple. We have a dedicated Member MemberJsonConverter for this purpose. The reason is Parent property of a Member class. If we look inside Member.Parent method of CellSet in  Microsoft.AnalysisServices.AdomdClient we will see the following:

 public Member Parent
    {
      get
      {
        if ((Member) null == this.parent)
        {
          if (this.baseData.CubeName == null)
            throw new NotSupportedException(SR.NotSupportedByProvider);
          if (this.Connection == null)
            throw new NotSupportedException(SR.NotSupportedWhenConnectionMissing);
          AdomdUtils.CheckConnectionOpened(this.Connection);
          ListDictionary restrictions = new ListDictionary();
          restrictions.Add((object) CubeCollectionInternal.cubeNameRest, (object) this.baseData.CubeName);
          AdomdUtils.AddCubeSourceRestrictionIfApplicable(this.Connection, restrictions);
          restrictions.Add((object) "MEMBER_UNIQUE_NAME", (object) this.UniqueName);
          restrictions.Add((object) "TREE_OP", (object) 4);
          DataRowCollection rows = AdomdUtils.GetRows(this.Connection, "MDSCHEMA_MEMBERS", restrictions);
          if (rows.Count > 0)
            this.parent = new Member(this.Connection, rows[0], (Level) null, (Member) null, MemberOrigin.Metadata, this.baseData.CubeName, (Tuple) null, -1, this.baseData.Catalog, this.baseData.SessionID);
        }
        return this.parent;
      }
    }

Basically Parent of a Member of a dimension or a hierarchy is not returned by SSAS, instead it is being individually requested from the server everytime you access it. It's fine, sort of, if you have 10 or 20 members, but when you have gazillion of them, it will make it as slow as koala on prozac. Instead we can request DIMENSION PROPERTIES PARENT_UNIQUE_NAME in the actual MDX on each of the axis and read PARENT_UNIQUE_NAME from member properties instead. Oh, yes, we do need Member Parent in the first place, because that is how we building up hierarchical layout of a dimension members on an X axis in the grid view. 

Client Side

Client side is Angular CLI project version 8+. Again lets not dive into the structure of the project too deep, as its pretty standard implementation, instead we will focus on crtical components, such as how do we convert JSON from cellset to something that is suitable for a grid. In our case we use agGrid Community. Side note, agGrid is super powerful, very quick and pretty, Community version gives us a good base, but for commercial implementation I would recommend upgrading to agGrid Enteprise as it unlocks some cool built in pivoting, custom sorting and cell formatting functionality, which can be very handy. 

CellSet Json to agGrid

First we need column definition. MDX query returns all members of an Axis as a list of lists (array of arrays for those who will celebrate 25th birthday of Windows 95 this August). 

Hierarchy itself is not particularly well presented. In the example above, United Kingdom is member of Europe - a Sales Territory dimension, logically it occures as tuple ordinal directly following its parent. What we want is to represnt this relationship on the UI to the user on the grid:

We have a class in app/models/CellSet created to faciliate CellSet conversion to different views, particullary we want to extract column definition applicable to agGrid. 

export class CellSet{    

    public Axes :Axis[];
    public Cells:Array<Array<string>>;

    constructor(private fromRaw: any){
        Object.assign(this,fromRaw);
    }
    
    public AgGridColDefs(): any[]{
        // logic to conver Axis[0]  Tuples and it's Members to agGrid colDefinitions
    }
    
    public AgGridData() : any[]{
       // logic to conver Axis[1] Tuples and it's Members to cell data - more on that later...
    }
}

Once we instantiated CellSet class with raw JSON returned from the WebAPI we call AgGridColDefs() that does the magic. In a nutshell, here we figure out maximum number of levels in all hierarchies of each dimension, then for each dimension member we simply itterate through all members on the same level putting them into hierarchical structure (we have specific class for this AxisMember, its simply there to strongly type a member, just makes life a little easier). Exact logic of this method is a little beyond the scope of this article, step through it if you wish to understand how it does it - debug will enlighten you. 

Ok column definition is easy and straight forward, at the end of the day agGrid has builtin column grouping, which we have used in this case. What can we do about Axis1 (Y axis) row headers, again lets look at our result from MDX:

See, Australian Dollar for example is repeated for every row, which is ugly. Also Bike Racks is actually a member of Accessories. Anyhow agGrid doesn't have row headers, the community version at the very least. We need to cheat. What we will do is do the same as what we have done for columns in terms of figuring out what member belongs to what and simply pad the cell value according which level it is in the hierarchy. For all intense and purpses to agGrid it will look just like another cell value, we end up with something like this:

We have AgGridData() method in CellSet class which does this sort of stuff, again the exact logic there is a little beyond the scope of this article - Debug it if you are into that sort of thing. 

Once we have converted raw CellSet data into something usable by agGrid the data is bound to the grid. This happens on the actual app/viewer/pivotalgrid/pivotalgrid.component.ts Execut method. 

  Execute(){
    this.IsExecuting=true;    
    this.mdxapi.Query(this.query).then(r=>{
      this.cellSet  = new CellSet(r as any);
      this.columnDefs = this.cellSet.AgGridColDefs();
      this.rowData = this.cellSet.AgGridData(); 
      this.IsExecuting=false;
      this.gridApi.hideOverlay();
    }).catch(ex=>{
      this.IsExecuting=false;
      this.gridApi.hideOverlay();
      throw ex;
    });

    this.gridApi.showLoadingOverlay();
  }

Whats next

• Obviously some authentication/authorization on the WebAPI would be a good idea. My next article will be about general session management and auth in .Net WebAPI which I will tie up this project. 
• I constantly face with a problem of long running queries and inevidably slow or even kill SSAS. In the current implementation Execute button turns to X, would be cool if we could Cancel the query and kill spid on the SSAS entirly if its bloated. 
• Ace Editor has pretty nifty ability to inject intellisense. In the current implementation I have modified definition files for Ace Editor (src\assets\EditorTheme\mode-mdx.js and src\assets\EditorTheme\theme-mdx.js) for highlighting MDX functions. Would be nice if we had some sort of object explorer similar to how we have it in SQL Server Management Studio and intellisence for memebr attribute paths and functions.