Grand Total Issue in OBIEE

OBIEE Grand Totals with Calculated Columns (report)

This is a bit of a specific problem, but I could see it being something that those new to OBIEE could run into. It deals with incorrect Grand Total values being displayed for a Calculated Column when using report filters.
Consider the following report:

In this report, Amount A and Amount B are standard columns in a database table, and Variance is a calculated column in the repository. The calculation for Variance is 100 * (Amount B - Amount A) / Amount A. So, going by the Grand Total amounts in the report above, 100 * (696 - 550) / 550 = 26.55.
Suppose you want to filter the results of this request so that it only shows items having a Variance greater than 30:

Note the Grand Total for Amount A and Amount B are summed correctly for the filter, but the Variance still shows 26.55. Obviously, this is incorrect, so what’s going on here? The filtered amount should be reported as 100 * (220 - 130) / 130 = 69.23.
If you want to show the correct variance for the filtered Grand Total amounts, you need to enable an option in your instanceconfig.xml file. Open the file $OracleBIData/web/config/instanceconfig.xml, and add the following line somewhere inside your <ServerInstance> and </ServerInstance> section:

<ReportAggregateEnabled>true</ReportAggregateEnabled>

Make sure you don’t accidentally put this inside an element nested inside <ServerInstance>, or it won’t work.

Save instanceconfig.xml, and restart your BI Server and Presentation service.
Now, the correct amount should be shown for the filtered Grand Total variance:

This was previously a bug in OBIEE that was addressed by adding the ReportAggregateEnabled option. It’s not really documented anywhere other than on Metalink, so hopefully this will be helpful to someone.

OPMN startup issues : OBIEE 11g

When you see below error message while starting up opmn services 

ONS server initiated

 [opmn] [TRACE:1] [522] [pm-internal] PM state directory exists: /u01/app/middleware/instances/instance1/config/OPMN/opmn/states

 [opmn] [NOTIFICATION:1] [675] [pm-internal] OPMN server ready. Request handling enabled

[opmn] [ERROR:1] [] [libopmncustom] Process Ping Failed: 

1) Delete states directory in 

     /u01/app/middleware/instances/instance1/config/OPMN/opmn/

2) ' no address matched properties for the request' error message 

 Process Ping Failed: coreapplication_obisch1~OracleBISchedulerComponent~coreapplication_obisch1~1 (467413041:1854) [No addresses matched the properties for the request]

 [opmn] [ERROR:1] [] [libopmncustom] Process Ping Failed: 

Open opmn.xml (ORACLE_HOME/opmn/conf/opmn.xml) and look the ports which are already using kill the pid's and start opmn services 

ps -ef|grep 'opmn -d'|grep -v 'grep'

kill -9 <pid>

3)  Unknown error 1074200016
RPC response timeout
time out while waiting for a managed process to stop

Take a backup of the  

/u01/app/middleware/instances/instance1/config/OPMN/opmn/opmn/conf/opmn.xml file.

-  Edit the opmn.xml

and increase timeout start/ stop parameter in seconds

<start timeout="1200" retry="2"/>

<stop timeout="240"/>

<restart timeout="920" retry="2"/>

Oracle recommends that you shutdown OPMN before shutting down your computer. Use the opmnctl stopall command to stop OPMN and OPMN-managed processes.

On the Microsoft Windows operating system, you can use the Windows services control panel to stop OPMN and OPMN-managed processes.

Types of Views in OBIEE

Types of Views in OBIEE

