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:

SQL
14 lines
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

And here we see the results:

HSP_MEMBER_TO_ATTRIBUTE_1

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:

SQL
40 lines
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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:

HSP_MEMBER_TO_ATTRIBUTE_2

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.

Leave a Reply


This site uses Akismet to reduce spam. Learn how your comment data is processed.