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.