- Research
- Open Access
- Published:

# An integrated decision-making prototype based on OLAP systems and multicriteria analysis for complex decision-making problems

*Applied Informatics*
**volume 4**, Article number: 11 (2017)

## 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.

## 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.

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.

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.

### 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.

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.

## 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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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 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).

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.

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.

## References

Al-Aqrabi H, Liu L, Hill R, Antonopoulos N (2015) Cloud BI: future of business intelligence in the cloud. J Comput Syst Sci 81(1):85–96

Aligon J, Gallinucci E, Golfarelli M, Marcel P, Rizzi S (2015) A collaborative filtering approach for recommending OLAP sessions. Decis Support Syst 69:20–30. doi:10.1016/j.dss.2014.11.003

Behzadian M, Kazemzadeh RB, Albadvi A, Aghdasi M (2010) PROMETHEE: a comprehensive literature review on methodologies and applications. Eur J Oper Res 200:198–215

Bilsel RU, Buyukozkan G, Ruan D (2006) A fuzzy preference ranking model for a quality evaluation of hospital web sites. Int J Intell Syst 21(11):1181–1197

Boutkhoum O, Hanine M, Tikniouine A, Agouti T (2015a) Multi-criteria decisional approach of the OLAP analysis by fuzzy logic: green logistics as a case study. Arab J Sci Eng 40(8):2345–2359. doi:10.1007/s13369-015-1724-8

Boutkhoum O, Hanine M, Agouti T, Tikniouine A (2015b) An improved hybrid multi-criteria/multidimensional model for strategic industrial location selection: Casablanca industrial zones as a case study. SpringerPlus. doi:10.1186/s40064-015-1404-x

Boutkhoum O, Hanine M, Boukhriss H, Agouti T, Tikniouine A (2016a) Multi-criteria decision support framework for sustainable implementation of effective green supply chain management practices. SpringerPlus. doi:10.1186/s40064-016-2233-2

Boutkhoum O, Hanin M, Agouti T, Tikniouine A (2016b) Selection problem of cloud solution for big data accessing: fuzzy AHP-PROMETHEE as a proposed methodology. J Digit Inf Manag 14(6):368–382

Brans JP, Vincke P (1985) A preference ranking organization method. Manag Sci 31:647–656

Buckley JJ (1985) Fuzzy hierarchical analysis. Fuzzy Sets Syst 17(3):233–247

Codd EF, Codd SB, Salley CT (1993) Providing OLAP to user-analysts IT mandate. Codd & Date, Inc, United States

Cuzzocrea A, Moussa R, Xu G (2013) OLAP*: effectively and efficiently supporting parallel OLAP over big data. Model Data Eng 8216:38–49

Dehne F, Kong Q, Rau-Chaplin A, Zaboli H, Zhou R (2014) Scalable real-time OLAP on cloud architectures. J Parallel Distrib Comput. doi:10.1016/j.jpdc.2014.08.006

Gil-Lafuente AM, Merigó JM, Vizuete E (2014) Analysis of luxury resort hotels by using the fuzzy analytic hierarchy process and the fuzzy Delphi method. Econ Research-Ekon Istraž 27(1):244–266

Gumus AT (2009) Evaluation of hazardous waste transportation firms by using a two step fuzzy-AHP and TOPSIS methodology. Expert Syst Appl 36:4067–4074

Hanine M, Boutkhoum O, Maknissi AE, Tikniouine A, Agouti T (2016a) Decision making under uncertainty using PEES–fuzzy AHP–fuzzy TOPSIS methodology for landfill location selection. Environ Syst Decis. doi:10.1007/s10669-016-9609-2

Hanine M, Boutkhoum O, Agouti T, Tikniouine A (2016b) A new integrated methodology using modified Delphi-fuzzy AHP-PROMETHEE for Geospatial Business Intelligence selection. Inf Syst e-Bus Manag. doi:10.1007/s10257-016-0334-7

Hilson G, Nayee V (2002) Environmental management system implementation in the mining industry: a key to achieving cleaner production. Int J Miner Process 64(1):19–41

Kang WL, Kim HG, Lee YG (2015) Efficient indexing for OLAP query processing with MapReduce. Comput Sci Appl 330:783–788

Kannan G, Pokharel S, Sasi Kumar P (2009) A hybrid approach using ISM and fuzzy TOPSIS for the selection of reverse logistics provider. Resour Conserv Recycl 54(1):28–36

Kaya M, Alhajj R (2014) Development of multidimensional academic information networks with a novel data cube based modeling method. Inf Sci 265:211–224

Kimball R (1996) The data warehouse toolkit: practical techniques for building dimensional data warehouses. Wiley, New York. ISBN 0-471-15337-0

Kimball R, Ross M (2002) The data warehouse toolkit: the complete guide to dimensional modeling, 2nd edn. Wiley, Hoboken

Lee CKH, Choy KL, Ho GTS, Chin KS, Law KMY, Tse YK (2013) A hybrid OLAP-association rule mining based quality management system for extracting defect patterns in the garment industry. Expert Syst Appl 40(7):2435–2446

Loudcher S, Jakawat W, Morales EPS, Favre C (2015) Combining OLAP and information networks for bibliographic data analysis: a survey. Scientometrics 103(2):471–487

Pentaho (2016) Mondrian: Online Analytical Processing server (OLAP). http://community.pentaho.com/projects/mondrian/

