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.