Archive for "January, 2011"

Slowly Changing Dimensions – Type One Models

Data Warehousing, Posts Jan 31, 2011 No Comments

Type One – Overwrite the Record

Type one modeling of slowly changing dimensions is very simple and effectively handles updates to incorrect and/or outdated values within the dimension table. Type one models do not retain a history of changes and do not store previous values in any way. With this modeling technique, the data field that has changed is simply updated to reflect the most current value.  Since this technique does not preserve any previous value, it should only be used when there is no requirement to retain historical data.

Type one models makes sense when correcting data issues, but not when the system requires retrieval of historical values. Without a doubt, type one is the simplest method for handling slowly changing dimensions. However, best practice is to utilize this technique on a very limited basis. Moreover, it should only be implemented when there is no need to track the history of changes.

Example of Type One

First an electronics company loads its list of products within its data warehouse. Later the company discovers that a television which was originally manufactured in the USA is now manufactured in China, and the company has in fact never received a shipment of the older American-made televisions. The product description column in the table is simply updated, and the date and time of the update is entered in the “Last Update” column.

Type One - Slowly Changing Dimension

To correct the product description, the existing description is simply overwritten with the correct value, and the “Last Update” field is revised with the current date. Moreover, the product description is updated because the item is no longer manufactured in the United States. Utilizing the type one method of managing slowly changing dimensions, the record on the product dimension table is updated in-place in the following manner…
–  The product description is updated in-place to the new description.
–  The last update is updated to the date and time of the change.

What are Slowly Changing Dimensions?

Data Warehousing, Posts Jan 20, 2011 No Comments

Modern data warehouse design assumes that business transactions such as sales, orders, shipments, fulfillments, and receivables can occur at a rapid rate and each the details of each transaction needs to be recorded.  Hence a fact table with a dimensional model contains a separate record for each business transaction.  While in contrast, the describing or text-based values of the transaction or dimension often remain fairly constant.  Often, dimensional tables within the dimensional model do not take changes into account.

But in reality, dimensional values can and do change over time and numerous fields of a given row within a dimension table will need to be updated. This phenomenon in data modeling is known as “slowly changing dimensions” and it can be applied to any dimension table within a data warehouse schema.  Moreover, both simple and advanced modeling techniques have been established and can be implemented for handling updates and changes within a dimension table.  In addition, slowly changing dimensions assist the data warehouse in precisely recording the past values, providing an efficient method for tracking history, and allowing for the ability to respond to changes to descriptive values of transactions.

Examples of slowly changing dimensions include:
–  account name
–  customer phone number
–  vendor address
–  product description

These are good examples as they are text-based values that remain relatively constant, but can change and commonly do change over time.  Names, phone numbers, addresses are fairly intuitive and it is easy to see how these values can change slowly over time.  But let’s see how a product description could change…  A simple ingredient change or a packaging change in a product may be so trivial that the organization does not decide to give the product a new product id.  Rather the source system provides the data warehouse with a revised description of the product.  Hence the data warehouse needs to track both the old and new descriptions of the product.

Other good examples of common slowly changing dimensions are the region and territory names for a sales force. Many organizations have management that rename their region and territories on a regular basis or the management realigns their regions and territories along customer purchase patterns.   Typically the requirement of a data warehouse is to keep a record of the names of the regions and territories and the dates they were active.

Originally pioneered by Ralph Kimball, PhD, four main data modeling techniques have been established for managing dimension tables that contain slowly changing dimensions:

Type One – Overwrite the Record
Type Two – Update Record to Inactive / Create an Active Record
Type Three – Leverage Previous and Current Value Fields
Type Four – Insert Into a History Table

These four data modeling techniques range from the complete loss of historical data to an elegant but technically complex method of saving almost all historical data.  Choice of the appropriate technique by the database designer can ensure that the data warehouse contains required historical values and allows for comparisons of current data or data from other time periods.


Dimensional Modeling and Data Warehouses

Data Warehousing, Posts Jan 14, 2011 No Comments

Dimensional modeling is a specific discipline for modeling data that is an alternative to entity-relationship (E/R) modeling. A dimension model contains the same information as an E/R model but packages the data in symmetric format whose design goals are user understandability, query performance, and resilience to change.
Ralph Kimball, PhD, The Data Warehousing Lifecycle Toolkit, 1998

Basic Dimensional Model (Star Schema)

Basic Dimensional Model (Star Schema)

Dimensional modeling is a data modeling technique used to support on-line analytical processing (OLAP) systems and is implemented in databases that host either an enterprise data warehouses or data marts. The key point on the design of dimensional models is to resolve questions in the format “measures by dimensions.”  In addition, dimensional models are commonly referred to as star schema as they comprised of a central fact table surrounded by several dimension tables.

Within a dimensional model or star schema, there exists two types of data entities or tables
•  Facts (Measurements – Numerical Values)
•  Dimensions (Contexts and Attributes – Text, Strings, Dates, & Flags)
Transactional (OLTP) Systems to Analytical (OLAP) Systems

Transactional (OLTP) Systems to Analytical (OLAP) Systems

Within an enterprise data warehouse or data marts, data is fundamentally static, non-volatile and does not get updated.  Rather data is inserted or loaded in bulk into the tables in the model utilizing using batch programs or extraction, transformation, & loading (ETL) routines.  End-users of dimensional models develop queries that either read or select data, and there is no end-user inserting, updating, or deleting of data.  Data in dimensional databases requires data to be converted or extracted from on-line transactional processing (OLTP) or other OLAP systems.

The key benefits of dimensional models and data warehouses include ….
•  Separate environment from transactional systems
•  Allows for high-performance of select/read queries
•  Insulated from changes in source systems
•  Intuitive to developers and business users of queries
•  Contains data from multiple source systems
•  Optimized format for data warehouses and data marts

Business Intelligence Vendors and Products (2010)

Business Intelligence, Posts Jan 08, 2011 1 Comment

As of the end of 2010, the business intelligence (BI) software marketplace is in a state in which there are clear leaders or as Gartner, Inc puts it, “megavendors” that own roughly two-thirds of the $6 billion market.  In addition the rest of the BI vendors are more commonly referred to as “niche” or “pure-play” vendors.  These “niche” vendors each produce and sell viable business intelligence software products, but they do not encompass the breadth of complimentary technologies that the “megavendor” produces.  Regardless of the size of the vendor, both the “megavendors” and “pure-play” vendors do provide very capable and feature-rich software solutions.

Even though many of vendors listed below produce a number of related products, this list is only focused on business intelligence software products.

BI Software Vendors and Products (2010)

BI Software Vendors and Products (2010)

The following companies are currently known as megavendors
• Microsoft
• Oracle
And the following companies are now known as niche or pure-play vendors
• MicroStrategy
• Information Builders
• Actuate
• LogiXML
• QlikTech
• Pentaho
• Jaspersoft
• Panorama Software
• Tibco Software (Spotfire)
• Tableau Software
• Targit
• arcplan
• Board International
Facebook Like Button for Dummies