OBIEE Catalog for large organisations part 2

In OBIEE Catalog for large organisations part 1 I showed you how to set up a catalog for large organisation following the OBIEE documentation. But still you might run into trouble.

Take a large energy firm which supplies there customers with some reporting on there energy consumption. They serve about 90.000 homes. Each home is an account, each account is based on the Dutch postal code system. >>zip_code+house_number<< (fi: 5721XW002).

The code is roughly divided into 4 part; The first 2 numbers are the post district area, the next 2 are the city area, the 2 letters are the street(side), the 3 number indicated the house number (optionally there might be a suffix)

If we only ‘hash’ the catalog by the first 2 characters you still run into trouble since there are roughly 20.000 houses in the 57xx postal code area.

A solution might be to change the 2 into a 5. This will ‘hash’ the first 5 characters. (yes, theoretically you might still run into trouble, but luckily not every possible postal code is issued and not every street has a 999 houses )

Remember this setting has to be made BEFORE the first user log on to the catalog!

Till Next Time

OBIEE Get all users from RPD

I had to get all the users from a very large repository because they where implementing a new security model. Wrote a small script to make life easy:

'Read_Users.VBS
'John Minkjan
'http:// http://www.obiee101.blogspot.com/
'Get all the users from a repository
'1: Make an UDML export of the PRD using nqgenudml.exe
'2: Change the filename/location in this script
'3: Run the script from the command line cscript Read_Users.VBS > users.txt
Set objFSO = CreateObject("Scripting.FileSystemObject")

'point this to your UDML EXPORTSet
objFile = objFSO.OpenTextFile("E:\names.txt", ForReading)

Const ForReading = 1
Dim arrFileLines()
dim strRLinedim strTemp1dim strTemp2

i = 0

Do Until objFile.AtEndOfStream
strRline = objFile.ReadLine
if left(strRline,12) = "DECLARE USER" then
Redim Preserve arrFileLines(i)
arrFileLines(i) = strRline
i = i + 1
end if
Loop

objFile.Close
'Then you can iterate it like this
For Each strLine in arrFileLines
strTemp1 = MID(strLine ,15 , 50)
IF instr(strline,"}" ) >0 THEN
strTemp2 = MID(strLine , instr(strline,"{")+ 1, (instr(strline,"}") - (instr(strline,"{")+ 1))) ELSE strTemp2 = ""
END IF
WScript.Echo MID(strTemp1 ,1 , instr(strTemp1, """")-1) &";" & strtemp2
Next

Till Next Time

OBIEE Impersonate a user

If you want to check how a report or dashboard runs as a different user but you haven't yet setup "ACT AS" functionality or you don't know the password of the user, you can pass the &impersonate variable trought the URL. (If you are logging in as Administrator)

Change this URL to the one shown below:

For dashboards: http://vmobiee:9704/analytics/saw.dll?Dashboard&impersonate=OBIEE_A

Directly in answers: http://vmobiee:9704/analytics/saw.dll?Answers&impersonate=OBIEE_A

where OBIEE_A is the username of the person you want to impersonate.

Till Next Time

OBIEE scripting user into the repository

On the OTN forum today there was a question about how to script a new user into the repository: http://forums.oracle.com/forums/thread.jspa?threadID=697646&tstart=0

The only way I know is using the nQUDMLExec.exe

First create a txt file with your new users:

DECLARE USER "NewUser" AS "NewUser" UPGRADE ID 1 FULL NAME {New User} PASSWORD
'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F'
PERIODICITY 90 HAS ROLES ( "Administrators", "XMLP_ADMIN" ) DESCRIPTION
{password = welcome1} PRIVILEGES ( READ);

The password can only be entered encrypted, this string is for "welcome1".

Next you have to "merge" the txt file into the reopsitory:

E:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P Administrator -I E:\temp\NewUser.txt -B E:\temp\paint.rpd -O e:\temp\newrep.rpd

Check the new repository!

Till next Time

OBIEE referencing session variables in report title

After spending way to much time figering out the exact lingo, I decided to blog them for futher reference:

• currentTime => @{system.currentTime}
• system.productVersion => @{system.productVersion}
• session.locale => @{session.locale} = en-gb
• session.language => @{session.language} = en
• rtl =>@{session.rtl} = false
• session.loginTime => @{session.loginTime}
• session.logoutTime =>@{session.logoutTime }
• session.lastAccessTime =>@{session.lastAccessTime}
• session.currentUser => @{session.currentUser.id} = administrator
• user.homeDirectory => @{user.homeDirectory} = /users/administrator
• user.id => @{user.id} = administrator
• user.displayName => @{user.displayName} = administrator