DATA WAREHOUSE

Trends in Data Warehousing: A Practitioner’s View

2009-01-22T08:50:00.000+05:30

Industries experience with data warehousing over the last decade has provided important lessons on what works in today’s business intelligence (BI) solutions. It is not only these lessons, but also the emerging trends which are also shaping our industry directions in business solutions. As a result, our emerging reference architectures used in building these enterprise data warehouse solutions are changing to meet business demands.
This evolving reference architecture used in building solutions will be overviewed, followed by the implications of these changes. It is these evolving reference architectures that are putting new demands on the databases that are used in warehousing. An important point is that although many of these concepts are not new, databases are being pushed in new ways which are requiring further technology invention.
With the emergence and evolution of the intranet, as well as more businesses exploiting semi-structured data, the more traditional business models are evolving with respect to such things as data accessibility, delivery, and concurrency. Technology such as XML and webservices become more critical as databases integrate with web portals and BI tooling. Moreover, additional demands on more broad decision making within enterprises are causing heavy consolidation and non-traditional mixed workloads (heavily mixing OLTP and DSS) beyond what has been conventional in the past. Service level agreements, as well as normal operational characteristics are not the same (e.g., backups). Moreover, in many cases the consolidation is not an option and or desired. In such latter cases, the business question still needs to be run. As a result, federation augmentation is also very real in enterprise systems. Query management in a federated environment is still a challenging task. A combination of consolidation and federation augmentation is being seen.
In addition to heavy consolidation and federation augmentation, both real-time (right-time) and active data warehousing systems are being built. These systems present interesting challenges to traditional maintenance and extract/transformation/load operational procedures. Specifically, in large multi-terabyte systems which are 24x7x365. Queries in such systems that execute over aggregated data (including materialized views) need to be very close in time to a consolidated operational data store (ODS) in the same enterprise data warehouse. The maintenance challenges are pushing the technology. Finally, the closed loop processing in an enterprise-wide solution, allows warehouses to play an even more crucial role. Not only are operational systems creating events, so are data warehouses; they play a crucial active role in an enterprise. One such example of events produced in a warehouse is measures, which may be key business indicators (KPIs) used in business performance monitoring through portals.
In addition to this talk presenting emerging data warehousing reference architectures, trends and directions shaping these enterprise data warehousing installations will be overviewed. In doing so, some key implications to databases will be highlighted. In addition to the database itself, any warehouse solution consists of a solution stack. Implications on the whole stack will be touched upon, including such things as metadata and interoperability via standard interfaces such as XML.

Data Warehousing Trends

2008-07-27T12:57:00.003+05:30

Friends , today Very few enterprises set out to there remedy data quality problems just for the sake of data quality. So what’s pushing enterprises to actually do something about data quality, instead of just talking about it? First, poor data quality costs them money in terms of lost productivity, faulty business decisions and an inability to achieve results from expensive investments in enterprise applications. Second, poor data quality can make regulatory compliance extremely difficult. It’s true that many companies have cleaned up their customer data to enable CRM-related initiatives. However, their focus has now turned to data in other areas of the business, such as supply chain and finance, and to tackling what can seem like intractable data quality problems in nearly every business domain.

Galaxy Schema

2008-04-28T11:22:00.001+05:30

Galaxy Schema is combination of more than one star schema.

Snow flake Schema

2008-04-28T11:11:00.002+05:30

The Snow Flake is also the simplest data warehouse schema. Because the diagram resembles a same as star schema .But only difference is dimension table is surrounded by sub-dimension table in snow flake schema

Star Schemas

2008-04-28T10:54:00.001+05:30

The Star Shema is the one of the simplest data warehouse schema. Because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.

Data Warehousing Schemas

2008-04-28T10:51:00.002+05:30

