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 - 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

OBIEE - Change Repository Password

If you need to change your repository password, the one you use to 'unlock' the repository for editing or uploading, it is relatively simple to change

Open the repository as normal...


In the File menu is a “Change Password” option

Click and change the password, couldn't be simpler.

OBIEE - Testing the Repository Using NQCMD

You have finished making your changes to the repository and the consistency check reports no errors or warnings. Great, it's all working as planned. Unfortunately all that the consistency checker does, is make sure that logically the repository hangs together, not that your business logic is correct. A consistency check means that most common problems with the construction of the physical layer and the facts and dimensions.

Now to test that the outputs are as expected. (see also Generating an OBIEE performance Test Harness)

NQCMD

We can automate this using nqcmd and sample reports.

Before starting NQCMD run bi-init (found in ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup) to give a properly configured command line environment to run the OBI commands.

A look at the help for the command reveals a load of options:

C:\obiee11g\Oracle_BI1\bifoundation\server\bin>nqcmd -?

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


Argument error near: -?
Command: nqcmd - 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 in the output result file
         -utf16 (a flag to enable UTF16 for communicating to Oracle BI ODBC driver)
         -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)
         -ONFormat<FormatString, i.e. TM9, 0D99>

Before we go further go to answers and build a test query against the part of the repository you wish to test, in the advanced tab copy out the query and save this to a file. This will be the SQL file used in the test.

Run the following, your connection data source name will be different, as hopefully will your username and password.

nqcmd -d coreapplication_OH206444865 -u weblogic -p weblogic -s C:\Test\test.sql -o C:\Test\test.txtv -D; -utf16

Note that the -D is the delimiter between SQL statements in the input file if more than one select statement is in there.

Your output file should contain the query and end something like
....
....
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 250001 ROWS ONLY

-------------------------
s_0          s_1         
-------------------------
0            00/000008/  
-------------------------
Row count: 1
-------------------------


Processed: 1 queries

If you set up a DSN to the other server you can run the same command line and only change the contents of the -d flag, then compare the results.

Always useful if you want to test that nothing is broken prior to releasing a change.

Update :
See here for an worked example of how to automate all this in Generating an OBIEE Performance Test Harness

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.

MUD - Multi User Development (option file)

Recommendation - Each user of MUD ie. those that will check out projects, make changes and check those changes in, should have a full (simple) install on their local machine.

Before going 'big-bang' on MUD, try all the features in a simple and controlled manner.

Pay particular attention to creating the (hidden) repository.opt file and using the correct options. This has a big impact on the way you will work.

1. The MUD options are set in a file that you create in the MUD shared directory.
2. The filename should be be the same as the MUD master repository ie. if your MUD master is called BIREP.rpd, then your options file should be called BIREP.opt.
3. The file attributes should be set to hidden using the file properties dialog box.
4. As an additional step you may want to disallow edit permissions to all users (beyond a nominated set of administrators).

Note: Any options not explicitly set are considered to be turned off by default.

The contents of the opt file are as below:
[Options]
BuildNumber = Yes
Enforce Build Number = 11.1.1.7.0
Enforce MUD Protocol Version Number = 1
Prevent Rpd Upgrade = Yes
Admin = weblogic;joebloggs
Mandatory Consistency Check = Yes
Equalize During Merge = Yes

To turn an option off either, remove it from the file or set the value from Yes to No.

BuildNumber = Yes - when set to Yes this displays the build version of the administration tool in MUD history.

Enforce Build Number = 11.1.1.7.0 - When set this means that only users with an exact match of Administration Tool version can make changes. To unset this option remove it from the opt file.

Enforce MUD Protocol Version Number = 1 - This option will only allow those users with an exact match of MUD version number to perform changes. Remove this option to disable the enforcement.

Prevent Rpd Upgrade = Yes - Only users with an exact match of repository version can perform changes using MUD. It is possible for later versions (client side) to check out and make changes to the repository and potentially (though unlikely) make changes incompatible with the current master MUD repository . Set to No or remove to disable this option.

Admin = weblogic;joebloggs - This is the list of MUD administrators, seperated by semicolons. Only administrators listed here can delete MUD history.

Mandatory Consistency Check = Yes - A full consistency check is performed at publish to network. All errors in the repository will need to be fixed before publishing completes. Set to No or remove to disable this option.

Equalize During Merge = Yes - A equalisation process takes place during the merge process, potentially affecting performance. The equalization process ensures that objects in the two repositories that have the same name but different upgrade ids are treated as the same object, preventing unintended renaming during the merge.

OBIEE TNSNames Revisited

Following on from my previous article on OBIEE and the tnsnames file.

If for some reason you have no access to the OBI server, and you have a tnsnames.ora file do not despair.

It is entirely possible to configure a database connection within the connection pool settings. But bear in mind this is not very portable, especially with regards to promoting repositories to production - you will have to edit these details manually, and should be used sparingly. There is a "higher" risk of mistyping details.

So how does it work?

To configure the Oracle BI repository:

1. In the Administration Tool, open the repository in online mode. Online mode is strongly recommended for performing data access security tasks.

2. In the Physical layer, right-click and select New Database. The Database dialog is displayed.

3. For Name, enter a name of your choice.

4. For Database, select the type of database that you have installed for use with Oracle Business Intelligence (typically Oracle 11g).

5. Select the Connection Pool tab and click the Add button. The Connection Pool dialog is displayed.

6. For Name, enter the connection pool name you require.

7. Select the Call interface appropriate for the database (for example, OCI 10g/11g for Oracle Database). For Data source name, provide the information that is appropriate for the database that you have installed and configured for use with Oracle Business Intelligence. For example, for Oracle Database, enter a connection string similar to the following:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=BIOracle)(SID=BIOracl)))

