Firewall Ports and OBIEE 12c

Firewalls for OBIEE 12c

Firewalls are incredibly useful and alsy immensely frustrating, especially when you're in a large corporation and want to get things done. You have a new server sitting somewhere on the network and install (or try to) OBIEE only to discover that only you can see this machine as localhost and it is buried behind firewalls and varying levels of security.

Getting approval to open this machine up to be useful is bound to be a challenge.

Here's a list of the ports I have uncovered that I had opened enabling a multi node OBIEE install.

OBI System Components

9507: BI Presentation Services
9508: Cluster Controller
9509: Cluster Controller (monitor)
9510: Java Host
9511: Scheduler
9512: Scheduler (monitor)
9513: Scheduler (script)

Weblogic Components

9500: Admin Server (HTTP)
9501: Admin Server (HTTPS)
9504: Admin Server (Internal)
9502: Managed Server (HTTP)
9503: Managed Server (HTTPS)
9505: Managed Server (Internal)
9506: Node Manager

And don't forget 1521 - very important if you want to talk to your Oracle database.

OBIEE - Remember Username and Password

I came accross this 'hack' and thought it may be worth a mention if used sparingly on a non-production (i.e. DEVELOPMENT) environment.

Use at your own risk.

Remember Username and Password in Obiee11g

Go to your BIPS config path, for example like below,
D:\OBIEE11G\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

and the search Security tab in your instanceconfig file and add

<AllowRememberPassword>true</AllowRememberPassword>

Then save it and restart bips servervices.
Include the elements and their ancestor elements as appropriate, as shown in the following example:

<Security>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ClientSessionExpireMinutes>210</ClientSessionExpireMinutes>
<AllowRememberPassword>true</AllowRememberPassword>
<CookieDomain>value</CookieDomain>
<CookiePath>/analytics</CookiePath>
<InIFrameRenderingMode>prohibit</InIFrameRenderingMode>
</Security>

Save your changes and close the file.

Restart Oracle Business Intelligence Presentation Services using OPMN

Login to your BI Home page ..then logout and login again. OBIEE will remember your Username and Password and go straight to your BI home page.

For More please refer to the Oracle documentation,
http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/psconfigset.htm#BIESG1292

OBIEE 11g : RPD Consistency Check

A Consistency Check ensures that the repository is in a fit state to process any queries passed through.

A "Consistency Check" is a feature of the Administration Tool that checks whether a repository has met certain requirements, such as the following:

•  All logical columns are mapped directly or indirectly to one or more physical columns.
•  All logical dimension tables have a logical key.
•  All logical tables have a logical join relationship to another logical table.
•  There are at least two logical tables in the business model: a logical fact table and a logical dimension table. Both can map to the same physical table. 
• There are no circular logical join relationships. 
• A subject area exists for the business model.

You can check consistency for the entire repository or for a single object:

For the entire repository: select File > Check Global Consistency.  or Tools > Show Consistency Checker.

For an individual repository object: select the object, right-click and select Check Consistency. You can select multiple objects and check their consistency.

When you check in changes to a repository you are also prompted to Check Consistency before saving, this however is for the entire repository. A large repository can take some time to complete a check.

It is good practise to keep checking consistency in the local area you are working and making changes. You may even notice that the consistency checker has reported no errors but has 'fixed' some inner workings. It may be worth digging in to what is actually being adjusted automatically to suit what the consistency checker expects. If anyone knows please drop me a line.

OBIEE 11g: Exceeded configured maximum number of allowed input records

Oracle have limited various numbers of outputs for rows, columns, prompts, cells and pivots to default values. These values are ususally set high enough to never become an issue, but for really large reports you will bump up against an error message saying you have exceeded some maximum.

So what do you do?

Step 1

Edit the instanceconfig.xml file. Typically, this file is located in the following directory: <OBIEE11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
If you are running a load balanced environment please make sure all environments have the same settings (or share the same settings) or you'll be forever chasing down 'weird' customer experiences.

Step 2

There are parts of the instanceconfig.xml you cannot edit directly and those sections will be marked as only being updateable via Oracle Enterprise Manager. The parts we can edit I have marked in bold.

<Views>
<Pivot>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxCells>10000000</MaxCells>
<MaxVisibleColumns>1000</MaxVisibleColumns>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>10000</MaxVisibleRows>
<MaxVisibleSections>25000</MaxVisibleSections>
<DefaultRowsDisplayed>128000</DefaultRowsDisplayed>
</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>10000</MaxVisibleRows>
<MaxVisibleSections>2000</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxCells>10000000</MaxCells>
</Table>
</Views>

Note that the value you choose for <MaxCells> should never be more than <MaxVisibleColumns> * <MaxVisibleRows>.

In the case above <MaxVisibleColumns>1000</MaxVisibleColumns> * <MaxVisibleRows>10000</MaxVisibleRows> = <MaxCells>10000000</MaxCells>

