Datawarehousing Tools

2009-11-02T08:18:00.000-08:00

New Evolutions

Many vendors in the traditional data consolidation market are positioning their products as either Extract-Transform-Load (ETL), Extract-Load-Transform (ELT), or maybe even Transform-Extract-Load (TEL) tools. Each vendor naturally touts the strengths of their adopted approach, and highlights the weaknesses inherent in those of their competitors.

So, which approach is best? The truth is that all approaches have their strengths and weaknesses, and it is likely that most organizations will find a need to use a combination of all of these techniques. Therefore, the real key to the alphabet soup of ETL vs. ELT vs. TEL, is flexibility and the ability to support the technique that best suits the job at hand. Molding a data flow that fits well into an ETL architecture into an ELT architecture, just because the tool lacks the ability to adequately support one process or the other, is a recipe for disaster.

WebSphere DataStage is an inherently flexible data consolidation tool that can natively support ETL, ELT, and TEL topologies. This article shows how the combination of WebSphere DataStage and WebSphere Federation Server can extend the alphabet soup by effectively supporting Transform-Extract-Transform-Load (T-ETL) data consolidation topologies. Within a T-ETL topology, WebSphere DataStage and WebSphere Federation Server complement each other in such a manner that significant performance benefits and CPU savings can be achieved relative to using WebSphere DataStage alone.

In this scenario, WebSphere Federation Server is able to perform processing close to the input sources so that less data is presented to the extraction stage, and less transformation needs to be done by WebSphere DataStage. This benefit is achieved because the T-ETL architecture plays exactly to the strengths of both products; WebSphere Federation Server for its cost-based optimizer and set processing efficiency in a heterogeneous environment, and WebSphere DataStage for its powerful parallel transformation and data flow engine.

The following section of this article provides a brief introduction to WebSphere Federation Server before describing the T-ETL architecture in more detail. The subsequent use-case scenarios sections detail four different cases that highlight the benefits of T-ETL. The general traits of WebSphere DataStage jobs that are likely to benefit from this architecture are then summarized.

Before Next

2008-08-27T00:33:00.000-07:00

Transition to Production

The Transition to Production stage moves the Data Warehouse development project into the production environment. The production database is created, and the extraction/cleanse/transformation routines are run on the operations system source data.

The development team works with the Operations staff to perform the initial load of this data to the Warehouse and execute the first refresh cycle.

The Operations staff is trained, and the Data Warehouse programs and processes are moved into the production libraries and catalogs.

Rollout presentations and tool demonstrations are given to the entire customer community, and end-user training is scheduled and conducted. The Help Desk is established and put into operation. A Service Level Agreement is developed and approved by the customer organization.

Finally, the new system is positioned for ongoing maintenance through the establishment of a Change Management Board and the implementation of change control procedures for future development cycles.

2008-08-27T00:31:00.000-07:00

Implementation

Once the Planning and Design stages are complete, the project to implement the current Data Warehouse iteration can proceed quickly. Necessary hardware, software and middleware components are purchased and installed, the development and test environment is established, and the configuration management processes are implemented.

Programs are developed to extract, cleanse, transform and load the source data and to periodically refresh the existing data in the Warehouse, and the programs are individually unit tested against a test database with sample source data. Metrics are captured for the load process.

The metadata repository is loaded with transformational and business user metadata. Canned production reports are developed and sample ad-hoc queries are run against the test database, and the validity of the output is measured. User access to the data in the Warehouse is established.

Once the programs have been developed and unit tested and the components are in place, system functionality and user acceptance testing is conducted for the complete integrated Data Warehouse system. System support processes of database security, system backup and recovery, system disaster recovery, and data archiving are implemented and tested as the system is prepared for deployment.

The final step is to conduct the Production Readiness Review prior to transitioning the Data Warehouse system into production. During this review, the system is evaluated for acceptance by the customer organization.

2008-08-27T00:29:00.002-07:00

Detail Design

In the Detail Design stage, the physical Data Warehouse model (database schema) is developed, the metadata is defined, and the source data inventory is updated and expanded to include all of the necessary information needed for the subject area implementation project, and is validated with users. Finally, the detailed design of all procedures for the implementation project is completed and documented.

Procedures to achieve the following activities are designed:

- Warehouse Capacity Growth

- Data Extraction/Transformation/Cleansing

- Data Load

- Security

- Data Refresh

- Data Access

- Backup and Recovery

- Disaster Recovery

- Data Archiving

- Configuration Management

- Testing

- Transition to Production

- User Training

- Help Desk

- Change Management

2008-08-27T00:28:00.001-07:00

Iteration Project Planning

The Data Warehouse is implemented (populated) one subject area at a time, driven by specific business questions to be answered by each implementation cycle. The first and subsequent implementation cycles of the Data Warehouse are determined during the BQA stage. At this point in the Process the first (or next if not first) subject area implementation project is planned.

The business requirements discovered in BQA and, to a lesser extent, the technical requirements of the Architecture Design stage are now refined through user interviews and focus sessions to the subject area level. The results are further analyzed to yield the detail needed to design and implement a single population project, whether initial or follow-on.

The Data Warehouse project team is expanded to include the members needed to construct and deploy the Warehouse, and a detailed work plan for the design and implementation of the iteration project is developed and presented to the customer organization for approval.

2008-08-27T00:27:00.001-07:00

Tool Selection

The purpose of this stage is to identify the candidate tools for developing and implementing the Data Warehouse data and application architectures and for performing technical and support architecture functions where appropriate. Select the candidate tools that best meet the business and technical requirements as defined by the Data Warehouse architecture, and recommend the selections to the customer organization. Procure the tools upon approval from the organization.

