WIXLIB

As it is stated, the data warehouse is a huge repository of data, but it does not tell much by itself.Therefore, the tools for extracting the information that can be useful in a decision-making processshould be introduced. These tools are known as the exploitation tools. The ones that are the mostrelevant to my work in this thesis are the OLAP tools (On-Line Analytical Processing). The OLAP’smain objective is to analyze business data from its dimensional perspective. These tools are generallyconceived to exploit the data warehouse for analysis tasks based on multidimensionality.Multidimensionality represents a paradigm based on the fact/dimension dichotomy and it is intendedfor representing data as if placed in an n-dimensional space, which allows easy understanding andanalysis of data in terms of facts (the subjects of analysis) and dimensions showing the differentpoints of view from where a subject can be analyzed.The fact that OLAP tools are used for analysis based on multidimensionality, arose the necessity forthe appropriate multidimensional design (MD) of the underlying data warehouse.At this point, for the sake of better understandability, the specific multidimensional modelingterminology from the above mentioned fact/dimension dichotomy point of view, and that is usedthrough this thesis, is introduced.-Dimensional concepts represent the desired perspective, i.e., part of multidimensionalspace, from which we observe the fact (e.g. Time, Place). This perspective can be defined by:odimensions that can contain a hierarchy of levels representing different granularitiesof data(e.g. Time: Year ; Place: City), andodescriptors (slicers), which are the means for describing the characteristics andcontext of a particular level (e.g. Year “2006”, City “Tokyo”).-Factual data containing measures represents the data of interest for the analysis process(e.g. number of sold products). Value of this data is obtained according to chosendimensional concepts (perspective).Besides the appropriate multidimensional design (MD) of the underlying data warehouse andexploiting (OLAP) tool, data warehousing systems requires the means for managing the dataflowfrom the available sources to the target MD schema. This supporting process is called the ETL processand it has the main role in Extracting data from the chosen data sources, appropriately Transformingand homogenizing those data and Loading them into the underlying storage (data warehouse).Since in all IT projects business requirements are often a precursor to designing and building a newbusiness application/system, the design of the data warehousing systems also highly depends on thebusiness needs expressed as requirements. Therefore, a very important task is to fully understandrequirements coming from the business specialist and to correctly transform those requirements intothe appropriate MD and ETL designs.2

1.1.Starting pointDue to high complexity of MD and ETL structures, correlated with the lack of understandingbusiness needs, not that rarely decision-making support projects fail.Professors Alberto Abelló and Oscar Romero from ESSI department (Departament d’Enginyeriade Serveis i Sistemes d’Informació) at Technical University of Catalonia (UPC), together with AlkisSimitsis have recently presented, in [11], the GEM framework. GEM represents the system thatsemi-automatically produces both the data warehouse multidimensional (MD) design and theETL process designs of the resulting data warehousing system, concerning both the set ofbusiness requirements and source data stores as the inputs. This system tends to supportdesigners during the early stages of the DW design projects, by offering them help in overcomingobstacles that have previously threaten to hold down the whole project.During past several years, the architecture of the GEM system, presented recently in [11], hasbeen fully developed. (Figure 1)Figure 1: GEM framework architectureThe GEM framework, during the process of producing the ETL and MD conceptual designs, passesfive different stages. In the sequel, only brief introduction to these stages will be provided.Nowadays, it is well known that any data warehousing system must treat as first-class citizenboth the business (analytical) requirements and the source data that will populate the targetdata warehouse. Therefore, the GEM system, developed in this approach, takes as an input threedifferent pieces of information:-Source data stores whose semantics, characteristics and constrains are represented in atailored ontology.3

