I consider Single Sign-On setup through Active Directory(AD) a key point in every system’s life. Up to this point — it’s just a PoC, a prototype or a department (10-20 users) level application.

AD integration step is usually prosponed, because it’s documented somewhere and there are much more important things to do, like showing that this system is of any value at all. But adding an AD connection mean that system is rooted (even if lightly) in company’s IT ecosystem.

There’s always a dilemma on groups and rights storage:

1. You can use AD groups for BI level user rights. AD admins are usually against this, it means more work for them.
2. Just use AD as a user catalog and add all groups and rights information in BI system. This means introducing more work to BI administrators and adding potential security problems. Imagine if BI admin won’t be notified of a person transfer from department to department or from sales to marketing. And why should BI administrator even care about that )

Pro’s and cons of each way are easy to deduct and choice is always organization-dependent, so I won’t stop on that and go right to complains section.

Using Active Directory in Cognos and Oracle BI

Cognos

About a year ago we had a PoC on Cognos, where the requirement was to not just common “users and groups”, but also we had to use custom created AD properties for data filtering.

It’s easy to do in Cognos 8 BI: you just add Framework Manager macro that returns this property. Piece of cake really. And it turned out to be hard for our competitors. Description on IBM portal, historical number —  1027162

Oracle

Recently I’ve had to do the same with Oracle BI and it turned out that Oracle BI’s current integration with Active Directory is pretty basic. The only way to communicate with AD is authentificate users through it, there’s no way to access User groups or custom properties (there is a way, but it requieres changing ActiveDirectory, which isn’t good at all). See Metalink note on this subject: 544828.1

So there’s no built-in way in OraBI to get all those delicious groups and other properties. But there’s always a way out called scripting )

Extracting Active Directory information to csv

You can write a script that will extract needed information from Active Directory to a csv file and then schedule it for nightly extracts, load it into a table and use this table in Oracle BI security variables. This is a reliable solution, but it introduces a time lag between AD modifications and your extracts, which can be important from security point of view. Same example –user is transfered and can still access BI data he’s no longer authorized to see until Active Directory information is extracted again. Setting small extract lags can bring unwanted load on Active Directory, so it’s once again a balanced choice to make.

Links for extracting Active Directory information to csv:

• Step by Step Active Directory Export Guide
• JXplorer — a graphical tool to browse any LDAP server