Business intelligence’s Self-Service tools evaluation

Interuniversity Master in Statistics and Operations Research Title: Business Intelligence’s Self-Service tools evaluation Author: Jordina Orcajo Advisor: Pau Fonseca Department: Statistics and Operative Research University: UPC-UB Academic year: 2015 Facultat de Matemàtiques i Estadística Universitat Politècnica de Catalunya Master’s degree thesis Business Intelligence’s Self-Service tools evaluation Jordina Orcajo Hernández Director: Pau Fonseca Department of Statistics and Operational Research 4 5 1 Abstract This project proposes a comparison analysis between four different tools, called Self-Service tools, from the Business Intelligence area. The comparison was done adapting a Systemic Quality Model, already, formalized and using a database simulated with R. In order to assess the quality of this tipe of software, seven (7) characteristics and eighty-two (82) metrics were considered. 6 Index 1 Abstract ................................................................................................................................. 5 2 Introduction .......................................................................................................................... 8 2.1 Approach ....................................................................................................................... 9 2.2 Introduction to BI systems ............................................................................................ 9 2.3 BI users ........................................................................................................................ 11 3 Methodology ....................................................................................................................... 13 3.1 The systemic quality model (SQMO) ........................................................................... 13 3.1.1 Level 0: dimensions .......................................................................................... 13 3.1.2 Level 1: categories ........................................................................................... 14 3.1.3 Level 2: characteristics .................................................................................... 14 3.1.4 Level 3: Metrics ................................................................................................. 16 3.2 Algorithm ..................................................................................................................... 16 3.2.1 Product software ............................................................................................... 16 3.2.2 Development Process ...................................................................................... 17 3.3 Adoption of the systemic quality model (SQMO) ....................................................... 18 3.3.1 Scales of measurement ................................................................................... 20 3.3.2 The concept of satisfaction ............................................................................. 24 3.4 Sub-characteristics and metrics for Self-Service BI tools evaluation .......................... 26 3.4.1 Functionality category ...................................................................................... 27 3.4.2 Usability category ............................................................................................. 32 3.4.3 Efficiency category: .......................................................................................... 34 4 Software selection for the evaluation ................................................................................. 35 4.1 Algorithm ..................................................................................................................... 35 4.2 The 4 evaluated software ............................................................................................ 40 5 Data ..................................................................................................................................... 41 5.1 Relational data model ................................................................................................. 41 5.2 20141220_Initial_test ................................................................................................. 42 5.3 Tables .......................................................................................................................... 44 5.3.1 Client table ......................................................................................................... 45 5.3.2 Auto table ........................................................................................................... 46 5.3.3 Region table ...................................................................................................... 46 7 5.3.4 RiskArea table ................................................................................................... 47 5.3.5 Guarantees table .............................................................................................. 48 5.3.6 RiskAreaXGuarantees table ........................................................................... 49 5.3.7 Policy table ........................................................................................................ 50 5.3.8 SinistersXYears ................................................................................................ 51 5.3.9 Sinisters table .................................................................................................... 52 6 Evaluation Results ............................................................................................................... 53 6.1 Results ......................................................................................................................... 55 7 Conclusions ......................................................................................................................... 61 8 Bibliography ........................................................................................................................ 62 9 Figures index ....................................................................................................................... 64 10 Tables index ..................................................................................................................... 65 Annex 1 : Scripts for 20141220_Initial_test database ................................................................ 66 Annex 2 : Questionnaires ............................................................................................................ 76 Annex 3:QlickView evaluation..................................................................................................... 85 Annex 4: SAP Lumira evaluation ................................................................................................. 94 Annex 5: MicroStrategy Analytics evaluation ........................................................................... 101 Annex 6: Tableau evaluation ..................................................................................................... 108 Annex 7: Reporting examples ................................................................................................... 113 8 2 Introduction This study belongs to the business sector. In particular the Business Intelligence sector, where I participated doing this Master’s degree thesis. Business Intelligence (BI) is the name associated to the set of tools and techniques for the transformation of raw data into meaningful and useful information for business analysis purposes. BI technologies are capable of handling large amounts of unstructured data to help identify, develop and otherwise create new strategic business opportunities. And the main goal of BI is to allow the easy interpretation of these large volumes of data. In particular, the Self-Service BI aims to boost that the company is able to get useful information from their own data. The idea behind deploying self-service software, is to empower business people to analyze and understand data without specialized expertise. There are many benefits that can be derived through the implementation of a self-service BI system. Functional workers can make, faster, better decisions because they no longer have to wait during long reporting backlogs. At the same time, technical teams will be freed from the burden of satisfying end user report requests, so they can focus their efforts on more strategic IT initiatives. There are many Self-Service BI tools in the market, and before recommending a particular one, a depth analysis of the available tools on the market must be done, according to own requirements. And because of this, the aim of this thesis is to build a comparative assessment of Self-Service BI tools, adapting a Systemic Quality Model (SQMO) and apply this methodology in the evaluation of four (4) particular tools. In order to accomplish this, first of all we had to learn how to use Self-Service BI tools in order to know its operation, what they can do and understand how useful they are for the BI sector. Knowing, with a minimum level of depth, tools in order to evaluate them, demands spending much time in addition to technical and functional knowledge. And because of this , we have done this work together with the department of Business Intelligence from INDRA S.A and under the tutelage of Dr. Pan Fonseca. Secondly, we adapted the SQMO to particular aims and finally four (4) tools were evaluated. They were Tableau, MicroStrategy Analytics, QlikView and SAP Lumira. As it has been pointed before, in the BI world there are many Self-Service tools, making this thesis interesting within this sector, because, probably, not all of them fulfil the requirements for all type of projects. It has to take into account that the concept “best tool” is difficult to apply in this ambit. And for this reason, it is more usual to choose an appropriate solution for a particular project. At this moment, many consultant companies are interested in knowing which are the tool/s closer to their clients’ requirements. Particularly, INDRA was interested in determine which of the four (4) evaluated tools is/are closer to its clients’ requirements. INDRA S.A was also interested to apply this evaluation method on further comparisons, with other Self-Service BI tools. It means that, from this thesis, can result an applicable method to determine which tool has to be chosen in each particular project. Nowadays, the term Business Intelligence it is also known as Business Analysis (BA). This change is due to the implemented techniques added in order to extract more information from 9 business data. BA is defined as the skills, technologies and practices for continuous iterative exploration and investigation of past business performance to gain insight and drive business planning, based on data and statistical methods. To gain future vision of the business, predictive modelling takes an important role. It helps to get different scenarios depending on different possible business paths. The implementation of predictive modelling can be considered the biggest difference between Business Intelligence and Business Analysis. Although predictive techniques are not in the pure definition of Business Intelligence, offering predictive techniques will be positively evaluated on this thesis, because it is considered that those tools must also evolve with the needs and interests of the companies. 2.1 Approach To carry out an assessment, a series of steps must be followed. First of all, the responsible of preparing the assessment known as the evaluator, must know the area of use. Then, the evaluator has to fix a methodology and adapt it to the particular area of use. The adaption implies decide the interesting metrics which will be evaluated. Users can advice to the evaluator about the interesting metrics, and the evaluator have to design a questionnaire to enclose the interesting metrics. Additionally, the evaluator has to fix the area of application, in order to not misuse the methodology. Following, evaluator has to send a questionnaire to the users, in order to get opinions from experienced people in the area. Moreover, the evaluator has to provide every item required to do the evaluation (questionnaires, data, applications ...). Finally, the evaluator collects the questionnaires and proceeds to evaluate the results according to the chosen methodology . In this particular thesis, the used methodology consists in the adaption of a Systemic Quality Model (SQMO), which is a model to evaluate software. The Systemic Quality Model and its adaption is explained, in detail, in chapter 3. On the other hand, in chapter 4, there is the method used to select the applications, which can be evaluated. Finally, in order to evaluate particular tools, data and a questionnaire, which should be provided to users, were built. A database called 20141220_Initial_test was simulated, and it is explained in chapters 5. Moreover, the R scripts built to simulated it are in Annex 1. The questionnaire, resulting on the adaption of the SQMO, is in Annex 2. Finally, the answered questionnaires were analyzed, and the results are explained in chapter 6. Finally, in Annex 7, there some graphs built by SelfService BI tools in order to introduce them to the reader. Recalling, that the first step in a assessment is to know the area of use, and in this particular case it is the Business Intelligence area. For this reason, the terminology used in the thesis can be specific from the BI area. And reading the following sub-chapter 3.2 is recommended to understands the terminology used a long the thesis. 2.2 Introduction to BI systems The objective of the following chapter is to introduce the Business Intelligence terminology in order to ease the interpretation of the thesis. When a business needs to analyse its data in order to profit them and extract information and take advantage of this, business intelligence takes the role. Most of the companies generates data and these data are stored in databases. 16 Category Characteristics Process Effectiveness Process Efficiency CustomerSupplier Acquisition System or Software product Supply Requirements determination Operation Engineering Development Maintenance of software and systems Support Quality assurance Documentation Joint review Configuration management Auditing Verification Solving problems Validation Joint review Auditing Solving problems Management Management Management Quality management Project management Risk management Quality management Risk management Organizational Organizational Alignment Establishment of the process Management of change Process evaluation Process improvement Process improvement Measurement HHRR management Reuse Infrastructure Tab. 2 Characteristics for Process sub-model 3.1.4 Level 3: Metrics Each characteristic has a group of metrics to be evaluated. They are the evaluable attributes of the product and the process and they are not agreed because they vary depending on each study case. Metrics are defined, for our particular case in sub-chapter 3.4. 3.2 Algorithm The algorithm to measure the systematic quality by the SQMO, referenced in Mendoza, L. E., Pérez, M. A., & Grimán, A. C. (2005) is the following explained. First of all the Product Software is measured and then the Development Process. 3.2.1 Product software The first measured category must be always Functionality. If the category Functionality is satisfied, the evaluation continues with other categories. If the product does not meet the Functionality category, the evaluation is ended. It is because the functional category is the most 17 important in the quality measuring, given that Functionality identifies the software capability to fit to purpose for it was built. After that, a sub-model is adapted depending on the requirements. Two categories from the five remaining must be selected, which should be satisfied by the product and evaluated. The algorithm recommends working with a maximum of three product characteristics (including Functionality) , because if more than three product features are selected , some might conflict . In this sense, (Bass, Clements, & Kazzman, 1998) indicates that the satisfaction of quality attributes can have an effect, sometimes positive and sometimes negative , on meeting other quality attributes . (The definition of satisfaction can vary depending on the case of use and it is not fixed by the methodology. In sub-chapter 3.3.2, this issue is discussed). Finally, to measure the quality product of the software there is shown Tab. 3, in which there are the quality levels related with the satisfied categories. Functionality Second category Third category Quality level Satisfied No satisfied No satisfied Basic Satisfied Satisfied No satisfied Medium Satisfied No satisfied Satisfied Medium Satisfied Satisfied Satisfied Advanced Tab. 3 Quality levels for the Product Software Once the evaluation of the product software has ended, recalling that only if the quality level is at least basic, the Development Process evaluation may start. 3.2.2 Development Process In order to evaluate the Development Process there are 4 steps to follow. The algorithm used in the Development Process evaluation is fixed, unlike the Product Software evaluation. The steps are the followings: 1. Determining the percentage of N/A (Not applying) answers in the questionnaire for each category. If this percentage is greater than 11% appliance of the measuring instrument must be analysed and the algorithm stops. Otherwise, the step two is the next. 2. Determining the percentage of N/K (Not knowing) answers in the questionnaire for each category. If this percentage is greater than 15%, it shows that there exist a high level of ignorance for the activities of the particular category. If the percentage is lower, step three is the next. 3. Determining the satisfaction level for each category . (The definition of satisfaction can vary depending on the case of use and it is not fixed by the methodology. In sub-chapter 3.3.2, this issue is discussed). 4. Measuring the quality level of the process. The quality levels related with the satisfied categories are: Basic level: It is the minimum required level. Categories Customer-Supplier and Engineering are satisfied. 18 Medium level: In addition to the basic level categories satisfied, categories Support and Management are satisfied. Advanced level: All categories are satisfied. Quality Levels Category satisfied Advanced Medium Basic Customer-Supplier Engineering Support Management Organizational Tab. 4 Quality levels for Development Process Finally, there must be a join between the product quality measuring and the process quality measuring, in order to obtain the systematic quality measuring. The systemic quality levels are proposed in Tab. 5. Product quality level Process quality level Systemic quality level Basic - Null Basic Basic Basic Medium - Null Medium Basic Basic Advanced - Null Advanced Basic Medium Basic Medium Basic Medium Medium Medium Advanced Medium Medium Basic Advanced Medium Medium Advanced Medium Advanced Advanced Advanced Tab. 5 Systemic quality levels This method of measurement is responsible for maintaining a balance between the sub-models (when they are both included in the model). 3.3 Adoption of the systemic quality model (SQMO) SQMO was adopted as reference because it is a complete work influenced by many other models. First of all, it respects the concept of Total Quality Systemic from (Callaos & Callaos, Designing with a systemic total quality, 1996). It also considers the balance between the Process and Product sub-models proposed by (Humphrey, 1997). These sub-models are based on the Product and Process Quality models from (Ortega, Pérez, & Rojas, 2000) and (Pérez, Rojas, Mendoza, & Grimán, 2001), respectively. Moreover, the product quality categories are based on the work of (Dromey, 1996) and the international standard ISO/IEC 9126 (JTC 1/SC 7, 1991). And the process categories are extracted from the international standard ISO/IEC 15504 (ISO IEC/TR 15504-2, 1998). 19 Some authors as Kitcheman (Kitchenman, 1996) have pointed out that when characteristics are complex can be divided into a set of some simpler and a new level, sub-characteristics, can be created. In that particular case, sub-characteristics have been considered in order to gain clarity. In order to adapt the SQMO to each particular case, there must be decided which sub-model is considered (Product, Process or both), which dimension (Efficiency or/and Effectiveness), which sub-characteristics for each characteristic and which respective metrics should be evaluated. In the current evaluation, only the Product sub-model of SQMO was considered. The Process sub-model is excluded, because our intent is to evaluate the fully already developed tools as a future tool used for the BI workforce. For this reason, Process sub-model is not considered. Moreover, only Effectiveness dimension was considered because the special attention was focused on the evaluation of the software quality features observed on its execution. But, if anyone ever considers appropriately to include the sub-model Process or the Efficiency dimension, due to his owns interests, there exists the option to do so, by following the steps explained above. Hence, Fig. 2 reflects the adapted model used in the current evaluation for BI tools. Fig. 2 Diagram of the adapted Systemic Quality Model (Rincon, Alvarez, Perez, & Hernandez, 2005) Besides the Functionality category, we chose the Usability, because this type of tools are focused on non-technical users and the difficulty of the product must be minimum. Moreover, it must be an attractive product because the success of the tool depends on the user’s satisfaction. Finally, the Efficiency category was chosen because the processor type, the hard disk space and the minimum RAM required, are factors that play an important role in making the deployment 20 of the tool a successful one. Self-Service BI tools are popular thanks to its “working in memory”. Then it is important to evaluate the minimum amount of memory required. 3.3.1 Scales of measurement In the current evaluation, all the evaluated metrics are ordinal variables because they have more than two categories and they can be ordered or ranked. Recalling, that metrics are explained, in detail, in the next sub-chapter 4.9. There are different types of scale measurement depending on the metric.  Type A of scale measurement: The main part of metrics are measured by the following scale. With this scale, metrics are measured from 0 to 4, as follows: 0: The application does not have the feature. 1: The application matches the feature poorly or it does not matches strictly the feature but it can get similar results. 2: The application has the feature and matches the expectations, although it needs an extra corporative complement. This mark should be also assigned, when the feature implies a manual job (e.g. typing code, click a bottom) and the metric is requiring an automatic job. 3: The application has the feature and matches the expectations successfully without a complement. 4: The application has the feature and moreover, present advantages behind others. Even so, other metrics need to be measured in a special way.  Sub-type A.1 of scale measurement is assigned to binary metrics: We assign 0 value if the application does not have the feature, and 4 value if the application has it. We chose these values in order to be consistent with the rest of the measurement scales.  Sub-type A.2 of scale measurement is assigned when the metric is measurable: We assign 4 to the application with a better result and lower score to the others. As there are 4 applications, the scale is from 4 to 1. Although, if some applications had the same value for the metric, the same score has to be assigned to them. To clarify the current scale measurement, we present an example of the metric Compilation speed (which will be presented in sub-chapter 3.4). The Compilation Speed is measured with a scale from 1 to 4. We assign 1 value to the tools which requires more time to compile, and 4 to the tool which requires the shorter time. 21 Additionally, the official SQMO method involves a balance between all the characteristics because they have the same level of importance. But, sometimes, the user wants to give more importance to certain characteristics depending on his owns interests, and for that, we provided the following alternative, also very used as a variant of SQMO. This alternative consists on assigning weights to the metrics. Therefore the importance level of the metrics varies. In the current project, the weights were assigned by Carlos Barahona, an expert user from INDRA. He remarked, that weights must depend on the requirements of each project. However, he tried to assign weights generalizing and based on his own experience managing projects. Recalling, that if the methodology is implemented for another use case, they can be modified. The used weights scale are the following: 0: Not applicable to Organization. 1: Possible usage feature or wish list item. 2: Desired feature. 3: Required or must have feature. Finally, final scores for sub-characteristics, are computed using the weights assigned to the metrics. The final score of a sub-characteristic corresponds to the following formula: Where, is the value for the score assigned to metric j, while is the weight for the corresponding metric. And n corresponds to the number of metrics in the sub-characteristic i . This adaption of the methodology, is applied when the importance level of the metrics is not the same in all metrics. By this way, we got a score for each sub-characteristic, considering the weights of metrics. Tab. 6 shows the weights assigned to each metric, in that case of use. Recall, that metrics are defined in the next sub-chapter 3.4. METRIC WEIGHT Direct connection to data sources 2 BigData sources 1 Apache Hadoop 1 Microsoft Access 2 Excel files 3 From an excel file, import all sheets at the same time 2 Cross-tabs 2 Plain text 3 22 Connecting to different data sources at the same time 2 Easy integration of many data sources 2 Visualizing data before the loading 2 Determining data format 2 Determining data type 2 Allowing column filtering before the loading 2 Allowing row filtering before the loading 2 Automatic measures creation 3 Allow renaming datasets 2 Allow renaming fields 3 Data cleansing 2 Data model is done automatically 2 The done data model is the correct one 2 Data model can be visualized 3 Alerting about circular references 3 Skiping with circular references 3 A same table can be used several times 2 Creating new measures based on previous measures 3 Creating new measures based on dimensions 3 Variety of functions 3 Descriptive statistics 2 Preduction functions 2 R connection 2 Geographic information 2 Time hierarchy 3 Creating sets of data 2 Filtering data by expression 3 Filtering data by dimension 3 Visual Perspective Linking 2 No Null data specifications 2 Considering nulls 3 23 Variety of graphs 3 Modify graphs 3 No limitations to display large amounts of data 2 Data refresh 2 Dashboards Exportation 3 Templates 2 Free design 2 Reports Exportation 3 Templates 2 Free design 2 Languages displayed 2 Operating Systems 2 SaaS/Web 1 Mobile 2 Using the project by third parts 2 Exportation in txt 2 Exportation in CSV 2 Exportation in HTML 2 Exportation in Excel file 3 Password protection 3 Permissions 3 Average learning time 3 Consistency between icons in the toolbars and their actions 3 Displaying right click menus 3 Ease of understanding the terminology 3 User guide quality 2 User guide adquisition 2 On-line documentation 2 Availability of tailor-made training courses 2 Phone technical support 2 On-line support 2 24 Availability of consulting services 2 Free formation 2 Community 2 Editing elements by double-clicking 2 Dragging and dropping elements 2 Editing the screen layout 2 Automatic update 2 Compilation Speed 2 CPU(processor type) 2 Minimum RAM 2 Hard disk space required 2 Additional software requirements 2 Tab. 6 Weights of metrics 3.3.2 The concept of satisfaction As it is said in sub-chapters 3.2.1 and 3.2.2, the term satisfaction can vary depending on the case of use. In fact, the evaluator can assign a limit, for example, 50%, and sentence that a feature is satisfied if its score is higher than the 50% ,of its maximum score in the measuring scale. For example, as our metric measuring scale is from 0 to 4, a metric is satisfied if its score is higher than 2. But, the evaluator can also sentence the limit to 3, and by this way, a metric is satisfied if its score is higher than 3. Usually, assessments are done to determine which tools are better than others, supposing that all the evaluated tools satisfy the main part of the features. When the evaluator is looking for a distinction between tools, these type of limits can be useful. This concept is applicable to our units of measurement, which are metrics, sub-characteristics, characteristics and categories. Once, metrics are evaluated with their respective scales of measurement (A, A.1, A.2), the methodology used to determine the satisfaction score is as follows:  Metrics scores are normalized with a percentage.  A metric is satisfied if its percentage score is higher or equal than the fixed limit.  Sub-characteristics are measured by the amount of metrics satisfied (satisfaction score). Then, a particular sub-characteristic is satisfied if the amount of satisfied metrics is higher or equal than its fixed limit. As weights were added, the satisfaction score become 25 Where, While is the weight for the corresponding metric j. And n corresponds to the number of metrics in the sub-characteristic i .  Characteristics are measured by the amount of satisfied sub-characteristics (satisfaction score). Then, a particular characteristic is satisfied if the amount of satisfied subcharacteristics is higher or equal than its fixed limit.  Categories are measured by the amount of satisfied characteristics (satisfaction score). Then, a particular category is satisfied if the amount of satisfied characteristics is higher or equal than its fixed limit. In the current evaluation, we decided to use the following limits, in order to get distinctions between tools: Limit for metric 50% Limit for sub-characteristic 50% Limit for Characteristic 75% Limit for Category 75% Tab. 7 Satisfaction limits The evaluator can decide to modify the levels, in the case that any distinction exist between tools or to be more restrictive or unrestrictive. 32 i Languages: This sub-characteristic is composed by a metric, which evaluates the variety of languages displayable in the tool. 1 Languages displayed (FIL1): It evaluates the variety of displayed languages offered by the tool. In particular, it evaluates if the tool can be displayed in more than two languages or not. 2 Portability: This sub-characteristic is composed by three metrics, which evaluates the ability of a tool to be executed in different environments. 3 Operating systems (FIP1): This metric measures the variety of different operating systems compatible with the tool. In particular, it evaluates if the tool can work, at least, in two different operating systems. 4 SaaS/Web (FIP2): The acronym SaaS means Software as a Service. This metric evaluates if a tool offers access to projects via web browser for hosting their own deployments in the cloud. 5 Mobile (FIP3): It evaluates the possibility to have reports and dashboards available in the mobile device via a mobile app. ii Use project by third parts: This sub-characteristic is composed by a unique metric and it measures the capability of sharing and modifying projects by other people. 1 Using the project by third parts (FIU1): It evaluates the capability to share projects and modify them by other users. iii Data exchange: This sub-characteristic is composed by metrics, which evaluate the data exportation when they have already been manipulated in the tool. 1 Exportation in .txt (FID1): It evaluates the capability of a tool to export data .txt. 2 Exportation in CSV (FID2): It evaluates the capability of a tool to export data in CSV format. 3 Exportation in HTML (FID3): It evaluates the capability of a tool to export data in HTML format. 4 Exportation in Excel file (FID4): It evaluates the capability of a tool to export data in Excel files. 3 Security characteristic: This characteristic is composed by a unique sub-characteristic, which groups metrics about the security process. i Security devices: This sub-characteristic is composed by two metrics related with the protection of data. 1 Password protection (FSS1): It evaluates the capability to protect projects with password. 2 Permissions (FSS2): It evaluates the capability to assign different permissions to different users. 3.4.2 Usability category 1 Ease of understanding and learning characteristic: This characteristic includes different sub-characteristics. i Learning time: This sub-characteristic includes only one metric. 1 Average learning time (UEL1): This metric measures the time spent by the user in learning the functionality of the tool. 33 ii Browsing facilities: This sub-characteristic evaluates how the user can browse inside the tool. 1 Consistency between icons in the toolbars and their actions (UEB1): This metric measures the capability of the tool to be consistent with its icons. 2 Displaying right click menus (UEB2): This metric measures if the tool offers a displaying menu by right clicking. iii Terminology: This sub-characteristic evaluates if the terminology is consistent with the global business intelligence terminology. 1 Ease of understanding the terminology (UET1): This metric measures how easy is for the user to understand the terminology. iv Help and documentation: This sub-characteristic is composed by metrics, which measures the help offered by the tool to a user when he has doubts about the functionality or management of the tool. 1 User guide quality (UEH1): This metric evaluates if the user guide is understandable. Highlighting that Self-Service tools are also offered for nontechnical users. 2 User guide acquisition (UEH2): This metrics measures the process to get to the user manual. For example, if it is free, if it is difficult to find in the web…. 3 On-line help (UEH3): It measures the offering of on-line help. v Support and training: This sub-characteristic measures the quality and variety of the support offered by the tool. 1 Availability of tailor-made training courses (UES1): It measures if the tool offers training courses adapted to organizations, and it is positively measured if the course can be done in the organization. 2 Phone technical support (UES2): It measures if the tool offers a phone for technical support and the timetable of it. 3 On-line support (UES3): It measures if the tool offers on-line support, and if it is in life or not. 4 Availability of consulting services (UES4): It measures if the company offers consulting services. 5 Free formation (UES5): It evaluates if the platform offers free formation for users. 6 Community (UES6): It evaluates if there exist a community to ask for doubts or to share knowledge with other users. 2 Graphical interface characteristic: This characteristic evaluates the graphical interface of the tool. i Windows and mouse interface: This sub-characteristic evaluates the windows interface and the mouse functions. 1 Editing elements by double-clicking (UGW1): It measures if the tool offers editing elements by double-clicking. 2 Dragging and dropping elements (UGW2): It measures the capability of the tool in dragging and dropping elements. 34 ii Display: This sub-characteristic refers to a unique metric about the capability of editing the screen layout. 1 Editing the screen layout (UGD1): It measures the capability of a tool to edit the screen layout. 3 Operability characteristic: This characteristic evaluates the ability of the tool to keep the system and the tool in reliable functioning conditions. i Versatility: This sub-characteristic evaluates the versatility of the tool. 1 Automatic update (UOV1): It measures if the tool is automatically updated when new versions appears. 3.4.3 Efficiency category: 1. Execution performance characteristic: This characteristic is composed by subcharacteristics, which evaluates the execution performance of the tool. ii Compilation speed: This sub-characteristic measures the compilation speed, how fast the software build a particular chart. 1 Compilation speed (EEO1): It measures the compilation speed. It is a very subjective measure because it depends on the machine where it is installed. iii Resource utilization: This sub-characteristic evaluates the extra hardware and software requirements. 1 CPU (processor type) (EER1): This metric evaluates if the tool can be installed as much to x86 processors suc has to x64 processors. 2 Minimum RAM (EER2): It measures the RAM needed in the way that a maximum punctuation means it requires low memory while the minimum punctuation means it needs many memory. 3 Hard disk space required (EER3): It measures hard disk space needed in the way that a maximum punctuation means it requires low space while the minimum punctuation means it needs many memory. iv Software requirements: This sub-characteristic is composed by a unique metric, which measures if adding software is required to execute the tool. 1 Additional software requirements (EES1): this metric evaluates if adding software is required to execute the tool. 35 4 Software selection for the evaluation Prior to an evaluation there must be a selection of software, hence some aspects should be considered. Firstly, the area of application and use of the software should be pre-established. The selection of software depends on this aspect because not every software is appropriate for every area. If the area of application is pre-established, the selected software will be according with it. Secondly, a new level of depth should be considered with more specifications about the tool functionality. It should consider the features that make the tool useful for what we want to do. And finally, there is the identification of the required attributes based on the particular aims of the organization who will use the tool. Some of these attributes must be mandatory and others must be non-mandatory. Mandatory attributes are those that must be met by the selected software, while non-mandatory are those that will be evaluated, that are the metrics. Therefore, this aspect takes an important role in the selection and also in the evaluation. 4.1 Algorithm In order to select the software, the first step was to decide which tools could be evaluated with this model. Nowadays, there are many applications in the market related with Business Intelligence. And because of that, deciding which applications should be included in an evaluation is a laborious task. In this stage we were inspired by the methodology for selecting software proposed by Le Blanc. In the first place, a long list of BI tools was elaborated. Next step was to reduce this to a medium list containing popular tools which accomplish critical capabilities for business intelligence and analytics. And finally, a short list provided with particular aims of the organization, was built. The particular area of application is Business Intelligence and there are many platforms specialized in this area in the market. Therefore we focus on which have been mentioned in the report Magic Quadrant for Business Intelligence and Analytics Platforms (February 2015) from Gartner. Gartner is an information technology research and advisory company, which presents every year different market research reports on IT products. Magic Quadrant is an annual report that reflects the innovations and changes that are driving the BI market and shows the relative position of each competitor in the business analytics space. They consider all tools in the market, and if these tools met the inclusion criteria they are included in the evaluation. In this first step, we used Gartner as a data source of all Business Intelligence and Analytics platforms in the market. Each year it edits an updates reports and also their inclusion criteria changes depending on how the market changes, so it is a reference company to have knowledge of BI tools. By this way, all the tools mentioned in the Magic Quadrant report of February 2015 (although Gartner, finally, have not evaluated them) composed our long list of 63 different platforms, which is the following: 1) Adaptive Insights 2) Advizor Solutions 3) AFS Technologies 4) Alteryx 5) Antivia 6) Arcplan 7) Automated Insgihts 8) BeyondCore 9) Birst 10) Bitam 11) Board International 12) Centrifuge Systems 13) Chartio 14) ClearStory Data 15) DataHero 16) Datameer 17) DataRPM 18) Datawatch 19) Decisyon 20) Dimensional Insight 21) Domo 22) Dundas Data Visualization 23) Eligotech 24) eQ Technologic 25) FICO 26) GoodData 27) IBM Cognos 28) iDashboards 29) Incorta 30) InetSoft 31) Infor 32) Information Builder 33) Jedox 34) Kofax(Altosoft) 35) L-3 36) LavaStorm Analytics 37) Logi Analytics 38) Microsoft BI 39) MicroStrategy. 40) Open Text (Actuate) 41) Oracle 42) Palantir Technologies 43) Panorama 44) Pentaho 45) Platfora 46) Prognoz 47) Pyramid Analytics 48) Qlik 49) Salesforce 50) Salient Management Company 51) SAP 52) SAS (SAS Business Analytics) 53) Sisense 54) Splunk 55) Strategy Comapnio 56) SynerScope 57) Tableau 58) Targit 59) ThoughtSpot 60) Tibco Software 61) Yellowfin 62) Zoomdata 63) Zucche 37 To build the medium list we followed also the steps of Gartner, in the Magic Quadrant report, where they choose the platforms to be evaluated if they satisfied 13 technique features and 3 non-technique. The 13 technique features were, by Gartner, the critical capabilities that every Business Intelligence and Analytics platform must satisfy. And they were classified in three categories: Enable, Produce and Consume. Enable:  Functionality and Modelling: Combination of different sources and the creation of analytic models such as user-defined measures, sets, groups and hierarchies. Advanced capabilities include semantic auto discovery, intelligent joins, intelligent profiling, hierarchy generation, data lineage and data blending on varied data sources, including multi structured data.  Internal Platform Integration: A common look and feel, install, query engine, shared metadata, promo ability across all platform components.  BI Platform Administration: Capabilities that enable securing and administering users, scaling the platform, optimizing performance and ensuring high availability and disaster recovery.  Metadata Management: Tools for enabling users to leverage the same systems-of-record semantic model and metadata. They should provide a robust and centralized way for administrators to search, capture, store, reuse and publish metadata objects, such as dimensions, hierarchies, measures, performance metrics/KPIs, and report layout objects.  Cloud Deployment: Platform as a service and analytic application as a service capabilities for building, deploying and managing analytics in the cloud.  Development and Integration: The platform should provide a set of programmatic and visual tools and a development workbench for building reports, dashboards, queries and analysis. Produce:  Free-Form Interactive Exploration: Enables the exploration of data via the manipulation of chart images, with the colour, brightness, size, shape and motion of visual objects representing aspects of the dataset being analysed.  Analytic Dashboards and Content: The ability to create highly interactive dashboards and content with visual exploration and embedded advanced and geospatial analytics to be consumed by others.  IT-Developed Reporting and Dashboards: Provides the ability to create highly formatted, print-ready and interactive reports, with or without parameters. This includes the ability to publish multi object, linked reports and parameters with intuitive and interactive displays.  Traditional Styles of Analysis: Ad hoc query enables users to ask their own questions of the data, without relying on IT to create a report. In particular, the tools must have a reusable semantic layer to enable users to navigate available data sources, predefined metrics, hierarchies and so on. 38 Consume:  Mobile: Enables organizations to develop and deliver content to mobile devices in a publishing and/or interactive mode.  Collaboration and Social Integration: Enables users to share and discuss information, analysis, analytic content and decisions via discussion threads, chat, annotations and storytelling.  Embedded BI: Capabilities for creating and modifying analytic content, visualizations and applications, and embedding them into a business process and/or an application or portal. Moreover, platforms had met other non-technical criteria:  Generating at least $20 million in total BI-related software license revenue annually, or at least $17 million in total BI-related software license revenue annually, plus 15% yearover-year in new license growth.  In the case of vendors that also supply transactional applications, show that its BI platform is used routinely by organizations that do not use its transactional applications.  Had a minimum of 35 customer survey responses from companies that use the vendor's BI platform in productions. With this added non-technical features, they guaranty that at least 35 companies use each one of the tools. Moreover, they guaranty that companies, which are growing year-over-year, use these tools. That’s why INDRA is interested in these particular tools because of their popularity and, as a consultant, they want to be up-to-date on this area. And the medium list obtained is: 1) Alteryx 2) Birst 3) Board International 4) Datawatch 5) GoodData 6) IBM Cognos 7) Information Builder 8) Logi Analytics 9) Microsoft BI 10) MicroStrategy. (MicroStrategy Visual Insight) 11) Open Text (Actuate) 12) Oracle 13) Panorama 14) Pentaho 15) Prognoz 16) Pyramid Analytics 17) Qlik (QlikView) 18) Salient Management Company 19) SAP (SAP Lumira) 20) SAS (SAS Business Analytics) 21) Tableau 22) Targit 23) Tibco Software 24) Yellowfin 39 Finally, to build the short list we focus on the particular aims of the organizational unit. The particular tools that we wanted to evaluate are Self-Service BI tools and it means that the business user should be able to analyze the information he wants and build his owns reports. In traditional tools, user asked to a technical team for the information he needed and he ordered how information had be displayed and the technical team prepared data and built the ordered reports. Against that, Self-Service tools are being imposed on others because the working methodology is changing from being driven by the business model to being driven by the data model. The main features of Self-Service tools, according to INDRA S.A are:  Ease of use: These tools are designed to be used by non-technical people. It means that users do not need to spend much time in learning how the tool works before doing a basic analysis.  Ability to incorporate data sources, both corporative data base (Oracle, SAP,etc) as local (Basically excels) and also external data base (Twitter,etc).  ‘Intelligence’ to interpret correctly data models. As they are auto-service tools and they face to many type of data model, without a previous modeling by a technical team, the interpretation of the model from the tool must be the correct one. If it is not the correct one, it can be misleading. How easy is to discover that the data model is wrong and how easy is to arrange the data model, are also important points to consider.  Analysis functions: Besides the typical pie and bar graphs, they must incorporate other tools in order to get advanced analysis (integration in R, statistic routines…) always remembering the easy use.  Possible integration with corporative systems and efficiency: Usually, the user will work with huge volume of data and therefore the analysis cannot be in a local PC. Tools should have the option of a central server which access to data and process them. Big companies, as INDRA S.A clients, needs security when the server is incorporated to the corporative environment. And then, the role of an administrator to manage the user’s access is key for big companies.  Support: In the case, that an open source tool was included in the larger list, it will not be considered in the medium list because open source cannot offer an instantly customer support. In open source there are communities of users who can help others in their problems, and for INDRA as a big company, and as a company who offers they workers as a service, the customer support is very important and must be fast. Therefore, these five (5) features characterize the particular aims of the organization for the tools to be evaluated with the adapted SQMO. And then, from the medium list, the short list includes only tools, which, by our point of view, satisfy the mentioned features, and they are: 1) MicroStrategy Visual Insight from MicroStrategy platform 2) Panorama 3) Pyramid Analytics 4) QlikView tool from Qlik platform 5) SAP Lumira from SAP platform 6) SAS Business Analytics from SAS platform 7) Tableau 40 8) Tibco Jaspersfot from Tibco Software Platform Fig. 6 Schema for the selection process 4.2 The 4 evaluated software In this project we evaluate four (4) tools from the short list. The evaluated tools are those that, according to the vision of INDRA (INDRA has the major Business Intelligence unit in Spain), have more projection. As a cause of the amount of clients/projects implementing them or because clients show interest in these applications, the final four tools are: QlikView, Tableau, MicroStrategy Analytics and SAP Lumira. QlikView was designed in 1993 to generate business insight by accessing information from standard database applications and displaying their data associatively. Moreover, it already runs entirely in memory, as a pioneer. And 7 years later, QlikView was focused on the BI market. Because it was the more mature tool in the market running in memory associative search engine, it was a very interesting tool for clients, and it was evaluated. MicroStrategy, as a global BI platform, was the most implemented tool among INDRA‘s clients. And because of that, its BI tool, MicroStrategy Analytics, was evaluated. Tableau was the tool, among all Self-Service BI tools, which was mentioned by more clients. This tool was created with the objective of giving more emphasis to the visual data analysis. Because of its popularity among clients, it was evaluated. SAP Lumira was chosen because of the huge number of SAP implementations in management systems. SAP had been incorporated recently in the Data discovery with SAP Lumira but its success in management systems and its huge number of implementations maked it a natural competitor to consider. As many clients had implemented SAP in their management system, they, surely, would opt to implement SAP Lumira thinking in a better integration with their system and an easier architecture because of a unique provider. 41 5 Data In order to use and evaluate the applications, we needed a set of data and we decided to simulate it. The data set was simulated using the tool R and it was constructed replying an car insurance company database and using a relational structure. 5.1 Relational data model A relational database is based on the relational model developed by E.F. Codd. In such models data are organized into tables related one to each other by at least one common field. The main important properties of relational data model are that:  Data are presented as a collection of relations between tables.  Each relation is defined by one or more column (field) in common between tables.  Columns are attributes that belong to the entity modelled by the table (ex. In a client table, you could have name, gender, birthday, etc.).  Each row (also called tuple) represents a single entity (ex. In a client table, John Smith, Male, 30/11/1975, would represent one client entity).  Every table has a set of attributes that taken together as a key, uniquely identifies each entity (e.g.: in a client table, “ClientID” would uniquely identify each client – no two clients would have the same clientID). Certain fields are designated as keys, which means that searches for specific values of that field will uniquely identify each entity. There are many types of keys, however, quite possibly the two most important are the primary key and the foreign key. The primary key is what uniquely identifies each entity within a table. The foreign key is a primary key of one table, that is also present into another table. Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields. Usually, but not always, the fields will have the same name in both tables. Ultimately, the use of foreign keys is the heart of the relational database model. This linkage that the foreign key provides, is what allows to link data together. In the relational data model, there are two important rules that help to ensure data integrity. They are:  Every tuple is unique. This means that for every record in a table there is something that uniquely identifies it from any other tuple, the primary key.  Table names in the database must be unique and attribute names in tables must be unique. No two different tables can have the same name in a data model. Attributes (columns) cannot have the same name in a table. You can have two different tables that have similar attribute names. Among relational data model, there are different types of models depending on its structure. Particularly, we used a snow flake schema which is a type of relational data model composed by two types of tables: Fact tables and Dimension tables. 48 At the last point, a name was assigned to each risk area category by ourselves in the RiskAreadesc field. Finally, the resulting Risk Area table Fig. 11 has 5 registers and 2 columns. Fig. 11 RiskArea table 5.3.5 Guarantees table Guarantees table corresponds to a dimension table, including information about guarantees offered for the insurance company and it is composed by the following fields: Guarantees and Base. They are described in Tab. 13. Numeric fields Field name Description Min Max Base The cost which is responsible the company 25 3.000 Categorical fields Field name Description Values Guarantees Guarantees ‘windows’, ‘travelling’, ‘driver insurance’, ‘claims’, ‘fire’, ‘theft’, ‘total loss’, ‘health assistance’ Tab. 13 Guarantees table description Both fields were created by ourselves. Finally, the resulting Guarantees table Fig. 12 has 8 registers and 2 columns. Fig. 12 Guarantees table 49 5.3.6 RiskAreaXGuarantees table RiskAreaXGuarantees table corresponds to a dimension table, which provides the relation between RiskArea table and Guarantees table. It shows which guarantees are offered by each risk area. And it is composed by the following fields: RiskArea and Guarantees. They are described in Tab. 14. Categorical fields Field name Description Values RiskArea Risk area identification ‘1’, ‘2’, ‘3’,’4’,’5’ Guarantees Guarantees ‘windows’, ‘travelling’, ‘driver insurance’, ‘claims’, ‘fire’, ‘theft’, ‘total loss’, ‘health assistance’ Tab. 14 RiskXGuarantees table description We created the RiskArea field repeating the value of each risk area as many times as guarantees it offers. And Guarantees is the field with the respective guarantees offered by each risk area. Recall that it has a composed primary key because any of the fields is capable to identify uniquely the registers, but both together form a composed primary key. Finally, the resulting RiskXGuarantees table showed in Tab. 14, has 29 registers ( risk area 1 offers 8 guarantees, risk area 2 offers 7 guarantees, risk area 3 offers 6 guarantees, risk area 4offers 5 guarantees and risk area 5 offers 3 guarantees) and 2 columns. Fig. 13 RiskAreaXGuarantees table 50 5.3.7 Policy table Policy table corresponds to a dimension table, which includes information about the policies of the 26.000 clients and it is composed by the following fields: PolicyID, ClientID, RecordBeg, RecordEnd, VehBeg, VehBrand, BonusMalus, RiskArea and Code. Fields are described in Tab. 15. Numeric fields Field name Description Min Max PolicyID Policy identification 1 26.000 ClientID Client identification 1 26.000 Date fields Field name Description Min Max RecordBeg Policy starts date 2000-01-01 2010-12-31 RecordEnd Policy ends date 2000-01-01 2010-12-31 VehBeg Date when vehicle was build 1911-01-26 2011-01-01 Categorical fields Field name Description Values VehicleBrand Vehicle Brand ‘1’,’2’,’3’,’4’,’5’,’6’,’10’,’11’,’12’,’13’,’14’ BonusMalus Bonus/Malus 50, 51, 51,... (75 different values) RiskArea Risk Area included in the policy ‘1’,’2’,’3’,’4’,’5’ Code Region identification ‘1’, ‘2’, ‘3’,... (52 different values) Tab. 15Policy table description The field PolicyID was created with values from 1 to 26.000. In that particular case, this field is equal to the ClientID because we were supposing that a client only had a policy in order to ease the analysis. The field ClientID let the join between Policy table and Client table. In order to create the fields RecordBeg we took 26.000 random dates between ‘2000-01-01’ and ‘2010-12-31’, remembering that we wished data for 10 years. And in order to create RecordEnd field we have supposed that the probability to leave the policy is 0.3. It means, that with a probability of 0.3 we assigned the NULL date ‘9999-01-01’ to RecordEnd components. For the filled components we assigned randomly a date between one year after the RecordBeg and ‘2010-12-31’. VehBeg field is composed by data extracted from the vehicle age field from freMTPL2freq, for the 26.000 randomly selected vehicles in Auto table. The values were previously modified because, in freMTPL2freq, the vehicle age was measured in years and we preferred to have the date when the car was building. VehBrand is the field mentioned in the Auto table paragraph, with 26.000 values corresponding to the vehicle brand of the selected vehicles. And RiskArea is the field mentioned in the RiskArea table paragraph. The field BonusMalus corresponds to a risk indicator of the policy. Its data were extracted from the BonusMalus field of freMPL6 dataset, for our 26.000 clients. Finally, the field Code refers to the code of the region where the policy is registered. It is created by assigning randomly numbers from 1 to 52 (each number refers to a region) depending on the population of each region. 51 Finally, the resulting Policy table Fig. 14 has 26.000 registers and 8 columns. Fig. 14 Policy table 5.3.8 SinistersXYears SinistersXYears table corresponds to a fact table, which shows how many accidents are registered in each policy along the years from 2000 to 2010. This is a cross-tab ¡Error! No se encuentra el origen de la referencia. and because of that the structure of that is more special than others. It is composed by the following fields: PolicyID, Sinisters, 2000, 2001,...2010. They are described in Tab. 16. Qualifier field Field name Description ClientID Policy identification Attribute field Field name Description 2000 Year 2001 Year 2002 Year ... ... 2010 Year Data field Field name Description Sinisters Amount of sinisters Tab. 16 SinistersXYears table description In order to assign the amount of accidents per year to each policy, we assigned a probability of 0,2 to have an accident in a year, to every policy. Depending on the characteristics of the client the probability could be increase.  If client is younger than 24, the probability increases in “0.1”.  If license is less than 12 months old, the probability increases “0.2”.  If client is between 50 and 65 years old and is a Male the probability increases in “0.2”.  If client it is between 40 and 45 years old and is a woman the probability increases in “0.2”. 52 After one accident, the probability of accident decreases on “0.1”. And we supposed that a policy could not have more than 3 accidents in the same year. The resulting SinistersXYear table Fig. 15 SinistersXYear tableFig. 15 has 26.000 registers and 12 columns. The first column corresponds to the ClientID field and each one of the others corresponds to a year from 2000 to 2010. Fig. 15 SinistersXYear table 5.3.9 Sinisters table This table corresponds to a fact table ¡Error! No se encuentra el origen de la referencia., including information about the accidents and it is composed by the following fields: PolicyID, RiskArea, Guarantees, Sinisterdate and Code. They are described in Numeric fields Field name Description Min Max PolicyID Policy identification 1 26.000 Date fields Field name Description Min Max Sinisterdate Policy starts date 2000-01-01 2010-12-31 Categoric fields Field name Description Values RiskArea Risk Area included in the policy ‘1’,’2’,’3’,’4’,’5’ Guarantees Guarantees ‘windows’, ‘travelling’, ‘driver insurance’, ‘claims’, ‘fire’, ‘theft’, ‘total loss’, ‘health assistance’ Code Region identification ‘1’, ‘2’, ‘3’,... (52 different values) Tab. 17 Sinisters table description This table is based on SinistersXYears table, because SinistersXYears table fixes the amount of sinister for each policy. Data for Sinisterdate are random dates with the year fixed for the SinistersXYears table. If a policy does not have any accident along the 11 years, it also appears in the table but with ‘999901-01’ as Sinisterdate. It is not usual, to add a policy without any accident in that type of tables, but it is useful to analyse how the applications manage null values. To create Code field, random numbers (referring to the code of the region) from 1 to 52 were assigned, but imposing that having a accident in the same region where the policy is registered, 53 is most probable (probability of “0.7”) than in another region (probability of “0.3/51”). Moreover, we also imposed that in the particular region ‘Granada’, the probability of accident increases in summer for people who are not from the region or environs (‘Jaen’, ‘Cordoba’, ‘Albacete’, ‘Malaga’, ‘Almeria’) and decrease for people who are from the region or environs. The Guarantees field has random guarantees as components depending on the risk area contracted by the policy. At the last, RiskArea is a field with the same components as RiskArea from Policy table but repeating each value as many times as accidents have the particular policy. Finally, the resulting Sinisters table has 196.235 registers and 5 columns: Fig. 16 Sinisters table The 9 datasets were exported in an Excel files, and from excel file they were loaded to the corresponding applications. 6 Evaluation Results Once the metrics are chosen, weights are assigned to each metric, applications are selected and data are available, it is time to carry out the evaluation. The current evaluation was done only by me as an Explorer user. But, as it is said in sub-chapter 3.3, an evaluation should be done by several users, representing all the different types of users. In this project, it could not be possible, but in order to get concluding results, it should be done. In order to store the scores, an excel sheet with the 82 metrics was build. It is where the evaluator have to fill the cells with the score for each of the metrics. The sheet was build considering the weights and the satisfaction score, established in sub-chapters 3.3.1 and 3.3.2, respectively. The sheet was replicated identically assigning a sheet to each application. Therefore, a total of 4 excel sheets were filled by the evaluator. Reader can visualize them in Annex 2, although the whole file will be attached to the thesis. 54 Additionally to the sheets, user has to work with the particular Self-Service BI applications in order to evaluate them, and they must be available to him. Mostly all Self-Service applications offer distinct editions. They usually have a Server Edition and a Personal Edition (also known as Desktop Editions). A Server edition is focused on companies. They offer the connection of several users to a central server which access to data and process them with much power than a local computer. Moreover, projects and data can be easily shared between users connected to the same server. On the other hand, Personal Editions, are single user editions, usually free trials, with the same operational characteristics, except for the connection to a server. And consequently, they can have limitations in the projects sharing. Moreover, the connection of different users to a server, usually implies the option of security devices, assigning permissions and passwords to data or projects. These options are not offered by Personal Editions. In this project, the evaluated applications are Server Editions, although, in order to evaluate their operation, we used Personal editions, which can be installed in local PC and they are offered in their respective corporative webs, by free. Particularly, the used editions were: QlikView View Personal Edition is the free trial for QlikView. With QlikView Personal Edition, user cannot open projects done by another Personal Edition’s user and does not have security devices. As we said, QlikView Personal Edition cannot be connected to a server with other QlikView users. On the other hand, it can be connected to the same type of databases than QlikView. MicroStrategy Analytics Desktop is the free edition for MicroStrategy Analytics Enterprise. MicroStrategy Analytics Desktop does not have any problem using projects done by other free edition’s users. Moreover, it can be connected to the same type of databases than the MicroStrategy Analytics. But, it cannot be connected to a server with other users and it has not security devices. SAP Lumira Desktop Standard Edtion used in the project is a 30-day free trial. This edition, is the personal edition of SAP Lumira Server. It can be connected to the same type of databases than the server one, and it can also open projects done by other users. SAP Lumira Desktop cannot be connected to a server with other users and it has not security devices. Tableau Desktop used in this project is a 14-day free trial. This edition, is the personal edition of Tableau Server. It can be connected to the same type of databases than the server one, and it can also open projects done by other users. Although, it cannot be connected to a Server with other users and it has not security devices. Once time, the user has the evaluation sheets and he has already used the corresponding applications with the database 20141220_Initial_test, he has to score the metrics. Scoring the metrics is the key step in order to get results about each of the applications in each of the three categories: Functionality, Usability and Efficiency. Remember, that the results could not be considered as concluding, because more user opinions should be considered. If there are more evaluators, the four (4) sheets, the four (4) applications and the database 20141220_Initial_test must be offered to each of them. 55 It must be consider, that the database used in the evaluation is in a excel file, and therefore we don’t have the experience to connect tools to databases. Therefore the information about connecting to databases provided here is extracted from external sources(user guides or corporative webs) and our experience do not prove it. 6.1 Results Once time every metric has been evaluated it is the time to get the results of the assessment. In the case than more than one user, is being implied in the evaluation of the metrics, we recommend to calculate a mean score for each metric. On the other hand, one of the basis of the methodology of (Mendoza, Pérez, & Grimán, 2005) is that if Functionality category is not satisfied, the evaluation is aborted and other categories are not evaluated. Because of that, the analysis starts with the satisfaction score of Functionality category. In the current evaluation, using the satisfaction limits mentioned in Tab. 7 from sub-chapter 3.3.1, the obtained satisfaction scores for Functionality are showed in Fig. 17. Fig. 17 Results for Functionality category In the adaption of the methodology in sub-chapter 3.3.1, we sentence that a category is satisfied if the 75% of their characteristics are satisfied. And, applying that, MicroStrategy Analytics did not satisfy the Functionality category because it only satisfies the 66,67% of the functional characteristics. Then, the evaluation of MicroStrategy is aborted. On the other hand, the other three (3) tools satisfy the Functionallity category, because they satisfy the 100% of the corresponding characteristics. In order to know the reason why MicroStrategy does not satisfy the Functionality category, a deeper level helped us to know what are the scores for each functional characteristic. Functional characteristics are Fit to Purpose, Interoperability and Security, and Fig. 18 shows their respective 56 satisfaction score. We could see that the characteristic Fit to purpose is not satisfied because only the 66,67% of its sub-characteristics are satisfied. Particularly, the sub-characteristics non-satisfied are Field Relations and Reporting. Fig. 18 Functinality characteristics results, for MicroStrategy Analytics MicroStrategy Analytics does not satisfy the sub-characteristic Fields relations because it is not capable to alert about the presence of circular references (FFF1), and in fact, it does not skip them (FFF2). Moreover, it cannot directly relate a table to more than one table (FFF3). On the other hand, Reporting sub-characteristic, are not satisfied because MicroStrategy Analytics does not have an option to build reports (FFR1), (FFR2), (FFR3). Then, MicroStrategy evaluation is aborted and the evaluation followes for the other three (3) tools. The other three tools satisfy, additionally to the Functionality, the Usability category. Moreover, QlikView and Tableau satisfy also the Efficieny category, but SAP Lumira does not. Fig. 19 shows the satisfaction score in each category. 57 Fig. 19 Category results SAP Lumira does not satisfy the Efficiency category. In fact, it does not satisfy the unique characteristic for Efficiency, which is Execution Performance, as it is shown in Fig. 20. Fig. 20 Efficiency characteristics results, for SAP Lumira This characteristic has a satisfaction score of 66,67%, lower than the fixed limit 75% and because of that it is considered as not satisfied. Only the 66,67% of the Execution Performance sub-characteristics are satisfied. In particular, Fig. 21 shows the satisfaction scores for the corresponding sub-characteristics. 64 9 Figures index Fig. 1 Diagram of the systemic quality model (SQMO) (Callaos & Callaos, 1996)................... 13 Fig. 2 Diagram of the adapted Systemic Quality Model (Rincon, Alvarez, Perez, & Hernandez, 2005) ........................................................................................................................................... 19 Fig. 3 Characteristic schema for each category, according to (Mendoza, Pérez, & Grimán, 2005) ..................................................................................................................................................... 26 Fig. 4 The correct data model for 20141220_Initial_test data ................................................... 28 Fig. 5 Circular Reference ............................................................................................................. 29 Fig. 6 Schema for the selection process ...................................................................................... 40 Fig. 7 Data model for 20141220_Initial_test .............................................................................. 43 Fig. 8 Client table ........................................................................................................................ 45 Fig. 9 Auto table .......................................................................................................................... 46 Fig. 10 Region table .................................................................................................................... 47 Fig. 11 RiskArea table ................................................................................................................. 48 Fig. 12 Guarantees table .............................................................................................................. 48 Fig. 13 RiskAreaXGuarantees table ............................................................................................ 49 Fig. 14 Policy table ..................................................................................................................... 51 Fig. 15 SinistersXYear table ......................................................................................................... 52 Fig. 16 Sinisters table .................................................................................................................. 53 Fig. 17 Results for Functionality category ................................................................................... 55 Fig. 18 Functinality characteristics results, for MicroStrategy Analytics .................................... 56 Fig. 19 Category results ............................................................................................................... 57 Fig. 20 Efficiency characteristics results, for SAP Lumira ............................................................ 57 Fig. 21 Execution Performance sub-characteristics results, for SAP Lumira ............................... 58 Fig. 22 Quality levels depending on satisfied categories. The particular case ............................ 58 Fig. 23 Category results, for a second evaluation ....................................................................... 59 Fig. 24 Characteristic results, for a second evaluation ............................................................... 59 Fig. 25 Heat Map chart built in MicroStrategy ............................................................................ 88 Fig. 26 Block chart built in QlikView ............................................................................................ 89 Fig. 27 Block Chart with background color assigned to an expression built in QlikView ............ 90 Fig. 28 Example of a Heat Map build by MicroStrategy Analyitics ........................................... 113 Fig. 29 Example of a Network chart built by MicroStrategy Analyitics ..................................... 113 Fig. 30 Example of a Network chart built by MicroStrategy Analyitics ..................................... 114 Fig. 31 Example of a Map chart built by MicroStrategy Analyitics............................................ 114 Fig. 32 Example of a Map chart built by MicroStrategy Analyitics............................................ 115 Fig. 33 Example of a k-means classification plot, done by the previous connection of MicroStrategy Analytics to R ..................................................................................................... 115 Fig. 34 Example of a Heat Map built by QlikView ..................................................................... 116 Fig. 35 Example of a Radar Map built by QlikView ................................................................... 116 Fig. 36 Example of a Radar Map built by QlikView ................................................................... 117 Fig. 37 Example of a forecasting ,built by SAP Lumira .............................................................. 118 Fig. 38 Example of a Funnel map, built by SAP Lumira ............................................................. 118 Fig. 39 Pie charts, built by Tableau ............................................................................................ 119 65 10 Tables index Tab. 1 Characteristics for Product sub-model ............................................................................. 15 Tab. 2 Characteristics for Process sub-model ............................................................................. 16 Tab. 3 Quality levels for the Product Software ........................................................................... 17 Tab. 4 Quality levels for Development Process .......................................................................... 18 Tab. 5 Systemic quality levels..................................................................................................... 18 Tab. 6 Weights of metrics ......................................................................................................... 24 Tab. 7 Satisfaction limits ............................................................................................................. 25 Tab. 8 Circular reference ............................................................................................................. 29 Tab. 9 Client table description .................................................................................................... 45 Tab. 10Auto table description ..................................................................................................... 46 Tab. 11 Region table description ................................................................................................. 47 Tab. 12 RiskArea table description ............................................................................................. 47 Tab. 13 Guarantees table description .......................................................................................... 48 Tab. 14 RiskXGuarantees table description ................................................................................ 49 Tab. 15Policy table description ................................................................................................... 50 Tab. 16 SinistersXYears table description .................................................................................. 51 Tab. 17 Sinisters table description .............................................................................................. 52 Tab. 18 Satisfaction Limits, for a second evaluation ................................................................... 58 66 Annex 1 : Scripts for 20141220_Initial_test database During the simulation process, some variables were called different than in the database. It Because of the relationships (1:n or n:m) , foreign key fields were build two times with different lengths, one time for each table from where they pertained. And to keep the consistency in the R code, they had to be considered as different fields, and for this reason they were called different. Although, once the database was created, the names were changed in order to build relations between tables by a common field. Tab. 18 shows the fields which take a different name during the simulation. Variable Name in Simulation Table Data base field VehBrand_N Policy VehBrand RiskArea_N Policy RiskArea Code_O Policy Code V1,…,V11 SinistersXYear 2000, …, 2010 PolicyID_S Sinisters PolicyID RiskArea_s Sinisters RiskArea Guarantees_s Sinisters Guarantees Code_S Sinisters Code ## CLIENT TABLE ## #LIBRARIES ##################### library(xts) library(zoo) library(CASdatasets) data(freMPL6) #PARAMETERS N<-26000 #FIELDS ClientID<-c(1:N) Gender<-freMPL6$Gender[ClientID] MariStat<-freMPL6$MariStat[ClientID] CSP<-freMPL6$SocioCateg[ClientID] actual<-as.Date('2011-01-01') 67 LicAge<-freMPL6$LicAge[ClientID] LicBeg<-actual-(LicAge*30) DrivAge<-freMPL6$DrivAge[ClientID] DrivBeg<-actual-(DrivAge*365) #DATAFRAME df_Client<-data.frame(ClientID, Gender, MariStat, CSP, LicBeg, DrivBeg) View(df_Client) #EXPORTATION library(foreign) write.table(df_Client, "C:/Users/jorcajo/Desktop/Tables/Client.txt", sep="\t", row.names=F) ## AUTO TABLE ## #LIBRARIES library(xts) library(zoo) library(CASdatasets) data(freMTPL2freq) #FIELDS set.seed(12342) autos<-sample(1:dim(freMTPL2freq)[1], N, replace=FALSE) VehBrand_N<- freMTPL2freq$VehBrand[autos] VehBrand<-levels(factor(VehBrand_N)) ###################### #Cluster power in few categories depending on the VehBrand. For each VehBrand, I do the power mean: #and select the means as the new levels for VehPow. From freMTPL2freq. #> levels(factor(freMTPL2freq$VehPow)) #[1] "4" "5" "6" "7" "8" "9" "10" "11" "12" "13" "14" "15" #> levels(factor(freMTPL2freq$VehBrand)) #[1] "1" "2" "3" "4" "5" "6" "10" "11" "12" "13" "14" ###################### l<-length(levels(factor(freMTPL2freq$VehBrand))) VehPow<-NULL for (i in 1:l){ pow<- freMTPL2freq$VehPow[which(freMTPL2freq$VehBrand==levels(factor(freMTPL2freq$VehBran d))[i])] VehPow[i]<-round(mean(pow),0) } ####################### 68 #> VehPow #[1] "6" "6" "6" "6" "6" "6" "9" "9" "7" "8" "7" ########################## VehType<-c("compact", "compact", "familiar", "compact", "terrain", "familiar", "sport", "sport", "terrain", "sport", "sport") #DATAFRAME df_Auto<-data.frame(VehBrand, VehPow, VehType) View(df_Auto) #EXPORTATION library(foreign) write.table(df_Auto, "C:/Users/jorcajo/Desktop/Tables/Auto.txt", sep="\t", row.names=F) ## REGION TABLE ## #FIELDS Code<-c(1:52) Region<-c("Alava", "Albacete", "Alicante", "Almeria", "Asturias", "Avila", "Badajoz", "Barcelona", "Burgos", "Caceres", "Cadiz", "Cantabria", "Castellon", "Ciudad Real", "Cordoba", "La Coruna", "Cuenca", "Gerona", "Granada", "Guadalajara", "Guipuzcoa", "Huelva", "Huesca", "Islas Baleares", "Jaen", "Leon", "Lerida", "Lugo", "Madrid", "Malaga", "Murcia", "Navarra", "Orense", "Palencia", "Palmas", "Pontevedra", "La Rioja", "Salamanca", "Segovia", "Sevilla", "Soria", "Tarragona", "Teruel", "Tenerife", "Toledo", "Valencia", "Valladolid", "Vizacaia", "Zamora", "Zaragoza", "Melilla", "Ceuta") Population<-c(319227, 402318, 1934127, 702819, 1081487, 172704, 693921, 5529099, 375657, 415446, 1243519, 593121, 604344, 530175, 805857, 1147124, 219138, 756810, 924550, 256461, 709607, 521968, 228361, 1113114, 670600, 529799, 442308, 351350, 6489680, 1625827, 1470069, 642051, 333257, 171668,1096980, 963511, 322955, 352986, 164169, 1928962, 95223, 811401, 144607,1029789, 707242, 2578719,534874, 1155772, 191612, 973325, 78476, 82376) #DATAFRAME df_Region<-data.frame(Code, Region, Population) View(df_Region) #EXPORTATION library(foreign) write.table(df_Region, "C:/Users/jorcajo/Desktop/Tables/Region.txt", sep="\t", row.names=F) 69 ## RISKAREA TABLE ## #FIELDS RiskArea_N<-freMPL6$RiskArea[ClientID] #Clusters RiskArea in 5 group depending on their frequency #> sort(table(freMPL6$RiskArea)) #1 13 12 2 3 4 8 5 11 9 6 10 7 #13 25 57 345 471 895 1236 1535 3022 3828 3864 4620 6089 ############################# for(i in 1:N){ if(RiskArea_N[i]==1 || RiskArea_N[i]==13 || RiskArea_N[i]==12 ){ RiskArea_N[i]<-1 } if(RiskArea_N[i]==2 || RiskArea_N[i]==3 || RiskArea_N[i]==4 ){ RiskArea_N[i]<-2 } if(RiskArea_N[i]==8 || RiskArea_N[i]==5 || RiskArea_N[i]==11 ){ RiskArea_N[i]<-3 } if(RiskArea_N[i]==9 || RiskArea_N[i]==6){ RiskArea_N[i]<-4 } if(RiskArea_N[i]==10 || RiskArea_N[i]==7 ){ RiskArea_N[i]<-5 } } RiskArea<-levels(factor(RiskArea_N)) #RISKAREADESC RiskAreadesc<-c("gold", "silver", "master", "plus", "regular") #DATAFRAME df_RiskArea<-data.frame(RiskArea, RiskAreadesc) View(df_RiskArea) #EXPORTATION library(foreign) write.table(df_RiskArea, "C:/Users/jorcajo/Desktop/Tables/RiskArea.txt", sep="\t", row.names=F) ## RISKAREAXGUARANTEES ## #FIELDS RiskArea_guarantees<-c(rep(1, 8), rep(2, 7), rep(3, 6), rep(4, 5), rep(5, 3)) g1<-c("windows", "travelling", "driver insurance", "claims", "fire", "theft", "total loss", " health assistance") g2<-c("windows", "travelling", "driver insurance", "claims", "fire", "theft", " health assistance") g3<-c("windows", "driver insurance", "claims", "fire", "theft", " health assistance") 70 g4<-c("windows", "driver insurance", "fire", "theft", " health assistance") g5<-c("windows", "driver insurance", " health assistance") Guarantees<-c(g1, g2, g3, g4, g5) #DATAFRAME df_RiskAreaXGuarantees<-data.frame(RiskArea_guarantees, Guarantees) View(df_RiskAreaXGuarantees) #EXPORTATION library(foreign) write.table(df_RiskAreaXGuarantees, "C:/Users/jorcajo/Desktop/Tables/RiskAreaXGuarantees.txt", sep="\t", row.names=F) ## POLICY TABLE ## #FIELDS PolicyID<-c(1:N) prob<-(Population/sum(Population)) #probabilities of each region depending on its population set.seed(12342) Code_O<-sample(c(1:52), N, replace=TRUE, prob=prob) set.seed(12342) inici <- as.Date('2000-1-1') fi <- as.Date('2010-12-31') dates <- as.Date(inici:fi, origin='1970-1-1') RecordBeg<-sample(dates, N, replace=T) RecordEnd<-rep(0, N) RecordEnd<-as.Date(RecordEnd, origin='1970-1-1') #Supposing that the 70% of the clients keep #their policy during the following 10 years for(i in 1:N){ x<-sample(c(0,1), 1, replace=TRUE, prob=c(0.7, 0.3)) #x=0 means that there is no an end date for the policy. if(x==0){ RecordEnd[i]<-"9999-01-01" } else{ #Supposing that policies keep, one year as minimum, in the company. inici<-RecordBeg[i]+365 dates <- as.Date(inici:fi, origin='1970-1-1') RecordEnd[i]<-sample(dates, 1) } } VehAge<-freMTPL2freq$VehAge[autos] actual<-as.Date('2011-01-01') VehBeg<-actual-(VehAge*365) BonusMalus<-freMPL6$BonusMalus[ClientID] 71 #DATAFRAME df_Policy<-data.frame(PolicyID, ClientID, RecordBeg, RecordEnd, VehBeg, VehBrand_N, BonusMalus, RiskArea_N, Code_O) View(df_Policy) #RiskArea_N is created in RiskArea script. #EXPORTATION library(foreign) write.table(df_Policy, "C:/Users/jorcajo/Desktop/Tables/Policy.txt", sep="\t", row.names=F) ## SINISTERS TABLE AND SINISTERXYEAR TABLE ## #LIBRARIES library("lubridate", lib.loc="C:/Program Files/R/R-3.1.1/library") inici <- as.Date('2000-1-1') fi <- as.Date('2010-12-31') dates <- as.Date(inici:fi, origin='1970-1-1') #The vector dates is modified in order to delete all the 29th February to prevent errors. dates_sinisters<-dates for(i in 1:length(dates_sinisters)){ if(day(dates_sinisters[i])==29 && month(dates_sinisters[i])==02){ dates_sinisters[i]<-dates_sinisters[i]-1 } } #PROBABILITIES OF SINISTER #every client has a probability of 0.2 to have a sinister, as minimum. p<-rep(0.2, N) #Some characteristics make this probability increases for ( i in 1:N){ if(DrivAge[i]<24){ p[i]<-p[i]+0.1 } if(LicAge[i]<12){ p[i]<-p[i]+0.2 } if(DrivAge[i]>50 && DrivAge[i]<65 && Gender[i]=="Male"){ p[i]<-p[i]+0.2 } if(DrivAge[i]>40 && DrivAge[i]<45 && Gender[i]=="Female"){ p[i]<-p[i]+0.2 } } #SINISTERDATE FIELD AND SINISTERXYEAR MATRIX #It spend 20 minutes 72 Sinisterdate<-as.Date('1970-1-1') SinistersXYear<-matrix(data=0, nrow=N, ncol=11) set.seed(12342) for( i in 1:N){ nsinisters<-rep(0,11) for( j in 1:11){ prob<-p[i] s<-NULL for(k in 1:3){ #A maximum of 3 accidents per year. s[k]<-sample(c(0,1), 1, replace=TRUE, prob=c((1-prob), prob)) if (s[k]!=0) { prob<-(prob-0.1) #After a happening a sinister, the probability to } #have a sinister decreases. } } if(sum(s)!=0){ Sinisterdate_2<-sample(dates_sinisters, sum(s), replace=FALSE) year(Sinisterdate_2)<-2000+j-1 Sinisterdate<-c(Sinisterdate, Sinisterdate_2) } nsinisters[j]<-sum(s) #total number of sinisters for policy i in year j } SinistersXYear[i, ]<-nsinisters if(sum(nsinisters)==0){ Sinisterdate<-c(Sinisterdate, as.Date('9999-01-01')) } } Sinisterdate<-Sinisterdate[2:length(Sinisterdate)] #Delete the first value of Sinisterdate #DATAFRAME SINISTERSXYEAR df_SinistersXYear<-data.frame(ClientID, SinistersXYear) View(df_SinistersXYear) #EXPORTATION SINISTERSXYEAR write.table(df_SinistersXYear, "C:/Users/jorcajo/Desktop/Tables/SinistersXYear.txt", sep="\t", row.names=F) #OTHER FIELDS #Code_S l<-length(Sinisterdate) Code_S<-rep(0, l) Code_O_sinisters<-rep(0, l) probs<-rep((1/52), 52) total<-rep(0,N) for (i in 1:N){ 73 total[i]<-sum(SinistersXYear[i, ]) } #Code_O_SINISTERS #Code_O_sinisters is needed to create Code_S j<-1 for( i in 1:N){ if(total[i]!=0){ for(k in 0:(total[i]-1)){ Code_O_sinisters[j+k]<-Code_O[i] } j<-j+k+1 } if(total[i]==0){ Code_O_sinisters[j]<-Code_O[i] j<-j+1 } } probs<-NULL #Code_S is equal to Code_O with a probability of 0.7 set.seed(12342) for( i in 1:l){ for( j in 1:52){ if(Code_O_sinisters[i]==Code[j]){ probs[1:(j-1)]<-(0.3/51) probs[j]<-0.7 probs[(j+1):52]<-(0.3/51) probs<-probs[1:52] Code_S[i]<-sample(Code, 1, prob=probs) } } } PolicyID_s<-rep(0,l) RiskArea_s<-rep(0, l) Guarantees_s<-rep(0, l) set.seed(12342) j<-1 for( i in 1:N){ if(total[i]!=0){ for(k in 0:(total[i]-1)){ PolicyID_s[j+k]<-PolicyID[i] RiskArea_s[j+k]<-RiskArea_N[i] if(Code_O_sinisters[j+k]!=25 && Code_O_sinisters[j+k]!=19 && Code_O_sinisters[j+k]!=15 && Code_O_sinisters[j+k]!=2 && Code_O_sinisters[j+k]!=30 && Code_O_sinisters[j+k]!=4 && month(Sinisterdate[j+k])%in%c(7,8,9)){ p<-probs-0.002 p[19]<-0.104 #19 is the position for "Granada" 80 FFA17 No limitations to display large amounts of data A. 1 4 2 8 100,00% 1 FFA18 Data refresh A 2 2 4 50,00% 1 Dashboards FFD1 Dashboards Exportation A 3 3 9 75,00% 1 71,43% 1 FFD2 Templates A 0 2 0 0,00% 0 FFD3 Free design A 4 2 8 100,00% 1 Reporting FFR1 Reports Exportation A 3 3 9 75,00% 1 42,86% 0 FFR2 Templates A 0 2 0 0,00% 0 FFR3 Free design A 1 2 2 25,00% 0 INTEROPERABILITY Languages FIL1 Languages displayed A. 1 4 2 8 100,00% 1 100,00% 1 75,00% 0 Portability FIP1 Operating Systems A. 1 0 2 0 0,00% 0 40,00% 0 FIP2 SaaS/Web A 1 1 1 25,00% 0 FIP3 Mobile A 3 2 6 75,00% 1 Using the project by third parts FIU1 Using the project by third parts A 3 2 6 75,00% 1 75,00% 1 Data exchage FID1 Exportation in txt A 3 2 6 75,00% 1 100,00% 1 FID2 Exportation in CSV A 3 2 6 75,00% 1 FID3 Exportation in HTML A 3 2 6 75,00% 1 FID4 Exportation in Excel file A 3 3 9 75,00% 1 SECURTIY Security devices FSS1 Password protection A 3 3 9 75,00% 1 100,00% 1 100,00% 1 FSS2 Permissions A 3 3 9 75,00% 1 USUABILITY EASE OF UNDERSTANDING AND LEARNING Learning time UEL1 Average learning time A. 2 4 3 12 100,00% 1 100,00% 1 100,00% 1 100,00% 1 Browsing facilities UEB1 Consistency between icons in the toolbars and their actions A 3 3 9 75,00% 1 100,00% 1 UEB2 Displaying right click menus A 3 3 9 75,00% 1 Terminology UET1 Ease of understanding the terminology A 4 3 12 100,00% 1 100,00% 1 Help and documentation UEH1 User guide quality A. 2 3 2 6 75,00% 1 100,00% 1 UEH2 User guide adquisition A 3 2 6 75,00% 1 UEH3 On-line documentation A 3 2 6 75,00% 1 Support training UES1 Availability of tailor-made training courses A 3 2 6 75,00% 1 100,00% 1 UES2 Phone technical support A 3 2 6 75,00% 1 UES3 On-line support A 3 2 6 75,00% 1 UES4 Availability of consulting services A 3 2 6 75,00% 1 UES5 Free formation A 3 2 6 75,00% 1 UES6 Community A 3 2 6 75,00% 1 GRAPHICAL INTERFACE CHARACTERISTIC Windows and mouse interface UGW1 Editing elements by double-clicking A 0 2 0 0,00% 0 50,00% 1 100,00% 1 UGW2 Dragging and dropping elements A 3 2 6 75,00% 1 Display UGD1 Editing the screen layout A 3 2 6 75,00% 1 75,00% 1 OPERABILITY Versatility UOV1 Automatic update A 2 2 4 50,00% 1 50,00% 1 100,00% 1 EFFICIENCY EXECUTION PERFORMANCE Compilation speed EEC1 Compilation Speed A. 2 4 2 8 100,00% 1 100,00% 1 100,00% 1 100,00% 1 Resource utilization EER1 CPU(processor type) A. 1 4 2 8 100,00% 1 100,00% 1 EER2 Minimum RAM A. 2 3 2 6 75,00% 1 EER3 Hard disk space required A. 2 4 2 8 100,00% 1 Software requirements EES1 Additional software requirements A 4 2 8 100,00% 1 100,00% 1 81 SAP Lumira CATEGORY CHARACTERISTIC SUBCHARACTERIST IC-DESC METRICCODE METRIC M .S VAL UE WEI GHT COMPENSE D VALUE NORMAL. VALUE INDICATOR_ METRIC TOTAL SUBCHARACTERISTIC INDICATOR_SUB -CHARAC. TOTAL CHARACTERIST IC INDICATOR_ CHARAC TOTAL CATEGORY INDICATOR_ CATEG. FUNCTIONALIT Y FIT TO PURPOSE Data loading FFI1 Direct connection to data sources A 2 2 4 50,00% 1 100,00% 1 83,33% 1 66,67% 0 FFI2 BigData sources A 2 1 2 50,00% 1 FFI3 Apache Hadoop A 2 1 2 50,00% 1 FFI4 Microsoft Access A 2 2 4 50,00% 1 FFI5 Excel files A 3 3 9 75,00% 1 FFI6 From an excel file, import all sheets at the same time A 3 2 6 75,00% 1 FFI7 Cross-tabs A 4 2 8 100,00% 1 FFI8 Plain text A 3 3 9 75,00% 1 FFI9 Connecting to different data sources at the same time A 3 2 6 75,00% 1 FFI10 Easy integration of many data sources A 3 2 6 75,00% 1 FFI11 Visualizing data before the loading A 3 2 6 75,00% 1 FFI12 Determining data format A 3 2 6 75,00% 1 FFI13 Determining data type A 3 2 6 75,00% 1 FFI14 Allowing column filtering before the loading A 3 2 6 75,00% 1 FFI15 Allowing row filtering before the loading A 3 2 6 75,00% 1 FFI16 Automatic measures creation A 3 3 9 75,00% 1 FFI17 Allow renaming datasets A 3 2 6 75,00% 1 FFI18 Allow renaming fields A 3 3 9 75,00% 1 FFI19 Data cleansing A 3 2 6 75,00% 1 Data model FFD1 Data model is done automatically A 0 2 0 0,00% 0 42,86% 0 FFD2 The done data model is the correct one A 1 2 2 25,00% 0 FFD3 Data model can be visualized A 3 3 9 75,00% 1 Field relations FFF1 Alerting about circular references A 4 3 12 100,00% 1 100,00% 1 FFF2 Skiping with circular references A 4 3 12 100,00% 1 FFF3 A same table can be used several times A 3 2 6 75,00% 1 Analysis FFA1 Creating new measures based on previous measures A 3 3 9 75,00% 1 68,89% 1 FFA2 Creating new measures based on dimensions A 3 3 9 75,00% 1 FFA3 Variety of functions A 3 3 9 75,00% 1 FFA4 Descriptive statistics A 3 2 6 75,00% 1 FFA5 Preduction functions A 3 2 6 75,00% 1 FFA6 R connection A 2 2 4 50,00% 1 FFA7 Geographic information A 3 2 6 75,00% 1 FFA8 Time hierarchy A 3 3 9 75,00% 1 FFA9 Creating sets of data A 0 2 0 0,00% 0 FFA10 Filtering data by expression A 2 3 6 50,00% 1 FFA11 Filtering data by dimension A 1 3 3 25,00% 0 FFA12 Visual Perspective Linking A 0 2 0 0,00% 0 FFA13 No Null data specifications A. 1 0 2 0 0,00% 0 FFA14 Considering nulls A 4 3 12 100,00% 1 FFA15 Variety of graphs A 4 3 12 100,00% 1 FFA16 Modify graphs A 1 3 3 25,00% 0 82 FFA17 No limitations to display large amounts of data A. 1 0 2 0 0,00% 0 FFA18 Data refresh A 2 2 4 50,00% 1 Dashboards FFD1 Dashboards Exportation A 3 3 9 75,00% 1 71,43% 1 FFD2 Templates A 0 2 0 0,00% 0 FFD3 Free design A 3 2 6 75,00% 1 Reporting FFR1 Reports Exportation A 3 3 9 75,00% 1 100,00% 1 FFR2 Templates A 3 2 6 75,00% 1 FFR3 Free design A 3 2 6 75,00% 1 INTEROPERABILITY Languages FIL1 Languages displayed A. 1 4 2 8 100,00% 1 100,00% 1 75,00% 0 Portability FIP1 Operating Systems A. 1 0 2 0 0,00% 0 20,00% 0 FIP2 SaaS/Web A 3 1 3 75,00% 1 FIP3 Mobile A 1 2 2 25,00% 0 Using the project by third parts FIU1 Using the project by third parts A 3 2 6 75,00% 1 75,00% 1 Data exchage FID1 Exportation in txt A 0 2 0 0,00% 0 55,56% 1 FID2 Exportation in CSV A 3 2 6 75,00% 1 FID3 Exportation in HTML A 0 2 0 0,00% 0 FID4 Exportation in Excel file A 3 3 9 75,00% 1 SECURTIY Security devices FSS1 Password protection A 3 3 9 75,00% 1 100,00% 1 100,00% 1 FSS2 Permissions A 3 3 9 75,00% 1 USUABILITY EASE OF UNDERSTANDING AND LEARNING Learning time UEL1 Average learning time A. 2 3 3 9 75,00% 1 75,00% 1 100,00% 1 100,00% 1 Browsing facilities UEB1 Consistency between icons in the toolbars and their actions A 4 3 12 100,00% 1 50,00% 1 UEB2 Displaying right click menus A 0 3 0 0,00% 0 Terminology UET1 Ease of understanding the terminology A 4 3 12 100,00% 1 100,00% 1 Help and documentation UEH1 User guide quality A. 2 3 2 6 75,00% 1 100,00% 1 UEH2 User guide adquisition A 3 2 6 75,00% 1 UEH3 On-line documentation A 3 2 6 75,00% 1 Support training UES1 Availability of tailor-made training courses A 0 2 0 0,00% 0 66,67% 1 UES2 Phone technical support A 3 2 6 75,00% 1 UES3 On-line support A 3 2 6 75,00% 1 UES4 Availability of consulting services A 0 2 0 0,00% 0 UES5 Free formation A 3 2 6 75,00% 1 UES6 Community A 3 2 6 75,00% 1 GRAPHICAL INTERFACE CHARACTERISTIC Windows and mouse interface UGW1 Editing elements by double-clicking A 0 2 0 0,00% 0 50,00% 1 100,00% 1 UGW2 Dragging and dropping elements A 3 2 6 75,00% 1 Display UGD1 Editing the screen layout A 3 2 6 75,00% 1 75,00% 1 OPERABILITY Versatility UOV1 Automatic update A 3 2 6 75,00% 1 75,00% 1 100,00% 1 EFFICIENCY EXECUTION PERFORMANCE Compilation speed EEC1 Compilation Speed A. 2 3 2 6 75,00% 1 75,00% 1 66,67% 0 0,00% 0 Resource utilization EER1 CPU(processor type) A. 1 0 2 0 0,00% 0 33,33% 0 EER2 Minimum RAM A. 2 3 2 6 75,00% 1 EER3 Hard disk space required A. 2 1 2 2 25,00% 0 Software requirements EES1 Additional software requirements A 4 2 8 100,00% 1 100,00% 1 83 Tableau CATEGORY CHARACTERISTIC SUBCHARACTERIST IC-DESC METRICCODE METRIC M .S VAL UE WEI GHT COMPENSE D VALUE NORMAL. VALUE INDICATOR_ METRIC TOTAL SUBCHARACTERISTIC INDICATOR_SUB -CHARAC. TOTAL CHARACTERIST IC INDICATOR_ CHARAC TOTAL CATEGORY INDICATOR_ CATEG. FUNCTIONALIT Y FIT TO PURPOSE Data loading FFI1 Direct connection to data sources A 3 2 6 75,00% 1 90,00% 1 100,00% 1 100,00% 1 FFI2 BigData sources A 3 1 3 75,00% 1 FFI3 Apache Hadoop A 3 1 3 75,00% 1 FFI4 Microsoft Access A 3 2 6 75,00% 1 FFI5 Excel files A 3 3 9 75,00% 1 FFI6 From an excel file, import all sheets at the same time A 3 2 6 75,00% 1 FFI7 Cross-tabs A 2 2 4 50,00% 1 FFI8 Plain text A 3 3 9 75,00% 1 FFI9 Connecting to different data sources at the same time A 3 2 6 75,00% 1 FFI10 Easy integration of many data sources A 3 2 6 75,00% 1 FFI11 Visualizing data before the loading A 3 2 6 75,00% 1 FFI12 Determining data format A 3 2 6 75,00% 1 FFI13 Determining data type A 3 2 6 75,00% 1 FFI14 Allowing column filtering before the loading A 3 2 6 75,00% 1 FFI15 Allowing row filtering before the loading A 0 2 0 0,00% 0 FFI16 Automatic measures creation A 3 3 9 75,00% 1 FFI17 Allow renaming datasets A 3 2 6 75,00% 1 FFI18 Allow renaming fields A 3 3 9 75,00% 1 FFI19 Data cleansing A 0 2 0 0,00% 0 Data model FFD1 Data model is done automatically A 4 2 8 100,00% 1 100,00% 1 FFD2 The done data model is the correct one A 3 2 6 75,00% 1 FFD3 Data model can be visualized A 3 3 9 75,00% 1 Field relations FFF1 Alerting about circular references A 3 3 9 75,00% 1 62,50% 1 FFF2 Skiping with circular references A 0 3 0 0,00% 0 FFF3 A same table can be used several times A 3 2 6 75,00% 1 Analysis FFA1 Creating new measures based on previous measures A 3 3 9 75,00% 1 79,55% 1 FFA2 Creating new measures based on dimensions A 3 3 9 75,00% 1 FFA3 Variety of functions A 3 3 9 75,00% 1 FFA4 Descriptive statistics A 3 2 6 75,00% 1 FFA5 Preduction functions A 3 2 6 75,00% 1 FFA6 R connection A 3 2 6 75,00% 1 FFA7 Geographic information A 3 2 6 75,00% 1 FFA8 Time hierarchy A 3 3 9 75,00% 1 FFA9 Creating sets of data A 3 2 0,00% 0 FFA10 Filtering data by expression A 3 3 9 75,00% 1 FFA11 Filtering data by dimension A 3 2 6 75,00% 1 FFA12 Visual Perspective Linking A 0 2 0 0,00% 0 FFA13 No Null data specifications A. 1 0 2 0 0,00% 0 FFA14 Considering nulls A 0 3 0 0,00% 0 FFA15 Variety of graphs A 3 3 9 75,00% 1 FFA16 Modify graphs A 4 3 12 100,00% 1 84 FFA17 No limitations to display large amounts of data A. 1 3 2 6 75,00% 1 FFA18 Data refresh A 4 2 8 100,00% 1 Dashboards FFD1 Dashboards Exportation A 3 3 9 75,00% 1 71,43% 1 FFD2 Templates A 0 2 0 0,00% 0 FFD3 Free design A 3 2 6 75,00% 1 Reporting FFR1 Reports Exportation A 3 3 9 75,00% 1 71,43% 1 FFR2 Templates A 0 2 0 0,00% 0 FFR3 Free design A 3 2 6 75,00% 1 INTEROPERABILITY Languages FIL1 Languages displayed A. 1 4 2 8 100,00% 1 100,00% 1 100,00% 1 Portability FIP1 Operating Systems A. 1 4 2 8 100,00% 1 60,00% 1 FIP2 SaaS/Web A 3 1 3 75,00% 1 FIP3 Mobile A 1 2 2 25,00% 0 Using the project by third parts FIU1 Using the project by third parts A 3 2 6 75,00% 1 75,00% 1 Data exchage FID1 Exportation in txt A 3 2 6 75,00% 1 77,78% 1 FID2 Exportation in CSV A 3 2 6 75,00% 1 FID3 Exportation in HTML A 0 2 0 0,00% 0 FID4 Exportation in Excel file A 3 3 9 75,00% 1 SECURTIY Security devices FSS1 Password protection A 3 3 9 75,00% 1 100,00% 1 100,00% 1 FSS2 Permissions A 3 3 9 75,00% 1 USUABILITY EASE OF UNDERSTANDING AND LEARNING Learning time UEL1 Average learning time A. 2 1 3 3 25,00% 0 25,00% 0 80,00% 1 100,00% 1 Browsing facilities UEB1 Consistency between icons in the toolbars and their actions A 3 3 9 75,00% 1 100,00% 1 UEB2 Displaying right click menus A 3 3 9 75,00% 1 Terminology UET1 Ease of understanding the terminology A 3 3 9 75,00% 1 75,00% 1 Help and documentation UEH1 User guide quality A. 2 3 2 6 75,00% 1 100,00% 1 UEH2 User guide adquisition A 3 2 6 75,00% 1 UEH3 On-line documentation A 3 2 6 75,00% 1 Support training UES1 Availability of tailor-made training courses A 3 2 6 75,00% 1 83,33% 1 UES2 Phone technical support A 1 2 2 25,00% 0 UES3 On-line support A 3 2 6 75,00% 1 UES4 Availability of consulting services A 3 2 6 75,00% 1 UES5 Free formation A 4 2 8 100,00% 1 UES6 Community A 3 2 6 75,00% 1 GRAPHICAL INTERFACE CHARACTERISTIC Windows and mouse interface UGW1 Editing elements by double-clicking A 3 2 6 75,00% 1 100,00% 1 100,00% 1 UGW2 Dragging and dropping elements A 3 2 6 75,00% 1 Display UGD1 Editing the screen layout A 3 2 6 75,00% 1 75,00% 1 OPERABILITY Versatility UOV1 Automatic update A 2 2 4 50,00% 1 50,00% 1 100,00% 1 EFFICIENCY EXECUTION PERFORMANCE Compilation speed EEC1 Compilation Speed A. 2 2 2 4 50,00% 1 50,00% 1 100,00% 1 100,00% 1 Resource utilization EER1 CPU(processor type) A. 1 4 2 8 100,00% 1 100,00% 1 EER2 Minimum RAM A. 2 4 2 8 100,00% 1 EER3 Hard disk space required A. 2 3 2 6 75,00% 1 Software requirements EES1 Additional software requirements A 4 2 8 100,00% 1 100,00% 1 85 Annex 3:QlickView evaluation This chapter explains how does QlikView meet (or not) the metrics evaluated in this project. Metrics are grouped in sub-characteristics, and metric’s codes appear in the text when they are mentioned. Data loading: User can extract data from files (table files, data files and web files) (FFI8) or can connect to databases by ODBC (Open Database Conectivity) and OLEDB (Object Linking and Embedding Database). Examples of connections are Oracle, Microsoft Access (FFI4) or Microsoft SQL Server. Moreover, it can also be connected to BigData sources like Teradata(FFI2). QlikView has not integrated connectors (ODBC or OLEDB) to database (FFI1), but QVSource, which is a Qlik’s partner, offers a variety of API connectors for QlikView. These API connectors allow the connection to different social and business APIs without requiring any technical knowledge. Some examples are Twitter, Facebook, Google Analytics, Google Docs/Calendar, Mashape...Additionally, QVSource also offers developing connectors for other sources that may not have an ODBC driver, such as NoSQL type databases as MongoDB or Hadoop (FFI3). The loading data is done by the Editor Script, where user has to write a specific code in SQLlike language. There also exist the option to click on tabs and the code is written on the Editor Script by the machine, and finally, user just executes the code in order to load data. Additionally, user can load data, easily, from spreadsheets (e.g Excel files) by an assistance (Wizard Assistance (FFI5). Unfortunately, if user wants to import data from more than one sheet in the same file, he must repeat the same process as many time as there are sheets. But, if user knows SQL code, it is advisable to type code in the Editor Script instead of repeat the same browsing through menus process many times (FFI6). Usually, in excel data sources there are cross-tabs and QlikView has the option to import them from excel files. During the loading, user indicates if a table is a cross tab and he can establish the parameters of the cross tab and change their names (FFI7). On the other hand, connecting to multiple data sources is possible, just repeating the same process for each different connection (FFI9). QlikView can combine data from many different data sources with high performance, regardless of how these data sources work on their own. Tables from wherever data source will be charged in the memory of QlikView as simply datasets. Therefore, the integration of many datasources become the integration of different datasets (FFI10). With the Editor Script, user can clean and prepare data for the loading. For example, user can create new calculated fields, rename fields (FF117), filter data and columns (FFI14) (FFI15), assign name to the dataset (FFI18) ... As the loading data is based on the written code, user can also insert data manually. User can do almost all these functions typing code or by menus, interchangeably,because the Editor Script also offers menus to do almost all functions. But, for example, filtering can not be done by menus and user must to type code to get this. QlikView does not assign a data type to fields (dimension or measure), only when fields are displayed in charts, they take the names of dimensions or expressions, otherwise they are called just fields (FFI12). On the other hand, the data format can be agreed, by user, before loading the data. On the top of the script there are the default settings for the data format and user can 86 modify them. For example, the following sentence can be written on the top of the script: SET DateFormat='DD/MM/YYYY'; It means, that every data with the following format: DD/MM/YYYY will be interpreted as a date by QlickView. Therefore, data formats can be changed by user typing the corresponding code during the loading (FFI13). Whether user loads data from files, data are showed before the loading. However, when data are loaded from data bases they are not showed (FFI11). Finally, QlikView does not create automatically any measure from fields as other tools do (FFI16). With knowledge of SQL language user has more flexibility and gain more speed during the processes, but not knowing SQL language is not an impediment to use Qlikview. However, there exist an option “Syntax check” that marks the code that is not right and it can be useful to learn and improve SQL language. Data model: QlikView creates automatically the data model taken as reference the names of the fields. In fact, two tables are related if there exist two fields, one in each one, with same name(case sensitive) and matched values (FFD1). Therefore, during the loading process is important to pay attention to field names in order to get the right model. Automatic modeling is an advantage because it saves time, but sometimes user can visualize the model and realize that it is not the desired. In that case, user has the alternative of modifying the loading script in order to get the desired model (FFD2). The visualization of the data model is key to understand the relations between fields and with QlikView user can visualize the data model every time (FFD3). Field relations: Unlikely, because of the automatic relation by name, there can appears circular references and QlikView doesn’t support them. A circular loop appears when ‘there are two ways to get the same field by two different tables’. As a response of the circular reference, QlikView alerts about that (FFF1) and disconnects one of the tables, in fact it disconnect the biggest one, in order to display data(FFF2). User can realize the disconecction, visualizing the data model. Circular references can be repaired duplicating tables, but with QlikView, it implies to load the table one time more in memory (FFF3). Analysis: Once time data are loaded, user can create calculated fields, to display in different objects as list boxes, statistics boxes, multi boxes, table boxes and charts... User can also create new fields in the Editor Script after the loading, executing only the part of the script corresponding to the creation of the new field. These fields are considered as loaded fields. Morover, in sheet objects user can create calculated expressions and/or dimensions from loaded fields but they can only be used in the respective sheet object. It means, they are not considered fields (FFA1) (FFA2). QlikView offers a variety of functions to create new fields/expressions from all type of imported fields and they are classified in: Aggregation, Color, Conditional, Counter functions, Date and Time, Exponential and Logarithmic, Financial, Formatting, General Numeric, Inter- 87 record, Logical, Mapping, Mathematical constants and Parameter Free Functions, None, Null, Number interpretation, Range, Ranking, String, System and Trigonometric and Hyperbolic (FFA3). With these functions a descripitive analysis can be done (FFA4), but unfortunately it does not offer predictive functions (FFA5). Anyway, QlikView can be connected to R project, which is an open source programming language and software environment for statistical computing and graphics. R has its own language and for this reason user who wants to use its functions must know it. The integration of R in QlikView is not very popular yet, and for this reason, there is not much information on the net and either on the website of QlikView (FFA6). QlikView is famous because of its Visual Perspective Linking. When a value or several values (in a field) are selected, QlikView makes a split second association showing only values (in other fields) associated with the current selection. Simultaneously, sheet objects (holding one or several general expressions), are calculated to show the result of the current selection. For example, there exist interaction between charts when user select some values in a chart automatically another chart will only show values associated with the selection. This fact eases discovery relations between fields and it is key in data discovery science (FFA12). On the other hand, creating new data sets is useful to analyze directly particular samples in the same workbook, but QlikView has not the option to do create them. Similarly, QlikView analizes particular datasets using its visual perspective linking (FFA9). Due to the same reason, QlikView have not got filters. User filters data using the interaction between sheet objects (FFA10)(FFA11). Moreover, QlikView also offers the option to lock sheet objects in order to not being modified due the interaction. QlikView offers a corporative complement, GeoQlik, which is a GIS component for GeoBusiness Intelligence within QlikView. It offers normalized GIS formats as ShapeFile, PostGIS, Oracle Spatial, Oracle Locator, Esri spatial databases, virtual globes Google Maps, OpenStreetMap, all kinds of geometries, rasters and Web services and also .csv files with the coordinates. It gives much power to QlikView in Geo-Business Intelligence, but it is a component and it is not integrated in QlikView versions (FFA7). On the other hand, user can create expressions or fields based on time functions. For example, user can use the year function as Year(sinister_date), when sinisterdate has the corresponding date format. Some tools create some of the fields Year, Month and Quarter of a date, automatically based on date. But it is not the case of QlikView, in which user must create them by himself (FFA8). QlikView distinguish between nulls and empty spaces. When data comes from a database and there are nulls, they traspass automatically to QlikView as nulls. But when data comes from files, white spaces are considered missing values and not nulls. Calculation are made although some operands or function parameters are null or missing values. In charts, missing values are considered as other values, while null values are special and user can decide to show them in a chart or not. User can transform missing values to null values by functions in QlikView Editor (FFA14). Then, the only requisite to treat null values is that when they are coming from files, they must be a white space (FFA13). In order to display data, QlikView offers a variety of objects, they are: List box, Statistic box, Multibox, Table box, Chart, Input Box, Current Selections Box, Button, Text Objects, Line/Arrow objects, Slider/Calendar objects, Bookmark object, Search object, Container and Custom object. And particularly, the charts are: Bar chart, Line chart, Radar chart, Gauge chart, Mekko chart, Scatter chart, Grid chart, Pie chart, Funnel chart and Block chart. It is not the tool which offers more distinc charts, but it has a good selection (FFA15). Charts offer a variety of customizing settings (Dimension limits, Sort, Style, Presentation, Axes, Colors, Number format, 88 Font, Layout and Caption) and user can modify them whenever he wants. It is the tool of the evaluated which offers more flexibility in the design of charts (FFA16). Although, it is not the tool with a more variety of different graphs, thanks to the graphs settings, user can get similar graphs to graphs done with other tools. For example, QlikView does not offer a Heat map but it offers a Block Chart. The difference between them, is that in a Heat map two expressions can be displayed in addition to a dimension. While with Block chart only one expression and one dimension can be displayed. Heat maps can relates the color of the blocks on a expression and the size of the blocks to another expression. By this way, two expressions can be showed in a block chart, and user can realize if there exist any relation between them or not. Basically he can visualize if there exist any pattern related with both expressions. The following image is an example of a heat map where blocks represents the values of the field Region_sinister, the size of blocks is based on the population of each region, and the color of the blocks is based on the number of sinisters happened there. Fig. 25 Heat Map chart built in MicroStrategy With QlikView’s block chart, color cannot be represented by an expression. Only the size can represent an expression and in this particular example the expression is the population: 89 Fig. 26 Block chart built in QlikView Even so, there exist an alternative to relate color with expressions. Each built expression in a chart has a backgroud color and user can set it to fix a color pattern for the values of the expression; by default this expression is empty. It requires type code and in my opinion it is difficult because there are not many information on the userguide about that. However, the option to set the background color is very useful and interesting, although implementing that can be not trivial. The following function is an exemple of how user can set the color of an expression. In particular, colors are set depending on the fractiles 0.2, 0.40, 0.60, 0.80 and 0.90 of the amount of sinisters in each region. It corresponds to the Background color for the population expression, which is the expression visualized in the chart. For example: If([NumericCount (SinisterID)]<=1574.8, rgb(255,204, 204), If([NumericCount (SinisterID)]>=1574.8 and [NumericCount (SinisterID)]<2082.4, rgb(255,153,152), If([NumericCount (SinisterID)]>=2082.4 and [NumericCount (SinisterID)]<2630, rgb(255,102,102), If([NumericCount (SinisterID)]>=2630 and [NumericCount (SinisterID)]<3532.6, rgb(255,51,51), If([NumericCount (SinisterID)]>=3532.6 and [NumericCount (SinisterID)]<7134.4, rgb(255,0,0), If([NumericCount (SinisterID)]>=7134.4, rgb(204,0,0) )))))) And the result is: 96 Moreover, user can create calculated measures and calculated dimensions by the formula editor script. There, two fields can be combined to create a new one. User can apply functions from a predefined set of numeric, date and text functions. Using also If...Then...Else clauses, called logical functions and a calendar picker for date parameters. SAP Lumira is not the application with more functions, but they are enough to do descriptive statistics (FFA3) (FFA4). SAP Lumira offers also predictive calculations. By choosing the down arrow on a measure displayed with a date range user is able to choose a Forecast or Linear Regression Predictive Calculation type to add to the visualization along with specifying how many periods forward user wanted to predict (FFA5). Moreover, there is also another snap-in product for SAP Lumira called SAP Predictive Analysis which can adds predictive features into SAP Lumira. SAP Predictive Analysis is far more robust with regards to predictive features than the offered in SAP Lumira and moreover SAP Predictive Analysis supports the use of predictive algorithms from open source R, unlike SAP Lumira (FFA6). SAP Lumira offers a a plethora of data visualization types. Moreover, in the user guide they are very well classified depending on the type of analysis the user want to do. It is one of the keys of SAP Lumira, the variety of graphs (FFA15).  Comparison: Column Chart, Column Chart with 2 Y-Axes, 3D Column Chart, Radar Chart, Area Chart, Tag Cloud, Heat Map, Table.  Percentage: Pie chart, Donut chart, Pie with depth chart, Stacked Column Chart, Tree, Funnel Chart.  Correlation: Scatter plot, Scatter Matrix Chart, Bubble Chart, Network Chart, Numeric Point, Tree.  Trend: Line chart, Line Chart with 2 Y-Axes, Combined Line chart, Combined Line chart with 2 Y-Axes, Waterfall Chart, Box Plot, Parallel Coordinates Chart.  Geographical: Geo Bubble Chart, Geo Choropleth Chart, Geo Pie Chart, Geo Map. But, to create a Geo Map, user must have an Esri ARcGis Online Account. There exist Trellis to display a particular chart for each value of an additional dimension. For example, if user creates a bar chart that compares revenue by region, and then he adds country to the trellis, multiple charts will appear. Each cart will display the revenue by region for one country. In other tools is also possible but is not as comfortable to do. User can always change the color of a legend, but when the measure showed in the legend is continuous, there is no the possibility to change the rank limits for each color. Then, although there is much variety between data, user can not realize it. In fact, the data rank is always divided in 5 equal portions and for each portion a color is assigned. User can change this color but not the rank limits. An example is the following heat map, in where it is not possible to realize about a pattern, which in other tools can be visualized: 97 It can be considered an important con when user is doing an analysis. SAP Lumira is one of the tools with more distinct graphs to display data. But they can not be very customized by user (FFA16). There exist an icon to refresh data. If there is any change in the data source, user can refreshes data by it, because it is not done automatically (FFA18) . User can create a time hierarchy (Year level, Quarter level, Month level and Day level) from a date field just in one click. Also geographical and personalized hierarchies can be built in order to be ploted in maps (FFA7) (FFA8). User can filter data in a visualization filtering by any dimension (FFA11), selecting data points in a chart to filter or exclude them, and displaying only the top or the bottom ranking data points for dimension or measure. But, for example, there is no the possibility to filter by a specific requirement of a measure by a formula or for a specific value of a dimension (FFA10). Other tools offer much better filtering capabilities. User can interact with a chart filtering data by the legend or selecting data points. But a filter done in a chart does not interact with other charts. It means, the interactions is in a chart and not between charts (FFA12). And moreover, there is not exist the possibility to create data sets (FFA9). Null data are considered, by default, as a one more value of the field. Null values does not intervene in calculated measures. User can choose if the null value is represented in a visualization or not (FFA14). There is not any problem with null data. When data is loaded from a file, null data must be represented as an empty space (FFA13). Moreover, usually SAP Lumira shows the advice: “There are too many data points to visualize (maximum is 10.000) Filter the data to reduce the number of data points”. It is a big con when user is displaying many data. Although, it is a default parameter and user can increase it, also considering increasing the virtual memory allocated to SAP Lumira. However, there exists a option to solve the problem, but in my opinion the default maximum number of data points is much low for this type of tool. It totally pales in comparison to Tableau that can render 60 million points (FFA17). 98 Dashboards: Dashboards can be exported and share with other users (FFD1). There are not templates integrated in the application (FFD2). Moreover, user can design himself dashboards with totally freedom(FFD3). Reporting: Reports can be exported to SAP Lumira Cloud, SAP Lumira Server and SAP Business Objects BI platform. Moreover, they cam be exported to pdf file (FFR1). There are templates that fix only the distribution of the charts but user has totally free to customize them (FFR2). There are different options to report the results. Boards pages where user can add interactive charts, filter and input controls, Infographic where user can add chart visual properties, pictograms, shapes, images and text and Report pages where user can add interactive charts, sections and input controls. Input controls, let user filter data in a comfortable way. When user is building the report, and he has choose a board or a report page, he can select a dimension, and in the board there will appear a input control with all the values of the selected dimension and user can filter by them (FFR3). Languages: SAP Lumira is displayed in several languages: Deutsch, English, Spanish, French, Hungarian, Polish, Portuguese, Japanese and simplified Chinese (FFL1). Portability: SAP Lumira only works in Windows 7/8 with 64 or 32-bit, Windows Server 2008 with 64 bit and Windows 2012 operative systems (FIP1). SAP Lumira Cloud is the cloud computing SaaS of SAP and it is free. But SAP Lumira Cloud has some limitations, for example, predictive charts are not supported (FIP2). SAP Lumira Cloud can be also accessed from any mobile device supporting HTML5, but there is not any particular mobile application for SAP Lumira. There exist the free mobile application SAP Business Object Explorer connected to the SAP Business Object Explorer that can visualize documents built in firstly in SAP Lumira and then published in SAP Business Object Explorer (FIP2). Data exchange and Using the project by third parts: User can share data, charts and the full project. Datasets can be exported to .csv or Microsoft Excel file and published to SAP HANA (FID1) (FID4). Data cannot be exported in .txt (FID2) files or HTML files (FID4). Charts can be shared via email and printing or saving it in PDF, but they can’t be saved in Excel files. Moreover, user can also publish projects, including data and charts to an SAP StreamWork activity to share with the community, also publishing in SAP Lumira Cloud, SAP Lumira Server to share with colleagues and publishing in SAP business Objects Intelligence platform. 99 Security devices: In SAP Lumira Desktop Standard Edition password is not requested to open a locally saved document (Note: In SAP Lumira Desktop Standard Edition only the creator of a document can open it again). If the document is in SAP Lumira Cloud, a user name and a password is required. By the way, it would be useful the possibility to require a password in the Desktop Standard Edition. Moreover, with SAP Lumira Server, an administrator can assign passwords (FSS1) to projects and permissions to users (FSS2). Ease of understanding and learning: It is the second tool evaluated in this thesis, which requires a shorter average learning time(UEL1). Addtionally, SAP Lumira is very intuitive and its icons and tabs are very consistency an let user easily learn the management. It is the tool with the easiest interface (UEB1). Right cliclking does not display any menu (UEB2). Its terminology is agree with the BI terminology and it is not complicated (UET1). There are many tutorials of different topics in web of SAP Lumira, not only a user guide. There are tutorials of more advanced actions. All are understandable and actions are explained step by step. There are also video tutorials in the web (UES1) (UES2)(UES3). In the web of SAP Lumira, there is no information about tailor-made training-courses in the web. SAP Lumira offers a phone technical support but its schedule is not showed. But, user can also ask questions in the web to an expertise. This option is available at full time (UES1) (UES2) (UES3). SAP Lumira also offers in its web consulting solutions, classified in Sales Management with Sales Team Performance and Sales Quota & Comission, Marketing with Campaign Analysis and Segment Analysis, Financial Plannig with Financial Statement Analysis and Corporate Project Monitoring and finally Human Resources with SuccessFAcots Compensation Analysis (UES4). It offers free webinars and free video and interactive tutorials in the web of SAP Lumira as free formation. Morover, user can register to events by free (UES5). And there is also a coumminity (UES6). Graphical interface: User cannot edit elements by double-clicking (UGW1), but dragging and dropping method can be used (UGW2). Screen layout can be modified by the user (UGD1). Operability: SAP Lumira can check automatically dayly, weekly, monthly for updates connecting by itself to SAP Public Portal or SAP Pulic Support (UOV1). 100 Execution performance: Its compliation speed can be considered fast. Server Edition requires 3.7 GB of disk space (EER3) and 4 GB of RAM (EER2) .And it can only be installed in x64 bit processor (EER1). 101 Annex 5: MicroStrategy Analytics evaluation Data loading: Data can be imported from a file (*.xls, *.xlsx, *.txt , *.csv) (FFI8) (FFI5) of 200MB as maximum, from a database or using a database query. For *.xls and *.xlsx files, multiple worksheets can be included in the file, but only one worksheet can be uploaded at a time (FFI6). And crosstabs can be also uploaded but they must be in a particular format (FFI7). Moreover, data can be also loaded by a connection to databases. In order to load data from a database the connection is done by ODBC. Some data sources can be directly connected to Analyitcs because it already has several particular ODBC integrated (FFI1). Some examples are Amazon EMR Cloud, Apache Hadoop (FFI3), Cloudera CDH, Cloudera Impala, Greenplum, Hortonworks HDP, IBM DB2, IBM Netezza, Microsoft Acces, Microsoft SQL Database, Microsoft SQL Server, Oracle, PostfreSQL, Web services data sources…(FFI4) Other data sources can be connected but an ODBC previously installed is needed to establish the connection between SAP Lumira and the database. Some examples are Mongo, My sql Community and Enterprise, SAP CDBMS, SAP HANA, Teradata , Aster Google BigQuery, HP Vertica,… An intuitive visual interface makes easy to import data by dragging and dropping tables, selecting columns (FFI14), rename fields and datasets (FFI17) (FFI18) and specifying filter conditions (FFI15). Moreover, data is visualized before being loaded (FFI11). By default, when user selects data to import, MicroStrategy automatically generates the SQL query that is required to select the data from the database. Alternatively, there is the option to load data by Freeform script. With Freeform script user writes his owns database queries to retrieve data from a relational database. For example, user can load data from a database using SQL, from third-party web services using XQuery, from Hadoop using HiveQL… always using a previous connection to the data source. User can then customize how the data is imported by changing the SQL query displayed in the Editor panel. User can use joins, expressions, aggregations, and filters to define the data that he wants to load. Therefore, Freeform script loading, let user to do a data cleansing before load data (FFI19). Moreover, user can also import a dashboard and data that an Analytics Desktop or MicroStrategy Analytics Express user has shared with him. During the loading process, user can define a data column type as attribute or metric but only during the importing process because in the dashboard user cannot modify it (FFI13). When it is defined as attribute, user can decide to assign a geo role to the attribute. Moreover, user can also choose the data format between: number, text, date, date time, big decimal, email, html tag, phone number, symbol and url (FFI12). If the column’s data type is Date, Time, or DateTime, Analytics automatically generates additional time-related information based on the contents of the data column. For example, if the column is assigned the Date data type, Analytics Desktop can automatically generate separate attributes for year and month information just with a click. User can also assign georole or shape key to the data column to enable data to be displayed on a map-based visualization. When a data column has been assigned a geo role, Analytics can also 102 automatically generate separated attributes containing higher level of geographical data. If the data column contains city data (that MicroStrategy Analytics recognize), the tool automatically generates the State attribute, which contains the state each city is located in. Moreover, for each dataset, the measure Row_Count is automatically created. It gives information about how many rows the dataset has. During the loading user can also choose not to import a column of data and rename columns while data is visualized. In contrast, user cannot filter the rows. Finally, user loads the datasets renaming it (FFI16). Analytics Desktop allows user to combine data from different sources in order to analyze relations between them (FFI9). The integration of different data sources is easy because fields are grouped in datasources, and user can distinguish them (FFI10). Data Model: MicroStrategy gives users a powerful option, to model their data, or not to model. By default, when user imports a new dataset directly into a dashboard that contains at least one dataset, the new dataset is automatically linked to attributes that already exist in the dashboard. MicroStrategy attempts to link attributes that share the same name. User can also manually link attributes that are shared across multiple existing datasets (FFD1) in order to get the desired data model (FFD2). An attribute that is linked across multiple datasets is displayed with a link icon and is displayed as one attribute when added to a visualization. MicroStrategy Anlytics does not offer the option to visualize the data model, and because of that this icons are very important(FFD3). User can unlink attributes that are already linked. Unlinked attributes with the same name are treated as two separate attributes when displayed in a visualization. Manually linking attributes allows user to link attributes across multiple existing datasets. The attributes that user link must be the same data type. User can link an attribute to attributes in one or more datasets. Circular references: Although the link is done manually, there can be circular references and MicroStrategy does not advice about them (FFF1). In fact, it works but results are not the correct ones (FFF2). The unique way to solve circular references are to load one time more the same table and link manually, or renaming the fields. It is because Analytics does not let to use the same table with different connections but by the same field (FFF3). The unique way to realize that there are circular references is knowing well the data, because MicroStrategy Anlytics does not offer the option to visualize the data model. Analysis: During the data analysis, user can create new metrics (called derived metrics) based on attributes and metrics that have already been loaded to a dashboard (FFA1). A derived metric performs a calculation on-the-fly with the data available on dashboard, without re-executing the dashboard against the data source. Derived metrics are saved and displayed only in the specific 103 dashboard in which they are created. MicroStrategy Analytics offers a plethora of functions to create new metrics.  User can create easily a new metric based on an arithmetic calculation (+,-,x,÷) from two metrics already in the dataset. -User can also create a new metric by aggregation functions as create a new metric to calculate a running total, moving total, assigning a numeric rank to each value in a metric, displaying metric values as percentages of a cumulative total, combining the values of two or more metrics...  User can also create a metric based on a dimension (FFA2) with the functions: Average, Count, Maximum, Minimum, Standard Deviation, Sum and Variance. If the attribute is not numeric it seems to don’t have sense. But if user wants to use a dimension to create a new measure, for example: He wants to create a new metric with value 1 if gender is “Male” and 0 if the gender is “Female” , he cannot do it directly, because MicroStrategy formulas only support metrics as arguments. Then, the solution is to create firstly a metric based on Gender by Average, Maximum, Minimum, Standard Deviation, Sum and Variance. These functions have only transform from attribute to metric, but the resulting values are the same: “Male” and “Female”. Once time, the new metric is created with exactly same values than the attribute, user can use it in logical formulas to create other new metric.  User can also create a new derived metric based on a MicroStrategy function. The categories of functions offered are: Basi functions, Data Mining functions, Date and Time, Financial, Internal, Math, Null/Zero, OLAP, Rank and NTile, Statistical and String.  User can create a derived metric from scratch and use conditional calculations provide conditional analysis by combining data into different groups based on the value of one or more metrics in a dashboard. Once time a derived metric is created it cannot be edited. If user wants to edit it, he must to remove it and created another one derived metric. Summarizing, there are many functions (FFA3) in order to let the user to do a well descriptive analysis (FFA4). Moreover, user can perform statistical analysis in Analytics using R (FFA6). Analytics supports the deployment of R analytics from the R statistical environment as derived metrics and once an R analytic is deployed to Analytics as a derived metric, the statistical analysis can be added to and analyzed on visualizations. The connection with R is not very well documented and it is not very popular. The integrated Data mining functions in MicroStrategy requires a connection to R (FFA5). The R scripts of the functions are already built and user only needs connect to R to execute the code. As stated above, when data have type Time, Date or DateTime Analytics Desktop create derived attributes with additional time-related information. Similar things happen when an attribute has assigned a geo role. To sum up, when a field is containing time or geographic information it is considered different (FFA7) (FFA8). When user has a very large set of data in a dashboard, it can be easier to work with that data grouping by in into logical subsets, and viewing only one of the subsets at a time. Adding an attribute to the Page-by panel, user can click an attribute elements to use to group data. When user group data in a dashboard, the grouping is applied to all visualizations on the current layout tab. Each layout in a dashboard is grouped separately, without affecting the contents of the other layouts in the dashboard (FFA9). Moreover, filter can be added in visualizations in order to display only filtered data. And filters can be on dimensions and also on metrics 104 (FFA10)(FFA11). On the other hand, when user is creating multiple visualizations in a same dashboard, he can filter data, selecting values in one visualization (the source) to automatically update the data displayed in another visualization (the target). In the settings menu of the source visualization user can choose the option to use as filter and can choose in which target visualizations the filter should be applied. Then, MicroStrategy allows the interaction between graphs but it is not automatic (FFA12). There are some assumptions about NULL data in files from where data is loaded. For Excel, .csv and text files, user should leave cells of data empty to represent NULL values, rather than using the text NULL (FFA13). On the other hand, on measures, null data are not considered. In fact, they are omitted for new calculated measures, but there exist functions to create a new calculate measure related with null data of a measure. For example, Null function determines how many nulls are displayed. In contrast, null data for dimensions are considered as a one more value, but when this attribute is also in other dataset, Analytical Engine only use the nonnull form value from the other dataset instead of the null form (FFA14). MicroStrategy Analytics offers many types of visualizations (FFA15). There are graph visualization where user can choose between lines, bar, area, scatter, bubble, grid and pie graph. Moreover there is also the option to add double axis. There are also, grid visualizations, network visualization, heat map visualizations, map visualization, density map visualizations, and map with areas visualization. Particularly, for a map with areas visualization user must provide an attribute whose values include the names of each area in the map’s base map. The base map is an ESRI map that contains the shape of each area that can be displayed in the visualization. The base maps available in MicroStrategy Analytics includes maps from continents, countries of the world, United States counties, United States regions, United States state abbreviations , United States state names, United States ZIP codes and World administrative divisions. Finally, MicroStrategy Analytics is not the tool with a wider variety of functions, but is the tool in which graphs can be more customized by user. Moreover, they can be totally modify by the user (FFA16). There has not been limitation to show many data dots, during the testing (FFA17). There is an option to refresh data, but it is not done automatically, user must order that clicking on an icon. And user can choose between overwrite existing data, update existing data as well as add new data and keep existing data and add new data (FFA18). Dashboard: User can export the whole MicroStrategy file to keep the interaction in visualizations. When user exports a dashboard, the entire dashboard, including visualizations, filters, and so on, as well as the associated dataset, are exported. And then a other user can import it and works on it (FFD1). Unfortunately, there are not templates integrated in dashboards (FFD2). And user has totally freedom to build them, adding text, images, links... (FFD3). 105 Reports: There is not the option to build reports with MicroStrategy Analytics. Reports are considered dashboards saved them as pdf (FFR1). Then, there are not templates for specific reports (FFR2). And free design is the offered in dashboards (FFR3). Languages: MicroStrategy Analytics Desktop can be displayed in more than two languages. They are Chinese, Danish, Dutch, English, French, German, Italian, Japanese, Korean, Polish, Portuguese, Russian, Spanish and Swedish (FIL1). Portability: MicroStrategy Analytics is a solution only for Windows operating systems (FIP1). The compatible versions are Windows Vista Business Edition SP2 (on x86 or x64), Windows Vista Enterprise Edition SP2 (on x86 or x64), Windows 7 Professional Edition SP1 (on x86 or x64), Windows 8 all editions (on x64), Windows Server 2008 Standard Edition R2 SP1 and SP2 (on x64),, Windows Server 2008 Enterprise Edition R2 SP1 and SP2 (on x64), and Windows Server 2012 Standard (on x64). MicroStrategy platform offers MicroStrategy Analytics Express which is a cloud-based selfservice visual analytics (FIP2). It is free for a year and it let user to be able to establish an account, invite colleagues to connect, analyze and share their data insight and do it all at no charge. With Express, user can easily access and explore data in Analytics using interactive visualizations. Analytics Express and Analytics Desktop are not connected, then if you want to have a project done by Desktop in the cloud you should load the project to Express manually. MicroStrategy also offers a custom free mobile app, which is built by MicroStrategy professionals in two weeks and it is for either the iPad, iPhone or Android. Then, there are not a application available for users, they must ask it to the corporation (FIP3). Using the Project by third parts: Analytics allows user to create MicroStrategy files (.mstr) to share dashboards with other Analytics Desktop, Analytics Express and MicroStrategy Analytics Enterprise users (FIU1). Data Exchange: Analytics Desktop also allows user to easily and rapidly export data from particular visualizations to Excel and CSV files. (FID1) (FID4)(FID2)(FID3). 112 Versatility: The upgrades are not automatic, and user must go to the web to upgrade the version (UOV1). Compilation Speed: During the test, it is not the tool which is faster, but it runs acceptably (EEC1). Resources Utilization: Tableau can be installed in processors x86 and also in x64(EER1). The minimum required RAM are 2GB (EER2) and the Hard disk space required is 750MB (EER3) Software requirements: Non extra softwares are required, excepto f a browser, an adobe reader and a Flash Player.(EES1) 113 Annex 7: Reporting examples In this chapter there are some charts created by the 4 evaluated Self-Service BI applications. These charts are showed in order to introduce their functionality to the reader. Fig. 28 Example of a Heat Map build by MicroStrategy Analyitics Fig. 29 Example of a Network chart built by MicroStrategy Analyitics 114 Fig. 30 Example of a Network chart built by MicroStrategy Analyitics Fig. 31 Example of a Map chart built by MicroStrategy Analyitics 115 Fig. 32 Example of a Map chart built by MicroStrategy Analyitics Fig. 33 Example of a k-means classification plot, done by the previous connection of MicroStrategy Analytics to R 116 Fig. 34 Example of a Heat Map built by QlikView Fig. 35 Example of a Radar Map built by QlikView 117 Fig. 36 Example of a Radar Map built by QlikView 118 Fig. 37 Example of a forecasting ,built by SAP Lumira Fig. 38 Example of a Funnel map, built by SAP Lumira 119 Fig. 39 Pie charts, built by Tableau