Onassis @ WordPress

Just another WordPress.com weblog

Moving to Blogger

I’ve decide to move to Blogger!
http://onassiscayetano.blogspot.com/

How to implement Oracle Hyperion System 9.3.1 Row Level Security

Overview

This howto is specific to Oracle Hyperion System 9.3.1, but I believe it may work on previous versions of Hyperion.  I’m also using Oracle 11g for the Hyperion repository in this howto.  The official documentation that describes the Hyperion Row Level Security comes with the product (if you chose to install it) and can be found in /Hyperion/9.3.1/BrioHelp/en/frameset.htm?launch.html.

This howto focuses on the steps that I personally found difficult and which aren’t fully described in the official documentation.  So I put more detail in those steps.  Please remember that you’ll need to alter the steps described here to fit your environment and configuration.

Step 1 – Setup a Data Access Service

Row Level Security uses a set of 3 tables, BRIOSECG, BRIOSECP, and BRIOSECCR.  Hyperion Workspace needs to know where these tables exists.  The data access service you setup in this step is for the database where these tables exists.

Run the Service Configurator by selecting Start > All Programs > Hyperion > Reporting and Analysis > Utilities and Administration > Service Configurator.

Once the Service Configuration window is up and running, from it’s menu select  Modules > Local Service Configurator.  (Note that my Hyperion installation is local, so I choose the Local Service Configurator).  You will be prompted to login.

 

Once the Local Service Configurator is up, double-click on the service DAS1_[hostname].

Once the DAS1_[hostname] properties window is up, click on the Data Source Tab.  You will see all the data sources already available to you.

If you already see the data source where your Row Level Security tables exists, then you’re all set.  Make a note of the Hostname/Provider of the data source where they exists.  This is information you will  need later.  If you don’t see the data source, then you’ll have to create one.  Click on the New… button to get the Add Data Source to DAS window.

Select the Connectivity Type and Database Type and fill in the name of the data source.  Note that for Oracle databases, this is the TNSNAME of the database.

Step 2 – Create the Row Level Security Tables

I found that by default Hyperion already creates these tables in it’s schema during installation.  For my particular local setup, the tables where created in a schema named “hyperion”.  If you don’t find these three tables BRIOSECG, BRIOSECP, and BRIOSECCR or if you choose to use some other schema for them, then you’ll have to create them.  There are two ways to do this:

  1. Hyperion provides you with an Interactive Report specifically for administering the Row Level Security which includes a section for creating the tables.  You can find this report in \Hyperion\BIPlus\docs\en\row_level_security.bqy.
  2. Run a SQL script manually.  You can find the script in \Hyperion\BIPlus\database\[databasetype]\CreateRLS.sql.

Personally, I found the “Row Level Security Administration Tool” report difficult to understand and follow, so I would choose to run the script manually.  But you’ll still have to use the report later in the setup.

I’ll assume you know how to run scripts against your database, so I am not providing the steps here.

Step 3 – Configure Row Level Security in Workspace

Login to Hyperion Workspace as an administrator and from the menu select Navigate > Administer > Row Level Security.  You’ll get the Manage Row-Level Security page.

Check Enable Row Level Security.   Select the Connectivity and Database Type.   Enter the Data Source Name which must match the data source name you created in Step 1 – Setup a Data Access Service.   Enter the User Name and Password to connect to the database.  Click on Save Properties.  You will be notified that the service needs to be restarted for the feature to be enabled.

Hyperion consists of so many dependent services.  If you know exactly the dependencies between services then you can be brave and restart them in the proper order.  I find it much easier to restart the entire machine, but then I was doing this only in a development environment, not production.

Step 4 – Define the Row Level Security Rules

You need to be very familiar with your datamodel to setup the rules.  The examples I have here is for a data warehouse using a star schema.  I have a fact table named AUDIT_FACT and a dimension table called OFFICE_DIM.  And they are joined via AUDIT_FACT.OFFICE_KEY = OFFICE_DIM.OFFICE_KEY.

Although you could define the rules directly in the 3 security tables, I recommend using the provided Interactive Report for administering those rules.  You can find the report in \Hyperion\BIPlus\docs\en\row_level_security.bqy.  Start up Hyperion Interactive Report Studio and open this report.

When you click on the Click Here to Proceed button you’ll get a Warning message “Could not find connection file: C:\nosuchoce.oce”.  Ignore this message and click on OK.  You will be prompted to select an existing Interactive Report OCE file.  Hopefully you’ve already have one created for your schema.  Select that OCE to continue and you’ll be prompted to enter the login information for the OCE.

You’ll get a couple more messages.  Read them and then click OK.  They’re mostly informational messages and they’ll popup every time you run the report.

When you finally get to the main page for the tool, enter the User ID and Password for the schema that contains those 3 security tables.  If you’re properly connected you should see the proper table specified with the label Restriction Table in Use in the orange box.

