OBIEE 11G Upgrade- Considerations

Considerations

• With the latest release of OBIEE 11g (11.1.1.6.6), Oracle Business Intelligence Technology Foundation become best in class in SMB as well as Enterprise organization
• Some new key features that will benefit OBIEE guys in pursuing for OBIEE 11g Upgrade
• Upgrade of an existing OBIEE 10g system to OBIEE 11g requires careful planning, preparation, setup and testing.
• Upgrade of RPD (Repository/Business Model Metadata)
• Upgrade of Webcat (User Interface Metadata)
• Upgrade of BI Pub (BI Publisher)
• Upgrade of BI Security
• Upgrade of BI Delivers (iBots/Agents)

Steps for OBIEE 11g Upgrade

• OBIEE 11g Upgrade in non-invasive to the current production setup, as it does not overwrite the existing 10g architecture
• OBIEE 11g will be installed a-fresh on Dev/QA and Production instances
• Pre-requisites checklist
• Installation architecture – decisions
• Production architecture – decisions
• Installation of OBIEE 11g on Dev/QA Environment
• Upgrading the OBIEE Metadata Repository and Web Catalog
• Importing Security Credentials (Users/Groups) Setup
• Modifying Security Set Up as per 11g Architecture
• Testing the installation set up and all components
• Testing the OBIEE Metadata repository consistency
• Validating ‘select’ OBIEE 10g reports for any errors
• Validating Security Setup and Users/Groups Mapping
• Installation of OBIEE 11g on Production System
• Code Migration to Production
• Smoke Testing on Production System
• Availability of OBIEE 11g on Production

Analyze Existing 10g For Upgrade

• Analyze existing 10g System on following key things
• Hardware including Sizing
• Operating System
• Security data model
• RPD Size
• Web Catalog Size
• Reports – Majority views used (Ex: Pivot, Charts etc)
• Custom UI configuration (Style Sheets / Logos / Messages / Web Templates)
• iBots if any
• Total number of users and Concurrent users of the Application
• External Systems involved for Security of BPEL
• Warehouse Database Schema and Scheduler Schema
• Configuration Files (instanceconfig.xml, NQSConfig.INI, etc)
• Compare existing 10g Environment with System Requirements and Certification documentation of 11g
• Custom Date formats

Pre-Upgrade Steps

• Install and Configure OBIEE 11g System
• System Requirements and Supported Platforms
• Sizing Considerations based on Expert recommendations
• Configure and Setup Out of the Box OBIEE 11g System with Sample Application Repository and Web Catalog
• Validate the Application after installation
• Configure specific parameters (OPMN.xml) for the data sources (Oracle, SQL Server, Teradata etc)
• Configure specific parameters for BI Publisher (xmlp-server-config.xml)
• Configure 11g specific tags in instanceconfig.xml
• Separate Connection Pools for Queries and Init Blocks in Physical Layer of the OBIEE RPD
• Invalid Objects are deleted

Optimize Existing 10g For Upgrade

• Identify Superfluous, redundant and unused content
• Web Catalog Objects:
• Reports
• Dashboards & Dashboard Prompts
• Shared Folders & User Folders
• Web Groups
• RPD Objects:
• Physical Layer Objects
• BMM Layer Objects
• Presentation Layer Objects
• Initialization Blocks
• Variables
• Users
• Security Groups
• Consistency Check before Upgrade
• Leading/Trailing Spaces on Column Names in OBIEE Repository Not Supported in 11g

Test Plan for Upgrade

• Perform a test upgrade – On a smaller Catalog with few selected critical Reports/Dashboards
• Identifying unknown issues during Actual Upgrade
• How does the new system look like after upgrade
• Define different phases/stages for upgrade process
• Upgrade Schemas (Scheduler)
• Configure Connections
• Upgrade Repository
• Upgrade Web Catalog
• Upgrade BI Publisher Catalog
• Upgrade BI Publisher Scheduler
• Perform testing and validate results at the start and end of each stage
• Validate iBots/Agents on new Schema – Fix issues manually if any
• Check TNS Entries for Oracle, opmn.xml for Teradata, Correct version of Essbase client
• Fix all Consistency Errors, Configure Connection Pools
• Manually fix reports/dashboards for known issues
• Configure the parameters in xmlp-server-config.xml
• Validate the Scheduler reports