View Name	Description
Compound Layout	Use the compound layout view to assemble different views for display on a dashboard. On the Criteria tab, you can click the following button to access the compound layout view.
Title	Use the title view to add a title, a subtitle, a logo, a link to a custom online help page, and timestamps to the results.
Table	Use the table view to show results in a standard table. Users can navigate and drill down in the results. You can add totals, customize headings, and change the formula or aggregation rule for a column. You can also control the appearance of a column and its contents, and specify formatting to apply only if the contents of the column meet certain conditions. On the Criteria tab, you can click the following button to access the table view.
Chart	Use the chart view to drag and drop columns to a layout chart. You can customize the title, legend location, axis titles, and data labels. You can customize the size and scale of the chart, and control colors using a style sheet. Oracle BI Answers supports a variety of standard chart types, including bar charts, column charts, line charts, area charts, pie charts, and scatter charts. Custom chart subtypes include two-and-three-dimensional, absolute, clustered, stacked, combination, and custom. On the Criteria tab, you can click the following button to access the chart view.
Pivot Table	Use the pivot table view to take row, column, and section headings and swap them around to obtain different perspectives. You can drag and drop headings to pivot results, preview them, and apply the settings. Users can navigate through pivot tables and drill down into information. Users can create complex pivot tables that show aggregate and nonrelated totals next to the pivoted data, allowing for flexible analysis. For an interactive result set, elements can be placed in pages, allowing users to choose elements. On the Criteria tab, you can click the following button to access the pivot table view.
Gauge	Use the gauge view to show results as gauges, such as dial, bar, and bulb-style gauges.
Filters	Use the filters view to show the filters in effect for a request. Filters allow you to constrain a request to obtain results that answer a particular question.
Column Selector	Use the column selector view to permit users to dynamically change which columns appear in results. This allows users to analyze data along several dimensions. By changing the facts, users can dynamically alter the content of the results.
View Selector	Use the View Selector view to select a specific view of the results from among the saved views. When placed on a dashboard, the view selector appears as a drop-down list from which users can make a selection.
Legend	Use the Legend view to document the meaning of special formatting used in results, such as the meaning of custom colors applied to gauges.
Funnel Chart	Use the funnel chart view to show a three-dimensional chart that represents target and actual values using volume, level, and color. It is useful for depicting target values that decline over time, such as a sales pipeline.
Narrative	Use the narrative view to show the results as one or more paragraphs of text. You can type in a sentence with placeholders for each column in the results, and specify how rows should be separated.
Ticker	Use the ticker view to show the results of the request as a ticker or marquee, similar in style to the stock tickers that run across many financial and news sites on the Internet. You can control what information is presented and how it scrolls across the page.
Static Text	Use the static text view to include static text in the results. You can use HTML to add banners, tickers, ActiveX objects, Java applets, links, instructions, descriptions, graphics, and so on, in the results.
No Results	The no results view allows you to specify explanatory text to appear if the request does not return any results.
Logical SQL	Use the logical SQL view to show the SQL generated for the request. This view is useful for trainers and Oracle BI administrators, and is usually not included in results for typical users. You cannot modify this view, except to delete it.
Create Segment	The create segment view is for users of the Oracle's Siebel Marketing Version 7.7 (or higher) operational application. Use it to display a Create Segment link in the results. Users can click this link to create a segment in their Oracle Siebel Marketing operational application, based on the results data.
Create Target List	The create target list view is for users of Oracle's Siebel Life Sciences operational application integrated with Oracle's Siebel Life Sciences Analytics applications. Use it to create a Create Target List link in the results. Users can click this link to create a target list, based on the results data, in their Oracle Siebel operational application.

Aggregate Persistance Wizard in OBIEE 11g

This exercise must be done on rpd’s which are deployed with the BI server is running

 Aggregate persistence is used to automate the creation and initial population of aggregate tables, persist them in backend datawarehouse schema and configures the rpd file to use them wherever its appropriate.  This is best way to deal with performance issue in datawarehouse tables for which intervention of ETL professionals is not needed.

Here are the steps to create an aggregate where a frequent query is fired for SALE_PRICE,MONTH and MODEL

Open rpd in online mode and and open the Utilities, choose “Aggregate Persistence”

Provide a name and location for the script file to be created

Select the metrics required to be persisted

Select the dimension level of the attributes that needs to be persisted. Check the use of “Surrogate Keys”

Select the Database, the schema and the connection pool and then specify the name of the aggregate table .

The created script now appears and simultaneously the script file is also created.

Once and check the script contents

Navigate to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin

Check that nqcmd.exe is present. This is a utility from OBIEE to execute scripts.

Cd to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin in a command window.

Issue the command nqcmd –d coreapplication_ON863956460 –u admin –p mypassword123 –s c:\agg_persistence.sql

Note that the entry after –d switch is the name of the OBIEE DSN. (to checn this Start>run>type odbcad32)

-u is the administrator username

-p is the administrator password

-s is script path

 Execute this command.

Once script executes, the command window displays the same as Processed. Note, any errors in this stage is because of 2 reasons :

·         BI Server is not running (opmnctl startall should be done) OR

·         The script is taken from an rpd that’s not deployed (take script from a rpd in online mode)

Once the script completes the changes done by it are visible.

·         New  tables are created in physical layer

·         Same are created in Database as well

·         New Logical table sources are created in BMM and mapped to respective columns

We can verify that column mapping to new LTS is done by opening the LTS’s created by the aggregate persistence wizard.

Notice that each of the objects created by aggregate persistence wizard in the rpd has a small “GEAR” symbol.

Calculation Wizard in OBIEE 11g

Create a Calculation Measure Using the Calculation Wizard

Create a Calculation Measure Using the Calculation Wizard

In this topic, you use the Calculation Wizard to define a new calculation measure named Share of Category in the Sales Facts logical table.

To create a calculation measure using the Calculation Wizard, perform the following steps:

