The Planning Repository: HSP_MEMBER (Part 3 – UDA’s)
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 220.127.116.11.500 and 18.104.22.168.
- 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).
Eventually we will find all of tables required to get all of the important information about members out of the repository. Our next step is to go find where UDA’s are hiding. Much like aliases, Planning allows for an undefined number of UDA’s. So, of course we need another table…or two. The good news is that we actually have tables outside of the HSP_OBJECT table.
There are two tables we will be using to identify the UDA’s associated with members. First, let’s take a look at the HSP_UDA table:
As far as Planning tables go, few are more straight forward than this one. Contrary to aliases, UDA’s do not even merit a row in the HSP_OBJECT table. HSP_UDA is it. So how do members get associated with these UDA’s? Another table of course! Let’s take a look at the HSP_MEMBER_TO_UDA table:
And I just got done saying that few are more straight forward than HSP_UDA…well, this one is even more straight forward. Given that this one is entirely self-explanatory, let’s go straight to combining this with the the HSP_MEMBER table. This will require a little bit more complexity. The methods are entirely different between Microsoft and Oracle.
SELECT o.OBJECT_NAME AS MEMBER_NAME ,op.OBJECT_NAME AS PARENT_NAME ,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) , '"/>
', '' ) AS MemberUDA ,od.OBJECT_NAME AS DIMENSION_NAME 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 WHERE od.OBJECT_NAME = 'Account'
Instead of a table join, we have to do a sub-query. Basically, we want to get a list of all of our UDA’s associated with each member without each member having multiple rows. So our sub-query gets the list of members first and then we use the For XML Raw command to put the results of the sub-query into XML format. From there, we replace the XML parts with commas to give us a nice clean result:
SELECT o.OBJECT_NAME AS MEMBER_NAME ,op.OBJECT_NAME AS PARENT_NAME ,od.OBJECT_NAME AS DIMENSION_NAME ,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST" 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 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 WHERE od.OBJECT_NAME = 'Account' GROUP BY o.OBJECT_NAME ,op.OBJECT_NAME ,od.OBJECT_NAME
In Oracle, we do a little of both. First we do a LEFT JOIN to a sub-query that returns all of our associated UDA’s for each member. If we left it at that, when a member has three UDA’s associated, it would have three rows in the result set. But, we made use of an excellent function to aggregate our list and separate it with commas. The LISTAGG function let’s you choose a value and a separator and then how you want to order the list. The downside to this function is that we have to GROUP BY all of our other columns. So be sure to include anything you add to the query to the GROUP BY as well. Here’s the result:
This is our first major difference in the queries that we execute against the repository. As luck would have it, we can get the same result regardless of our back end technology, we just need the right functions. That’s it for UDA’s! In our next look at the Planning Repository, we’ll dive into member formulas.