Category: Business Intelligence (BI) Development

Business Intelligence, Business Intelligence (BI) Development, Business Intelligence Platform

How to collapse or minimize a dashboard section by default in OBIEE

In OBIEE, you can easily set a dashboard section to be “Collapsible” which allows a user to collapse or minimize that section when desired.  By default, when a dashboard page is opened, the sections are maximized or expanded (i.e., not collapsed).

MaximizedExpandedSection

But what if you have a requirement to have a section minimized or collapsed by default?  There is no flag or option to do that.   This post shows you how to make a section minimized / collapsed by default.

I found the solution for making a dashboard section collapse by default here… http://www.orakelite.com/2013/01/iii-javascriptcss-tips-to-obiee-ui.html
However, the author did not include some details which I will cover here.

First, you need to make the section collapsible, by simply editing the dashboard…
EditDashboard

… and then setting the “Collapsible” property of the relevant section.
CollapsibleProperty

You will need to add the following Java Script to the section you want to minimize by default.
The text highlighted in the Java Script is the Section ID of the section to be minimized by default. So first you will need to find this Section ID.
——–

var sectionId = “d:dashboard~p:ve9fga7bp3omltnr~s:9qfn1ms6bco9bsva“;
var sectionDiv = document.getElementById(“Embed”+sectionId);
var plusImg = document.getElementById(sectionId+”Max”);
var minusImg = document.getElementById(sectionId+”Min”);
var contentsTable = document.getElementById(sectionId+”Contents”);
minusImg.style.display = “none”;
contentsTable.style.display = “none”;
plusImg.style.display = “”;
sectionDiv.setAttribute(“minimized”, “true”);

——–
To determine the value of the Section ID, you need to go to the dashboard page and from the browser menu, select View –> Source.

Search for the section by name, or just search for the word “section”. This should help you to identify what Section ID is related to the section you are interested in. (In example below, the black arrow points to the SECTION ID, and the red arrow points to the user given section name that can be used to search. )

SectionName

Use your Section ID in the Java Script code above.

Place the Java Script code in a “Text” dashboard object inside the section you want to control.

MinimizeSectionJavaScript 

Save your dashboard page changes.

Now when you reopen the dashboard page, the section will be minimized / collapsed by default.

CollapsedSection

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform

How to run Catalog Manager in GUI mode from the Linux Server

OBIEE Catalog Manager can be accessed from a client tool on Windows or from a server side process.  There are times when it is required that you run the Catalog Manager in offline mode, and at those times you will be required to run it on the server.

Although there is a command line option for Catalog Manager, it is easier to work with it in a Graphical User Interface (GUI) mode and at times its necessary to use that mode.

Start Xming (or another X-Windows type tool) on your local PC or laptop. [This will allow the Linux screens to show up on your desktop when you run the shell script on the Linux server]

For more on Xming, visit http://en.wikipedia.org/wiki/Xming

Connect to your Linux server from your desktop/laptop using Putty (or other tool).   Then go to the catalog manager directory:

cd [$FMW_HOME]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager

runcat-start-catalogmanager

Start up Catalog Manager by running:    ./runcat.sh

This will start Catalog Manager, and it will be running on the Linux server, but the screens will be showing on your desktop/laptop.

From the main Catalog Manager window, you can then click on File –>Open Catalog

catalogmanager_interface

And then select the Type based on how you want to connect – Online or Offline mode.  Accessing Catalog Manager in this way is most likely needed when you need to connect in Online mode.

And then of course, you need to enter the URL – which is the full path to the catalog, for example:[$FMW_HOME]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/default

And enter the User and Password.

catalogmanager_onlineoffline_login

Assuming everything was entered correctly, you will be connected to the catalog.  You can then use the interface to browse the catalog and perform other tasks on catalog objects.

catalogmanager_browsing

Good luck!

Business Intelligence (BI) Development, Business Intelligence Platform, Data Integration (ETL)

Informatica Transformations Frequently used in OBIA

These are some of the Informatica transformations that are frequently used in Oracle Business Intelligence Applications (OBIA).  The OBIA SDE and SIL mappings used to load the Oracle Business Analytics Warehouse (OBAW) are built using these and other transformations.