Post Upgrade Considerations

• Implement New and Enhanced 11g Functionalities
• Support for additional data sources (Ex: Essbase, Oracle TimesTen, Hyperion Financial Management, ADF, SAP BW Native, OBI Metadata Web Service etc)
• Hierarchy Objects in Presentation Layer (Analysis)
• Support Unbalanced Hierarchies (Ragged & Skipped)
• Support Parent-Child Hierarchies
• Delta Code Migration as against full RPD migration after initial go-live
• Multi-Currency Support
• Logical Source determination is no longer Random
• Upgrade MUD Repositories Manually (Use ONLY Command line upgrade utility – “obieerpdmigrateutil”)
• Use ONLY Upgrade Assistant to perform the upgrade of Production Repository and Web Catalog
• Configure Essbase specific database features corresponding to Environment Variables
• Fix Localized Text for Users “My Folder” objects
• Siebel Workflow actions are not upgraded – Perform this task manually
• RPD & Web Catalog deployments now controlled using FMW Control
• Security
• Doesn’t allow Blank Repository Password
• Groups in 10g transform to Application Roles in 11g
• Users/Application Roles are not managed Externally
• Default Cluster Configuration even in Single Server Environment
• Pivot table Calculated Items needs to be fixed manually
• Custom Styles and Skins are not upgraded, Have to be implemented manually again in 11g

OBIEE 11.1.1.6.6 Patch Released

This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X).

•     Patch 15844023 (1 of 7) Oracle Business Intelligence Installer.
•     Patch 15844066 (2 of 7) Oracle Real Time Decisions.
•     Patch 14800665 (3 of 7) Oracle Business Intelligence Publisher.
•     Patch 15843961 (4 of 7) Oracle Business Intelligence ADF Components.
•     Patch 15844096 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x.
•     Patch 14791926 (6 of 7) Oracle Business Intelligence.
•     Patch 15839347 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer

Note:

• The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch.
• This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4 and 11.1.1.6.5 patch sets.
• However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files.
• The instructions to apply the above patches are identical, and are contained in the readme file for patch 15844023.
• Please bear in mind, that the readme states to apply patch 13952743 for JDeveloper, too.

Fusion Middleware Control to Enable SSO Authentication

Using Fusion Middleware Control to Enable SSO Authentication

After Oracle Business Intelligence has been configured to use the SSO solution configured for use by Oracle Fusion Middleware, you enabled SSO authentication for Oracle Business Intelligence in Fusion Middleware Control from the Security tab.

To enable Oracle Business Intelligence to use SSO authentication:

1. Go to the Business Intelligence Overview page.
   For information, see "Logging In to Fusion Middleware Control" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

2. Go to the Security page.
   Click the Help button on the page to access the page-level help for its elements.

3. Click Lock and Edit Configuration.

4. Check Enable SSO.
   The SSO provider list becomes active.

5. Select the configured SSO provider from the list.

6. Click Apply, then Activate Changes.

7. Manually edit each instanceconfig.xml file for every Oracle BI Presentation Services process to configure the login and logout information. Inside the <Authentication> section, add the following:
   <SchemaExtensions>
     <Schema name="SSO" logonURL="{your SSO logon URL}" logoffURL="{your logoff URL}/>
   </SchemaExtensions>
   
   Note:
   For the logout page, you must use the URL specified by the SSO provider for this purpose. Do not use a URL within the domain and port protected by the SSO provider, because the system does not log users out.
   For information about where to locate Oracle Business Intelligence configuration files, see "Where Configuration Files are Located" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

8. Save each instanceconfig.xml file.
9. Restart the Oracle Business Intelligence components using Fusion Middleware Control.
   For more information, see "Starting and Stopping the Oracle Business Intelligence Components" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Pasted from <http://docs.oracle.com/cd/E14571_01/bi.1111/e10543/sso.htm#CEGJBAED>

