The Planning Repository: HSP_OBJECT and HSP_OBJECT_TYPE

Brian Marshall

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:

Attention: The internal data of table “1” is corrupted!

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:

Attention: The internal data of table “2” is corrupted!

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

Author:

One Comment

  1. Michele
    MicheleReply
    October 2, 2017 at 8:00 pm

    Please add me to your mailing list – interesting blog

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>

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