1. Source Qualifier
The Source Qualifier transformation is used to bring data from one or more tables from the same source into the mapping.  If being used for more than one table, then a join condition needs to be defined between the tables.  The typical naming convention for a Source Qualifier transformation is SQ_* or sq_*.

2. Joiner
The Joiner transformation is used to join tables in different data sources.  The typical naming convention for a Joiner transformation is JNR_* or jnr_*.

3. Expression
The Expression transformation is used to perform simple row-based calculations or derivations.  The typical naming convention for an Expression transformation is EXP_* or exp_*.

4. Filter
The Filter transformation is similar to a where clause in SQL – it adds a conditional filter to the data passing through the mapping.  The typical naming convention for a Filter transformation is FIL_* or fil_*.

5. Aggregator
The Aggregator transformation is used to perform aggregate calculations on the data passing through the mapping, for example, performing a sum or max.  The typical naming convention for an Aggregator transformation is AGG_* or agg_*.

6. Lookup
The Lookup transformation is used to lookup values based on another known/submitted value, and pass the looked up value into the mapping.  There are 2 types of Lookups – connected and unconnected.  The typical naming convention for a Lookup transformation is LKP_* or lkp_*.

7. Update Strategy
The Update Strategy transformation is used to determine and perform the appropriate course of action for data in the mapping.  Based on the determined state of the data, the transformation is used to insert, update, delete or reject records.  The typical naming convention for an Update Strategy transformation is UPD_* or upd_*.

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform

Windows command to find equivalent directory name without space

You may occasionally run into an issue with OBIEE where it cannot locate the Java directories or files.  This is usually because Java has been installed in its default location under “C:Program Files…”.  This post may be helpful in resolving this issue.

At times, some OBIEE processes or programs do not like the spaces in directory or file names.  So, for example, you may get an error because the program does not locate the files under the “C:Program Files…” directory (due to the space in the name).

To resolve this, you will need to update the necessary Environment Variables to use the no-space equivalent of the windows directory.  To find the equivalent no-space name,  from your windows command prompt, go to the relevant directory (for example … cd C:) and enter … dir /X (as shown below).

dirX

Notice to the left of the regular directory name, there is another no-space name.  For example, “Program Files” has a no-space equivalent of PROGRA~1.

Now update all the relevant OBIEE related Environment Variables to use this no-space name instead of the regular name (see example below).

envvar

Business Intelligence (BI) Development, Business Intelligence Platform, Data Modeling, Data Security

Oracle E-Business Suite (EBS) Foundation Tables

This post is simply a list of Oracle E-Business Suite (EBS) Foundation tables. The Foundation tables contain data that relate to the entire suite of applications – they are not specific to any one module.

Some foundation tables are used in Oracle BI Applications (OBIA), for example, the FND_USER, FND_USER_RESP_GROUPS, and FND_RESPONSIBILITY_VL tables are used in security-related Initialization Blocks.

In general, it’s good to be familiar with these tables when working on OBIEE projects or any BI platform with EBS as a source system.

Foundation Table Purpose
FND_APPLICATION Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS This table stores output files created by Concurrent Request.
FND_CURRENCIES Stores information about currencies.
FND_DATABASES It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS Stores language-independent information about a document.
FND_EXECUTABLES Stores information about concurrent program executables.
FND_FLEX_VALUES Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES Stores information regarding languages and dialects.
FND_MENUS It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS Stores information about user profile options.
FND_REQUEST_GROUPS Stores information about report security groups.
FND_REQUEST_SETS Stores information about report sets.
FND_RESPONSIBILITY Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL Stores translated information about responsibilities.
FND_RESP_FUNCTIONS Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES Stores information about the registered sequences in your applications.
FND_TABLES Stores information about the registered tables in your applications.
FND_TERRITORIES Stores information for countries, alternatively known as territories.
FND_USER Stores information about application users.
FND_VIEWS Stores information about the registered views in your applications.
FND_USER_RESPONSIBILITY
FND_RESPONSIBILITY_VL
FND_ORACLE_USERID
FND_DATA_GROUP_UNITS
Business Intelligence (BI) Development, Business Intelligence Platform, Data Governance, Data Modeling, Reporting

