My bittersweet affair with OWB continued. The venue changed, but the quest remained the same.

To conquer OWB -- whatever it took.

At least this time it wasn't so bad -- technically, Sembawang is still SG -- as I had come prepared. Prepared for the worst, that is. Anyway, having consigned myself some long time ago to a firefighter role, I accepted the challenge. Fully aware that once you rolled in, there was no rolling out.

In the end the migration was -- finally -- successful. Never mind that it started so late and took so long that the security guard had left for the night and I couldn't get my identity card back until the next day. Never mind that I cheated OWB in the process. Hey, if you can't beat them, buy them some char kway teow or something.

Next came the Hyperion Performance Suite -- the so-called business intelligence platform. One thing became clear immediately: you do need intelligence -- lots of it -- to make this thing work. Work the way we want it to, I mean.

The highlight of the week? Gotta be that row-level security thingy. At such times I'm always reminded how so a Micro$oft-slave I am. *OMG, Chris Jericho looks handsome with his new hair* Despite everything, I still find their documentation the best. Hyperion? Not even close. At least Oracle now has step-by-step guides.

Although, speaking of Oracle, I had such a hard time troubleshooting that thing they call, incidentally, the Intelligent Agent. It had worked fine until I tried to be smart and set up the Management Server. Yes, again. This supposedly intelligent agent refused to start. And my listener.log kept informing me of errors.

Well, I'm not gonna document all the details -- enough Oracle stuff to last a few weeks. Suffice to say that I removed all the listeners, uninstalled the agent, and then re-set up them over. It worked. So did my Oracle Workflow 2.6.2. And life was good again.

Until that row-level security stuff came along. The document and sample included helped somewhat, but, as expected, not much. After two days of trials and errors -- split by my Sembarang experience -- I came up with these steps to enable row-level security in Hyperion -- most of them are already in that hpsu_admin.pdf, mind you, it's just that they're so scattered about. Y'all are most welcome to CMIIW.

1. Prepare a database where Hyperion is to store the row-level security info. The document recommends creating a separate database (schema, in case of Oracle) for security reason. At this point database security wasn't a priority for me, so I used the same schema as the Hyperion repository.

2. Create the necessary row-level security tables using CreateRLS.sql found in directory disk1/data/<database> on installation CD #1. Two tables -- BRIOSECG and BRIOSECR -- will be created.

3. Create a new data source in the DAS service using the Service Configurator tool. Give a name to be used in the next step. Restart the DAS service.

4. Login to the web-based server as an administrator and enable the row-level security feature using the Administration - System module on the Hyperion server. As the data source, give the name you have specified in step 3 and provide the necessary login info.

5. Find the row_level_security.bqy file in the documentation CD, in the en/Intelligence Clients directory. You might wanna copy this file somewhere on your local disk, you know, just in case. One can never be too careful these days. Open the file in the Hyperion Intelligence Designer.

6. Create the necessary .oce file to connect to the database set up in step 1. Note: it will say OCE not found the first time, don't worry about it. Configure the security tables' owner. My experience at this step wasn't too smooth. It kept giving me error. Then suddenly it worked. I swore I didn't do anything special, except being frustrated and going down to Coffee Bean to get some Sunrise *shrugs*.

7. Now, here's the key. It'd be too wordy to describe everything here, I'll just tell you what I did for my situation. Scenario: I have three fact tables (FACT_CASH_GL, FACT_ACCRUAL_GL, and FACT_BUDGET) and many dimensions. Yes, we're talking a data warehouse here. Three groups of users are to be set up, each can access facts from certain ministries only. Group A can access data from ministries M1, M2, and M3 only. Group B M1, M2, and M4 only. Group C can access M5 only. In addition, Group A can access FACT_CASH_GL and FACT_BUDGET only, Group B FACT_ACCRUAL_GL only, and Group C all three fact tables. Other users/groups do not have any access to the facts. Note: ministries are stored in MINISTRY_CODE column in DIM_COST_CENTER dimension. The dimension's primary-key column is called COST_CENTER_ID and so are the fact tables' foreign-key columns.

7a. Define the three groups using row_level_security.bqy and add the users accordingly. From what I gathered, the group names do not have anything to do with the actual group names defined in the Hyperion server. You can give any names you want. But the usernames must be exactly -- they're case sensitive, BTW -- those in the server. In other words, whenever I create a new user in the server, I must then add the same user into the row-level security groups.

7b. Deny all users access to the three fact tables. There's this special PUBLIC group that automatically consists of all users. Add a new security restriction. Put PUBLIC as the group and one of the fact tables as the source table, e.g. FACT_CASH_GL. Put asterisk (*) as the source column name. Note: it's best to leave the database name and owner blank. Set constraint to N/A -- No Limit Constraint and constraint value to NULL. Repeat for the other two fact tables.

7c. Override the above restriction for the three groups. This is done by creating six new restrictions.
Group A; source table: FACT_CASH_GL; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC
Group A; source table: FACT_BUDGET; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC
Group B; source table: FACT_ACCRUAL_GL; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC
Group C; source table: FACT_CASH_GL; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC
Group C; source table: FACT_ACCRUAL_GL; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC
Group C; source table: FACT_BUDGET; source column: *; constraint: O -- Override Existing Constraint; to override: PUBLIC

7d. Finally, create the restrictions that, IMO, matter most.
Group A; source table: FACT_CASH_GL; source column: * -- it has to be an asterisk, otherwise the restriction won't apply if the queries' limits do not include the columns specified here; join table: DIM_COST_CENTRE; join column from source: COST_CENTER_ID; join column from join table: COST_CENTER_ID -- don't confuse the two columns, they just happen to have the same names; constraint: J -- Limit on Join Table; constraint column: MINISTRY_CODE; operator: IN; values: ('M1', 'M2', 'M3')
Group A; source table: FACT_BUDGET; source column: *; join table: DIM_COST_CENTRE; join column from source: COST_CENTER_ID; join column from join table: COST_CENTER_ID; constraint: J -- Limit on Join Table; constraint column: MINISTRY_CODE; operator: IN; values: ('M1', 'M2', 'M3')
Group B; source table: FACT_ACCRUAL_GL; source column: *; join table: DIM_COST_CENTRE; join column from source: COST_CENTER_ID; join column from join table: COST_CENTER_ID; constraint: J -- Limit on Join Table; constraint column: MINISTRY_CODE; operator: IN; values: ('M1', 'M2', 'M4')
And so on with the three other restrictions -- I'm sure you get the idea.

8. Make sure the row-level security setup works. Login to the Hyperion Performance Suite server as any of the users belonging to any of the three groups and try processing the published queries. A note of caution here: do not get easily satisfied, trust me. Play around with different queries, change the tables and requests used. Modify the row-level security restrictions. After every change, exit from the Suite and re-login for the new changes to take effect.

And that's a wrap on Hyperion row-level security. Damn, that was long.

Current music: Spin Doctors - Two Princes
Current mood: happy