InCorrect Grand Total in Tabular report

Add this

<ReportAggregateEnabled>true</ReportAggregateEnabled>

somewhere inside your

<ServerInstance> and </ServerInstance>

on instance config and restart the presentation services.

Enable/Disable Cache depending on the user login

A table needs to be maintained which maps the user name with the flag, determining that the cache should be purged on the user's logon or not.

After this, at each logon the Initialization block should run a query referring this table against that user, and sets dynamically a target repository variable.

Cache will be purged whenever the initialization block that populates dynamic repository variable is refreshed, which is at logon(set so).
P.S.: Its important to note here that the cache once purged would be affecting all users, which you would not want to hit performance, until the requirement is to purge the cache through a specific user's logon.

How to send mail to NOT BI users: BI Delivers

http://forums.oracle.com/forums/thread.jspa?threadID=702179&tstart=25

Using impersonate from JDBC driver for BI server

Answer you are looking for. First set a new property and then add the property to your getConnection method call.

Example:

Properties oProps = new Properties();
oProps.setProperty("IMPERSONATE", "USER_TO_IMPERSONATE_GOES_HERE");
oConn = DriverManager.getConnection(url, oProps);

The rest is standard to your jdbc calls.

Write Back Function in OBIEE 11g

In OBIEE 11g You need to make sure that you mark the logical columns in your Repository RPD as "Writeable". Otherwise the ability to write to these columns is not allowed for WriteBack functionality.

How to Hide a table from a Custom Role in OBIEE 11g

- No Access to EveryOne
- No Access to BIConsumer
- No Access to Custom Role1
- Changed from Default to Read access to rest all roles (including custom role 2)

Then the column is not visible to custom role 1 and is visible to custom role 2.

What is the need for going complex join in BMM layer

When ever you build a request in answers BI server first checks in BMM layer to build a query for the request built so you have to have joins right to be able to built query and the main reason is when Complex joins are used in the business layer then they are really acting as placeholders. They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

Simple understanding is physical layer as it is the first layer in RPD as you import tables you need to establish joins between keys and this is a thumb rule to use primary-foreign key relationships.
Secondly you cant do complex join in physical layer cz you dont mention which key to join in complex join,you just establish a join.  You can use foreign-key join in BMM layer depends on the requirement.

If you dont have joins,then import those tables into physical layer and identify a key column or create a key column in those tables and when you go to properties of the column you find last tab as key ...you need to check or enable so it becomes as a key column.That way you can establish joins.

Can we extract the Primary Key of the Table Using Web Services API?

No is the short answer.

The OBIEE SOAP WSDL API (web service) does not provide logical or physical layer metadata information.
It is a presentation services / subject area based API.

The only other "service" that you can use to find this would be to use the bijdbc.jar library which you add to a Java Library / application. From there you can pull back information regarding table metadata, etc.

Depending on your implementation the other option would be to extract the metadata manually from the RPD using several commands possible there.

How can we identify facts and dimensions in physical layer?

A fact table is a table with measures. Measures need to be defined in a logical fact; any column with an aggregation rule is a measure. Oracle BI Server understands that any table in a repository that only has many-to-one (N:1) joins to it is a fact table. The Oracle BI Server uses the logical joins in the business model to identify a fact table.

Dimension tables contain attributes that describe business entities. For example, Customer Name, Region, Address, Country and so on.
Dimension tables also contain primary keys that identify each member.

Dimension tables are expected to store columns that cannot be aggregated whereas fact tables are expected to store columns that can be aggregated.

When and why do we go for complex join in BMM layer?

When Complex joins are used in the business layer then they are really acting as placeholders.
They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

How to display presentation variable in a narrative view

use this synatx @{PRESENTATION_VARIABLE}{'Default'}

Fragmentation Concepts in OBIEE 11g

http://www.obieetalk.com/fragmentation-obiee

Installation of Loopback Adapter for OBIEE 11g in Windows XP OS

You will need to install Loopback adapter to overcome this password error during the 11g installation. Its a pre-requisite for OBIEE 11g to call the IP of the host file.It is defined that way,you can go through the documentation for it.

Instruction to install Loop Back:

To manually install the Microsoft Loopback adapter in Windows XP, follow these steps:
1. Click Start, and then click Control Panel.
2. If you are in Classic view, click Switch to Category View under Control Panel in the left pane.
3. Double-click Printers and Other Hardware, and then click Next.
4. Under See Also in the left pane, click Add Hardware,and then click Next.
5. Click Yes, I have already connected the hardware, and then click Next.
6. At the bottom of the list, click Add a new hardware device, and then click Next.
7. Click Install the hardware that I manually select from a list, and then click Next.
8. Click Network adapters, and then click Next.
9. In the Manufacturer box, click Microsoft.
10. In the Network Adapter box, click Microsoft Loopback Adapter, and then click Next.
11. Click Finish.

