Detta arbete har sin utgångspunkt i Tekniska Verkens och Östkrafts integrerade operationella system. Dessa är utvecklade för att stödja beslutsprocesser för bl.a. fysisk och finansiell elhandel. Integreringen har gjorts genom annamandet av en IRM-baserad lösning, av verksamheterna benämnt "datavaruhus". Avregleringen av elmarknaden medförde större krav på elleverantörerna med avseende på flexibilitet och funktionalitet när kunderna fick välja elbolag själva. Det som främst bidrar till komplexiteten gällande elhandel är de många olika sorters elavtal som kan tecknas och det ständigt varierande inköpspriset på nordiska kraftbörsen. För fallstudiens företag gäller att deras datavaruhuslösning lider av osedvanligt dåliga prestanda. Syftet med uppsatsen är att utifrån en kvalitativ studie försöka identifiera primära faktorer för dessa prestandaproblem. Vidare vill vi belysa hur man bör integrera befintliga operationella system för att uppnå goda prestanda. Arbetets slutsats är att prestandaproblemen kan härledas både till det arkitekturella och det strukturella planet såväl som till valet att egenutveckla den logik som bearbetar data genom att hämta, transformera och uppdatera datavaruhuset. Ytterligare en faktor utgörs av den höga detaljeringsgrad som kännetecknar data i datavaruhuset.

Atlikta duomenų integravimo procesų, metodų bei DI technologijų ir įrankių analizė siekiant rasti labiausiai tinkamą variantą įmonei. Išnagrinėti MS SQL 2005 Server SSIS paslauga, Oracle ir įmonėje veikusi MS SQL 2000 Server DTS funkcija. Duomenų integravimo įrankiai paremti ETL platforma. Tai procesai, leidžiantys kompanijoms perkelti duomenis iš įvairių šaltinių, pakeisti formatą ir įkelti juos į kitas duomenų bazes, duomenų centrus, nagrinėjimo saugyklas ar į kitas operacines sistemas palaikyti verslo procesus. Įvykdyta GNT įmonės duomenų integravimo sistemos posistemio reinžinerija naujai pasirinkta technologija. Reinžinerija yra egzistuojančios sistemos analizės ir modifikavimo procesas, atliekamas kai norima pertvarkyti sistemą. Pasirinktas informacijos apie produktus duomenų integravimo procesas, sukurtas DTS pagalba. Procesas patobulintas ir perkeltas į naują technologiją. Atlikus įmonėje veikiančių duomenų integravimo procesų analizę, pastebėtas skirtingas duomenų detalumas ir galimybė juos suskirstyti į kategorijas. Gauta informacija apibendrinta šablonu pavidalu, kuris pavadintas „Bendrinių duomenų atskyrimo šablonu“. Eksperimento metu nustatytas proceso pagreitėjimas, panaudojus sukurtą šabloną. Jis sėkmingai taikomas kitiems duomenų integravimo procesams, atliekant reinžinerią. / The research area of thesis covers various data extraction, transfer and integration methods and technologies; the main object of the research is the process of transferring data from remote subsidiaries and integrating it into one central database currently active in JSC “GNT Lietuva”. The goal of this research is to move data integration (DI) processes into a new technological environment and upgrade them without interrupting active daily DI process, ultimately creating a flexible data integration model (pattern), which could be reused in the future. Following tasks were carried out in order to achieve this goal: analysis of reengineering and data integration principles as well as new integration technologies; investigating their adaptability to the current DI processes and their improvement; implementing integration solutions and experimentation to verify the efficiency of new DI processes, and, finally, construction of a flexible integration solution. The final generated solution was formalized as a data integration pattern. Conclusions drawn from the experiment accomplished in JSC “GNT Lietuva” indicate that practical application of the pattern reduced overall duration of the DI process by 45,4%, whilst the additional application of the SSIS technology resulted in duration decrease of 81,99%. The data integration process became more flexible and new data sources can be easily incorporated from now on.

