Conditional Formatting Might Not Work After Upgrade

Conditional Formatting Might Not Work After Upgrading from Oracle Business Intelligence Release 10gto Release 11g:

If an attribute column's Value Suppression setting (in the Column Format tab of the Column Properties dialog) is not set to Repeat, then the measure is aggregated to the grain as defined by the position of the column with the format defined. This might cause a change in the conditional formatting after upgrading from Oracle Business Intelligence Release 10g to Release 11g. If the Value Suppression setting is set to Repeat, then the measure column that the conditional format is based on always uses the detail grain of the table view, in which case the conditional formatting works as expected.

Oracle Business Intelligence Certication

This post will give an overview of OBI Foundation suite 11g exam.

• Oracle Business Intelligence (OBI) Foundation Suite 11g Essentials | 1Z0-591 

Topics tested in this exam are:

Oracle BI and EPM System Overview
OBI EE Architecture
Oracle BI Installation
OBI EE User Interface Overview
Dimensional Modeling
Oracle BI Repository Overview
Build Basic Repository
Repository Testing and Deployment
Complex Repository Modeling
Advanced Repository Modeling
Building a Repository with Multiple Sources
Hierarchies and Level Based Measures
Using Variables
Implementation Methodology and Best Practices
Oracle Business Intelligence Enterprise Edition Security
Cache Management & Performance Tuning
MS Office Integration
Spatial Integration
Write Back within OBI EE
Essbase Integration


Here are few sample questions for the Certification Exam.

What does the Oracle BI Foundation Suite consists of ?
•    A. Oracle BI Enterprise Edition Plus
•    B. Oracle Scorecard
•    C. Endeca Information Delivery
•    D. Hyperion Financial Management
•    E. Oracle Discoverer
Answer: A,B

What does the common Enterprise Information Model provide ?
A. A Common Metadata Foundation across all data sources
B. A dimensional data model
C. ETL data dictionary
D. A consolidated model for MDX queries
Answer: A

Name the three components of a Cluster Server ?
•    A. Master Server
•    B. Repository Publishing Directory
•    C. Primary Cluster Controller
•    D. Web Server
•    E. Server Request Controller
Answer: A,B,C

Identify the two components that can be used to customize the user interface in Oracle BI Presentation Services ?
•    A. Dashboard Editor
•    B. Styles and Skins
•    C. XML Message Files
•    D. Scripts in installation folders
•    E. VBScripts
Answer: B,C

In a dimensional model, if you want to capture history for a slowly changing dimension, what methodology would you use ?
A. Type I Slowly Changing Dimension
B. Type II Slowly Changing Dimension
C. Type III Slowly Changing Dimension
D. Type IV slowly Changing Dimension
Answer: B

The import feature of the OBI Repository allows you to import objects into the repository from
•    A. Another Multi Dimensional Source
•    B. The Database
•    C. Another Repository
•    D. All of the above
Answer: B

What is the correct method to edit the Oracle BI repository ?
•    A. All major enhancements and developments should be made in the Online Mode
•    B. All major enhancements and developments should be made in the Offline Mode
•    C. Always make major enhancements and developments in the database and never on the BI Repository
•    D. Always edit the file using a text editor on the server
Answer: B

When you open a sales.rpd file in the BI Server repository directory, it displays a message “The repository sales.rpd can only be opened as Read-Only. Do you want to open it ?”. What is the correct statement ?
•    A. Oracle BI Presentation services is running
•    B. Sales.rpd file can be opened only in the Online mode
•    C. Oracle BI Server is running and Sales.rpd is the default repository
•    D. None of the above
Answer: C

What do you use to Model a schema where the dimension table and the fact table have a many t o many relationship ?
•    A. Complex Join
•    B. Aggregate Table
•    C. Helper Table
•    D. Split Table
•    E. Bridge Table
Answer: E

Which layer within the Oracle BI Repository provides customized, role based views to users who build analyses and other client applications ?
•    A. Physical Layer
•    B. ETL Layer
•    C. Business Model and Mapping Layer
•    D. Presentation Layer
Answer: D

OBIEE can connect natively to the following MOLAP data sources ( Check all that apply )
•    A. Oracle Essbase
•    B. Oracle OLAP
•    C. SQL Server Analytic Services
•    D. SAP BW
•    E. All of the above
Answer: E