After this go into the network setting and look for the loop back network adaptor and configure it with a static IP address, such as (192.168.1.1) as follow:

After the above step, if the box lost network connection to the rest of the actual network. Then go back and re-configure the Physical Network Adaptor to the way it was (such as DHCP). Because of the Loop Back,the LISTENER.ORA must be updated to reflect the new Static IP address assigned above as part of the Loop Back setup.

Date Difference Calculation in OBIEE 11g

For Date Difference........

TimestampDiff(interval, timestamp1, timestamp2)

ex:TimestampDiff(SQL_TSI_DAY, cast('1-apr-2011' as date), current_date)

Where:
interval
The specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY,
SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

Control the Size of Dashboard Prompt EditBox in 10g

There's no way to change that feature in the front end of OBIEE. It seems to default to 10.

However, using Javascripts, one can update the HTML before it's displayed to the user.

The following piece of code can be used to increase the size of the input box:

<script type="text/javascript">
var aElm=document.getElementsByTagName('input');

for (var i =0; i <aElm.length;i++){
if( aElm\[i\].getAttribute('type') == 'text' || aElm\[i\].getAttribute('type') == 'TEXT') {
aElm\[i\].size=100; //change 100 to what you want
} // end if
} // end for
</script>

Put that inside a static text block in your dashboard, check the html checkbox and it should resize your text prompts (edit boxes) to 100. If that's too big, just change the value 100 to what ever suits your needs.

Good luck!

Purging the Entire Caches Through Command line in OBIEE

http://obiee101.blogspot.com/2008/03/obiee-manage-cache-part-1.html

Converting the data in column from Upper case to Camel Case

Two ways to achieve this functionality are listed Below:

UPPER(SUBSTRING(Comment_Text FROM 1 FOR 1)) || upper(substring(Comment_Text.STATUS FROM 2))

or

EVALUATE(‘INITCAP(%1)‘, Table.ColumnName)

See the difference on Fact column between current year and year selected in prompt

If you want to see the differences between current year and year selected in prompt, do this:

1) You need a filter on your year column: BETWEEN YEAR(SYSTEM DATE) AND '@{year}'

This will give you the range of years for your report.

2) In your report you have three columns: Year, Revenue, and Revenue_Diff, where in the fx of Revenue_Diff you have FILTER(REVENUE_COLUMN USING (YEAR_COLUMN = 2011')) - REVENUE_COLUMN

Time out settings for Users in OBIEE 10g and 11g

10g:

For Time to Expire for Idle Connections

add the below code in instanceconfig.xml

To set time to expire for Idle Oracle BI Presentation services Client connections.
<ConnectionExpireMinutes>enter no of minutes</ConnectionExpireMinutes>

Setting the Time to Expire for Oracle BI Presentation Services Client Sessions
<ClientSessionExpireMinutes>enter no of minutes</ClientSessionExpireMinutes>

11g:

This functionality in 11g can be acheived from Enterprise Manager.

How to avoid null values in Pivot table ?

http://total-bi.com/2010/10/replace-nulls-in-obiee-pivot-table/

Using EVALUATE function to display Duplicate results in the report.

If you ever get a requirement to dispay duplicate values for a report that has aggregation on one column, you can use the EVALUATE(‘rownum’ AS INTEGER ) function.  In order to achieve this functionality add a new column, and hide that column ( if needed ).  By doing this you can display the duplicated values as well.  There might be one error that you might occur during this:


State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46030] Field descriptor id 0 is beyond the maximum field count 0. (HY000)

If you get this error uncheck the pref_internal_switch_join feature in the DB features in the physical layer.

Unable to open OBIEE 11g - Catalog Manager ? Its a Known Bug.

If your not able to open Catalog Manager in OBIEE 11g, dont panic, it seems to be a known bug.  In order to fix this issue, open the RUNCAT File, and there is a parameter called SET PATH command in there and it is not quoted.  If there are any spaces in the path, then please remove all the spaces, and put quotes around the SET PATH command and save the file and open the catalog manager again and it should work fine.

Make sure Date Prompts in OBIEE 11g are working across all machines.

• Go to OBIEE > Administration.

• Then double click on Connection Pool, and click on ''Connection Scripts'' tab.

• Make new script, and enter:

• alter session set NLS_DATE_FORMAT= 'Format of date that you want'

Source: http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31bi-354140.html

TECHNOLOGY: Business Intelligence

As Published In May/June 2011

 

Putting Business Intelligence on the Map

By Mark Rittman
Map geographies and business information together to see results.
Most business intelligence applications include an element of spatial, or geographic, data—with sales and performance data often analyzed in terms of dimensions such as customer location, geography, and sales territory. Oracle Database, Enterprise Edition 11g Release 2, with the included Oracle Locator feature and the Oracle Spatial database option, can store spatial data such as maps and points of interest along with your data for analysis, and the Oracle Fusion Middleware MapViewer feature can render this spatial data in the form of Web-based interactive maps.
Oracle Business Intelligence Enterprise Edition 11g, part of Oracle Fusion Middleware, is preintegrated with MapViewer and gives you the ability to create business intelligence analyses and dashboards that include maps. This article looks at how to create map views and combine them with other data visualizations by using Oracle Business Intelligence Enterprise Edition 11g.
If you would like to try out the examples in this column, you can download the sample data and installation instructions. Download the sample spatial data and maps used by the examples separately from Oracle Technology Network.

Creating a Custom Point Layer Map

In the first example, you are analyzing retail data for stores in your organization. Most of your stores are in the San Francisco Bay Area, and you will now create a map view that places each store in its correct location, along with a legend and a store icon colored either red, amber, or green, depending on how well the store is performing. Prior to creating this map, you will need to configure Oracle Business Intelligence Enterprise Edition 11g to work with MapViewer and associate the maps and layers within your maps with subject area columns in your data set. The sample data download provided with this column contains instructions on how to perform this configuration, and the configuration is also described in Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).
To create the first map view, do the following:

1. From the common header menu on the Oracle Business Intelligence Enterprise Edition 11g home page, select New -> Analysis.
2. When prompted, select the Sales – Fact Sales subject area, and then from the Subject Areas panel on the left-hand side of the screen, choose the following columns for your analysis:
   Dim Stores.Metro City
   Dim Stores.Latitude
   Dim Stores.Longitude
   Fact Sales.Revenue 
3. Move your cursor over the Dim Stores.MetroCity column in the Selected Columns area on the right-hand side of the screen, and select Filter from the menu. Use the New Filter dialog box to create the following filter: 
   Metro City is equal to/is in
   SAN FRANCISCO
   Then switch to the Results tab and confirm that a list of store locations in San Francisco, along with revenue figures, is displayed. 
4. Now you can create your map: locate the Views panel at the bottom left of the screen, and select New View -> Map. When the map view is displayed, select OBIEE_SF_MAP1 from the list of maps shown at the top of the view.   
5. The new map view is now ready for editing. A panel on the right-hand side enables you to define map formats. Click the Delete button next to the default OBIEE_CITY map layer to remove it from the list of formats, because you will create your own format in the following steps. Then click the Add New Map Formats button to add an image map format, using a custom point layer. This will enable you to place icons on the map, based on latitude and longitude coordinates. 
6. In the Image (Custom Point Layer) dialog box that appears, enter the following settings to place the stores in your sample data set on the map:
   X (e.g. Longitude) : Longitude
   Y (e.g. Latitude)     : Latitude
   Tooltips                   : Longitude,
                                       Latitude,
                                       Revenue
   Vary Image By       :  Revenue
   Bin Type                  :  Percentile Binning
   Bins                          : 3
   In the Select Image dialog box, select the Red, Yellow, and then Green circles (traffic lights) for the 0-33%, 33%-66%, and 66%-100% bins, respectively. 
7. Click OK to finish this map definition. Then use the pan and zoom buttons on the left-hand side of the map to locate downtown San Francisco, as shown in Figure 1.  

Figure 1: The store icons on this map view of San Francisco reflect performance data.

You have now created your first map view. Once you have viewed the map, click the Home link at the top of the common header menu to return to the Oracle Business Intelligence Enterprise Edition home page.

Creating Thematic Maps

Your first map, OBIEE_SF_MAP1, used x and y coordinates to plot the location of your stores in the San Francisco Bay Area. In this next example, you will display a larger-scale map of the continental U.S. and place pie charts over the states in which the company operates, with the charts showing the breakdown of sales across product categories.
To create this map, do the following:

1. Select New -> Analysis from the Oracle Business Intelligence menu at the top of the screen, and select Sales – Fact Sales as the subject area.
2. From the Subject Areas panel on the left-hand side of the screen, choose the following columns to add them to the Selected Columns area:
   Dim Stores.Country and State Code
   Dim Products.Product Category
   Fact Sales.Revenue
   Because you will be mapping stores located in all of the company’s U.S. territories, you do not need to apply a filter to the selection criteria.
   Switch to the Results tab and confirm that a list of states, together with product categories and revenue amounts, is shown.
3. To create the map, locate the Views panel and click the New View button. Select Map from the list of views, and select the OBIEE_WORLD_MAP as the map to be displayed within the map view.
4. As with the previous map view, navigate to the Map Formats panel and delete the default map format. Click the Add New Map Formats button, but this time select Pie Graph from the list of formats. Then, when prompted, select OBIEE_STATE from the list of map layers available.
5. In the Pie Graph dialog box that appears, set the following for the pie charts: 
   Tooltips          : Country and
                              State Code;
                              Revenue
   Slice Size       : Revenue
   Graph Size     : 15
   Slices              : Product Category

