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.

Guided Navigation

Guided Navigation:

is a way of making OBIEE dashboards and reports more dynamic, personal and relevant to the current user viewing the dashboard.

For example - specific regions of the dashboard can be made to 'only' appear when there is important information to report.

To do this you create a condition based on either an Analysis or KPI, where the result is boolean (true/false).
i.e. a count > 20 or no rows returned.

The conditions can be used to determine:

• If agents should run and /or to who they should deliver their contents.
• If action links are displayed
• If reports are shown

For example if the sales department has a target to meet, an anlysis could be built to only show a report if sales targets are not met and also send out an email report via the agent showing underperforming sales. If targets are met, then no email is sent, or a 'nice' email is sent out.

There are two types of condition - Named and Inline.

Inline Conditions:

If you only ever intend to use a condition once - and this condition is only for dashboard reporting, you can create an inline condition. This is created within the dashboard page when you build and design the layout and is saved as part of the page.

Named Conditions:

A Named Condition is created from the New dropdown or from the create --> more panel on the home page. It is as its name suggests, named, and therefore saved. This means that the same condition can be used in many places to guide action links, raise alerts or expose parts of the dashboard.

A judicious use of guided navigation using conditions can improve the entire BI experience for your users, but as always, too much of a good thing will confuse rather than clarify the story you tell using the dashboard as your canvas.

ODBC or OCI?

How should you set your connection pool - OCI or ODBC?

When possible, configure your connection pools to use a “native driver” to connect to your physical databases.  For example, use OCI for connecting to an Oracle database rather than ODBC

The native drivers understand the database better than ODBC. ODBC is the lowest common denominator and uses "simple" statements to select data and then passes the load to the business intelligence server for aggregation and transformation.

The native drivers allow the load to passed from the application server to the database, and the database then returns only the result required.

A simple example would be to ask for a sales total.

Using ODBC

In ODBC the query pushed to the database would be to select the relevant sales. The sales then are returned to the BI server and the total is calculated and sent on to the dashboard. YOu may even find that all the sales records are returned to the BI server, then filtered for the relevant records, before any aggregation takes place.

Whereas:

Using OCI

Using a native driver helps the OBI server understand what the database, where the data resides, is capable of. Just how much complexity can be pushed down and passed to the database, relieving the load on the OBI server.

You will find filters, aggregations and complex calculations appearing in the physical queries being passed down with the native drivers (eg. OCI for Oracle). I have examined some queries in the process of 'debugging' various parts of the repository and been surprised with new ways and functionality in SQL that the OBIEE server passes down. I have been writing SQL queries for a good many years and OBIEE still finds ways to expand my knowledge.

No more Complex Joins please!

Avoid Physical Layer Complex Joins.

While it is perfectly acceptable to use complex joins in the physical layer, this can (and usually does) result in poor query performance when OBIEE generates the query against the database. Far better to use the foreign key joins that should be implicit in the star/snowflakes of your data model.

For example where the complex join involves a between condition, then you are looking at index range scans on the database, repeat this several million times when you need an average on a large dataset, and you are potentially going to be looking at a spinning clock symbol for a long time.

Any work that has to be done by the BI server costs time. Much as you tune a query in the database to avoid making function calls in the 'WHERE' clause, so you should aim to do the same in your physical joins. - No more BICOLUMN = TRUNC(DATECOLUIMN) or BICOLUMN = SUBSTR(CHARCOLUMN,1,4).

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)

Set a Dashboard Landing Page for all Users

If you want all your default and new users to go to a specific page when they login, and they have not set a specific page or their 'home' page is set to default then they can be directed via the PORTALPATH setting.

PORTALPATH

The PORTALPATH setting will only work if the user has not set a new default home page in their account settings.

You will need to make a few changes in the repository to enable this setting.

Open the repository and go to manage --> variables

Navigate the tree displayed and open up Session and then Variables and then System
You will create a new Session Variable here

Right click on the right hand side and select the new PORTALPATH or create a new variable PORTALPATH if no option is presented.

In the Default Initialiser - give the path to the required dashboard. eg /shared/samplesales/_portal/landingpage

Close the repository and those users without a default page in their account setting will instead land on the new landing page.

Patching support for OBIEE 10.1.3.4.1 ends on 30th September

Patching support for OBIEE 10.1.3.4.1 ends on 30th September 2012.

After September, there will be no more one-off patches available for OBIEE 10.1.3.4.1 or lower versions. If you wish to continue receiving one-off patches, should the situation arise, please apply the OBIEE 10.1.3.4.2 patch set.