Oracle E-Business Suite (EBS) List of Flexfields

This post is simply a table listing of the Key Flexfields (KFFs) in Oracle E-Business Suite (EBS) (aka Oracle Applications). The table contains the flexfields, along with their Code, Owning Application and base table. 

Flexfields are commonly used in OBIEE reporting for EBS source systems. Flexfields are also an integral part of the Oracle Business Analytics Warehouse (OBAW) data model. It is worthwhile to be familiar with them.

  Name Code Owning Application Table Name
1 Account Aliases MDSP Oracle Inventory MTL_GENERIC_DISPOSITIONS
2 Accounting Flexfield GL# Oracle General Ledger GL_CODE_COMBINATIONS
3 Activity Flexfield FEAC Enterprise Performance Foundation  
4 AHL Route AHLR Complex Maintenance, Repair, and Overhaul  
5 Asset Key Flexfield KEY# Oracle Assets FA_ASSET_KEYWORDS
6 Bank Details KeyFlexField BANK Oracle Payroll PAY_EXTERNAL_ACCOUNTS
7 CAGR Flexfield CAGR Oracle Human Resources  
8 Category Flexfield CAT# Oracle Assets FA_CATEGORIES
9 Competence Flexfield CMP Oracle Human Resources  
10 Cost Allocation Flexfield COST Oracle Payroll PAY_COST_ALLOCATION_KEYFLEX
11 Grade Flexfield GRD Oracle Human Resources PER_GRADE_DEFINITIONS
12 Item Catalogs MICG Oracle Inventory MTL_ITEM_CATALOG_GROUPS
13 Item Categories MCAT Oracle Inventory MTL_CATEGORIES
14 Job Flexfield JOB Oracle Human Resources PER_JOB_DEFINITIONS
15 Location Flexfield LOC# Oracle Assets FA_LOCATIONS
16 Oracle Service Item Flexfield SERV Oracle Inventory MTL_SYSTEM_ITEMS
17 People Group Flexfield GRP Oracle Payroll PAY_PEOPLE_GROUPS
18 Personal Analysis Flexfield PEA Oracle Human Resources PER_ANALYSIS_CRITERIA
19 Position Flexfield POS Oracle Human Resources PER_POSITION_DEFINITIONS
20 Public Sector Budgeting BPS Oracle Public Sector Budgeting  
21 Sales Orders MKTS Oracle Inventory SALES_ORDER_ID
22 Sales Tax Location Flexfield RLOC Oracle Receivables AR_LOCATION_COMBINATIONS
23 Soft Coded KeyFlexfield SCL Oracle Human Resources HR_SOFT_CODING_KEYFLEX
24 Stock Locators MTLL Oracle Inventory MTL_ITEM_LOCATIONS
25 System Items MSTK Oracle Inventory MTL_SYSTEM_ITEMS
26 Territory Flexfield CT# Oracle Receivables RA_TERRITORIES
27 Training Resources RES Oracle Learning Management  
Business Intelligence (BI) Development, Business Intelligence Platform

OBIEE Repository (rpd) development tips

A few tips on BI Repository (RPD) Development …

Physical Layer

  • Import metadata when possible, instead of creating it manually
  • Only import objects that you need or forsee you may need in the near future
  • Import Tables, Keys, and Foreign Keys – which is the default option
  • Create Aliases of Physical Tables – to eliminate unwanted joins and to satisfy any naming conventions you may have chosen
  • Use the Physical diagram to configure joins
  • Create all relevant joins in a star-schema / dimensional model

BMM Layer

  • Use terminology that users are familiar with – words of the business instead of technical physical table and column names
  • Use the Rename Wizard to rename most of your logical objects when possible (this can save you a lot of time)
  • Delete unnecessary objects from your BMM layer
  • Create derived columns for calculations that you forsee using in multiple reports/presentation areas

Presentation Layer

  • Only move the columns you need users to see and work with to your Presentation Layer
  • Rename objects to match the language of the business and ensure that the meaning is very clear
  • Organize your presentation columns in a logical/meaningful order – for example, in a hierarchical order or alphabetic order such as Product Category, Product sub-category, Product -or- Region, Country, State, City, Zip