It is important to note that the process of selecting tools is often dependent on the existing technical infrastructure of the organization. Many organizations feel strongly for various reasons about using tools for the Data Warehouse applications that they already have in their "arsenal" and are reluctant to purchase new application packages. It is recommended that a thorough evaluation of existing tools and the feasibility of their reuse be done in the context of all tool evaluation activities. In some cases, existing tools can be form-fitted to the Data Warehouse; in other cases, the customer organization may need to be convinced that new tools would better serve their needs.

It may even be feasible that this series of activities is skipped altogether, if the organization is insistent that particular tools be used (no room for negotiation), or if tools have already been assessed and selected in anticipation of the Data Warehouse project.

Tools may be categorized according to the following data, technical, application, or support functions:

- Source Data Extraction and Transformation

- Data Cleansing

- Data Load

- Data Refresh

- Data Access

- Security Enforcement

- Version Control/Configuration Management

- Backup and Recovery

- Disaster Recovery

- Performance Monitoring

- Database Management

- Platform

- Data Modeling

- Metadata Management

2008-08-27T00:25:00.000-07:00

Architecture Review and Design

The Architecture is the logical and physical foundation on which the Data Warehouse will be built. The Architecture Review and Design stage, as the name implies, is both a requirements analysis and a gap analysis activity. It is important to assess what pieces of the architecture already exist in the organization (and in what form) and to assess what pieces are missing which are needed to build the complete Data Warehouse architecture.

During the Architecture Review and Design stage, the logical Data Warehouse architecture is developed. The logical architecture is a configuration map of the necessary data stores that make up the Warehouse; it includes a central Enterprise Data Store, an optional Operational Data Store, one or more (optional) individual business area Data Marts, and one or more Metadata stores. In the metadata store(s) are two different kinds of metadata that catalog reference information about the primary data.

Once the logical configuration is defined, the Data, Application, Technical and Support Architectures are designed to physically implement it. Requirements of these four architectures are carefully analyzed so that the Data Warehouse can be optimized to serve the users. Gap analysis is conducted to determine which components of each architecture already exist in the organization and can be reused, and which components must be developed (or purchased) and configured for the Data Warehouse.

The Data Architecture organizes the sources and stores of business information and defines the quality and management standards for data and metadata.

The Application Architecture is the software framework that guides the overall implementation of business functionality within the Warehouse environment; it controls the movement of data from source to user, including the functions of data extraction, data cleansing, data transformation, data loading, data refresh, and data access (reporting, querying).

The Technical Architecture provides the underlying computing infrastructure that enables the data and application architectures. It includes platform/server, network, communications and connectivity hardware/software/middleware, DBMS, client/server 2-tier vs.3-tier approach, and end-user workstation hardware/software. Technical architecture design must address the requirements of scalability, capacity and volume handling (including sizing and partitioning of tables), performance, availability, stability, chargeback, and security.

The Support Architecture includes the software components (e.g., tools and structures for backup/recovery, disaster recovery, performance monitoring, reliability/stability compliance reporting, data archiving, and version control/configuration management) and organizational functions necessary to effectively manage the technology investment.

Architecture Review and Design applies to the long-term strategy for development and refinement of the overall Data Warehouse, and is not conducted merely for a single iteration. This stage develops the blueprint of an encompassing data and technical structure, software application configuration, and organizational support structure for the Warehouse. It forms a foundation that drives the iterative Detail Design activities. Where Design tells you what to do; Architecture Review and Design tells you what pieces you need in order to do it.

The Architecture Review and Design stage can be conducted as a separate project that runs mostly in parallel with the Business Question Assessment stage. For the technical, data, application and support infrastructure that enables and supports the storage and access of information is generally independent from the business requirements of which data is needed to drive the Warehouse. However, the data architecture is dependent on receiving input from certain BQA activities (data source system identification and data modeling), so the BQA stage must conclude before the Architecture stage can conclude.

The Architecture will be developed based on the organization's long-term Data Warehouse strategy, so that future iterations of the Warehouse will have been provided for and will fit within the overall architecture.

2008-08-27T00:24:00.000-07:00

Business Question Assessment

Once a Business Case has been developed, the short-term strategy for implementing the Data Warehouse is mapped out by means of the Business Question Assessment (BQA) stage. The purpose of BQA is to:

- Establish the scope of the Warehouse and its intended use

- Define and prioritize the business requirements and the subsequent information (data) needs the Warehouse will address

- Identify the business directions and objectives that may influence the required data and application architectures

- Determine which business subject areas provide the most needed information; prioritize and sequence implementation projects accordingly

- Drive out the logical data model that will direct the physical implementation model

- Measure the quality, availability, and related costs of needed source data at a high level

- Define the iterative population projects based on business needs and data validation

The prioritized predator value stream or most important strategic initiative is analyzed to determine the specific business questions that need to be answered through a Warehouse implementation. Each business question is assessed to determine its overall importance to the organization, and a high-level analysis of the data needed to provide the answers is undertaken.

The data is assessed for quality, availability, and cost associated with bringing it into the Data Warehouse. The business questions are then revisited and prioritized based upon their relative importance and the cost and feasibility of acquiring the associated data.

The prioritized list of business questions is used to determine the scope of the first and subsequent iterations of the Data Warehouse, in the form of population projects. Iteration scoping is dependent on source data acquisition issues and is guided by determining how many business questions can be answered in a three to six month implementation time frame.

A "business question" is a question deemed by the business to provide useful information in determining strategic direction. A business question can be answered through objective analysis of the data that is available.

