The Planning Repository: HSP_MEMBER (Part 4 – HSP_MEMBER_FORMULA)

Brian Marshall

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 NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member.
PLAN_TYPEintNUMBER(38,0)The plan type.
DATA_STORAGEintNUMBER(38,0)The data storage type to be used in Essbase for the specific plan type.
SOLVE_ORDERintNUMBER(38,0)The plan type specific data storage for the member.
FORMULAntextCLOBThe 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):

HSP_MEMBER_FORMULA_M

Oracle (SQL Developer):

HSP_MEMBER_FORMULA_O

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

HSP_MEMBER_FORMULA_2M

Oracle (SQL Developer):

HSP_MEMBER_FORMULA_2O

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.

Author:

Leave a Reply

Name*
Email*
Url
Your message*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>