The Planning Repository: HSP_DIMENSION

Not to sound like a broken record, but…a few disclaimers:

• This is the fun stuff…that voids your warranty.  Not that you have to tell Oracle that you poke around here, but if you do, they will blame anything and everything that’s wrong on you for being in there.
• The content here has been tested and validated against 11.1.2.3.500 and 11.1.2.4.
• The content here has been tested and validated against Microsoft SQL Server and Oracle 11g.
• The content here is based on the Vision sample application.
• The content here is totally unsupported by Oracle (in case you missed the first bullet).

In our last post, we covered the HSP_OBJECT table.  Now that we’ve covered the basics, let’s dig a little deeper and start to look at how Planning manages meta-data inside of our applications.  The starting point for this is the HSP_DIMENSION table.  This table will have a pretty light set of data (just a few more rows than the number of dimensions in your app) but has quite a few properties.  Each of the properties for the dimensions that you set in the GUI will be found here.  Let’s take a look at the table structure:

If you’ve looked at this table in the past, not too much has changed.  There are a few interesting things to mention.  First let’s look at the USED_IN field.  This field is a bitmask.  So what does that mean?  It means that Oracle is the devil.  That’s what it means.  Bitmasks are among the most annoying things you can run across when decoding someone else’s database.  But here we are, so let’s take a look.  The developers of Planning have chosen to use a single integer field to tell us all of the plan types that a specific dimension can be part of.  This means that we have up to 13 different pieces of information that are stored in a single integer.  So how do they do this?  The easiest way to illustrate this is with a table.  Here we see how we would represent all 13 plan types numerically:

So how do all of these become a single integer?  Simple  math.  Add up the ones that the dimension should be used in.  So let’s run through a few examples:

Plan Types 1 and 3:  1 + 4 = 5

Plan Types 1, 2, and 3:  1 + 2 + 4 = 7

Plan Types 1 – 13: 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256 + 1048 + 2048 + 4096 = 8191

So that’s all great, but how do I determine if the plan type is used without having to build a giant set of logic?  The bitwise operator!  Basically we ask if the number is included in our sum.  For instance, is if my sum is 5, is Plan Type 2 included:

SELECT 5 & 2

This will return a 0 because 5 is a combination of 1 and 4 and does not include 2.

Let’s try a different example that returns a different an affirmative response.  If my sum is 7, is Plan Type 2 included:

SELECT 7 & 2

This will return 2.  It returns 2 because 7 does include 2.  So we can basically assume if I use the bitwise & operator and the result is not 0, then its included.

Wow…this post is getting long.  But hey, let’s keep going anyway, if you are still awake now, you must really want or need to know about how this bitwise thing works.

So how do we use this in a practical way?  Let’s take a look at the HSP_PLAN_TYPE table.  I won’t waste any space showing the structure of this table because it’s basically the same as my sample above.  The plan types by name and their numerical equivalent.  So here’s a practical example of how we can determine all of the plan types that a dimension belongs to.

SQL Server:

SELECT 
	TYPE_NAME
	,PLAN_TYPE
	,CASE WHEN (SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 31) & PLAN_TYPE = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
	HSP_PLAN_TYPE

Oracle:

SELECT 
	TYPE_NAME
	,PLAN_TYPE
	,CASE WHEN BITAND((SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 53702),PLAN_TYPE) = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
	HSP_PLAN_TYPE

Both queries should return a list of all of our Plan Types and whether or not the dimension in our where clause is used:

SQL Server (Management Studio):

Oracle (SQL Developer):

So now that we have made it past that diversion, let’s keep looking at our table.  The rest of the dimension table is pretty well described by the table at the very beginning of this post (does anyone remember that far back?).  But I do want to note a few interesting things.  First, you will notice that we have only 7 density fields but for position and enumeration we have 15.  Why?  I scratched my head for a minute on this one, but then it hit me…ASO doesn’t care.  So basically the repository support 7 BSO plan types and and additional 8 ASO plan types.  Now if we look at the actual limitations in Planning, we can max out Planning with 13 plan types (which you can see in our sample table above).  So we basically have an extra BSO plan type and an extra ASO plan type.  It’s also possible that they use these extra fields for some other system purpose that I’m not aware of.  If you know, drop me a comment!

So there you have it…an unexpected dive into some pretty technical workings of SQL code just to look at our dimensions.  Now that we’ve had a look at the dimensions themselves, we can start looking at the members…in our next marathon post!