Getting Started with Learning About Data Warehousing

Read up on some fundamental technical topics

If you are technical, you may find you will be greatly helped by reading up on SQL queries (especially multi-table and summary queries and subqueries), database indexing, join processing, and how query optimization works. The latter knowledge will most likely be found in books aimed at DBAs for specific commercial databases. This knowledge will help you even if you are not in a DBA role. - If you are not technical, you can still read primers on SQL and database design.

Visit a couple of organizations that have had data warehousing systems in production for over a year

You will get an excellent education if you can ask an organization who 'has done it' what are the biggest issues it faced in developing systems and what are the biggest issues it faces in maintaining systems. Also, ask what the organization felt it did right and what it felt it could have done differently. I believe that if you do this you will learn a great deal aspects of data warehousing that do not get discussed much in the literature - specifically the politics of data warehousing projects, the maintenance burdens data warehousing imposes, and how to deal with data warehousing software/hardware vendors and consultants. If you cannot visit other organizations, try going to vendor road shows or data warehousing conventions and talk with people with real experience with data warehousing. To repeat the point just made, too much about data warehousing goes unsaid by the media, the books, the vendors, and the consultants.

Download a trial copy of a query tool and an OLAP tool or an open source or free tool

Look for tools with sample data that you can experiment with on your own. The sample data is sure to highlight the tool's selling point. However, by playing with the tools you can get a feel of what companies use these tools for in real life.

Read this site

While this whole site is geared to the person getting started, the essays on a definition of data warehousing, the case for data warehousing, the case against data warehousing, aspects of data warehousing architecture, a definition of decision support, and what decision support tools are used for may be especially useful to a person new to the field.

Read the books "Building the Data Warehouse" by W. H. Inmon and "The Data Warehouse Toolkit" by Ralph Kimball

With due respect to all the other fine books on data warehousing and decision support, when read in combination I believe these two books provide a great introduction to and overview of the strategic and tactical issues system developers face - even though the original version of these books are over ten several years old. Despite what you read in the trade media, the basics of data warehousing do not change that much. Especially valuable are Inmon's overall overview and description of the iterative nature of data warehouse development and Kimball's description of data modeling principles and query/report tools. If you want to read further, check out Kimball's other books. Kimball stands out as a writer who is both substantive and easy to read. Finally, if you want a 10,000 foot, non-technical view of data warehousing in the business, read "Competing on Analytics: The New Science of Winning" by Thomas Davenport

Build something!

Computer texts love to cite a (supposedly) Confucian quote "What I hear I forget. What I see I remember. What I do I understand." Well, this quote is apt in the case of learning about data warehousing. After you build something, no matter how modest, you will gain a more profound appreciation of the topic.

datawarehousing notes

Benefits of data warehousing

Some of the benefits that a data warehouse provides are as follows: [2][3]

* A data warehouse provides a common data model for all data of interest regardless of the data's source. This makes it easier to report and analyze information than it would be if multiple data models were used to retrieve information such as sales invoices, order receipts, general ledger charges, etc.
* Prior to loading data into the data warehouse, inconsistencies are identified and resolved. This greatly simplifies reporting and analysis.
* Information in the data warehouse is under the control of data warehouse users so that, even if the source system data is purged over time, the information in the warehouse can be stored safely for extended periods of time.
* Because they are separate from operational systems, data warehouses provide retrieval of data without slowing down operational systems.
* Data warehouses can work in conjunction with and, hence, enhance the value of operational business applications, notably customer relationship management (CRM) systems.
* Data warehouses facilitate decision support system applications such as trend reports (e.g., the items with the most sales in a particular area within the last two years), exception reports, and reports that show actual performance versus goals.

[edit] Data warehouse architecture

Architecture, in the context of an organization's data warehousing efforts, is a conceptualization of how the data warehouse is built. There is no right or wrong architecture. The worthiness of the architecture can be judged in how the conceptualization aids in the building, maintenance, and usage of the data warehouse.

One possible simple conceptualization of a data warehouse architecture consists of the following interconnected layers:

