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.

there r 2 methodologies 1)kimball-first datamarts then EDWH 2)inmon-first EDWH then datamarts from edwh

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.