2008-08-27T00:23:00.001-07:00

Business Case Development

A variety of kinds of strategic analysis, including Value Stream Assessment, have likely already been done by the customer organization at the point when it is necessary to develop a Business Case. The Business Case Development stage launches the Data Warehouse development in response to previously identified strategic business initiatives and "predator" (key) value streams of the organization.

The organization will likely have identified more than one important value stream. In the long term it is possible to implement Data Warehouse solutions that address multiple value streams, but it is the predator value stream or highest priority strategic initiative that usually becomes the focus of the short-term strategy and first run population projects resulting in a Data Warehouse.

At the conclusion of the relevant business reengineering, strategic visioning, and/or value stream assessment activities conducted by the organization, a Business Case can be built to justify the use of the Data Warehouse architecture and implementation approach to solve key business issues directed at the most important goals. The Business Case defines the outlying activities, costs, benefits, and critical success factors for a multi-generation implementation plan that results in a Data Warehouse framework of an information storage/access system. The Warehouse is an iterative designed/developed/refined solution to the tactical and strategic business requirements.

The Business Case addresses both the short-term and long-term Warehouse strategies (how multiple data stores will work together to fulfill primary and secondary business goals) and identifies both immediate and extended costs so that the organization is better able to plan its short and long-term budget appropriation.

2008-08-27T00:22:00.001-07:00

The Data Warehouse Process

The james martin + co Data Warehouse Process does not encompass the analysis and identification of organizational value streams, strategic initiatives, and related business goals, but it is a prescription for achieving such goals through a specific architecture.

The Process is conducted in an iterative fashion after the initial business requirements and architectural foundations have been developed with the emphasis on populating the Data Warehouse with "chunks" of functional subject-area information each iteration. The Process guides the development team through identifying the business requirements, developing the business plan and Warehouse solution to business requirements, and implementing the configuration, technical, and application architecture for the overall Data Warehouse. It then specifies the iterative activities for the cyclical planning, design, construction, and deployment of each population project.

The following is a description of each stage in the Data Warehouse Process. (Note: The Data Warehouse Process also includes conventional project management, startup, and wrap-up activities which are detailed in the Plan, Activate, Control and End stages, not described here.)

2008-08-27T00:20:00.000-07:00

Data Warehouse Configurations

A Data Warehouse configuration, also known as the logical architecture, includes the following components:

- one Enterprise Data Store (EDS) - a central repository which supplies atomic (detail level) integrated information to the whole organization.

- (optional) one Operational Data Store - a "snapshot" of a moment in time's enterprise-wide data

- (optional) one or more individual Data Mart(s) - summarized subset of the enterprise's data specific to a functional area or department, geographical region, or time period

- one or more Metadata Store(s) or Repository(ies) - catalog(s) of reference information about the primary data. Metadata is divided into two categories: information for technical use, and information for business end-users.

The EDS is the cornerstone of the Data Warehouse. It can be accessed for both immediate informational needs and for analytical processing in support of strategic decision making, and can be used for drill-down support for the Data Marts which contain only summarized data. It is fed by the existing subject area operational systems and may also contain data from external sources. The EDS in turn feeds individual Data Marts that are accessed by end-user query tools at the user's desktop. It is used to consolidate related data from multiple sources into a single source, while the Data Marts are used to physically distribute the consolidated data into logical categories of data, such as business functional departments or geographical regions. The EDS is a collection of daily "snapshots" of enterprise-wide data taken over an extended time period, and thus retains and makes available for tracking purposes the history of changes to a given data element over time. This creates an optimum environment for strategic analysis. However, access to the EDS can be slow, due to the volume of data it contains, which is a good reason for using Data Marts to filter, condense and summarize information for specific business areas. In the absence of the Data Mart layer, users can access the EDS directly.

Metadata is "data about data," a catalog of information about the primary data that defines access to the Warehouse. It is the key to providing users and developers with a road map to the information in the Warehouse. Metadata comes in two different forms: end-user and transformational. End-user metadata serves a business purpose; it translates a cryptic name code that represents a data element into a meaningful description of the data element so that end-users can recognize and use the data. For example, metadata would clarify that the data element "ACCT_CD" represents "Account Code for Small Business." Transformational metadata serves a technical purpose for development and maintenance of the Warehouse. It maps the data element from its source system to the Data Warehouse, identifying it by source field name, destination field code, transformation routine, business rules for usage and derivation, format, key, size, index and other relevant transformational and structural information. Each type of metadata is kept in one or more repositories that service the Enterprise Data Store.

While an Enterprise Data Store and Metadata Store(s) are always included in a sound Data Warehouse design, the specific number of Data Marts (if any) and the need for an Operational Data Store are judgment calls. Potential Data Warehouse configurations should be evaluated and a logical architecture determined according to business requirements.

2008-08-27T00:13:00.000-07:00

Data Warehouses can be defined as subject-oriented, integrated, time-variant, non-volatile collections of data used to support analytical decision making. The data in the Warehouse comes from the operational environment and external sources. Data Warehouses are physically separated from operational systems, even though the operational systems feed the Warehouse with source data.

Subject Orientation

Data Warehouses are designed around the major subject areas of the enterprise; the operational environment is designed around applications and functions. This difference in orientation (data vs. process) is evident in the content of the database. Data Warehouses do not contain information that will not be used for informational or analytical processing; operational databases contain detailed data that is needed to satisfy processing requirements but which has no relevance to management or analysis.

Integration and Transformation

