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