Working with BI Composer

In last post, I explained about installing & configuration of BI Composer. (Installing BI Composer)

Lets concentrate on exploring the features of BI Composer.

BI Composer component is available in modes : 

• Regular Mode -
• Accessible Mode - Accessible mode has same feautures except it is optimized for screen reading mode.
• Also, need to enable "Access to BI Composer" priviledge from the Admin/Manage Priviledges.

Example of BI Composer screen features :

Lets create a simple analysis using available options.

A. Click on New Analysis and select Subject Area (Sample Sales Lite). As we chose "wizard option" earlier from My Account, OBIEE will directly jump to BI Composer screen. First screen is displayed above.

B. Select Columns : In this step, you first select the columns that should be included in analysis.

Also, we can specify :

• Specify column interactions
• Specify/Edit a column formula
• Rename a colum

For Example, I have selected Product, Year, Company, Actual Unit Price, Avg Order Size, # of Orders. We can specify Drill and rename the column name from this step. Column Formula can also be changed as shown below.

C. Select Views : In this step, we will choose the views (Table/Pivot or/and Graphs) which can be added to the analysis. 

D. Edit Table - This option will provide options to change the properties of the Table view.

For example we can add following :

• Create prompts/Sections
• Use a column to create a section of the analysis
• Exclude certain columns from the tabular view.
• You can also preview the results.
• Arranging of the columns can be done only from the left pane properties.

E. Edit Graph : Similar to Edit Table, this option will provide options to edit the graph which is selected earlier.  Following options are avaialable.

Create prompts/Sections

Use a column to create a section of the analysis

Exclude certain columns from the tabular view. 

You can also preview the results.

Arranging of the columns can be done only from the left pane properties.

Change the Chart Sub Type (Ex. changing Horizontal -> Vertical,Stake bar)

F. Sort and Filter — In this step you apply sorting and filters to the views.
Sorting can be applied on the selected columns only, while you can add additional column for filtering the analysis. You can also add "Saved Filter" for this option.

G.Highlight — In this step you apply conditional formatting to the tabular view.

H. Save - Save the report in the new folder/existing folder.

I. View the Analysis -  Navigate to Catalog folder and view the analysis.

BI Composer is a new concept and I believe, this feature will be enhanced significantly in future releases. Still, it is powerful for the end user's analysis who do not want to get into the complexity of Answers development.

cheers..

BI Composer Installation & Setup

BI Composer is a simple-to-use wizard that allows you to quickly and easily create,

edit, or view analyses without the complexities of the "Analysis editor". 

BI Composer tool reminds of "Query Studio" of Cognos 8 Suites. This is mainly designed for Adhoc analysis where end users wants to develop queries/analysis on-the-fly.

Main Components of BI Composer are :

■ On the Top, Buttons for each step in the wizard.

■ On the left are the Catalog tab and the Subject Areas tab.

■ To the right of the tabs is the panel area, where the components for each step are

displayed.

■ To the far right are the Back, Next, Finish, and Cancel buttons.

  

We will cover all the steps in next blog, "Working with BI Composer". Lets focus on the installation/configuration first.

There are two basis steps required for the installation.

1. Extend the Oracle WebLogic Server domain to include the BI Composer application and the BI Composer run-time implementation shared libraries

2. Configuring the instanceconfig.xml file on the installed computer and on any scaled-out computers

Steps :-

A. Stop/Shutdown the server. (Including weblogic/OPMN)

B. Start the Oracle Fusion Middleware Configuration Wizard from Command/Unix located at

cd MW_HOME\wlserver_10.3\common\bin\config.exe

This will open up a new window for extending weblogic domain.

C. Select the Extend an existing WebLogic domain radio button

D. Select a WebLogic Domain Directory and Click Next.

MWHOME/user_projects/domains/bifoundation_domain

 

E. In the Select Extension Source page select the Extend my domain using an existing extension template radio button.

F. Click Browse and then select the oracle.bicomposer_template_11.1.1.jar file in the
following location: 

MWHOME/BI_HOME/common/templates/applications/oracle.bicomposer_tem plate_11.1.1.jar/ 

