What is a Bridge Table ?

If you want to connect two tables where there is no relationship, you can use a third table, or bridge table for connecting them. This table will have common columns in both tables, this is used in BMM Layer.

You would mainly use a bridge table to perform a many-to-many join without the bridge table being seen as the fact table. The bridge table is then joined in the Business Modelling Layer and 'tagged' as a bridge type or assignment.

Many-to-many relationships can cause nightmares in the modelling and, producting correct and

How to avoid a bridge table?
There are several approaches:

Hide the many-to-many relationship. You can publish two versions of the schema: the full one for use by structured reporting and a handful of power users, and a version that eliminates the many-to-many relationship for use by more casual users.

Eliminate the many-to-many relationship by collapsing or aggregating multiple rows. Add a row to the many-to-many dimension table: “Multiple rows”. The fact table can then link directly with the dimension. As with all design decisions, the IT organization cannot choose this approach without consulting with the user community. For something which has limited information value, this approach may be quite acceptable.

Identify a single primary row (The top 1) It may be possible to identify a primary row, either based on some logic in the transaction system or by way of business rules.
 
Pivot out the many-to-many dimension (see below the Boolean Column Method). If the domain of the multi-choice space is small, you can eliminate the bridge table by creating a dimension table with one column for each choice.

OBIEE SampleApp 11.1.1.5 available on OTN

SampleApp V107 includes a multiple enhancements since previous 11.1.1.3 public release. Many examples showcase OBIEE 11.1.1.5 new features (Mobile, New types of data sources), and numerous additional OBIEE examples were added (Geospatial, Datamining, Functional examples and more).

Get it here.

Performance Metrics for Management

I was on a trawl to get backing for (yet another) an argument at work.

My argument has always been that everyone should understand how the numbers on a dashboard are derived, and its indicative value, is it good, bad, normal, etc. The viewer should also understand what the trend behind this number is, what makes this number better, or less bad.

Logging Level

What is logging level?
Where can you set logging levels?

You can enable logging level for individual users; you cannot configure a logging level for a group.


Typically logging is set to enable some kind of debug or tuning exercise to take place. There is a slight overhead of writing the logging, so disabling this feature in production is probably a good idea.

Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These levels are designed for use by OBIEE administrators.


Level 0

• No logging.

Level 1

• Logs the SQL statement issued from the client application.
• Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing.
• Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.

Level 2

• Logs everything logged in Level 1.
• Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.

It is possible to set a higher number, but do not do so without the help of Oracle support.

How to Set Logging Level
1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user's user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging Level field

What is the Purpose of an Alias Table?

An Alias table (Alias) is a physical table with the type of Alias. Physical aliases can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these table source types.


Alias Tables can be an important part of designing a physical layer. Here are some of the main reasons to create an alias table:
-To rename physical tables allowing them to be easily identified in the business model layer.
-By renaming you can impose a form of ordering on the tables shown in the physical layer.
-To reuse an existing table more than once in your physical layer (without having to import it several times)
-To set up multiple alias tables, each with different keys, names, or joins
-To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

You need to create aliases to
-Eliminate physical joins that cross dimensions.
-Eliminate circular joins.

More on Dates

Do you have timestamp values????

And do you want to......, well join them to a date????

Cast(timestamp_value as DATE)

Does the same as TRUNC(timestamp_value) in Oracle. It removes the time section and you are left with just the date part.

Works in Prompts as well as prompted columns (fortunately).

Which table is selected at runtime

You have many tables under your logical table in OBIEE. Is there any way to guarantee the order in which they are used when multiple paths through the data are available?

Well there is a way to ensure that given tables are ranked lower in the list for selection.

Priority Group

Found on the General Tab of the Logical table source properties.

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have a lookup table and another very large table where the data is repeated. Both have the same data, just one has many more rows. Often, scanning a large (many million row table) for distinct values is expensive, while access to a lookup table is “cheap.” In this situation, you can assign a higher priority to the lookup to ensure that all queries are fulfilled using the smaller (more efficient) table if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Note that the default value for Priority Group is 0 (zero), and changing this value to 1 (one) makes it lower in priority. Zero is therefore highest priority.