Abstract

OLAP (Online Analytical Processing) system appears as a revolutionary technology that provides adequate analytic solutions for decision support. Using OLAP, analysts and policymakers are able to process and analyze data in an interactive, fast, and effective way according to several axes. This provides a clear vision of their business at any time and in real time. However, these systems suffer from certain limitations related to the consideration of both the multicriteria and fuzzy aspects of multidimensional data when making decisions. To overcome these limitations, we propose an integrated decision-making prototype based on OLAP system and multicriteria analysis (MCA) to generate a hybrid analysis process dealing with complex multicriteria decision-making situations. This proposed solution, based on our previous contributions, allows the analyst to extract data from OLAP data cube model and analyze them using OLAP operators and MCA methods.

Keywords

Background

In a decision-making context, analysis is an intellectual process that allows generating knowledge from hypotheses and data (Codd et al. 1993). This can be formalized by explaining a phenomenon and proposing solutions or recommendations for decision making. To make that analysis applicable, OLAP system as a BI (business intelligence) technology, appears among the most adequate multidimensional analysis tools most used by decision makers and analysts who need to transform data into actionable information, which has the effect of facilitating the management of the performance of organizations and having a clear vision of their activities at all times and in real time.

Moreover, the economic and business intelligence (BI) tools, especially OLAP systems, are considered among the best technologies most eminent and most powerful in the environment of decision support systems. In fact, OLAP systems are at the heart of many economic analysis applications, and appear as complete systems providing useful and necessary services for efficient, rational, and analytic processing of data. The functionalities of these systems, based on a multidimensional database approach (Kimball 1996), are characterized by the ability to support efficient and flexible exploration of multidimensional cubes in data warehouses (Aligon et al. 2015).

Several studies have been conducted around the theme of OLAP technology reflecting its degree of importance and effectiveness to be implemented in multiple business intelligence areas. In fact, in a decision-making context, OLAP system is a relatively well-mastered technology when it comes to simple data, which explains its ability to be easily integrated with other environments such as cloud computing (Dehne et al. 2014; Al-Aqrabi et al. 2015) and Big Data (Cuzzocrea et al. 2013; Song et al. 2015; Kang et al. 2015). Nevertheless, the complex consequences of certain decision-making situations necessitate taking into account the multicriteria and conflicting aspects of data, as well as the consideration of several types of data (quantitative and qualitative) in order to represent adequately all the necessary information for making decisions. However, OLAP tools that exist in decision-making area still suffer from limitations related to the lack of technical means to consider the multicriteria and imprecise nature of decision data in the analysis process.

To improve the analytic capabilities of OLAP systems, several researchers have proposed incorporating decision support techniques such as integration of information networks (Loudcher et al. 2015); integration of association rules (Lee et al. 2013); new methods for modeling OLAP cubes (Kaya and Alhajj 2014), etc. However, these contributions do not take into account the qualitative preferences of decision makers and the multicriteria aspects of certain decision-making situations. To support these limitations, multicriteria analysis and fuzzy analysis are the most appropriate tools to provide complementary analysis to the OLAP process. On the one hand, multicriteria analysis methods are used to deal with complex decision-making situations involving multiple, often conflicting, criteria. These methods are characterized by their way of modeling the preferences of decision makers and the possibility of weighting the criteria. On the other hand, fuzzy analysis techniques allow taking into account fuzzy and imprecise decision data.

It is therefore necessary to explore the advantages of multicriteria analysis and fuzzy analysis techniques in order to meet the new requirements of decision makers and analysts who use decision-making systems by proposing the most appropriate solutions. These should be a natural extension of the analytic capabilities of OLAP systems.

The aim of this contribution lies in the context of critical decision-making situations where strategic decision support is generally characterized by the presence of several difficulties that are mainly related to the multicriteria and complexity aspects of these decision-making situations, the amount of data to be considered, and the understanding of the evolution of the values of evaluation criteria over time. The decision support requires, then, greater synergy between several analytic and technical decision-making tools, in order to achieve satisfactory results to the needs of decision makers.