G.  Click Next -> Next -> Next -> Next -> Next. You don't need to specify/check any options from the list of Data sources/Test/Optional Configurations.

H. Click Done when Process is completed.

I. Add BI Composer Element in InstanceConfig.xml.

<ServerInstance>

.......

<BIComposer>

<Enabled>true</Enabled>

</BIComposer>

.......
</ServerInstance>

J. Start the server ( Admin/Managed/OPMN).

K. BI Composer is now available in Oracle Business Intelligence.


L. Login to OBIEE Application. Go to My Account and click on Wizard(limited functionality) under Analysis Editor option. 

M. Now Edit the Analysis/Create a New Analysis, which will open BI Composer screen for quick analysis.

In next blog, we will discuss about basic features of BI Composer.

Cheers..

Generating Metadata Dictionary

Metadata Dictionary is helpful for end users to retrieve more useful information about how metrics/attributes are derived and calculate in RPD layer.  This also gives a clear picture about how the particular object is brought from Physical -> Logical -> Presentation layer.

Key Notes :-

• A metadata dictionary is a static set of XML documents. This document needs to be updated for every change in RPD file.
• Each XML document describes, how such each object such as column, table is derived and its properties between different objects.
• This document can be viewed within OBIEE Presentation Service UI.
• Metadata dictionary files needs to be hosted on Web Server.

Here are the steps to configure and deploy metadata dictionary in OBIEE11g.

1. Open RPD in offline mode. ( Not possible to generate metadata dict. is online mode.)
2. Click on Execute from Tools->Utilities -> Generate Metadata Dictionary.

3. Provide the destination folder path and click OK. Message will be displayed on successful completion

of the process. It may take a while depending upon the size of the RPD.

4. Navigate to the destination folder to make sure that all the XML files are generated under the RPD name of original file.

Deployment of the metadata file

These files need to be deployed to the webserver so that OBIEE can read the files from the accessible location under the web server directory.

A. Copy the metadata_dict files & folder from the the original folder ( in my case, C:\MetaData_Dict) to the server location inside Instance folder.

Ex. $OBIEE_HOME\OBIEE11_6\instances\instanceX\MetaData_Dict

B. As these files need to be deployed to web server, we need to copy WEB_INF folder from

$OBIEE_HOME\OBIEE11_6\instances\instanceX\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes

C. Make sure both WEB-INF and Metadata files are copied under Metadata_Dict folder.

D. Go to weblogic console. Navigate to "Deployment" and select a new "Install"

E. Provide the path of a metadata dictionary and Click "Next".  For Unix/Linux, path will be the "Relative" path of a metadata_dict folder.

F. Choose Install this deployment as an application and click Next. Select I will make the deployment accessible from the following location and Click Next.

G. Navigate to Deployments. Go to metadata_dict in deployments table.Click on Start-> Servicing all requests.Metadata_dict should changed to Active state.

H. Update the instanceconfig.xml file.  

Open the file instanceconfig.xml located at

$OBIEE_HOME\OBIEE11_6\instances\instanceX\config\OracleBIPresentationServicesComponent\coreapplication_obips1

Add relevant entries within </ServerInstance>

<ServerInstance> ....

<SubjectAreaMetadata>
<DictionaryURLPrefix>http://localhost:7001/metadata_dict/</DictionaryURLPrefix>
</SubjectAreaMetadata>
...</ServerInstance>


I. Restart the services. Login to answers and go to the subject area.

G. Select the presentation table/column and Click on the icon for Metadata Dictionary

Note :-
Please make sure that, exact name is given for the physical paty/ Installed application (under console) and instance config entries. These are case-sensitive,too.

If you see path not found error, check the source of the error page. You would be able to see which path is being hit to retrieve metadata information and based on this, make necessary changes on the installed application or instanceconfig paths.

Also, RPD file name on the physical location should be same as the deployed RPD.

Cheers.. 

Customized Links on the Header OBIEE 11.1.1.6

In this blog, I will explain on the steps required to configure the custom links on the header portal. This option is only available with 11.1.1.6 version.

