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.

Introduction to Cube Designer - Files

OAC-Essbase has introduced a new platform concept called a catalog. The catalog is an area of storage within the cloud for both Essbase artifacts and end user artifacts. The catalog uses the OAC-Essbase security to restrict access.

Can Python digest a cube?

Then, I drew the inside of the boa constrictor, so that the grown-ups could see it clearly. They always need to have things explained.

But let's put elephants and boa constrictors aside. The more important question would be -- Can Python digest a cube? Nowadays even kids in elementary schools do python, so I thought it would be useful to show how Python code can call OAC Essbase REST APIs. Let's try to list files under /gallery/Cubes directory in the Essbase catalog.

Introduction to outline editing using JSON and REST apis

In this blog, we will cover how we can automate the outline editing using simple JSON file and REST apis.

Read more about JSON here: https://www.json.org/
and REST apis here: https://en.wikipedia.org/wiki/Representational_state_transfer

We will use cURL for executing the outline REST. cURL is a command line library to execute HTTP requests.
https://en.wikipedia.org/wiki/CURL

cURL is installed by default on the Linux systems but on windows, you have to download and install it before trying the examples. https://curl.haxx.se/download.html

If you want to try any REST clients like POSTMAN etc., you can skip the cURL installation.

 Create a simple JSON file on your system with below code snippet.
{
  "editActions" : [
     {
        "mbrAdd" : {
                            "mbrName" : "Test", "parent" : "Product"
                       },
        "mbrRename" : {
                           "thisMbr": "Test", "mbrName":"Test1"}
     }
   ]
}

In this JSON, we are trying to add a member called "Test" in Product dimension and renaming it to "Test1".  This example will work on any outline with Product dimension in it.


Now, let's run this.
Save the above JSON into a file called myBOE.json. We can name it anything though.

Open command prompt and run it like below:

suresh@mylaptop:/mnt/d/study/Essbase/boe$ curl -X POST -u <username>:<password> "https://<host>/essba
se/rest/v1/applications/<application>/databases/<database>/boe" -HAccept:application/json -H Content-Type:application/json --data "@
/mnt/d/study/Essbase/boe/myBOE.json"
{
  "messages" : "Info [1370001]: at line 2 char 111 element[otlEditMain], outline version reset to [0].
   Total member added: 1
   Total member updated: 1
   Total member deleted: 0
   Total XML edit time:[0.010000]"
}

Replace <username> with your username, <password> with password <host> with your OAC host, <application> with your Essbase application and <database> with database/cube name.


Yay! we got the output saying a member is added and updated. I don't believe, it's that simple. Let's confirm if it's real in the outline user interface :-)

Nice! It added and renamed a member.

To see what one can do and what are all the elements we can put in the JSON, click the below link:
https://drive.google.com/file/d/1Qs-T6_K3mamXuOq0hpghzsxYUL6pHd6b/view?usp=sharing

Happy learning!!!

  

Can we visualize zigzag?

Yes, you can

What are the index based rules and why they are used by cube designer?

In the previous blog, we discussed how to create a dimension build rules file and how it creates a dimension with few clicks from raw data.

In this blog, we will cover the basics of index based rules and the difference between them.

• Good news is they are the similar except the way column operations(join, create columns, ...) are handled. Another difference is index based rules are supported for dimension build only and not for data load. 

• Another good news is, if your rules are in regular format, there is no need to convert them to index based. If you are writing new rules, index based rules are easy to develop and understand.

(Re)Introducing load rules in OAC

Rules define operations that Essbase performs on data values or on dimensions and members when it processes a data source. Use rules to map data values to an Essbase database or to map dimensions and members to an Essbase outline.

 In other words, rules file is the small out of the box ETL utility to extract, transform and load the data into Essbase. You can either build/edit dimension or load data into Essbase using these rules.

In this blog, we will convert the flat data into Essbase dimension called "Year". There is no need to create this empty dimension in the outline. Rules will take care of creating it too.