Please restart all presentation services to pick up the new settings.

OBIEE - Catalog Cleanup

Catalog Cleanup

Do you sometimes look at the catalog and wonder what kind of timebomb is lurking in there? I have nightmares. You've designed a catalog layout and asked developers to follow standards and follow the guidelines, but one year later......? What about two or even three years later? What Answers work has been started and abandoned, unfinished and unworking? What Answers remain from 'deleted' or 'hidden' dashboards and now no longer work due to repository changes?

The challenge

We are a small team of developers so so for us automation of tasks is key. For each report in the catalog what we need to know is:
Does this report generate a useable query? (Is it broken)?
Is this report on a dashboard, or more than one dashboard?
If the report is on a dashbord, is it in the correct directory?

We anticipate that this will be repeated periodically, you can never be sure what creeps in and slips through the cracks, but at least the generation of the report can be automated and scheduled.

At a high level what we do is to combine the output of some Catalog reports output, and use NQCMD to generate logical queries and track the outputs.

The Catalog Reports

Firstly we will generate a Dashboard Report and then an Analysis Report, and we'll upload the data into some database tables and stitch the two together with an outer join so we can see those reports on dashboards as well as those without.

We call the Analysis Report and a Dashboard with a script like below; Note, your system will have different settings.

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Analysis.txt -type "Analysis" "Name" "Path" "Table" "Column" "Subject Area"

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Dashboard.txt -type "Dashboard" "Name" "Path" "Dashboard Style" "Dashboard Page Name" "Dashboard Page Path" "Dashboard Page Description" "Analysis Name" "Analysis Path"

Join the two outputs on "Path" and "Analysis Path" - think outer join here because we still want to process those Analysis not on dashboards.

Generating a Logical Query

What we are going to do is pass the each analysis into NQCMD using the current repository to test whether a valid Logical Query.

Create a text file called cred.txt and make the contents something like
login=weblogic
pwd=weblogic123

Now using the report output from part 1 we can run the following to extract the query from the catalog for the answer. The Query will be saved into a file called myquery.lsql
D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -online http://%SERVR%/analytics/saw.dll -credentials "D:\CatalogCapture\creds.txt" -forceOutputFile "myquery.lsql" -folder "/shared/...../myreport" -type "Analysis" "SQL"

One of two things happen here:
1. The file containing the query is generated, or
2. An "empty file" is generated.

If we have an empty file the Answer failed to generate a Query and should be flagged for investigation.

However, if we have a query in the file, we need to pass the contents through NQCMD to see if the Answer works.

Extending into NQCMD

We have previously used NQCMD to do impact analysis on repository changes and can use the functionality almost unchanged.

"D:\OBI\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OH140093583 -u weblogic -p weblogic123 -s "myquery.lsql" -o "output.lres"

Once again one of two things can happen here:
1. The logical query passed in returns a result, or
2. An error is generated.

The error may be something like

