OBIEE 11g Patching Catalogs using the Catalog Manager Command Line Interface

CATALOG MANAGER

-          Catalog Manager is a tool that lets you perform online and offline management of Oracle BI Presentation Catalogs.

You can use Catalog Manager to:

-          Manage folders, shortcuts, and objects (analyses, filters, prompts, dashboards, and so on). For example, you can rename and delete objects, and you can move and copy objects within and between catalogs.

-          View and edit catalog objects in Extensible Markup Language (XML).

-          Preview objects, such as analyses and prompts.

-          Search for and replace catalog text.

-          Search for catalog objects.

-          Create analyses to display catalog data.

-          Localize captions.

2 Catalog Modes:

i)                    Online Mode:

Presentation Services and the web server must be running for you to open catalogs in online mode.

ii)                  Offline Mode:

In offline mode, you connect to a local file system. In this mode, you are logged in as a super user or system user, and no permissions are applied. You can see all objects in the catalog.

Generally, working in offline mode is faster than working in online mode. This is because you are accessing, creating, and updating the individual files directly, and the catalog does not have to communicate with Presentation Services as it does when you are working in online mode

Patching catalogs using the catalog manager command line interface

High level Steps:

  

   1)      Copy the development(Current) and Original(Original) catalogs to an accessible location.

(For e.g c:\catalogs)

   2)      Using the command prompt change the directory to the location of the runcat.cmd directory.

The runcat.cmd is located in the catalogmanger

                The Location is:

c:\Oracle\instances\instance1\bifoundation\OraclePresentationServicesComponents\coreapplication_obips1\catalogmanager

   3)      Run the file to create the diff file:

Runcat.cmd - cmd diff –baseline c:\catalog\orginial -latest c:\catalog\developement -outputFile c:\diff.txt - folder /shared -verbosity detail -winsConflict latest

You should get a message indicating that the diff was performed successfully:

Diffing c:\catalog\original ..2..1..Done!

Diffing c:\catalog\development ..2..1..Done!

   4)      Create a patch file using the diff file

Runcat.cmd -cmd createPatch -inputFile c:\diff.txt -outputFile c:\patch.txt -production c:\catalog\production -winsConflict latest -folder /shared

   5)      Then, the patch file is applied to the Production Catalog

C:\oracle\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager>

Runcat.cmd -cmd applyPatch -inputFile c:\patch.txt -outputFile c:\patch_results.txt -folder /shared

                The console then shows the (verbose) output:

Merging ..5

..4

..3

..2

..1..Done!

Check the contents of the output file and see what has been changed in the production repository

You can also use the Catalog manager to check new items that have been added by the patching process

OBIEE 11g How to enable BI Composer

ENABLE BI COMPOSER

1.      Log in to the Analytics

2.      Click on the username-> My Account

3. In My account dialog page click on the Preferences Tab

4. In Analysis Editor option, Select the Wizard (limited functionality). Click OK

5. Create a new Analysis

6. The BI composer window opens up

OBIEE 11.1.1.7 new features

1. BEST VISUALIZATION

Best Visualization is the process of creating a view according to the data. For e.g if I have a table, the best visualization creates a view.
- Select the columns needed

- Click on results. You will have a table of which columns you have selected.

- Click on NEW VIEW and select BEST VISUALIZATION.

- A view will be automatically created for you.

2. RECOMMENDED VISUALIZATION

In this view, you can select the best view for your analysis. Views can be of graphs, pie chart, bar diagram etc. The following view (Recommended Visualization) is helpful because we can compare and analyze the data based on the different views.

a) Analyzing trends
b) Comparing values
c) Comparing values and their parts
d) Comparing Explicit value
e) Comparing Percentages
f) Discovering patterns and outliners

For example:

- Here I have created a stacked bar diagram.

3. PERFORMANCE TILE

This view type displays a single aggregate measure value in a manner that is both visually simple and prominent. For Example:

4. BREADCRUMBS

• Breadcrumbs help users to understand the current location within Oracle BI content and the path that they have used to navigate Oracle BI content. 
• Breadcrumbs are displayed at the bottom of the page, and users can click a breadcrumb or the breadcrumb overflow button to navigate to a specific location on their breadcrumb trail.

5. ENHANCEMENT TO GRAPHS