OBIEE Interview Questions & Answers

 Define repository in terms of Siebel Analytics?

o Repository stores the Meta data information. Siebel repository is a file system ,extension of the repository file. rpd.
o META DATA REPOSITORY
o With Siebel Analytics Server, all the rules needed for security, data modeling, aggregate navigation, caching, and connectivity is stored in metadata repositories.
o Each metadata repository can store multiple business models. Siebel Analytics Server can access multiple repositories

What is the end to end life cycle of Siebel Analytics?

o Siebel Analytics life cycle
1. Gather Business Requirements
2. Identify source systems
3. Design ETL to load to a DW if source data doesn’t exist.
4. Build a repository
5. Build dashboard or use answers for reporting.
6. Define security (LDAP or External table…)
7. Based on performance, decide on aggregations and/or caching mechanism.
8. Testing and QA.
What were you schemas? How does Siebel Architecture works? Explain the three layers. How do you import sources?

o There are five parts of Siebel Architecture.
1. Clients
2. Siebel analytics Web Server
3. Siebel analytics server
4. Siebel analytics scheduler
5. data sorces
o Metadata that represents the analytical Model Is created using the siebel Analytics Administration tool.
o Repository divided into three layer
1. Physical – Represents the data Sources
2. Business – models the Data sources into Facts And Dimension
3. Presentation – Specifies the users view of the model;rendered in Siebel answer

If you have 3 facts and 4 dimension and you need to join would you recommend joining fact with fact? If no than what is the option? Why you won’t join fact to fact?

 In the BMM layer, create one logical table (fact) and add the 3 fact table as logical table source

 What is connection pool and how many connection pools did you have in your last project?

connection pool is needed for every physical database.
 It contains information about the connection to the database, not the database itself.
 Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass through .
 We can have multiple connection pools for each group to avoid waitin

 Purpose of Alias Tables?

  An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a logical table source, and inherits all its column definitions and some properties from the logical table source. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these logical table source types.
  Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table:
  To reuse an existing table more than once in your physical layer (without having to import it several times)
  To set up multiple alias tables, each with different keys, names, or joins
  To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

How do you define the relationship between facts and dimensions in BMM layer?


 Using complex join ,we can define relationship between facts and dimentions in BMM layer.

 What is time series wizard? When and how do you use it?

We can do comparison for certain measures ( revenue.,sales etc.. ) for current year vs previous year, we can do for month or week and day also
 Identify the time periods need to be compared and then period table keys to the previous time period.
 The period table needs to contain a column that will contain “Year Ago” information.
 The fact tables needs to have year ago totals.
 To use the “Time series wizard”. After creating your business model right click the business model and click on “Time Series Wizard”.
The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
 The Time Series Wizard prompts you to select the period table used for the comparison measures
 Select the column in the period table that provides the key to the comparison period. This column would be the column containing “Year Ago” information in the period table.
Select the measures you want to compare and then Select the calculations you want to generate. For ex: Measure: Total Dollars and calculations are Change and Percent change.
 
Once the Time series wizard is run the output will be:-
a) Aliases for the fact tables (in the physical layer)
b) Joins between period table and alias fact tables
c) Comparison measures
d) Logical table sources
o In the General tab of the Logical table source etc you can find “Generated by Time Series Wizard” in the description section
o Then you can add these comparision measures to the presentation layer for your reports.
o Ex: Total sales of current qtr vs previous qtr vs same qtr year ago

 Did you create any new logical column in BMM layer, how?

o Yes. We can create new logical column in BMM layer.
o Example: Right click on fact table -new lgical column-give name for new logical column like Total cost.
o Now in fact table source,we have one option column mapping, in that we can do all calculation for that new column.
Can you use physical join in BMM layer?

 yes we can use physical join in BMM layer.when there is SCD type 2 we need complex join in BMM layer.

Can you use outer join in BMM layer?

 yes we can.When we are doing complex join in BMM layer ,there is one option type,outer join is there.

 What are other ways of improving summary query reports other than Aggregate Navigation and Cache Management