Les ED deviennent des composants incontournables dans les entreprises et les organisations.Le thème de conception des ED a fait l’objet de recherches actives ces dernières années. Laprincipale limitation des approches proposées est le manque d’une vision globale s’inscrivantdans le cadre du cycle de conception des ED, même si la communauté reconnait toutes lesphases de ce cycle. Nos principales contributions dans cette thèse portent sur la proposition d’uneméthode de conception adaptée aux récentes évolutions qu’a connu le cycle de conception, etenglobant l’ensemble de ses phases. Le cycle de conception a connu une diversification importantedes modèles de stockage de données et des architectures de déploiement possibles offrant deschoix de conception variés. Ce cycle reconnait l’importance des besoins des utilisateurs dansle processus de conception, et l’importance d’accès et de représentation de la sémantique desdonnées. Notre première proposition présente une méthode de conception suivant une approcheà base d’ontologies de domaine, permettant de valoriser les besoins des utilisateurs en leur offrantune vue persistante au sein de l’ED. Cette vue permet d’anticiper diverses tâches de conceptionet de simuler les différents choix de conception. Notre deuxième proposition revisite le cycle deconception en exécutant la phase ETL (extraction-transformation-chargement des données) dèsla phase conceptuelle. Cette proposition permet de fournir un moyen de déploiement multiplesur différentes plateformes disponibles. / Data Warehouses (DWs) become essential components for companies and organizations.DWdesign field has been actively researched in recent years. The main limitation of the proposedapproaches is the lack of an overall vision covering the DW design cycle. Our main contributionin this thesis is to propose a method adapted to recent evolutions of the DW design cycle,and covering all its phases. These evolutions have given rise to new data storage models andnew deployment architectures, which offers different design choices for designers and administrators.DW literature recognizes the importance of user requirements in the design process, andthe importance of accessing and representing data semantics. We propose an ontology drivendesign method that valorizes users’ requirements by providing them a persistent view in theDW structure. This view allows anticipating diverse design tasks and simulating different designchoices. Our second proposal revisits the design cycle by executing the ETL phase (extractiontransformation-loading of data) in the conceptual stage. This proposal allows a deployment à lacarte of the DW using the different deployment platforms available.