The data within the Data Warehouse is integrated. This means that there is consistency among naming conventions, measurements of variables, encoding structures, physical attributes, and other salient data characteristics. An example of this integration is the treatment of codes such as gender codes. Within a single corporation, various applications may represent gender codes in different ways: male vs. female, m vs. f, and 1 vs. 0, etc. In the Data Warehouse, gender is always represented in a consistent way, regardless of the many ways by which it may be encoded and stored in the source data. As the data is moved to the Warehouse, it is transformed into a consistent representation as required.

Time Variance

All data in Data Warehouse is accurate as of some moment in time, providing an historical perspective. This differs from the operational environment in which data is intended to be accurate as of the moment of access. The data in the Data Warehouse is, in effect, a series of snapshots. Once the data is loaded into the enterprise data store and data marts, it cannot be updated. It is refreshed on a periodic basis, as determined by the business need. The operational data store, if included in the Warehouse architecture, may be updated.

Non-Volatility

Data in the Warehouse is static, not dynamic. The only operations that occur in Data Warehouse applications are the initial loading of data, access of data, and refresh of data. For these reasons, the physical design of a Data Warehouse optimizes the access of data, rather than focusing on the requirements of data update and delete processing.

2008-08-27T00:01:00.000-07:00

Description

A Data Warehouse is not an individual repository product. Rather, it is an overall strategy, or process, for building decision support systems and a knowledge-based applications architecture and environment that supports both everyday tactical decision making and long-term business strategizing. The Data Warehouse environment positions a business to utilize an enterprise-wide data store to link information from diverse sources and make the information accessible for a variety of user purposes, most notably, strategic analysis. Business analysts must be able to use the Warehouse for such strategic purposes as trend identification, forecasting, competitive analysis, and targeted market research.

Data Warehouses and Data Warehouse applications are designed primarily to support executives, senior managers, and business analysts in making complex business decisions. Data Warehouse applications provide the business community with access to accurate, consolidated information from various internal and external sources.

The primary objective of Data Warehousing is to bring together information from disparate sources and put the information into a format that is conducive to making business decisions. This objective necessitates a set of activities that are far more complex than just collecting data and reporting against it. Data Warehousing requires both business and technical expertise and involves the following activities:

- Accurately identifying the business information that must be contained in the Warehouse

- Identifying and prioritizing subject areas to be included in the Data Warehouse

- Managing the scope of each subject area which will be implemented into the Warehouse on an iterative basis

- Developing a scaleable architecture to serve as the Warehouse’s technical and application foundation, and identifying and selecting the hardware/software/middleware components to implement it

- Extracting, cleansing, aggregating, transforming and validating the data to ensure accuracy and consistency

- Defining the correct level of summarization to support business decision making

- Establishing a refresh program that is consistent with business needs, timing and cycles

- Providing user-friendly, powerful tools at the desktop to access the data in the Warehouse

- Educating the business community about the realm of possibilities that are available to them through Data Warehousing

- Establishing a Data Warehouse Help Desk and training users to effectively utilize the desktop tools

- Establishing processes for maintaining, enhancing, and ensuring the ongoing success and applicability of the Warehouse

Until the advent of Data Warehouses, enterprise databases were expected to serve multiple purposes, including online transaction processing, batch processing, reporting, and analytical processing. In most cases, the primary focus of computing resources was on satisfying operational needs and requirements. Information reporting and analysis needs were secondary considerations. As the use of PCs, relational databases, 4GL technology and end-user computing grew and changed the complexion of information processing, more and more business users demanded that their needs for information be addressed. Data Warehousing has evolved to meet those needs without disrupting operational processing.

In the Data Warehouse model, operational databases are not accessed directly to perform information processing. Rather, they act as the source of data for the Data Warehouse, which is the information repository and point of access for information processing. There are sound reasons for separating operational and informational databases, as described below.

- The users of informational and operational data are different. Users of informational data are generally managers and analysts; users of operational data tend to be clerical, operational and administrative staff.

- Operational data differs from informational data in context and currency. Informational data contains an historical perspective that is not generally used by operational systems.

- The technology used for operational processing frequently differs from the technology required to support informational needs.

- The processing characteristics for the operational environment and the informational environment are fundamentally different.

The Data Warehouse functions as a Decision Support System (DSS) and an Executive Information System (EIS), meaning that it supports informational and analytical needs by providing integrated and transformed enterprise-wide historical data from which to do management analysis. A variety of sophisticated tools are readily available in the marketplace to provide user-friendly access to the information stored in the Data Warehouse.

2008-08-22T01:03:00.000-07:00

XML ETL Processing

XML

This section introduces XML (Extensible Markup Language) in data processing and explains basic XML concepts which might help understand the use of various ETL tools to process the XML data.

The sample business case shows how to implement ETL process to process the XML data from a web-based front-end to an OLTP system and to process the operational data periodically.

XML data processing concepts

The success and broad use of XML mainly derives from it being platform independent, text based, straightforward, easy to understand, human readable and user defined.
By far the most widely known and encountered form of XML is HTML, a markup language for web pages. In data integration, the XML standard is very often used to communicate between applications.

Most commercial ETL tools have dedicated components to handle the XML processing.

Each ETL process for the XML processing task comes with its own challenges and requires its own techniques. Most ETL tools usually provide various approaches to process and transform XML data.

However, most XML ETL processing tasks tend to yield to one of the two ways the data is interpreted and represented:

Event XML model is a way to interpret XML as a series of events. Each incoming XML data string is treated as a separate entity - an event. In the XML event approach each of those events is converted to a record in a data flow, where each tag and attribute value is stored in a separate field. This model can be set up quickly, it works with every kind of XML document and the elements can be adjusted as needed. However, the event model does not provide the same level of support for XML Schema, in many cases this model ignores some parts of the document which may result in incomplete data. The event model is a simple way of interpreting XML strings, and one that does not involve great effort to set up.

