OBIEE 11g – Lookup Tables – Sparse and Dense Lookups

Lookup table are mainly used for,

1. Resolve code columns and get description/name values from a lookup table.
2. Currency conversion.

There are 2 types of lookup tables.

1. Sparse Lookups – A sparse lookup basically means that the main driving table does not necessarily have corresponding lookup values in the lookup table for all the id values. This can be considered to be an equivalent of a Left Outer Join.

2. Dense Lookups – A dense lookup basically means that the main driving table will have matching lookup values in the lookup table for each of its unique id value. This can be considered to be an equivalent of an inner join.

In short, we can summarise all this in one equation,

Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup = Outer join (with Nulls having a custom name) Dimension and lookup table

To illustrate this in more detail,

Consider the following schema, PLAYERS & TEAM_LKP. You can download this schema from this link.

• PLAYERS table contains all the information of players with TEAM_ID being the unique primary key.

• TEAM_LKP has Team information with TEAM_ID being the primary key. 

The main difference between these 2 tables is, not all players in the main PLAYERS table have a corresponding team assigned in TEAM_LKP table.

1. Import those two corresponding table in Physical layer of your repository.

2. It is mandatory to define a table as a Lookup table, you must define a primary key for that particular table.

3. Take the lookup table to the BMM layer. Here, you will notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.

4. Double click on TEAM_LKP (Here in ref, Lookup table) logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

5. Now, in the Business Model and Mapping layer, lets create a two columns called TEAM_DENSE & TEAM_SPARSE. After that one, lets go to the LTS mapping and apply the following the function for each of them resp. Both column should be derived from, "Derived from existing columns using an expression".

Note: If you have more than one column as a primary key, the order of columns used in the key should match with the column order in the Lookup function.

1. TEAM_DENSE:

Formula:  

LOOKUP( DENSE  "Loopkup Example"."TEAM_LKP"."TEAM" , "Loopkup Example"."PLAYERS"."TEAM_ID")

2. TEAM_SPARSE:

Formula:

 LOOKUP( SPARSE  "Loopkup Example"."TEAM_LKP"."TEAM" , 'Team Not Assigned', "Loopkup Example"."PLAYERS"."TEAM_ID")

6. We now have the 2 lookup columns in the PLAYERS dimension as follows:

7. Take the newly created columns to the presentation layer into the customer dimension display folder and save your work. Check consistency of your repository.

8. Create a sample analysis as follows and verify the result.

9. Notice that, you are getting only those players information whose correspondence value are present in Lookup table i.e TEAM_LKP.

By seeing query log, you can get to know that BI server is applying inner join between TEAM_LKP & PLAYERS table. So, we are not getting any records for Mario & Naymar.

10. Next we create a report with the “SPARSE TEAM”.

11. Notice that, you are getting all players information. The result will be as follows. Notice that, 'TEAM_ID' doesn’t have a corresponding lookup value in TEAM_LKP lookup table and we see the string “Team not assigned”.

Happy blogging. :-)

Importing data from Essbase cubes.

While using Oracle Essbase as your data source in OBIEE, there are some additional things that you need to configure before proceeding.

My previous article about "Setting Up Essbase Data Sources" will help to do this.

When you are done with this configuration steps, you are good to go with handling Essbase data source with OBIEE. But there's a problem if you don't know what are default port on which your Essbase data source is installed. If everything is installed on the default ports by your Admin, you are lucky enough; but unfortunately that's not the truth always. If it is not, you might get the following error message,

Essbase Error: 1030818: Login failed. Please check if server and port are correct. If you received timeout or handshake failure, please check if you tried to connect to secure port without secure keyword or clear port with secure keyword.

To get on which port your essbase can be accessible, you can follow the below steps.

• Open you easconsole
• http://Ess_Server:9704/easconsole
• Launch application.
• Put the credentials to connect your Essbase server.
• Expand Essbase servers.
• Select your particular Essbase server where your cubes are residing. (In my case, EssbaseCluster-1)

• Right click and go to Edit > Properties.

• Move to Environment tab.

You can use this AGENT PORT at the time of importing your cubes in OBIEE. Refer the following screenshot,

Happy stay.. :-)

Admintool could not load Essbase driver. Please check the configuration.

Problem:

On Windows systems, when we install Administration Tool from "client-only" installation, it can throw an error while import Essbase data source.

