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 Name | SQL Server Type | Oracle Type | Description |
|---|---|---|---|
| ATTR_ID | int | NUMBER(38,0) | The OBJECT_ID of the attribute. |
| DIM_ID | int | NUMBER(38,0) | The OBJECT_ID of the associated dimension for the attribute. |
| ATTR_TYPE | smallint | NUMBER(38,0) | The type of attribute dimension. 0 = Text 1 = Numeric 2 = Boolean 3 = Date |
| PERSPECTIVE1 | int | NUMBER(38,0) | No ideaÉmay not even ben in use. Let me know if you have a clue. |
| PERSPECTIVE2 | int | NUMBER(38,0) | No ideaÉmay not even ben in use. Let me know if you have a clue. |
HSP_ATTRIBUTE_MEMBER:
| Field Name | SQL Server Type | Oracle Type | Description |
|---|---|---|---|
| ATTR_MEM_ID | int | NUMBER(38,0) | The OBJECT_ID of the attribute dimension member. |
| ATTR_ID | int | NUMBER(38,0) | The OBJECT_ID of the attribute dimension. |
| LEVEL0 | smallint | NUMBER(38,0) | This is supposed to tell us if the member is a leaf. It doesn't actually do that. |
| TEXT_VAL | varchar(32) | VARCHAR(32 CHAR) | In theory this holds a text value for the member. Instead it is always null. |
| NUMBER_VAL | decimal(18,0) | NUMBER(38,0) | In theory this holds a numeric value for the member. Again, always null. |
| BOOLEAN_VAL | smallint | NUMBER(38,0) | There's a pattern forming here...always null. |
| DATE_VAL | datetime | DATE | You 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:
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:
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.