•

&n= bsp;

What is= a data warehouse?

A data warehouse is a corporate data repository created for = the reporting purposes and Business Intelligence. The data is extracted from le= gacy systems and then it is cleansed, scrubbed, and integrated to form the repository. For example, a customer’s records from multiple departments and legacy systems are unified to form a single record. Similarly, information regarding other entities such as products, services and supplier is extract= ed and integrated to form a single version of the truth to provide a consistent view of the corporate data.

This is data warehouse explained in its simplicity. A data warehouse is not a tool or syntax, but an architecture which is driven by t= he reporting requirements, and not by the legacy system. It is built increment= ally and in an iterative manner. It stores data for operational as well as analy= tical reporting and provides a platform for business intelligence.

What is Business Intelligence?

Business intelligence is a process of making business decisi= ons based on the data from the data warehouse. In the simplest form, business intelligence is efficiently using your data warehouse for achieving your bu= siness goals. It’s a comprehensive knowledge of all of the factors that affect your business, and a process of identifying sales trends, new business opportuni= ties and corporate weaknesses.

With business intelligence, you make timely decisions based = on a single version of the truth. It is used in the areas of customer profiling, customer support, market research, market segmentation, product profitabili= ty, statistical analysis, inventory and distribution analysis and many more.

Business Intelligence with data warehousing

Business intelligence is a term commonly associated with the= data warehouse and both are used interchangeably. Business intelligence refers to the information, and data warehouse refers to data. Business intelligence is transferring information into knowledge; data warehouse is converting data = into information. Business intelligence empowers corporate executives to make be= tter business decisions based on the data from the data warehouse.

Putting it into perspective, the repository of the corporate data is the data warehouse and usage of that repository is business intelligence. The data warehouse is the source of business intelligence reports. Business intelligence is merely the presentation layer of the data warehouse.

Benefit= s of a data warehouse

There are many benefits of creating and efficiently using a = data warehouse. One of the major benefits is an integrated view of the data collected from multiple platforms and source systems into a single report. =

The data wareho= use provides a single version of the truth. This means that no matter how the d= ata is accessed, who is accessing the data, and whatever the report format may = be - the result is always consistent.

The data warehouse enhances end-users access to a wide varie= ty of corporate data. Overall, it increases productivity and decreases computi= ng and report development cost. With a denormalized data warehouse, a query ca= n be written from a single dimension table instead of joining result sets from multiple tables.

The data warehouse allows capturing data history and creating snapshots. The data can be stored for a significant period of time to provi= de trends, patterns, periodic reports and other analyses.

Benefit= s of Business Intelligence

Business Intelligence eliminates a lot of guesswork within t= he organization. It enhances communication among departments and enables corpo= rate executives to respond quickly to the changes in the market, financial conditions, customer preferences, and dropping sales revenues. <= /span>

Business intelligence provides a platform for exploring data correlation. For example, leading retail stores periodically analyze their sales data to find items that are often bought together and then place them= next to each other for consumers’ shopping convenience.

Business intelligence enables the users with ‘What-if’ scena= rio types of reporting. This kind of reporting is extremely helpful for budgeti= ng and planning. For example, the sales manager can analyze the relationship between the training budget and its direct effect on the overall sales.

Business intelligence enables the corporate managers to automatically search large volumes of data for hidden patterns. It also provides sales trend reports, t= ime series analysis, and forecasting.

When do= you need a Data Warehouse?

Mainly, when your company is facing a data integration issue= and you have multiple reporting solutions without a consistent view of the corporate data. Also, when you have occurrences of the same data items at multiple locations with different ids and it takes weeks to integrate these data items into a single report. If a single version of the truth is not av= ailable for making business decisions, a data warehouse is a necessity.<= /span>

The big picture

The following figure shows corporate data scattered in multi= ple heterogeneous and independent applications. Mostly, these applications are completely disintegrated from each other and may not even be aware of the o= ther’s existence. This results in vast data fragmentation. For example, a customer= may exist as ‘David Brown’ in CRM, as ‘Mr. Brown’ in sales, and as ‘D. Brown’ i= n marketing leading to multiple occurrences of the same customer in different systems w= ith different ids. Similar may be the situation for your suppliers, products and services. As a result of this, the same customer and supplier may receive multiple invoices, marketing brochures, and emails.

<= /p>

Figure 1- The big picture

With the data warehouse architecture, fragmented data is integrated and stored in a single repository. The following example shows h= ow multiple records from sales, marketing and account receivables are combined together to form a single record of Mr. David Brown in the data warehouse:<= o:p>

Sales
ID	S00001DB
Name	David Brown
Address	10 Lexington Road, MA 01803
Sales Amount<= /p>	$1,500

Marketing=
ID	M0245
Name	D Brown
Address	Burlington

Account Receivables<= /span>
ID	ARDB19991210<= /p>
Name	Mr. D M Brown=
SS Number	123-45-6789
Address	01803
Telephone	508-555-1212<= /p>
Email	dbrown@aol.com
Paid Amount	$1,100

Data Warehouse
Customer ID	1001
Sales ID	S00001DB
Marketing ID<= /p>	M0245
AR ID	ARDB19991210<= /p>
Title	Mr.
First Name	David
Middle Initial	M
Last Name	Brown
SS Number	123-45-6789
Address Line 1	10 Lexington Road
Address Line 2
City	Burlington
State	MA
Zip Code	1803
Country	USA
Sales Amount<= /p>	$1,500
Paid Amount	$1,100
Balance Amount	$400
Credit Rating=	A
Telephone	508-555-1212<= /p>
Email	dbrown@aol.com