"Admintool could not load Essbase driver. Please check the configuration"

Cause:

The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. You must ensure that the Essbase client libraries are installed on the computer running the Oracle BI Server before you can set up a connection to or import metadata from Essbase data sources. You also need to ensure that the Essbase client libraries are installed on any computer where you want to run the Administration Tool.

After you verify that the Essbase client libraries are installed on the appropriate computers, you must ensure that the PATH environment variable on each computer includes the location of the Essbase client driver (for example, EPM_ORACLE_HOME/products/Essbase/EssbaseClient)

You also need to ensure that an additional environment variable is set appropriately where Admin tool is installed.

Because, other system processes need the information contained in the environment variables set for the Essbase client libraries. The following sections provide additional information:

Solution:

On Windows systems, to enable connectivity, you need to update bi-init.cmd on each computer running the Administration Tool.

To add Essbase environment variables to bi-init.cmd:

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin

Add a line setting the ESSBASEPATH or ARBORPATH environment variable, as appropriate for your version and installed location of the Essbase client libraries. 

For example:

set ORACLE_HOME=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome

set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst

set ORACLE_BI_APPLICATION=coreapplication

set ESSBASEPATH=C:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0

set ARBORPATH=C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

Locate the line that sets the PATH environment variable, and add the location of the Essbase client binaries.

For example:

set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;%ESSBASEPATH%\bin;%ARBORPATH%\bin;%PATH%

Save and close the file.

Now you can open your Admin Tool & can try to import Essbase cubes,

Credits:

Tarun Kumar Swami. :-)

Oracle Data Integrator: Types of Repositories

There are two types of repositories in Oracle Data Integrator:

Master Repository:

This is a data structure, containing information on the topology of the company's IT resources, on security and on version management of projects and data models. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. 

In general, you need only one master repository. However, it may be necessary to create several master repositories in one of the following cases: 

• Project construction over several sites not linked by a high-speed network (off-site development, for example).
• Necessity to clearly separate the interface's operating environments (development, test, production), including on the database containing the master repository. This may be the case if these environments are on several sites.

Work Repository:

This is a data structure containing information on data models, projects, and their use. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. Several work repositories can be created with several master repositories if necessary.

However, a work repository can be linked with only one master repository for version management purposes.

Note:

The standard method for creating repositories is using Repository Creation Utility (RCU). RCU automatically manages storage space as well as repository creation. However, if you want to create the repositories manually, it is possible to manually create and configure the repositories.

References: https://docs.oracle.com

Pass Presentation Variable To the Title View Using Dashboard Prompt?

When you select a value from the dashboard prompt , the title is reflected correctly with the selected value in dashboard prompt.

Create the report:

• Create an analysis using SampleAppLite repository with following criteria:
• Add a Filter ->Product: Brand and make it 'Is prompted' (we will be making Brand as the presentation variable)

• Click on Result and Edit the Title.
  
• In the 'subtitle' field , Enter the name of presentation variable, say @{Brand}
  
• Save the report as 'Presentation var'

Create the Dashboard prompt

• Create a New Dashboard Prompt (New->Dashboard Prompt)
• Add a Column Prompt: Product ->Brand
• Expand the Options and Set a variable to 'Presentation variable' and Enter 'Brand' in the field below.
  
• Save the dashboard prompt as 'PVprompt'

Create the Dashboard Page

• Create a new board and name it as :PVDashboard
• Drag and drop the Dashboard Prompt created from Section 2 into the dashboard page
• Drag and drop the report created from Section 1 to the dashboard page
• Save the Dashboard page and run it.
• When there is no value selected in the dashboard prompt, the subtitle is displayed as '@{Brand}' 
  
• When you select a value for Dashboard prompt : say 'BizTech', it is displayed correctly as BizTech as shown below:
  
• To fix this issue, Do the following:
• Edit the Title view from Results tab and add a default value for All selection for eg, 'All Brand' in this case: @{Brand}{All Brand} and Save the title.
  
• Save the Dashboard PVDashboard,and Run it .Now the Presentation Variable is displayed correctly when you don't select any value for the Dashboard prompt.
  

  

Reference: https://blogs.oracle.com

Happy Stay. :-)

Send Master-Detail Events in OBIEE

Master-detail linking of views:

