Playing with essbase ad hoc analysis using command line

If you are not familiar with cURL, JSON and REST, please read my earlier blog. Initial section of it has few links to quick start.

In this blog, we are going to play with a grid using command line. Wait! Is this even possible?
Let's try...

Create a Sample/Basic cube from Gallery.
Let's get a default grid first.
curl -X GET -u <user>:<password> "https://host/essbase/rest/v1/appliations/Sample/databases/Basic/grid"

It returned the default grid, it is based on the Smart View protocol.
...
  "slice" : {
    "columns" : 4,
    "rows" : 3,
    "data" : {
      "ranges" : [ {
        "end" : 11,
        "values" : [ "", "Product", "Market", "Scenario", "", "Measures", "", "", "Year", "105522.0", "", "" ],
      ...

The json above explains that it's a 3x4 grid and values contains all the values in a flat array.

Now let's Zoom In Year. Really??

Save above grid as "grid.json" by adding an initial 'zoomin' action node.


curl -X POST -u <user>:<password> "https://<host>/essbase/rest/v1/applications/Sample/databases/Basic/grid" -H Content-Type:application/json  --data "@/mnt/d/grid.json"

{
"action" : "zoomin",
"ranges": [[2,0,1,1]],
"grid": { <above grid goes here> }
}

"slice" : {
    "columns" : 4,
    "rows" : 7,
    "data" : {
      "ranges" : [ {
        "end" : 27,
        "values" : [ "", "Product", "Market", "Scenario", "", "Measures", "", "", "     Qtr1", "24703.0", "", "", "     Qtr2", "27107.0", "", "", "     Qtr3", "27912.0", "", "", "     Qtr4", "25800.0", "", "", "Year", "105522.0", "", "" ],
...

ranges in the above json has four values: row#, column#, number of rows, number of columns.  This means we can add multiple symmetric ranges in a single operation.

Great, what next? Can we keep only, remove only, zoom out, pivot?
Yes. See the details here: https://drive.google.com/file/d/1qC6xIM8TbnqTw7DDcJyI4LbZDyD6lf2V/view?usp=sharing
(Download and view it in MS Word or Libre Office as Google docs cannot render it properly)

Let's zoom in, keep only to get the editable grid like below. ( Hack Hint: You can manually build a grid and call refresh to avoid too many zoom-ins...)
...
"slice" : {
    "columns" : 5,
    "rows" : 3,
    "data" : {
      "ranges" : [ {
        "end" : 14,
        "values" : [ "", "", "", "Actual", "Actual", "", "", "", "Sales", "COGS", "New York", "Cola", "Jan", "678.0", "271.0" ],
...

Let's change the COGS to 300 from 271. Save this grid like earlier in grid.json with initial "submit" action node.

{
"action" : "submit",
"grid": { <above grid goes here along with the dirtycells array inside slice tag> }
}

Here is how to add dirtycells. Dirty cells are the indexes of changed cells.

"slice" : {
    "columns" : 5,
    "rows" : 3,
    "data" : { ....}
    "dirtyCells":[14]

Here 14 is the index of an array which starts from 0.

Grid comes back with changed values. We can zoom out on Profit to see the impact.

By the way, you can execute MDX queries as well. See the link above.

Cool, isn't it? Let me know if have any questions.