OBIEE 11g: How To Prevent Users from Deleting My Folder

In OBIEE 11g, how can you stop users from deleting My Folders in the web catalog?

1. Log in to OBIEE as Administrator.
2. Click on the Catalog link.
3. Click on Admin View.
4. Click on Catalog Root.
5. Under User Folders, select More > Permissions.
6. Add Authenticated user role if it is already not present.
7. Set Permissions to Custom.
8. In the pop-up box, make sure the Delete option is unchecked.
9. Check the following two flags after setting the permission to custom:
   1. Apply permissions to sub-folders.
   2. Apply permissions to items within folder.
10. Save the changes.

-Asifkhan P.

Configure OBIEE To Generate A Single SQL Query Instead Of Multiple Queries.

When OBIEE translates logical sql into physical SQL, the algorithm is designed to determine the optimal commands to be executed in the data source. However, there are times when OBIEE will generate multiple sql commands, when a single SQL statement would improve performance.  Subrequest Caching controls whether or not multiple queries can be generated. By default, Subrequest Caching is set to allow multiple queries.

There are 2 ways to configure Subrequest Caching to execute only a single command:

@ Single Analysis Level:

- Edit the Analysis.

- Click on the Advanced tab.

- Add the following clause, in the prefix field:

      set variable DISABLE_SUBREQUEST_CACHING=1;

  and then click on the 'Apply SQL' button.

@System Level default:

- Edit the NQSConfig.ini file (FMW_HOME\instances\instanceX\config\OracleBIServerComponent\coreapplication_obisX).

- In the 'CACHE' section, set:

     DISABLE_SUBREQUEST_CACHING = YES

- Bounce the system.

-Asifkhan P.

Oracle Middleware Learning Stream for OBIEE Videos from Oracle University Tutors

Recently Oracle given this beautiful link, which has collection of videos from Oracle University tutors. BTW, you can view videos for all oracle technologies including OBIEE.

Link : http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=724&get_params=streamId:22,p_searchWords:OBIEE,p_pageNumber:1

Enjoy :)


-Asifkhan P.

BI Validator (OBIEE Testing utility) download link

BI Validator is the most comprehensive Regression, Performance and Stress Testing tool for Oracle BI Applications (OBIEE). Its easy to use Test Plans empower Business Analysts, Testers and Developers to quickly create and execute tests in a matter of minutes without the need to do any custom programming.

Download link :  http://datagaps.com/bi-validator-download/viewcategory/4

Youtube Link : https://www.youtube.com/watch?v=0mCS4d_0RRw  

I still need to download and test it :)

-Asifkhan P.

Oracle Data warehousing best practices (Whitepapers & Demos)

Oracle Data warehousing best practices page on OTN: http://www.oracle.com/technetwork/database/bi-datawarehousing/dbbi-tech-info-best-prac-092320.html which has the following whitepapers, webinars and presentations:

Best Practices :

·	Managing Optimizer statistics in a Data Warehouse on Oracle Database 11g (blog entry)
·	Performant and Scalable Data Loading with Oracle Database 11g
·	6-part webinar series: Third Normal Form Schema, Star Schema, Data Loading, Parallel Execution, Optimizer and Statistics Management, Partitioning,
·	Best practices for a Data Warehouse on Oracle Database
·	Best Practices for Workload Management of a Data Warehouse on the Oracle Exadata Database Machine
·	Enabling Pervasive BI through a Practical Data Warehouse Reference Architecture
·	Best Practices for Implementing a Data Warehouse on the Oracle Exadata Database Machine
·	Best Practices for Deploying a Data Warehouse on Oracle Database 11g (Recording)
·	Best Practices for Extreme Performance with Oracle Data Warehousing (OOW 2009)
·	Best Practices for Deploying a Data Warehouse on Oracle Database 11g (OOW 2008)
·	High-Speed Data Loading (OBE)
·	How to Use Oracle Data Warehousing Functionality (OBE)
·	Deep Dive on Oracle Partitioning (OBE)
·	Conducting a Data Warehousing Benchmark
·	Security and the Data Warehouse