- Waterfall Graph

- Stacked Graph

- 100% Stacked Graph

6. ACTION LINKS

- First select the columns you would like to add in your analysis
- Under any selected columns click on column properties.

- Once in column properties, go to the Interaction tab and in the Value section select ACTION LINK.
- An Action link can 

• Navigate to BI Content
• Navigate to Web Browser etc.

- Make sure Enable on Totals is enabled.

- Once an action link is created right click on the column and navigate to the action link.

7. ENHANCEMENT TO DASHBOARD (Create custom layout)

We can create custom print layouts for high-fidelity printing of dashboard pages. Custom print layouts allow your end users to produce high-quality printed dashboard content.

When you create a custom print layout, the dashboard page is exported to BI Publisher and the following items are generated automatically:
- A BI Publisher Report with a layout based on the exported dashboard layout.
- A data model to retrieve data for the dashboard page components.
- BI Publisher Report Editor opens in a new browser window with the auto generated layout displayed as a thumbnail. The report editor allows you to edit or delete the layout, or add a new layout.

NOTE:

1. If you delete the data model or the layout manually from the Oracle BI Presentation Catalog, then the associated BI Publisher report will no longer work and the custom print layouts will no longer be available. If you delete an analysis, then the data model and layout will be available but will fail when run.
2. As you create custom print layouts, be aware that some customization's and some views may not be supported in BI Publisher. Among some of the items that are not supported are hierarchical columns, performance tiles, map views, trellis views, and dashboard prompts. If an item is not supported, it will be removed from the layout and a message will be displayed as to the reason it is unsupported.

Once you have saved the custom print layouts in BI Publisher, they are available for that dashboard page and appear in the Custom Print Layouts area of the as follows:

To create one or more custom print layouts for a dashboard page:
1. Edit the dashboard that contains the dashboard page for which you want to create a custom print layout. 

2. Navigate to the dashboard page.

3. Click the Tools button and select Print Properties. The Print Options dialog is displayed.

4. Click the Custom Print Layouts button and select Create Layouts. BI Publisher Report Editor Opens (in a new browser window) with the auto generated layout displayed as a thumbnail.

5. In BI Publisher:

• Edit the layout as desired and save it. 
• Create additional layouts, as desired. 
• Exit BI Publisher. 

6. If you want to make custom print layouts available in the Print menu for the dashboard page, display the Print Options dialog by clicking the Tools button and select Print Properties.
7. Select the custom print layouts that you want to make available.
8. Click OK.
9. Save the dashboard.

OBIEE Archiving and Un-archiving

Archiving

Archiving is a way of zipping your catalog folders which consist of analysis, dashboards, KPI’s etc and copying them to another instance. For e.g from development to production.

Un archiving

Un Archiving is the process of unzipping the archived files in your catalog. The best example is: Moving catalog objects from one environment to another.

Going through the steps it will be much clearer.

Step 1

First select the folder which you want to archive. For this example, I have chosen Development Folder.

I created a new folder and named it as Development Folder and copied all necessary reports, analysis and dashboards into that folder. 

Step 2

Select the development folder and in the task box select archive to zip the files.

Select the keep permissions and the keep timestamps boxes if needed.

After clicking ok, It will ask you to save the file. Save the file in a desired location.

Step 3

Once the file is saved, you can un-archive it into any environment.

Again, go to the task box and select un-archive. Before un-archiving make sure where you want to save your folder in the catalog.

Browse the file which was archived and click ok.

Once you click ok, the folder will appear in where you want to save it.

Then you can open your reports, analysis, and dashboard in another environment or the same environment.

OBIEE 11g Act as Functionality (Proxy User)

The act as functionality is a proxy authentication functionality which authorize a user to act as another user when navigating in Presentation Service.

When a user (called the proxy user) acts as another (called the target user), the proxy user can access the objects in the catalog for which the target user has permission.

Enabling a user to act for another is useful, for example:

1.       when a manager wants to delegate some of his work to one of his direct reports

2.       When IT support staff wants to troubleshoot problems with another user’s objects.

Brief Steps:

1.       Defining the Association Between Proxy Users and Target Users

2.       Creating Session Variables for Proxy Functionality

3.       Creating a Custom Message Template for Proxy Functionality

