- Introduction
One of the most important evolvement that IT projects face with, is the great increase of the data volume. According to researches, in every 2-5 years, the volume of data in the world increases twice [1]. Regardless of the size, business activities of many companies involve huge amount of information that comes from different sources including internal and external. In this case, it is not always possible to analyze and generate reports from this raw information. On the other hand, companies necessarily require proper decisions to be made based on the significant information more quickly and accurately in the growing competitive environment. All these reasons direct us to find new solutions or metodologies rather than the traditional methods to obtain consistent data and create meaningful reports from the raw information. BI is an effective response to this need and provides many capabilities in this aspect.
One of the benefits that BI provides is to create meaningful information from the integrated data. In general, data can be defined as everything that we can digitized. Metadata is the definition of the data. When we join data and metadata, information occurs. BI makes operations and decisions on that integrated information. In general, BI can be defined as a system comprised of all technologies, metodological approaches and technics to create meaningful information from the integrated data to enable effective decision making and management support, with the purpose of increasing organizational performance [2]. Additionaly, it gives feedbacks to review the given decisions.
Recently, there have been many studies that define the technology, components, usage and benefits of BI in the practitioner and academic literature[3-5]. The main objective of this paper is to demonstrate the importance of BI in the practical domain and present a generic implementation framework for BI solutions. The main goals of the BI implementations are to present a new approach to data management, presentation and reporting.
- Business Intelligence Projects
1 Roles in BI Projects
In order to develop a BI solution for companies, we need to have the right people involved with their multidisciplinary nature. Business professionals, information analysts, usability experts, senior/project managers are all involved in the BI stage in contrast with conventional IT project teams. Most BI projects include the following roles:
- Project manager, consultant, business analist, BI architect,
- Operators who input information and feed the database,
- Technical people:ETL developer, database administrator, report developer, tester, usability expert, etc.
- Other companies,
- Customer, sponsor, user
- BI Components
Most BI solutions include the basic five components as business data sources, extract, transform and load (ETL), data warehouse, analytical data models, reporting and analysis [7-8]. Figure 1 summarizes these components of BI solutions which can be applied to all platforms and denotes the relationships between them. BI solutions start with a data source, process data and create data warehouses or data models as interlayer to obtain consistent data. Finally, they provide meaningful reports and analysis results to the end users.
2.2.1 Business data source
Business data source is the data that provides the basis for BI solutions. Most companies process business operations with the help of some software applications such as enterprise resource planning (ERP), supply chain management (SCM), customer relationship management (CRM) for sales-oriented businesses or point-of-sale (POS) system for retail businesses [8]. Almost all of these systems generate some form of business data that is used as a starting point for any BI solutions. Application database is mostly designed as a relational database by using a relational database management system such as Sql Server, Oracle or Microsoft Access. Business data source can be external like a cloud-based database or a web service or it can be an internal operational database.
2.2.2 Extract, transform and load (ETL)
ETL refers to a process of three separate functions (extract, transform, load) combined into a single job. ETL systems are commonly used to transfer data from an operational database to a data warehouse. For this purpose, ETL extracts data from the source system, transforms the content of the data to the data warehouse schema and loads it into the data warehouse.
ETL system keeps the data warehouse system to be up to date and ensures that the BI solution continues as a whole by providing the data integrity. ETL system is considered as the heart of the BI solutions. Processing an effective ETL system means to design and build more effective BI solutions.
2.2.3 Data warehouse
Data warehouse is assumed to be the brain of the BI solutions as it is the central data repository which all reporting and analysis are based on. Due to the fact that there will be ongoing operations, operational databases are not convenient to produce reports or make analysis. It may take a long time to operate even a simple select command on the operational databases. We mainly focus on the select command because reports are mostly generated with the complex set of select commands. To shorten the reporting time, it is not enough to take a copy of the operational database and use it offline. We need a structure which will decrease the load of operational databases. These needs are recompensed by the data warehouses. Business data is consolidated into a data warehouse which makes the data easier to be analyzed or to be used to generate reports.
Data warehouse is also assumed to be the memory of the BI solutions as it keeps information historically. In operational databases, it is only possible to reach the last version of the data after it is updated. However, in a data warehouse, it is possible to observe all versions of data before and after the updates. For instance, assume that in 2011 a worker had two children and in 2014 he had one more child. In an operational database, the number of child column is updated and we can see as he has three children. In a data warehouse, this data is recorded as in 2011 he had two children and in 2014 he had three children. As a result, this will also cause to reserve a big space to store such information while designing the data warehouses.
Data cleansing must also be done with the help of ETL operations on the operational databases. For instance, the same expression can be written in different formats. When the data is transformed into a form of the data warehouse, these conflicts should be handled and all types should refer to the original one.
Tables in a data warehouse should be denormalized which may decrease the number of tables and increase the volume of the saved information. The column’s names should be given meaningful to be directly used in the generated reports. Therefore, we don’t have to rename the columns or change their alias.
2.2.4 Analytical data models
Companies may need to perform all analysis and reporting directly from the data warehouse. It is very common for analytical data models to use the data warehouse as a source. The purpose of analytical data model is to provide meaningful information that the business requested, for this it adds value to the data gathered from the data warehouses. Analytical data models offer additional capabilities like key performance indicators (KPI), calculated measures and user-defined hierarchies that are not provided by the data warehouse. Typically, analytical data models create multidimensional structures called as cubes. A cube is a specific organization of measures and enables users to aggregate measures [6].
2.2.5 Reporting
One of the fundamental benefits of the BI solutions is to help the companies to track and improve their business performance with reporting and analysis. Reporting is the summary of the business performance and activities that is used to determine which actions should be taken for next years.
Most of the BI solutions include reporting elements which provide standard business reports. Reports can be generated directly from a data warehouse or an analytical data model. It depends on the needs or the specific business requirements.
- Commercial BI solutions and products
There are many different platforms to create the BI solutions. Almost all of the solutions and products include the basic five components of the BI that are mentioned above.
Commercial BI Solutions can be classified into two groups:
— Companies focus on BI Solutions: Information Builders, QlikTech, Actuate
— Companies with much wider work areas: Microsoft, Oracle, IBM, SAP
The following is a list of BI solutions provided by different vendors [3]:
| Vendor | Product |
| IBM | IBM Cognos Express |
| SAP | Business Objects Edge |
| SAS | SAS BI/Enterprise for mid-sized businesses |
| Microsoft | Performance Point Server/Excel/Sql Server |
| Microsoft Strategy | Microsoft Strategy Reporting Suite |
| Oracle | Oracle Business Intelligence Suite Standart Edition |
| QlikView | QlikView |
| Information Builders | WebFocus |
| Actuate | Actuate Business Intelligence Suite |
| Vai | S2K Analytics |
3.1 Microsoft BI Platform
After the discussion of the general components of the BI solutions, we focus on a specific platform to show some implementation scenario examples. We will be using and giving examples on Microsoft Sql Server and its services related to BI to create the solutions.
The followings are some of the technologies of Microsoft BI Platform:
— The Sql Server database engine, used for application databases and data warehouses,
— ETL capabilities are provided by Micrsoft Sql Server Integration Services(SSIS),
— Data cleansing is provided by Data Quality Services(DQS)
— Master data management(DMS) is provided by Master Data Services(MDS)
— Storage and query processing engine is provided by Sql Server Analysis Services(SSAS),
— Publishing and delivering reports are provided by Sql Server Reporting Services(SSRS).
Microsoft Sql Server supports two types of analytical models:
Multidimensional Data Models: This type of analytical model is supported by all versions of Sql Server. One can create the analysis service database that contains one or more cubes by using a multidimensional data model.
Tabular Data Models: All versions after Sql Server 2008 r2 support the tabular data model. Tabular data model is easier than but not as qualified as the multidimensional model. You are not supposed to have as much online analytical processing knowledge as in multidimensional models.