Demo & How-To :

• High-Speed Data Loading (OBE)
• How to Use Oracle Data Warehousing Functionality (OBE)
• Oracle Partitioning in the Oracle Learning Library (OBEs)

6-part Webinar Series on Data Warehousing Best Practices :

This new six-part series on best practices for data warehousing explains in detail the technologies available in the Oracle database to optimize every aspect of your data warehouse from schema design, to data loading, and query performance:

• Third Normal Form Schema 
• Star Schema 
• Data Loading 
• Parallel Execution 
• Optimizer and Statistics Management 
• Partitioning

Big thanks to Oracle for providing collaborated info at one place!!!

-Asifkhan P.

Troubleshooting Oracle BI EE Connections to OLAP

This paper summarizes how Oracle BI EE connects to Oracle OLAP for both metadata administration and query. It then provides troubleshooting tips for common issues with those connections. 

http://www.oracle.com/technetwork/database/options/olap/troubleshootingbieeconnections-504856.pdf

Thanks to Oracle!!

-Asifkhan P.

OBIEE 11g Security Demo Link

This is very useful link, nicely explained about OBIEE 11g Security. Thanks to Integrigy !!

https://www.youtube.com/watch?v=kgA9Ly56rRc

-Asifkhan P.

OBIEE 10G/11G - BI Server Command-line Utilities

You can use a variety of command-line utilities with the Oracle BI Server to make programmatic changes to your repository file, run sample queries, delete unwanted repository objects, and perform other tasks.

OBIEE have several executables files which permit you to automate some actions.

In 11g, before running any of the Oracle BI Server command-line utilities, you must first run bi-init.cmd (or bi-init.sh on UNIX)

Utility	11G	Description	Note
nqcmd.exe	nQcmd.exe	-	To run queries against other ODBC data sources than the Oracle BI Server.
OBIEE 11G/10g - Connection Pool Password Change	10G/11G	Utility to change the passwords	-
nqsecudmlgen.exe	nQSecUDMLGen.exe	-	-
nqsshutdown.exe	nQSShutDown.exe	Utility to shut down the Oracle BI Server	-
nqudmlcli.exe	nQUDMLCli.exe	-	-
nqudmlexec.exe	nQUDMLExec.exe	udml Executor	This command line tool can modify the RPD file by combining the fragment you define in the UDML file with the input repository and generate a output repository.
nqudmlgen.exe	nQUDMLGen.exe	udml Generator	can generate the entire repository in a text format
SchConfig.exe	SchConfig.exe	-	You may use the schconfig utility to configure Oracle BI Scheduler.
schshutdown.exe	SchShutdown.exe	-	-
-	SASchInvoke.exe	-	-
admintool.exe	admintool.exe	-	-
nqlogviewer	-	-	-
patchrpd	11G	Used to generate and apply an XML patch file. This utility is especially useful for patching repository files on Linux or UNIX systems
prunerpd	11G	Used to delete unwanted repository objects from your repository file, such as databases, tables, columns, initialization blocks, and variables.
sametaexport	11G	Used to exchange metadata (dimension, hierachy) with the Oracle Database and DB2
extractprojects	11G	Used to extract projects from a given repository.
equalizerpds	10G/11G	Used to equalize objects
nqgenoldverrpd	11G	Repository Downgrade utility
XML utilities (11G) Used to leverage the Oracle BI Server XML API
biserverxmlgen	11g	generates XML from an existing RPD
biserverxmlexec	11g	executes the XML in offline mode to create or modify a repository file
biserverxmlcli	11g	executes the XML against the Oracle BI Server

Where are they located ?

OS	Directory 10G	Directory 11G
Windows	OracleBI_HOME\server\Bin	ORACLE_HOME\bifoundation\server\bin
Unix	$INSTALLDIR/Bin

-Asifkhan P.

Removing Unused Physical Objects

Large repositories use more memory on the server and are harder to maintain. Additionally, development activities take longer on a large repository. This utility enables you to remove objects that you no longer need in your repository. You can remove databases, initialization blocks, physical catalogs, and variables.