Click on the Change Multi-Group Conjunction button.  This will take you to the Change Multi-Group page that explains what it does.

Select the conjuction you wish and click on OK.  WARNING:  This page may not be functioning properly.  When I used it, it did not create the necessary record in the BRIOSECP table.  So I resorted to creating the record manually.  The table consists of simply one column.  Insert one record into the table and set the data to the string “AND” or “OR”.

Return to the main page and click on the Add New Security Restrictions button.  You’ll get the Add Row Level Security Rules page.

 

The sample rule I’ve defined restricts the user “johndoe” to only see audit records that belong to the “Main” office.  Specifically, the rule defines a constraint that applies to the dimension table that is joined to the fact table.  The contraint is OFFICE_DIM.OFFICE_NAME in (‘Main’).  And this constraint is applied only when AUDIT_FACT.OFFICE_KEY = OFFICE_DIM.OFFICE_KEY.

WARNING:  The security rules entries are case sensitive!  Although in this write-up I upper case all table and field names for clarity, in my screen capture I show the entries all in lower case.  Unfortunately the Hyperion documentation shows all entries in upper case.  So which is correct?  I found out which is correct for my environment by monitoring the SQL generated by Hyperion against my Oracle database.  They were all in lower case.  So all my rules are in lower case.  This was by far the most crucial piece of information that I wish Hyperion documented.  I spent hours trying to figure this out.

Once you have defined and saved your rule, go ahead and login using the test user (“johndoe” in this example) and try your Interactive Report.  You must refresh or re-process your Interactive Report for the rule to take affect with the current user.

Another important note to remember is that the Row Level Security feature only applies to Interactive Reports that connect to a relational database.  It does not apply to OLAP Queries, such as those against ESSBASE databases.  And it certainly does not apply to Web Analysis Reports.

Essentially this security feature does SQL injection.  It parses the SQL and checks against the rules you’ve setup.  If it finds a match it will append/inject the constraints that you’ve defined into the SQL before executing it against the database.

YouTube Revenue Sharing

I got this email from YouTube last week.  I was so surprised.  Who knew a couple of cooking/baking videos from my wife would get enough attention from YouTube.  I always imagined that the videos would have to be getting hundreds of thousands of views before you get offered to make money from YouTube.  Our videos barely hit 350 views.  But I’ll take it.  This is exciting, although I’m trying to keep my head straight.  I’ve already started thinking about doing a weekly cooking video, of course, if the wife agrees since she will be the one on it.  And I already bought a new HD camcorder to make it easier to shoot videos.  The ones I’ve been posting are taken just using an iPhone.

Yep, I’m already going overboard, buying a new camcorder. But hey, it’s fun.

YouTube 500 Internal Server Error

Just got this error from YouTube.  I hope their “highly trained monkeys” fix the problem soon.  I need to watch some ninja cats.

Technical Forums Ranking

Check out my ranking!

Is it sad to be proud of being ranked in a technical forum like Oracle’s Hyperion Developer Network? I’m ranked right at the middle. And those are not just for posting questions; they’re also for posting answers. So I guess that’s something to be proud of. Check out my ranking!

OK, 15 posts is not much, but hey it’s more than my own blog.

…. yeah, I know it’s pretty sad.

Trial Period

Symantec Norton Internet Security Trial Period

I have fully licensed version of Symantec’s Norton Internet Security.  So I guess full license = over 5 million days of trial?

Flock

When I tried Flock years ago, I didn’t really know what to make of it. I didn’t blogged and I wasn’t interested much in sharing my photos or videos on the web. But things for me are much different now, and the browser makes a lot of sense. It’s hooks into the popular photo, video, and blog sites make me want to actually update my sites, hence this post.

To renew or not renew, that is the .Mac question

So I’m trying to decide if I should update my .Mac account. I’m starting to realize now that I’m slowing moving all my stuff into web applications. Although the iLife apps are great, they limit me. I have to be on my machine to do anything. For example, I have a lot of my photos on Flickr. I can edit and manage those photos anywhere where there is internet access. I also just started a blog at WordPress. Again, I can edit and manage my blog anywhere since it’s a web app. With iWeb I have to be on my computer at home to do anything.

My .Mac expires at the end of the month. I wish Apple would also go the web app direction. But I guess Apple’s approach is to simply use the .Mac as a file/web server, not an application server. Although I’m sure they certainly can make amazing web applications that can rival Flickr or WordPress or many other web apps out there.

I have the same issue with the iCal too. Although I can publish my calendar online, it is read only. I can’t add events online. It would take just a little bit more coding for Apple to implement this, I’m sure. So it must be just not part of their long term strategy, which I guess makes sense. Web apps do not require a Mac to use, iLife does.

Plunging in to social networking

I just signed up for Facebook, Twitter, and LinkedIn.  I don’t even have time to check all my emails . . . what am I thinking?!