Upgrading the Oracle BI Repository and Catalog

To upgrade the Sample Sales repository and presentation catalog, perform the following steps by using the Upgrade Assistant. Please note that the WebLogic Administration Server and the Managed Server in the 11g environment must remain running during the upgrade process.

1. Navigate to the ORACLE_HOME\bin directory of the Oracle Fusion Middleware installation. In this example, the directory path is D:\bi\Oracle_BI1\bin.

2. Double-click ua.bat to open Upgrade Assistant in the graphical user interface mode. Refer screen shot:

3. On the Welcome screen, click Next.

4. On the Specify Operation screen, select Upgrade Oracle BI RPD and Presentation Catalog and click Next.

5. On the Specify Source Details screen, select Upgrade Repository (RPD).

6. Click Browse next to the RPD File field.

7. Browse to the location of the 10g version of the Sample Sales repository and click Open. In this example, the path is D:\SampleSales\samplesales.rpd.

 8. Enter the Administrator User Name and Administrator Password for the 10g samplesales repository. In this    example, the username and password are both Administrator.

9. Enter new password for rpd which doesn't match with the previous 10g rpd password.

10. Select Upgrade Catalog.

11. Browse to the location of the 10g version of the Sample Sales presentation catalog and click Open. In this example  the path is D:\SampleSales\Sample_Sales_V1.3_Webcat\samplesales.

12. The path to the Sample Sales presentation catalog is added to the Catalog Directory field. The path to  the deliveries directory is automatically added to the Catalog Deliveries Directory field.

13.Enter the weblogic admin server details. Port number, user name and password. 

14. Click Next to continue. On the Examining Components screen, wait until you see a succeeded message. During the examination process, Upgrade Assistant checks whether the source directory exists, the source directory is readable, and contains a file for upgrade.

15. Click Next to continue. On the Upgrade Summary screen, review the summary of the upgrade to be performed.

16. Click Next to continue. When the upgrade completes the status changes to succeeded.

17. Click Next to continue. n the Upgrade Success screen, review information related to the upgrade task and  click Close.

             In this way have upgraded rpd and catalog to 11g. Comment if it is helpful and keep visiting  for new posts.

Thanks....!!

RPD Layers

Three Layers of RPD:

RPD (Repository) is divided into 3 layers

1. Physical Layer: This layer is used for

ü  Importing data

ü  Creating Aliases

ü  Building physical joins

ü  Setting up connection pool and its properties

ü  Enabling/ Disabling cache for individual table

2. BMM (Business Model & Mapping) Layer: This layer is used for

ü  Writing the business logic

ü  Creating Logical columns and tables

ü  Creating hierarchy

ü  Creating LBM (level based measures)

ü  Creating shares

ü  Creating Time series functions

ü  Creating Fragmentation on tables

ü  Creating filters on repository

3. Presentation Layer: This layer is used for

ü  Arranging the data for users view (Folder Structure)

ü  Creating Presentation hierarchy

ü  Creating Implicit Fact column

ü  Implementing Column level security

In short three layers of RDP consist of following functions:

PRESENTATION LAYER User Roles And Preferences Simplified Views Logical SQL Interface

BMM LAYER Dimensions Hierarchies Measures Calculations Aggregation Rules Time Series Functions

PHYSICAL LAYER Map Physical data Connections Schema Aliases, Joins

Data Warehouse Concepts

Facts Tables:

ü  A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact's data on detail or aggregated level.

ü  A fact table stores quantitative information for analysis and is often de-normalized.

ü  Fact table is typically numeric data and it is often data that can be easily manipulated, particularly by summing together many thousands of rows.

Types of fact:

1.      Additive:
Additive facts are facts that can be summed up through all of the dimensions in the fact table. A sales fact is a good example for additive fact.

2.      Semi-Additive:
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
E.g.  Daily balances fact can be summed up through the customers dimension but not through the time dimension.

3.      Non-Additive:
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
E.g. Facts which have percentages, ratios calculated.

Dimensions:

ü  A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

ü  Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

Surrogate key:

ü  Surrogate keys are nothing but integers which do not have any meaning in terms of business and used as primary key in dimension table.

ü  Surrogate keys join the dimension tables to the fact table. Surrogate keys serve as an important means of identifying each instance or entity inside of a dimension table.

Fact less fact:

A fact less fact table is fact table that does not contain fact. They contain only dimensional keys and it captures events that happen only at information level but not included in the calculations level. Just information about an event that happen over a period.

A fact less fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of fact less fact tables include:

ü  Identifying product promotion events

ü  Tracking student attendance or registration events

ü  Tracking insurance-related accident events

ü  Identifying building, facility, and equipment schedules for a hospital or university

ü  Fact less fact tables are used for tracking a process or collecting stats.

E.g. student, time, and class dimensions used to create student attendance fact less fact table. 

Degenerated dimension:

ü  A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table.

ü  These are essentially dimension keys for which there are no other attributes. In a data warehouse, these are often used as the result of a drill through query to analyze the source of an aggregated number in a report.

ü  You can use these values to trace back to transactions in the OLTP system.

Conformed dimension:

ü  A Dimension that is used in multiple locations is called a conformed dimension.

ü  A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses.

Schema types:

1.  Star Schema:

ü  In the star schema design, a single object sits in the middle and is radically connected to other surrounding objects (dimension lookup tables) like a star.

ü  Each dimension is represented as a single table.

ü  The primary key in each dimension table is related to a foreign key in the fact table.

2.  Snowflake schema:

ü  The snowflake schema is an extension of the star schema, where each point of the star explodes into more points.

ü  In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

OBIEE Variables

Types of OBIEE variables:

1. Session Variables

Session variables created during the creation of new session.

Following are types of session variables:

a.      System session variables:

Which are defined by OBIEE and are reserved:

E.g. USER, PROXY, GROUPS, ROLES, PERMISSIONS, LOGLEVEL, DESCRIPTION, SELECT_PHYSICAL

Initialized when its first usage.

@{biServer.variables['NQ_SESSION.VariableName']}

b.      Non system session variables

            A common use for non system session variables is setting user filters.

            E.g. Select sales as “User_Sales” from sales_region where user_name=:USER;

           

2. Repository Variable

      a.   Static Repository Variables:

The value of a static repository value is initialized in the Variable dialog box. This value persists, and does not change until an Oracle BI Administrator decides to change it.
E.g. filter on sales region.

      b.   Dynamic Repository Variables:

            Refresh its value after every request.

            E.g. Selection of month from system dates.

           

3. Presentation Variables:

a.      Column prompt

Column Prompt is based on a Real Column or a Formula. Column Prompt, "Is Prompted" and "Protect Filter".

b.      Variable prompt

A variable prompt allows the user to select a value that is specified in the variable prompt to display. Variable Prompt has no actual column behind and is aimed to create presentation or request variables only.

c.       Image prompt

Image area is used in the results. E.g. Sales Region.

d.      Currency Prompt

Represents the currency selected in currency column.

e.       Dashboard prompt

This prompt is global to dashboard.

4. Request Variables:

A request variable is an OBI Server Session Variable but with the scope of the request. The value of the session variable will not be change for the session. A request variable is a variable that is used to temporarily override the value of a session variable.

You can set it up in three main ways:

·         In a dashboard prompt with the help of the SET VARIABLE column.

·         By adding manually the SET VARIABLE clause in a logical SQL statement (into an answer, Dashboard prompt, Issue SQL, …)

Setting Request variables:

In an Answer

      1.   with value:

            In an answer, in the advanced tab, you have the section “Advanced SQL Clauses”.

            In the prefix field, you can add a “SET VARIABLE MYVARIABLE=MyValue;”

      2.   with a presentation variable:

            You can then use the value of a presentation variable to initialize a request variable.

      3.   in dashboard prompt:

            Click on column names->select options->In Set a variable, select Request variable.

            Enter the name of the session variable to override in the Variable Name field.

Referencing variables:

          Session: @{biServer.variables['NQ_SESSION.variablename']}
                  E.g.@{biServer.variables['NQ_SESSION.USER']}

          Repository: @{biServer.variables.variablename} or @{biServer.variables['variablename']}
                  E.g.@{biServer.variables.prime_begin} or @{biServer.variables['prime_begin']}

          Presentation or request: @{variables.variablename}[format]{defaultvalue} or 
                                                   @{scope.variables['variablename']} 
                  E.g.@{variables.MyFavoriteRegion}{EASTERN REGION} or 
                         @{dashboard.variables['MyFavoriteRegion']}