To remove unused physical objects:

1. In the Administration Tool, select Tools, then select Utilities.
   
2. Select Remove Unused Physical Objects and click Execute.
   
3. In the Remove Unused Physical Objects dialog, from the Type list, select the type of object.
   
4. In the list of objects, verify that only the objects that you want to remove are selected.
   Below the list of objects, the number of selected objects and the total number of objects appears.
   
5. To remove the selected objects, click Yes.

-Asifkhan P.

Generating Documentation of Repository Mappings

The Repository Documentation utility documents the mapping from the presentation columns to the corresponding logical and physical columns. The documentation also includes conditional expressions associated with the columns. The documentation can be saved in comma separated (CSV), XML, or tab delimited (TXT) format.

You can use the Repository Documentation utility to extract Oracle Business Intelligence metadata to a flat file so that it can be loaded into Excel and RDBMS. You can query the resulting file to answer questions such as "If I delete physical column X, what logical columns will be affected?" or "How many places in the business model refer to the physical table XYZ?" Then, you can establish dependency relationships among elements in the repository.

The Repository Documentation utility creates a comma-separated values file or a tab-separated values file that shows the connections between the Presentation and Physical layers in the current repository. This file can be imported into a repository as a Physical layer. Note that the file does not include information about repository variables and marketing objects.

To run the Repository Documentation utility:

1. In the Administration Tool, select Tools, then select Utilities.
   
2. Select Repository Documentation and click Execute.
   
3. In the Save As dialog, choose the directory where you want to save the file.
   
4. Type a name for the file.
   
5. Choose a type of file and an Encoding value, and then click Save. Current encoding options are ANSI, Unicode, and UTF-8.

-Asifkhan P.

Update Physical Layer Wizard utility

You can use the Update Physical Layer Wizard to update database objects in the Physical layer of a repository, based on their current definitions in the back-end database. This wizard is not available for repositories that are open in read-only mode, because they are not available for updating.

When the wizard processes the update, the Oracle BI Server connects to each back-end database. The objects in the Physical layer are compared with those in the back-end database. Explanatory text alerts you to differences between objects as defined in the database in the Physical layer and as defined the back-end database, such as data type-length mismatches and objects that are no longer found in the back-end database. For example, if an object exists in the database in the Physical layer of the repository but not in the back-end database, the following text is displayed:

Object does not exist in the database and will be deleted

The wizard does not add columns or tables to the repository that exist in the back-end database, but not in the repository. Additionally, the wizard does not update column key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values do not match, the wizard updates the type and length of the column in the repository.


The connection pool settings for each database need to match the connection pool settings used when the objects were last imported into the Physical layer from the back-end database.

To update objects in the Physical layer:

1. In the Administration Tool, select Tools, then select Utilities.
   
2. Select Update Physical Layer and click Execute.
   The databases in the Physical layer of the repository are listed in the left pane of the wizard.
   
3. In the Select Database screen, select the databases that you want to update in the left pane, and then click Add. To remove a database from the update list in the right pane, select it and click Remove.
   
4. Click Next.
   
5. In the Select Connection Pool screen, select the connection pool for each database that you want to update and then click Next. You might need to set or confirm values for variables before continuing.
   
6. In the Update screen, review the information about each update and select the updates you want to perform. You can sort the rows (toggle between ascending and descending order) by clicking the Name column heading.
   
7. If you decide that you do not want the wizard to update a particular object in the Physical layer, click the Back button and remove the object.
   
8. When the repository is open in online mode, the Checkout screen appears. In online mode, objects need to be checked out before you can make changes to them. Click Next to check out the necessary objects.
   
9. Click Finish.
   The wizard updates the objects in the Physical layer, and then closes automatically. Objects that do not exist in the database are deleted.
   
10. From the File menu, select Save to save the updated objects in the Physical layer.

-Asifkhan P.

Rename Wizard utility

