The Planning Repository: HSP_OBJECT and HSP_OBJECT_TYPE

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

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 NameSQL Server TypeOracle TypeDescription
OBJECT_IDintNUMBER(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_NAMEvarchar(80)VARCHAR2(80 CHAR)The name of the object in Planning.
OBJECT_DESCRIPTIONintNUMBER(38,0)The key value from the HSP_STRINGS table that references the description of the object.
OBJECT_TYPEsmallintNUMBER(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_IDintNUMBER(38,0)The OBJECT_ID of the parent for object.
OWNER_IDintNUMBER(38,0)The OBJECT_ID of the owner of the object.
GENERATIONintNUMBER(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.
POSITIONfloatFLOATThe sort-order for the object in relation to other objects in the hierarchy.
HAS_CHILDRENsmallintNUMBER(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_DELETEsmallintNUMBER(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.
CREATEDdatetimeDATEThe date and time that the object was created.
MODIFIEDdatetimeDATEThe date and time that the object was last modified.
MOVEDdatetimeDATEThe date and time that the object was last moved.
OLD_NAMEvarchar(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_IDintNUMBER(38,0)No longer used.
REMOVABLEsmallintNUMBER(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_BYvarchar(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_TYPETYPE_NAME from HSP_OBJECT_TYPETYPE_NAME Made Up By Brian
1Folder
2Dimension
4Calendar
5User
6Group
7Form
9Currency
10Alias
11Cube
12Planning Unit
23NULLType for the parent of all task lists
24NULLTask List
25NULLTask List Item
31Scenario
32Account
33Entity
34Time Period
35Version
38Year
39NULLPlanning Unit Hierarchy
45Shared Member
50User Defined Dimension Member
51NULLPlanning Unit Selection
103NULLMenu
107NULLComposite Form
115NULLCalculation Manager Rule
116NULLCalculation Manager Rule Set
117NULLCalculation Manager Variable
118NULLType for the parent of Calculation Manager Rules
119NULLType for the parent of Calculation Manager Rule Sets
120NULLType for the parent of Calculation Manager Variables
143NULLGood 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):

HSP_OBJECT_QUERY_M

Oracle (SQL Developer):

HSP_OBJECT_QUERY_O

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

Currency Conversion in ASO
Brian @ Kscope15

Comments

  1. Please add me to your mailing list – interesting blog

Leave a Reply

Your email address will not be published / Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.