Dimensional Modeling: Basic Concepts

Difference between OLTP and Decision Support Systems

The comparison can be well understood by looking at the above figure which depicts a moving car.

The OLTP systems are operational systems, which participate in day to day activities of the business. If business is the Car, OLTP systems are the wheels of the car, which help it move by loading data in the dbases about a single entity or “make” the wheels of the car turn. Examples would be

1.       Take an order

2.       Process  claim

3.       Make a shipment

4.       Generate an invoice

5.       Reserve an airline seat

The technological implementation would be a Java or .NET based web form at the front end, which is used as an input to store data in the backend which could be any dbase.

The Decision support system, on the other hand is more analytical in nature. With reference to our example, it would be “watching” the wheels of the car turn and predicting the behavior of car at different speeds and on different surfaces. In Business terminology, decision support systems would do the following:

1.       Show the top selling product for a particular product in a particular region

2.       Alert the manager, when a particular store sells a particular product, below a certain quantity.

Since the output data in the decision support systems is used for analytical purposes. It follows a different data model as compared to the traditional OLTP systems.  This is called as Dimensional Modeling.

We do need different types of decision support systems to provide strategic information. The type of information needed for strategic decision making is different from that available from operational systems. We need a new type of system environment for the purpose of providing strategic information for analysis, discerning trends, and monitoring performance.

Let us examine the desirable features and processing requirements of this new type of system environment. Let us also consider the advantages of this type of system environment designed for strategic information.

A New Type of System Environment

The desired features of the new type of system environment are:

1.       Database designed for analytical tasks - (achieved through Dimensional modeling)

2.       Data from multiple applications- (achieved through ETL tools like Informatica, Abinitio)

3.       Easy to use and conducive to long interactive sessions by users

4.       Read-intensive data usage

5.       Direct interaction with the system by the users without IT assistance (achieved through reporting  tools like Cognos )

6.       Content updated periodically and stable (achieved through Dimensional modeling)

7.       Content to include current and historical data (achieved through Dimensional modeling)

8.       Ability for users to run queries and get results online (achieved through reporting  tools like Cognos )

9.       Ability for users to initiate reports (achieved through reporting  tools like Cognos )

This new system environment that users desperately need to obtain strategic information happens to be the new paradigm of data warehousing. Enterprises that are building data warehouses are actually building this new system environment. This new environment is kept separate from the system environment supporting the day-to-day operations. The data warehouse essentially holds the business intelligence for the enterprise to enable strategic decision making.

Defining Business Requirements: Key to a successful data warehouse

The new methodology for determining requirements for a data warehouse system is based on business dimensions. It flows out of the need of the users to base their analysis on business dimensions. The new concept incorporates the basic measurements and the business dimensions along which the users analyze these basic measurements. Using the new methodology, you come up with the measurements and the relevant dimensions that must be captured and kept in the data warehouse. You come up with what is known as an information package for the specific subject.

 Our primary goal in the requirements definition phase is to compile information packages for all the subjects for the data warehouse. Once we have firmed up the information packages, we’ll be able to proceed to the other phases.

Essentially, information packages enable you to:

·         Define the common subject areas

·         Design key business metrics

·         Decide how data must be presented

·         Determine how users will aggregate or roll up

·         Decide the data quantity for user analysis or query

·         Decide how data will be accessed

The Requirement gathering phase involves extensive client interaction and interviews between the Vendor Team with the senior management, middle management, Business Analysts and the IT department of the client. Executives will give you a sense of direction and scope for your data warehouse. They are the ones closely involved in the focused area. The key departmental managers are the ones that report to the executives in the area of focus. Business analysts are the ones who prepare reports and analyses for the executives and managers. The operational system DBAs and IT applications staff will give you information about the data sources for the warehouse.