Les besoins fonctionnels et non fonctionnels représentent la première brique pour la conception de toute application, logiciel, système, etc. L'ensemble des traitements associés aux besoins est établi dans le cadre de l'ingénierie des Besoins (IB). Le processus de l'IB comporte plusieurs étapes consistant à découvrir, analyser, valider et faire évoluer l'ensemble des besoins relatifs aux fonctionnalités du système. La maturité de la communauté de l'IB lui a permis d'établir un cycle de vie bien déterminé pour le processus de besoins qui comprend les phases suivantes :l'élicitation, la modélisation, la spécification, la validation et la gestion des besoins. Une fois ces besoins validés, ils sont archivés ou stockés dans des référentiels ou des dépôts au sein des entreprises. Avec l'archivage continu des besoins, ces entreprises disposent d'une mine d'informations qu'il faudra analyser afin de reproduire les expériences cumulées et le savoir-faire acquis en réutilisant et en exploitant ces besoins pour des nouveaux projets. Proposer à ces entreprises un entrepôt dans lequel l'ensemble de besoins est stocké représente une excellente opportunité pour les analyser à des fins décisionnelles et les fouiller pour reproduire des anciennes expériences. Récemment, la communauté des processus (BPM) a émis le même besoin pour les processus. Dans cette thèse, nous souhaitons exploiter le succès des entrepôts de données pour le reproduire sur les besoins fonctionnels. Les problèmes rencontrés lors de la conception des entrepôts de données se retrouvent presque à l'identique dans le cas des besoins fonctionnels.Ces derniers sont souvent hétérogènes, surtout dans le cas d'entreprises de grande taille comme Airbus, où chaque partenaire a la liberté d'utiliser ses propres vocabulaire et formalisme pour décrire ses besoins. Pour réduire cette hétérogénéité, l'appel aux ontologies est nécessaire. Afin d'assurer l'autonomie de chaque source, nous supposons que chaque source a sa propre ontologie.Cela nécessite des efforts de matching entre les ontologies afin d'assurer l' intégration des besoins fonctionnels. Une particularité importante liée à l'entreposage de besoins réside dans le fait que ces derniers sont souvent exprimés à l'aide des formalismes semi-formels comme les use cases d'UML avec une partie textuelle importante. Afin de nous rapprocher le plus possible de ce que nous avons fait dans le cadre de l'entreposage de données, nous proposons un modèle pivot permettant de factoriser trois semi-formalismes répandus utilisés par les sources de besoins avec une description précise de ces derniers. Ce modèle pivot permettra de définir le modèle multidimensionnel del' entrepôt de besoins, qui sera ensuite alimenté par les besoins des sources en utilisant un algorithme ETL (Extract, Transform, Load). À l'aide des mécanismes de raisonnement offerts par les ontologies et des métriques de matching, nous avons nettoyé notre entrepôt de besoins. Une fois l'entrepôt déployé, il est exploité par des outils d'analyse OLAP.Notre méthodologie est supportée par un outil couvrant l'ensemble des phases de conception et d'exploitation d'un entrepôt de besoins. / Functiona] and non-functional requirements represent the first step for the design of any application, software, system, etc. Ail the issues associated to requirements are analyzed in the Requirements Engineering (RE) field. The RE process consists of several steps consisting of discovering, analyzing, validating and evolving the requirements related to the functionalities of the system. The RE community proposed a well-defined life-cycle for the requirements process that includes the following phases: elicitation, modeling, specification, validation and management. Once the requirements are validated, they are archived or stored in repositories in companies. With the continuous storage of requirements, companies accumulate an important amount of requirements information that needs to be analyzed in order to reproduce the previous experiences and the know-how acquired by reusing and exploiting these requirements for new projects. Proposing to these companies a warehouse in which all requirements are stored represents an excellent opportunity to analyze them for decision-making purposes. Recently, the Business Process Management Community (BPM) emitted the same needs for processes. In this thesis, we want to exploit the success of data warehouses and to replicate it for functional requirements. The issues encountered in the design of data warehouses are almost identical in the case of functional requirements. Requirements are often heterogeneous, especially in the case of large companies such Airbus, where each panner bas the freedom to use its own vocabulary and formalism to describe the requirements. To reduce this heterogeneity, using ontologies is necessary. In order to ensure the autonomy of each partner, we assume that each source bas its own ontology. This requires matching efforts between ontologies to ensure the integration of functional requirements. An important feature related to the storage of requirements is that they are often expressed using semi-forma! formalisms such as use cases of UML with an important textual part. In order to get as close as possible to our contributions in data warehousing,we proposed a pivot model factorizing three well-known semi-formalisms. This pivot model is used to define the multidimensional model of the requirements warehouse, which is then alimented by the sources requirements using an ETL algorithm (Extract,Transform, Load).Using reasoning mechanisms otfered by ontologies and matching metrics, we cleaned up our requirements warehouse. Once the warehouse is deployed, it is exploited using OLAP analysis tools. Our methodology is supported by a tool covering all design phases of the requirements warehouse