To this end, we propose a software implementation based on OLAP systems and multicriteria analysis to concretely test the intake of the proposed solutions. This implementation is composed of two interfaces. The first one, entitled AMCD interface, is a web application dedicated to collective decision-making that allows the computation of the importance weight of the evaluation criteria, based on the algorithm of the geometric mean procedure of the FAHP method. This assessment is carried out via a group of decision makers using a linguistic scale for evaluation. The second interface, called OLAP_MML, is a Java application dedicated to online analysis of alternatives based on the evaluation criteria derived from the data cube model. The weights of the criteria calculated via the AMCD interface and the alternatives analyzed via the OLAP_MML application are taken as input variables in the Visual Promethee program, integrating the PROMETHEE multicriteria analysis method. The latter program helps to simplify the final assessment of alternatives by allowing decision makers to intervene during the analysis process when making decisions. According to the literature review, the study of software integration modes, such as the MCA and OLAP tools, shows that there are three modes: full coupling, tight coupling, and loose coupling. Since our prototype is based on interactions between the different entities that compose them, we opted for loose coupling (incomplete integration). This mode requires that the software elements of our prototype remain completely independent, and the communication between them is carried out via an intermediate system.

This paper is organized as follows. The “Research methodology” section discusses our research methodology and develops our proposed prototype. In the “Application” section, we describe the case study devoted to solve the selection problem of the most appropriate itineraries for the transport of chemicals in the industrial region of Casablanca. Finally, the “Conclusion” section contains some concluding remarks.

Research methodology

Improve the OLAP analysis process

To search for a suitable model combining all the advantages of OLAP analysis, multicriteria analysis, and fuzzy analysis, it is necessary to establish a representative process when structuring and solving some complex and critical decision-making support problems. Indeed, the main contribution of this paper is to propose a decision-making solution combining multicriteria analysis, fuzzy analysis, and OLAP systems, in order to generate an advanced analysis process adapted to the needs of decision makers. The development of this solution is performed according to two main axes. The first axis aims to provide an analytic context, which is different from the classical analysis cycle presented in Fig. 1, which can be analyzed using OLAP analysis operators and multicriteria analysis methods.

This analysis context is based on the design of a new multidimensional model of the data cube called multicriteria/logistic model (MLM), to understand and simplify the combination of the concepts of multicriteria analysis and OLAP analysis, as shown in Fig. 2.

Fig. 2

Evolution of the analysis cycle

The second axis aims to realize the technical integration of multicriteria analysis, fuzzy analysis, and OLAP analysis in the data processing and analysis process. This integration is achieved by incorporating directly multicriteria analysis techniques, when exploring data, using MDX queries to interact in a multidimensional manner with the new proposed data cube model, and indirectly via an external process which is complementary to that of OLAP. Moreover, in order to avoid ambiguity and uncertainty of data, we propose to integrate fuzzy analysis in the analysis process. This improves and expands the technical and analytic capabilities of the decision support systems while creating an advanced analysis process that is adapted to the strategic decision-making needs.

Proposed data model

The proposed data model, which is based on the multidimensional modeling of the data warehouse, is a star dimensional structure that provides a fact table representing the new OLAP cube. This fact table contains observable, measurable, and numerical data (Kimball and Ross 2002), derived from a structured business datamart. It is surrounded by a unique circle of dimensions constituting the multidimensional and multicriteria characteristics specific to the decision makers’ needs and to the mode of extraction and analysis of the data during the decision-making process as presented in Fig. 3.

Fig. 3

Multidimensional modeling of the new OLAP cube model

The abstract representation of the new proposed data cube model is shown in Fig. 4 with respect to the multidimensional modeling of the data already presented in Fig. 3.

Fig. 4

OLAP cube abstraction

Proposed prototype: general architecture

