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

Renaming Objects in the Business Layer

Rename Wizard

If you need to rename of your business layer objects in the repository, try using the Rename Wizard, found under Tools > Utilities > Rename Wizard. You can also right click objects in the Business Model and Mapping or Presentation layer and Select the rename option from the context menu.

Actually the first line should start WHEN.

You will be able to quickly and globally replace underscores with spaces, database names with business names, change from all caps to first letter capitalized only, and perform many other useful Find and Replace operations. 

The Wizard allows you to create an ordered sequence of operations, and select which repository objects to include in the processing. Be aware though, if you include objects from the presentation layer, the original object names will be preserved as aliases once you have renamed them using the Wizard.

You will be asked to approve all changes before they are applied.



See more in the Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition -- Chapter 18

OBIEE reports performance

OBIEE Reports performance

I am assuming you are using a data warehouse (DWH) environment for the reports, rather than a live OLTP.

Now in normal circumstances, Bitmap indexes are best suited for DWH because there is huge amounts of data to handle.
Bitmap indexes work best where the change in data (transactions) is slow. Bitmap indexes consume very little space too, compared to the common b-tree index. And with tables containing millions, billions, or more rows of data, this becomes an important aspect.
A common assumption also says that bitmap works best with columns having lesser distinct values or cardinality. But it is best to look for the amount of data updated from concurrent systems. If that's low, always use bitmap.

Remember that we talk about indexes when we mean performance tuning of the database or DWH.

Best practices include - 

Using star schema model wherever possible. Snow-flaking affects performance. So if you can convert a snow flake to star by using some alias tables or multiple LTS, go for it.

Using aggregate tables wherever possible. You probably won't be able to create aggregate tables for every possible combination of levels in all the heirarchies, but if you check your usage statistics you'll see which are the most common (or longest running) queries and you can plan accordingly.

Avoid excessive use of functions like cast, nvl, lpad, rpad, trim etc. or push them to the ETL phase. Functions may kill the indexes and remember that every foreign key of your tables would also be acting as an index in the star or snowflake schema. Actually unless you build function based indexes, assume that functions in join conditions will destroy performance.

Avoiding unnecessary calculations or again push them to the ETL phase if possible. Get a proper DWH built by the ETL team, in accordance to the reporting requirements.

Using filter functions instead of case when statements. Filter functions include an internal where clause which helps them to use less data for comparison and hence perform better.

Using Union All instead of Union wherever possible.

Using materialized views (for query rewrite) when some complex queries are needed. Assuming that your database is setup to allow query rewrite, materialized views can significantly improve performance, without rework in the repository or front-end. Remember as always, that this is a band-aid solution and at some stage you will need to migrate your repository to use the materialized view, and materialized views bring their own complications and administration.

Some of the NQSConfig.ini parameters may also be altered for a better tuning.

And always check the NQQuery.log file to see the final query that is being executed for a report. That may also tell you what alterations might be needed for a better performance.

In some cases, you are probably looking to run a few reports faster which are taking some overly long time to open. So the best/quickest suggestion would be to use a proper cache mechanism. There are various ways in which you can manage the cache for your reports to run faster. The easiest way to pre-fill the cache with the report would be to have an agent 'run' the report into the cache, thus seeding the cache for the following users.

Modelling Many-to-Many relationships:

The following provides a summary of some design options, which the development team should review and check whether any of the snowflaked models are mapped as a many to many relationships and can be re-design using one of these approaches:

Technique #1: Select a Primary

Although not a technical solution, the best way to solve the M:M problem is to eliminate it.  By selecting one of the many dimensional records that are associated with a fact, the entire problem can be avoided.  In the OLTP, Primaries are used throughout the model, which are carried over and used in the Oracle BI model.  If it is at all possible to identify a primary, and the use of the primary is acceptable to the user community, then it is recommended to use this technique.

Technique #2: Direct Modeling into the Dimension

A straightforward technique is where the table that serves as the intersection table is modeled into a lower level in the Dimension.  The specifics of this technique are similar to those outlined in Solution B of the No direct physical link between a base Dimension and a Fact table section above.

Note that over-counting will occur when performing the many-to-many join.

Technique #3a: Use of a Bridge Table

Instead of modeling the relationship table into a new lower level in the dimension as in Technique #2, the relationship table can become a separate logical table that servers as the Bridge between the dimension and the facts.  Create a new Logical table with the M:M relationship table as the source, mark the logical table as a Bridge table, and adjust the Business model to show the relationship of Facts:Bridge as 1:M and Bridge:Dimension as M:1.  The indication that the Logical Table is a Bridge table is merely an indicator to Analytics that the table is not a Fact table, which it assumes to be any lowest-level table in the data model.

Note that over-counting will occur when performing the many-to-many join

Technique #3b: Use a Weighted Bridge Table