[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed: 
[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist
 at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.
Statement execute failed


Processed: 1 queries
Encountered 1  errors

Once again flag the error.

Wrapping things up and bringing it together

From each Answer we now know
1. Is it used on a dashboard?
2. Where is the Answer stored?
3. Does it generate a logical query?
4. Does the logical query work?

Now we can focus efforts on fixing, rewriting or deleting those Analysis on dashboards, then those that are not on a dashboard but do not work.
Your initial steps may just be to identify those Answers that don't generate a logical query for a maintenance routine?

OBIEE - Automatically Generate Repository Documentation

I have strived to close the loop with automating all of the internal reporting on OBIEE.

So far we have automated the capture of what reports, filters and prompts are on our dashboards.

We have in place an automatic system for recording the performance of the reports on the current dashboards. This we run every night and the results uploaded to the database.

We have the option of running an 'after' script which compares the output from dashboards (logical sql, row counts and timings) when we upload a new repository, this gives us impact analysis.

What we lacked was a way to automate the generation of the repository documentation. This was the manual step that was sometimes skipped.

Having a report on the repository, from physical to presentation and then joining this data to what's on the dashboards gives us a view of what's being used where and where the data is sourced from.

Now I must admit, this seems like a 'hack' and I wouldn't use it on any environment where I couldn't freely break things without causing damage. Oh, and it is windows only - there is no AdminTool for Linux/Solaris - sigh.

The Repository Command Script

So what do we have to do?

We are going to use the /command switch of the AdminTool to run a script of, you guessed it, .... commands.

The command script we've called report_rep.build and put in the following few lines, replacing MWHOMW with your own settings, repository with your own and the repository password with your own.

OpenOffline <MWHOME>\instances\instance1\bifoundation\OracleBIServerComponennt\coreapplication_obis1\repository\repository.rpd <repository password>
DescribeRepository D:\scriptoutput\repository_report.csv UTF-8
Close
Exit

The next part involves creating a script to run the AdminTool and pass in the script file. Just to be safe it may be worth setting the environment using some of the bits from the bi-init.cmd as follows:

@echo off
set ORACLE_HOME=<MWHOME>\Oracle_BI
set ORACLE_INSTANCE=<MWHOME>\instances\instance1
set ORACLE_BI_APPLICATION=coreapplication
set JAVA_HOME=<MWHOME>\Oracle_BI1\jdk
set ESSBASEPATH=%ORACLE_HOME%\clients\epm\Essbase\EssbaseRTC
call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
set PATH=%ORACLE_HOME%\common\ODBC\Merant\5.3\Drivers;%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ESSBASEPATH%\bin;%ORACLE_HOME%\bin;%PATH%
<MWHOME>\Oracle_BI1\bifoundation\server\bin\AdminTool.exe /command d:\scripts\report_rep.build

Before running the above script we copy the latest repository to one side and call it repository.rpd. Not very imaginative I know but that way we don't muck around with the actual repository and, in theory, if we script it right, this will always be a copy of the latest and most up to date RPD.

You should be left with a CSV report of your repository (all being well).

Upload to your favourite database and analysis tool.

OBIEE - Displaying the Value of System Variables

Following from the previous article here on the subject of displaying your repository variables, I thought it may be time to look at showing some of the system variables

On a dashboard, drag in a text item and edit the contents to include something like

We are running OBIEE version [u][b]-@{system.productVersion} [/b][/u]
This is the @{dashboard.name} dashboard
and we are on the @{dashboard.currentPage} page.

OBIEE - Sample Administration Documentation

As I mentioned in Part 1 of a previous article here, you really need to have documentation around your OBIEE environment.


Not the most glamerous of suggestions surely, but your boss and sysadmin will love you. Start by documenting where to find stuff middleware home, logfiles, config files (instanceconfig.xml etc.), the URLs used for administration.

Then move on to documenting the config changes made after the install, what settings were changed and if known what the default values were before and what they are now. What are the connection details for your RCU schemas (BIPLATFORM and MDS). Capture things like server names and directory structures.

Now comes the living documents which you can generate periodically and version. Run off a repository report and catalog reports. This will give you (and your colleagues) a better understanding of what the repository looks like and also what in the catalog is where on the dashboards.

As an added benefit you can now work backwards from the catalog reports through the repository reports to see where data comes from to fill reports, and vice-versa, from a physical column work your way through the repository to the catalog report to see where the data is used.

Remember, having no documentation means a risk to your business.

And keep it up to date, mark your calendar for a monthly review, most times , no change, but there will be that month.......

Here's a suggestion of some topics to get you started!

General Info about your OBIEE Install

Server
URL
DNS Shortcut
WebLogic Console
Enterprise Manager
Repository Name
Catalog Path

Install Type Information

Type
OS
Install Dir

OBIEE Configuration

writeback
EVALUATE_SUPPORT_LEVEL
Usage Tracking
Repository Monitoring

Users/Passwords

Schema Prefix
BIPLATFORM
MDS
weblogic
Repository

Custom Deployments

password_change.ear

Maps

OBIEE Lockdown - Part 1

Security Recommendations in Oracle Business Intelligence Installations

There are three key security recommendations for installing and securing OBIEE in your enterprise environment.

1.       Be serious about password management.

It is important to lock down the OBIEE environment and changing default passwords after installation.

The passwords for weblogic user should not be simple to guess or widely distributed. The best practice is to have different passwords throughout the environments. Moreover, I’d recommend replacing “weblogic user” with another obfuscated username. The username and password combination should be different throughout the environments (at least the password).

Change the default passwords for the RPD, as everyone knows “Admin123.”  If you are promoting an RPD, your password for Dev, Test, and Prod RPDs should be different as well to avoid a possibility of a developer making changes in a wrong environment.

Do not use the administrative account to present reports to the users and avoid providing it to users, there is high risk of breaking/deleting something by accident. Consider automating deployments to avoid having to deal with passwords manually.

2.       Take the authentication and authorization outside of OBIEE

Use whatever is standard in your organization for managing authentication (be it OID, Active Directory, or some other sort of LDAP). Not only does it improve user experience by removing the need to memorize yet another username/password, but it also saves time for the development team. Remember: They are not in the user management business. Another great use case is the ability to push this functionality further by automating users’ authorization by managing LDAP groups’ memberships and mapping them to the OBIEE’s application roles.

3.       RPD Security Objects

Unfortunately, the Administration Tool does not allow development separation within the repository objects (one needs to be an administrator to edit the RPD file – no way around it), hence anyone with the RPD/Admin password can do pretty much anything within the RPD. At this time, there are limited built-in options to audit or keep track of the RPD development changes. One must rely on a custom developed solution if this functionality is important. An easy and practical option is to keep an XLS spreadsheet of the major development milestones. It’s simple; however, its disadvantage is, that there is an overhead associated with maintaining it, especially during active development periods. Another option would be to use an Administration tool utility to create metadata repository document on a regular basis and track deltas. Finally, more advanced options would include MUDE (multi user development environment) projects and XML export.

OBIEE - Increase Map Joins

OBIEE has a default limit of 500 joins to a map.

There are occasions where you may need to up this limit and you will need to make changes to this setting if more points of contact to the map exist.

You will need to add something like the below into your instanceconfig.xml file

<SpatialMaps>
<ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
<RemoteOracleMapViewerAbsoluteURL/>
<LayerDataLayout>
<MaxRecords>1500</MaxRecords>
</LayerDataLayout>
</SpatialMaps>

OBIEE Repository Variable Report

You can create a report to show what your repository variables are set to by doing something like below:

Create a 'dummy' report with a static text view and enter something like below and make sure you have selected the contains HTML Markup box.

[u][b]Predefined Presentation Variables[/b][/u][br/]
[b]session.language:[/b] @{session.language}[br/]
[b]session.locale:[/b] @{session.locale}[br/]
[b]DISPLAYNAME:[/b]@{biServer.variables['NQ_SESSION.DISPLAYNAME']}[br/]
[b]USER:[/b]@{biServer.variables['NQ_SESSION.USER']}[br/]
[b]GROUP:[/b]@{biServer.variables['NQ_SESSION.GROUP']}[br/]
[b]ROLES:[/b]@{biServer.variables['NQ_SESSION.ROLES']}[br/]
[b]ROLEGUIDS:[/b]@{biServer.variables['NQ_SESSION.ROLEGUIDS']}[br/]
[b]PERMISSIONS:[/b]@{biServer.variables['NQ_SESSION.PERMISSIONS']}[br/]
[b]USERGUID:[/b]@{biServer.variables['NQ_SESSION.USERGUID']}[br/]

Update: See more on System Variables here.

OBIEE - Repository System Session Variables

Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

Two types of session  variables:

• System Session Variables
  
• Nonsystem Session Variables
  

Sytem Session Variables : 

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and nonsystem session variables).

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL.

 

Variable	Description
USER	Holds the value the user enters as his or her logon name. This variable is typically populated from the LDAP profile of the user.
PROXY	Holds the name of the proxy user. A proxy user is a user that has been authorized to act for another user.
GROUP	Contains the groups to which the user belongs. Exists only for compatibility with previous releases. Legacy groups are mapped to application roles automatically.
WEBGROUPS	Specifies the Catalog groups (Presentation Services groups) to which the user belongs, if any. Note that the recommended practice is to use application roles rather than Catalog groups.
USERGUID	Contains the global unique identifier (GUID) of the user, typically populated from the LDAP profile of the user.
ROLES	Contains the application roles to which the user belongs.
ROLEGUIDS	Contains the global unique identifiers (GUIDs) for the application roles to which the user belongs. GUIDs for application roles are the same as the application role names.
PERMISSIONS	Contains the permissions held by the user, such as oracle.bi.server.impersonateUser or oracle.bi.server.manageRepository.
DISPLAYNAME	Used for Oracle BI Presentation Services. It contains the name that is displayed to the user in the greeting in the Oracle BI Presentation Services user interface. It is also saved as the author field for catalog objects. This variable is typically populated from the LDAP profile of the user.
PORTALPATH	Used for Oracle BI Presentation Services. It identifies the default dashboard the user sees when logging in (the user can override this preference after logged on).
LOGLEVEL	The value of LOGLEVEL (a number between 0 and 5) determines the logging level that the Oracle BI Server uses for user queries. This system session variable overrides a variable defined in the Users object in the Administration Tool. If the administrator user (defined upon install) has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.
REQUESTKEY	Used for Oracle BI Presentation Services. Any users with the same nonblank request key share the same Oracle BI Presentation Services cache entries. This tells Oracle BI Presentation Services that these users have identical content filters and security in the Oracle BI Server. Sharing Oracle BI Presentation Services cache entries is a way to minimize unnecessary communication with the Oracle BI Server.
SKIN	Determines certain elements of the look and feel of the Oracle BI Presentation Services user interface. The user can alter some elements of the user interface by picking a style when logged on to Oracle BI Presentation Services. The SKIN variable points to an Oracle BI Presentation Services folder that contains the nonalterable elements (for example, figures such as GIF files). Such directories begin with sk_. For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx.
DESCRIPTION	Contains a description of the user, typically populated from the LDAP profile of the user.
USERLOCALE	Contains the locale of the user, typically populated from the LDAP profile of the user.
DISABLE_CACHE_HIT	Used to enable or disable Oracle BI Server result cache hits. This variable has a possible value of 0 or 1.
DISABLE_CACHE_SEED	Used to enable or disable Oracle BI Server result cache seeding. This variable has a possible value of 0 or 1.
DISABLE_SUBREQUEST_CACHE	Used to enable or disable Oracle BI Server subrequest cache hits and seeding. This variable has a possible value of 0 or 1.
SELECT_PHYSICAL	Identifies the query as a SELECT_PHYSICAL query..
DISABLE_PLAN_CACHE_HIT	Used to enable or disable Oracle BI Server plan cache hits. This variable has a possible value of 0 or 1.
DISABLE_PLAN_CACHE_SEED	Used to enable or disable Oracle BI Server plan cache seeding. This variable has a possible value of 0 or 1.
TIMEZONE	Contains the time zone of the user, typically populated from the LDAP profile of the user.

The SELECT_PHYSICAL command provides the functionality to directly query objects in the Physical layer of the metadata repository, and to nest such a statement within a query against the Business Model and Mapping layer or the Presentation layer.

Syntax for the SELECT_PHYSICAL Statement

Basic syntax for SELECT_PHYSICAL queries is equivalent to  basic syntax of select statement  with the term

SELECT_PHYSICAL replacing the word SELECT, namely:

SELECT_PHYSICAL [DISTINCT] select_list
FROM from_clause
[WHERE search_condition]
[GROUP BY column {, column}
     [HAVING search_condition]]
[ORDER BY column {, column}] 

 Source:

http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/variables.htm#BIEMG3104

Smooth(er) OBIEE Administration

I am one of those people who likes to know how things work. I'm constantly digging through the Oracle guides for OBIEE and trawling the net to find those nuggets of information that will make my life easier.

I have a confession to make at this point. I will go out of my way to avoid doing repetitive (boring) work. Like most people I would rather do  something new and exiting rather than push the same buttons in the same sequence day after day.

OBIEE administration is one of those jobs that could suck up hours and hours of your day unless you think smart. Unfortunately, most environments expect you to 'get stuck in' and then before you know it ......... the same thing day-after-day. Every OBIEE site has common issues that they struggle with; migration across environments, performance, testing, development, lineage.....

Part 1. OBIEE Documentation: 

Not the most glamerous of suggestions surely, but your boss and sysadmin will love you. Start by documenting where to find stuff middleware home, logfiles, config files (instanceconfig.xml etc.), the URLs used for administration.

Then move on to documenting the config changes made after the install, what settings were changed and if known what the default values were before and what they are now. What are the connection details for your RCU schemas (BIPLATFORM and MDS). Capture things like server names and directory structures.

Now comes the living documents which you can generate periodically and version. Run off a repository report and catalog reports. This will give you (and your colleagues) a better understanding of what the repository looks like and also what in the catalog is where on the dashboards.

As an added benefit you can now work backwards from the catalog reports through the repository reports to see where data comes from to fill reports, and vice-versa, from a physical column work your way through the repository to the catalog report to see where the data is used.

Remember, having no documentation means a risk to your business. View sample documentation here.

Part 2.  OBIEE Automation:

You don't already? You will need to think of all those tasks you do and tackle them one-by-one. It may take you some time to get to a state where executing a single script will do an entire day's work, but you can certainly make a start by taking simple startup and shutdown tasks and automating them. Join them together shutdown then startup, there you go you have a script to 'bounce' the environment. How much time did that save?

You will need to have scripts to help with testing connection pools and migrating RPDs and catalogs. You can generate scripts that 'auto-correct', they start up an environment if it is down or restart components of OPMN. How about migrating users and groups across environments.

When you create a script you put all the effort in up front and reap the benefits in the days that follow.

Part 3. OBIEE Procedures:

There is nothing wrong with doing all your tasks in an ad-hoc manner for a Proof of Concept Project. The nature of the POC is to rapidly change things to reflect the change in your customer's requirements.

But what ad-hoc work does is distract you and eats up your time. Before you know it, you've spent an entire day ad-hoccing (is that a word?) and have nothing to show for it.

If you have a thread of automation or a deployment script for promotion across environments then with a little planning and communication you can ease the workload.

So what do you need to know to deploy? What are you deploying, from where to where and what are the configuration details and security settings?

This doesn't have to become something heavy and cumbersome with reams of documentation, a couple of lines and provides project history and documentation as a byproduct.

Each project will have it's own lifecycle and demands but setting up a process and schedule for releases, do you absolutely have to release the latest developer build to test immediately or can it wait until an agreed time where several pieces of work can be batched and planned in. Immediately the level of distraction for you has dropped.

In conclusion, as you start to fill in some of the parts of administration, you start a feedback cycle that will over time reduce your workload, increase your inderstanding of OBIEE and make you more productive

Generating a OBIEE Performance Test Harness

You know you should be testing you OBIEE stack for performance, but what exactly should you be doing, and how can you do it?

Another question may be what is your baseline? Do you have a known baseline for performance? What tools do you use?

What we want is a method of generating a test plan where we can run (repeatably) the reports on our dashboards. Ideally we would like to generate a series of tests that we can run before a release to generate a baseline and after a release to ensure that nothing (that we are testing) is broken.

So what is available to us "out of the box" that we can plug in and start using straight away?

We will use a couple of components to build up a test.

• Usage Tracking
• NQCMD

Usage tracking: 

Turn on usage tracking for one of your users and open a few dashboards and reports. If you have things configured correctly you should see some entries in the S_NQ_ACCT table (I have made the assumption that you will enable database recording of usage tracking - see how to set this up here.)

The S_NQ_ACCT table tells us what components were run when you viewed the dashboard. To find out what components make up usage tracking query the "QUERY_SRC_CD" column. A sample of results may show something like:
DashboardPrompt
Report
ValuePrompt
Memeber Browser Display Values
Soap
rawSQL

The logical query run for each component is in the "QUERY_TEXT" column, though this truncates the full query after a number of characters and, if like me you have some very large logical queries, it may be worthwhile getting the query out of the "QUERY_BLOB" column.

NQCMD:

I have already discussed NQCMD here but it may be worthwhile going over some of the concepts briefly. NQCMD is a mechanism for executing a logical query against a repository for a user. This means we can execute a logical query against a repository of our choice and run the query as a named user.

The command is in the format
MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -d coreapplication_OH140000583 -u <username> -p <password> -s <Query input file> -T -q  1> output.out

Running the help gives the following output
MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -help

-------------------------------------------------------------------------------
          Oracle BI ODBC Client
          Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------


Argument error near: -help
Command: MW_HOME\Oracle_BI1\bifoundation\server\bin\nqcmd.exe - a command line client which can issue SQL statements
                 against either Oracle BI server or a variety of ODBC compliant backend databases.
SYNOPSIS
         nqcmd [OPTION]...
DESCRIPTION
         -d<data source name>
         -u<user name>
         -p<password>
         -s<sql input file name>
         -o<output result file name>
         -D<Delimiter>
         -C<# number of fetched rows by column-wise binding>
         -R<# number of fetched rows by row-wise binding>
         -a (a flag to enable async processing)
         -f (a flag to enable to flush output file for each write)
         -H (a flag to enable to open/close a request handle for each query)
         -z (a flag to enable UTF8 instead of ACP)
         -utf16 (a flag to enable UTF16 instead of ACP)
         -q (a flag to turn off row output)
         -NoFetch (a flag to disable data fetch with query execution)
         -NotForwardCursor (a flag to disable forwardonly cursor)
         -v (a flag to display the version)
         -SessionVar <SessionVarName>=<SessionVarVAlue>

To use NQCMD we need to pass a logical query SQL in a file.
The logical query file can look something like

SET VARIABLE PRODUCT_NAME='T00103', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q0213EB1783381CBEC92AB1166D5FF8FC', TEST=1:SELECT .................. FETCH FIRST 25001 ROWS ONLY

Now we have looked at the two parts we'll use let's put it all together.

To start we need to mine the usage tracking table for the logical queries of the reports on our dashboards that we wish to test.
Each query should be in its own file.
Ideally each query should be uniquely identifiable, so we can trace a query back to a relevant report or dashboard and also as a means of identification when running the test.

Generating the test logical query input files: I have knocked up a SQL script that extracts the queries from the Usage Tracking Table and saves them in a series of files. Because PL/SQL cannot spool to multiple files dynamically, the following script generates an intermediate file, which is then called and executed.

SET TERMOUT OFF
SET SERVEROUTPUT ON
SET ECHO OFF
SET DEFINE OFF
SET FEEDBACK OFF
SET LINESIZE 5000
SPOOL intermediate_file.sql

DECLARE
  CURSOR cr_qrys  IS
      SELECT DISTINCT TO_CHAR (query_blob) AS query_text, query_key
        FROM s_nq_acct
       WHERE     query_src_cd = 'Report'
             AND dbms_lob.getlength (query_blob) <= 3900
             AND ERROR_TEXT IS NULL
             AND row_count > 0
    ORDER BY query_key;

  i          NUMBER := 0;
  filename   VARCHAR2 (5);
  p1         VARCHAR2 (5000);
BEGIN
  dbms_output.enable (NULL);

  FOR lr_qrys IN cr_qrys LOOP
    i          := i + 1;
    filename   := LPAD (TO_CHAR (i), 5, '0');
    p1         :=
         'SET VARIABLE PRODUCT_NAME=''T'
      || filename
      || ''', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME=''Q'
      || lr_qrys.query_key
      || ''', TEST=1:'
      || lr_qrys.query_text;
    dbms_output.put_line ('SET COLSEP ,  
SET PAGESIZE 0
SET TRIMSPOOL ON 
SET HEADSEP OFF
SET FEEDBACK OFF
SET DEFINE OFF
SET ECHO OFF
SET TERMOUT OFF
SET LINESIZE 5000
SPOOL test_file_' || filename || '.inp
SELECT q''[' || p1 || ']''
FROM DUAL;
SPOOL OFF');
  END LOOP;
END;
/

SPOOL OFF
PROMPT     executing intermediate file
@intermediate_file.sql;
SET SERVEROUTPUT OFF

This generates a series of files with the following structure test_file_xxxxx.inp where the xxxxx matches the test PRODUCT_NAME variable of the query.

This is our the first version of our baseline series of tests. As new dashboards and reports are released you will need to update the test input files with the new report queries and potentially remove those reports that are no longer relevant.

The next step is to pass each file through the NQCMD in turn and capture the output. Assuming the logical query test input files are in a subdirectory called testcases (and using DOS) we can loop through the .inp files as follows

for /r %%i in (testcases\*) do (
<mw_home>\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -d <dsn to repository> -u <username> -p <password> -s %%i -T -q >%%~ni.out
)

Once the above loop has finished we will have a series of files called test_file_xxxxx.out

Peering into an output file you will see they have a structure as detailed below


-------------------------------------------------------------------------------
          Oracle BI ODBC Client
          Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------


SET VARIABLE PRODUCT_NAME='T03818', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q4DFD2F426D93BC2DA37036577533BF8B', TEST=1:SELECT ........... ASC NULLS LAST, 3 ASC NULLS LAST FETCH FIRST 200001 ROWS ONLY

SET VARIABLE PRODUCT_NAME='T03818', DISABLE_PLAN_CACHE_HIT=1, DISABLE_CACHE_HIT=1, LOGLEVEL=2, QUERY_NAME='Q4DFD2F426D93BC2DA37036577533BF8B', TEST=1:SELECT .............................

--------------------------------------------------------------------------------------------------------------------------------------------------------
s_0          s_1                                                                                                  s_2          3 s_4                    
--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
Clock time: batch start: 10:34:21.000 Query from: 10:34:22.000 to: 10:35:22.000 Row count: 15
 total: 59.920 prepare: 0.016 execute: 59.889 fetch: 0.015
Cumulative time(seconds): Batch elapsed: 60 Query total: 59.920 prepare: 0.016, execute: 59.889, fetch: 0.015, query count:  1, cumulative rows:  15

--------------------------------------------------------------------------------------------------------------------------------------------------------


Processed: 1 queries

You can see from the contents we have a wealth of information and using a couple of scripts you can extract the values for PRODUCT_NAME and the timings near the end of each file. When you process them together you should have something like

Date Test No Batch Start Query From Query To Row Count Total Time Prepare Time Execute Time Fetch Time Batch Elapsed Prepare Query Total Fetch Query Count Cumulative rows
21/01/2014 'T00005' 15:24:08 15:24:11 15:24:17 2 5.906 0.016 5.859 0.031 8 0.016 5.859 0.031 1 2
21/01/2014 'T00007' 15:24:21 15:24:22 15:24:23 9 0.593 0 0.578 0.015 1 0 0.578 0.015 1 9
21/01/2014 'T00008' 15:24:24 15:24:25 15:24:38 35 12.671 0.031 12.624 0.016 13 0.031 12.624 0.016 1 35

Which you can load into your favourite database and perhaps even build a dashboard on.

OBIEE - Javascript and CSS locations

In OBIEE 11g, the path for Javascript and Custom Style Sheet is located at-

OBIEE\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\

And for using them-
<script type="text/javascript" src="res/test.js"></script>
<link href="res/test.css" type="text/css" rel="stylesheet">

Refererence- https://forums.oracle.com/forums/thread.jspa?threadID=2453825&tstart=0

OBIEE Security - Default BI/Application Roles

Default BI Role:

Authenticate User : BI Administrative/Administration User

Default Application Roles

An application role defines a set of permissions granted to a user or group.Default application roles have corresponding default system user groups used in assigning catalog permissions and system privileges.

The Following are the Default Application Roles:

• The BIAdministrator role grants administrative permissions necessary to configure and manage the Oracle Business Intelligence installation. Any member of the BIAdministrators group is explicitly granted this role and implicitly granted the BIAuthor and BIConsumer roles.

• The BIAuthor role grants permissions necessary to create and edit content for other users to use, or to consume. Any member of the BIAuthors group is explicitly granted this role and implicitly granted the BIConsumer role.

• The BIConsumer role grants permissions necessary to use, or to consume, content created by other users. By default, every Oracle Business Intelligence authenticated user is part of the BIConsumers group and does not need to be explicitly added to the group or role

OBIEE 11.1.1 - How to turn off EM centralized control

Note: This is not for the faint hearted and is not recommended, but is available in emergencies.

There is a flag in the central config file which can be used to turn off all of the EM central configuration. Edit the biee-domain.xml file from location ORACLE_HOME\user_projects\domains\bifoundation_domain\config\fmwconfig.  The flag is near the top; it says: centralConfigurationEnabled="true" . If you change this to "false", then none of the config files will be modified centrally.

(You will need to restart the managed server and BI System Components)

So what happens: In OBIEE 11G, The instanceconfig.xml file stores the OBI Presentation Server configuration settings. Many configuration settings are available in Enterprise Manager and those should be set within EM only. If any particular setting is not available in EM then we make changes directly in the instanceconfig.xml file. Like changing the default value of the maximum number of rows in a pivot table.

If you change any of the settings directly under EM control, as soon as you restart the BI components you will find the original values returned and your settings overridden. Setting centralConfigurationEnabled="false" will disable the EM settings overwriting any new values.

Important Note: Turning off EM centralized control for managing BI config files is NOT recommended.

OBIEE - Clear Cache -SAPurgeAllCache()

Warning !!!!
What follows is technical (geeky) and should be approached only by those personnel wearing appropriate safety equipment!

One command to remember

Call SAPurgeAllCache()

Login to dashboards as normal, and go to Administration and then Issue SQL

In the box that appears input the command from above.
Call SAPurgeAllCache()

Click Issue SQL

Server Cache cleared! I’ll leave you to clear the browser cache.

Here’s what the server cache looks like.

You can also clear the cache of specific databases.
Call SAPurgeCacheByDatabase( ‘MYDB’ );

Will clear just the MYDB database cache i.e. the OBIEE physical database in the right column.

There are other similar commands to clear specific tables / queries. That I’ll leave to you.

OBIEE MUDE

It's been a while since I last put up any substantial post so I thought maybe it was time to leave you with a brain dump of what I've (been attempting to) achieved over the past month.

Two major things stand out;
1. MUDE - Multi User Development Environment.
2. Integration with SVN Subversion.

The first was a no-brainer really, a few too many developers in the repository at once, lots of half finished code, the nightmare of promoting the repository to production based on what was complete or mostly complete. Definately time for MUDE.

For those of you unaware of MUDE and what it is, not many I'm sure, it is a method of segregating the repository into various projects, where each project is self contained and has all the component parts required to function. The projects can be checked out to a local install by developers where they work, in isolation, before merging their changes back into a central repository.

MUDE

To setup MUDE you need three things; a bi server, a shared directory on that server and a full local install of OBIEE.

On the server create a shared directory and ensure that all your users have sufficient priveleges to merge changes back.
The MUD Administrator then copies in the Repository required and this will become the MUD master against which all changes are published.
The repository is opened offline and divided into projects - again by the MUD Administrator.

On the client side the users open the OBIEE administration tool and in the Tools --> Options --> Multiuser Tab, point the where the MUD master shared directory (\\server\muddir) is and describe the environment (eg DEV Machine)

When a developer wants to work they will start the OBIEE administration tool on their local installation. From the File menu they will then select Multiuser and then checkout. Select the project, as setup by the MUD Administrator, on which you want to work and open.

The local repository consists of only those details required to make the top level components in the repository work. The project may not have specified the physical layer to include and this will be added at checkout by the MUD process.

The developer, using their local install, will make changes and test outputs against their local answers queries for that project.

Once changes are made the operator has several options;
1. Discard changes - revert to how things were.
2. Refresh subset - pick up any changes in the master MUD repository that have occured since the checkout, that affect this project.
3. Compare to original - show what has changed.
4. Publish to Network - promote changes back to the MUD Master repository.

When you publish back to the MUD Master repository, a merge takes place and if conflicts arise, you need to resolve them by selecting what goes through for publication.

The MUD Master repository will then be uploaded through EM and changes promoted up through your environments.

Just bear in mind the following;

You may require named user plus licensing to be financially viable
MUD is more complex than online development, but makes sense when you know how it works
MUD Administrator divides main repository into projects; self-contained RPD subsets
Master repository is then published to a network share
Projects are then worked on independently, and then merged back into the master RPD
Uses the repository compare and merge features under the covers
Works best when each developer has a full OBIEE “Sandbox” environment to develop with and unit test their work License considerations

Integration with SVN follows.

iPad and OBIEE sessions

There are several users that connect via iPads to view dashboards.

Here's what they look like when connected and you view them in the session manager from the Administration page.

We haven't made any special accommodation for these devices, but they don't seem to suffer as a result of our unintentional neglect and their performance is no worse than through a browser over wifi.