The general architecture of the software prototype, allowing us to take into consideration the new data cube model presented previously (Figs. 3, 4), consists of two evaluation processes: The process of criteria evaluation (AMCD interface) or that of alternatives evaluation (OLAP_MML interface and Visual Promethee interface) as illustrated in Fig. 5. The proposed prototype represents a simplified implementation of our decision-making approach proposed in our previous contributions (Boutkhoum et al. 2015a, b) combining multicriteria analysis, fuzzy analysis, and OLAP systems. It is divided into two layers: first, the Data warehouse containing the datamart that feeds the proposed data cube model; and second the interrogation and presentation layer that consists of the Mondrian OLAP server, allowing the exploration and interrogation of cube data via MDX queries. These queries are sent from the user interface to view and visualize the different analysis results.

Fig. 5

General architecture of the proposed software solution

At the operational level, the integration mode of these analysis processes is an incomplete mode (loose coupling), where the AMCD, OLAP_MML, and Visual PROMETHEE interfaces remain completely independent. The aim of the AMCD interface is to ensure collective decision making by computing the weights of the criteria selected from the MLM data cube (Figs. 4, 5) based on the geometric mean method algorithm (Buckley 1985; Boutkhoum et al. 2016a). This evaluation is carried out via a group of three decision makers using a linguistic scale for evaluation. For the OLAP_MML interface, for which the objective is to be connected to the Mondrian OLAP server, it is used to identify potential actions from our MLM and to analyze them for a specific period of time. This analysis interface allows visualizing the result of the score of each action in the form of a final ranking. Finally, the criteria weights calculated via the AMCD interface and the alternatives analyzed via the OLAP_MML application are taken as input variables at the Visual Promethee program level, integrating the PROMETHEE multicriteria method. This latter program helps to simplify the final assessment of alternatives by allowing decision makers to intervene during the decision-making process (Boutkhoum et al. 2016b).

Proposed processing and analysis algorithms

To illustrate the overall functioning of our proposed prototype, we adopt the following algorithms detailed in Table 1.

Table 1

The proposed algorithms for the construction of the prototype

Algorithms

Input

Treatment

Output

Observation

Algo 1: phase of computing criteria weights

All criteria selected for the assessment

Objective: calculate the significance weights of the selected criteria

Application

Description of the case study

Issues related to the Green Supply Chain Management at the level of mining activities have serious social–environmental consequences and underlying economic implications (Hilson and Nayee 2002; Poulton et al. 2013). In this illustrative example linked to the field of green logistics, potential itineraries for the transport of chemicals in Casablanca industrial region (see Fig. 6) are assessed and classified for a sustainability objective. It examines several itineraries and controls their evolution during a period of time beginning from 2000 to 2013, according to several evaluation criteria as shown in Fig. 7, describing the hierarchical structure of this problem.

Fig. 6

Itineraries of the industrial region of Casablanca

Fig. 7

The hierarchical structure of the problem

However, achieving this objective is often complicated and requires consideration of tradeoffs between social–political, environmental and economic impacts. Moreover, the different types of data included in the decision-making process can be of a certain, uncertain, quantitative or qualitative nature, leading organizations to make decisions in important conditions of uncertainty, causing unexpected results. It is therefore necessary to look for decision support tools with high analytic capacity to meet certain characteristics and specificities of this type of problems, which we quote as follows: conflict of objectives; comparison of several possible actions according to several criteria and objectives; contradiction of criteria; qualitative and quantitative criteria; scalable and temporal aspects of certain criteria; ambiguity of preferences; and subjectivity of judgments of decision makers. All these situations merely highlight the complex and multicriteria aspects of this type of problems.

Analysis and results

Criteria evaluation phase: AMCD interface