Business Intelligence (BI) enables organizations to collect and analyze internal and external business data to generate knowledge and business value, and provide decision support at the strategic, tactical, and operational levels. The consolidation of data coming from many sources as a result of managerial and operational business processes, usually referred to as Extract-Transform-Load (ETL) is itself a statically defined process and knowledge workers have little to no control over the characteristics of the presentable data to which they have access. There are two main reasons that dictate the reassessment of this stiff approach in context of modern business environments. The first reason is that the service-oriented nature of today’s business combined with the increasing volume of available data make it impossible for an organization to proactively design efficient data management processes. The second reason is that enterprises can benefit significantly from analyzing the behavior of their business processes fostering their optimization. Hence, we took a first step towards quality-aware ETL process design automation by defining through a systematic literature review a set of ETL process quality characteristics and the relationships between them, as well as by providing quantitative measures for each characteristic. Subsequently, we produced a model that represents ETL process quality characteristics and the dependencies among them and we showcased through the application of a Goal Model with quantitative components (i.e., indicators) how our model can provide the basis for subsequent analysis to reason and make informed ETL design decisions. In addition, we introduced our holistic view for a quality-aware design of ETL processes by presenting a framework for user-centered declarative ETL. This included the definition of an architecture and methodology for the rapid, incremental, qualitative improvement of ETL process models, promoting automation and reducing complexity, as well as a clear separation of business users and IT roles where each user is presented with appropriate views and assigned with fitting tasks. In this direction, we built a tool —POIESIS— which facilitates incremental, quantitative improvement of ETL process models with users being the key participants through well-defined collaborative interfaces. When it comes to evaluating different quality characteristics of the ETL process design, we proposed an automated data generation framework for evaluating ETL processes (i.e., Bijoux). To this end, we classified the operations based on the part of input data they access for processing, which facilitated Bijoux during data generation processes both for identifying the constraints that specific operation semantics imply over input data, as well as for deciding at which level the data should be generated (e.g., single field, single tuple, complete dataset). Bijoux offers data generation capabilities in a modular and configurable manner, which can be used to evaluate the quality of different parts of an ETL process. Moreover, we introduced a methodology that can apply to concrete contexts, building a repository of patterns and rules. This generated knowledge base can be used during the design and maintenance phases of ETL processes, automatically exposing understandable conceptual representations of the processes and providing useful insight for design decisions. Collectively, these contributions have raised the level of abstraction of ETL process components, revealing their quality characteristics in a granular level and allowing for evaluation and automated (re-)design, taking under consideration business users’ quality goals.

This diploma thesis deals with the design of a data warehouse that stores events created in the mobile app. The goal was to design an alternative to the current solution. The thesis explains concepts, mainly data warehouse terminology, which are used in subsequent chapters. An analysis of the current solution is conducted, and as well as research of available data warehouse and ETL services. Based on the results of the analysis, a suitable new solution is chosen, implemented, and tested.

Business Intelligence (BI) enables organizations to collect and analyze internal and external business data to generate knowledge and business value, and provide decision support at the strategic, tactical, and operational levels. The consolidation of data coming from many sources as a result of managerial and operational business processes, usually referred to as Extract-Transform-Load (ETL) is itself a statically defined process and knowledge workers have little to no control over the characteristics of the presentable data to which they have access. There are two main reasons that dictate the reassessment of this stiff approach in context of modern business environments. The first reason is that the service-oriented nature of today’s business combined with the increasing volume of available data make it impossible for an organization to proactively design efficient data management processes. The second reason is that enterprises can benefit significantly from analyzing the behavior of their business processes fostering their optimization. Hence, we took a first step towards quality-aware ETL process design automation by defining through a systematic literature review a set of ETL process quality characteristics and the relationships between them, as well as by providing quantitative measures for each characteristic. Subsequently, we produced a model that represents ETL process quality characteristics and the dependencies among them and we showcased through the application of a Goal Model with quantitative components (i.e., indicators) how our model can provide the basis for subsequent analysis to reason and make informed ETL design decisions. In addition, we introduced our holistic view for a quality-aware design of ETL processes by presenting a framework for user-centered declarative ETL. This included the definition of an architecture and methodology for the rapid, incremental, qualitative improvement of ETL process models, promoting automation and reducing complexity, as well as a clear separation of business users and IT roles where each user is presented with appropriate views and assigned with fitting tasks. In this direction, we built a tool —POIESIS— which facilitates incremental, quantitative improvement of ETL process models with users being the key participants through well-defined collaborative interfaces. When it comes to evaluating different quality characteristics of the ETL process design, we proposed an automated data generation framework for evaluating ETL processes (i.e., Bijoux). To this end, we classified the operations based on the part of input data they access for processing, which facilitated Bijoux during data generation processes both for identifying the constraints that specific operation semantics imply over input data, as well as for deciding at which level the data should be generated (e.g., single field, single tuple, complete dataset). Bijoux offers data generation capabilities in a modular and configurable manner, which can be used to evaluate the quality of different parts of an ETL process. Moreover, we introduced a methodology that can apply to concrete contexts, building a repository of patterns and rules. This generated knowledge base can be used during the design and maintenance phases of ETL processes, automatically exposing understandable conceptual representations of the processes and providing useful insight for design decisions. Collectively, these contributions have raised the level of abstraction of ETL process components, revealing their quality characteristics in a granular level and allowing for evaluation and automated (re-)design, taking under consideration business users’ quality goals.