A schema is a collection of database objects, including tables, views, indexes, and synonyms. You can arrange schema objects in the schema models designed for data warehousing in a variety of ways. The model of your source data and the requirements of your users help you design the schema. We can sometimes get the source model from your company's enterprise data model and reverse-engineer the logical data model for the data warehouse from that. The physical implementation of the logical data warehouse model may require some changes to adapt it to your system parameters—size of machine, number of users, storage capacity, type of network, and software.There are three types of schema
1) Star Shema
2) Snow flake Shema
3) Galaxy Shema

Data Warehouse Architecture (with a Staging Area and Data Marts)

2008-04-28T10:46:00.002+05:30

We can do this by adding data marts, which are systems designed for a particular line of business. For example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales

Data Warehouse Architecture (with a Staging Area)

2008-04-28T10:34:00.003+05:30

We need to clean and process our operational data before putting it into the warehouse. So we can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and normal data warehouse management

Difference between Data Warehouse & Data Mart

2008-04-23T13:07:00.005+05:30

DATA WAREHOUSING Focuses enterprise level, With different Subject.
Example : IN LIC
we have loan & Insurance, for both of these if we create Data Warehouse then it is said to be Data Warehousing

DATA MART Focuses singal line of business.

Example : IN LIC
if we create separate Data Warehousing for both these, then it is said to be Data Mart.
Data Mart is sub set of DataWarehosuing

Data Warehousing Architecture

2008-03-30T08:24:00.002+05:30

Difference between Transactional and Warehoused Data

2008-03-30T08:21:00.003+05:30

TRANSACTION PROCESS Vs DECISION SUPPORT

1) Operational & Analytical

2) Simple Queries & Complex Queries

3) Results of 1-30 rows & Results of 1-30 million rows

4) Data manipulation & Data retrieval

5) Dynamic Data & Static Data.

DATA WAREHOUSING

2008-03-30T07:54:00.005+05:30

Data warehousing is essentially what you need to do in order to create a data warehouse, and what you do with it. It is the process of creating, populating, and then querying a data warehouse and can involve a number of discrete technologies such as:

· Source System Identification: In order to build the data warehouse, the appropriate data must be located. Typically, this will involve both the current OLTP (On-Line Transaction Processing) system where the "day-to-day" information about
The business resides, and historical data for prior periods, which may be contained in some form of "legacy" system. Often these legacy systems are not relational databases, so much effort is required to extract the appropriate data.

· Data Warehouse Design and Creation: This describes the process of designing the warehouse, with care taken to ensure that the design supports the types of queries the warehouse will be used for. This is an involved effort that requires both an understanding of the database schema to be created, and a great deal of interaction with the user community. The design is often an iterative process and it must be modified a number of times before the model can be stabilized. Great care must be taken at this stage, because once the model is populated with large amounts of data, some of which may be very difficult to recreate, the model can not easily be changed.

· Data Acquisition: This is the process of moving company data from the source systems into the warehouse. It is often the most time-consuming and costly effort in the data warehousing project, and is performed with software products known as ETL (Extract/Transform/Load) tools. There are currently over 50 ETL tools on the market. The data acquisition phase can cost millions of dollars and take months or even years to complete. Data acquisition is then an ongoing, scheduled process, which is executed to keep the warehouse current to a pre-determined period in time, (i.e. the warehouse is refreshed monthly).

· Changed Data Capture: The periodic update of the warehouse from the transactional system(s) is complicated by the difficulty of identifying which records in the source have changed since the last update. This effort is referred to as "changed data capture". Changed data capture is a field of endeavor in itself, and many products are on the market to address it. Some of the technologies that are used in this area are Replication servers, Publish/Subscribe, Triggers and Stored Procedures, and Database Log Analysis.

