The Planning Repository: HSP_MEMBER (Part 2 – HSP_ALIAS)
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 18.104.22.168.500 and 22.214.171.124.
- 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).
Now that we have the annoying complex pieces of the HSP_MEMBER table figured out (bitmasks), let’s move on to the easier to obtain and far more gratifying information about our meta-data in Hyperion Planning. This is where we leave the HSP_MEMBER table and go looking for everything else. The focus of this post will be the HSP_ALIAS table.
So why aren’t aliases in the HSP_MEMBER table? The answer is simply that Planning allows for an undefined number of alias tables. So why add a large number of columns to the main table? Instead we have an HSP_ALIAS table. Before we get into that table, let’s first determine how many alias tables we are dealing with. For that, we just go to the HSP_ALIAS_TABLE table…oh wait. That doesn’t exist.
Instead, we have to query the HSP_OBJECT table to determine our alias tables. But at least if we have to use the HSP_OBJECT table, we should be able to filter that easily using something like OBJECT_TYPE, right? Of course not…if we do that, aliases have an OBJECT_TYPE of 1 which equates to a folder. So because there are a lot of folders in Planning, that’s not terribly helpful. But…at least there is a parent for us to query. So here goes:
SELECT o.OBJECT_NAME AS ALIAS_TABLE FROM HSP_OBJECT o INNER JOIN HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID WHERE op.OBJECT_NAME = 'Aliases'
Pretty simple…though it still seems like a round-about way to get our results:
So we can see that we have two alias tables. Now we should be ready to go take a look at the HSP_ALIAS table. Let’s start by looking at the structure:
|Field Name||SQL Server Type||Oracle Type||Description|
|MEMBER_ID||int||NUMBER(38,0)||The OBJECT_ID of the member.|
|ALIASTBL_ID||int||NUMBER(38,0)||The OBJECT_ID of the alias table.|
|ALIAS_ID||int||NUMBER(38,0)||The OBJECT_ID of the alias.|
So basically, there isn’t much going on here. Probably the most interesting thing about this table is that it just provides a reference back to the HSP_OBJECT table. So each alias also exists in the Planning repository as an object. So now let’s add alias information to our HSP_MEMBER query:
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 ,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 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 WHERE od.OBJECT_NAME = 'Product'
I know what you’re thinking. I cheated. And its true, I used a sub-query join for each of the aliases that I knew existed. Not very dynamic, but it gets the job done. So let’s look at the results:
That’s it for aliases. If you plan to use this type of query for other downstream targets, like say an Essbase cube, you may want to consider putting in some code to compare the two aliases. Essbase will not allow non-unique aliases even for the same member. Next up in our series will cover UDA’s. Stay tuned…