Global Header contains different links/menus like "New, Catalog,Help,etc". With OBIEE 11.1.1.6 version, we can customize the global header and the Get Started section of the Home page for better accessibility and good user experience. These changes do not affect the other links/menu in the global header.

This feature will be very helpful for navigation from OBIEE application to other applications. Examples include, sharepoint link, similar project application related to OBIEE application, Custom Help link (project specific help), Contact Us, FAQ,etc.

For custom links, you can specify various attributes, including the following:

• The text for the link (either a static string or a message name to use for localization).
• A URL to access.
• Whether the page from the URL replaces the current page or opens in a new tab or window that you can name.
• The relative ordering of links in the header.
• An optional icon to use with the link.
• Link access/display privilege.

Steps required to configuration :-

1. Update customscripts.xml located at $OBIEE_Home\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips

2. Insert relevent element details and save the file at same location or other physical location.

3. If file is stored in custom location, update Instanceconfig file to provide location of custom file path.

4. Restart the services.

Example :-

Here is the sample tags which could be added in customscripts.xml file.

<?xml version="1.0" encoding="utf-8"?>
<customLinks xmlns="com.siebel.analytics.web/customlinks/v1">

<link id="l1" name="SharePoint" description="Application SharePoint Link open in new window" src="http://www.microsoft.com" target="blank" >
   <locations>
      <location name="header"/>
   </locations>
</link>

<link id="l2" name="Google Search" description="Google open in named window" src="http://www.google.com/" target="google" iconSmall="common/info_ena.png" >
   <locations>
      <location name="header" insertBefore="advanced" />
   </locations>
</link>

<link id="l3" name="Yahoo" description="Yahoo" src="http://www.yahoo.com" target="yahoo" iconLarge="common/helptopics_lg_qualifier.png">
   <locations>
      <location name="getstarted"/>
   </locations>
</link>

<link id="14" name="Finance Details" description="Navigates to Reuters" src="http://www.reuters.com" target="blank" iconSmall="res/s_blafp/images/report_bankAccount.jpg" >
   <locations>
      <location name="header" insertBefore="catalog" />
   </locations>
</link>

</customLinks>

As shown in above script, following links should get added in global header/Get Started page.

1. Sharepoint link (11) on the global header.

2. Google Search link (12) on the global header which is required to be shown before "advanced" link on the header.

3. Yahoo link (13) which needs to be shown in GetStarted links.

4. Finance portal (14) will navigate to Reuters and this will be shown before "catalog" file.

Link ID is used as a unique ID that specifies the position of the link. We must include IDs for custom links to position them relative to default links.These IDs should not get repeated.

InsertBefore can accept following entries :

Navigation Bar
    catalog
    dashboard
    favorites
    home
    new
    open
    user
Search Bar
    admin
    advanced
    help
    logout

 

Please note that, it is not possible to put InsertBefore for GetStarted links as these can be customized. 

Path of the customscripts.xml file:

According to Oracle documentation, OBIEE should be able to read this file from the same location ($OBIEE_Home\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips). But this did not work for me. (Any suggestions?)

Now, I kept this file on custom location and updated InstanceConfig files as shown below. (before end of ServerInstance.)

Instanceconfig.xml located at $OBIEE_HOME\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

<ServerInstance>
     <CustomLinks>
          <filePath>C:/Custom_OBIEE/customscripts.xml</filePath>
     </CustomLinks>    
</ServerInstance>

Save Instanceconfig.xml and restart the services. Clear browser cache.

We can see all the links added and displayed in the portal under GetStated/Header.

Cheers...

Globally change RPD changes to catalog file - II

In my last blog, I explained all the steps to automate the folder(table) name change in catalog file by using XML Search and Replace.


Now, lets thinks about the complex scenrio where you have sizeable changes in your catalog files and you want to automate this by writing a script. Also, the first option does not give feature to change the specific objects like "Folder name/Formula, Subject Area" only.

Option 2 :- Use "Import from File"

This options has extra features like changing specific part of the report XML code.


Here is the sample XML file.