Usually, this kind of granular and operational data is store= d in the data warehouse in a special repository called Operational Data Store (O= DS).

What is= an ODS?

The Operational Data Stores (ODS) is a corporate data reposi= tory. It stores operational data organized around a specific business domain such= as Customer, Product, Supplier, Policy or Distribution. An ODS is usually desi= gned to contain low level or atomic (indivisible) data such as transactions and prices as opposed to aggregated or summarized data such as net contribution= s. The granularity of the data in the ODS depends on the reporting requirements. If the reports need sales data at the daily level, the ODS will have sales data summarized at the day level.

The ODS data is integrated and denormalized which means inst= ead of creating multiple tables to store many business relationships associated with a domain, only a single table is created. This is in contrast to the general theory that the ODS should be in a third normal form which defeats = the purpose of having a data warehouse. A data warehouse is created only for re= porting purposes and it is optimized for data retrieval and not for data storage. S= ome normalization may be required to preserve business relationships, but an ODS should be a denormalized schema.

What is= a Data Mart?

A data mart is departmental or subject oriented, denormalized and summarized data storage. It is generally star schema based architecture= . On rare occasions, a snowflake may be more suitable. The data marts differ from the ODS in terms of granularity and summarization. The data mart is used for specific groups of reports, users or departments and has a limited audience when compared with the ODS.

What is= OLAP and OLAP Cubes?

On Line Analytical Processing (OLAP) is an approach to quick= ly provide the answer to analytical queries that are dimensional in nature and OLAP cubes are data marts on a specialized multidimensional database. They store data similar to data marts, but the data is highly summarized and the underlying database structure is radically different than the relational databases.

What ar= e the components of a data warehouse? Is ODS the Data Warehouse?

&n= bsp;

Sometimes an ODS is considered to be a data warehouse. It sh= ould not be. The combination of the ODS, data marts and OLAP cubes should be the= data warehouse. This greatly simplifies the overall understanding of the data warehouse. In general, any data that is extracted from the legacy system for reporting purposes - operational, analytical, relational, or multidimension= al is part of the data warehouse.

&n= bsp;

What do= you need - ODS, Data Mart or OLAP Cube?

It all depends on your reporting requirements. If users need more analytical reports than operational reports, an ODS is not required an= d a data mart is more appropriate. If the reports involve complex queries, OLAP cubes are needed. Following query examples may help you to decide further:<= o:p>

Query	ODS	Data Mart=	OLAP Cube=
List all the customers in Zip c= ode 01801	Yes	No	No
What was the sales amount for Jan-2006?	Yes	No	No
Who are the insured and owners = of the policy?	Yes	No	No
How much commission was paid to= the agents in May-2005?	Yes	No	No
List customer information colle= cted from multiple sources	Yes	No	No
Show sales for the past four quarters/years	No	Yes	No
Allow drilling down sales from = years to quarters, and quarters to months	No	Yes	No
Show commission paid to the age= nts by product, and location	No	Yes	No
Show sales by location, product= and year	No	Yes	No
Who are the top five customers?=	No	Yes	No
What are the bottom five produc= ts in terms of sales?	No	Yes	No
Who are the top five customers = for the bottom five products?	No	No	Yes
List the products with annual s= ales dropped by 15% in the last eighth quarters and marketing budget slashed by 20%	No	No	Yes
Show the forecasted sales for n= ext six months, for the products that have been top seller for last six month= s	No	No	Yes
Compare Q1-2005 sales with last= or next year	No	No	Yes
Correlate sales data with marke= ting budget	No	No	Yes
What will be the affect on sales revenue if the sales training budget is dropped by 10%?=	No	No	Yes
Rank the products within a geography where sales have been up by 20%	No	No	Yes

It is critical to understand that an ODS may be able to answ= ers all the complex queries, but it will be a daunting task of writing SQLs beh= ind these queries.

The following graph illustrates the variation of data summar= ization, number of users and operational data with ODS, data mart and OLAP cube.

Figure 2 – variation by data ware= house components

&n= bsp;

&n= bsp;

How dat= a is stored in an ODS, Data Mart and OLAP Cube?

The ODS and data marts are created in a relational database = such as Oracle, Microsoft SQL Server, and DB2. The OLAP cubes are created in a multidimensional database such as Microsoft Analysis Services, Oracle OLAP,= and Hyperion. In relational databases, data is stored in a two dimensional tabl= es where as in multidimensional databases, it is stored in multidimensional variable= s.

Let’s take an example of a retailer company called Global Co= mputers. It has store outlets in all of the major cities in North America and Europe. Their major products Desktop, Laptop, Print= er and Copier are sold by catalog, internet and direct mailing channels.

For Global Computers, a typical ODS may look like this:

And the ODS may have following data:

	North America	Europe
Sales Channel	Catalog	Internet<= /p>	Direct mail	Catalog	Internet<= /p>	Direct Mail	Catalog	Internet<= /p>
Sales Date	Desktop	Laptop	Printer	Copier	Desktop	Laptop	Printer	Copier
10-Jan-2006	$50,000	$35,000		$60,000