Next Steps DOWNLOAD sample data for this column o31bi-323440.zip SampleApp_Navteq_dmp.zip READ more about Oracle Business Intelligence Enterprise Edition 11g Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) Oracle Fusion Middleware Developer’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)

Click OK to save the settings, and then with the map view displayed, use the pan and zoom buttons to display the U.S. West Coast. Locate Oregon on the map, place your cursor over the pie chart, and then click the legend. Note that the state name and the product categories are all hyperlinks—you can use these to pass filter values to other analyses, as you will see in a moment. For now, though, save this map to the Presentation Server catalog, using the name Country and State Map.

Using Master-Detail Actions

Because map views are fully integrated into Oracle Business Intelligence Enterprise Edition 11g, you can use links displayed in map legends to pass parameters to other views. By doing this, you can make selections by using the map view and use these to change the values displayed in associated chart, table, and other views.
In this final example, you will use the Country and State Map you created previously to drive changes in two other views. To do this, start by creating two additional analyses that you will later link to the map view.

1. First create an analysis using the Dim Products.Product Category, Dim Times.Month, and Fact Sales.Revenue subject area columns, and display the results as a horizontal bar chart. Place Dim Products.Product Category in the Graph Prompts area of the graph layout, and save this analysis to the Presentation Server catalog, naming the analysis Chart Detail.   
2. Then create another analysis, this time based on the Dim Stores.Country and State Code, Dim Stores.RegionName, and Fact Sales.Revenue columns. Using the Table view that is provided by default for a new analysis, use the Layout Editor to move Dim Stores.Country and State Code to the Table Prompts area. Save this analysis to the Presentation Server catalog, naming it Table Detail. 

Now you can start to add links between the map and these new analyses, using the new master-detail linking feature introduced in Oracle Business Intelligence Enterprise Edition 11g.
To make use of master-detail linking for these examples, first configure the analysis containing the map to broadcast master-detail event messages for the Product Category and Country and State Code columns and then configure individual views within the last two analyses you created to listen for these events:

1. From the common header menu, select Open -> Open and navigate to where you saved the Country and State Map. Then, with the analysis open for editing, select the Criteria tab to show the list of columns used in the analysis.   
2. Move your cursor over the Product Category column in the Selected Columns area, and select Column Properties from the menu. When the Column Properties dialog box appears, select the Interaction tab and then change the Value Primary Interaction setting to Send Master-Detail Events. When the Specify Channel setting appears, enter MDCAT. 
3. Now repeat the above step for the Dim Stores.Country and State Code column, this time entering MDSTATE for the Specify Channel setting. Then, with these changes made, save the analysis back to the Presentation Server catalog.
4. Setting the map analysis to broadcast master-detail events is the first part of this process; the second is to configure individual views within the target analyses to listen for them. To do this, using the common header menu, select Open -> Open, navigate to the Chart Detail analysis, and select the Results tab to show the bar chart. Click the Edit View button for the chart view, and then click Edit Graph Properties.
   Then, in the Graph Properties dialog box, check the Listen To Master-Detail Events checkbox and enter MDCAT in the Event Channel text box. Click OK to close the dialog box, and then save the analysis back to the Presentation Server catalog. 
5. Now repeat the previous step for the Table Detail analysis by editing the table view within the analysis and clicking the Table View Properties button to display the Table Properties dialog box. Check the Listen to Master-Detail Events checkbox as before, but this time enter MDSTATE for Event Channel. This will ensure that this table view responds to state and country selections made on the map. Save the analysis back to the Presentation Server catalog once you have finished making the changes. 

To view the analyses together and check that the two detail-level analyses respond to selections made in the map view, select Dashboard -> My Dashboard from the common header menu and use the dashboard editor to add the three analyses to a new dashboard page, with the map displayed in a section just above the two detail-level analyses, as shown in Figure 2.

Figure 2: This dashboard page displays pie charts and detail-level analyses.

Save and then run the new dashboard, which should look like Figure 2. Move your cursor over the pie chart displayed over California, and click the legend to display the action links. Click the Gifts product category, and watch how the bar chart listens for and displays your product category selection. Now click the USA_CA link in the legend, and see how it changes the values displayed in the table. You can use map views to display data with a geographic component and use map selections to drive filter value changes in other analyses on the dashboard.

Conclusion

Most business intelligence applications include an element of geographic data, and the new integrated mapping feature in Oracle Business Intelligence Enterprise Edition 11g makes it easy to visualize this data in the form of rich, interactive maps. With master-detail links, users can connect map views to other views, creating an interactive query environment built around geographic and other data.