-Mappings that are expressed as an XML structure and that represent the information ifan ontology concept is mapped to the real source data and how it is actually mapped.-Business requirements are also expressed in a structured form of an XML file, whichrepresents the analytical query needed for the organizational decision making process.It can be noticed that the first two pieces of information represent the information about thedata sources, while the third one represents the requirements coming from the businessspecialists.Starting from the concepts stated inside the input business requirements, the first stage,Requirements Validation, searches for the corresponding concepts in the sources, i.e., in theontology. After the required concepts are identified in the ontology, they are then taggedaccording to the multidimensional role they may play (Level, Measure or Descriptor). Afterwards,the system searches for mapping of each tagged concept to the source data stores. The set ofontology concepts identified from the business requirements, tagged and mapped in theRequirements Validation stage, is then complemented with the additional information from thesources, during the following stage of Requirements Completion. This stage identifiesintermediate concepts that are not explicitly required in the input requirements, but are neededin order to retrieve data that will fully answer the input requirements. These concepts are lateralso tagged with their appropriate multidimensional role. Afterwards, the stage ofMultidimensional Validation, validates the correctness of these taggings, as a whole, accordingto multidimensional design principles. The following stage of GEM, Operation Identification,identifies the ETL operations needed to support the dataflow from the source data stores to thenewly created MD target data stores. ETL operations are here, using the meta-knowledgegenerated in the previous stages, identified in three separated phases. The final stage of theGEM is Conciliation stage. All the above stages should be run once for each input businessrequirement. The designs produced for each business requirement are then conciliated, duringthis stage, into a single multidimensional design and single supporting ETL process design.Besides the architecture, different stages of the framework have already been studied in depthand developed. The stage of Multidimensional Validation has been fully developed by ProfessorOscar Romero in [12], within his PhD thesis, while the Operation Identification stage has beenimplemented by Daniel Gil Gonzalez, as part of his final university project at Technical Universityof Catatonia (UPC).The basis of my master thesis represents the initial phase of the GEM framework. This phase, infact, includes integration of the elicited business requirements, with the whole process ofautomation of the MD and ETL conceptual designs. It consists of building a process that wouldinterpret the input business requirements according to the available sources, and translate theminto structures that the final stages of GEM require as their inputs. This actually intends toautomate the manipulation of business requirements and integrates them in the whole4

framework. This process is actually covered with the first two stages of the GEM framework.During the stage of Requirement Validation, this process, as already explained, first validatesinput requirements, tags them with their multidimensional roles, and maps them to the sources.At the same time, during this stage, based on these mappings, the process builds the initial ETLstructure. This structure represents the part of the input of the Operation Identification stageand actually includes the set of the ETL operations needed to extract the required concepts formthe source data stores. Afterwards, during the Requirement Completion stage, the processsearches the ontology and tries to relate required concepts inside the data sources, and as aresult it produces a graph structure. This structure contains identified paths between taggedconcepts including also the intermediate concepts and associations found on those paths. Finally,this graph structure represents the input for the Multidimensional Validation stage while it is alsoessential for the identification of the operations of the output ETL process design.1.2.Motivation and ObjectivesBusiness requirements are usually collected in various, sometimes even unstructured ways.Questionnaires, checklists, surveys, recorded oral conversations etc. Even when we deal with thestructured forms of the requirements, process of translating these requirements into the desireddesign requires high effort and is usually error-prone.As nowadays the markets are highly competitive and have strong global tendency, corporationshas to make rapid and smart decisions in order to survive on it. Therefore, companies’ revenuegrowth highly depends on the existence and quality of the decision-making systems.This project is mainly motivated by the desire to eliminate mentioned obstacles in the early stageof the multidimensional and ETL design, and to possibly lower the current projects failure rate. Itcan be stated here, that the main goal of my work is to provide the GEM framework with the preprocess that will higher the level of automation of the whole system and generalize the system’sinput data formats.1.3.Scope of the projectThis project represents master thesis and the final project, on the Master in Computing program,at Technical University of Catalonia.Led by the motivations and goals previously expressed, this project consists of the following:-Theoretical part. This part represents the research in the field of automating andcustomization of multidimensional and ETL designs. It also includes exploration of theprevious attempts in building a system which would lead system designers during theprocess of the ETL design, and5

-Technological part. This part includes the realization of the initial stages of the GEMframework. Besides implementation of these stages, technological part of the thesis alsoincludes complete integration of the initial stages with the other, already implementedstages of GEM, i.e., Multidimesional Validation (MDBE) and Operation Identification (ETLgeneration), into the whole framework. These stages have been developed by professorOscar Romero and Daniel Gil Gonzalez respectively.1.4.Structure of the documentAs this document is the final university project, it is structured in the way to completelyrepresent my work during the project, beginning with the research and theoretical part, followedby the complete documentation of the development and testing processes.Chapter 2 represents the State of the Art in the field of automation of ETL process designincluding some early customization attempts and some newly a

-Dimensional concepts represent the desired perspective, i.e., part of multidimensional space, from which we observe the fact (e.g. Time, Place). This perspective can be defined by: o dimensions that can contain a hierarchy of levels representing different