1.	Return to the SH repository, which should still be open in online mode in the Administration Tool.
2.	Right-click the Amount Sold logical column and select Duplicate.
3.	A new column named Amount Sold#1 is added to the business model.
4.	Rename Amount Sold#1 to Category Sales.
5.	Double-click Category Sales to open the Logical Column dialog box.
6.	Click the Levels tab and select Category as the logical level for ProductsDim. Category Sales is now a level-based measure that will calculate total sales at the category level when used in a query. Level-based measures are useful for creating share measures. You use the Calculation Wizard to create a share measure in the steps that follow.
7.	Click OK to close the Logical Column dialog box.
8.	Right-click Amount Sold and select Calculation Wizard. The Calculation Wizard opens.
9.	Click Next.
10.	Check Category Sales as the column to compare with Amount Sold.
11.	Click Next.
12.	Uncheck Change and Percent Change and check Percent.
13.	Change Calculation Name to Share of Category.
14.	Click Next.
15.	Click Finish. Share of Category is added to the business model.
16.	Drag Category Sales and Share of Category to Sales Facts in the Presentation layer.
17.	Check in changes.
18.	Click Yes when prompted to check global consistency. If the repository is consistent (no Error messages), close the Consistency Check Manager. If there are Error messages, you must correct the errors before continuing.
19.	Save the repository.
20.	Return to Answers, which should still be open from the previous topic.
21.	Click Reload Server Metadata.
22.	Expand Sales Facts and verify that Category Sales and Share of Category are now visible in Answers.
23.	Place the cursor over the icon to display the screenshot and use it as a reference to create a query. Notice the sort settings for the Prod Category and Amount Sold columns.
24.	Expand the Times folder.
25.	Hold down the Ctrl key and click the Calendar Year column to open the Create/Edit Filter dialog box.
26.	Click the All Choices link.
27.	Click 2001 to add it to the Value field.
28.	Click OK to close the Create/Edit Filter dialog box. The filter is added to the request.
29.	Click Results. Only partial results are shown in the picture.
30.	Leave Answers open.

Look Up Tables in OBIEE 11g

Lookup table is a new feature in obiee 11g.
This is used mainly to:
• Resolve code columns and get description/name values from a lookup table.
• It can also be used for checking the currency conversion values from a daily updated source.


Lets try to see how this works.Assume that our customer table XW_CUSTOMER_D, has a column cust_state which is just the state code and not the state name:

We also have a table called STATE in the database which contains the state code and state names:

If we had a customer dimension and want to show the state name, instead of state code we need to create a lookup based on the STATE table.
Step-1
Import the STATE table into the physical layer of OBIEE and make an alias out of it.

Step-2
Define the Key column for the STATE table in the physical layer.

Step-3
Pull the lookup table to the BMM layer. You 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.


Step-4
Double click on Lkup_State_Name 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.

Step-5
Define 2 new logical columns in the Dimn_Customers_D logical dimension table by right clicking on the lodical table name as follows :
These 2 columns are “Derived from existing columns using an expression”
1-Dense Looked Up State Name
Formula:
Lookup(DENSE "Sales"."Lkup_State_Name"."STATE_NAME",-> Lookup Value
"Sales"."Dimn_Customer_D"."Customer State"-> Lookup column
)

2-Sparse Looked Up State Name
Formula:
Lookup(SPARSE "Sales"."Lkup_State_Name"."STATE_NAME", -> Lookup Value
‘Lookup State Name not Found', ->String used when code not found(remove)
"Sales"."Dimn_Customer_D"."Customer State" -> Lookup column
)


We now have the 2 lookup column in place in the customer dimension as follows :

Step -6
Pull the new columns to the presentation layer into the customer dimension display folder and save your work.

---

Once done we create a report with the “Customer State” and “Sparse Looked up State Name”. The result will be as follows. Notice that code ‘UT1’ doesn’t have a corresponding lookup value in STATE lookup table and we see the string “Lookup State Name not Found”

This shows that “SPARSE” lookup causes an outer join between the customer and the lookup table.
The OBIEE server fires 2 SQL’s as follows and joins the results in the reporting layer.
SAWITH0 AS (select distinct T13265.CUST_STATE as c1
from
XW_CUSTOMER_D T13265 /* Dimn_CUSTOMER_D */ )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
And
select T42371.STATE_NAME as c1,
T42371.ID as c2
from
STATE T42371 /* Lkup_State_Name */

Next we create a report with the “Dense Looked UP State Name” and we see the following result.

This shows that DENSE lookup executes an inner join between the customer and lookup table
We had a value ‘UT1’ which was not brought forward due to the inner join by the dense lookup, since that value was not present in STATE lookup table.
Moral :
Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup=Outer join (with Nulls having a custom name) Dimension and lookup table

