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
>>Chapter 8 : Different Ways of Data Warehousing
>>Chapter 9 : Knowledge Management
>>Chapter 10 : Data Extraction
>>Chapter 11 : Business Intelligence Life Cycle
>>Chapter 12 : Business Intelligence User Model<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
- Online Analytical Processing
- Dashboard
Online Analytical Processing
The Online Analytic Processing is the ability to store and manage the data in a way, so that it can be used effectively to generate the actionable information. The OLAP is between the Data Warehouse and the End-user tools.
OLAP can make the Business Intelligence happen by enabling the following:-
§ Changing the data into multi-dimensional cubes.
§ Summarizing the pre-aggregated and the delivered data.
§ Establishing a strong query management.
§ Making multitude of calculation and modeling functions.
In data warehouse, the data can in be various formats like dimensional with high degree of normalization or highly relational like the third normal form.
OLAP gives the building blocks to allow analysis like the rich functions, multi-dimensional models and analysis types. The end user tools like the business modeling tools, data mining tools, performance reporting tools lying on top of the OLAP to the user Business Intelligence interface. The OLAP and Data Warehouse work together to provide the general data access to the end user tools. There are different ways to store the data in the OLAP and Data warehouse combination. The following explains the OLAP architecture in BI architecture:
§ Multi Dimensional Online Analytical Processing (MOLAP): Storage of OLAP data in the multi-dimensional mode. There is one array for one combination of dimension and also the associated measures. In this method there is no link between the MOLAP database and the data warehouse database for the query purpose. This means that a user cannot drill down from the MOLAP summary data to the transaction level data of data warehouse.
§ Relational Online Analytical Processing (ROLAP): The OLAP storing the data in the relational form in the dimensional model. This is the de- normalized form in the relational data structure. The ROLAP database of the OLAP server can be linked to the Data warehouse database.
§ Hybrid Online Analytical Processing (HOLAP): Storing the aggregated data in the multi-dimensional model in the OLAP database and keeping the transactional level data in the relational form in the Data Warehouse database. There is a link between the summary MOLAP database of OLAP and the relational transactional database of Data warehouse.
OLAP Defined
In contrast to the usual data warehousing systems, where the data is present in the form of the relations, the OLAP or the On Line Analytical Processing gives a better understanding into the untapped potential of data. The multidimensional view of data is supported, which gives various increases in the information content to the same amount of data. The OLAP can give fast, consistent, and efficient access to the different views of the information.
The OLAP can be stated in terms of just five keywords – Fast Analysis of Shared Multidimensional Information. Fast, so that the most complex queries which requires not more than 5 seconds can be processed. Analysis is the process of analyzing information of all the relevant kinds in order to process the complex queries and also to set up clear criteria for the results of such queries. The information that has to be used for analysis is normally obtained from a shared source, such as data warehouse. The information can be related in more than one or two dimensions. For example, a set of business data may be related to sales figures, market trends, consumer buying patterns, supplier conditions and the liquidity of the business. Presented in such a multidimensional detail, such data can be useful and important to managerial decision-making.
OLAP Techniques
The Online analytical processing or OLAP can be implemented in many different ways. But, the most common way is to stage the information obtained from various corporate databases, for example data warehouses, is staged that is stored temporarily into the OLAP multi-dimensional databases for recovery by the front-end systems. The multidimensional database can be optimized for fast recovery. There are several techniques for speeding up the data retrieval and the analysis is implemented on the procedural side of the database management.
The OLAP is mainly concerned with the multidimensionality of the data. The multidimensional models make use of the inbuilt relationships in data to fill data in multi-dimensional matrices called the Data Cubes or the Hypercubes (This is used for more than three dimensions). A standard spreadsheet, which signifies a conventional database, is a two-dimensional matrix. One example would be a spreadsheet of regional sales by product for a specific time period. Products could be shown as rows with sales revenues for each region having the columns. By adding a time dimensional such as the organization‟s fiscal quarters will produce a three-dimensional matrix, which will be represented using a cube.
In multidimensional model storage has two types of tables which are dimension tables and fact tables. A dimension table can simply store the tuples of the attributes of the dimension. A fact table is considered to have tuples, which is one per recorded fact. This fact table can contain some measured or observed variable or variables, which recognizes it with pointers to the dimension table. The fact table can contain data and the dimensions will identify each tuple in that data.
Changing from one dimensional hierarchy to another can be easily achieved in a data cube by using a technique called Pivoting1, also known as rotation. There are several other techniques which go into making a complete OLAP implementation. These differ from drilling down into finer details, from various databases, to rolling up and grouping them into larger units.
The OLAP can be implemented by using the following techniques:
§ Consolidation or Roll Up
The Consolidation involves data aggregation which can involve simple roll- ups or complex grouping regarding inter-related data. For example, the sales office can be rolled-up to district and the district again to regions.
§ Drill-down
The OLAP can go in the reverse direction and can display detailed data that consists of consolidated data. This is known as drill-down. For example, consider the sales done by individual products or by the sales- representatives that will make up a region‟s total sales can be easily accessed.
§ Slicing and dicing
The slicing and dicing relates to the ability to look at the database from different viewpoints. One slice of the sales database may show all the sales of product type within a region. Another slice may show all the sales by sales channel, present within each product type. The Slicing and dicing is regularly performed along the time axis in order to analyse the trends and the final patterns.
OLAP Applications
The OLAP is generally used in several regions of data management. Some of the applications will include: –
§ Financial Applications
º Activity-based estimation or resource allocation
º Budgeting
§ Marketing or Sales Applications
º Market Research Analysis
º Sales Estimation
º Promotions Analysis
º Customer Analysis
º Market or Customer Division
§ Business modeling
º Modeling business behavior
º Extensive or real-time decision support system for managers
All of the above applications need the ability to give managers with the information that they need to make useful decisions about an organization’s planned directions. The key sign of a successful OLAP application is its capability to provide information, as required, that is, the capability to give "just-in-time" information for useful decision-making. This needs more than a base level of detailed data.
Applying the OLAP to Data Warehousing
Data warehousing is mainly used to archive the data which is of the nature of business knowledge. The main constraint is the quick analysis of the shared data which will result in multidimensional views of the data, which in turn will result in the information acquisition. OLAP comes into play when it is necessary to analyze the data stored in a warehouse, so as to create complex results, in a time constrained environment which means just-in-time information. Most of the data warehouses support, what is known as Ad hoc querying, which means that any combination of the complicated queries can be executed against the stored data.
There are many tradeoffs which must be made while applying OLAP to warehouses because of the bulk of data that has to be analyzed. For example, an OLAP system must be able to process a complex query in the shortest possible time.
For this, it can use two approaches:
§ To have pre-calculated data.
§ To apply all calculations on the fly.
The first approach requires huge amounts of data storage area, and hence exotic hardware support, which proves to be cost-ineffective. The second approach is to apply the calculations, on the fly, which will result in slower execution of the queries. Hence, the optimal approach is to pre-calculate some of the data, and to calculate in real time other parts of the queries. The elements have to be balanced in an optimal way for a good execution of OLAP systems.
Benefits of using OLAP
OLAP responsible for several benefits for businesses:
§ OLAP helps the managers in decision making through the multidimensional data views which is capable of giving, hence increasing their productivity.
§ OLAP applications are self-sufficient due to the inbuilt flexibility given to the organized databases.
§ OLAP allows simulation of the business models and problems, through the widespread use of analysis capabilities.
§ OLAP in conjunction with the data warehousing can be used to give reduction in the application backlog, leading to faster information retrieval and reduction in the query drag.
Dashboard
The Dashboard helps the decision makers to manage the organizations operations. This helps to track the main performance indicators and also give warnings, action alerts and next steps when the areas of business fail to meet its pre-determined metrics.
The Dashboard popularity is growing continuously as the organizations look for simpler ways to get the information within the large data sets and also to analyze the information in the hope of managing performance, making improved decisions and lowering of the overall costs. Yet the development and the maintenance of these dashboards have not always been easy. Organizations can select the charts, graphs, maps and almost any visualization imaginable which can be used within their dashboards. Unfortunately, more choice does not assure more efficient use of visualizations, which may lead the companies being unable to recognize the best way to build and monitor the dashboards which they are using.
Generally, businesses require guidelines to help them recognize the important components of a dashboard and also guidelines on how to create their own dashboards to suit their specific needs, while taking into account the general rules which will help to ensure that the proper information is gets highlighted on the dashboard. The information that has to be included within a dashboard to make it more efficient includes the metrics identification and
development, the types of visualization to be considered and the general dashboard management.
Recognizing the dashboard’s purpose and general considerations
The one thing that is important to realize while building and designing a dashboard is that each organization may have its own business needs and corporate culture which means that what may be useful for one organization may not work for another. Yet, the companies can use guidelines that relate to the overall dashboard use as a start while building the initial dashboards or while looking to optimize the current performance metrics. In either of the case, recognizing the proper metrics, giving the right level of detail to the end users, and making sure that the information is timely are some of the aspects that has be considered regardless of the type of dashboard being deployed.
Breaking down the metrics
Apart from recognizing the common purpose of a dashboard, looking at the metrics is one of the first steps to building an efficient dashboard. So the questions for the organizations to be with is how to define the effective metrics and how to recognize what the metrics are important and which ones are just nice to have. Before defining what metrics to use, the definition of the metrics should be recognized. The Metrics or the Key Performance Indicators (KPIs)2 are mainly the measures of success tied to the different business drivers.
To link the goals to plans, the organizations should focus on the specific KPIs instead of measuring everything that can be thought of. Hence, the companies should look at the top 5 to 10 items that are necessary to measure on a regular basis. Whether the sales performance, the sales pipelines, the customer satisfaction, the sales by region, the employee compensation management or the employee performance goals etc having high-level metrics will give a general view of what is vital to be checked on a regular basis. The aim of any dashboard should not be to look at everything existing, but to get insight into what is happening inside the business unit so as to make better decisions and also anticipate the issues that can be acted upon faster.
Using the proper visualizations
Once the metrics has been defined, the organizations will have to consider how to see the information. In general, there is continuous debate inside the data visualization world regarding the proper types of visualizations that are most efficient. Some say that simple spark lines and line graphs give the best way to recognize how the organizations perform against the targets, while others choose more visually pleasing ways of looking at the information. Either way, the aim of visualizations is to make analyzing the information simpler, meaning that the way information is viewed will vary based on the individual preferences.
The important thing to remember is the use of particular visualization is:
§ The Gauges and the bullet charts with the targets recognized can be a good way of recognizing how well the organizations are performing against the set targets because the goals can be set and then the gauge dial can change depending on the performance or the range market can move towards the target or the threshold.
§ The Bar charts helps to recognize the overall sales based on the products or the services or to compare the individual performance which could be individual, service or product based.
§ The Stacked bar charts and the pie graphs allow the companies to compare the various measures such as the products against one another. A pie graph can show the percentages of sales in contrast with others or recognize the regional performance. Apart from these the drill- through capabilities allow the decision makers to select what information can be worth looking at more in detail. Also some of the pie graphs can automatically group the information together if it falls under a certain percentile to target on the larger numbers.
§ The Heat maps helps to recognize the common trends and product popularity along with the sales comparisons.
§ The Geographic maps can serve many purposes ranging from recognizing different gas prices in various regions, to comparing or contrasting election results, and looking at the discrepancies in the sales or the student scoring, etc.
§ The Embedded reports are used in some of the dashboards to join the reporting and the dashboards into a single interface.
§ The Charts and the graphs give the organizations with a general view of the sales and the time-series type of information.
One or more of these visualizations can be displayed within a dashboard. The more simple the information is displayed, the better as it is vital to balance the concept of analyzing the information and the overall data visibility by giving immediate access to the most important and the immediate metrics required for regular monitoring.
Many of the solutions providers give dashboard templates which can be used to drag and drop visualizations into general dashboards with two or four blocks, etc. This idea is the basis that organizations should use while designing their dashboards. Even though the business users will be comparing multiple types of information in to one centralized area, in reality, the less and the more direct information viewed can be drilled through to gain deeper insights Hence, it may be good to limit the dashboard to six key areas that will encompass the 10 metrics, leaving room to link to other visualizations, reports or data sources based on the in-depth analysis that may be required.
Dashboard management and takeaways
In general, the focus on the front-end interactions, the dashboards have become the first line access for deeper analytics. The Organizations should recognize the types of metrics that are necessary, while limiting their scope to the top 10. Visualizations can be then combined or separated depending on how the metrics are interrelated.
Although the dashboards expand the general use of business intelligence, the businesses cannot neglect the back-end requirements as well.
Here are some of the general aspects that the organizations should think about while considering the dashboard infrastructure:
§ The Data architecture and what the company already have in-house.
§ The Data latency and how often the data updates are required. Although this may not influence the actual visualization components, it may affect the type of deployment and also how often the data is updated.
§ The Current BI, reporting and analytics which may be redundant or complementary to the dashboard proposal.
§ The type of users who can be interacting with the information, as the data visualizations and the overall design and functionality will vary depending on the skill set of end users.
§ The Future goals and the expected uses of the dashboard.
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 |
(B)usiness (I)ntelligence Mentalist 