An Introduction to Data Warehousing

1
An Introduction to Data Warehousing

• By Vivek R. Gupta,Senior Consultant,System
  Services corporation,Chicago, Illinois

2
Evolution of an application class

• Traditional approaches to historical data
• Data from legacy systems
• Extracted information on the Desktop
• Decision-Support and Executive Information
  Systems

3
Evolution of an application class

• Emergence of key enabling technologies
• Hardware prices plummeting according to the
  Moores law
• Desktop power increasing
• Ever increasing power of server software
• Explosion of Intranets and Web based applications

4
Evolution of an application class

• Emergence of key enabling technologies

5
Evolution of an application class

• Change in the nature of the business
• Economic factors of the recent years
• Global corporation
• Emergence of standard business applications
• End-user more technology savvy
• Management more information conscience

6
Evolution of an application class

• Change in the nature of the business

7
Data warehousing attributes and concepts

• Warehousing data outside the operational
  systems
• Integrating data from more than one operational
  system
• Differences between transaction and analysis
  processes
• Data is mostly non-volatile
• Data saved for longer periods than in transaction
  systems

8
Data warehousing attributes and concepts

• Warehousing data outside the operational systems

9
Data warehousing attributes and concepts

• Logical transformation of operational data
• Structured extensible data model

10
Data warehousing attributes and concepts

• Logical transformation of operational data
• Data warehouse model aligns with the business
  structure

11
Data warehousing attributes and concepts

• Logical transformation of operational data
• Transformation of the operational state
  information

12
Data warehousing attributes and concepts

• Logical transformation of operational data
• De-normalization of data
• First Normal Form a single entity and it
  contains no arrays or repeating attributes
• Second Normal Form in addition to the First
  Normal Form properties, all attributes are fully
  dependent on the primary key for the relation
• Third Normal Form in addition to Second Normal
  Form, all non-key attributes are completely
  independent of each other

13
Data warehousing attributes and concepts

• Logical transformation of operational data
• Static relationships in historical data

14
Data warehousing attributes and concepts

• Physical transformation of operational data
• Operational terms transformed into uniform
  business terms
• Single physical definition of an attribute
• Consistent use of entity attribute values
• Issues associated with default and missing values

15
Data warehousing attributes and concepts

• Physical transformation of operational data

16
Data warehousing attributes and concepts

• Business view summarization of data
• Initial analysis in summary views
• Summary views generates the detail data by
  applying business rules
• While generating summary views, the data
  warehouse may perform complex database operations

17
Data warehousing attributes and concepts

• Business view summarization of data
• Significant performance gains
• Time-consuming data analysis can be easily done
  with summarization of data
• All the complexities of detail data can be easily
  interpreted for an end user
• Many views into the same detail
• Providing multiple views into the same detail
  data
• Providing an efficient method for the analyst to
  link with the detail data when necessary

18
Data warehousing attributes and concepts

• Business view summarization of data

19
Data warehousing attributes and concepts

• Definition of Data warehouse
• A structured extensible environment designed for
  the analysis of non-volatile data, logically and
  physically transformed from multiple source
  applications to align with business structure

20
Business use of a data warehouse

• Tools to be used against the data warehouse
• Spreadsheets for low-end users
• multi-dimensional analysis tool for active
  high-end users
• Both tools can be used with data warehouse
  software

21
Business use of a data warehouse

• Standard reports and queries
• Dataware users need a set of reports and queries
• Standard tools are useful to share the work with
  others
• Advanced analysis can be possible with such tools

22
Business use of a data warehouse

• Queries against summary tables
• Simple filtering and summation from the summary
  views accounts for most of the analysis activity
  against many data warehouses
• Summary views contain predefined standard
  business analysis

23
Business use of a data warehouse

• Data mining in the detail data
• Starting with summary data and drills down into
  the detail data
• Looking for arguments to prove or disprove a
  hypothesis
• Evolving rapidly to understand the behavior of
  business units

24
Business use of a data warehouse

• Interface with other data warehouses
• A single interface with the data is much easier
  and more function than multiple interfaces with
  the operation application
• Dataware house cannot be source of all operation
  system interfaces

25
Business use of a data warehouse

• Interface with other data warehouses