Operational database layer
The source data for the data warehouse - An organization's ERP systems fall into this layer.
Informational access layer
The data accessed for reporting and analyzing and the tools for reporting and analyzing data - Business intelligence tools fall into this layer. And the Inmon-Kimball differences about design methodology, discussed later in this article, have to do with this layer.
Data access layer
The interface between the operational and informational access layer - Tools to extract, transform, load data into the warehouse fall into this layer.
Metadata layer
The data directory - This is often usually more detailed than an operational system data directory. There are dictionaries for the entire warehouse and sometimes dictionaries for the data that can be accessed by a particular reporting and analysis tool.

[edit] Normalized versus dimensional approach for storage of data

There are two leading approaches to storing data in a data warehouse - the dimensional approach and the normalized approach.

In the dimensional approach, transaction data are partitioned into either "facts", which are generally numeric transaction data, and "dimensions", which are the reference information that gives context to the facts. For example, a sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order. A key advantage of a dimensional approach is that the data warehouse is easier for the user to understand and to use. Also, the retrieval of data from the data warehouse tends to operate very quickly. The main disadvantages of the dimensional approach are: 1) In order to maintain the integrity of facts and dimensions, loading the data warehouse with data from different operational systems is complicated, and 2) It is difficult to modify the data warehouse structure if the organization adopting the dimensional approach changes the way in which it does business.

In the normalized approach, the data in the data warehouse are stored following, to a degree, the Codd normalization rule. Tables are grouped together by subject areas that reflect general data categories (e.g., data on customers, products, finance, etc.) The main advantage of this approach is that it is straightforward to add information into the database. A disadvantage of this approach is that, because of the number of tables involved, it can be difficult for users both to 1) join data from different sources into meaningful information and then 2) access the information without a precise understanding of the sources of data and of the data structure of the data warehouse.

These approaches are not exact opposites of each other. Dimensional approaches can involve normalizing data to a degree.

[edit] Conforming information

Another important decision in designing a data warehouse is which data to conform and how to conform the data. For example, one operational system feeding data into the data warehouse may use "M" and "F" to denote sex of an employee while another operational system may use "Male" and "Female". Though this is a simple example, much of the work in implementing a data warehouse is devoted to making similar meaning data consistent when they are stored in the data warehouse. Typically, extract, transform, load tools are used in this work. See Master Data Management.

[edit] Top-down versus bottom-up design methodologies

[edit] Bottom-up design

Ralph Kimball, a well-known author on data warehousing, [4] is a proponent of the bottom-up approach to data warehouse design. In the bottom-up approach data marts are first created to provide reporting and analytical capabilities for specific business processes. Data marts contain atomic data and, if necessary, summarized data. These data marts can eventually be unioned together to create a comprehensive data warehouse. The combination of data marts is managed through the implementation of what Kimball calls "a data warehouse bus architecture".[5]

Business value can be returned as quickly as the first data marts can be created. Maintaining tight management over the data warehouse bus architecture is fundamental to maintaining the integrity of the data warehouse. The most important management task is making sure dimensions among data marts are consistent. In Kimball words, this means that the dimensions "conform".

[edit] Top-down design

Bill Inmon, one of the first authors on the subject of data warehousing, has defined a data warehouse as a centralized repository for the entire enterprise.[5] Inmon is one of the leading proponents of the top-down approach to data warehouse design, in which the data warehouse is designed using a normalized enterprise data model. "Atomic" data, that is, data at the lowest level of detail, are stored in the data warehouse. Dimensional data marts containing data needed for specific business processes or specific departments are created from the data warehouse. In the Inmon vision the data warehouse is at the center of the "Corporate Information Factory" (CIF), which provides a logical framework for delivering business intelligence (BI) and business management capabilities. The CIF is driven by data provided from business operations

Inmon states that the data warehouse is:

Subject-oriented
The data in the data warehouse is organized so that all the data elements relating to the same real-world event or object are linked together.
Time-variant
The changes to the data in the data warehouse are tracked and recorded so that reports can be produced showing changes over time.
Non-volatile
Data in the data warehouse is never over-written or deleted - once committed, the data is static, read-only, and retained for future reporting.
Integrated
The data warehouse contains data from most or all of an organization's operational systems and this data is made consistent.