Poulton MM, Jagers SC, Linde S, Van Zyl D, Danielson LJ, Matti S (2013) State of the world’s nonfuel mineral resources: supply, demand, and socio-institutional fundamentals. Annu Rev Environ Resour 38:345–371

Promethee (2016) Multicriteria decision aid methods, modeling and software. http://www.promethee-gaia.net/visual-promethee.html

Song J, Guo C, Wang Z, Zhang Y, Yu G, Pierson JM (2015) HaoLap: a Hadoop based OLAP system for big data. J Syst Softw 102:167–181

Thalhammer T, Schrefl M, Mohania M (2001) Active data warehouses: complementing OLAP with analysis rules. Data Knowl Eng 39(3):241–269

Tsao C-T, Chu C-T (2001) Personnel selection using an improved fuzzy MCDM algorithm. J Inf Optim Sci 22(3):521–536

Tuzkaya UR (2009) Evaluating the environmental effects of transportation modes using an integrated methodology and an application. Int J Environ Sci Technol 6(2):277–290. doi:10.1007/bf03327632

## 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.

## Author information

### Authors and Affiliations

### Corresponding author

## Appendices

### Appendix A

The various computational steps involved in employing the FAHP methodology (Buckley 1985; Gil-Lafuente et al. 2014; Boutkhoum et al. 2016a; Hanine et al. 2016a) are described as below:

###
**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, *x*
_{
ij
} = importance of *i*th criteria according to *j*th 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).

\(\tilde{x}_{{ij}} = (a_{{1ij}} ,\;m_{{ij}} ,\;a_{{2ij}} ), \quad a_{{1ij}} \le m_{{ij}} \le a_{{2ij}},\quad {i,\;j}{\text{ }} = {\text{1}},\;{\text{2}} \ldots ,\;{\text{n}}\)

where (*a*
_{1ijk
}, *m*
_{
ijk
}, *a*
_{2ijk
}) is the fuzzy evaluation of member *K* on the relative importance of criteria *i* and *j*.

###
**Step 4:**

After establishing triangular fuzzy numbers to evaluate experts’ fuzzy opinions, a fuzzy positive reciprocal matrix \(\tilde{D}\) must be established as follows:

###
**Step 5:**

For the consistency verification of fuzzy matrix \(\tilde{D}\):

We assume that *D* = [*x*
_{
ij
}] is a positive reciprocal matrix and its corresponding fuzzy positive reciprocal matrix is \(\tilde{D} = \left[ {\tilde{x}_{ij} } \right]\). Therefore, *D* = [*x*
_{
ij
}] is consistent, as well as \(\tilde{D} = \left[ {\tilde{x}_{ij} } \right].\)

###
**Step 6:**

The fuzzy weight (\(\tilde{W}_{i}\)) of the fuzzy positive reciprocal matrix is calculated as explained below:

\(\tilde{Z}_{i}\): geometric average of triangle fuzzy numbers.

###
**Step 7:**

During this step, we conduct a defuzzification process using the gravity method as follows:

\(W_{{a_{1} i}}\): the value of the minimum fuzzy weight (left value). *W*
_{
mi
}: the value of the grade of membership of the fuzzy weight. \(W_{{a_{2} i}}\): the value of the maximum fuzzy weight (right value). *W*
_{
i
}: 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:

### 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 *a*
_{
i
}(*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 (*a*
_{
i
}, *a*
_{
k
}) with respect to criterion *j*, denoted as *d*
_{
j
}(*a*
_{
i
},*a*
_{
k
}),

###
**Step 2:**

Choose the preference function (*P*
_{
j
}
*(a*
_{
i
}
*, a*
_{
k
}
*))* which means the preference of alternative a_{i} with regard to alternative a_{k} as shown in the Eq. (12):

where *F*
_{
j
} is a non-decreasing function of the observed deviation (*d*) between *f*
_{
j
} (*a*
_{
i
}) and *f*
_{
j
} (*a*
_{
k
}).

Six types for *F*
_{
j
} have been suggested as shown in Fig. 15. If *a* is better than *b* according to *j*th criterion, *F*
_{
j
}(*a*, *b*) > 0, otherwise *F*
_{
j
}(*a*, *b*) = 0. These types are: (1) usual criterion, (2) quasi-criterion (U-shape), (3) criterion with linear preference (V-shape), (4) level criterion, (5) V-shape with indifference criterion, and (6) Gaussian criterion.

###
**Step 3:**

Determine the aggregated preference function incorporating the weights:

###
**Step 4:**

Calculate the leaving and entering outranking flows. The leaving flows as a measure for the weakness of the alternative *a*
_{
i
}, and the entering flows as a measure for the strength of the alternative *a*
_{
i
}.

where *h* is the number of alternatives.

###
**Step 5:**

Calculate the net outranking flow *ϕ*(*a*
_{
i
}):

The final ranking of each proposed solution is obtained by classifying the net outranking flow *ϕ*(*a*
_{
i
}) from the largest value to the lowest value.

### Appendix C

Star schema of the new proposed OLAP cube model used in the case study.

## Rights and permissions

**Open Access** This 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.

## About this article

### Cite this article

Boutkhoum, O., Hanine, M. An integrated decision-making prototype based on OLAP systems and multicriteria analysis for complex decision-making problems.
*Appl Inform* **4**, 11 (2017). https://doi.org/10.1186/s40535-017-0041-6

Received:

Accepted:

Published:

DOI: https://doi.org/10.1186/s40535-017-0041-6

### Keywords

- Decision information systems
- Decision support systems
- OLAP systems
- MCA
- Fuzzy analysis
- Group decision making