Monday, 16 July 2012

Data Warehousing

1. what is the difference between database and data
warehousing?

Major difference is the historical
data which can be maintained in data warehouse instead of keeping it in your
real time OLTP(database) system. Also we don't want to decrease the processing
time of our OLTP server for taking data for reporting and analysis purpose.

Now why informatica and why other
OLAP tools are required :coz companies don't want to spend lots of money in
hiring fulltime database developer to write pl/sql packages and procedure to
store all these information in ODS. Therefore, most of the time they hire a
short-period informatica/ETL developer to make mapping which is GUI based, easy
to maintain and easy to run.

Database is the place where the data is taken as a base and managed
to get available fast and efficient access.

Data warehouse is the place where the application data is managed
for analysis and reporting purposes.

2. what is a derived table in data warehousing?

it is similar to views, it is mainly used to restrict the data

3. What are the methodologies of Data Warehousing.

Every company has methodology of their own. But to name a few
SDLC Methodology, AIM methodology are stardadly used. Other methodologies are
AMM, World class methodology and many more.

Most of the time ,we use Mr. Ralph Kimball methodologies for
datawarehousing design.Two kind of schema :star and snow flake.

Regarding the methodologies in the Datawarehousing . They are
mainly 2 methods.

1. Ralph Kimbell Model

2. Inmon Model.

Kimbell model always structed as Denormalised structure.

Inmon model structed as Normalised structure.

Depends on the requirements of the company anyone can follow the
company's DWH will choose the one of the above models.

In Data warehousing contains the Two Methods

1.Top Down Method

2.Bottom up method

In
Top Down method: First load the Data marts and then load the data warehouse.

In
Bottom Up method: first load the Data warehouse and then load the Data marts.

Top Down approach is first Data warehouse then Data marts.

Bottom up approach is first Data marts then Data warehouse.

There are two approaches in Data ware housing named as

üTop
Down Approach

üBottom-up
Approach

Top down approach in the sense preparing individual departments
data (Data Marts) from the Enterprise Data warehouse

Bottom Up Approach is nothing but first gathering all the
departments data and then cleanse the data and Transforms the data and then
load all the individual departments data into the enterprise data ware house

What is active data
warehousing?

An active data warehouse provides information that
enables decision-makers within an organization to manage customer relationships
nimbly, efficiently and proactively. Active data warehousing is all about
integrating advanced decision support with day-to-day-even
minute-to-minute-decision making in a way that increases quality of those
customer touches which encourages customer loyalty and thus secure an organization's
bottom line. The marketplace is coming of age as we progress from
first-generation "passive" decision-support systems to current- and
next-generation "active" data warehouse implementations

Cheers,

Active Dataware house means

Every user can access the database any time 24/7

that is called Active dwh

Active Transformation means data can change and pass

A Data warehouse is a repository of integrated information,
available for queries and analysis. Data and information are extracted from
heterogeneous sources as they are generated. This makes it much easier and more
efficient to run queries over data that originally came from different
sources". Another definition for data warehouse is: " A data
warehouse is a logical collection of information gathered from many different
operational databases used to create business intelligence that supports
business analysis activities and decision-making tasks, primarily, a record of
an enterprise's past transactional and operational information, stored in a
database designed to favour efficient data analysis and reporting (especially
OLAP)". Generally, data warehousing is not meant for current
"live" data, although 'virtual' or 'point-to-point' data warehouses
can access operational data. A 'real' data warehouse is generally preferred to
a virtual DW because stored data has been validated and is set up to provide
reliable results to common types of queries used in a business.