· Data Cleansing: This is typically performed in conjunction with data acquisition (it can be part of the "T" in "ETL"). A data warehouse that contains incorrect data is not only useless, but also very dangerous. The whole idea behind a data warehouse is to enable decision-making. If a high level decision is made based on incorrect data in the warehouse, the company could suffer severe consequences, or even complete failure. Data cleansing is a complicated process that validates and, if necessary, corrects the data before it is inserted into the warehouse. For example, the company could have three "Customer Name" entries in its various source systems, one entered as "IBM", one as "I.B.M.", and one as "International Business Machines". Obviously, these are all the same customer. Someone in the organization must make a decision as to which is correct, and then the data cleansing tool will change the others to match the rule. This process is also referred to as "data scrubbing" or "data quality assurance". It can be an extremely complex process, especially if some of the warehouse inputs are from older mainframe file systems (commonly referred to as "flat files" or "sequential files").

· Data Aggregation: This process is often performed during the "T" phase of ETL, if it is performed at all. Data warehouses can be designed to store data at the detail level (each individual transaction), at some aggregate level (summary data), or a combination of both. The advantage of summarized data is that typical queries against the warehouse run faster. The disadvantage is that information, which may be needed to answer a query, is lost during aggregation. The tradeoff must be carefully weighed, because the decision can not be undone without rebuilding and repopulating the warehouse. The safest decision is to build the warehouse with a high level of detail, but the cost in storage can be extreme.Now that the warehouse has been built and populated, it becomes possible to extract meaningful information from it that will provide a competitive advantage and a return on investment. This is done with tools that fall within the general rubric of "Business Intelligence".

Data Warehouse:

2008-03-30T07:50:00.002+05:30

The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: "A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process".

He defined the terms in the sentence as follows:

· Subject Oriented: Data that gives information about a particular subject instead of about a company's ongoing operations.

· Integrated: Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.

· Time-variant: All data in the data warehouse is identified with a particular time period.

· Non-volatile: Data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.

(Source: "What is a Data Warehouse?" W.H. Inmon, Prism, Volume 1, Number 1, 1995). This definition remains reasonably accurate almost ten years later. However, a single-subject data warehouse is typically referred to as a data mart, while data warehouses are generally enterprise in scope. Also, data warehouses can be volatile. Due to the large amount of storage required for a data warehouse, (multi-terabyte data warehouses are not uncommon), only a certain number of periods of history are kept in the warehouse. For instance, if three years of data are decided on and loaded into the warehouse, every month the oldest month will be "rolled off" the database, and the newest month added.

Ralph Kimball provided a much simpler definition of a data warehouse. As stated in his book, "The Data Warehouse Toolkit", on page 310, a data warehouse is "a copy of transaction data specifically structured for query and analysis". This definition provides less insight and depth than Mr. Inmon's, but is no less accurate.

Biographical Information

2008-03-30T07:48:00.002+05:30

Bill Inmon
Bill Inmon is universally recognized as the "father of the data warehouse." He has over 26 years of database technology management experience and data warehouse design expertise, and has published 36 books and more than 350 articles in major computer journals. His books have been translated into nine languages. He is known globally for his seminars on developing data warehouses and has been a keynote speaker for every major computing association. Before founding Pine Cone Systems, Bill was a co-founder of Prism Solutions, Inc.

Ralph Kimball
Ralph Kimball was co-inventor of the Xerox Star workstation, the first commercial product to use mice, icons, and windows. He was vice president of applications at Metaphor Computer Systems, and founder and CEO of Red Brick Systems. He has a Ph.D. from Stanford in electrical engineering, specializing in man-machine systems. Ralph is a leading proponent of the dimensional approach to designing large data warehouses. He currently teaches data warehousing design skills to IT groups, and helps selected clients with specific data warehouse designs. Ralph is a columnist for Intelligent Enterprise magazine and has a relationship with Sagent Technology, Inc., a data warehouse tool vendor. His book "The Data Warehouse Toolkit" is widely recognized as the seminal work on the subject.

INTRODUCTION TO DATA WAREHOUSE

2008-03-26T22:24:00.002+05:30

INTRODUCTION TO DATA WAREHOUSE