<?xml version="1.0" encoding="utf-8"?>
<actions>
<action command="textReplace" oldValue="paint" newValue="HoleyShoes" ignoreCase="true"/>
<action command="renameSubjectArea" oldValue="SnowflakeSales" newValue="GG SALES" ignoreCase="false"/>
<action command="renameTable" subjectArea="Paint Exec" oldValue="forecast measures" newValue="GGFCMEASURES" ignoreCase="true"/>
<action command="renameColumn" oldValue="CategoryID" newValue="GG CATID" ignoreCase="false"/>
<action command="renameFormula" oldValue="&quot;Paint Exec&quot;.Measures.&quot;Year Ago Dollars&quot;" newValue="&quot;Paint Exec&quot;.Measures.&quot;GG YAGODOLLARS&quot;" ignoreCase="false"/>
<action command="renameFormula" subjectArea="&quot;Paint Exec&quot;" oldValue="&quot;Products&quot;.Brand" newValue="GGPRODUCTS.&quot;GG BRAND&quot;" ignoreCase="false"/>
</actions>

1. textReplace :- will perform blind search/replace and change every context where the specific text is used. Similart to basic option -1. Be careful while using this option.
2. renameSubjectArea :- This will change only the subject area name from the catalog XMLs.
3. renameTable :- This will change the Table name of the presentation layer (folder).
4. renameColumn :- This will update presentation column name of the folder.
5. renameFormula :- This option will update formulas applied in the criteria.

Once all changes are specified in xml file, save file in .XML format and import the file in the Import option from the XML Search/Replace window.

Example :-

1. Rename the catalog files where Actual Revenue $ is used with Actual Revenue #. (textReplace)
2. Renaming entire subject area from Sample Sales Lite to Sample Sales Lite Version (renameSubjectArea)
3. Rename the files where presentation layer table "Calculated Facts" is used with "Calculated Measures" (renameTable)
4. Rename the files where presentation layer column "Discount Amount" is used with "Discount Amount_1".

Create a new XML file with below syntax. Save it and provide this file as a reference under catalog manager XML Find and Replace utiltiy.

<?xml version="1.0" encoding="utf-8"?>
<actions>
<action command="textReplace" oldValue="Actual Revenue $" newValue="Actual Revenue #" ignoreCase="true"/>
<action command="renameSubjectArea" oldValue="Sample Sales Lite" newValue="Sample Sales Lite Version" ignoreCase="false"/>
<action command="renameTable" subjectArea="Sample Sales Lite" oldValue="Calculated Facts" newValue="Calculated Measures" ignoreCase="true"/>
<action command="renameColumn" oldValue="Discount Amount" newValue="Discount Amount_1" ignoreCase="false"/>
</actions>

Open Catalog manager and perform XML Search and Replace by providing the xml file reference as show below.

We can see the changes after run is completed in the results window.

Now, deploy the changed RPD and restart services. You can see all the changes are reflected in presentation catalog. No impact on the saved reports/analysis.

Similarly, you can use renameFormula option to update specific column formula in the catalog. This may be necessary sometimes as all the calculations may not be directly fetched from repository.

<action command="renameFormula" oldValue="&quot;Sample Sale&quot;.Measures.&quot;Year Ago Dollars&quot;" newValue="&quot;Sample Sales&quot;.Measures.&quot;GG YAGODOLLARS&quot;" ignoreCase="false"/>
<action command="renameFormula" subjectArea="&quot;Sample Sales&quot;" oldValue="&quot;Products&quot;.Brand" newValue="GGPRODUCTS.&quot;GG BRAND&quot;" ignoreCase="false"/>

Remember, dont forget to take backup of originial files. 

Cheers...

Globally change RPD changes to catalog file - I

Many a times we face the situation where any changes on RPD presentation layer, causing lots of rework on web catalog side by manaully changing references of the column/table/subject area.

Instead of maunally changing the references in all the reports, we can use Catalog manager's option of XML Search and Replace for small changes. If there are significant changes, we can explore XML file option to write all the changes in one file and make changes in ONE Go.

Option 1 :- XML Search and Replace