The top-down design methodology generates highly consistent dimensional views of data across data marts since all data marts are loaded from the centralized repository. Top-down design has also proven to be robust against business changes. Generating new dimensional data marts against the data stored in the data warehouse is a relatively simple task. The main disadvantage to the top-down methodology is that it represents a very large project with a very broad scope. The up-front cost for implementing a data warehouse using the top-down methodology is significant, and the duration of time from the start of project to the point that end users experience initial benefits can be substantial. In addition, the top-down methodology can be inflexible and unresponsive to changing departmental needs during the implementation phases.[5]

[edit] Hybrid design

Over time it has become apparent to proponents of bottom-up and top-down data warehouse design that both methodologies have benefits and risks. Hybrid methodologies have evolved to take advantage of the fast turn-around time of bottom-up design and the enterprise-wide data consistency of top-down design.

[edit] Data warehouses versus operational systems

Operational systems are optimized for preservation of data integrity and speed of recording of business transactions through use of database normalization and an entity-relationship model. Operational system designers generally follow the Codd rules of data normalization in order to ensure data integrity. Codd defined five increasingly stringent rules of normalization. Fully normalized database designs (that is, those satisfying all five Codd rules) often result in information from a business transaction being stored in dozens to hundreds of tables. Relational databases are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected each time a transaction is processed. Finally, in order to improve performance, older data are usually periodically purged from operational systems.

Data warehouses are optimized for speed of data retrieval. Frequently data in data warehouses are denormalised via a dimension-based model. Also, to speed data retrieval, data warehouse data are often stored multiple times - in their most granular form and in summarized forms called aggregates. Data warehouse data are gathered from the operational systems and held in the data warehouse even after the data has been purged from the operational systems.

[edit] Evolution in organization use of data warehouses

Organizations generally start off with relatively simple use of data warehousing. Over time, more sophisticated use of data warehousing evolves. The following general stages of use of the data warehouse can be distinguished:

Off line Operational Database
Data warehouses in this initial stage are developed by simply copying the data of an operational system to another server where the processing load of reporting against the copied data does not impact the operational system's performance.
Off line Data Warehouse
Data warehouses at this stage are updated from data in the operational systems on a regular basis and the data warehouse data is stored in a data structure designed to facilitate reporting.
Real Time Data Warehouse
Data warehouses at this stage are updated every time an operational system performs a transaction (e.g., an order or a delivery or a booking.)
Integrated Data Warehouse
Data warehouses at this stage are updated every time an operational system performs a transaction. The data warehouses then generate transactions that are passed back into the operational systems.

[edit] History

The concept of data warehousing dates back to the late 1980s [6] when IBM researchers Barry Devlin and Paul Murphy developed the "business data warehouse". In essence, the data warehousing concept was intended to provide an architectural model for the flow of data from operational systems to decision support environments. The concept attempted to address the various problems associated with this flow - mainly, the high costs associated with it. In the absence of a data warehousing architecture, an enormous amount of redundancy of information was required to support the multiple decision support environments that usually existed. In larger corporations it was typical for multiple decision support environments to operate independently. Each environment served different users but often required much of the same data. The process of gathering, cleaning and integrating data from various sources, usually long existing operational systems (usually referred to as legacy systems), was typically in part replicated for each environment. Moreover, the operational systems were frequently reexamined as new decision support requirements emerged. Often new requirements necessitated gathering, cleaning and integrating new data from the operational systems that were logically related to prior gathered data.

Based on analogies with real-life warehouses, data warehouses were intended as large-scale collection/storage/staging areas for corporate data. Data could be retrieved from one central point or data could be distributed to "retail stores" or "data marts" which were tailored for ready access by users.

Key developments in early years of data warehousing were:

* 1960s - General Mills and Dartmouth College, in a joint research project, develop the terms dimensions and facts.[7]
* 1970s - ACNielsen and IRI provide dimensional data marts for retail sales.[7]
* 1983 - Teradata introduces a database management system specifically designed for decision support.
* 1988 - Barry Devlin and Paul Murphy publish the article An architecture for a business and information systems in IBM Systems Journal where they introduce the term "business data warehouse".
* 1990 - Red Brick Systems introduces Red Brick Warehouse, a database management system specifically for data warehousing.
* 1991 - Prism Solutions introduces Prism Warehouse Manager, software for developing a data warehouse.
* 1991 - Bill Inmon publishes the book Building the Data Warehouse.
* 1995 - The Data Warehousing Institute, a for-profit organization that promotes data warehousing, is founded.
* 1996 - Ralph Kimball publishes the book The Data Warehouse Toolkit.
* 1997 - Oracle 8, with support for star queries, is released

[edit] Disadvantages of data warehouses

There are also disadvantages to using a data warehouse. Some of them are:

* Over their life, data warehouses can have high costs. The data warehouse is usually not static. Maintenance costs are high.
* Data warehouses can get outdated relatively quickly. There is a cost of delivering suboptimal information to the organization. New data warehouses solve this by using a technology called Change_data_capture.
* There is often a fine line between data warehouses and operational systems. Duplicate, expensive functionality may be developed. Or, functionality may be developed in the data warehouse that, in retrospect, should have been developed in the operational systems and vice versa..

[edit] The future of data warehousing

Data warehousing, like any technology niche, has a history of innovations that did not receive market acceptance.[8]

A 2007 Gartner Group paper predicted the following technologies could be disruptive to the business intelligence market .[9]

* Service Oriented Architecture
* Search capabilities integrated into reporting and analysis technology
* Software as a Service
* Analytic tools that work in memory
* Visualization

Another prediction is that data warehouse performance will continue to be improved by use of data warehouse appliances, many of which incorporate the developments in the aforementioned Gartner Group report.

Finally, management consultant Thomas Davenport, among others, predicts that more organizations will seek to differentiate themselves by using analytics enabled by data warehouses. [10]

datawarehousing

CH 01 - Data Warehousing Concepts

· What are DW and BI? Differences.
· Introduction to (DW) Data Warehouse.
· Introduction to (BI) Business Intelligence.
· RDBMS – Concepts – Structures and Indexing.
· Need for a Warehouse ?
· Advantages and disadvantages of Warehousing.
· OLTP & OLAP Databases
· Dimensions and Facts.
· Data Marts – Need, Advantages and Differences between Warehouse.
· ODS -Need, Advantages and Differences between Warehouses.
· Data Models and Design Operators.
· Drill up & Drill Down and Slicing & Dicing of data.
· DMR, ROLAP, MOLAP and HOLAP.
· Data Mining, Data Cleansing and Data Integrating.
· ETL – Extract, Transform and Load Process.


CH 02 - Architecture of Data Warehouse


· Data Warehouse Life Cycle – Architecture
· Characteristics of a Data Warehouse
· OLAP Databases and Differences
· Dimension Tables, Fact Tables (Attributes & Measures)


CH 03 - Types of Dimensions and Fact Tables


· Slowly Changing Dimensions
· Surrogate Key
· Degenerate Dimension
· Conformed Dimension
· Time Dimension
· Fact Less Fact tables


CH 04 - Dimensional Modeling Layouts


· Dimension and Fact Tables
· Star Schema
· Snow Flake Schema
· Multi-Star Schema
· Multi- Snow Flake Schema


CH 05 - Data Warehouse Modeling Techniques

· Normalization and De-Normalisation.
· Multi-Dimensional Modelling.
· DFD - Data Flow Diagrams.
· E-R - Entity Relationship Diagrams.
· Relational Modeling and Dimensional Modeling .
· Designing Star Schema, SnowFlake Schema and Multi-Star Schema.


CH 06 - Data Load Types


· Load Jobs
· Full Initial Load
· Incremental Loading

datawarehousing topics

