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.