The criteria presented in Fig. 7 are segmented according to an MDX query executed at the OLAP server, allowing to view in descending order all the evaluation criteria related to the choice of itineraries. This segmentation aims to simplify the selection of these criteria and consider only those with a high final score. In this context, the evaluation of these criteria via the AMCD interface is carried out according to six complementary steps as illustrated in Fig. 8. The first step consists in specifying the number of criteria to be considered in the evaluation. This is used by the AMCD interface to generate the comparison matrix corresponding to the number of criteria already specified.

Fig. 8

Choice of the number of evaluation criteria (step 1)

At the beginning of the evaluation, according to the number of criteria set by decision makers using the AMCD interface (step 1 presented in Fig. 8), the criteria are numbered as follows: C1, C2, C3… and are directly modified by the decision makers (DM1, DM2, DM3), as illustrated in Fig. 9.

Fig. 9

Appreciations of the criteria by the decision-making group

The role of decision makers is to complete the criteria evaluation matrix by their assessments for each criterion in relation to the others (Fig. 9). These qualitative assessments are based on linguistic variables (Table 2) representing the relative importance of paired elements, which are then transformed to fuzzy triangular numbers.

The reason for using these fuzzy numbers is that they are intuitively easy to use for capturing the vagueness and ambiguity of the language assessments provided by the decision group.

After finalizing the evaluation matrix by the linguistic appreciations of the decision group, we transform this qualitative data into numerical ones via fuzzy triangular numbers, in order to aggregate them as shown in Fig. 10.

Fig. 10

Transformation of linguistic data and aggregation of triangular numbers

The fourth step of the AMCD interface, as mentioned in Fig. 11, calculates the geometric mean based on the aggregation of the fuzzy triangular numbers already calculated in step 3 of Fig. 10.

Fig. 11

Calculation of the geometric mean

Then, based on the geometric mean already obtained, we calculate the fuzzy weights of the criteria which are in the form of a triangular fuzzy number using Eqs. (6–7) of Appendix A as illustrated in Fig. 12.

Fig. 12

Fuzzy weight calculation

The final step of MCA process is to make the defuzzification of the fuzzy weight obtained in Fig. 12, using the gravity method applied via Eq. (8) of Appendix A. The final standardized weight resulting from this defuzzification is then obtained via Eq. (9) as shown in Fig. 13.

Fig. 13

Calculation of the final standardized weight

Once the final standardized weight calculation (Fig. 13) of the main criteria has been completed and validated, the committee begins to calculate the subcriteria weights using the same interface and perform the final backup of all these weights in a CSV file. These weights are considered as inputs in upcoming alternatives evaluations (stage 2).

Identification and evaluation phase of alternatives (stage 1)

The proposed modeling to deal with the decision problem is presented in Appendix C using a multidimensional star schema. The implementation of this modeling integrates the design of an XML description file that describes the measurement axes (analysis axis) and the dimensions to consider before querying data through a client tool.

In this context, the second decision-making process that comes after completing the phase concerning the computation of the criteria weights is the evaluation of alternatives. Thus, the input data to be considered in step 1 of the alternative evaluation process are the selected criteria (without incorporating the weight of these criteria during this step), and the alternatives (itineraries) to be evaluated.

The first operation to be performed when analyzing the data in the alternatives evaluation phase is the exploration of the data cube through MDX queries, based on OLAP_MML interface editor provided in Fig. 14.

Fig. 14

MDX query execution editor

The query execution editor allows users to create and execute instructions and scripts written in MDX language.

During the evaluation, the OLAP_MML interface offers the possibility to use the file containing the weights of the criteria calculated via the AMCD interface (see Fig. 15), when integrating the utility function (weighted sum) into the OLAP analysis process, as presented in the overall approach of our previous contributions (Boutkhoum et al. 2015a, b).

Fig. 15

Possibility of loading the CSV file containing the weights of the criteria

Nevertheless, our objective in this paper is to indirectly combine OLAP analysis with PROMETHEE as an outranking multicriteria method, which will be responsible for the consideration of the criteria weight when evaluating alternatives derived as output variables of the OLAP process. The reason to include the PROMETHEE method in the analysis process is to enable decision makers to better manage the limitations of the weighted sum method, primarily related to the problem of compensatory aggregation of actions.

