Friday 27 December 2013

Oracle Business Intelligence Enterprise Edition (OBIEE) Basics

Oracle Business Intelligence Enterprise Edition (OBIEE) Basics

 1. What is Database?
A. A database is a collection of information that is organized so that it can easily be accessed,                managed, and updated. In simple way, Group of schemas is called as Database.

2. What is Schema ?
A. A database schema is a way to logically group objects such as tables, views, stored procedures etc.              Schema is a container of objects. In simple words, Group of tables is called as Schema.

3. What is Table?
A. In a relational database, a table organizes the information about a single topic into rows and columns.
     For example, a database for a business would typically contain a table for customer information, which          would store customers' account numbers, addresses, phone numbers, and so on as a series of columns.        Each single piece of data (such as address) is a field in the table. In simple words, Group of columns is          called as Table.

4. What is Column?
A. A column consists of all the entries in a single field, such as the telephone numbers of all the customers.          Fields, in turn, are organized as records, which are complete sets of information (such as the set of                information about a particular customer), each of which comprises a row. The process of normalization          determines how data will be most effectively organized into tables.

5.What is Measure Column?
A. After summarization of a numeric column is given valid(meaningful) information. Then it is called as                Measure Column.

6. What is Key Column? 
A. A column which contains Primary Key is called as Key Column. A primary key, also called a primary              keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a          driver license number, telephone number (including area code), or vehicle identification number (VIN).

7. What is Fact Table?
A. A table which contains one Measure Column is called as Fact Table.

8. What is Dimension Table?
A. A table which doesn't contain atleast one Measure Column is called as Dimension Table.

9. What are the different types of Schema?
A. A group of tables is called as Schema. The following are the different schema.
   
               1. Star Schema
               2. Snow Flake Schema
               3. Mixed Schema

10. What is Star Schema?
A.  Star Schema:
       Star Schema organizes data into a central fact table with surrounding dimension tables. Each dimension          row has many associated fact rows. In star schema, any two dimension tables don't directly relate to              each other. All dimension tables are denormalized. It optimized to read data. It is user friendly, easy to          understand. In OBIEE, BMM layer uses Star Schema.

11. What is Snow Flake Schema?
 A.  Snow Flake Schema:
        In Snow Flake Schema, Normalized tables are used. It is also called as Extended Star Schema. Unlike         in star schema two dimension tables are directly joined. Like star schema, It has only one fact table.

12. What is Mixed Schema?
 A.  Mixed Shema:
        Mixed Schema contains more than one fact with some common dimensions (Conformed Dimensions).           It is combination of Some stars or some snows or both.

13. What is conformed Dimension?
 A.  A dimension table is shared by two or more facts then it is called as conformed dimension.



Data Warehouse (DWH) Basics


Data Warehouse Basics

1. What is Data?
A. Data is a collection of information which gives sense or meaning. i.e., Any meaningful information is called      called Data.
2. What are the different types of Data?
A. Data is 2 types. They are

        1. Transactional Data
        2. Analytical Data

3. What is Transactional Data?
A. Transactional data is run time data or day to day data. It is current and detail in nature. Transactional data       is stored in On Line Transaction Processing (OLTP). This is useful to run the business. Applications such       as Share Market Transactions, ATM Transactions are sources of Transactional Data. 
4. What is Analytical Data?
A. Analytical Data is data which is used for analyzing the business. It is historical and summarized data. It is        sourced in On Line Analytical Processing(OLAP), or Data Warehouse(DW).
5. What is Data Warehouse?
A. A data warehouse is a relational database that is designed for query and analysis rather than for                      transaction processing. It usually contains historical data derived from transaction data, but it can include        data from other sources. It separates analysis workload from transaction workload and enables an                organization to consolidate data from several sources.
6. Draw Data Warehouse Architectue.
A. In addition to a relational database, a data warehouse environment includes an extraction, transportation,        transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis      tools, and other applications that manage the process of gathering data and delivering it to business users.
    
    In addition to a relational database, a data warehouse environment includes an extraction, transportation,       transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis       tools, and other applications that manage the process of gathering data and delivering it to business users.
                                                   Data Warehouse Architecture
7. What are different types of Data Warehouse Tools?
A. Data Warehouse tools are divided into 2 types
  
         1. ETL Tools
         2. Reporting Tools

1. ETL Tools:
 1. Informatica 
 2. Data Stage
 3. Abintio
 4. SSIS
 5. ODI
 6. OWB
 7. BODI

2. Reporting Tools:

       1. OBIEE
       2. BI Publisher
       3. Cognos
       4. SAP-BO
       5. DOMO
       6. Qlick View
       7. MSTR

8. What are the differences between OLTP and OLAP?
A.  
OLAP vs OLTP  source: www.rainmakerworks.com