# Data warehouse architectures
# New view of dimensional modeling
# Required snowflakes
# Conforming facts and dimensions
# Heterogeneous dimensions and facts
# Changing dimensions and facts
# Mixed changes
# Modeling for different types of time changes
# Fact to fact joins
# Do all facts have count, amount; are all dimensions without them.
# Factless facts.
# Fact or dimension
# Design for parallel
# Multiple roles
# Use of surrogate keys
# Handling multi-valued dimensions
# Dimensions with varying characteristics
# Handling complex dimensions, such as hierarchical, ragged, multiple dimensions
# Handling time and history
# Surrogate keys
# Name value pairs
# What changed?
# Name value pairs
# Detecting change data
# Problems with flattening T1 and T2 dimensions
# Designing aggregates
# Aggregates vs. on-the-fly
# Supporting restatement or aggregates
# Predicate analysis for star joins
# Designing for trickle load
# Exercises

Oracle Apps (E-Business Suite) DBA Interview Questions

Q. How you will troubleshoot if concurrent request is taking long time ?

Q. If you are applying a patch.It was started successfully. In the middle you realise nothing happening and no update in patch log file,worker log file (No updates & no error message). What to do & How to troubleshoot ?

Q. Why there are three ORACLE_HOMEs in 11i or R12 ?

Q.What is difference between shared appltop and staged appltop ?

Q. what are the request incompatibilities ? how conflict resolution manager solve them?

Q.Where and how you update workflow notification mailer configuration setting ? (This depends on which workflow mailer you are running C Mailer or Java Mailer )

Q. If you want to change Concurrent Manager log and out file location , Is it possible ? If Yes, How ? If No, why not ?

Q. What are conflict resolution managers in CM ?

Q. What are interoperability patches ?

Q. How frequent you run Gather Schema Statistics program & with what option ? Why you need to run it ? What is cost based optimizer ?

Q. Name few common issues you encountered recently related to Web Server, Forms Server, Concurrent Manager (CM), Jinitiator, Database, Cloning, Patching .

Q. What all things you will check after cloning and before handing over instance to end users ?

Q. If users complain that reports are not running, what you will do to troubleshoot ?

Q. What is Rep-300 toolkit error ? Did you ever encounter this ?

Q. Share configuration/setup you have done w.r.t. Apps (expect some questions on that setup) ?

oracle interviews 24

Q. What is difference between fresh database and vision database install types ?
Fresh Database - Database is installed with Apps but with no data
Vision Database - Database installed with Apps with dummy data

Q. What are various components installed after 11.5.10 (11i) install ?
–9iAS (1.0.2.2.2) web server, Developer 6i Forms & Reports, Discoverer, Jinitiator

Q. What is O.S. level software requirement for installing Apps ?
ar, ld, make & X Display server for all unix machines (linux, solaris, IBM, HP Unix )
with following additional as per O.S.

Linux- gcc, g++, ksh
HP-Unix- cc, acc
IBM AIX - cc, linkx1C

For Windows you need
Microsoft C++, MKS Toolkit, GNU Make

Q. What is minimum approx. disk requirement for 11.5.10 (Note these disk requirement changes as per type of installation, languages installed and release )

For 11.5.10
Application Tier File System - 26 GB
Database Tier (Fresh install) - 31 GB
Database Tier (Vision install) - 65 GB

Q. What is staging area ?
Staging Area is special directory structure where you can dump 11i installation software so that you don’t have to insert CD’s during install and these disks will automatically be picked by Installer.

Q. How you set up staging area ?
Use adautostg.pl to create staging area orcreate required directory manually for staging like (following directories under Stage11i - startCD, oraApps, oraDB, oraiAS, oraAppsDB, oraNLS and inside these directories Disk1, Disk2…)

Q. Is it possible to install apps without staging area ?
YesThese questions are very basic and for freshers who mention in their CV’s that they have installation experience, for advanced installation questions keep looking at this site

oracle interviews 22

How to compile an Oracle Reports file ?

Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character


Q. What is difference between AD_BUGS & AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.


Thanks to Vikram Das for sharing below Q's/A's with readers.
Q. What exactly happens when you put an Oracle Apps instance in maintenance mode ?

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature
a) shuts down the Workflow Business Events System and
b) sets up function security so that no Oracle Applications functions are available to users.

Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch. (Source Metalink Note: 233044.1)