Oracle BI Solutions: OBIEE 11g - Sorting on the Total in a Pivot

Oracle BI Solutions: OBIEE 11g - Sorting on the Total in a Pivot: Pivots in OBIEE are a great option of displaying data, especially monthly data.  However,  I often run into a situation where the custo...

Oracle BI Solutions: OBIEE 11g - Switching Between Multiple Reports

Oracle BI Solutions: OBIEE 11g - Switching Between Multiple Reports: I will show switching between reports without using any intermediate report. So I am using 3 reports which I want to switch using dashboa...

Oracle BI Solutions: OBIEE 11g - Multiple values in single column using...

Oracle BI Solutions: OBIEE 11g - Multiple values in single column using...: Let us start with the SH subject area. We would be combining Calendar_Year,Product_category and Customer_City_Id in a single column with ...

OBIEE 11g - OBI Server and Presentation Cache Management

OBIEE 11g - OBI Server and Presentation Cache Management

Oracle BI Server Cache Management

• BI server could be leveraged by multiple users and reports depending on whether they are accessing similar or subset of the cached data, though from a different reports.
• To purge OBI Server cache the best-practice is to set it as automated process after completing ETL, because if we set the auto-purge at specified time, but for some reason ETL fails, the BI Server cache gets purged and all user queries will have to get executed all over, which will impact the BI Server performance.

1. To complete this task, please follow these steps:
2. Create a text file called: PurgeAllCache.txt and save it in C:\ (easier to remember)
3. Open and type the following: Call SAPurgeAllCache()
4. Save and Close.
5. Create another text file called: PurgeCache.txt (we will later convert this file into batch file .bat) and save it in C:\
6. Go to: \Oracle_BI\bifoundation\server\bin\nqcmd.exe and make sure you see the file DO NOT run it.
7. Copy and paste the nqcmd.exe file directory somewhere temporary
1. This is a ODBC client that runs commands
8. Now, go to: Start > All Programs > Administrative Tools > Data Sources (ODBC)
9. Click on System DSN tab and locate Oracle BI Server 11g_XXXXX > click on Configure…
10.  
11. Copy and paste the Name: with nqcmd.exe file path for now. The ODBC name in this case is:coreapplication_OH905911364
12.  
13. Cancel to close Oracle BI Server and ODBC windows.
14. Open PurgeCache.txt and type the following:
1. Echo off
2. \Oracle_BI\bifoundation\server\bin\nqcmd.exe -d coreapplication_905911364 -u weblogic -p password -s C:\PurgeAllCache.txt
1. Make sure this command is ALL IN ONE LINE
3. Echo 

• Explnation:
• \Oracle_BI\bifoundation\server\bin\nqcmd.exe [nqcmd.exe file path]
• -d coreapplication_905911364 [my DSN ODBC name]
• -u weblogic [-u BI Admin Username]
• -p password [-p BI Admin Password]
• -s C:\PurgeAllCache.txt [-s SQL Input File to execute]

1. Save and Close. 
2. Change PurgeCache.txt into PurgeCache.bat this will convert the file into executable file. 
3. Now run couple of analysis in OBIEE. 
4. Open the RPD in an Online mode:
5. Go to: Manage > Cache - and you'll see recent saved cache.
6. Now run PurgeCache.bat and recheck the RPD Cache, you will see that it is cleaned :).

  

Oracle BI Presentation Cache Management

• When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.
• The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP.
• The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory: 
•  ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp
• The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.
• The Presentation Services cache is not the same cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.
• i.e. if the requirement is to set the cache to expire every 15 minutes; open to edit the instanceconfig.xml for OBI Presentation Services and add the following : 
• Your code should look like this:
   
• 
• Save and Close the file.

Alternative Cache Management Techniques

• Open  BI Administration Tool > Open the RPD
• Go to: Physical table properties
• Check: Checkeable and choose as appropriate
• Cache never expires - self-explanatory
• Cache persistence time - specify how often the table should purge the cache
• Note: this implies only to this table and is not the best-techniques when you are working with 100s of tables.

Automate OBIEE 11g Cache Clearing Process

Most of the OBIEE 11g leverages the Cache mechanism to provide the user with faster results . However the downfall is that if the Cache becomes stale, it might show outdated result . Hence it is necessary to clear the Cache at regular Intervals.

Cache Mangement is a huge concept in OBIEE. In this post, i would just deal with a way on clearing the OBIEE 11g Cache using Analysis.

1.) We would need a separate database conenction in the RPD, this would connect us directly to the BI server.Create a new database and choose "ODBC Basic" as the Database type. Make sure you check the direct database request checkbox.

2.) Next, create a new Connection Pool for the above Database and  in the DNS ( data source name) select the default database connection to BI server (ususally it starts as coreapplication... XXX). A user name and password is not required here .