Which layer allows you to define the data hierarchy, define dimensions and measures for multidimensional analysis and create predefined calculations ?
•    A. Presentation Layer
•    B. Business Model and Mapping Layer
•    C. Physical Layer
•    D. ETL Layer
Answer: B

What is the name of the object that is used to initalize dynamic repository variable and system session variables for OBIEE Sessions ?
•    A. Tokens
•    B. Variables
•    C. Initialization Blocks
•    D. Meta Tags
Answer: C

Why do you use Aliases in the Physical Layer of the repository ?
•    A. To remove Complexity
•    B. To hide original Objects
•    C. To enforce naming conventions
•    D. To avoid Circular Joins
Answer: D

In a multi user environment, how do you resolve project conflicts ?
•    A. The Administration tool locks projects on the server to avoid conflicts
•    B. Always delete local changes when checking in files to avoid conflicts
•    C. Conflicts are resolved during the Check-In process
•    D. Multiple users cannot work on projects at the same time.
Answer: C

When you want to schedule an Analysis to run at a pre determined timeframe, what component will you use ?
•    A. Action Framework
•    B. Scorecard
•    C. KPI
•    D. Agent

Answer: D

Security features for the Oracle BI plug-in for Microsoft Office include :
•    A. The ability to secure tables and graphs in Office applications so users must credentials to view Oracle BI Data
•    B. Support for the Oracle BI Security Model including support for both encrypted (SSL ) and Single Sign On modes
•    C. Only A
•    D. Both A & B

Answer: D

What are the tools that can be used to construct map based applications for use in Oracle BI?
•    A. Google Maps
•    B. Oracle Mapviewer
•    C. Endeca Infront
•    D. Oracle Map Information Server

Answer: A,B

How would you enable an Analysis to “listen” for Dashboard Prompts ?
•    A. Create a variable in Analysis and then Create a similar variable in the Dashboard Prompt
•    B. When building a filter in Analysis, select “Is Prompted”
•    C. By Creating an Initialization Block
•    D. Through Custom Scripting

Answer: B

Which of the following tools provide viable methods to view Essbase Data ?
•    A. Smartview
•    B. OBIEE
•    C. SQL Developer
•    D. Essbase Administration Services


Answer: A,B,D

Best of Luck for the Certification!!!!

Reference: Oracle.com

OBIEE Left Outer Join Puzzle Solved.

Issue:
Recently i was facing an issue with my report where the measures are doubled, where as i dont have any dubes in the Fact table, issue was because of the left outer join that i had as it was throwing null values the entire row is repeated twice as a result because of my aggregation defined on measure.

Sol 1:
whenever you group a colum with left outer always write a ifnull(expr, 'NA') so that distinct is counted and you wont be facing any issues.

Sol 2:
In other ways try to fig it out if you can avoid left outer by implementing a look-up on the fact.

OBIEE unalbe to Login

While migrating the Repository i was facing this issue and i was unable to login to OBIPS.

[2012-07-30T12:30:54.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 18c5ef9f7c3aec02:-1a7b1b1:138d66ca491:-8000-0000000000000cd9] [tid: 12b8]  [nQSError: 13041] The GUID of user 'weblogic' does not match user reference GUID at the repository. Please ask the administrator to delete the old user reference at the repository and login again.

Resolution:

As guided in the error i opened the RPD offline deleted the reference of weblogic in identitiy and uploaded the RPD and now i was able to login to the Server.

Always the best practice is to update the GUIDs at-least monthly once.

OBIEE Login Failure Communication Error

I am facing a wiered issue all my services are up and running Yet i am unable to login to the Portal.

looking into the logs:

Log from OBIPS:

[OBIPS] [ERROR:1] [] [saw.security.odbcuserpopulationimpl.getbisystemconnection] [ecid: ] [tid: ] Authentication Failure.
Odbc driver returned an error (SQLDriverConnectW).
State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43126] Authentication failed: invalid user/password. (08004)[[

 Log from OBIS nqserver.log

[nQSError: 12010] Communication error connecting to remote end point: address = saleem; port = 7001.
[nQSError: 46119] Failed to open HTTP connection to server saleem at port 7001.'
]]

Solution:


Navigate to Business Intelligence -> coreapplication ->Capacity Management' tab -> 'Scalability' sub-tab
Click on 'Lock and Edit Configuration' button
Enter the IP address in the 'Listen Address' field
Click on 'Activate Changes' followed by 'Release Configuration' buttons
Restart BI Server and Presentation Services.





After Adding the Listener Address my issue was resolved.

Core Issue behind the scenes:

Please have loop back adapter installed in your system. My server IP changed when i connected to internet and it was the issue.

Interaction in OBIEE

We can configure various options that change the way that right-click interactions are handled in views for an analysis at run time. These are controlled by the tags given below , to be configured in instanceconfig.xml file.

 

<ServerInstance>
  <Analysis>
    <InteractionProperties>
      <InteractionPropertyAddRemoveValues>true</InteractionPropertyAddRemoveValues>
      <InteractionPropertyCalcItemOperations>true</InteractionPropertyCalcItemOperations>
      <InteractionPropertyDrill>true</InteractionPropertyDrill>
      <InteractionPropertyGroupOperations>true</InteractionPropertyGroupOperations>
      <InteractionPropertyInclExclColumns>true</InteractionPropertyInclExclColumns>
      <InteractionPropertyMoveColumns>true</InteractionPropertyMoveColumns>
      <InteractionPropertyRunningSum>true</InteractionPropertyRunningSum>
      <InteractionPropertyShowHideSubTotal>true</InteractionPropertyShowHideSubTotal>
      <InteractionPropertySortColumns>true</InteractionPropertySortColumns>
    </InteractionProperties>
  </Analysis>
</ServerInstance> 

Configuring Views OBIEE 11g

In OBIEE 11g we need to configre the Views to manually increase the limit of records/rows it can be displayed in a pivot/table/cube. You have to make these changes in instanceconfig.xml and restart the presentation server.


<ServerInstance>
  <Views>
    <Cube>
      <CubeMaxRecords>60000</CubeMaxRecords>
      <CubeMaxPopulatedCells>240000</CubeMaxPopulatedCells>
    </Cube>
   <Table>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>500</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
      </Table>
      <Pivot>
        <MaxVisibleColumns>300</MaxVisibleColumns>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>500</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
      </Pivot>
      <Charts>
        <MaxVisibleColumns>2000</MaxVisibleColumns>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>2000</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>
      </Charts>
  </Views>
</ServerInstance>

Adding a Default Header or Footer to New Analyses

To Edit the Answers Template for default template you must override the kuiCriteriaDefaultViewElements tag of Answers.

I want to add the Footer so i choose the tag  pageFooter to achieve this  "ObieeLearner Propriety and Confidential --Saleem" , to achieve this i creaated the header.xml custom xml as shown below.

Note: For Header please add the tag: pageHeader.

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
   <WebMessageTable system="Answers" table="ViewDefaults">
<WebMessage name="kuiCriteriaDefaultViewElements" translate="no"><HTML>
   <view signature="compoundView" >
      <pageProps pageSize="a4">
         <pageFooter showOnDashboard="true" show="true">
            <zone type="top"><caption>[b]OBIEE LEARNER Propriety and Confidential --Saleem[/b]</caption>
            <displayFormat fontColor="#FFE000"/></zone>
         </pageFooter>
      </pageProps>
   </view>
</HTML>
</WebMessage>
   </WebMessageTable>
</WebMessageTables>

Once the changes the done just Reload the metadata you should be able to see the Results

Set Default Logging Level in OBIEE

By Default there will be no log level set in obiee. when you try to see the sql in the session manager it will display nolog

To enable Default logging in obiee. Open the RPD Online/Offline.

Tools -> utilities->options->Repository and set the logging level.

Right Function in OBIEE

Here is a look at the right function in obiee

Actual:  

"Time"."Per Name Month"

After:

right("Time"."Per Name Month",2)

Export or Print Dashboard Page

Follow the Screenshots below:

Hide Catalog User Interface in 11g

I had a requirement where we don't want to deny catalog UI access to some roles  in the Portal.

Goto Admininstration -> Manage Privilages ->Home and Header tab ->Acces Catalog UI
and remove the access for the Role.

OBIEE Out of Disk Space

Error Details
Error Codes: AAD5E5X3:OPR4ONWY:U9IM8TAC
Odbc driver returned an error (SQLFetchScroll).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46118] Out of disk space. (HY000)

