Deleting duplicate data in the database

This post if for db beginners who have created duplicate records in their db by mistake.
Let your scripts be like this.
--------------------------------------------------------------------------------------
-----------------Table creation-------------------------------------------------------

 CREATE TABLE CALENDAR
   ( "ROW_ID" NUMBER,
"DAY" NUMBER,
"MONTH" NUMBER,
"YEAR" NUMBER
   ) ;

--------------------------------------------------------------------------------------
-----------------Populating data------------------------------------------------------

Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2000,8,9,2011);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2001,4,1,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2002,6,4,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2003,7,6,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2004,5,9,2012);

Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2000,8,9,2011);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2001,4,1,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2002,6,4,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2003,7,6,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2004,5,9,2012);
--------------------------------------------------------------------------------------
Now you have two sets of redundant data.
Here, what we want to do is to remove the duplicate data that we inserted. To remove that run the following piece of script.

--------------------------------------------------------------------------------------
-----------------Removing duplicate  data--------------------------------------------



DELETE FROM CALENDAR
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM CALENDAR
                          GROUP BY ROW_ID,DAY,MONTH,YEAR);
--------------------------------------------------------------------------------------


Now you can see that all the duplicate records have gone.

OBIEE 10 G Architecture

OBIEE components can be segmented as below
• Oracle BI Web
• Oracle BI Presentation Server
• Oracle BI Server
• Oracle BI Scheduler

Oracle BI Web

This component is the User Interface/Web Applications which is accessible to the client and users to prepare reports, dashboard and provide ad-hoc reporting. Oracle BI Answers, Oracle BI Interactive Dashboard, Oracle Delivers and Oracle BI Publisher are the different interfaces for the web applications

        Oracle BI Answers - Analysis tool that allows users with the appropriate
        permissions to build and modify reports that let end users explore and interact
        with information, and present visualize information using charts, pivot tables, and
        reports.

        Oracle BI Interactive Dashboard - Interactive Dashboards provide points of
        access for analytics information. Dashboards are typically used to display reports 
        that contain content specific to the needs of individual users or groups.

        Oracle BI Delivers - An interface used to create alerts based on analytics
        results using intelligence bots called iBots to detect specific results. iBots are
        software-based agents, driven by schedules or events that can access, filter, and
        perform analytics on data based on specified criteria. Oracle BI Delivers server is
        the engine that drives the proactive detection, Intelligent workflow and alerting
        capabilities in Oracle Delivers

       Oracle BI Publisher - Supports high fidelity report templates that are created and
       published via personal productivity applications and delivered directly or through
       Interactive Dashboards to end users


Oracle BI Presentation Server 

The BI Presentation server runs along with HTTP server and is basically a web server on which the OBIEE web application runs. It processes the client requests and routes it to the underlying BI Server and vice versa via an ODBC driver called 'Analytics Web'. It can be deployed on any of the following application server IIS, weblogic or Oc4j.

It also contains a folder like structure called WebCatalog to store report definitions, dashboards, folders, filters, etc. Apart from that,It also contains information regarding the permissions of dashboards & reports created by users. It is created when the Presentation server starts and can be administered using the tool called Catalog Manager. 

Oracle BI Server

BI Server is the middle tier of the OBIEE architecture often described as the highly scalable query and analysis server. Precisely it’s the SQL generator for queries and efficiently integrates data from multiple relational, unstructured & OLAP application sources and defines security implementation.

How BI Server processes a request?

• BI Server interacts with the Presentation server over TCP/IP and takes the reporting request
• Processes the request and formulates logical and physical queries using the Oracle BI Repository
• The generated physical query is sent to the underlying data source using ODBC connectivity from which the data is processed.

      Oracle BI Repository (RPD)

      The metadata using which the server gets the information of the joins and the filters
      to be used in the query and handles security. Oracle BI Administrator Tool is the
      Windows based client tool to develop and manage Metadata repository. RPD
      defines three layers of abstraction – Physical Layer, Business Modelling &
      Mapping (BMM) Layer and Presentation Layer.
      Physical Layer - connections to physical data sources (OLTP, OLAP, Flat Files,
      Oracle, SQL*Server, Teradata, etc.) and defines the relation between them

      BMM Layer – Brings together data elements from Physical sources into a logical
      Star-Schema representation. This is where business rules and definitions are setup.
      Aggregation rules, Data level security, modelling Facts & Dimensions and
      Hierarchy definitions are defined in this layer

       Presentation Layer – An abstraction from the Business Model, defines exactly
       how users will see tables and fields in the OBIEE reporting user Interfaces.
       Security Permissions can also be defined

Repository changes can be done in either online or offline mode. Online mode is when BI Server is actively reading the repository file (when a user is logged in). Offline is when BI Server is idle. Best practice is to develop in Offline mode. Minor changes can be made when in online mode.

Oracle BI Scheduler

This is the Server application that manages and schedules jobs. Provides extensible scheduling for analyses to be delivered to users at specified times. Note that Oracle BI Publisher has its own scheduler.)

Oracle BI Scheduler supports two kinds of jobs:

• Scripted jobs
• Unscripted jobs (iBOTS/Agents)

Scripted Jobs

Scripted jobs are set up and submitted using the Job Manager feature of the Oracle Business Intelligence Server Administration Tool. For example, a scripted job could periodically load Oracle BI Server usage statistics into a back-end database. For scripted jobs, Oracle BI Scheduler communicates with Oracle BI Server.

Oracle BI Scheduler supports two published Java interfaces and two scripting languages.

Supported Java interfaces:

      i. SchedulerJavaExtension.java
      ii. SchedulerJobInfo.java

Supported script languages (Windows Platform only):

      i. VBScript
      ii. JScript

   Unscripted jobs
   iBots/Agent delivers reports and alerts to end users. iBots are configured and submitted for execution using the web client Oracle Business Intelligence Delivers which is a module of BI Presentation Service
 

What is Oracle Business Intelligence (BI) Publisher ?

Oracle Business Intelligence (BI) Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite, Peoplesoft Enterprise, and JD Edwards EnterpriseOne. It provides a flexible and robust approach to report design and publishing by integrating familiar desktop word processing tools with existing data reporting. BI Publisher leverages standard, well-known technologies and tools, so you can rapidly develop and maintain custom report formats. The flexibility is a result of the separation of the presentation of the report from its data structure. The collection of the data can still be handled by existing report tools, but with BI Publisher you can design and control how the report outputs will be presented in separate template files. At runtime, your designed template files are merged with the report data to create a variety of outputs to meet a variety of business needs, including: • Customer-ready PDF documents, such as financial statements, marketing materials, contracts, invoices, and purchase orders utilizing colors, images, font styles, headers and footers, and many other formatting and design options. • HTML output for optimum online viewing. • Excel output to create a spreadsheet of your report data. • "Filled-out" third-party provided PDF documents. You can download a PDF document, such as a government form, to use as a template for your report. At runtime, the data and template produce a "filled-out" form. • Flat text files to exchange with business partners for EDI and EFT transmission.