Hybrid Essbase: Rapidly Make Parents Dynamic Calc

Hybrid Essbase is the biggest advancement in Essbase technology since ASO was released.  It truly takes Essbase to another level when it comes to getting the best out of both ASO and BSO technology.  Converting your application from BSO to Hybrid can be a long process.  You have to make sure that all of your calculations still work the way they should.  You have to make sure that your users don’t break Hybrid mode.  You have to update the storage settings for all of your sparse dimensions.

I can’t help you with the first items, they just take time and effort.  What I can help you with is the time required to update your sparse dimensions.  I spend a lot of time hacking around in the Planning repository.  I suddenly found a new use for all of that time spent with the repository…getting a good list of all of the upper level members in a dimension.  If we just export a dimension, we get a good list, but we have to do a lot of work to really figure out which members are parents and which are not.  Luckily, the HSP_OBJECT table has column that tells us just that: HAS_CHILDREN.

Microsoft SQL Server

The query to do this is very, very simple.  The process for updating your dimensions using the query takes a little bit more explanation.  We’ll start with SQL Server since that happens to be where I’m the most comfortable.  I’m going to assume you are using SQL Server Management Studio…because why wouldn’t you?  It’s awesome.  Before we even get to the query, we first need to make a configuration change.  Open Management Studio and click on Tools, then Options.

HybridEssbase01

Expand Query Results, then expand SQL Server, and then click on Results to Grid:

HybridEssbase02

Check the box titled Include column headers when copying or saving the results and click OK.  Why did we start here?  Because we have to restart Management Studio for the new setting to actually take affect.  So do that next…

Now that we have Management Studio ready to go, we can get down to the query.  Here it is in all of its simplicity:

SELECT
	o.OBJECT_NAME AS Product
	,po.OBJECT_NAME AS Parent
	,'dynamic calc' AS [Data Storage (Plan1)]
FROM
	HSP_OBJECT o
INNER JOIN
	HSP_MEMBER m ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_DIMENSION d ON m.DIM_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT do ON do.OBJECT_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT po ON po.OBJECT_ID = o.PARENT_ID
WHERE
	do.OBJECT_NAME = 'Product'
	AND o.HAS_CHILDREN = 1

We have a few joins and a very simple where clause.  As always, I’m using my handy-dandy Vision demo application.  A quick look at the results shows us that there are very few parents in the Product dimension:

HybridEssbase03

Now we just need to get this into a format that we can easily import back into Planning.  All we have to do it right-click anywhere in the results and click on Save Results As….  Enter a file name and click Save.

HybridEssbase05

Now we should have a usable format for a simple Import to update our dimension settings.  Let’s head to workspace and give it a shot.  Fire up your Planning application and click on Administration, then Import and Export, and finally Import Metadata from File:

HybridEssbase06

Select your dimension from the list and then browse to find your file.  Once the file has uploaded, click the Validate button.  This will at least tell us if we have a properly formatted CSV:

HybridEssbase07

That looks like a good start.  Let’s go ahead and complete the import and see what happens:

HybridEssbase08

This looks…troubling.  One rejected record.  Let’s take a look at our logs to see why the record was rejected:

HybridEssbase09

As we can see, nothing to worry about.  The top-level member of the dimension is rejected because there is no valid parent.  We can ignore this and go check to see if our changes took affect.

HybridEssbase10

At first it looks like we may have failed.  But wait!  Again, nothing to worry about yet.  We didn’t update the default data storage.  We only updated Plan1.  So let’s look at the data storage property for Plan1:

HybridEssbase11

That’s more like it!

Oracle Database

But wait…I have an Oracle DB for my repository.  Not to worry.  Let’s check out how to do this with Oracle and SQL Developer.  First, let’s take a look at the query:

SELECT
	o.OBJECT_NAME AS Product
	,po.OBJECT_NAME AS Parent
	,'dynamic calc' AS "Data Storage (Plan1)"
FROM
	HSP_OBJECT o
INNER JOIN
	HSP_MEMBER m ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_DIMENSION d ON m.DIM_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT do ON do.OBJECT_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT po ON po.OBJECT_ID = o.PARENT_ID
WHERE
	do.OBJECT_NAME = 'Product'
	AND o.HAS_CHILDREN = 1

This is very, very similar to the SQL Server query.  The only real difference is the use of double quotes instead of brackets around our third column name.  A small, yet important distinction.  Let’s again look at the results:

HybridEssbase12

The Oracle results look just like the SQL Server results…which is a good thing.  Now we just have to get the results into a usable CSV format for import.  This will take a few more steps, but it still very easy.  Right click on the result set and click Export:

HybridEssbase13

Change the export format to csv, choose a location and file name, and then click Next.

HybridEssbase14

Click Finish and we should have our CSV file ready to go.  Let’s fire up our Planning application and click on Administration, then Import and Export, and finally Import Metadata from File:

HybridEssbase06

Select your dimension from the list and then browse to find your file.  Once the file has uploaded, click the Validate button.  This will at least tell us if we have a properly formatted CSV:

HybridEssbase15

 

Much like the SQL Server file, this looks like a good start.  Let’s go ahead and complete the import and see what happens:

HybridEssbase16

Again, much like SQL Server, we have the same single rejected record.  Let’s make sure that the same error message is present:

HybridEssbase09

As we can see, still nothing to worry about.  The top-level member of the dimension is rejected because there is no valid parent.  We can ignore this and go check to see if our changes took affect.

HybridEssbase10

As with SQL Server, we did not update the default data storage property, only Plan1.  So let’s look at the data storage property for Plan1:

HybridEssbase11

And just like that…we have a sparse dimension ready for Hybrid Essbase.  Be sure to refresh your database back to Essbase.  You can simply enter a different dimension name in the query and follow the same process to update the remaining sparse dimensions.

Celvin also has an excellent Essbase utility that will do this for you, but it makes use of the API and Java, and generally is a bit more complicated than this method if you have access to the repository.  So what happens if you can’t use the API and you can’t access the repository?  We have another option.  We’ll save that for another day, this blog post is already long enough!