Multidimensional Data Analysis with OLAP

Abstract

Microsoft Business Intelligence offerings with a focus on OLAP are investigated. A case study for a commodity retails company illustrates the key findings.

Keywords

• multidimensional analysis
• OLAP
• Business Intelligence
• Microsoft SQL Server
• Microsoft Analysis Services
• Microsoft Business Intelligence Development Studio

1 Introduction

One of the key factors for success on the market for companies is the application of information technologies in data analysis as mentioned in [ 1 ]. This kind of analysis usually uses several parameters and therefore information technologies have to support a multidimensional analysis of data.

The data available from the operational information systems are not directly applicable for analysis because they are in different format, structure, distribution etc.

Relational model of data is the most suitable as a tool for development of operational information systems. That is why the last ones are usually based on Relational Data Base Systems (RDBMS). Codd, as mentioned in [ 2 ], has not developed relational model of data for data analysis. That kind of analysis has to be done outside the RDBMS in specialized analytical tools. These tools store aggregated operational data in n-dimensional cubes of specialized physical structures that are very different from these supported in RDBMS.

Some of the commercial RDBMS support dynamic extraction of analytical data directly from the relational database. Befits of this approach is that there is no need to support specialized n-cube physical data structures but the result is slower performance in comparison to specialized tools.

As mentioned in [ 3 ], “Business intelligence (BI) is a technology-driven process for analyzing data and presenting actionable information to help executives, managers and other corporate end users make informed business decisions.” Many technologies support this process. Among them are OLAP and Data Mining.

The definition in [ 4 ], “OLAP (Online Analytical Processing) is the technology behind many Business Intelligence (BI) applications. OLAP is a powerful technology for data discovery, including capabilities for limitless report viewing, complex analytical calculations, and predictive “what if” scenario (budget, forecast) planning.” connects BI with OLAP.

Good definition of Data mining is given in [ 5 ]: “Data mining is the process of analyzing hidden patterns of data according to different perspectives for categorization into useful information, which is collected and assembled in common areas, such as data warehouses, for efficient analysis, data mining algorithms, facilitating business decision making and other information requirements to ultimately cut costs and increase revenue.

Data mining is also known as data discovery and knowledge discovery.” For more details on introduced terms visit above mentioned referenced sources.

3 Tools

Analysis of data for small and medium companies is the business use case. More precisely, retail store for electronic technics.

The tool suite for this business use case are Microsoft SQL Server and Microsoft Visual Studio. It is a cheap solution of mature tools. Exact versions of these tools are Microsoft SQL Server 2008 and Microsoft Visual Studio 2010.

Microsoft SQL Server 2008 is the Microsoft’s RDBMS. It is suitable for development of operational information systems for small and medium companies.

Microsoft SQL Server Data Tools Business Intelligence (SSDT-BI) for Visual Studio is a tool for development of data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services. It is a part of Microsoft Visual Studio 2010. SSDT-BI extracts, suitable for data analysis, n-dimensional cubes from the relational database.

4 The cube and its usage

The business case cube is based on every day sales by items, quantities, types, stores, supplies etc. Fig 1. illustrates part of the n-cube metadata and a data slice form it embedded in Microsoft Excel spreadsheet.

The analytical application developed on the cube allows managers to take informed decisions. For example, Fig. 2 shows that the best sales by air condition’s brands in 2016 are Midea and Elenberg in the Aktobe store chain.

View that is more detailed is Fig. 3. In the last months of 2016, the trend shows that more popular in the Elenberg brand. Therefore, the store management have to reload the chain with this more attractive brand.

The analytical application is simple and user friendly to the management, which has accepted it and used in their work. 5

Conclusion

The proposed tool suit is very simple and effectively generates business intelligence applications for small and medium companies.

References

1. Laudon , K.C. , Laudon , J.P. , Brabston , M.E. : Management information systems: managing the digital firm . Toronto, Prentice Hall ( 2005 ).

2. Codd E.F. , Codd S.B. , Salley C.T. : Providing OLAP to User-Analysts: An IT Mandate . E. F. Codd & Associates, 1993 .

3. business intelligence (BI), http://searchbusinessanalytics.techtarget.com/definition/businessintelligence-BI

4. Easy OLAP Definition, http://www.olap.com

5. Data Mining , http://www.techopedia.com/definition/1181/data-mining