The Planning Repository: HSP_MEMBER (Part 4 – HSP_MEMBER_FORMULA)
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.