The Planning Repository: Attribute Dimensions

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

Today we’ll take a brief break from the HSP_MEMBER related tables to lay the foundation for our next HSP_MEMBER post.  I think its important that we take look at our attribute dimensions before we see how they are associated with actual members.  There are two main tables that make up attribute dimension in the repository.  The first is HSP_ATTRIBUTE_DIM which contains the name, type, and associated dimension of our attribute.  The second is HSP_ATTRIBUTE_MEMBER which contains the members of the attribute dimension.  So let’s take our traditional look at the table structures for both.

HSP_ATTRIBUTE_DIM:

Field NameSQL Server TypeOracle TypeDescription
ATTR_IDintNUMBER(38,0)The OBJECT_ID of the attribute.
DIM_IDintNUMBER(38,0)The OBJECT_ID of the associated dimension for the attribute.
ATTR_TYPEsmallintNUMBER(38,0)The type of attribute dimension.
0 = Text
1 = Numeric
2 = Boolean
3 = Date
PERSPECTIVE1intNUMBER(38,0)No ideaÉmay not even ben in use. Let me know if you have a clue.
PERSPECTIVE2intNUMBER(38,0)No ideaÉmay not even ben in use. Let me know if you have a clue.

HSP_ATTRIBUTE_MEMBER:

Field NameSQL Server TypeOracle TypeDescription
ATTR_MEM_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension member.
ATTR_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension.
LEVEL0smallintNUMBER(38,0)This is supposed to tell us if the member is a leaf. It doesn't actually do that.
TEXT_VALvarchar(32)VARCHAR(32 CHAR)In theory this holds a text value for the member. Instead it is always null.
NUMBER_VALdecimal(18,0)NUMBER(38,0)In theory this holds a numeric value for the member. Again, always null.
BOOLEAN_VALsmallintNUMBER(38,0)There's a pattern forming here...always null.
DATE_VALdatetimeDATEYou guessed it...still null.

Obviously we have a few things to talk about.  Starting with the HSP_ATTRIBUTE_DIM, things are pretty straight forward.  We have a magic decoder ring for the attribute type and two columns that I still can’t actually identify.  The comments on the fields read “ID that identifies the 1st independent dimension”.  If you have any idea what that actually mean…drop me a line.  The good news is that it really doesn’t seem to matter.  Let’s have a look at the data:

SELECT
	ATTR_ID
	,o.OBJECT_NAME AS ATTR_NAME
	,DIM_ID
	,od.OBJECT_NAME AS DIM_NAME
	,ATTR_TYPE
	,PERSPECTIVE1
	,PERSPECTIVE2
FROM
	HSP_ATTRIBUTE_DIM ad
INNER JOIN
	HSP_OBJECT o ON ad.ATTR_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON ad.DIM_ID = od.OBJECT_ID

This query should return a list of our attribute dimensions.  As I’m using the Vision sample application, I had to go through and add attribute dimensions.  We join twice to the HSP_OBJECT table to get the names of both the attribute dimension itself and the associated real dimension.  Here’s what it looks like with the joins:

HSP_ATTRIBUTE_DIM

How about the HSP_ATTRIBUTE_MEMBER table?  This one is more frustrating.  From all of my testing and checking, it appears that the only two columns of use are the first two.  After that, the columns either aren’t used (the value columns) or are not properly filled out (LEVEL0 is always 1).  Instead we ignore the value columns and just use the OBJECT_NAME and HAS_CHILDREN columns from our trusty HSP_OBJECT table.  So let’s take a look at a specific attribute dimension:

SELECT
	ATTR_MEM_ID
	,o.OBJECT_NAME AS ATTR_MEM_NAME
	,op.OBJECT_NAME AS ATTR_PARENT
	,ATTR_ID
	,oa.OBJECT_NAME AS ATTR_NAME
	,LEVEL0
	,o.HAS_CHILDREN
	,TEXT_VAL
	,NUMBER_VAL
	,BOOLEAN_VAL
	,DATE_VAL
FROM
	HSP_ATTRIBUTE_MEMBER am
INNER JOIN
	HSP_OBJECT o ON am.ATTR_MEM_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT oa ON am.ATTR_ID = oa.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
WHERE
	oa.OBJECT_NAME = 'AttrText'
ORDER BY
	o.POSITION

This query should return a list of our attributes members.  Again, I had to add these, but there is a little bit of hierarchy to make sure that we understand which columns we should use to identify leaf-level members.  We have three joins to the HSP_OBJECT table this time.  First for the member name, then for the attribute dimension name, and finally for the name of our parent.  We also get the HAS_CHILDREN column from HSP_OBJECT table as the LEVEL0 column is always 1.  Perhaps when this table originated, the HSP_OBJECT table didn’t include HAS_CHILDREN.  So here’s the resulting data:

HSP_ATTRIBUTE_MEMBER

And that’s it for Attribute Dimensions.  We’ll dive into the relationship between members and attribute dimensions in our next post on the repository.

The Planning Repository: HSP_MEMBER (Part 4 – HSP_MEMBER_FORMULA)
The Planning Repository: HSP_MEMBER (Part 5 – Attribute Associations)

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.