Full XML parse processing model - at the time of reading the XML document, an ETL tool component knows the exact document structure and parses it accordingly. It requires additional work effort to be done in the preliminary setup phase, however this approach increases performance and ensures the proper mapping of all values and nullable fields. Another outcome of the investment in this approach is a reduced need for populating downstream components as it is far more error proof.
The Full Object model representation is more sophisticated, the most powerful and useful choice when working with complex XML schemas.

Business Scenario

The hypothetical manufacturing company uses two operational systems:

an OLTP system which allows management of the operational data, including invoices, orders, customers, products, etc. The data is entered manually by the customer service representatives and other employees.

There is also a web-based application where the customers may place purchase orders online. There is no direct integration between the two systems and when an internet user places a new order, the order details are stored in an XML file on the web hosting server. A separate XML file is created for each new order and it contains data from the internet form.

These purchase orders are stored in a designated directory on the web server and the whole collection is processed on a daily basis (overnight).

The aim is to load the purchase orders data from the multiple XML files into an operational table with company's orders.

Proposed solution

In rough outline, the ETL process involves the following steps:

1. Move XML source files from the web server to the ETL server (to the unprocessed_files folder)

2. Process each of the XML files individually:

a. Parse XML file using the event XML approach

b. Pass through additional data to the processing flow, like the file name which contains dates and a timestamp

c. Validate records (check for example if a customer exists in the database and if the data is correct)

d. Assign order number to each purchase order

e. Feed the OLTP table with open orders

f. Feed the OLTP table with loading history with a corresponding status

3. Once the entire XML file is processed correctly, it is moved to the processed_files folder

2008-08-21T23:28:00.000-07:00

Questions on Data Warehousing concept

1. What is Data Warehouse?

2. What is difference between Data Warehouse and Data Mart ?

3. What is Star schema?

4. What is Snow-flake schema?

5. What is fact and dimension?

6. What is surrogate key?

7. What Normlisation ?Explain 3rd Normlised form?

8. What is the difference between OLTP and OLAP?

9. Are you involved in data modeling ?If yes which tool/tech you are using?

10. Which schema modeling techniques you ever used?

11. What do you mean by summary table?

12. What Degenerated Dimensions

13. What is fact less fact?

Oracle question based on data warehouse?

1 What is parallel execution

2 What is Bitmap and B-Tree indexes ? Explain Local Vs Global variables

3 What is materialised view

4 What is page size/array size in oracle?

5 What are integrity constraints ?

6 How can one tune SQL’s in Oracle?

2008-06-30T13:23:00.000-07:00

Star Schema Modelling

A Dimensional modelling technique in which a detail fact table is linked to dimension tables. The data in data warehouses and data marts is accessed by end-users.

The information contained in the data warehouse/data mart must be easy for the end-user to use and access. Denormalized designs are easier for end-users to use than highly normalized designs, however these designs are more difficult to design and maintain.

The Star Schema diagram graphically models the end-user's view (i.e., the denormalized view) of how the information is accessed.

Components of a Star Schema Diagram

The diagram has three main components:

· Fact Table and its contents: metric attributes and the foreign keys necessary to join to the dimension tables,

· Dimension Tables and their contents: reference attributes, hierarchical attributes, and metric attributes. The dimension tables are highly denormalized,

· The lines that link the Dimension Tables to the Fact Table.

Star Schema Model

This diagram is a model of a star schema diagram.

Star Schema Example

The following is an example of a star schema for sales items.

CONCEPT OF DATA PROFILING

2008-06-30T12:22:00.000-07:00

Data Profiling

In 2005 Similarity Systems acquired Evoke software to add the Evoke Axio profiling tool to the data quality suite and gain access to a wider customer base in the US and Asia. Similarity was in turn acquired by Informatica in January of 2006.

Informatica renamed Evoke Axio to Information Data Explorer and offers it as a stand alone product or as a companion to PowerCenter.

Data Explorer provides data profiling functions to discover and define the content, structure and quality of data.

It provides data mapping capabilities for source to target mapping that can be accessed from within PowerCenter.

PowerCenter also has a Mapping Generation Option that uses the free Informatica Data Stencil design tool for Microsoft Visio that generates PowerCenter mappings from Visio designs or reverse engineers a PowerCenter mapping into a Visio mapping template.

CONCEPT OF DATA QUALITY

2008-06-30T12:08:00.000-07:00

Data Quality

In January of 2006 Informatica acquired Similarity Systems to considerably enhance its data quality and data profile functionality. The Similarity ATHANOR product has been renamed to Informatica Data Quality.

It offers data cleansing, data matching and data quality reporting and monitoring. nformatica Data Quality processes can be added to a PowerCenter job within the PowerCenter Mapping Designer by selecting a data quality building block.

Informatica relies on additional third party vendors to deliver address validation capabilities to Data Quality. These products are licensed separately through Informatica.

There is also PowerCenter Data Cleanse and Match option that was built by Informatica prior to the Similarity acquisition.

It offers data cleansing and parsing and data matching and is fully integrated with PowerCenter to seemlessly use the Data Partitioning and Enterprise Grid capabilities for scalable processing.

2008-06-30T11:52:00.000-07:00

Metadata Management

Informatica has some technical metadata functions and reports in the PowerCenter product to support ETL development. In addition Informatica has the Metadata Manager and the Metadata Exchange Option.