ETL processes play an increasingly important role for the support of modern business operations. These business processes are centred around artifacts with high variability and diverse lifecycles, which correspond to key business entities. The apparent complexity of these activities has been examined through the prism of Business Process Management, mainly focusing on functional requirements and performance optimization. However, the quality dimension has not yet been thoroughly investigated and there is a need for a more human-centric approach to bring them closer to business-users requirements. In this paper we take a first step towards this direction by defining a sound model for ETL process quality characteristics and quantitative measures for each characteristic, based on existing literature. Our model shows dependencies among quality characteristics and can provide the basis for subsequent analysis using Goal Modeling techniques.

Ingeniero Civil en Computación / Hoy en día las empresas nacen como respuesta a distintos problemas o desafíos entregando diversas de soluciones, las cuales se llevan a cabo ejecutando una serie de procesos. Estos pueden estar definidos formalmente o se van construyendo de forma natural a partir de la forma en que las personas abordan los problemas. Por esto, se hace fundamental poder realizar distintos análisis sobre dichos procesos, en especial descubrir cómo se llevan a cabo en forma real. Estos análisis nos permiten optimizar, mejorar, transformar estos procesos permitiendo a las empresas y grupos de trabajo evolucionar. Muchos de estos análisis, y los programas que los implementan, suelen ser de alta complejidad y tienden a tomar datos de los procesos en formatos específicos. Esto ocasiona que los registros de datos, también conocidos como logs de procesos, deban adaptarse a dichos formatos y por consiguiente a las herramientas y/o personas que los generan. Otra opción, es realizar un desarrollo sobre los programas de análisis para que puedan aceptar un nuevo formato, lo que tiene un costo proporcional a la complejidad del software. Es por esto que se propuso una nueva opción: desarrollar una herramienta ETL intermediaria que nos permita transformar logs desde distintos formatos a un formato utilizado por programas de descubrimientos de procesos u otros tipos de análisis relacionados. Se logró desarrollar una aplicación que, a través de una GUI, permite hacer distintas transformaciones desde bases de datos y planillas Excel al formato XES, el cual es usado por varios programas de descubrimiento de procesos. Esta aplicación permite al usuario configurar sus conversiones eligiendo los datos de origen así como su tipo. Este programa fue validado a través de distintas pruebas, utilizando datos reales y un programa que realiza descubrimientos de procesos sobre archivos XES. Este desarrollo ha permitido abarcar una amplia gama de formatos sin tener que modificar datos ni los otros programas que los utilizaban, permitiendo que diversos procesos puedan ser estudiados y mejorados. / Este trabajo ha sido parcialmente financiado por Proyecto Gems.

At a certain point of its life cycle, each data warehouse gets into a state in which the length of the ETL processing starts slowly approaching the acceptable limit. Even the slightest lapse means a hardly erasable time lag and the data supplies to users are increasingly delayed. Before the situation becomes more problematic, we need to react. This diploma thesis deals with the possible solutions of this problem- the ETL performance optimization. The main aim of the thesis is to propose the set of recommendations for the ETL process optimization and its presentation based on practical real world examples. While going through this thesis, the readers get inside the running of the real ETL optimization project. They learn how to proceed during its particular periods, what to avoid and how to reduce the risk level as much as possible. Moreover, the thesis mentions the prevention of the ETL performance loss and discusses the critical factors of the effective ETL process design. Although the proposed methods are described in a general perspective, the thesis is mainly focused on Oracle's database, which is its certain limitation. Also hardware optimization is not a part of the text. First chapters of the thesis describe the problem from the theoretical point of view, which is important for the correct understanding of the main content and the existing literature concerning this problematic is included here as well. Next sections describe the proposed methods and recommendations for the optimization of the ETL processes including tests and project results evaluation. They are mainly focused on the ETL process synchronization and the SQL code optimization areas. All the methods are presented of examples of the real project implemented by the author.