Welcome to the first of a long series of posts describing the Planning Repository. The point of this series is to describe many of the tables and their purpose inside of the relational repository that supports your Hyperion Planning applications. Additionally, we’ll take a look at things like deleting a dimension or years from your application.
First…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 184.108.40.206.500 and 220.127.116.11.
- 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).
We’ll start the series with the HSP_OBJECT table. The HSP_OBJECT table is essentially the foundation for the repository. Every object in Planning (forms, dimension members, task lists, etc.) will have a record in this table. Let’s start by taking a look at the columns that make up the HSP_OBJECT table:
|Field Name||SQL Server Type||Oracle Type||Description|
|OBJECT_ID||int||NUMBER(38,0)||The unique ID of the object. ÊThis is not an identity or auto-number field, rather it is actually generated internally by Hyperion Planning. ÊThe ID will be referenced throughout the repository in virtually every other table.|
|OBJECT_NAME||varchar(80)||VARCHAR2(80 CHAR)||The name of the object in Planning.|
|OBJECT_DESCRIPTION||int||NUMBER(38,0)||The key value from the HSP_STRINGS table that references the description of the object.|
|OBJECT_TYPE||smallint||NUMBER(38,0)||We'll cover this a little later when we get to the HSP_OBJECT_TYPE table. But in short, this is an integer value representing the type of object for the record (form, member, etc.). Sometimes this integer can be found in the HSP_OBJECT_TYPE table, sometimes it can't. More on this later.|
|PARENT_ID||int||NUMBER(38,0)||The OBJECT_ID of the parent for object.|
|OWNER_ID||int||NUMBER(38,0)||The OBJECT_ID of the owner of the object.|
|GENERATION||int||NUMBER(38,0)||The generation of the object if applicable and 0 if not. Objects like members and Planning Units have generations while things like forms do not.|
|POSITION||float||FLOAT||The sort-order for the object in relation to other objects in the hierarchy.|
|HAS_CHILDREN||smallint||NUMBER(38,0)||The flag that determines if a member is a leaf or not. If the member has children and is therefore not a leaf, the value is 1, otherwise it is 0. So if you are looking for leaves, look for 0.|
|MARKED_FOR_DELETE||smallint||NUMBER(38,0)||Members that have been deleted from Planning but not refreshed to Essbase will be marked for deletion with a value of 1. Otherwise this value should be 0.|
|CREATED||datetime||DATE||The date and time that the object was created.|
|MODIFIED||datetime||DATE||The date and time that the object was last modified.|
|MOVED||datetime||DATE||The date and time that the object was last moved.|
|OLD_NAME||varchar(80)||VARCHAR2(80 CHAR)||This field is used to maintain data in Essbase when a member is renamed. Prior to refreshing Essbase, the old name of the member will be here. Once the refresh is complete, the OBJECT_NAME and OLD_NAME will be the same.|
|SECCLASS_ID||int||NUMBER(38,0)||No longer used.|
|REMOVABLE||smallint||NUMBER(38,0)||The flag that determins if a member can be deleted. If an object can be deleted, the value is 1, otherwise it is 0.|
|MODIFIED_BY||varchar(80)||VARCHAR2(80 CHAR)||The user ID of the last user to modify the member. If you want any additional auditing of modifications to members, you have to hit the audit tables assuming you have them turned on.|
This table, for all of its importance to Planning isn’t all that interesting. There are really only two fields that require a little more information than the table above. The first is OBJECT_TYPE and the second is DESCRIPTION. The OBJECT_TYPE field is of particular interest if you really look at the data. For instance, if we were to check the OBJECT_TYPE contents against the HSP_OBJECT_TYPE table, we’ll find that not all types have made it into the table. I’ve gone through and identified all of the types that I have in my sample application. Here’s the full list:
|OBJECT_TYPE||TYPE_NAME from HSP_OBJECT_TYPE||TYPE_NAME Made Up By Brian|
|23||NULL||Type for the parent of all task lists|
|25||NULL||Task List Item|
|39||NULL||Planning Unit Hierarchy|
|50||User Defined Dimension Member|
|51||NULL||Planning Unit Selection|
|115||NULL||Calculation Manager Rule|
|116||NULL||Calculation Manager Rule Set|
|117||NULL||Calculation Manager Variable|
|118||NULL||Type for the parent of Calculation Manager Rules|
|119||NULL||Type for the parent of Calculation Manager Rule Sets|
|120||NULL||Type for the parent of Calculation Manager Variables|
|143||NULL||Good questionÉsomething related to Business Metrics|
Now let’s talk about DESCRIPTION. This is the first occurence of the HSP_STRINGS table. This table itself is pretty simple. It’s just a list of strings used in various places inside of Planning. I believe the idea here was to save space in the HSP_OBJECT table. Descriptions could be quite (up to 255 characters) so why allocate space in a table for long strings when you can separate them out? So how do we actually see the description? A simple join:
SQL Server and Oracle Code:
SELECT OBJECT_ID ,OBJECT_NAME --,DESCRIPTION ,THE_STRING ,OBJECT_TYPE ,PARENT_ID ,OWNER_ID ,GENERATION ,POSITION ,HAS_CHILDREN ,MARKED_FOR_DELETE ,CREATED ,MODIFIED ,MOVED ,OLD_NAME ,SECCLASS_ID ,REMOVABLE ,MODIFIED_BY FROM HSP_OBJECT o LEFT JOIN HSP_STRINGS s ON o.DESCRIPTION = s.STRING_SEQ
The results of the query should render all of our objects along with their description (and every other field in the HSP_OBJECT table):
SQL Server (Management Studio):
Oracle (SQL Developer):
That’s it for the HSP_OBJECT table! Next we’ll start looking at the way Planning stores meta-data (the HSP_DIMENSION table first and then HSP_MEMBER).