In 2003 Informatica launched the SuperGlue metadata management solution for additional metadata capabilities. It is a web based solution for assembling, visualizing and analyzing metadata from various enterprise systems.

In 2005 Informatica launched PowerCenter Advanced Edition that came bundled with SuperGlue. With the release of PowerCenter 8 Advanced Edition the SuperGlue name is gone and the product is now known as Metadata Manager and is only available in this product bundle.

Informatica offers a PowerCenter option called Metadata Exchange that uses a wizard driven interface to import source definitions into PowerCenter. It provides more descriptive metadata and some metadata synchronization functions for when source metadata definitions change.

It offers a combination of bi-directional and uni-directional exchange of metadata to products such as ERwin, Oracle Designer, Cognos Impromptu and Business Objects Designer.

2008-06-30T11:31:00.000-07:00

What are mapping parameters and mapping variables?

Mapping parameter represents a constant value that you can define before running a session.A mapping parameter retains the same value throughout the entire session.

When you use the mapping parameter , you declare and use the parameter in a mapping or mapplet.Then define the value of parameter in a parameter file for the session.

Unlike a mapping parameter, a mapping variable represents a value that can change through out the session. The informatica server save the value of mapping variable to the repository at the end of session run and uses that value next time you run the session.

Can you use the mapping parameters or variables created in one mapping into another mapping?

NO, we can use mapping parameters or variables in any transformation of the same mapping or mapplet in which have crated mapping parameters or variables.

Can you are the mapping parameters or variables created in one mapping into any other result transformation.

Yes because the reusable transformation is not contained with any mapplet or mapping.

How the informatica server sorts the string values in rank transformation?

When the informatica server runs in the ASCII data movement mode it sorts session data using binary sort order.If you configures the session to use a binary sort order, the informatica server calculates the binary value of each string and returns the specified number of rows with the highest binary values for the string.

What is the rank index in rank transformation?

The designer automatically creates a RANKINDEX port for each Rank transformation. The informatica server uses the Rank Index port to store the ranking position for each record in a group.For example, if you create a Rank transformation that ranks the top 5 sales persons for each quarter, the rank index number the salespeople from 1 to 5.

2008-06-30T11:22:00.000-07:00

Difference between the source filter and filter?

Source filter is filtering the data only relational sources. Where as filter transformation filter the data any type of source.

what is a tracing level?

Amount of information sent to log file.

What are the types of tracing levels?

Normal,Terse,verbose data,verbose intitialization.

Expalin sequence generator transformation?

Sequence generator transformation is used to generate sequence of numbers. It is substitute to the Primary key. There should be atleast one current value to generate Next value.

Can you connect multiple ports from one group to multiple transformations?

Yes

Can you connect more than one group to the same target or transformation?

NO

What is a reusable transformation?

Reusable transformation can be a single transformation.This transformation can be used in multiple mappings.when you need to incorporate this transformation into mapping you add an instance of it to mapping.

Later if you change the definition of the transformation, all instances of it inherit the changes.Since the instance of reusable transformation is a pointer to that transformation.U can change the transformation in the transformation developer, its instance automatically reflect these changes. This feature can save U great deal of work.

What are the methods for creating reusable transformation?

Two methods

First method is through Transformation Developer. and Second is by Mapplet.

Design it in the transformation developer.

Promote a standard transformation from the mapping designer.After you add a transformation to the mapping, you can promote it to status of reusable transformation.

Once you promote a standard transformation to reusable status, you can demote it to a standard transformation at any time.

If u change the properties of a reusable transformation in mapping , you can revert it to the original reusable transformation properties by clicking the revert.

2008-06-30T11:16:00.000-07:00

What is default join that source qualifier provides?

Inner equi join.

What are the difference between joiner transformation and source qualifier transformation?

You can join heterogeneous data sources in joiner transformation, which we cannot achive in source qualifier transformation.

You need matching keys to join two relational sources in source qualifier transformation.where you doesn’t need matching keys to join two sources.

Two relational sources should come from same data source in source qualifier.You can join relational sources, which are coming from different sources in source qualifier.You can join relational sources which are coming from different sources also.

What is update strategy transformation?

Whenever you create the target table whether you are store the historical data or current transaction data in to target table.

Describe two levels in which update strategy transformation sets?

what is default source option for update strategy transformation?

Data driven.

What is data driven?

The informata server follows instructions coded into update strategy transformations with in the session mapping determine how to flag records for insert,update,delete or reject if u do not choose data driven option setting , the informatica server ignores all update strategy transformations in the mapping.

What are the options in the trarget session of update strategy transformation?

Insert
Delete
Update
Update as update
Update as insert
Update else insert
Truncate table.

2008-06-30T11:08:00.000-07:00

What is a slowly growing dimension?

Slowly growing dimensions are dimensional data,there dimensions increasing dimension data with out update existing dimensions.That means appending new data to existing dimensions.

What is a slowly changing dimension?

Slowly changing dimension are dimension data,these dimensions increasing dimensions data with update existing dimensions.

Type1: Rows containing changes to existing dimensional are update in the target by overwriting the existing dimension.In the Type1 Dimension mapping, all rows contain current dimension data.

Use the type1 dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

Type2: The Type2 Dimension data mapping inserts both new and changed dimensions into the target.Changes are tracked in the target table by versioning the primary key and creating a version number for each dimension in the table.

Use the Type2 Dimension/version data mapping to update a slowly changing dimension when you want to keep a full history of dimension data in the table.version numbers and versioned primary keys track the order of changes to each dimension.

Type3: The type 3 dimension mapping filters source rows based on user-defined comparisions and inserts only those found to be new dimensions to the target.Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension the informatica server saves existing data in different columns of the same row and replaces the existing data with the updates.

