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 22.214.171.124.500 and 126.96.36.199.
- 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 Name||SQL Server Type||Oracle Type||Description|
|DIM_ID||int||NUMBER(38,0)||The OBJECT_ID of the dimension.|
|ENFORCE_SECURITY||int||NUMBER(38,0)||Determines if security is being enforced on this dimension.
0 = Don't Enforce Security
1 = Enforce Security
|DIM_TYPE||smallint||NUMBER(38,0)||The type of dimension.
0 = None
1 = Account
2 = Time
3 = Entity
7 = Entity Hierarchy
|DENSITY||int||NUMBER(38,0)||No longer used.|
|USED_IN||int||NUMBER(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_EDITOR||smallint||NUMBER(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
|DENSITY1||smallint||NUMBER(38,0)||The Essbase setting for density of the dimension.
0 = Dense
1 = Sparse
|POSITION1||smallint||NUMBER(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.|
|ENUM_ORDER1||smallint||NUMBER(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.|
|DRIVER_DIM_ID||int||NUMBER(38,0)||The OBJECT_ID of the dimension configured for data loads in Planning.|
|DISPLAY_OPTION||int||NUMBER(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 Type 1||1||Plan1|
|Plan Type 2||2||Plan2|
|Plan Type 3||4||Plan3|
|Plan Type 4||8||Wrkforce|
|Plan Type 5||16||Capex|
|Plan Type 6||32||Project|
|Plan Type 7||64||PlanASO1|
|Plan Type 8||128||PlanASO2|
|Plan Type 9||256||PlanASO3|
|Plan Type 10||512||PlanASO4|
|Plan Type 11||1024||PlanASO5|
|Plan Type 12||2048||PlanASO6|
|Plan Type 13||4096||PlanASO7|
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.
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
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!
October 21, 2020 - 7:34 am
Thanks Brian. Great reference. Thank you for your blog