The Planning Repository: HSP_MEMBER (Part 3 – UDA’s)

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

Eventually we will find all of tables required to get all of the important information about members out of the repository.  Our next step is to go find where UDA’s are hiding.  Much like aliases, Planning allows for an undefined number of UDA’s.  So, of course we need another table…or two.  The good news is that we actually have tables outside of the HSP_OBJECT table.

There are two tables we will be using to identify the UDA’s associated with members.  First, let’s take a look at the HSP_UDA table:

As far as Planning tables go, few are more straight forward than this one.  Contrary to aliases, UDA’s do not even merit a row in the HSP_OBJECT table.  HSP_UDA is it.  So how do members get associated with these UDA’s?  Another table of course!  Let’s take a look at the HSP_MEMBER_TO_UDA table:

And I just got done saying that few are more straight forward than HSP_UDA…well, this one is even more straight forward.  Given that this one is entirely self-explanatory, let’s go straight to combining this with the the HSP_MEMBER table.  This will require a little bit more complexity.  The methods are entirely different between Microsoft and Oracle.

SQL Server:

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,REPLACE(REPLACE(REPLACE((
		SELECT
			ou.UDA_VALUE AS UDA
		FROM 
			HSP_MEMBER_TO_UDA u
		INNER JOIN
			HSP_UDA ou ON ou.UDA_ID = u.UDA_ID
		WHERE
			u.MEMBER_ID = m.MEMBER_ID FOR XML Raw)
			, '"/>', '' )
	 AS MemberUDA
	,od.OBJECT_NAME AS DIMENSION_NAME
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Account'

Instead of a table join, we have to do a sub-query.  Basically, we want to get a list of all of our UDA’s associated with each member without each member having multiple rows.  So our sub-query gets the list of members first and then we use the For XML Raw command to put the results of the sub-query into XML format.  From there, we replace the XML parts with commas to give us a nice clean result:

Oracle:

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,od.OBJECT_NAME AS DIMENSION_NAME
  ,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST"
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
  ( SELECT 
      UDA_VALUE
      ,MEMBER_ID
    FROM
      HSP_MEMBER_TO_UDA mu
    INNER JOIN
      HSP_UDA u ON mu.UDA_ID = u.UDA_ID
    ) mu ON m.MEMBER_ID = mu.MEMBER_ID
WHERE
	od.OBJECT_NAME = 'Account'
GROUP BY
  o.OBJECT_NAME
  ,op.OBJECT_NAME
  ,od.OBJECT_NAME

In Oracle, we do a little of both.  First we do a LEFT JOIN to a sub-query that returns all of our associated UDA’s for each member.  If we left it at that, when a member has three UDA’s associated, it would have three rows in the result set.  But, we made use of an excellent function to aggregate our list and separate it with commas.  The LISTAGG function let’s you choose a value and a separator and then how you want to order the list.  The downside to this function is that we have to GROUP BY all of our other columns.  So be sure to include anything you add to the query to the GROUP BY as well.  Here’s the result:

This is our first major difference in the queries that we execute against the repository.  As luck would have it, we can get the same result regardless of our back end technology, we just need the right functions.  That’s it for UDA’s!  In our next look at the Planning Repository, we’ll dive into member formulas.

The Planning Repository: HSP_MEMBER (Part 2 – HSP_ALIAS)

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

Now that we have the annoying complex pieces of the HSP_MEMBER table figured out (bitmasks), let’s move on to the easier to obtain and far more gratifying information about our meta-data in Hyperion Planning.  This is where we leave the HSP_MEMBER table and go looking for everything else.  The focus of this post will be the HSP_ALIAS table.

So why aren’t aliases in the HSP_MEMBER table?  The answer is simply that Planning allows for an undefined number of alias tables.  So why add a large number of columns to the main table?  Instead we have an HSP_ALIAS table.  Before we get into that table, let’s first determine how many alias tables we are dealing with.  For that, we just go to the HSP_ALIAS_TABLE table…oh wait.  That doesn’t exist.  

Instead, we have to query the HSP_OBJECT table to determine our alias tables.  But at least if we have to use the HSP_OBJECT table, we should be able to filter that easily using something like OBJECT_TYPE, right?  Of course not…if we do that, aliases have an OBJECT_TYPE of 1 which equates to a folder.  So because there are a lot of folders in Planning, that’s not terribly helpful.  But…at least there is a parent for us to query.  So here goes:

SELECT
	o.OBJECT_NAME AS ALIAS_TABLE
FROM
	HSP_OBJECT o
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
WHERE
	op.OBJECT_NAME = 'Aliases'

Pretty simple…though it still seems like a round-about way to get our results:

So we can see that we have two alias tables.  Now we should be ready to go take a look at the HSP_ALIAS table.  Let’s start by looking at the structure:

So basically, there isn’t much going on here.  Probably the most interesting thing about this table is that it just provides a reference back to the HSP_OBJECT table.  So each alias also exists in the Planning repository as an object.  So now let’s add alias information to our HSP_MEMBER query:

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,a1.ALIAS_NAME AS ALIAS_DEFAULT
	,a2.ALIAS_NAME AS ALIAS_ENGLISH
	,od.OBJECT_NAME AS DIMENSION_NAME
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
	(	SELECT
			oa.OBJECT_NAME AS ALIAS_NAME
			,a.MEMBER_ID
		FROM
			HSP_ALIAS a
		INNER JOIN
			HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
		INNER JOIN
			HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
		WHERE
			oat.OBJECT_NAME = 'Default')
	 a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
	(	SELECT
			oa.OBJECT_NAME AS ALIAS_NAME
			,a.MEMBER_ID
		FROM
			HSP_ALIAS a
		INNER JOIN
			HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
		INNER JOIN
			HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
		WHERE
			oat.OBJECT_NAME = 'English')
	 a2 ON m.MEMBER_ID = a2.MEMBER_ID
WHERE
	od.OBJECT_NAME = 'Product'

I know what you’re thinking.  I cheated.  And its true, I used a sub-query join for each of the aliases that I knew existed.  Not very dynamic, but it gets the job done.  So let’s look at the results:

That’s it for aliases.  If you plan to use this type of query for other downstream targets, like say an Essbase cube, you may want to consider putting in some code to compare the two aliases.  Essbase will not allow non-unique aliases even for the same member.  Next up in our series will cover UDA’s.  Stay tuned…

The Planning Repository: HSP_MEMBER (Part 1 – The Table)

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

In our last post, we covered the HSP_DIMENSION table.  HSP_DIMENSION is the starting point for all things meta-data, but no actual members live there.  Enter the HSP_MEMBER table.  Any member added to your application will be added to this table, regardless of the dimension.  This table contains all of the properties common to all dimensions whether they are built-in (Account, Entity, Etc.) or custom.  This will be part one of a two-part series related to members.  There are too many other tables to get into for just one post.  Let’s take a look at the table structure:

If you have ever looked at this table in past releases, you will notice a pretty big difference.  The new field CONSOL_OP has replaced all of the CONSOL_OP# fields.  We’ll touch on that in a bit.  The other change you will see is that they added a new field named HIERARCHY_TYPE.  This field was added to support ASO plan types as the hierarchies in ASO can be either Stored or Dynamic.  What you won’t see in this table are some important items: the actual member name, the member name of the parent, aliases, attributes, UDA’s, formulas, and properties related to accounts, entities, scenarios, and versions.  Given that almost everything I just listed is in another table of its very own…this is a multi-part post.

Let’s start by querying just our basic member information that every member in Planning will have.  To do this we just need two tables: HSP_MEMBER (for the member information) and HSP_OBJECT (for the member name and the member name of the parent).  So let’s start with an easy query to get the member name, parent name, and a few of the simple to decode properties:

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,od.OBJECT_NAME AS DIMENSION_NAME
    ,CASE m.DATA_STORAGE
		WHEN 0 THEN 'Store Data'
		WHEN 1 THEN 'Never Share'
		WHEN 2 THEN 'Label Only'
		WHEN 3 THEN 'Shared Member'
		WHEN 4 THEN 'Dynamic Calc and Store'
		WHEN 5 THEN 'Dynamic'
	 END AS DATA_STORAGE
	 ,CASE m.DATA_TYPE
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'Currency'
		WHEN 2 THEN 'Non-currency'
		WHEN 3 THEN 'Percentage'
		WHEN 4 THEN 'Enum'
		WHEN 5 THEN 'Date'
		WHEN 6 THEN 'Text'
		ELSE 'Unspecified'
	 END AS DATA_TYPE
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Product'

So if we look at the query, there are several things of note.  First, we have three joins, all of which are the HSP_OBJECT table.  The first join is on MEMBER_ID.  This join provides us with access to the member name and the OBJECT_ID of the parent member.  The second joins the parents OBJECT_ID (PARENT_ID) from the first join to get the actual member name of the parent.  And the third joins the dimension’s OBJECT_ID (DIM_ID) from the HSP_MEMBER table to give us the actual name of the dimension.

After we get everything joined, we just need to decode a few of the simple fields.  We use simple case statements for DATA_STORAGE and DATA_TYPE.  It should also be noted that the DATA_STORAGE here is not necessarily that useful.  Because Planning allows us to have a different data storage for each plan type, we will have another table to look at this later.  This is the main setting from the member properties screen only.

So let’s look at the results.

SQL Server (Management Studio):

Oracle (SQL Developer):

That was easy…what’s the big deal?  Oh wait…we still don’t have consolidation operators.  In 11.1.2.2 and earlier, this was a simple decode.  There were separate columns for each plan type.  Starting in 11.1.2.3, they changed the structure of HSP_MEMBER.  We now have just one CONSOL_OP field and it uses a bitmask (again).  Even worse, this bitmask contains multiple values, because figuring out one value wasn’t painful enough!

So how do we store multiple values in one integer?  In this instance, they are again using the bitmask using three bits per value.  So to store a decimal 1, it becomes 001.  As we combine multiple values, we do so going from right to left.  So if I want to store decimal 1 and decimal 2 in that order, the value becomes 010001.  This value is then stored as an integer.  I’ve noticed that the integer stored is actually quite a bit bigger than just the boxes I’ve checked, but for our purposes today, we don’t care.  So how do we actually determine the consolidation operator for each of our plan types?

For the first plan type, this is an easy operation.  We can just use our bitwise operator or BITAND function to check the value without any trouble.  The real difficulty comes in when we try to do this for the second position and beyond.  The good news is we know that each value is stored in 3 bits.  This means we just need to shift over three bits to check the next value.  To do that, we just multiply the number by 2 to the power of the number of positions we want to shift.  So for the second plan type, we shift 2 to the power of 3.  For our third plan type we shift 2 to the power of 6.  But one last thing.  Before we do this, we should check the USED_IN field to verify that we even need to check the operator.  So here goes:

SQL Server:

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,od.OBJECT_NAME AS DIMENSION_NAME
    ,CASE m.DATA_STORAGE
		WHEN 0 THEN 'Store Data'
		WHEN 1 THEN 'Never Share'
		WHEN 2 THEN 'Label Only'
		WHEN 3 THEN 'Shared Member'
		WHEN 4 THEN 'Dynamic Calc and Store'
		WHEN 5 THEN 'Dynamic'
	 END AS DATA_STORAGE
	 ,CASE m.DATA_TYPE
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'Currency'
		WHEN 2 THEN 'Non-currency'
		WHEN 3 THEN 'Percentage'
		WHEN 4 THEN 'Enum'
		WHEN 5 THEN 'Date'
		WHEN 6 THEN 'Text'
		ELSE 'Unspecified'
	 END AS DATA_TYPE
	 ,CONSOL_OP
	,CASE WHEN m.USED_IN & 1 = 1 THEN 
		CASE 
			WHEN m.CONSOL_OP & 6 = 6 THEN '^' 
			WHEN m.CONSOL_OP & 5 = 5 THEN '~' 
			WHEN m.CONSOL_OP & 4 = 4 THEN '%' 
			WHEN m.CONSOL_OP & 3 = 3 THEN '/' 
			WHEN m.CONSOL_OP & 2 = 2 THEN '*' 
			WHEN m.CONSOL_OP & 1 = 1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL1_OP
	,CASE WHEN m.USED_IN & 2 = 2 THEN 
		CASE 
			WHEN m.CONSOL_OP & POWER(2,3)*6 = POWER(2,3)*6 THEN '^' 
			WHEN m.CONSOL_OP & POWER(2,3)*5 = POWER(2,3)*5 THEN '~' 
			WHEN m.CONSOL_OP & POWER(2,3)*4 = POWER(2,3)*4 THEN '%' 
			WHEN m.CONSOL_OP & POWER(2,3)*3 = POWER(2,3)*3 THEN '/' 
			WHEN m.CONSOL_OP & POWER(2,3)*2 = POWER(2,3)*2 THEN '*' 
			WHEN m.CONSOL_OP & POWER(2,3)*1 = POWER(2,3)*1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL2_OP
	,CASE WHEN m.USED_IN & 4 = 4 THEN 
		CASE 
			WHEN m.CONSOL_OP & POWER(2,6)*6 = POWER(2,6)*6 THEN '^' 
			WHEN m.CONSOL_OP & POWER(2,6)*5 = POWER(2,6)*5 THEN '~' 
			WHEN m.CONSOL_OP & POWER(2,6)*4 = POWER(2,6)*4 THEN '%' 
			WHEN m.CONSOL_OP & POWER(2,6)*3 = POWER(2,6)*3 THEN '/'
			WHEN m.CONSOL_OP & POWER(2,6)*2 = POWER(2,6)*2 THEN '*' 
			WHEN m.CONSOL_OP & POWER(2,6)*1 = POWER(2,6)*1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL3_OP
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Account'

Oracle:

SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,od.OBJECT_NAME AS DIMENSION_NAME
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CONSOL_OP
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL1_OP
  ,CASE WHEN BITAND(m.USED_IN,2) = 2 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*6) = POWER(2,3)*6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*5) = POWER(2,3)*5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*4) = POWER(2,3)*4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*3) = POWER(2,3)*3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*2) = POWER(2,3)*2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*1) = POWER(2,3)*1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL2_OP
  ,CASE WHEN BITAND(m.USED_IN,4) = 4 THEN 
  CASE 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*6) = POWER(2,6)*6 THEN '^' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*5) = POWER(2,6)*5 THEN '~' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*4) = POWER(2,6)*4 THEN '%' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*3) = POWER(2,6)*3 THEN '/' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*2) = POWER(2,6)*2 THEN '*' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*1) = POWER(2,6)*1 THEN '-' 
    ELSE '+' END
  ELSE
    NULL
  END AS CONSOL3_OP
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Account'

As you can see, for the first plan type, we just use a regular & or BITAND to determine the consolidation operator.  For the second and third plan types we use the power function with our base number of 2 and the number of positions we want to move to the left (if we wanted to go to the right, we would divide by the power function). 

Before we look at the results, we have just a few other important notes.  Because a negative test by both the & and BITAND function returns a 0, we can’t test for the value of 0, which is of course our most common operator (+).  Instead we have to exclude all other options that we can test for.  Assuming all of our other options fail, it must be 0 (+). 

Another similar issue results in our reverse order case statement.  Because case statement stop evaluating at the first successful case, we have to look at our bigger numbers first.  For instance, if 3 (011) or 5 (101) are compared to 1 (001) first, it would always evaluate to true.  Technically, 001 will fit into 011 and 101.  Of course that’s not what we’re looking for, so I just chose to test from biggest to smallest.  I haven’t been a DBA in a long…long time.  So for those of you out there smarter than me, let me know if there’s a better way!

And finally…let’s look at the results:

SQL Server (Management Studio):

Oracle (SQL Developer):

Works great!  You can add as many plan types as you want to this query with the examples above.  And that’s enough for one post.  In part two, we’ll go find all of those other important things that are missing from the HSP_MEMBER table.

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:

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