4.       Modifying the instanceconfig.xml File for Proxy Functionality

5.       Assigning the privilege and restarting the BI Presentation Service

Step 1. Defining the Association between Proxy users and Target users

Create table in database 

CREATE
TABLE OBEE11G_PROXY
(
PROXY_USER_ID   VARCHAR2(30 BYTE) NOT NULL ,
PROXY_TARGET_ID VARCHAR2(30 BYTE) NOT NULL ,
PROXY_LEVEL     VARCHAR2(10 BYTE) NOT NULL ,
CONSTRAINT OBEE11G_PROXY_PK PRIMARY KEY ( PROXY_USER_ID , PROXY_TARGET_ID )
ENABLE
) ;

PROXY_USER_ID: ID of the proxy user
PROXY_TARGET_ID: ID of the target user
PROXY_LEVEL: Proxy level (either full or restricted). A Restricted level gives you only a read access.

The table should be as follows-

PROXY_USER_ID	PROXY_TARGET_ID	PROXY_LEVEL
Ronald	Edward	full
Timothy	Tracy	restricted
Jeanne	Natalie	full
William	Kelly	restricted
Gail	Michael	restricted

Step 2. Create Session variables

·         Import the physical table in the Repository

·         Create a session variable PROXY

·         Create a session variable PROXYLEVEL

Step 3. Creating a Custom Message Template for Proxy Functionality

In this step create “LogonParamSQLTemplate.xml” file and place it under “<Middleware Home>\Oracle_BI1\bifoundation\web\msgdb\customMessages”-

My Location- C:\Oracle\Oracle_BI1\bifoundation\web\msgdb\customMessages

Note : If you dont find the folder ‘customMessages’ , then create create folder with the name ‘customMessages’ and place the ‘LogonParamSQLTemplate.xml’ file.

The content for LogonParamSQLTemplate.xml file is as follows-

<?xml version="1.0" encoding="utf-8" ?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">

 <WebMessageTable system="SecurityTemplates" table="Messages">

   <WebMessage name="LogonParamSQLTemplate">

      <XML>

       <logonParam name="RUNAS">

         <getValues>EXECUTE PHYSICAL CONNECTION POOL "VARIABLE" select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}'

</getValues>

         <verifyValue> EXECUTE PHYSICAL CONNECTION POOL "VARIABLE" select PROXY_TARGET_ID from OBIEE_PROXY where PROXY_USER_ID='@{USERID}' and PROXY_TARGET_ID='@{VALUE}'

</verifyValue>

         <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL "VARIABLE" select PROXY_TARGET_ID, PROXY_LEVEL from OBIEE_PROXY where PROXY_TARGET_ID='@{USERID}'

</getDelegateUsers>

       </logonParam>

    </XML>

  </WebMessage>

 </WebMessageTable>

</WebMessageTables>

Step 4- Modifying the instanceconfig.xml File for Proxy Functionality

Between the <ServerInstance> node, you can insert:

<LogonParam>
<TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
<MaxValues>100</MaxValues>
</LogonParam>

The name that you specify in the <TemplateMessageName> element must match the name that you specify in the <WebMessage> element in the custom message file.

Step 5- Assign the priviliges and restart the BI presentation services

·         Log on to the Presentation services.

·         Click on the administration Tab.

·         Under security click on Manage Priviliges.

·         Give Act As Proxy permission to the user.

Step 6- Log on to the presentation services as the user who has the ACT AS PROXY permission. Click on the Username-> Act As

Now the user should be able to act as the users(PROXY_TARGET_ID)

OBIEE 11g Configure Writeback

Write Back

• It provides users of a dashboard page or an analysis with the ability to modify the data that they see in the table view. 
• It is the ability in OBIEE to allow the user to enter a value or values directly into a report and have those values written into the database.
• Values are written back to an actual physical table in the database.
• Only if the user has the “Write back to database privilege”, then the writeback fields in the reports will editable.

Steps to configure write back:
1.    Create a physical table with write back columns.
2.    Import the write back table.
3.    Enable write back for the connection pool.
4.    Enable write back for logical columns.
5.    Set the write back permission’s in the presentation layer.
6.    Enable write back in the instanceconfig.xml file.
7.    Create the write back template.
8.    Store the write back template.
9.    Grant write back privileges.
10. Create an analysis with columns enabled for write back.
11. Override the default data format.
12. Enable write back in the table view.
13. Verify results.

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml

Step 1: In the Physical Layer

• Select a table to which you would like to writeback (for now we are doing product)
• Right click
• Check “Override Source Table caching properties” in order to uncache.

Step 2: In the BMM Layer

• Go to the table column (same as the physical layer)
• Double click column to open the logical column properties (Product Column)

•  Under General Tab – check Writeable

Step 3: In the Presentation Layer

• Double click the column product and click on Permissions in the General Tab
• Apply Read/Write to a user

• Drag the product key to the product table in the presentation layer.

(Note: Product key is necessary in order to writeback since we are making changes in the product column)

Step 4: Give Permission to the User/Application Roles

• On the top page Click Manage – Identity

• Click on Application Roles on the right pane and then double click on the User (In our case we have   BIAdministrator)

• Click on permissions and then the Query limits tab
• Scroll to the end right and in the Execute Direct Database Request drop down and make it to ALLOW

• Save the repository and click Yes, on Check Global consistency.

Step 5: Make Changes in XML

• Open the instanceconfig.xml from the following path

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\ instanceconfig.xml

Add: <LightWriteback>true</LightWriteback>

To the XML file between <ServerInstance>

Step 6: Go to OBIEE

• Click on Administration
• Under Security click on Manage Privileges
• Scroll all the way down to Write Back , Under Write back to database click Authenticated User

• On the right pane Permission drop down and select Granted.
• Similarly,  under Manage Write Back click BI Administrator Role
• On the right pane Permission drop down and select Granted

Deploy the rpd since changes were made.

Step 7: OBIEE Analysis

• Create Analysis on the same subject Area
• Under Products double click Product key,  Product and Revenue

In the Selected columns on the right pane you should have Product key, Product and Revenue columns

In Criteria, selected columns Click Product Column Properties and click Write Back tab

• And Check Write Back with Text Field Width n no. of columns.
• Click OK and then go the results tab
• In the compound layout Click on edit view and table view properties.
• Go the Write back tab and enable write back and Name the Template

Step 8: Create a xml file (writeback.xml)

<?xml version="1.0" encoding="utf-8" ?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">

<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

<WebMessage name="testwriteback">

<XML>

<writeBack connectionPool="Connection Pool">

<insert></insert>

<update>

UPDATE SAMP_PRODUCTS_D SET PROD_DSC=’@2’ WHERE PROD_KEY=@1

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

</WebMessageTable>

</WebMessageTables>

OR

<?xml version="1.0" encoding="utf-8" ?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">

<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

<WebMessage name="WriteBack">

<XML>

<writeBack connectionPool="Connection Pool">

<insert></insert>

<update>

UPDATE SAMP_PRODUCTS_D SET PROD_DSC = '@2' WHERE PROD_KEY = @1

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

We have selected 3 Columns in our analysis,

Product key, product and revenue.

Since product key is the first column it will be named as @1

Product as @2 and revenue as @3 respectively.

Save the XML as WriteBack.xml (Choose all files) and save it under

C:\BI11g\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

Go back to the analysis and rename any of the products and check in the sql developer.

OBIEE 11g Query Log Levels

Logging Levels	Logging Level information  That is Logged
Level 0	No Logging
Level 1	Logs the SQL statement issued from the client application. Also logs the following: ■ Physical query response time — The time for a query to be processed in the back-end database. ■ Number of physical queries — The number of queries that are processed by the back-end database. ■ Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).  ■ DB-Connect time — The time taken to connect to the back-end database. ■ Query cache processing — The time taken to process the logical query from the cache.  ■ Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this difference in time is negligible, the elapsed time equals the response time. ■ Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking, as described in Section 9.3, "Description of the Usage Tracking Data." ■ Compilation time — The time taken to compile the logical query.  ■ For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.
Level 2	Logs everything logged in Level 1  Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application
Level 3	Logs everything logged in Level 2  Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails
Level 4	Logs everything logged in Level 3  Additionally, logs the query execution plan.
Level 5	Logs everything logged in Level 4  Additionally, logs intermediate row counts at various points in the execution plan.
Level 6	Not Used
Level 7	Not Used