Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server

OLAP: An explorative walk with Infinite possibilities

Rate me:
Please Sign up or sign in to vote.
4.88/5 (6 votes)
23 Jun 2013CPOL5 min read 15.1K   9   1
A Practical approach for implementing Interactive query based systems using MSOLAP

Introduction 

It is now well known that data warehousing/Online Analytical Processing offers an approach that increases the level of productivity and improves the decision-making process of knowledge workers within an organization.

The ability to act quickly and decisively in today’s increasingly competitive marketplace is critical to the success of organizations. The volume of information that is available to corporations is rapidly increasing and frequently overwhelming. Those organizations that will effectively and efficiently manage these tremendous volumes of data, and use the information to make business decisions, will realize a significant competitive advantage in the marketplace.

OLAP enables analysts, business managers, and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information. OLAP transforms raw data so that it reflects the real dimensionality of the enterprise as understood by the user.

Analytical Technology itself is boundless. There are infinite possibilities in the research and development industry for Analytical technology. We will just describe a single implementation that shows how OLAP plays a vital role in Query based interactive systems. 

Background 

Data warehousing, the creation of an enterprise-wide data store, is the first step towards managing these volumes of data. The data warehouse is becoming an integral part of many information delivery systems because it provides a single, central location where a reconciled version of data extracted from a wide variety of operational systems is stored. 

Building a data warehouse has its own special challenges (common data model, common business dictionary, etc.) and is a complex endeavor. However, just having a data warehouse does not provide organizations with the often-heralded business benefits of data warehousing. To complete the supply chain from transactional system to decision maker, IT organizations need to deliver systems that allow knowledge workers to make strategic and tactical decisions based on the information stored in these data warehouses. These decision support systems are referred to as On-Line Analytical Processing (OLAP) Systems. 

 OLAP 

OLAP presents a complex viewing system (an example might be a graph) to represent data. We can compare it with graphs, rather its more generalized form, extending to nth dimension. Graphs are the most favorite option to get better understanding of the problem and its domain. 

 

 

Image 1

 

OLAP can also be defined as:<o:p>

OLAP = Heterogeneous Data sources + Multidimensional Data storage  + Querying Tool

                        (Data warehouse)                              (Cubes)                     (MDX)<o:p>

From now on I will discuss the technical side of OLAP. Here we must mention some terms that are necessary to understand before we go deeper. A diagram will show the architecture of OLAP and how it will integrate with other layers.<o:p> 

Image 2

Data warehouse and the Data Marts 

As we have defined Data warehouse, Data Mart term left for some discussion. Data ware house and the data marts are defined and used in distinct ways in different data warehousing systems. The simplest definition of Data mart can be is a small collection of data that is used for the business analysis queries of a single department or work group. Basically it’s just a logical categorization of sub systems data within a larger volume.<o:p> 

Cubes

Cubes are the main objects in online analytic processing (OLAP), a technology that provides fast access to data in a data warehouse. A cube is a set of data that is usually constructed from a subset of a data warehouse and is organized and summarized into a multidimensional structure defined by a set of dimensions and measures. Dimensions are structural attribute of a cube, which is an organized hierarchy of categories (levels). These categories typically describe Image 3

a similar set of members upon which the user wants to base an analysis. For example, a geography dimension might include levels for Country, Region, State or Province, and City. Measures are a set of values that are based on a column in the cube's table and are usually numeric. Measures are the central values that are aggregated and analyzed. 

MDX 

Since cubes are data structure, therefore there is an obvious need of a standard way to access the data resides in it. Just like the same way as SQL do. SQL is design for two-dimensional structure (tables), and because of such SQL’s limitation it cannot be used in Accessing cubes data. To overcome such limitation a new language has been developed which is similar in many ways to the SQL syntax called MDX stands for Multidimensional expression. But is not an extension of the SQL language.

<o:p>

Pilot Project: WCL Mapping to OLAP MDX expressions. 

We have used OLAP in an interactive system; objective of this project is to provide performance enhancement and analytical processing capabilities in the ABC Online System. ABC system is an online query system that asks questions in certain English language and gives results instantly.<o:p> 

We initiated the project with following objectives:<o:p> 

§  Design should be simple, intuitive<o:p> 

§  Compatibility with WCL queries<o:p> 

§  Easily extendable<o:p> 

§  Space for dynamic updates in the core logic<o:p> 

We also implemented WCL, English like querying language explicitly designed for this project. The users to interact with the system will finally use this language. The sample grammar is<o:p> 

<Captain> <o:p>

OR <Team> Batting<o:p> 

OR <average><o:p>

OR …<o:p> 

<o:p> 

AND Test/ODI<o:p>

AND DEBUT<o:p>

AND …<o:p> 

Image 4

The goal has successfully achieved. We have implemented a unique strategy that is simpler, both in terms of design and implementation

Mapping Details 

It has been noticed by deep study that WCL grammar has a specific pattern in their design, and therefore can be utilize to generate such SQL templates that accommodate almost every part of WCL. 

Image 5

Using above table we have successfully developed a generic SQL+MDX template that will be filled dynamically during MDX generation process.

 

Image 6

<o:p>This parsing engine parse tokens and gather information, writing it to MDX array. After the completion of this parsing process, we finally have a complete MDX that when executed returns information set required by the user. Since the logic has been broken into pieces and stored in the database, we also have the opportunity of even manipulating this logic at execution time. 

Conclusion 

There is still so much to explore in analytical processing. Technology is far more complex and need serious research work for maximum in the field of science and technology. We have shown just a single implementation that OLAP can also be use on non-transactional systems.<o:p> 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Product Manager Avanza Solutions
Pakistan Pakistan
Asif embarked on his programming journey back in 1991 when he first encountered 80286 8-16 MHz systems. His programming repertoire began with dBase III+, FoxPro, C, and assembly language, where he honed exceptional skills that have been a cornerstone of his development career.

Over the years, Asif's programming saga has seen him delve into a wide array of technologies and languages, ranging from C++, VC++, Java, Delphi, RPG400, SQL Server, and Oracle, to name just a few. His insatiable curiosity continues to lead him towards newer horizons, such as DOT Net Technologies, SOA architectures, BI, DSL, and beyond. These learning experiences are underscored by a robust theoretical foundation, with a penchant for research.

Beyond the realm of technology, Asif's interests include delving into the pages of fiction, exploring biotechnology, and gazing at the wonders of Astronomy. He finds joy in watching movies, and during his free time, he delights in quality moments spent with his children.

Comments and Discussions

 
GeneralMy vote of 5 Pin
_Vitor Garcia_25-Jun-13 6:12
_Vitor Garcia_25-Jun-13 6:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.