When connecting to an Oracle Database data source, you can include the entire connect string, or you can use the net service name defined in the tnsnames.ora file. As we have no tnsnames file to provide a net service name we use the method above.If you examine a tns entry in the tnsnames.ora file you should see that it is a fairly straightforward job to build the required string above.

8. Select Shared logon and enter values for User name and Password. In this step, you provide the user/schema name and password that you require to access the physical tables/views/synonyms.

9. Click OK in the Connection Pool dialog.

10. Click OK in the Database dialog.

OBIEE RPD Repository Report

Hidden away in a dusty corner of the OBIEE Admin tool is the Repository report.

This is pretty useful for a knowledge transfer. Click on tools -> utilities and then select "Repository Report".

This will give you a csv file detailing the information from presentation layer through the BMM and then the physical layer.

Columns are

Subject Area,
Presentation Table,
Presentation Column,
Description - Presentation Column,
Business Model,
Derived logical table,
Derived logical column,
Description - Derived Logical Column,
Expression,
Logical Table,
Logical Column,
Description - Logical Column,
Logical Table Source,
Expression,
Initialization Block,
Variable,
Database,
Physical Catalog,
Physical Schema,
Physical Table,
Alias,
Physical Column,
Description - Physical Column

I am currently experimenting with exporting this data and including it in management dashboards, tying it in with the catalog and usage tracking.

Only Measure columns in the Facts

OBIEE Repository Best Practice

Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned

When executing a query that includes columns which are not measures (defined with aggregate) and generates a GROUP BY or ORDER BY, this will cause the Oracle BI Server to generate an error when the data returned is not at the lowest level of granularity (such as dimensional data queried on the fact source).

Recommendation

Generally, dimensional data is placed on dimension tables to prevent the fact table from increasing in size, and to ensure that the queries executed on the fact tables can roll up the data appropriately.

It is important to keep in mind that the fact table will contain a large number of records compared to the dimension tables.

Therefore storing dimension data on these tables will result in the tables being increased in size unnecessarily, thus potentially impacting the performance of queries executed against it. It could potentially lead to the grain of the fact data to change if/when further dimensional data is added at a future stage.

Note however, there are cases where it is more efficient to place the dimensional data on the fact tables where the data remains the same grain regardless of the queries performed, an example of this is the Activity fact table in the standard DW schema, which has data at the lowest grain, and by extracting the dimensional data out into a separate dimension table would not reduce the dataset held in the dimension table in comparison to the fact table.

If the reasons for placing the data on the fact table is because the data elements are unique to each record in the fact source (transactional data), such as order number. The Oracle BI RPD logical model should be built in a specific way to ensure that no potential errors occur. The following provides details on how this should be configured:

In this particular case, if there is no requirement to define an aggregate for this column, then the columns should be configured to be used as dimensional data and not fact data. This can be achieved by configuring the base physical fact table as a logical table source to both a fact and dimension logical table, whereby the fact table consists of all the aggregated columns and the dimension table will contain all other columns.

Explaining OBIEE to Your Boss!

To explain all the details behind OBIEE would take a long time, probably bore a non-technical person half-way to death, yet still not provide them with any details they can conceptually grasp.

There is tons of documentation - the problem is wading through it all, and all your boss wants is-

"OBIEE in a Nutshell".

The OBIEE server exposes to an end user their business focussed measures and dimensions that can be used to assemble analyses of interest.

Assuming that your boss wants to find out how many cars were sold last month and whether they were new or used would assembling a theoretical query of:

"Calendar.Month", "Car.Condition", "Sales.UnitsSold"

with a filter of last month on "Calendar.Month"

The OBIEE server will now organise how the raw data sources are to be queried. It generates one, or more, queries against the data source(s). retreives the results, applies some post processing, and presents back to the user a result set that can be used to generate not only tables, but graphs, pivot tables and maps.

The OBIEE server has modelled physical raw data, applied calculations and transformations, all interwoven with a consistent security model, ensuring data access is seamless and controlled.

In discoverer or reports and to some extent in publisher you design a query. In OBIEE we no longer hand-craft individual queries, rather we model relationships and the ineractions between heirarchical behaviours. This allows for any possible random query entered by a user in the presentation layer to be transformed from a logical to a physical query, transparent to the user, and the results returned and displayed in the manner requested.

Adding a Excel spreadsheet as a datasource in OBIEE

OBIEE can combine Excel spreadsheets as sources of data into your BI solution.

Here's a quick run-through of the stepsyou should take to include the spreadsheet.



1. Open the excel file to the sheet in the workbook you want to use as a datasource.

2. Ensure you have no filters set.

3. Highlight the range of rows and columns you wish to use, including the column headings. The column headings will become the column names. Right click and give your range selection a name or you can go to Formulas-->Define Name and give the scope and range.

4. Each spreadsheet can have multiple named ranges. Each named range will become a "table".

5. Now you need to create a System DSN for the Excel data source. Open the Data Sources (ODBC) from the Control Panel or Administrative Tools menu.  Select the Microsoft Excel Driver from the list of drivers.

6. Give the DSN a name and browse to the spreadsheet with the named range and click on Ok.

7. Open the OBIEE Administraton Tool. For OBIEE 10g go to Administration tools and click on File>Import from Database. Select the DSN which you created for Excel. For OBIEE 11g select File>Import Metadata - the DSN should be in the list of DSN data sources available, select the DSN and continue to import as normal through the wizard.

8. When you click on Import, it will import all the tables present in that excel file.

9. Once you get the tables in physical layer, you can play around with those tables to create subject area.