The Planning Repository: HSP_MEMBER (Part 6 – Putting It All Together)

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.

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

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:

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.

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:

HSP_ATTRIBUTE_MEMBER:

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.

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:

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.