I had like 30Gb of free space in the server but still i was facing the issue where i was facing out of disk space error in the portal.

Issue: Users were running reports that occupied all the tmp space. Mount point where obiee was installed had only 3gb free space which was getting filled i had another mount point in the server where i had 25G free space.

Resolution: change the workdirectory to the new mount point in NQSconfig.ini and restart the services.

OBIEE Login Issue Invalid: WSM-06102 PolicyReference

I had an issue where all my services were up and running but i was unable to login. I was able to login to using weblogic to em but not analytics.


[2012-07-19T23:03:45.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 00i8wGM0^Gd6yGWFLzfP8A0000ew000000] [tid: 13e0] Error Message From BI Security Service: PolicySet Invalid: WSM-06102 PolicyReference The policy reference URI "oracle/wss_username_token_service_policy" is not valid.
[2012-07-19T23:03:46.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 00i8wGM0^Gd6yGWFLzfP8A0000ew000000] [tid: 13e0]  [nQSError: 43126] Authentication failed: invalid user/password.

Furthur analysis revealed that the wsm-pm State is Failed. At this point of time i redployed the library and now i was able to login to the portal.

To Redeploy:

click on lock and install once you do it updatebutton will highlight. click install and follow the wizard.


 After this i was able to login to the portal:

Hope this helps!!!!!!!!!!!!!

Thanks a lot.

Upgrade OBIEE 11g catalog

I have been getting a warning in the OBIPS log:

OBIEE 11g: Warning: "Upgrade is recommended to the latest 11.1.1.6.0 (Build 120104.0800 64-bit) catalog version" After Upgrading to OBIEE 11.1.1.6

so i decided to upgrade the catalog:

Here are the steps:

Note: RPD should be consistent and take a backup of catalog before starting

Step 1: Stop OBIPS 

cd ORACLE_INSTANCE/bin

opmnctl stopproc ias-component=coreapplication_obips1

Step 2: find and set the tag UpgradeAndExit to true from false in instanceconfig.xml

<UpgradeAndExit>true<UpgradeAndExit>

Step 3: Start OBIPS

cd ORACLE_INSTANCE/bin

opmnctl startproc ias-component=coreapplication_obips1

Step 4:

After the catalogs are updated, edit the instanceconfig.xml file again and change the upgradeAndExit option back to false.

Step 5: Restart all services.

LDAP Authentication Failure

Ldap authentication is failing when users logins after his/her group is changed. The issue is cache refresh interval of the LDAP Server is set to 1 day by default. Change the limit, Clear all the cache and restart the server.

To change the limit: open the Rpd goto tools -> options -> Repository.

Hide BI Portal Name

I had a requirement where i need to hide the portal banner name/ company name.

I need to remove Business Intelligence from the banner.

Edit common.css  (....../res/sk_blafp/b_mozilla_4/common.css)  , find the class HeaderBrandName and change the tag display: none;

Obiee permissions Traverse vs Read

OBIEE 11g Traverse vs Read:

Traverse and Read are tricky to understand as both grant access to the object you want, here is an insight into it.

Read:

Use this option to grant access, but not modify, the object.

Traverse:

Use this option to grant access to objects in folders within the selected folder when the user does not have permission to the selected folder. For example, if you grant users Traverse Folder permission to the /Shared Folders/Test folder, they cannot access objects in the/Shared Folders/Test folder but can access objects stored in lower-level folders, such as the /Shared Folders/Test/Guest folder.

Conditional Formats on Another Columns

You can create conditional formats to apply to one column based on the values of a second column, for display in tables and pivot tables. For example, you can create a conditional format to color the Region column green when values of the Sales column are greater than $30 million.

You can create a condition using a column that is not displayed in views for the analysis, if you use the Hide option on the Column Format tab of the Column Properties dialog. If you place the column in the Excluded drop target of the Layout pane, then you cannot create a condition using that column.
Conditional formatting is applied based on the underlying value, even if you select the Show Data As options in the Layout pane to show the data as percentages or indexes.

OBIEE Query is providing Incorrect results

If the obiee query is not providing the expecting results:

1) When Cache is enabled it is more likely that the results will come from cache instead of database. Clear the Cache.

To Clear the cache open
Admin Tool (online mode) -> Cache Manager-> Select the user name -> right click it and purge cache.


2) Check the connection pool if we are pointing to the correct database.