General

  • Check Global Consistency & resolve warnings and errors
  • Disable caching during development & testing
  • Verify your design by checking the SQL generated and sent to the database
Business Intelligence (BI) Development, Business Intelligence Platform

‘No fact table exists at the requested level’ error in OBIEE 11g

If you receive an error like this when you attempt to run a report, this post might help you.  This is an error that may occur after upgrading from OBIEE 10g to 11g.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail:

Check each column used in your report and each column used in the filter definition to ensure that they are referencing the correct table.

A likely cause of this error is that one or more of your select columns or filter columns is referencing a column in a different subject area that the one being used in your report (different from the one you intended).

OBIEE 10g was more forgiving than 11g – and maybe fortunately, so that errors like these do not go unnoticed/undetected.

Business Intelligence, Business Intelligence (BI) Development, Business Intelligence Platform

Using Custom Images in OBIEE 10g & 11g

In both OBIEE 10g and 11g, custom images need to be placed in 2 folders before use within reports/dashboards.

 In OBIEE 10g, images need to be placed in:

(1)     $BIHOMEoc4j_bij2eehomeapplicationsanalyticsanalyticsress_oracle10images

Windows directory path example:
C:OracleBIoc4j_bij2eehomeapplicationsanalyticsanalyticsress_oracle10images

Linux directory path example:
/usr/local/OracleBI/oc4j_bi/j2ee/home/applications/analytics/analytics/res/s_oracle10/images

-and-

(2)    $BI_HOMEwebappress_oracle10images

Windows directory path example:
C:OracleBIwebappress_oracle10images

Linux directory path example:
/usr/local/OracleBI/web/app/res/s_oracle10/images

 

In OBIEE 11g, images need to be placed in:

(1)    $OBIEE_FMW_HOMEuser_projectsdomainsbifoundation_domainserversbi_server1tmp_WL_useranalytics_11.1.17dezjlwarress_blafpimages

Windows directory path example:
C:obiee11guser_projectsdomainsbifoundation_domainserversAdminServertmp_WL_useranalytics_11.1.1silp1vwarress_blafpimages

Linux directory path example:
/u01/product/middleware/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/s_blafp/images

-and-

(2)    $OBIEE_FMW_HOMEOracle_BIbifoundationwebappress_blafpimages

Windows directory path example:
C:obiee11gOracle_BI1bifoundationwebappress_blafpimages

Linux directory path example:
/u01/product/middleware/Oracle_BI1/bifoundation/web/app/res/s_blafp/images

Note: you may also create or use directories at the same level or below the images directories under the s_oracle10 (in 10g) and s_blafp (in 11g) directories, for example, if you wanted to put all your custom images into a separate directory.

Then in Answers / Analysis, you would reference the image as shown below.

Edit the Column Properties:
ImagesDirectories-ColumnProperties

Then, in the Style tab, within the Cell area, click on the Image object…

ImagesDirectories-ClickImage

Then in the Graphics dialog, select “Custom Image” and enter  fmap:images/your_custom_image.jpg.

Note: If your images are in a directory other than the images directory, you need to supply that path, but still starting below s_oracle10 or s_blafp directories.

And set the Image Placement property. Images Left (shows the Image to the left of the column data), Images Right (displays the image to the right of the column data), Images Only (displays the image only – no column data)
ImagesDirectories-ImageNameAndPlacement

If new images were placed into the directories after the last BI Server / Presentation Server restart, you will need to restart the BI Server and Presentation Server for the images to be displayed.

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform, Data Development, Reporting

EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set

If you use the Oracle EVALUATE function in OBIEE, then you will need to set a parameter in the NQSConfig.INI file in OBIEE 11g.

If you get this error below, then you are using the EVALUATE function somewhere inside OBIEE (in one of your analyses/requests) and the parameter needs to be set appropriately in the NQSConfig.INI file.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

This is what you need to do to correct the problem… 

Edit the NQSConfig.INI file, located at: OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

This is what you will see by default in the config file…
————-
# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 0; 
————-

Change the parameter as follows: EVALUATE_SUPPORT_LEVEL = 2;

Save and restart the BI Server.  Your requests should work fine now.