Creating an Implicit Fact Column in OBIEE

Setting or Creating an Implicit Fact Column in OBIEE

To set an implicit fact column follow the steps and screenshot

• Create a Dummy Fact column in a Physical Layer called Implicit Fact Column and map that column to any number of your choice

Creating Implicit Fact Column

• Join all Dimensions to this Fact column in BMM Layer
• In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
• In the general tab, find the Implicit Fact Column section.

Selecting Implicit Fact Column from Presentation Catalog

• Click on Set button, this will open the browse window
• Select a fact column from the fact tables available in the presentation catalog.
• Click OK.
• Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
• Click OK.
• Save your changes in the repository.

How-to: Mapviewer Integration with OBIEE 11g (11.1.1.6 and higher)

One of the biggest new features in BI EE 11g is the ability to create native mapviewer maps and then overlay reporting components/visualizations on top of maps. For example if you are doing a country based sales analysis, its easy to color code each country across the world right within the maps. It is also possible to overlay pie charts, bar charts in the maps thereby providing an extensive visual analysis capability. In 10g, one had to go through some complex java scripting to achieve BI EE and mapviewer integration. I have blogged about it here and here. 11g makes this entire integration a lot easier.

Lets try to understand the integration through a simple example. Shown below is a report of Sales performance of 3 US states across 4 years.

We would like to depict this using a map as shown below.

As you see we have 2 visualizations applied on the same map. The first one is a different color coding for each of the 3 states depending on the sales performance. The other is a pivot representation of yearly performance for each state, all within a single map.

To do this we start with enabling the Mapviewer integration by updating the instanceconfig.xml to include the following tags (in each presentation server)

<SpatialMaps>
<ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
<RemoteOracleMapViewerAbsoluteURL></RemoteOracleMapViewerAbsoluteURL> <LayerDataLayout>
<MaxRecords>600</MaxRecords>
</LayerDataLayout>
</SpatialMaps>

Then we need to restart the presentation service. This enables the connectivity between BI EE and Mapviewer. After enabling the integration, we then import the standard spatial MVDemo schema into the Oracle Database. This schema contains Layers, Themes and some Base maps for all the major US States, Cities & some inter-state highways. After importing and installing the MVDemo schema, we need to download the Mapbuilder application which will enable us to create a custom map. We will be using this map for rendering in BI EE. The Mapbuilder application can be downloaded from here.

After downloading the Mapbuilder app, we start the application and login to the MVDemo schema. By default this schema contains a lot of built-in themes, Base Maps and Tile Layers.

We start off with creating a simple Base Map called STATE_MAP which will be using 2 out of the box themes THEME_DEMO_STATES and THEME_DEMO_STATES_LINE

This step will enable us to render all the US States and its boundaries within a very simple map as shown below

Once the base map is created, lets create a new tile layer called STATE_TILE_MAP which will use this base map for rendering. We can define all the zoom levels, boundaries etc within the tile layer

Now we have a map that can show all the US states and its boundaries. We need to now expose this map to the mapviewer application (as an admin user through Mapviewer admin screen) by taking it online as shown below

Now the map is available for consumption through BI EE. Remember, the theme that we used above, THEME_DEMO_STATES has an identifier called STATE_ABBR which is basically the key that we need to pass from BI EE. Relationship between BI EE repository columns/subject areas and Mapviewer maps are done from the Manage Map Data screen in the presentation services admin console.

In this screen, we need to import the layers, maps and the images that we want to include as shown below

Edit the THEME_DEMO_STATES theme and map the State column from the repository. There need not be any relation between the column used in spatial and the column mapped in BI EE. All we need to ensure is the attributes should match. In my case, the State abbreviations come from the SH schema and the THEME_DEMO_STATES come from STATES table in the MVDemo schema.

Then navigate to the STATE_TILE_MAP in the background maps and add the THEME_DEMO_STATES again.

This establishes a relationship between the Subject Area and the Map thereby enabling us to add the mapview from answers. After doing this, lets create the report from the SH schema containing just the 3 states for 4 years.

Use the new Map View option to create the map.

This will render the map that we created in mapviewer. If you notice carefully, it will automatically apply some color coding to the 3 states that are part of the report.

The color coding is actually part of default visualization that Map Views apply on a map. Lets change this to something as shown below

Lets now create one more pie chart visualization (for obtaining the yearly breakup of sales in all the 3 states)

Lets use the Year as the column providing the necessary slices for the pie charts

This will give us the necessary visualization using Mapviewer.

Though there are a lot of steps involved, majority of the steps are for setting up the mapviewer. Once we have the maps set up the entire process of adding visualization to maps is quite straight forward.