Most of the companies are having vast potential information in their organization. The data has all the information about the performance & the trends an organization has gone through during the past few years. If they can tap into this information they can improve the quality of decision making. The problem is that their operational systems were never designed to support this kind of business activity. To meet these needs new technology was introduced which is known as DATA WAREHOUSE.

DATA WAREHOUSE DEFINITION ( 1 )

· Foundation of Decision Support System

· To take facts based decisions

· Building intelligence to business.

WHAT IS BUSINESS INTELLIGENCE ?

· Converting information into knowledge.

· Converting information into actionable decisions.

· Discovering unknown things.

WHY A COMPANY NEEDS BUSINESS INTELLIGENCE?

· To make better decisions.

· Increase Sales.

· Deepen Customer relationships

· Build better products

· Provide better services

· Streamline operations

· Reduce costs.

BI TERMINOLOGY

· Data Warehouse

· Data Mart

· OLAP

· Mining

Start Learning DataWarehousing

2008-03-26T22:06:00.005+05:30

Hi Friend
I am a Data Warehousing developer. I have some basic knowledge in Data warehousing and ETL and Reporting tools. In my blog i decide to share that knowledge with other. I think sure it will be use for readers. If u have any doubts mail me sundar.skec@gmail.com. friends learn the content in date wise then it will be easy to understand.

Introduction to Data Warehousing:

Data Warehousing Concept
Difference between Transactional and Warehoused Data
Data Warehousing Architecture
Logical & Physical Design
Difference between Data Warehouse & Data Mart
Source Data ,Staging Area & Target Data
Operational Data Source (ODS)
Star Schema & Snow Flake Schema

Data Warehouse Design and Modeling:

Dimension Model (Schema) for Data Warehouse

Defining Dimensions And Measures
Defining Levers and Hierarchies
Granularity of data
Introducing Star Schema
Introducing Snow Flake Schema
Dimension Table And Fact Table

ETL (Extract, Transform & Load) Using Informatica:

Designer : Defining Target for Staging data base & Data Warehouse
Designer : Creating Multi-dimensional Cubes
Designer : Creating Mapping (with different transformation) and Mapplets
Transformation

Filter
Router
Sorter
Rank
Aggregator
Expression
update staterge
Lookup
Normalizer
stored Procedure
source qualifier

Task

Session
command
Email
Event Wait
Event Raise
Timer
Assignment
Control

Server Manager : Creating Sessions and batches
Concepts Workflow monitor

OLAP & PRESENTION using COGNOS:

Impromptu Administration : Defining database and setting up catalog
Impromptu Administration : Creating and Managing Folders, Prompts, Filter, Formula
Impromptu Administration : Defining Classes, Users & Privileges
Impromptu Administration : Generating & Formulating Falt Reports
Impromptu Administration : Generating Query Definition File
Cognos Powerplay Transformer : Creating Dimensions and Measures
Cognos Powerplay Transformer : Generating Cubes, Manipulating Cube Properties
Cognos Powerplay Transformer : Generating Multi-Dimensional reports using cube
Cognos Powerplay Transformer : Formatting & Printing Reports
Cognos Powerplay Transformer : Drill-down, Drill-up and Drill-through Reports
Cognos Powerplay Transformer : Generating Chats using cube

OLAP & PRESENTATION using BUSINESS OBJECTS

Supervisor: Creating Repository and Supervisor
Supervisor: Database Connections
Supervisor: Defining Groups and Users
Supervisor: Defining Privileges and Profiles for users
Supervisor: Scheduling Users Connection Sessions
Designer: Creating and Maintaining Universe
Designer: Defining Tables & Columns as Classes and Objects
Designer: Creating and Maintaining measures
Designer: Defining Relations, Formulas etc
Business Object: Defining Query, Reports Type
Business Object: Formatting Reports
Business Object: Slicing & Dicing Reports
Business Object: Drill-down, Drill-up Reports
Business Object: Filtering Records, Grouping Records etc.

