OLTP and OLAP

OLTP and OLAP difference:

POINT	OLTP	OLAP
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

OBIEE 10g and 11g Difference

Difference between OBIEE 10g and OBIEE 11g:

a.      OBIEE 11g uses WebLogic Server as the application server as compared to Oracle AS or OC4J in OBIEE 10g.

b.      The clustering process is much easier and automated in OBIEE 11g.

c.       We can now model lookup tables in the repository.

d.      The new UI called Unified Framework now combines Answers, Dashboards, and Delivers.

e.       A new column called the hierarchical column is introduced.

f.       BI Publishers is fully and seamlessly integrated with OBIEE 11g.

g.      New time series functions PERIOD ROLLING and AGGREGATE AT are introduced.

h.      In OBIEE 11g we can create KPIs to represent business metrics.

i.        The aggregate persistence wizard creates indexes automatically.

j.        The session variables get initialized on first usage in OBIEE 11g unlike OBIEE 10g where they were initialized as soon as a user logs in.

k.      OBIEE 11g now supports Parent-Child, Ragged (Unbalanced) and Skipped Hierarchy.

l.        SELECT_PHYSICAL command is supported in OBIEE 11g.

Changed terminologies:

a.       iBots are renamed as Agents.

b.      Requests are renamed as Analyses.

c.       Charts are renamed as Graphs.

d.      Presentation Columns are renamed as Attribute Column.

e.       Security terminology and architecture has changed.

Some of the key features include:

a.       Export to xml or export to tab delimited and power point.

b.      11g lets variable have multiple value.

c.       11g RPD cannot be without a password.

d.      You can use a "selection step" to limit data for columns.

e.       Drill in data (expanding a member, without impacting rest of the report)

f.       Sorting in 11g is now enabled by default.

g.      In a pivot table, you can easily drag and drop columns to various edges and rearrange.

h.      Graphs and gauges come with new feature of section sliders.

i.        Map view is a new view type that presents data in spatial form.

j.        We can also create master-detail relationships among certain view types

k.      We can create calculated items to perform mathematical operations on members.

l.        11g allows us to work with multiple subject areas in same report.

SQL Functions for Time Series Calculations and Level-Based Measures

1.      PERIODROLLING

2.      AGGREGATE AT

3.      PERIODAGO à AGO

4.      PERIODTODATE àTODATE

Enhanced database function usage capability:

1.      OBIEE 11g allows calling EVALUATE, EVALUATE_AGGR, and VALUATE_PREDICATE function for advanced calculations.

2.      Enhancements to the Oracle BI Presentation Catalog

3.      Perform basic tasks such as creating, deleting, and copying objects and folders.

4.      Find objects by performing a search.

OBIEE 11g Architecture

OBIEE 11g Architecture

Domains in OBIEE:

Web Logic Server Domain:

·         J2EE App Server used across the board for all 11g BI applications

·         Contains :

a.       Managed Server : Set of J2EE Applications used for “functioning” the BIEE system

b.      Admin Server : Set of J2EE Applications used for “administering” of BIEE system

Oracle Process Manager and Notification Server (OPMN) domain

·           Used to start/Stop system components (BI Server, BI Pres Server, BI Scheduler, BI cluster controller)

·           Can be accessed from CMD or from EM page(GUI)

 Admin Server Components:

WLS Admin Console

•            Admin GUI for WLS, Security and J2EE Components

 Fusion Middleware-EM Control (FMW-EM)

•            Admin GUI to Manage the BI Domain

•            JMX Beans

•            Java components that provide programmatic access for managing a BI domain.

Managed Server Components:

•      BI Plug-in: Sends web http requests to BI Presentation Services

•      BI Security: Integrates BI Server and FMW sec platform (using web service calls)

•      BI Action Services: Dedicated web services for Action framework

•      BI Web Service SOA: Provides Web services for objects in the BIEE Presentation          Catalog, to invoke analysis, agents, and conditions.

•      BI Office: Provides the integration between Oracle Business Intelligence and Microsoft Office products

BIEE Domain System Components:

 •     BI Server

 Provides capabilities to query and access data as well as services for accessing and managing the RPD file (BIEE Metadata).

•     BI Presentation Services

             Provides the framework and interface for the presentation of business intelligence data to Web clients. It maintains an Oracle BI Presentation Catalog service on the file system for the customization of this presentation framework.

            •     BI Scheduler     

                        Provides framework for scheduling and delivering reports to users

            •     BI Java host

            Enables BI Presentation Services to support various components like Java tasks for BI Scheduler, BI Publisher, and Graph generation.

•     BI Cluster Controller

 Used for distributing requests to BI server and ensure load balancing

Important Files in OBIEE:

•            Repository file (e.g. SampleSales.rpd)

•            Config Files (nQconfig.ini, instanceconfig.xml,)

•            Log Files (nqserver.log, nqquery.log, nqscheduler.log, sawlog0.log etc)

•            Presentation catalog

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']}