This option is straight forward and easy to use. You should know what is the change required in catalog files. To give you an example, I have changed subject area folder column from Revenue to Actual Revenue.  Shown the change below.

Now I need to change all the references in the catalog files to replicate this change. (I know keeping Alias on the Presentation later will also work, but dont want to keep aliases in my first build).

• Open Catalog Manager. Open catalog file in offline mode (preferred).
• Make sure you have already taken a backup of the code.
• Select the Folder from the catalog tree. (My case, I wanted to change it globally, so selected "Shared"
• Now Navigate to Tools -> XML Search/Replace Option (Ctrl +H)
• New window opens up with options like Old Text/New Text.
• Provide the change as needed. I have changed from "Revenue" to "Actual Revenue"
• ( Import File Option will be covered in next blog.

 

•  Click OK to start search/Replace operation.
• Once completed, results will be shown with the changed on the reports/analysis in catalog.

Once these changes are completed, deploy the new files and start the services. If this is done online, server restart is not required.

Assuming, server is restarted, I will now login to portal to do sanity check on the changed dashboards.
Here you go. I can see the changes reflected in catalog files and existing dashboards remain intact. :-)

Please note that, this option is not receommended as it might change other part of the code if same name is used for report name, subject area, conditions,etc. Always prefer to go by "Import From File" option.

Now, I will explain how to use "Import from File" in next blog.

Cheers...

How to implement Type Ahead/Auto Complete Functionality

OBIEE 11.1.1.6 has come up with some new features. Auto Complete is one of the new features which is being introduced with this version.

Oracle BI EE provides auto-complete functionality for prompts, which, when enabled, suggests and highlights matching prompt values as the user types in the prompt selection field. Auto-complete can be excluded or included on dashboards, and users can turn auto-complete off for their accounts.

Constraints :-

1. Option is available with OBIEE 11.1.1.6 version only.
2. Available only for the Choice List prompts.
3. Must select the "Enable user to type values" in the prompt dialog.
4. Auto Complete is not available for hierarchical prompts.

Options available are :-

1. case-sensitive or case-insensitive
2. Matching level can be set up as Starts With, Word Starts With, Match All.

Steps to configure Auto Complete Option :

A. System Settings 

Administrator will modify the system configuration file to enable this option. 

When  this functionality is turned on, all users can view this functionality.

1. Open the instanceconfig.xml file located at $OBIEE_HOME\instances\instance6\config\OracleBIPresentationServicesComponent\coreapplication_obips1\ instanceconfig.xml
2. Include elements and their ancestors as shown below. (should be put before end of </ServerInstance>

 

Syntax :-
<Prompts>

<MaxDropDownValues>256</MaxDropDownValues>

<AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>

<AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>

<AutoCompletePromptDropDowns>

<SupportAutoComplete>true</SupportAutoComplete>

<CaseInsensitive>true</CaseInsensitive>

<MatchingLevel>MatchAll</MatchingLevel>

<ResultsLimit>50</ResultsLimit>

</AutoCompletePromptDropDowns>

</Prompts>


Matching level can be set as “MatchAll “, “StartsWith”, “WordStartsWith”

ResultsLimit: - Specifies the number of matching values that are returned when the auto-complete functionality is enabled.

 3. Save your changes and close the file.

 4. Restart Oracle Business Intelligence.

B. Dashboard Settings : 

While creating a new prompt, choose user input as choice list and click on Enable user to type values. Save the prompt and Navigate to Dashboard.


C. Dashboard Execution :

As shown above, when user starts typing the values, OBIEE will generate matching values and display in the drop
downlist. Goal Achieved :-).

Now, I will describe what if we only need to enable it for specific dashboard/user.

D.Steps to disable the functionality from dashboards:


 1. Dashboard Settings:

     This functionality can be disabled for an individual dashboard as shown below.

    i. Click on Edit Dashboard -> Dashboard Properties

       ii.      Change Prompts Auto Complete as “Off”

  

      2 User Level Settings: - Individual user can set turn off this feature by going to  

                                                   My Account as shown below.

      Click on My Account -> Preferences. Select Prompt Auto Complete as Off.

Cheers... I hope this helps..