History

2008-03-25T10:16:00.002+05:30

The concept of data warehousing dates back to the mid-1980s [3] when IBM researchers Barry Devlin and Paul Murphy developed the "information 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 environment 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:

1983 - Teradata introduces a database management system specifically designed for decision support.
1986 - 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 "information 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.

Data warehouses versus operational systems

2008-03-24T16:29:00.002+05:30

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 defines 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.

Top-down versus bottom-up design methodologies

2008-03-24T14:47:00.003+05:30

Top-down design

Bill Inmon, one of the first authors on the subject of data warehousing and the man credited with coining the term "data warehouse", has defined a data warehouse as a centralized repository for the entire enterprise.^[3] 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.^[4]

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.^[3]

Bottom-up design

Ralph Kimball, another well known author on data warehousing, defines a data warehouse as "a copy of transaction data specifically structured for query and analysis."^[5] Kimball 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 eventally 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".^[3]

The bottom-up approach to data warehouse design provides the advantage of a quick turnaround. Business value can be returned as quickly as the first data marts can be created. However, a long term risk of this approach is inconsistencies in the multiple data marts and the resulting multiple "version of the truths" seen by users retrieving data from the data marts.^[3] Conforming dimensions among data marts and maintaining tight management over the data warehouse bus architecture can help mitigate these risks.

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 the fast turn-around time of bottom-up design and the enterprise-wide data consistency of top-down design.

Data warehouse Layer

2008-03-21T17:03:00.002+05:30

There is no widespread agreement on exactly what constitutes a data warehouse architecture. Though they may not be contradictory, views differ as to the relative importance of the possible components. One possible conceptualization of a data warehouse architecture consists of the following interconnected layers:

Operational database layer: The source data for the data warehouse
Informational access layer: The data accessed for reporting and analyzing and the tools for reporting and analyzing data
Data access layer: The interface between the operational and informational access layer
Metadata layer: The data directory (which is often much more detailed than an operational system data directory).

Benefits of data warehousing

2008-03-21T10:28:00.003+05:30

A data warehouse provides a common data model for data, regardless of the data's source. This makes it easier to report and analyze information than it would be if multiple data models from disparate sources 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 fast retrieval of data without slowing down operational 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.
Data warehouses can work in conjunction with and, hence, enhance the value of operational business applications, notably customer relationship management (CRM) systems.

INTERVIEW QUESTION

2008-01-28T13:21:00.003+05:30

1. What is source qualifier?
2. Difference between DSS & OLTP?
3. Explain grouped cross tab?
4. Hierarchy of DWH?
5. How many repositories can we create in Informatica?
6. What is surrogate key?
7. What is difference between Mapplet and reusable transformation?
8. What is aggregate awareness?
9. Explain reference cursor?
10. What are parallel querys and query hints?
11. DWH architecture?
12. What are cursors?
13. Advantages of de normalized data?
14. What is operational data source (ODS)?
15. What is meta data and system catalog?
16. What is factless fact schema?
17. What is confirmed dimension?
18. What is the capacity of power cube?
19. Difference between PowerPlay transformer and power play reports?
20. What is IQD file?
21. What is Cognos script editor?
22. What is difference macros and prompts?
23. What is power play plug in?
24. Which kind of index is preferred in DWH?
25. What is hash partition?
26. What is DTM session?
27. How can you define a transformation? What are different types of transformations in Informatica?
28. What is mapplet?
29. What is query panel?
30. What is a look up function? What is default transformation for the look up function?
31. What is difference between a connected look up and unconnected look up?
32. What is staging area?
33. What is data merging, data cleansing and sampling?
34. What is up date strategy and what are th options for update strategy?
35. OLAP architecture?
36. What is subject area?
37. Why do we use DSS database for OLAP tools?

Resources for Data Warehousing, DataMining & Business Intelligent

2007-10-31T23:17:00.002+05:30

What is a data warehouse?

"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."