Consequently, we execute MDX queries via the Mondrian OLAP server (Pentaho 2016) in order to illustrate the final results of the evaluation of all itineraries selected from the data cube as presented in Fig. 16, exploring the decision elements (criteria and actions) via the Jpivot interface of Mondrian OLAP server (the Mondrian/JPivot couple is available in the following suites: Pentaho Community Edition, JasperSoft, and SpagoBI).

Figure 17 shows the result of using MDX queries through the OLAP_MML application in order to provide decision makers with a list of alternatives ranked by importance. These alternatives result from the aggregation of the values of the criteria versus the alternatives during the period 2000–2013.

Fig. 17

MDX query for the analysis of potential itineraries

Based on the evaluation and analysis results presented in Fig. 17, ‘itinerary-Ref013’ is the most appropriate one followed by ‘itinerary-Ref009’ as the second choice, then ‘itinerary-Ref007,’ ‘itinerary-Ref011,’ …, until the last itinerary. The objective is to make a primary segmentation of the potential itineraries provided by OLAP analysis, and to consider them as input variables at the level of multicriteria analysis process proposed in the alternatives assessment phase (step 2).

Final evaluation phase of alternatives (stage 2)

The aim of this part is to analyze and evaluate a list of alternatives consisting of the first four itineraries resulting from the ranking provided by OLAP analysis process, based on the principle of the PROMETHEE method as a multicriteria decision-making aid method. This evaluation aims to integrate the importance weights assigned to the criteria in the MCA process in order to assist decision makers in choosing the best possible itinerary for the transport of chemicals in the industrial region of Casablanca.

To achieve this goal, we have used the Visual Promethee program (Promethee 2016) which is an easy and practical multicriteria analysis program. It allows evaluating and ranking the alternatives, using the methodological approach of the PROMETHEE method to which the algorithm is presented in the Appendix B.

The decision makers’ preferences for the evaluation of alternatives with respect to all the specified criteria will be made using linguistic scales for evaluation (Fig. 18 and Table 3).

In the same context, the V-shape preference function (see Fig. 19 and Appendix B—step 2) was selected by setting the value of the “P” parameter of this function to 2 for all selected criteria as shown in Table 4 and Fig. 20.

The data in Table 4 are transformed into the Visual Promethee program to perform the necessary processing for the evaluation of the alternatives as shown in Fig. 20

Fig. 20

Alternative evaluation matrix using Visual Promethee features

The geometrical analysis for interactive aid (GAIA) integrated in Visual Promethee program is used as a visualization method complementing the PROMETHEE ranking methodology, which will help to display graphically the relative position of itineraries in terms of contributions to the selected criteria (Fig. 21).

Fig. 21

GAIA plan generated by Visual PROMETHEE

During this analysis, the criteria are represented by vectors, and the alternatives, by points as shown in Fig. 21. Additionally, the conflicting criteria appear clearly in the GAIA plane visualization. Criteria vectors that express similar preferences are oriented in the same direction, while conflicting criteria are pointing in opposite directions.

To sum up, the positive flow, negative flow and net outranking flow allowing to provide the final ranking of itineraries are obtained as shown in Fig. 22. The results obtained at this level may be sufficient, taking into consideration the objective of the decision.

Fig. 22

Final result of net outranking flow

As shown in Fig. 22, the final evaluation of the most appropriate itineraries is provided. Indeed, each itinerary has its relative score calculated on the basis of the contribution of the weights of the selected criteria. Therefore, the most suitable itinerary is the one with the highest net outranking flow according to the final ranking shown in Fig. 22, which shows that the preferred itinerary is “itinerary-Ref007” with a net flow of 0.0948, followed by “itinerary-Ref013” (0.0889) and “itinerary-Ref011” (−0.0424) till the last preferred itinerary “itinerary-Ref009” (−0.1412).

