The Planning Repository: HSP_DIMENSION

Brian Marshall

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:

Field NameSQL Server TypeOracle TypeDescription
DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension.
ENFORCE_SECURITYintNUMBER(38,0)Determines if security is being enforced on this dimension.
0 = Don't Enforce Security
1 = Enforce Security
DIM_TYPEsmallintNUMBER(38,0)The type of dimension.
0 = None
1 = Account
2 = Time
3 = Entity
7 = Entity Hierarchy
DENSITYintNUMBER(38,0)No longer used.
USED_INintNUMBER(38,0)The plan type usage for the dimension This is a bitmask that tells us which plan types are used in a single integer.
DIM_EDITORsmallintNUMBER(38,0)Determines if the standard dimension editor can be used.
0 = Non-standard dimension, like period, scenario, etc.
1 = Standard dimension that can be modified with the standard editor , like account, entity, and custom dimensions
DENSITY1smallintNUMBER(38,0)The Essbase setting for density of the dimension.
0 = Dense
1 = Sparse
DENSITY2smallintNUMBER(38,0)See above
DENSITY3smallintNUMBER(38,0)See above
DENSITY4smallintNUMBER(38,0)See above
DENSITY5smallintNUMBER(38,0)See above
DENSITY6smallintNUMBER(38,0)See above
DENSITY7smallintNUMBER(38,0)See above
POSITION1smallintNUMBER(38,0)The Essbase setting for the position of the dimension. The position hear should line up with the number in the performance section of the GUI.
POSITION2smallintNUMBER(38,0)See above
POSITION3smallintNUMBER(38,0)See above
POSITION4smallintNUMBER(38,0)See above
POSITION5smallintNUMBER(38,0)See above
POSITION6smallintNUMBER(38,0)See above
POSITION7smallintNUMBER(38,0)See above
POSITION8smallintNUMBER(38,0)See above
POSITION9smallintNUMBER(38,0)See above
POSITION10smallintNUMBER(38,0)See above
POSITION11smallintNUMBER(38,0)See above
POSITION12smallintNUMBER(38,0)See above
POSITION13smallintNUMBER(38,0)See above
POSITION14smallintNUMBER(38,0)See above
POSITION15smallintNUMBER(38,0)See above
ENUM_ORDER1smallintNUMBER(38,0)The evaluation order of the dimension. The enum name comes Smart Lists which are stored in the HSP_ENUMERATION and HSP_ENUMERATION_ENTRY tables.
ENUM_ORDER2smallintNUMBER(38,0)See above
ENUM_ORDER3smallintNUMBER(38,0)See above
ENUM_ORDER4smallintNUMBER(38,0)See above
ENUM_ORDER5smallintNUMBER(38,0)See above
ENUM_ORDER6smallintNUMBER(38,0)See above
ENUM_ORDER7smallintNUMBER(38,0)See above
ENUM_ORDER8smallintNUMBER(38,0)See above
ENUM_ORDER9smallintNUMBER(38,0)See above
ENUM_ORDER10smallintNUMBER(38,0)See above
ENUM_ORDER11smallintNUMBER(38,0)See above
ENUM_ORDER12smallintNUMBER(38,0)See above
ENUM_ORDER13smallintNUMBER(38,0)See above
ENUM_ORDER14smallintNUMBER(38,0)See above
ENUM_ORDER15smallintNUMBER(38,0)See above
DRIVER_DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension configured for data loads in Planning.
DISPLAY_OPTIONintNUMBER(38,0)The display option selected for the dimension.
0 or null = Member name
1 = Alias
2 = Member name and Alias
3 = Alias and Member name
4 = Member count (I think this legacy)
5 = Member name and Description (I think this is legacy)

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:

Plan TypeIntegerExample
Plan Type 11Plan1
Plan Type 22Plan2
Plan Type 34Plan3
Plan Type 48Wrkforce
Plan Type 516Capex
Plan Type 632Project
Plan Type 764PlanASO1
Plan Type 8128PlanASO2
Plan Type 9256PlanASO3
Plan Type 10512PlanASO4
Plan Type 111024PlanASO5
Plan Type 122048PlanASO6
Plan Type 134096PlanASO7

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):

HSP_PLAN_TYPE_M

Oracle (SQL Developer):

HSP_PLAN_TYPE_O

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!

Author:

Leave a Reply

Name*
Email*
Url
Your message*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>