When you use for dynamic cache.

Your target table is also look up table then you go for dynamic cache .In dynamic cache multiple matches return an error.use only = operator.

What is lookup override?

Override the default SQL statement.You can join multiple sources use lookup override.By default informatica server add the order by clause.

We can pass the null value in lookup transformation?

Lookup transformation returns the null value or equal to null value.

what is the target load order?

You specify the target load order based on source qualifiers in a mapping.if u have the multiple source qualifiers connected to the multiple targets you can designate the order in which informatica server loads data into the targets.

2008-06-30T09:32:00.000-07:00

How many types of approaches in DHW?

Two approaches: Top-down(Bill-Inmon approach), Bottom-up(Ralph Kimball).

Explain Star Schema?

Star Schema consists of one or more fact table and one or more dimension tables that are related to foreign keys.

Dimension tables are De-normalized, Fact table-normalized.

Advantages: Less database space & Simplify queries.

Explain Snowflake schema?

Snow flake schema is a normalize dimensions to eliminate the redundancy.The dimension data has been grouped into one large table. Both dimension and fact tables normalized.

What is confirm dimension?

If both data marts use same type of dimension that is called confirm dimension.If you have same type of dimension can be used in multiple fact that is called confirm dimension.

2008-06-30T09:17:00.000-07:00

Difference between Power Center and Power Mart?

Power center receive all product functionality including ability to multiple register servers and metadata across the repository and partition data.

One repository multiple informatica servers. Power mart received all features except multiple register servers and partition data.

What is a staging area?

Staging area is a temporary storage area used for transaction, integrated and rather than transaction processing.

When ever your data put in data warehouse you need to clean and process your data.

Explain Additive, Semi-additive, Non-additive facts?

Additive fact: Additive Fact can be aggregated by simple arithmetical additions.

Semi-Additive fact: semi additive fact can be aggregated simple arithmetical. Additions along with some other dimensions.

Non-additive fact: Non-additive fact can’t be added at all.

What is a Fact less Fact and example?

Fact table which has no measures.

Explain Surrogate Key?

Surrogate Key is a series of sequential numbers assigned to be a primary key for the table.

2008-06-30T08:00:00.000-07:00

What is Data warehouse?

Data warehouse is relational database used for query analysis and reporting. By definition data warehouse is Subject-oriented, Integrated, Non-volatile, Time variant.

Subject oriented : Data warehouse is maintained particular subject.

Integrated : Data collected from multiple sources integrated into a user readable unique format.

Non volatile : Maintain Historical date.

Time variant : data display the weekly, monthly, yearly.

What is Data mart?

A subset of data warehouse is called Data mart.

Difference between Data warehouse and Data mart?

Data warehouse is maintaining the total organization of data. Multiple data marts used in data warehouse. where as data mart is maintained only particular subject.

Difference between OLTP and OLAP?

OLTP is Online Transaction Processing. This is maintained current transactional data. That means insert, update and delete must be fast.

OLAP is Online Analytical Processing which will be used to analyze the business and will be helpful in taking decisions in betterment of the business.

Explain ODS?

Operational data store is a part of data warehouse. This is maintained only current transactional data.

ODS is subject oriented, integrated, volatile, current data.

2008-06-10T06:18:00.001-07:00

ETL Concepts

Extraction, transformation, and loading. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database.

The first step in ETL process is mapping the data between source systems and target database(data warehouse or data mart). The second step is cleansing of source data in staging area. The third step is transforming cleansed source data and then loading into the target system.

Note that ETT (extraction, transformation, transportation) and ETM (extraction, transformation, move) are sometimes used instead of ETL.

Glossary of ETL

Source System

A database, application, file, or other storage facility from which the data in a data warehouse is derived.

Mapping

The definition of the relationship and data flow between source and target objects.

Metadata

Data that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata.

Staging Area

A place where data is processed before entering the warehouse.

Cleansing

The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.

Transformation

The process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources.

Transportation

The process of moving copied or transformed data from a source to a data warehouse.

Target System

A database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse.

Figure 1.12 : Sample ETL Process Flow

2008-06-10T06:16:00.000-07:00

ETL Tools: What to Learn?

With the help of ETL tools, we can create powerful target Data Warehouses without much difficulty. Following are the various options that we have to know and learn in order to use ETL tools.

Software:

» How to install ETL tool on server/client?

Working with an ETL Tool:

» How to work with various options like designer, mapping, workflow, scheduling etc.,?

» How to work with sources like DBMS, relational source databases, files, ERPs etc., and
import the source definitions?

» How to import data from data modeling tools, applications etc.,?

» How to work with targets like DBMS, relational source databases, files, ERPs etc., and import the source definitions?

» How to create target definitions?

» How to create mappings between source definitions and target definitions?

» How to create transformations?

» How to cleanse the source data?

» How to create a dimension, slowly changing dimensions, cube etc.,?

» How to create and monitor workflows?

» How to configure, monitor and run debugger?

» How to view and generate metadata reports?

2008-06-07T00:22:00.000-07:00

OLAP - Examples:

Topmost executives of an organization are really interested in aggregated facts or numbers to take decisions rather than querying several databases (that are normalized) to get the data and do the comparison by themselves.

OLAP tools visualize the data in an understandable format, like in the form of Scorecards and Dashboards with Key Performance Indicators enabling managers to monitor and take immediate actions.

In todays business life, OLAP plays a vital role by assisting decision makers in the field of banking and finance, hospitals, insurance, manufacturing, pharmaceuticals etc., to measure facts across geography, demography, product, and sales.

