Defining OLAP Solutions and Data Warehouse design

This tutorial covers OLAP solutions used by Data warehouses and understanding Data Warehouse design. The enterprise needs to ask itself certain fundamental questions before actually launching on the process of designing the data warehouse. It must begin with a conviction that a data warehouse would really help its business and the return on investment will make it worth it.

Defining OLAP Solutions

The data warehouse offloads data from a multitude of sources. The cleaned, validated and loaded data is voluminous and daunting. This data needs to be organized, categorized and arranged in meaningful order for analytical purposes. OLAP solutions are specifically designed to cater to this need.

OLAP solutions used by Data warehouses are:

Multidimensional views of data. Data in the data warehouse is organized into subject oriented categories and tables. Fact tables are constructed and linked to various dimensional tables in star or snowflake schemas or combinations of them to form multidimensional views of data. Cubes are built using these multidimensional schemas. Rapid browsing and querying then becomes possible. These views are independent of the way in which data is stored in the data warehouse.

Interactive query and analysis of data is another OLAP solution that enables users drill down, drill up and slice data by using multiple passes. Users can drill down to successive lower levels of detail or roll up to higher levels of summarization and aggregation.

Analytical modeling is an OLAP tool that is a calculation engine for deriving ratios, variances etc., involving measurements and numerical data across many dimensions.

Functional models are made available by using OLAP for forecasting, trend analysis etc. They support users in data analysis.

Graphical OLAP tools are used to display data in 2D or 3D cross tabs and charts and graphs with easy pivoting of axis. This is important for users who need to analyze data from different perspectives and the analysis of one perspective leads to business questions that need to be examined from other perspectives.

Rapid response to queries is a must in any analysis of data and the measure of success for the OLAP tool. Nigel Pendse and Richard Creeth, authors of the OLAP Report developed the FASMI (Fast Analysis of Shared Multidimensional Information) test to judge whether or not an application qualifies to an OLAP tool. Their contention was that an OLAP tool should provide fast browsing capabilities (< five seconds), should contain analytical tools both for the developer and the end user; the cubes must be able to handle the security requirements of sharing confidential information and it should present data multi-dimensionally.

Multi dimensional data storage engine stores data in arrays. These arrays are logical representations of the business dimensions.

Understanding Data Warehouse design

At a very global level, construction of the data warehouse is a business project by itself. The enterprise needs to ask itself certain fundamental questions before actually launching on the process of designing the data warehouse. It must begin with a conviction that a data warehouse would really help its business and the return on investment will make it worth it.

The general questions that are asked may be as below...

Do we need a data warehouse?

How will it help the business?

What will it mean in terms of cost?

What are the current data analysis methodologies being adopted?

In what way are they deficient?

Will setting up the data warehouse help in reducing these deficiencies?

What kind of reporting and analysis do we really want?

What is that we are getting now?

Will such data analysis make the business more efficient?

Will it help the business improve its services and customer relations?

Once the replies to the above questions have been asked, the organization needs to examine other very crucial issues that will determine the wrap and hoof of the data warehouse that is being set up.

What are the kinds of data that are being generated by the enterprise? What kinds of data storage technologies are currently being used to backup and store historical data?

What other external sources of information do we need to tap to make the data in the data warehouse meaningful for analysis?

What kind of hardware and software will be required to set up this data warehouse?

Who will be the personnel to handle the process of creating the data warehouse?

Which departments will benefit from the data being created?

Will the data warehouse be scaleable?

How will it connect to the different data sources for data?

How will we ensure that quality data is generated?

What kinds of tools will be deployed to support end user needs for reports and analytics?

The answers that emerge from these questions will be a set of business requirements. These requirements will determine the kind of data warehouse that will be ultimately set up in the enterprise. The first steps would be to define the global parameters that will shape the design of the data warehouse. The design can be a top down approach as recommended by Bill Inmon or a bottom up approach recommended by Ralph Kimball. It can be a combination of the two called the Hybrid approach or it can be a federated approach. Let us have a brief look at what these different approaches mean.

For more visualization of this article along with the screen shots and complete free training tutorials on Microsoft Analysis Services visit

http://www.exforsys.com/content/category/17/253/332/

Exforsys is a community of developers specializing in C, C++, C#, Java, J2EE, .NET, PeopleSoft, SAP, Siebel, Oracle Apps., Data warehousing, Oracle/SQL Server/DB2 and Testing. Please visit http://www.exforsys.com for more tutorials, for IT Interview questions visit http://www.geekinterview.com, for articles and resources visit http://www.itquestionbank.com

---

Huddle Up; Groupware on Three

It could just be me, but my experiences with document... Read More

New Web-Based HR Tool is Max From NAS, Hannibal, and InfoLink

Three highly respected names in Human Resources have joined forces... Read More

Free PDF Publishing Software

In a previous article, I wrote about OpenOffice... Read More

Cisco Certification: Five Things To Do DURING Your CCNA Exam

There are plenty of articles out there about how to... Read More

Selecting Microsoft Great Plains Partner/VAR/Reseller: ERP Implementation & Customization ? Overview

In the case when you represent mid-size or mid-size-to-large business,... Read More

Keep Your Software Simple! A Review of EditPlus

I like my software simple. If it's too complex or... Read More

Microsoft CRM Customization Secrets ? Second Edition

This article is for advanced Microsoft CRM SDK C# developers.... Read More

Change Your Word Docs in Record Time!

Anyone who has ever used Microsoft Word knows that it... Read More

MultiNational Corporation ERP Implementation ? Microsoft Business Solutions Great Plains

If you look back to the history, you will see... Read More

Why Java RDBMS?

It is a well known fact that Java as a... Read More

Separate Anti-Keylogging Protection: Who Needs it Most?

If there still are few unprotected computers left, I haven't... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Great Plains Dexterity ? Microsoft Great Plains Customization Overview

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More

Constructionalist Parsing - Deciphering Natural Language

The research in the field of Natural Language Processing usually... Read More

CROOK: A Methodology for the Refinement of Forward-Error Correction

Table of Contents1) Introduction 2) Related Work 3) Framework 4)... Read More

Industry Experts Answer the One Million-dollar Question - Why Genie Backup Manager?

Many reasons made GBM a unanimous choice for experts, one... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise... Read More

Call Alert Notifications - Free Answering Machine Software for PCs

If you're online using a dialup Internet connection, you'll probably... Read More

Spyware Definitions List

The adware and spyware definitions list is very long. But... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in... Read More

Daffodil DB: Web Database

What is a Web Database?A web database is a database... Read More

Three Steps To Windows Safety Heaven

Now there are Three Steps To Heaven Just listen and... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market... Read More

XML Parser and Their Types

XML parser is a software module to read documents and... Read More

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software... Read More

The Dirt on Screensavers

Remember back in the days where screensavers were the coolest... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

Microsoft Great Plains: Service Business Customization & Integration Example

Microsoft Business Solutions Great Plains might be considered as ERP... Read More

Microsoft CRM Messaging through Lotus Domino eMail Server - Balanced Solution

Microsoft CRM and IBM Lotus Notes Domino seem to be... Read More

Vlans

In order to implement VLANs in a network environment, you'll... Read More

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More

Screenplay and Script Writing Software

When it comes to screenplay software each screenwriter needs to... Read More