Similar to Technique #3a, this technique is the classic Kimball approach, where the Bridge table employs weighting factors to prorate a total value over multiple records.  For example, if there is one Opportunity worth $1,000,000 and there are two Employees associated with it, the bridge table might contain a record for each with a weighting factor of 0.5.  In this way, each employee will be associated with 0.5 of the whole amount of $1,000,000, or $500,000.  If it is determined that Employee A should receive 75% of the credit, then the weighting factors would be stored as 0.75 and 0.25, which would give Employee A 75 of the total or $750,000.

It is important to note that the weighting factors must all add up to 1 (One), as they are effectively percentages of a whole.  Additional ETL effort will be required to complete this solution.

This technique eliminates over-counting, but may be difficult to implement if users are not comfortable prorating a value over several records.

Technique #4: Use Level Based Measures

As an enhancement to Techniques 2 and 3, the use of level based measures can help prevent the over counting problem associated with each.  When a metric or measure is explicitly bound to a specific level in a dimension, it is indicating that the metric will be viewed at that level. If the metrics in a fact table are to be viewed by a Dimension with which it has an M:M relationship, those metrics can be set to a level in the dimension, thereby forcing that the records be broken out across that dimension.  By forcing a breakout of rows (one fact row for each dimensional row), aggregation is prevented, and therefore over counting will not occur.

As an example, suppose there is an M:M between Employee and Fact_Opty_Revenue.  The data in the tables indicate that Tom, Larry and Bill are all linked to an Opportunity worth $9 million.  The user makes a report that asks for the Opportunity Type and the total Potential Opportunity Revenue.  Without level setting the metrics on the fact table, a report that does not include the employee dimension will over count, as each of the three dim records will be brought into the query and aggregated into one:

Opportunity Type	Potential Opportunity Revenue
Software Sales	$27,000,000

By level setting the Revenue metrics to the Employee level in the Employee Dimension, this same report will return the following:

Opportunity Type	Potential Opportunity Revenue
Software Sales	$9,000,000
Software Sales	$9,000,000
Software Sales	$9,000,000

Although not intuitively obvious as to the cause of the breakout to the end user, the over counting scenario is prevented.  When the user adds the Employee to the report, the breakout becomes clearer:

Opportunity Type	Employee	Potential Opportunity Revenue
Software Sales	Larry	$9,000,000
Software Sales	Tom	$9,000,000
Software Sales	Bill	$9,000,000

Technique #5: Lower the Fact Table

The most complicated and involved solution is to lower the level of the fact table, and create a 1:M between the Dimensions and the Facts.  This involves a business rule to split up the metrics and spread them over all possible dimensional records.  In the example above, the simplest spread would be to assign Larry, Tom and Bill each 1/3 of the total amount of $9,000,000, or $3,000,000.  Thus, a report that does not break out by employee still totals to the correct $9,000,000.  Note that this would require three records in the fact table instead of one, hence the concept of lowering the level of detail in the fact.

When modeling M:M bridge tables, ensure that the requirements for reporting against the measures are reviewed.

Also refer to “Siebel Analytics Repository Design Best Practices [ID 477431.1]” on Oracle My Oracle Support which provides details on modelling snowflake relationships in OBIEE.

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.

Lack of Data Security Filters

Area of Impact is Data Integrity and Performance

Data Security Filter Consequence

A lack of data visibility filters will enable all users who have access to specific subject areas to have access to all records, regardless of any visibility restrictions which may be implemented within the source applications. It can potentially impact the overall performance of such queries, as the user may return a larger dataset than what they are permitted in seeing.

What should be done :
The following provides a high level summary of the functionality provided by data security filters:

When a user logs in, the following two processes should take place:
Authentication
• Verifies the user has the right to log in to the system
Authorization
• The process of defining which “Application Roles” should be assigned to the user once they have successfully authenticated

Once the above two processes have taken place, there are three OBIEE components that apply “security policies” based up the user’s Application Roles:
• Presentation Catalogue > Dashboard/report access and front-end privileges
• BI Repository > RPD metadata access and data-visibility
• FMW Policy Store > Access to BI Server (e.g. impersonation) and BI Publisher features

The default Roles has the following privileges in Oracle BI EE 11g:
BIAdministrator

• Manage BI Repository (RPD)
• Administer BI Publisher

BIAuthor

• Privileges defined in BI Presentation Catalog
• BI Publisher Develop / Design / Schedule Reports

BIConsumer

• Privileges defined in BI Presentation Catalog
• BI Publisher Excel / On-line Report Analyzer

Data filters can then be set within the OBIEE RPD via the Application Roles and Users through the “Manage > Identity” menu option.

The development team should use the above details provided and check whether similar security can be defined for the existing sources which are loaded into the data warehouse.

Further details on this topic can also be found in the following sections:
• Oracle® Fusion Middleware > Security Guide for Oracle Business Intelligence Enterprise Edition > 11g Release 1 (11.1.1)