OLAP can be performed in data warehouses that undergo frequent updates and that do not. Following are some of the examples to show how OLAP solves complex queries involving facts to be measured across company’s best-interested dimensions.

Comparison of sales (fact) of a product (dimension) over years (dimension) in the same region (dimension).

How may members (fact) have opened a savings account (dimension), in USA branch (dimension), over a period (dimension) ?

How many mortgage loans (fact) have been approved in fixed mortgage (dimension) or Adjustable Rate Mortgage (dimension) in New York City (dimension), over a period (dimension) ?

What is the total sales value (fact) of a particular product (dimension) in a particular grocery store (dimension), over a period (dimension) ?

What is the amount spent (fact) for a particular product promotion (dimension) in a particular branch (dimension) or in a particular city (dimension), over a period (dimension) ?

2008-06-07T00:20:00.000-07:00

OLAP & its Hybrids

OLAP, an acronym for Online Analytical Processing is an approach that helps organization to take advantages of DATA. Popular OLAP tools are Cognos, Business Objects, Micro Strategy etc. OLAP cubes provide the insight into data and helps the topmost executives of an organization to take decisions in an efficient manner.

Technically, OLAP cube allows one to analyze data across multiple dimensions by providing multidimensional view of aggregated, grouped data. With OLAP reports, the major categories like fiscal periods, sales region, products, employee, promotion related to the product can be ANALYZED very efficiently, effectively and responsively. OLAP applications include sales and customer analysis, budgeting, marketing analysis, production analysis, profitability analysis and forecasting etc.

ROLAP

ROLAP stands for Relational Online Analytical Process that provides multidimensional analysis of data, stored in a Relational database(RDBMS).

MOLAP

MOLAP(Multidimensional OLAP), provides the analysis of data stored in a multi-dimensional data cube.

HOLAP

HOLAP(Hybrid OLAP) a combination of both ROLAP and MOLAP can provide multidimensional analysis simultaneously of data stored in a multidimensional database and in a relational database(RDBMS).

DOLAP

DOLAP(Desktop OLAP or Database OLAP)provide multidimensional analysis locally in the client machine on the data collected from relational or multidimensional database servers.

2008-06-07T00:18:00.000-07:00

OLAP Analysis

Imagine an organization that manufactures and sells goods in several States of USA which employs hundreds of employees in its manufacturing, sales and marketing division etc. In order to manufacture and sell this product in profitable manner, the executives need to analyse(OLAP analysis) the data on the product and think about various possibilities and causes for a particular event like loss in sales, less productivity or increase in sales over a particular period of the year.

During the OLAP analysis, the top executives may seek answers for the following:

1. Number of products manufactured.

2. Number of products manufactured in a location.

3. Number of products manufactured on time basis within a location.

4. Number of products manufactured in the current year when compared to the previous year.

5. Sales Dollar value for a particular product.

6. Sales Dollar value for a product in a location.

7. Sales Dollar value for a product in a year within a location.

8. Sales Dollar value for a product in a year within a location sold or serviced by an employee.
OLAP tools help executives in finding out the answers, not only to the above mentioned measures, even for the very complex queries by allowing them to slice and dice, drill down from higher level to lower level summarized data, rank, sort, etc.

Example of OLAP Analysis Report

Time Dimension Id	Location Dimension Id	Product Dimension Id	Organization Dimension Id	Sales Dollar	DateTimeStamp
1	1	100001	1	1000	1/1/2005 11:23:31 AM
3	1	100001	1	750	1/1/2005 11:23:31 AM
1	1	100001	2	1000	1/1/2005 11:23:31 AM
3	1	100001	2	750	1/1/2005 11:23:31 AM

In the above example of OLAP analysis, data can be sliced and diced, drilled up and drilled down for various hierarchies like time dimension, location dimension, product dimension, and organization dimension .

This would provide the topmost executives to take a decision about the product performance in a location/time/organization.

In OLAP reports, Trend analysis can be also made by comparing the sales value of a particular product over several years or quarters.

2008-06-07T00:17:00.000-07:00

OLAP Database - Multidimensional

This is a type of database that is optimized for data warehouse, data mart and online analytical processing (OLAP) applications. The main advantage of this database is query performance.

Relational databases make it easy to work with individual records, whereas multidimensional databases are designed for analyzing large groups of records. Relational database is typically accessed using a Structured Query Language (SQL) query.

A multidimensional database allows a user to ask questions like "How many mortgages have been sold in New Jersey city" and "How many credit cards have been purchased in a particular county?".

Popular Multidimensional Databases

Database Name	Company Name
Crystal Holos	Business Objects
Hyperion Essbase	Hyperion
Oracle Express	Oracle Corporation
Oracle OLAP Option	Oracle Corporation
AWMicrosoft Analysis Services	Microsoft
PowerPlay Enterprise	Cognos

2008-05-20T23:27:00.000-07:00

What are ETL Tools?

ETL Tools are meant to extract, transform and load the data into Data Warehouse for decision making. Before the evolution of ETL Tools, the above mentioned ETL process was done manually by using SQL code created by programmers.

This task was tedious and cumbersome in many cases since it involved many resources, complex coding and more work hours. On top of it, maintaining the code placed a great challenge among the programmers.

These difficulties are eliminated by ETL Tools since they are very powerful and they offer many advantages in all stages of ETL process starting from extraction, data cleansing, data profiling, transformation, debuggging and loading into data warehouse when compared to the old method.
There are a number of ETL tools available in the market to do ETL process the data according to business/technical requirements.

Following are some those.

>>Next>>