You can use the Rename Wizard to rename tables and columns in the Presentation layer and Business Model and Mapping layer. It provides a convenient way to transform physical names to user-friendly names. This is very useful utility.

To use the Rename Wizard:

1. In the Administration Tool, select Tools, then select Utilities. Then, select Rename Wizard and click Execute.
2. In the Select Objects screen, select the objects you want to rename. First, select the layer that contains the objects (Presentation or Business Model and Mapping), then select an object and click Add. Click Add Hierarchy to add all objects associated with the selected object.
   
   Click Next after you have selected the objects you want to rename
3. In the Select Types screen, select the object types you want to rename, such as Subject Area, Logical Table, or Logical Column. Then, click Next.
4. In the Select Rules screen, select the renaming rules you want to apply and click Add. Select Change specified text to rename particular words or phrases. Click Next after you have selected renaming rules.
5. When the repository is open in online mode, the Checkout screen appears. In online mode, objects need to be checked out before you can make changes to them. Click Next to check out the necessary objects.
6. The Finish screen displays a summary of the objects that will be renamed. If you want to make changes to the list of renamed objects, click Back, or select a particular step from the navigation panel. Click Finish to rename the objects.

-Asifkhan P.

Oracle BI Event Tables Utility

This utility lets you identify a table as an Oracle BI event polling table. An event polling table is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

To start the Oracle BI Event Tables utility:

1. In the Administration Tool, select Tools, then select Utilities.
   
2. Select Oracle BI Event Tables and click Execute.

-Asifkhan P.

Administration Tool Utilities

Administration Tool provides several utilities and wizards that perform functions like renaming objects, persisting aggregates, and externalizing strings.

Utilities :

• Replace Column or Table Wizard
  
• Oracle BI Event Tables Utility
  
• Externalize Strings Utility
  
• Rename Wizard
  
• Update Physical Layer Wizard
  
• Repository Documentation 
  
• Generating a Metadata Dictionary
  
• Removing Unused Physical Objects
  
• Aggregates Persistence 
  
• Oracle BI Summary Advisor Wizard

-Asifkhan P.

Initialization blocks Execution Precedence in OBIEE 11g

Execution Precedence:

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

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.

To establish execution precedence:

1. In the Administration Tool, select Manage, then select Variables.
   
2. In the Variable Manager dialog, double-click the last initialization block that you want to be initialized.
   
3. In the [Repository|Session] Variable Initialization Block dialog, click Edit Execution Precedence.
   
4. In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add.
   Add is only available if there are initialization blocks that have not yet been selected.
   
5. In the Browse dialog, select the blocks that should be initialized before the block that you have open, and then click OK.
   
6. To remove a block, in the [Repository|Session] Variable Initialization Block Execution Precedence dialog, select the block you want to remove and click Remove.
   
7. Click OK.
   
8. If you want the initialization block to be required, in the [Repository|Session] Variable Initialization Block dialog, select the Required for authentication option.
   
9. Click OK.

Screen Shot :....

-Asifkhan P.

Merging and Importing Objects from Different Repositories by Using the Oracle BI 11g Administration Tool

Merging and Importing Objects from Different Repositories by Using the Oracle BI 11g Administration Tool :

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115/import_merge/merging_importing_11g.htm


-Asifkhan P.

Agents (or iBots) in OBIEE 11g

Oracle BI delivers:

• Oracle BI delivers are useful to create schedules (or) Agents.
• Automates Business Intelligence
• Creates alerts based on Business Intelligence results
• Detects specific results and immediately notifies the appropriate person or group through Web, wireless, mobile, and voice communications channels.

1) Configuring scheduler tables:

• In 11g configuring of Oracle BI Scheduler Server will be done with the installation Of OBIEE 11g product [in 10g it is manual process]
• Scheduler tables by default created in a schema DEV_BIPLATFORM with installation of RCU(Repository Creation Utility).

2) Scheduler tables are

• S_NQ_JOB
• S_NQ_JOB_PARAM
• S_NQ_INSTANCE
• S_NQ_ERR_MSG

