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).
We’ve finally made it to the last part in our series on the HSP_MEMBER table (and all of the many associated tables)! In this post, we’ll take everything that we’ve learned and combine it to get a full extract of a single custom dimension for a single plan type. In our SQL Server query, this is a pretty straight forward operation. We simply combine the queries from the prior five parts of this series.
SQL Server:
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
,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)
, '"/><row UDA="', ', '), '<row UDA="', ''), '"/>', '' )
AS UDA
,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
,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 PLAN1_CONSOL_OP
,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
,at.ATTRIBUTE_NAME AS ATTRIBUTE_TEXT
,ab.ATTRIBUTE_NAME AS ATTRIBUTE_BOOLEAN
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
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrText') at ON m.MEMBER_ID = at.MEMBER_ID
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrBoolean') ab ON m.MEMBER_ID = ab.MEMBER_ID
WHERE
od.OBJECT_NAME = 'Product'
It makes for a long query…but effective. This query will give us all of our properties for our first plan type. And here are the results:
So SQL Server wasn’t too bad. How about Oracle? Well…that’s a different story. There are two ways to approach this query on the Oracle side. Option 1: combine all of the queries like we did in SQL Server. Sounds easy enough until you remember back to Part 3 of our series where we first used the LISTAGG function. That function makes this process a pain. Instead of just combining the queries together, we have to add everything to our group by as well. Here it is for fun:
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
,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST"
,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
,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 PLAN1_CONSOL_OP
,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1) AS PLAN1_FORMULA
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
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
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
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
WHERE
od.OBJECT_NAME = 'Product'
GROUP BY
o.OBJECT_NAME
,op.OBJECT_NAME
,od.OBJECT_NAME
,a1.ALIAS_NAME
,a2.ALIAS_NAME
,m.DATA_STORAGE
,m.DATA_TYPE
,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
,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1)
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END
It’s ugly, but it works. There was a problem with just adding everything to the GROUP BY. Member formulas are of type CLOB which of course can not be used in a GROUP BY. So what do we do? We instead use a substring function to convert the CLOB to a 4,000 character VARCHAR. This works great…assuming you don’t have any large formulas. So this isn’t a great solution.
So what do we do? Option B. We take a step back and place our LISTAGG function into its own sub-query. This let’s us move the group by to a very small place and still reference our member ID. Here it is:
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
,( SELECT LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA_LIST" FROM (SELECT
UDA_VALUE
,MEMBER_ID
FROM
HSP_MEMBER_TO_UDA mu
INNER JOIN
HSP_UDA u ON mu.UDA_ID = u.UDA_ID) muda WHERE muda.MEMBER_ID = m.MEMBER_ID) AS UDA
,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
,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 PLAN1_CONSOL_OP
,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
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
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
WHERE
od.OBJECT_NAME = 'Product'
This makes for a much shorter and less complex query. Well, mildly less complex as this requires not just a sub-query, but a nested sub-query. With either query, whether you choose the complex query with the big group by or the far simpler query with the nested sub-query, the results are the same:
And so the series on the HSP_MEMBER table (and all the other tables related) ends. But wait, there are more properties missing? The account dimension has more? The entity dimension has more? Tune in for yet another series, this time on the built-in 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).
We are finally to the last piece before we put it all together: attribute associations. As with UDA’s and formulas, we have another table to take a look at. Members are associated with attribute members using the HSP_MEMBER_TO_ATTRIBUTE table. This is a pretty simple table, but let’s still take a quick look at the structure:
Field Name | SQL Server Type | Oracle Type | Description |
MEMBER_ID | int | NUMBER(38,0) | The OBJECT_ID of the member associated with the attribute member. |
ATTR_ID | int | NUMBER(38,0) | The OBJECT_ID of the attribute dimension. |
ATTR_MEM_ID | int | NUMBER(38,0) | The OBJECT_ID of the attribute dimension member associated with the member. |
PERSPECTIVE1 | int | NUMBER(38,0) | I have not been able to figure out what this is used for. It seems to always be 0 in my testing. |
PERSPECTIVE2 | int | NUMBER(38,0) | I have not been able to figure out what this is used for. It seems to always be 0 in my testing. |
Pretty simple. We have the member and the attribute member and also the attribute itself. This means we will have to filter this table to get to specific attribute associations by attribute. We also have two PERSPECTIVE fields that no longer appear to be in use. So let’s do a simple query to take a look at associations for a specific attribute dimension:
SELECT
o.OBJECT_NAME AS MEMBER_NAME
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE ado.OBJECT_NAME = 'AttrText'
And here we see the results:
So this is great to see all of the members that have a specific attribute associated, but how do we see all of the associations for all of our attribute dimensions? We join to the above query for each of our attributes:
SELECT
o.OBJECT_NAME AS MEMBER_NAME
,at.ATTRIBUTE_NAME AS ATTRIBUTE_TEXT
,ab.ATTRIBUTE_NAME AS ATTRIBUTE_BOOLEAN
FROM
HSP_MEMBER m
INNER JOIN
HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrText') at ON m.MEMBER_ID = at.MEMBER_ID
LEFT JOIN
(SELECT
m.MEMBER_ID
,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
HSP_MEMBER m
INNER JOIN
HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE
ado.OBJECT_NAME = 'AttrBoolean') ab ON m.MEMBER_ID = ab.MEMBER_ID
WHERE
od.OBJECT_NAME = 'Product'
This is just an example. If I were to do this in a production environment, each of the left joins would be to views. We would have a view for each of our attribute dimensions. But for an example that you can easily execute, it works just fine. Let’s look at the results:
This gives us a list of all of our product dimension members along with the attribute member association. And with that, we’re ready to put it all together and get a full extract of a custom dimension…in our next post.
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.
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).
As we near the end of the HSP_MEMBER series, we’ll take a look at a relatively low-complexity piece next: member formulas. As we mentioned before, formulas are stored in a separate table in Planning. This is done because we can have a different formula for each of our plan types. Our member formulas are stored in the HSP_MEMBER_FORMULA table. Let’s take a look at the structure:
Field Name | SQL Server Type | Oracle Type | Description |
MEMBER_ID | int | NUMBER(38,0) | The OBJECT_ID of the member. |
PLAN_TYPE | int | NUMBER(38,0) | The plan type. |
DATA_STORAGE | int | NUMBER(38,0) | The data storage type to be used in Essbase for the specific plan type. |
SOLVE_ORDER | int | NUMBER(38,0) | The plan type specific data storage for the member. |
FORMULA | ntext | CLOB | The member formula. |
There’s not a lot of new information here. The DATA_STORAGE field decodes just like it did in the HSP_MEMBER table and we just need to join to the HSP_PLAN_TYPE table to get our plan type names and to the HSP_OBJECT table to get our member names. Let’s give it a go:
SQL Server:
SELECT
MEMBER_ID
,o.OBJECT_NAME AS MEMBER_NAME
,mf.PLAN_TYPE
,DATA_STORAGE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
INNER JOIN
HSP_OBJECT o ON mf.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
Oracle:
SELECT
MEMBER_ID
,o.OBJECT_NAME AS MEMBER_NAME
,mf.PLAN_TYPE
,DATA_STORAGE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
INNER JOIN
HSP_OBJECT o ON mf.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
Our goal here is to just read out all of the formulas for each plan type. The most interesting thing here is that everything is stored by plan type. Basically, this table tells us which formula to use for which cube inside of the Essbase outline. The slight issue with this is that they also have the default calculation that exists in all used plan types unless another formula is specified for a plan type. So we have a sub-query in our join that gives us the 0 plan type (default) that we then union to our standard plan type query. Here are the results:
SQL Server (Management Studio):
Oracle (SQL Developer):
That gets us a list of all of the formulas for all of our members for all of our plan types and defaults. What if I just want to figure out the formula for a specific plan type. Because many developers just use the default formula, we have a little more logic that goes into it. So we turn to a pair of sub-queries along with a few case statements:
SQL Server:
SELECT
o.OBJECT_NAME AS MEMBER_NAME
,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
HSP_MEMBER m
INNER JOIN
HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
Oracle:
SELECT
o.OBJECT_NAME AS MEMBER_NAME
,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
HSP_MEMBER m
INNER JOIN
HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
(SELECT
MEMBER_ID
,mf.PLAN_TYPE
,CASE 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
,SOLVE_ORDER
,FORMULA
FROM
HSP_MEMBER_FORMULA mf
LEFT JOIN
(SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL
SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
WHERE
p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
That’s a long query just to get member formulas! On the bright side, it is far more usable than what the Outline Load Utility will produce. And I’ve said this before when I’ve used sub-queries…I wouldn’t typically do this as a sub-query. I would instead create views for these queries so that we can do regular joins and have a lot less maintenance should we need these formulas in other queries. With that, let’s look at the results:
SQL Server (Management Studio):
Oracle (SQL Developer):
That’s where we’ll stop for now. We have one last post to go (which will actually be two posts…) before we can put it all together and get one big query for each of our plan types with all of our properties. Stay tuned for attribute associations.
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:
Field Name | SQL Server Type | Oracle Type | Description |
UDA_ID | int | NUMBER(38,0) | The unique ID of the UDA. |
DIM_ID | int | NUMBER(38,0) | The OBJECT_ID of the dimension that the UDA belongs to. |
UDA_VALUE | varchar(80) | VARCHAR2(80) | The actual UDA itself. |
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:
Field Name | SQL Server Type | Oracle Type | Description |
MEMBER_ID | int | NUMBER(38,0) | The OBJECT_ID of the member associated with the UDA. |
UDA_ID | int | NUMBER(38,0) | The unique ID of the UDA. |
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.
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:
Field Name | SQL Server Type | Oracle Type | Description |
MEMBER_ID | int | NUMBER(38,0) | The OBJECT_ID of the member. |
ALIASTBL_ID | int | NUMBER(38,0) | The OBJECT_ID of the alias table. |
ALIAS_ID | int | NUMBER(38,0) | The OBJECT_ID of the alias. |
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…
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:
Field Name | SQL Server Type | Oracle Type | Description |
MEMBER_ID | int | NUMBER(38,0) | The OBJECT_ID of the member. |
DIM_ID | int | NUMBER(38,0) | The OBJECT_ID of the dimension to which the member belongs. |
DATA_STORAGE | smallint | NUMBER(38,0) | The data storage type to be used in Essbase for members without formulas (members with formulas have another table). See the reference table below for details. |
TWOPASS_CALC | smallint | NUMBER(38,0) | The Essbase two-pass calculation property.
0 = not two-pass
1 = two-pass |
CONSOL_OP | bigint | NUMBER | The consolidation operator for the member. This is actually a bitmask that allows for the storage of multiple operators across plan types in a single integer.
0 = Add
1 = Subtract
2 = Multiply
3 = Divide
4 = Percentage
5 = Ignore
6 = Never |
USED_FOR_CONSOL | smallint | NUMBER(38,0) | No longer used. |
HAS_MBR_FX | smallint | NUMBER(38,0) | Tells us if the member has a formula. The formula itself does not exist in this table as members can have more than one formula.
0 = no formula
1 = formula |
BASE_MBRID | int | NUMBER(38,0) | If the member is shared, then this contains the prototype member ID from the HSP_OBJECT table. |
ENABLED_FOR_PM | smallint | NUMBER(38,0) | Tells us if the member has been enabled for process management (workflow).
0 = not process management enabled
1 = process management enabled (table default) |
PS_MEMBER_ID | int | NUMBER(38,0) | Planning specific member ID that identifies predefined members used for custom components such as workforce (direct from OracleÉstill not exactly sure what this is for). |
DATA_TYPE | int | NUMBER(38,0) | The data type of the members.
0 = unspecified
1 = currency
2 = non-currency
3 = percentage
4 = smart list
5 = date
6 = text |
ENUMERATION_ID | int | NUMBER(38,0) | The ID of the smart list for the members. This links back to HSP_OBJECT and HSP_ENUMERATION. |
USED_IN | int | NUMBER(38,0) | The plan type usage for the member. This is also a bitmask that tells us which plan types are used in a single integer. |
HIERARCHY_TYPE | int | NUMBER(38,0) | This is a new column that is supposed to pertain to ASO plan types. I'm working on understanding what it means. |
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.
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_OBJECT table. Now that we’ve covered the basics, let’s dig a little deeper and start to look at how Planning manages meta-data inside of our applications. The starting point for this is the HSP_DIMENSION table. This table will have a pretty light set of data (just a few more rows than the number of dimensions in your app) but has quite a few properties. Each of the properties for the dimensions that you set in the GUI will be found here. Let’s take a look at the table structure:
Field Name | SQL Server Type | Oracle Type | Description |
DIM_ID | int | NUMBER(38,0) | The OBJECT_ID of the dimension. |
ENFORCE_SECURITY | int | NUMBER(38,0) | Determines if security is being enforced on this dimension.
0 = Don't Enforce Security
1 = Enforce Security |
DIM_TYPE | smallint | NUMBER(38,0) | The type of dimension.
0 = None
1 = Account
2 = Time
3 = Entity
7 = Entity Hierarchy |
DENSITY | int | NUMBER(38,0) | No longer used. |
USED_IN | int | NUMBER(38,0) | The plan type usage for the dimension This is a bitmask that tells us which plan types are used in a single integer. |
DIM_EDITOR | smallint | NUMBER(38,0) | Determines if the standard dimension editor can be used.
0 = Non-standard dimension, like period, scenario, etc.
1 = Standard dimension that can be modified with the standard editor , like account, entity, and custom dimensions |
DENSITY1 | smallint | NUMBER(38,0) | The Essbase setting for density of the dimension.
0 = Dense
1 = Sparse |
DENSITY2 | smallint | NUMBER(38,0) | See above |
DENSITY3 | smallint | NUMBER(38,0) | See above |
DENSITY4 | smallint | NUMBER(38,0) | See above |
DENSITY5 | smallint | NUMBER(38,0) | See above |
DENSITY6 | smallint | NUMBER(38,0) | See above |
DENSITY7 | smallint | NUMBER(38,0) | See above |
POSITION1 | smallint | NUMBER(38,0) | The Essbase setting for the position of the dimension. The position hear should line up with the number in the performance section of the GUI. |
POSITION2 | smallint | NUMBER(38,0) | See above |
POSITION3 | smallint | NUMBER(38,0) | See above |
POSITION4 | smallint | NUMBER(38,0) | See above |
POSITION5 | smallint | NUMBER(38,0) | See above |
POSITION6 | smallint | NUMBER(38,0) | See above |
POSITION7 | smallint | NUMBER(38,0) | See above |
POSITION8 | smallint | NUMBER(38,0) | See above |
POSITION9 | smallint | NUMBER(38,0) | See above |
POSITION10 | smallint | NUMBER(38,0) | See above |
POSITION11 | smallint | NUMBER(38,0) | See above |
POSITION12 | smallint | NUMBER(38,0) | See above |
POSITION13 | smallint | NUMBER(38,0) | See above |
POSITION14 | smallint | NUMBER(38,0) | See above |
POSITION15 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER1 | smallint | NUMBER(38,0) | The evaluation order of the dimension. The enum name comes Smart Lists which are stored in the HSP_ENUMERATION and HSP_ENUMERATION_ENTRY tables. |
ENUM_ORDER2 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER3 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER4 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER5 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER6 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER7 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER8 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER9 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER10 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER11 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER12 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER13 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER14 | smallint | NUMBER(38,0) | See above |
ENUM_ORDER15 | smallint | NUMBER(38,0) | See above |
DRIVER_DIM_ID | int | NUMBER(38,0) | The OBJECT_ID of the dimension configured for data loads in Planning. |
DISPLAY_OPTION | int | NUMBER(38,0) | The display option selected for the dimension.
0 or null = Member name
1 = Alias
2 = Member name and Alias
3 = Alias and Member name
4 = Member count (I think this legacy)
5 = Member name and Description (I think this is legacy) |
If you’ve looked at this table in the past, not too much has changed. There are a few interesting things to mention. First let’s look at the USED_IN field. This field is a bitmask. So what does that mean? It means that Oracle is the devil. That’s what it means. Bitmasks are among the most annoying things you can run across when decoding someone else’s database. But here we are, so let’s take a look. The developers of Planning have chosen to use a single integer field to tell us all of the plan types that a specific dimension can be part of. This means that we have up to 13 different pieces of information that are stored in a single integer. So how do they do this? The easiest way to illustrate this is with a table. Here we see how we would represent all 13 plan types numerically:
Plan Type | Integer | Example |
Plan Type 1 | 1 | Plan1 |
Plan Type 2 | 2 | Plan2 |
Plan Type 3 | 4 | Plan3 |
Plan Type 4 | 8 | Wrkforce |
Plan Type 5 | 16 | Capex |
Plan Type 6 | 32 | Project |
Plan Type 7 | 64 | PlanASO1 |
Plan Type 8 | 128 | PlanASO2 |
Plan Type 9 | 256 | PlanASO3 |
Plan Type 10 | 512 | PlanASO4 |
Plan Type 11 | 1024 | PlanASO5 |
Plan Type 12 | 2048 | PlanASO6 |
Plan Type 13 | 4096 | PlanASO7 |
So how do all of these become a single integer? Simple math. Add up the ones that the dimension should be used in. So let’s run through a few examples:
Plan Types 1 and 3: 1 + 4 = 5
Plan Types 1, 2, and 3: 1 + 2 + 4 = 7
Plan Types 1 – 13: 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256 + 1048 + 2048 + 4096 = 8191
So that’s all great, but how do I determine if the plan type is used without having to build a giant set of logic? The bitwise operator! Basically we ask if the number is included in our sum. For instance, is if my sum is 5, is Plan Type 2 included:
SELECT 5 & 2
This will return a 0 because 5 is a combination of 1 and 4 and does not include 2.
Let’s try a different example that returns a different an affirmative response. If my sum is 7, is Plan Type 2 included:
SELECT 7 & 2
This will return 2. It returns 2 because 7 does include 2. So we can basically assume if I use the bitwise & operator and the result is not 0, then its included.
Wow…this post is getting long. But hey, let’s keep going anyway, if you are still awake now, you must really want or need to know about how this bitwise thing works.
So how do we use this in a practical way? Let’s take a look at the HSP_PLAN_TYPE table. I won’t waste any space showing the structure of this table because it’s basically the same as my sample above. The plan types by name and their numerical equivalent. So here’s a practical example of how we can determine all of the plan types that a dimension belongs to.
SQL Server:
SELECT
TYPE_NAME
,PLAN_TYPE
,CASE WHEN (SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 31) & PLAN_TYPE = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
HSP_PLAN_TYPE
Oracle:
SELECT
TYPE_NAME
,PLAN_TYPE
,CASE WHEN BITAND((SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 53702),PLAN_TYPE) = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
HSP_PLAN_TYPE
Both queries should return a list of all of our Plan Types and whether or not the dimension in our where clause is used:
SQL Server (Management Studio):
Oracle (SQL Developer):
So now that we have made it past that diversion, let’s keep looking at our table. The rest of the dimension table is pretty well described by the table at the very beginning of this post (does anyone remember that far back?). But I do want to note a few interesting things. First, you will notice that we have only 7 density fields but for position and enumeration we have 15. Why? I scratched my head for a minute on this one, but then it hit me…ASO doesn’t care. So basically the repository support 7 BSO plan types and and additional 8 ASO plan types. Now if we look at the actual limitations in Planning, we can max out Planning with 13 plan types (which you can see in our sample table above). So we basically have an extra BSO plan type and an extra ASO plan type. It’s also possible that they use these extra fields for some other system purpose that I’m not aware of. If you know, drop me a comment!
So there you have it…an unexpected dive into some pretty technical workings of SQL code just to look at our dimensions. Now that we’ve had a look at the dimensions themselves, we can start looking at the members…in our next marathon post!
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:
Field Name | SQL Server Type | Oracle Type | Description |
OBJECT_ID | int | NUMBER(38,0) | The unique ID of the object. ÊThis is not an identity or auto-number field, rather it is actually generated internally by Hyperion Planning. ÊThe ID will be referenced throughout the repository in virtually every other table. |
OBJECT_NAME | varchar(80) | VARCHAR2(80 CHAR) | The name of the object in Planning. |
OBJECT_DESCRIPTION | int | NUMBER(38,0) | The key value from the HSP_STRINGS table that references the description of the object. |
OBJECT_TYPE | smallint | NUMBER(38,0) | We'll cover this a little later when we get to the HSP_OBJECT_TYPE table. But in short, this is an integer value representing the type of object for the record (form, member, etc.). Sometimes this integer can be found in the HSP_OBJECT_TYPE table, sometimes it can't. More on this later. |
PARENT_ID | int | NUMBER(38,0) | The OBJECT_ID of the parent for object. |
OWNER_ID | int | NUMBER(38,0) | The OBJECT_ID of the owner of the object. |
GENERATION | int | NUMBER(38,0) | The generation of the object if applicable and 0 if not. Objects like members and Planning Units have generations while things like forms do not. |
POSITION | float | FLOAT | The sort-order for the object in relation to other objects in the hierarchy. |
HAS_CHILDREN | smallint | NUMBER(38,0) | The flag that determines if a member is a leaf or not. If the member has children and is therefore not a leaf, the value is 1, otherwise it is 0. So if you are looking for leaves, look for 0. |
MARKED_FOR_DELETE | smallint | NUMBER(38,0) | Members that have been deleted from Planning but not refreshed to Essbase will be marked for deletion with a value of 1. Otherwise this value should be 0. |
CREATED | datetime | DATE | The date and time that the object was created. |
MODIFIED | datetime | DATE | The date and time that the object was last modified. |
MOVED | datetime | DATE | The date and time that the object was last moved. |
OLD_NAME | varchar(80) | VARCHAR2(80 CHAR) | This field is used to maintain data in Essbase when a member is renamed. Prior to refreshing Essbase, the old name of the member will be here. Once the refresh is complete, the OBJECT_NAME and OLD_NAME will be the same. |
SECCLASS_ID | int | NUMBER(38,0) | No longer used. |
REMOVABLE | smallint | NUMBER(38,0) | The flag that determins if a member can be deleted. If an object can be deleted, the value is 1, otherwise it is 0. |
MODIFIED_BY | varchar(80) | VARCHAR2(80 CHAR) | The user ID of the last user to modify the member. If you want any additional auditing of modifications to members, you have to hit the audit tables assuming you have them turned on. |
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:
OBJECT_TYPE | TYPE_NAME from HSP_OBJECT_TYPE | TYPE_NAME Made Up By Brian |
1 | Folder | |
2 | Dimension | |
4 | Calendar | |
5 | User | |
6 | Group | |
7 | Form | |
9 | Currency | |
10 | Alias | |
11 | Cube | |
12 | Planning Unit | |
23 | NULL | Type for the parent of all task lists |
24 | NULL | Task List |
25 | NULL | Task List Item |
31 | Scenario | |
32 | Account | |
33 | Entity | |
34 | Time Period | |
35 | Version | |
38 | Year | |
39 | NULL | Planning Unit Hierarchy |
45 | Shared Member | |
50 | User Defined Dimension Member | |
51 | NULL | Planning Unit Selection |
103 | NULL | Menu |
107 | NULL | Composite Form |
115 | NULL | Calculation Manager Rule |
116 | NULL | Calculation Manager Rule Set |
117 | NULL | Calculation Manager Variable |
118 | NULL | Type for the parent of Calculation Manager Rules |
119 | NULL | Type for the parent of Calculation Manager Rule Sets |
120 | NULL | Type for the parent of Calculation Manager Variables |
143 | NULL | Good questionÉsomething related to Business Metrics |
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).
In my last entry I demonstrated the use of dynamic members in Custom Plan Types. In today’s installment we’ll actually put dynamic members to a more practical use. The main benefit of dynamic members is to give the end-user the ability to add (or remove) their own members. But, if they have to go to the Business Rules section of Planning every time to do so, the process will get old in a hurry. Additionally, if you’ve never used menu’s in Planning, we’ll make excellent use of them today.
The first step in this process is to create out custom menus. Follow these steps to create the necessary menus:
- Click Administration, then Manage, then Menus.
- Click the Add Menu button.
- Enter Manage Entities for the name and click OK.
- Click on the newly created Manage Entities and click the Edit Menu button.
- Click on the Add Child button.
- Enter the following and click Save:
- Click on the newly added Managed Entities parent menu item and click the Add Child button.
- Enter the following and click Save (remember we created our business rule in Part 1):
- Click on the newly added Add Entity child and click the Add Sibling button.
- Enter the following and click Save:
Once we have our menu ready, we can create our form and add the newly created menus. Follow these steps to create the new form:
- Create a new form.
- Enter the following and click Next.
- Modify your dimension to match the following and click Next.
- Add Manage Entities to the Selected Menus list and click Finish.
- Open the form and test out your new right-click menu.
Now you have a form that can be used to allow users to input their own members in a custom plan type!