MSBI : BI # 33 : Business Intelligence – Tools & Theory # 25 : Introduction to Data Warehousing #3 : OLAP Tools & Internet

Hi Folks,

This post is part of Series Business Intelligence – Tools & Theory

Currently running topic for this series is listed as below :

Series Business Intelligence – Tools & Theory

>>Chapter 1 : Business Intelligence an Introduction

>>Chapter 2 : Business Intelligence Essentials

>>Chapter 3 : Business Intelligence Types

>>Chapter 4 : Architecting the Data

>>Chapter 5 : Introduction of Data Mining

>>Chapter 6 : Data Mining Techniques

>>Chapter 7 : Introduction to Data Warehousing<You are here>

Continuing from my previous post on this series, If you have missed any link please visit link below

We are going to Cover the Following Points in this article

  • OLAP Tools
  • OLAP Data Modeling
  • OLAP Tools and the Internet

OLAP Tools

Online Analytical Processing falls under the group of software tools which enables the analysis of data stored in a database. It is often used in data mining. Using this tool, user can analyze various dimensions of a multidimensional data. For example, it provides both the time analysis and the trend analysis views. There are two types of OLAP tools. They are:

Multidimensional OLAP (MOLAP): In this type of OLAP a cube is extracted from the relational data warehouse. Once the user generates a report request, the MOLAP tools responds quickly as the data is extracted.

Relational OLAP (ROLAP): In this type of OLAP, the data is not extracted. The ROLAP engine behaves like a smart SQL generator. The ROLAP tool comes with a ‘Designer’ piece, where the data warehouse administrator can not only specify the relationship between the relational tables, but also how dimensions, attributes, and hierarchies map to the database tables.

As of now the ROLAP and MOLAP traders are going in for the combination of both. MOLAP traders find it necessary to get down to low levels of details at times and ROLAP traders find it important to deliver results to users at a rapid pace. Hence the traders find it essential to merge both the tools

OLAP Data Modeling

In the OLAP data model the data is viewed as data cube that consists of measures and dimensions as mentioned earlier.

Each level of data in the dimension can be arranged as a hierarchy with levels of detail. For example, the dimension time can have levels such as days, months and years. Each level in the hierarchy will have specific values at a particular given instance. While viewing a database, a user can either move up or down to view less or more detailed information between levels.

Aggregation and Storage Models

The fundamental of multidimensional navigation of OLAP is cubes dimensions, hierarchy, and measures. When data is presented in this manner, users can use a complex set of data automatically.

The ideology of OLAP is to provide consistent response times for all operations the users request for. The information summary is worked out before hand as the data is always collected at the detail level only. These pre-computed values are the basis of performance gains in OLAP.

When OLAP technology began, few data warehousing dealers believed that the only solution for OLAP application is a specialized, non-relational storage model. Later, however, the dealers found that relational database management system (RDBMS) could be used for OLAP through database structures (Star and Snowflakes Schema), indexing and storing aggregates. They called their technology as Relational OLAP (ROLAP). Then the earlier traders adopted the name Multidimensional OLAP (MOLAP).

MOLAP can perform better than ROLAP technology but has issues with scalability. ROLAP is scalable. Customers prefer ROLAP as it implements existing relational database technology.

Hybrid OLAP (HOLAP) has been developed. It combines the best features of ROLAP and MOLAP architectures that are better performance and high scalability.

Building the OLAP Data Model

The challenge in building an OLAP data model is the mapping of the initial database model to the multidimensional model. This involves certain programming skill. OLAP database design has become an indispensable process in the development of OLAP products as it links with the OLAP technology being set up. As a result, OLAP database developers are specialized, which has led to high costs in developing applications which is concentrated at the data design stage.

OLAP Tools and the Internet

The two most persistent topics in computing have been the Internet and data warehousing. The combination of these two is of high importance. The reason is simple, the advantages of using web for access increases with data warehouse.

The reasons why internet is considered as a best decision support medium is as follows:

· Internet provides connectivity within and between companies.

· It makes managing complex administrative tasks easy even in a distributed environment.

· Data can be stored and managed on a server thus making updating and manipulating data centrally an easy task. Hence problems with software companies and data currency can be solved.

The general features of data access using the internet are as follows:

· In the first generation, clients accessed static HTML pages through web browsers. This was the static distribution model in which decision support reports were stored as HTML requests and on request from the user the results were displayed. This is inefficient as it does not provide web clients with interactive analytical efficiency such as drill down.

· The second generation utilizes a multitier architecture to process database queries. The web clients submit the request in the form of a HTML encoded request to a web server. Then, this web server sends the query in the form of structured data into CGI (Common Gateway Interface) script. It is the gateway which submits SQL queries to the database, receives an answer, transforms it into a HTML and then sends the page to the requester. Figure depicts the multitier architecture to process database queries which was used in the second generation.


Multitier Architecture to Process Database Queries

· The third generation is the upcoming technology. Web based application server replaces HTML gateways. These servers download Java applets or the Active X applications that executes on the clients end, or it may correspond with the applets running on the server side- servlets. This model provides users with the advantage of existing decision support applications without the necessity to load additional software except for a web browser.

Hope you will like Series Business Intelligence – Tools & Theory series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s