Tips for Using Essbase in Data Visualization

Last Friday I wrote a blog on how to connect Essbase and Essbase Cloud Service (EssCS) to Data Visualization (DV). I’ve decided to put together a couple tips to help you start using Essbase/EssCS as a data source in DV.

Let’s start…

If you followed the steps and have Essbase or EssCS as a DV data source, you might find yourself annoyed that the hierarchy names came in as Generations. Here is one of my examples from last week:

If you administer a cube, you might know what each generation refers to in the dimension. For example, “Gen2, Location” might be country, “Gen3, Location” might be state, and “Gen4, Location” might be city. However, if you are the end user, you might get confused with these details.

Doesn’t this look easier to navigate?

So, how can you give the generations a clearer definition of the hierarchy granularity? It’s actually pretty simple! I’ll show you how to do this in EssCS then in Essbase, but it’s really the logic, just different steps.

EssCS

There are two ways we can define generations for EssCS. You can choose which one you like best.

The first way is to log into EssCS, choose your cube, and click “Settings”.

In the “Dimensions” section, you will see the each dimension listed with details.

For demo purposes, I’m going to update Years.

Note that the generation names are “Gen1” and “Gen2”.

Let’s update “Gen1” to be “All Years” and “Gen2” to be “Year”. This is done by clicking on the Name (“Gen1” or “Gen2”) and entering the name you would like to enter.

Once I have finished naming my generations, I click “Save”.

Now, when I go into DV and connect to this cube, I have an updated data element name.

Previous:

Current:

The second way to do this in EssCS is to use the Cube Designer workbook. On the “Cube.Generations” tab, enter the names of the generations for the dimensions you want updated.

From the Cube Designer ribbon, choose “Build Cube”.

Choose to “Update Cube – Retain All Data” and click “Run”. You will likely not want to load the data sheets, but you can if you would like to.

Confirm that you want to update the listed cube.

Choose Yes to see the job run.

Once the job finishes, you should have generation names available in DV.

Essbase

If want to name the generations in Essbase, choose “Generations” from the right-click menu of the dimension you want to update:

Enter the names of the generations for that dimension and click “OK”.

When I go to DV, I see my generations updated for Department.

Note: To prove I wasn’t pulling any trickery, this is to prove that my screenshot is from my OP Essbase cube, not my cloud Essbase cube. …The Years were not updated in the OP cube!

Now we can build more meaningful visualizations in DV!

Visualizations in DV

Unlike in Smart View or HFR, you are NOT required to specify a member from every dimension in DV. Using the below as an example, I built a Row Expander visualization that is very something that we would normally build in Smart View for comparison. To the right, I used a stacked bar graph using just a few data elements. I’ve modified the visualization to show the currency amounts for the whole of what the government paid towards employee’s Basic Benefit and Thrift Savings Plans (no, it’s not real data!).

Imagine being able to drill into data visually and NOT in Excel, having to move around columns, dimensions, etc. How neat is that? Let’s drill into FY2017’s Basic Benefit Plan. I right-click on the portion of data I want to go deeper into for analysis.

I can choose from any dimension.

Let’s choose by Job Function.

Notice that both visualizations drill to the Job Function level.

Let’s keep drilling into the one with the most contributions, GS0600, to the Group level.

And, finally, to the Employee level for GS0688.

We now see all the employees that are contributing towards the GS0688 numbers. I’ve highlighted the filters in the screenshot to show it is very easy to get back to the starting point by deleting (or even altering) the filters.

Since the Basic Benefit Plan values are 1% of the employee’s base pay if they have served at least 5 years for the government, I want to see the actual number of years, grade, and step of the employees.

I’m going to alter the Row Expander visualization.

The numbers make complete sense and I was able to see data next to a visualization that update with each other. Pretty cool, huh??

Now, at this point, I really wanted to show how you can mashup data in DV, specifically using the Essbase data source. I had this great example of a spreadsheet that listed each employee’s job title:

And I was going to join them together in a visualization to show how they can be presented as one. However, as I was trying, I was having trouble with joining the sources. Since the Essbase connection is still in beta, there are a few caveats. One of them I found in the latest documentation:

Well, darn. I guess this post will have to be updated when the bugs are worked out!

As you can see, you can modify the settings in Essbase to make DV very simple to use for Essbase analysis. As I build out my demo more, I try to show more reasons why Essbase and DV are perfect compliments!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s