Indexes
Join algorithm
Mat/view query rewrite
Web proper report design its optimal by making sure that it is not getting any addition column or rows

Dimension Hierarchies in OBIEE:Level-Based Hierarchy and Parent-Child Hierarchy

Dimension Hierarchies in OBIEE:Level-Based Hierarchy and Parent-Child Hierarchy

A hierarchy is a cascaded series of many-to-one relationships and consists of different levels.
                                                 

Example, a region hierarchy is defined with the levels Region, State, and City.

In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object. Common dimensions might be time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Note that dimensions exist in the Business Model and Mapping (logical) layer and in the Presentation layer.

There are two types of logical dimensions:

1.       Dimensions with level-based hierarchies (structure hierarchies).

2.       Dimensions with parent-child hierarchies (value hierarchies).

Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

In parent-child hierarchies, members all have the same type. Oracle Business Intelligence also supports a special type of level-based dimension, called a time dimension, that provides special functionality for modeling time series data.

Level-Based Hierarchies

A dimension contains two or more logical levels. For example, a Time hierarchy might have three levels for Year, Quarter, and Month. Level-based hierarchies can also contain parent-child relationships. The recommended sequence for creating logical levels is to create a Grand Total level and then create child levels, working down to the lowest level.

Level-based Dimension hierarchy levels allow:

• to perform aggregate navigation,
• to configure level-based measure calculations,
• users from Dashboard and Answers to drill down from one parent to a child level.

The following are the parts of a dimension:

• Grand Total level : A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A Grand Total level does not contain dimensional attributes and does not have a level key. However, you can associate measures with a Grand Total level. The aggregation level for those measures will always be the grand total for the dimension.
• Level : All levels, except the Grand Total level, need to have at least one column. However, it is not necessary to explicitly associate all of the columns from a table with logical levels. Any column that you do not associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. All logical columns in the same dimension table have to be associated with the same dimension.
• Hierarchy : Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level and a common root (all) level.
• Level keys : Each logical level (except the topmost level defined as a Grand Total level) must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.
• Time dimensions and chronological keys : You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key.

The following is a list of some guidelines you should use when setting up and using time dimensions:

• Unbalanced (or ragged) hierarchy : A hierarchy in which all the lowest-level members do not have the same depth For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.
• For example, a Time hierarchy might have data for the current month at the day level, the previous month's data at the month level, and the previous 5 years' data at the quarter level. This type of hierarchy is also known as an unbalanced hierarchy.

Note that unbalanced hierarchies are not necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature, but level-based hierarchies can be unbalanced also.

• Skip-level hierarchy : A skip-level hierarchy is a hierarchy where there are members that do not have a value for certain higher levels. . For example, in the United States, the city of Washington in the District of Columbia does not belong to a state. The expectation is that users can still navigate from the country level (United States) to Washington and below without the need for a state.

                                          

Hierarchy with Unbalanced and Skip-Level Characteristics 

 

Parent-Child Hierarchies

A parent-child hierarchy is a hierarchy of members that all have the same type.It is a value-based hierarchy — Consists of values that define the hierarchy in a parent-child relationship and does not contain named levels. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy.

For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees. Employees can have titles, such as Vice President. Vice Presidents might report to other Vice Presidents and different Vice Presidents can be at different depths in the hierarchy.

The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:

• Each individual in the organization is an employee.
• Each employee, apart from the top-level managers, reports to a single manager.
• The reporting hierarchy has many levels.

These conditions illustrate the basic features that define a parent-child hierarchy, namely:

• A parent-child hierarchy is based on a single logical table (for eg, the "Employees" table)
• Each row in the table contains two identifying keys, one to identify the member itself, the other to identify the "parent" of the member (for example, Emp_ID and Mgr_ID)

                                        

Multi-Level Parent-Child Hierarchy

In level-based hierarchies, each level is named, and occupies a position in the hierarchy that corresponds to a real-word attribute or category that is deemed useful for analysis. Unlike level-based hierarchies, where the number of levels is fixed at design time, there is no limit to the depth of a parent-child hierarchy, and the depth can change at run time due to new data.