Master-detail linking of views enables you to establish a relationship between two or more views such that one view, called the master view, drives data changes in one or more other views, called detail views.

For example, suppose you have the following two views:

• A table that shows Dollars by "Per Name Year"
• A graph that shows Dollars by LOB with "Per Name Year" on a section slider

Using the master-detail linking functionality, you can link the two views so that when you click a particular "Per Name Year" in the table, the "Per Name Year" on the section slider of the graph as well as the data in the graph changes to reflect the "Per Name Year" that was clicked on the table. 

For example, clicking 2008 in the table in the "Per Name Year" column, positions the thumb in the section slider on 2008 and updates the data in the graph to the data for 2008.

Note: Master-detail linking is not supported when you click the plot area of a graph.

What Are Master Views actually?

A master view drives data changes in one or more detail views. A view becomes a master when you set up the interaction of a column in the view to send master-detail events on designated channels. This column is known as the master column.

The master column is the column whose values when clicked send a master-detail event, which passes information to update the data in a detail view.

A master view can be in the same analysis as the detail view or in a different analysis. A master view can update data in one or more detail views.

The following types of views can be master views:

• Graph
• Funnel graph
• Gauge Map
• Pivot table
• Table
• Trellis (only the outer edges, not the inner visualizations)

What Are Channels?

A channel links a master view to a detail view. It is the vehicle that carries master-detail events from the master view to the detail view. The same channel must be used for both the master view and the detail view in a master-detail relationship, for example, PassYear. Note that the channel name is case sensitive.

What Are Detail Views?

A detail view is a view that listens for and responds to master-detail events sent by a master view on a specified channel. A view becomes a detail view, when you set up the view to listen to master-detail events.

A detail view includes one or more columns whose values are changed directly by the information passed by a master-detail event. These columns are known as detail columns.

A detail view can:

• listen for master-detail events from multiple master views
• be in the same analysis as the master view or in a different analysis
• not act as a master to another view

Say for example, we'll take above scenario.

• Go to criteria of the above example.
  
• Go to column properties of "Per Name Year"
  
• Move to Interaction tab of the column properties.
  
• In Primary Interaction, select Send Master-Detail Events and Specify Channel name for this master detail event.
  
• Click OK.
• Move to the Results section of the analysis.
• Click an Edit mode of the bar graph view.
• Move "Per Name Year" to Sections and select "Display as Slider".
  
• Now click Edit Graph Properties button of the graph.
  
• Select Master-Detail check box, the Text Box for Event Channels will get enabled.
• Remember the channel we have defined in previous step. i.e. PassYear
  
  
• Click OK.
• Click Done to return to the analysis page.
• Now you can select any of the value for Per Name Year column in this analysis, the corresponding details report i.e. here Bar Graph View will get changed.

Happy stay.. :-)

Dynamic columns in reports / dashboards - OBIEE 11g

I got this requirement from my client, i.e. we can select any of the dimension listed over there and according to that, the report on the dashboard should get changed.

To be more clear, one example I will illustrate on this. The scenario is,

Time, Product,  Offices, etc.  are the dimensions in your repository. On this, there will be three radio buttons having values Year, Product Type, and Office.

There's a report which will be having one table view and bar graph for that. (just for example, it can view)

Now the actual requirement is, when you select any of the value from radio button list, the report's dimension should get changed. 

For example, suppose the default view of the report is "Year" vs. "Revenue".

Now when you select "Product Type" value from the list, the report will get changed as "Product Type " vs. "Revenue".

To achieve this, we can refer the following steps.

• Create one sample report as follows,
  
  
  
• Add bar graph to this analysis.
  
  
• Create one dashboard level prompt.

• Click New to add a new prompt.

• Put the following values as per our example.

• In the display section, you will be having following screenshot.

• Click OK when you finished. Save this prompt.
• Open the previously saved analysis in EDIT mode.
• Go to the Criteria tab.
• Select Column Properties of the "Per Name Year" column.

• Here, you will be setting the presentation variable name as a dimension for the report.

• Save the analysis.
• Now just drag and drop this prompt & report to the dashboard.
• Save the dashboard and click on Run button.

• Here you can see, the default dimension is set to "Time Per Year".

• Now change the dimension value from the list to change the attributes of the reports.

Related links,

Dynamic Column Headers - OBIEE 11g

Double Column Support - OBIEE 11g

Happy stay.. :-)