3.) Since you have allowed the Direct database access request, you could setup permissions for it so that the security is not compromised. Click on the "permissions" tab to set it up. This step is not mandatory.

4.) Now login to Analytics ( http://yourServername:9704/analytics) . Go to the Administration Tab and make sure you have the access privileges to send "Direct Database Requests".

5.) Next, Click New - > Create Direct Database Request

6.) Mention the "Database Name"."Connection pool name" that you had created in the earlier steps . in the SQL statement, pass the function SAPurgeAllCache() which would clear the entire cache.The end output would look similar to this:

So, in this manner you could create an analysis , when executed would clear the analysis.

Note: If required we can create an Agent/iBot and call this analysis on fixed schedule which would make this entire process truly automated.


I am ref below blog:
http://obiee-sharat.blogspot.com/2013/06/automate-obiee-11g-cache-clearing.html

Over lapping of views when exported to Excel

One of the major issue what everyone faces is the 'Overlapping of the Chart and table or filter view when downloaded to Excel'


So, here is a small workaround to resolve the issue:

Place a narrative text between the chart and the table/pivot table view. With the following code:
<span style="display:none">[br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/]</span>

Check the HTML and also the number of [br/] tags depends on the requirement.

Save it and export the reports.  You will see the chart and the table view separately.
 

OBIEE11g Report Logos

OBIEE has some nice default report logo’s. Problem is you don’t always have access to the directory to browse them. Here is a Cheat Sheet:

Common OBIEE Errors and Resolutions

Common OBIEE Errors and Resolutions

In this post I am going to cover some common errors faced during OBIEE implementation and its resolution, in the windows environment. I am hoping this will be handy to anyone who is new to OBIEE.
Unable to Log In
Error connecting to the Oracle BI Server: Could not connect to the Oracle BI Server because it is not running or is inaccessible. Please contact your system administrator.
Error Codes: WH4KCFW6:OPR4ONWY:U9IM8TAC

Common cause(s):

• "Oracle BI Server" windows service is not running. Starting the service could resolve the issue.
• In case the BI Server service is running and still the error occurrs, check the port the service is listening to is matching with the port specified in the ODBC DSN for the analytic repository. In NQSConfig.ini file look for RPC_SERVICE_OR_PORT. The default value is 9703. The ODBC DSN name can be found in instanceconfig.xml

"Oracle BI Server" windows service is not starting.
Common cause(s):

• The RPD file specfied in the NQSConfig.ini doesn't exist in the repository folder.
• RPD file may be inconsistent. Open it using Admin tool in offline mode and fix the consistency errors.

Error Generating Chart
Error Codes: ETI2U8FA

Common cause(s):

• "Oracle BI Java Host" windows service is not running. Starting the service could resolve the issue.

Access denied for user to path /users/username/_portal.
Error Codes: O9XNZMXB
Common cause(s):

• The specified user doesn't have permission to read/write to the his/her catalog folder. To resolve the issue, login as Administrator and grant permission to the user to his/her catalog folder using catalog manager.

OBIEE Varaibles Overview

OBIEE Interview Questions And Answers

Hi Guys I am listing by ref some other blogs...Please correct me if there are any wrong answers.
Cheers...

What is Chronological Key?

Chronological key uniquely identifies the data at particular level. Chronological key is mostly used in time dimensions where time series functions are used.

What is Logical Key?

Logical key is the key which is used to define the unique elements in each logical level. A logical level may have more than one level key. When that is the case, specify the key which is primary key of that level. It is used to specify the column which is used for drill down and which is used as primary keys.

What is level Based measure?

Level-based metrics means, having a measure pinned at a certain level of the dimension.

Briefing Book

·         A briefing book is a collection of static or updatable snapshots of dashboard pages, individual analyses, and BI Publisher reports.

·         Add the content of dashboard pages (including pages that contain BI Publisher reports) or individual analyses to new or existing briefing books.

·         Download briefing books in PDF or MHTML format for printing and viewing. The PDF version of a briefing book contains an automatically generated table of contents.

·         Update, schedule, and deliver briefing books using agents, if your organization licensed Oracle Business Intelligence Delivers.

Content Type

·         Updatable — Refreshes the data whenever the briefing book is downloaded, or when it is specified as the delivery content for an agent.

·         Snapshot — Adds the content in its current state. Snapshot content preserves the original data and is not updated when the briefing book is rerun. Snapshot content is not updated when using agents.

Note: 

If you have ever used the Briefing Books feature in OBIEE 10g you might remember the necessity to install the OBI Briefing Book Reader software in order to open the .sbb (Siebel Briefing Book) files.

In OBIEE 11g, there is no briefing book reader and no .sbb output anymore.

Instead we can now download briefing books as PDF (as in 10g) or MHTML.

Edit Execution Precedence

This Option we can find in Initialization block setup.

a.       When a repository has multiple initialization blocks, you can set the order (establish the precedence) in which the blocks will be initialized.