Conclusion

In this contribution, we have proposed a guided and simplified implementation of our decision-making approach proposed in our previous contributions (Boutkhoum et al. 2015a, b) combining multicriteria analysis, fuzzy analysis, and OLAP systems. The proposed implementation allows the application of the notions of multicriteria analysis and that of OLAP analysis, taking into account the subjective and objective assessments of the decision makers during the evaluation process. The obtained results allow decision makers to have a comprehensive and detailed view concerning the BI solutions to be adopted in the treatment of certain complex situations that evolve over time. The evaluation procedure begins with the specification of the criteria (number of criteria) to which the importance weights must be calculated using the AMCD web interface. This latter ensures a collective decision making during the process of evaluating the selected and segmented criteria through MDX queries executed at the OLAP level. Then, the OLAP_MML interface is used to evaluate the alternatives for a period of time starting from 2000 to 2013, by taking advantage of the analytic flexibility that the OLAP server can provide. The evaluation conducted by OLAP is a segmentation of potential alternatives based on the values of the selected evaluation criteria. The first four alternatives resulting from the ranking provided by OLAP are selected to be evaluated by PROMETHEE method, taking into account the importance weights of the criteria already calculated. The objective is to enable decision makers to intervene during the analysis process by proposing linguistic variables to simplify the final assessment of alternatives when making decisions.

Declarations

Authors’ contributions

OB and MH contributed equally to this work. Both authors read and approved the final manuscript.

Acknowledgements

The authors wish to thank Mr. R. Boulguid for pointing out many English corrections that lead to the improvement of the paper.

Competing interests

The authors declare that they have no competing interests.

Funding

There was no funding for this study.

Publisher’s Note

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Open AccessThis article is distributed under the terms of the Creative Commons Attribution 4.0 International License (http://creativecommons.org/licenses/by/4.0/), which permits unrestricted use, distribution, and reproduction in any medium, provided you give appropriate credit to the original author(s) and the source, provide a link to the Creative Commons license, and indicate if changes were made.

Appendix A

Step 1:

The problem must be decomposed into a hierarchy of interrelated elements (factors and subfactors). At the top of the hierarchy we find the goal, the elements contributing to achieve it are in the lower levels.

Step 2:

The comparison matrix D is built by conducting pairwise comparisons of the elements of each hierarchical level with respect to an element of the upper hierarchical level.

where n = criteria number to be evaluated, xij = importance of ith criteria according to jth criteria.

Step 3:

The triangular fuzzy numbers (TFNs) must be established using the geometric average to represent the consensus of most decision group members. They were established by integrating fuzzy opinions on the relative importance of paired elements. The reason for using TFNs to capture the vagueness of the linguistic assessments is that TFN is intuitively easy to use (Tsao and Chu 2001; Kannan et al. 2009).

Step 7:

\(W_{{a_{1} i}}\): the value of the minimum fuzzy weight (left value). Wmi: the value of the grade of membership of the fuzzy weight. \(W_{{a_{2} i}}\): the value of the maximum fuzzy weight (right value). Wi: convert the fuzzy weight of the triangular fuzzy numbers into a single value.

Step 8:

The final normalized weight (NW) is then obtained as follows:

$$NW_{i} = \frac{{W_{i} }}{{\sum\nolimits_{i = 1}^{n} {W_{i} } }}$$

(9)

Appendix B

The PROMETHEE method can be described as presented in the following steps (Brans and Vincke 1985; Behzadian et al. 2010; Boutkhoum et al. 2016b; Hanine et al. 2016b):

Assume that ai(i = 1, 2,…, m) is a set of m alternatives, and ωj(j = 1, 2,…, n) represent the weights of n criteria.

Step 1:

Calculate the evaluative differences of any two alternatives (ai, ak) with respect to criterion j, denoted as dj(ai,ak),