Difference Cube for Data Validation

On occasion throughout my career with Essbase, I have heard of difference cubes.  I understood the concept behind them, but until recently didn't see how they could be useful.  I don't know that I am using a difference cube in the sense that others have spoken of them, but I did find a great way to test large amounts of data using Essbase and the concept is still largely the same.


Recently we decided to upgrade one of our cubes by adding some new dimensions.  To do this we needed to update our history to include these new fields via a set of SQL update queries to our source table.  We had the the correct data in a separate set of PeopleSoft source tables.  We needed to ensure that our table updates populated the correct data.  We are talking more than 5 million rows of data so testing in Excel is not exactly a viable solution.  So what I did instead was load my data from my PeopleSoft tables into a copy of my cube into a scenario called "Actual."  Next I took my SQL updated table and and loaded it into the cube into a scenario called "Test."  I then had a dynamic scenario called "Difference" which did a simple calculation of "Test" - "Actual."  Once each of the data sets was loaded, I was able to set up a high level retrieve sheet for each of the drilled out new dimensions for the "Difference" scenario.  Anywhere, where I saw something other than zero was something that needed to be researched, as it was a variance between my two data sets.  I was also able to further drill down on my other dimensions to determine exactly where the variance was occurring.  I was able to completely validate my data in hours rather than the days or weeks it may have taken me if I had been forced to use a set of queries and Excel.


This was just the immediate use I found for an Essbase difference cube.  Taking just a few minutes, I can think of various other situations where this type of cube might come in handy.  Just a couple of examples would be that it might also be useful in:

• Verifying a new calculation for a cube
• Comparing a back up of an initial cube load to a new cube load
• Validating a new cube to an existing similar cube

A difference cube isn't always going to be something that makes sense, but it is definitely a tool that should be included in your tool box that can be drawn upon should an ideal scenario arise.  I don't see a difference cube as a daily use, especially for a functional user.  But for someone who develops cubes on a regular basis or works with large amounts of source data, there is definitely a functionality of this type of cube that is appealing.

As a note, if it were so necessary, you can also keep a copy of your difference cube as documentation of your validation for audit or SOX requests.

SQL and PL SQL Loading Via EAS

We have been doing quite a bit of SQL and PL SQL loading using EAS as of late. I have found the user interface can be confusing and the user guide to not be of much assistance. Below is what we have found to work for our needs.

First step is to get the ODBC set up, which the user guide can walk you through if you aren’t aware how to.

Once that is set up, within EAS you create a new load rule or modify and existing load rule if you prefer. Then navigate to File->Open SQL. You will first be prompted to select your Essbase Server, Application, and Database.

At first the next screen looks straight forward, but really there are a lot of extra fields that you may or may not need to use.

 
For instance to load via PL SQL, from a source such as PeopleSoft, you will want to select the Oracle Wire Protocol option from the SQL data sources section. You will enter your SQL statement by either using the boxes provided or by entering the entire statement into the Select section. Either of these methods will work. The next step is to select OK/Retrieve at the bottom of the screen. Note: You do not need to use the Connect section at all. Trying to fill in the Server, Application, Database, or Dictionary path in the Connect section will result in an error stating “Failed to Establish Connection With SQL Database Server. See log for more information.”
 
To load via SQL, you will want to select the SQLServer Wire Protocol option from the SQL data source section and enter your SQL statement into the provided boxes or into the Select section. Depending on how you have identified the tables in the from section of your query, you may have to provide the database name in the Connect section. If the database is included in the table description (ex: <>.dbo.<<>>) you will likely be able to move straight to selecting the OK/Retrieve button without issues. If you have not identified the database name in the table description, place the database name in the Connect section to move forward.  
Once you have selected OK/Retrieve, you will be prompted with the SQL Connect box where you are prompted for a user name and password, which will be your PL SQL or SQL user name and password. As a note, we found we had to have SQL authentication for this to work. Using Windows authentication produced errors and we were unable to connect. The Essbase Server, Application, and Database should default to your previous selection.

After selecting OK, your data will populate into your Data Prep Editor screen. From here you can create a load rule as you normally would. You can create both load and build load rules to run off of these data sources.

If you still receive a Failure to Establish Connection error message and you are sure of the passwords you are using, then you will want to look at the SQL next. Here are things to look for:

• Make sure you have not placed the word “Select” into the SQL box – It utilize the Select from the header on the box
• Remove table and column aliases – If you have multiple tables that share fields, you will need to put the entire table name in front of the field throughout your query
• Check your more complex SQL functions to make sure they are compatible with the SQL loader within EAS – Try removing the complex statements and see if you can connect
• Remove anything you have commented out – Leave the SQL statement as purely the SQL statement to be executed

When I have had an issue with my query I have stripped the SQL down to the most simple statement first. Sometimes, I have even gone so far as to have it be Select * from <>. Then adding each piece back in until I get the entire statement in place. It helps in identifying which part of the code EAS is having problems with.