b.       First, you open the block that you want to be executed last and then add the initialization blocks that you want to be executed before the block you have open. For example, suppose a repository has two initialization blocks, A and B. You open initialization block B, and then specify that block A will execute before block B. This causes block A to execute according to block B's schedule, in addition to its own.

Implicit Fact Column in OBIEE

In case we have multiple Fact tables under one Subject Area, then it is a best practice to set an implicit fact column. Setting an implicit fact has two benefits.

ü  An implicit fact column help BI Server choose the join path when multiple join paths are available between facts and dimensions.

ü  When a report is generated only from dimension tables, it will result in an error as an Analyses should always consist of at least one fact or measure. To overcome this error we define an implicit fact column. This ensures that a fact is always included in the SQL generated by BI Server.

To set an implicit fact column:

•      In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
•               In the general tab, find the Implicit Fact Column section.
•               Click on Set… button, this will open the browse window to select a fact column from the fact tables available in the presentation catalog.
•             Select the column.
•             Click OK.

Purging Options in OBIEE

Invoking ODBC Extension Functions

The following ODBC functions affect cache entries associated with the repository specified by the ODBC connection. You can call these functions using the nqcmd.exe command-line executable.

The syntax of the call will be as follows:

nqcmd -d "Analytics Web" –u administrator –p sadmin –s purge.txt.

Where purge.txt contains the call (for example, call SAPurgeAllCache())

ü  SAPurgeCacheByQuery()

Purges a cache entry that exactly matches a specified query.The following call programmatically purges the cache entry associated with this query:

Call SAPurgeCacheByQuery(‘select lastname, firstname from employee where salary >
100000’ );

ü  SAPurgeCacheByTable()

Call SAPurgeCacheByTable(‘DBName’, ‘CatName’, ‘SchName’, ‘TabName’);

Additionally,DBName and TabName cannot be null. If either one is null, you will receive an error message.

ü  SAPurgecacheByDatabase()

Purges all cache entries associated with a specific physical database name.

Call SAPurgeCacheByDatabase( ‘DBName’ );

ü  SAPurgeAllCache()

Purges all cache entries. The following is an example of this call:

Call SAPurgeAllCache();

OBIEE 10G & 11G DIFFERENCES

                              

ü  OBIEE 11g uses the WebLogic Server as the server application and in 10g it is OC4J (Oracle Connector for J2EE).

ü  Clustering process is much easier and automated in OBIEE11g.

ü  We can have model lookup tables in repository.

ü  The new UI call Unified Framework combines Answers, Dashboards and Delivers.

ü  A new column called the Hierarchical column is introduced.

ü  BI Publishers is fully and seamlessly integrated with OBIEE 11g.

ü  New Time series functions PERIOD ROLLING and AGGREGATE AT are introduced.

To_DATE(<Measure>, <Level>)

Ago (<Measure>, <level>,< No.of Periods>)

PeriodRolling(<Measure>,<Starting Period Offset>,<Ending Period Offset>)

periodRolling(<Measure>,-2,0)  à 3 PeriodRolling Sum

A measure with the PERIODROLLING function calculates results based on the query grain(if u select month in criteria it will calculate last 3months, if u select year it will calculate last 3years rollingsum).In your criteria if u have qtr, month and week it will calculate based on week. (It always takes low granularity)

ü  In OBIEE 11g we can create KPIs to represent business metrics.

ü  The Aggregate persistence wizard creates indexes automatically.

ü  The session variables get Initialized when they are actually used in OBIEE11g unlike 10g where they are initialized as soon as a user logs in.

ü  OBIEE11g supports ragged (unbalanced) and skipped hierarchy.

ü  You can also define parent child hierarchy in OBIEE11g.
ü  OBIEE 10g need to install the OBI Briefing Book Reader software in order to open the .sbb (Siebel Briefing Book) files. Where as in OBIEE 11g there is no briefing book reader and no .sbb output anymore. We can download briefing book as pdf or MHTML format.
ü  In OBIEE10g you have to install Event pooling table (S_NQ_EPT) manually in database where as in OBIEE11g it will be installed while creating RCU (Repository Creation Utility) in BIPLATFORM schema.
ü  In OBIEE 11g Presentation variable can hold more than one value. Where as in OBIEE 10g it can hold only one value at a time.

ü  SELECT_PHYSICAL command is supported in OBIEE 11g.(in 10g we have directdatabase request only.Here we will mention database columns …SELECT_PHYSICAL will get the data from rpd physical layer. here we will mention physical layer alias tables and columns.

Benefits of using SELECT_PHYSICAL

 By using SELECT_PHYSICAL, the developer can:

•     Create queries using the alias names, simplifying the query readability
•         Simplify the query, in case of using opaque views created in the Physical Layer of the RPD
•     Join tables from multiple data sources
•     Access the data sources without need of knowing the login credentials