3) Creating delivery device:
Device will be created by each & every user his own

Process:
1) Login to Analytics by using weblogic user
2) Click on weblogic --> click on my account --> click on delivery options --> Click
on Create Device --> name : Testing --> Device Type : HTML
Email --> Adress/Number xyz@gmail.com --> Click on ok

4) Develop a Agent:

Process:
click on New --> under Actionable Intelligence --> click on Agent --> click on
Schedule tab --> select frequency as once --> click on select date & time --> give
10 mins forward from current time --> select Delivery Content tab --> select
Analysis --> click on browse --> select agent report --> click on ok --> Save into
Shared Folder folder with the name of first agent

Other Related Posts:
Find the Status of the iBot Develiry through Back-End Table


-Asifkhan P.

Configure Write Back In OBIEE 11g

  Write back is a feature that allows users to type values into the dashboard and then copy them back to the underlying source database.

Follow the steps to enable it in your environment......

Step1: Enable Writeback at system level.

Add the following line between the tags <WebConfig> and </WebConfig> in the instanceconfig.xml located under $INSTANCE_HOME/config/OracleBIPresentationServicesComponent/coreapplication_obips1:

<LightWriteback>true</LightWriteback>

Step2: Create Template for write back.

Create your own xml for your report and save the file in below location with any name with .xml format.
Path: $INSTANCE_HOME\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

Sample File :  emp.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="SetWriteBackValue">
         <XML>
            <writeBack connectionPool="OBIEEDB_POOL">
              <update>UPDATE SCOTT.EMP SET SAL=@2 WHERE ENAME='@1'</update>
    </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>


Note: OBIEE 11.1.1.5 and above versions already has a template SQL for write back under above location with name writebacktemplate.xml. 


Step 3 : Give the permissions
check and give the write back permissions to the user in Analytics->Administration> Manage Privilege as shown below:

Step 4:  Enable Writable option at logical column level.

Step 5 : Allow Execute Direct Database Requests to the user.

Step 6 : Bounce your OBIPS and OBIS to reflect your changes.

Step 7 :  Now create report with ENAME, SAL columns and go to SAL column properties --> Write Back tab --> and select Enable Write Back check box.

Step 8 :  Finally edit table view properties and check the tab 'Enable write back'.In the template name field,make sure that you have provided the SetWriteBackValue  as documented in WebMessage Tag in emp.xml file

Step 9 :  Then click Update , modify values and click Apply  button. That set, updated values should have gone to database , if you have done above steps correctly :).

Other Related Posts : 
Write-Back Limitations in OBIEE 11G
OBIEE 11g: Writeback Done Button Does Not Refresh Data Displayed


-Asifkhan P.

OBIEE 11g: Writeback Done Button Does Not Refresh Data Displayed

Today I faced this issue in 11g....

After implementing writeback, data is not refreshing at front-end (i.e. report or dashboard), but data is updated in database. To get updated data, we need to purge OBIPS and OBIS cache.

This is a bug in OBIEE 11g, here is metalink prof....


APPLIES TO:

Business Intelligence Server Enterprise Edition - Version: 11.1.1.5.0 [1308] and later   [Release: 11g and later ]
Business Intelligence Suite Enterprise Edition - Version: 11.1.1.5.0 [1308] and later    [Release: 11g and later]
Information in this document applies to any platform.

SYMPTOMS

In OBIEE 10g, after you click on the Update button you could see the results of the writeback in the dashboard. Now, in 11g it does not seem to refresh. You can see the new data in the database but not in the dashboard.

CAUSE
This is expected behavior in 11g. In 11g dashboards do partial refresh because it's more efficient. 

SOLUTION
A dashboard page refresh (from top right Page Options) refreshes all the dashboard's content and shows the new data. 

There is an enhancement request to re-introduce the functionality of OBIEE 10g.  For more information see Bug 13058029 - write back auto-refresh in 11g.

Other Related Posts : 
Configure Write Back In OBIEE 11g
Write-Back Limitations in OBIEE 11G

-Asifkhan P.