...and Lifecycle Erik Perjons, DSV, SU/KTH perjons@dsv.su.se The data warehouse architecture The back room The front room Analysis/OLAP Productt Product2 Product3 Product4 Time1 Time2 Time3 Time4 Value1 Value2 Value3 Value4 Value11 Value21 Value31 Value41 Data warehouse External sources Extract Transform Load Serve Query/Reporting Operational source systems Data marts Data mining Falö aöldf flaöd aklöd falö alksdf Operational source Data staging systems (RK) area (RK) Legacy systems Back end tools OLTP/TP systems Data presentation area (RK) ”The data warehouse” Presentation (OLAP) servers Data access tools (RK) End user applications Business Intelligence tools Operational Source Systems Operational source systems characteristics: Operational source systems • the source data often in OLTP (Online Transaction Processing) systems, also called TPS (Transaction Processing Systems) • high level of performance and availability • often one-record-at-a time queries • already occupied by the normal operations of the organisation OLTP vs. DSS (Decision Support Systems) OLTP vs. OLAP (Online analytical processing) Operational Source Systems More operational source systems characteristics: Operational source systems • a OLTP system may be reliable and consistent, but there are often inconsistencies between different OLTP systems • different types of data format and data structures in different OLTP systems AND DIFFERENT SEMANTICS Operational...
Words: 2902 - Pages: 12
...Data Mining: Concepts and Techniques (3rd ed.) Jiawei Han, Micheline Kamber, and Jian Pei University of Illinois at Urbana-Champaign & Simon Fraser University ©2011 Han, Kamber & Pei. All rights reserved. Adapted for CSE 347-447, Lecture 1b, Spring 2015 1 1 Introduction n n n n n n n n n n Why Data Mining? What Is Data Mining? A Multi-Dimensional View of Data Mining What Kind of Data Can Be Mined? What Kinds of Patterns Can Be Mined? What Technologies Are Used? What Kind of Applications Are Targeted? Major Issues in Data Mining A Brief History of Data Mining and Data Mining Society Summary 2 Why Data Mining? n The Explosive Growth of Data: from terabytes to petabytes n Data collection and data availability n Automated data collection tools, database systems, Web, computerized society n Major sources of abundant data n n n Business: Web, e-commerce, transactions, stocks, … Science: Remote sensing, bioinformatics, scientific simulation, … Society and everyone: news, digital cameras, YouTube n n We are drowning in data, but starving for knowledge! “Necessity is the mother of invention”—Data mining—Automated analysis of massive data sets 3 Evolution of Sciences: New Data Science Era n n Before 1600: Empirical science 1600-1950s: Theoretical science n Each discipline has grown a theoretical component. Theoretical models often motivate experiments and generalize our understanding...
Words: 3169 - Pages: 13
...Data warehousing History: "Data Warehouse" (QW) as a term was first coined and used by Bill Inmon in 1990. He asserted that Data warehouse is subject Oriented, Integrated, Time-Variant and nonvolatile collection of facts that can be useful in supporting decision making if studied or critically analysed. Definition: A DW is a centralized repository that stores data and information from multiple sources and can be used to transforms this data into a common, multidimensional data model for efficient and effective querying and analysis. A data warehouse can also be considered to be a repository generally used for research as well as supporting decision making based on transaction operating system On-line Transaction processor (OLTP) or on-line analytical processing (OLAP). A data warehouse is designed for query and analysis rather than for transaction processing, separates analysis workload from transaction workload and enables an organization to consolidate data from several sources. Additionally important, data warehouse will include a way of extracting, transporting, transformation and techniques of providing solutions. It also provides client analysis tools, anaylysis engines (OLAP), plus a series of other applications that manage the process of capturing data as it occurs. Facts about a DW * Less frequent updating done * Kept separate from the organization's operational database * Is made up of consolidated historical data which help the organizations’...
Words: 751 - Pages: 4
...YFOSBAPP Database A database is information organized in such a way that a computer program can quickly select pieces of data. You can think of it as an electronic filing system. A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. Data Integration Data integration involves combining data residing in different sources and providing users with a unified view of these data. This process becomes significant in a variety of situations, which include both commercial (when two similar companies need to merge their databases) and scientific (combining research results from different bioinformatics repositories, for example) domains. Data integration appears with increasing frequency as the volume and the need to share existing data explodes. It has become the focus of extensive theoretical work, and numerous open problems remain Existing DBMSs provide various functions that allow management of a database and its data which can be classified into four main functional groups: Data definition – Creation, modification and removal of definitions that define the organization of the data. Update – Insertion, modification, and deletion of the actual data. Retrieval – Providing information in a form directly usable or for further processing by other applications. The retrieved data may be made available in a form basically the same as it is stored in the database or in a new...
Words: 4053 - Pages: 17
...What is a Data Warehouse • A data warehouse is a relational database that is designed for query and analysis. • It usually contains historical data derived from transaction data, but it can include data from other sources. Finance, Marketing, • Data warehouse can be: Subject Oriented Integrated Nonvolatile Time Variant Inventory SAP, Weblogs, Legacy Identical reports produce same data for different period. daily/monthly/quarterly basis Why Data Warehouse • • • • Provide a consistent information of various cross functional activity. Historical Data. Access, Analyze and Report Information. Augment the Business Processes Why is BI so Important Information Maturity Model Return on Information BI Solution for Everyone BI Framework Business Layer Business goals are met and business value is realized Administration & Operation Layer Business Intelligence and Data Warehousing programs are sustainable Implementation Layer Useful, reliable, and relevant data is used to deliver meaningful, actionable information BI Framework Business Requirements Data Sources Data Sources Data Acquisition, Cleansing,& Integration Data Acquisition, Cleansing, & Integration Data Stores Data Stores Information Services Information Delivery Information Delivery Business Analytics Business Analytics Business Applications Business Applications Business Value Business Value Development Data Resource Administration ...
Words: 3637 - Pages: 15
...Implementation of a Data Warehouse Prototype for the University of Nairobi within the Context of Relational Online Analytical Processing (Data Analysis) PRESENTED BY: 1. JORAM KIPLIMO D61/68960/2013 0724431978 . PRESENTED TO: Dr. Muranga Njihia Word count: 2752 ABSTRACT Data ware housing is a booming industry with many interesting research problem. The data warehouse is concentrated on only few aspects. The discussion here is about the data warehouse design and usage in the case of the University of Nairobi Environment. Data warehouse can be built using a top-down approach, bottom – down approach or a combination of both. In this research paper we are discussing about the data warehouse design process. Data Warehouse (DWH) systems are used by decision makers for performance measurement and decision support. Currently the main focus of the DWH research field is not as much on the interaction of the DWH with the organization, its context and the way it supports the organization’s strategic goals, as on database issues. The aim of the study is to emphasize and describe the relationship between the DWH and the organization with conceptual models, and to use this knowledge to support data interpretation with business metadata. KEYWORDS Data Warehouse (DWH) Data Mart Extraction, Transformation and Loading (ETL); Software that is used to extract data from a data source like a operational system or data warehouse, modify the data and then...
Words: 2816 - Pages: 12
...Reporting Infrastructure Tool Evaluation Introduction Ease of use of the AWS data warehouse is one of the key tenants of the AWS DW team. Currently AWS DW customers connect directly to data warehouse databases (RO & Primary) and use SQL to extract the data. Providing a general-purpose and self-service reporting infrastructure significantly reduces the effort spent by data warehouse customers to exact data and subsequently build metrics decks and dashboards. Oracle Business Intelligence (OBIEE) and Tableau are being evaluated for their suitability for our AWS data warehouse customers. We are recommending Tableau. We have determined that it provides the best self-service experience for general purpose reporting needs and for more advanced data analytics and data visualization needs of the users of the AWS data warehouse. Problem Statement AWS data warehouse customers build and maintain SQL’s to extract data from the data warehouse. Once the raw data is extracted, it is often combined with custom data for individual services. Metrics on excel worksheets are then generated/updated. These excel worksheets are circulated to service team members and leadership for analyzing the usage and revenue of their products and also for identifying opportunities for operational improvements. Most tasks that involve creating the metric desks are manual, consuming significant effort of the data warehouse customers. The manual efforts are typically error prone and associated with the...
Words: 829 - Pages: 4
...ORACLE 91) 1. Write a note on File based system/ Data base system Solution: File-Based Systems: Conventionally, Before the database Systems evolved , data in the software’s Systems was stored in and represented using flats files. Drawbacks of File-Based Systems: File - Based Systems [pic] As shown in the figure, in a file-based system, different programs in the same application may be interacting with different private data files. There is no system enforcing any standardized control on the organization and structure of these data files. • Data Redundancy and Inconsistency Since data resides in different private data files, there are chances of redundancy and resulting inconsistency. For example, in the above example shown, the same customer can have a savings account as well as a mortgage loan. Here the customer details may be duplicated since the programs for the two functions store their corresponding data in two different data files. This gives rr.>e to redundancy in the customer's data. Since the same data is stored in two files, inconsistency arises if a change made in the data in one file is not reflected in the other. • Unanticipated Queries In a file-based system, handling sudden/ad-hoc queries can be difficult, since it requires changes in the existing programs. • Data Isolation Though data used by different programs in the application may be related, they reside in isolated data files. • Concurrent Access Anomalies In large multi-user...
Words: 4599 - Pages: 19
...CSCI 1507 (1903) "Enterprise level data work flows and Data Warehousing" Professor Rajni Palikhey University of Northern Virginia Acknowledgement This Research Paper would not have been possible without the guidance and the help of my co-students and respected Professor who in one way or the other contributed and extended their valuable assistance in the preparation and completion of this research paper. I would to like to convey my sense of gratitude to Professor.Rajni Palikhey who helped and supported us right throughout the semester. This paper would not have been possible without her cooperation and technical assistance. We would also thank our Institution and our faculty members without whom this project would have been a distant reality. We also extend our heartfelt thanks to our family and well wishers. I would like to take this occasion to specially thank University of Northern Virginia to provide us with excellent faculty and also in supporting us getting quality education remotely. Contents SL No Title Page no 1 Abstract 5 2 Introduction to Databases 6 3 OLTP and OLAP Systems 7 4 Difference between OLTP and OLAP 9 5 Data Modeling 13 6 Workflows in Enterprise level Data warehousing 18 7 Business Intelligence tools used in Data flow and Data Warehousing 21 8 Analysis in Data warehousing 24 9 Conclusion 28 10 Foot Note 30 11 References 31 ABSTRACT These days majority...
Words: 6349 - Pages: 26
... 4. Improve the internal control structure 5. Improve decision making Key Role if the AIS 1. Collecting and storing data 2. Providing information for decisions 3. Safeguarding assets Value Chain Activities 1. Inbound Logistics------ receiving and storage 2. Operations -------------- manufacturing and repackaging 3. Outbound Logistics---- distribution shipping 4. Marketing & Sales------ advertising, selling 5. Service-------------------- repair, maintenance Characteristic of Useful Information 1. Relevant 2. Reliable 3. Timely 4. Verifiable 5. Understandable 6. Accessible 7. Complete Chapter 2/SUA Purpose/use of general ledger, subsidiary ledgers, special journals and cash prelist General Ledger= contains summary level data for every asset, liability, equity and revenue, and expense account...(accounts receivable) Subsidiary Ledger= contains detailed data for any general ledger account with many individualized subaccounts.... (Separate account for each accounts receivable) General Journal= used to record infrequent or non-routine transaction, such as loan payments Specialized Journal= records large numbers of repetitive transactions such as sales, cash receipts, and cash disbursement Sequence of events in accounting cycle 1. Collect Data 2. Journalize Transactions (capture in transaction files) 3. Post transactions to accounts in ledger (G/L master file) ...
Words: 2842 - Pages: 12
...Enterprise Data Warehouse of ABC University TABLE OF CONTENTS Page CHAPTER 1. INTRODUCTion 3 A. BUSINESS INTELIGENCE 3 B. Data Warehouese 3 C. ETL 3 D. DATA Mart 3 CHAPTER 2. Enterprise 3 A. project plan 3 1. Data Requirements 3 2. Historical Requirements 3 3. Security Requirements 3 4. Performance Requirements 6 B. datAbase design criteria 6 1. The Theory 6 2. Data Modeling 6 3. Unique Identifier 6 4. Database type 6 C. etl process 6 1. Extraction: 6 2. Transfer: 6 3. Load: 6 D. data mart 6 E. data Mining 6 CHAPTER 3. summary 6 A. datawarehosue 6 B. ETL 6 C. data mart 6 D. data mining 6 CHAPTER 4. work cited 6 INTRODUCTion BUSINESS INTELIGENCE Business Intelligence is not a new tactic of reviewing data as there had been traditional tools to analyze data to be able to deliver value to the owners of those data (Souhard Joens, Birst, 8/21/14). How to benefit from the vast data which we had gathered, planned or by default, how this data can be consolidated, and is there any way to make sense of these data have been the drive behind the new tools presented to be able to arrive at some conformity and then being able to use the analytically to better the entity, whatever the mission. Data Warehouese Data Warehouse is a database which holds vast amount of data different in the nature and type in various tables inside in a manner which they can be accessed and manipulated to generate intelligent reports for analytical...
Words: 1689 - Pages: 7
...DATA WAREHOUSE ARCHITECTURE Albina Yusupova Data Warehouse Architecture The main difference between the database architecture in a standard, on-line transaction processing oriented system (usually ERP or CRM system) and a Data Warehouse is that the system’s relational model is usually de-normalized into dimension and fact tables which are typical to a data warehouse database design. The differences in the database architectures are caused by different purposes of their existence. In a typical OLTP system the database performance is crucial, as end-user interface responsiveness is one of the most important factors determining usefulness of the application. That kind of a database needs to handle inserting thousands of new records every hour. To achieve this usually the database is optimized for speed of Inserts, Updates and Deletes and for holding as few records as possible. So from a technical point of view most of the SQL queries issued will be INSERT, UPDATE and DELETE. Opposite to OLTP systems, a Data Warehouse is a system that should give response to almost any question regarding company performance measure. Usually the information delivered from a data warehouse is used by people who are in charge of making decisions. So the information should be accessible quickly and easily but it doesn't need to be the most recent possible and in the lowest detail level. Usually the data warehouses are refreshed on a daily basis (very...
Words: 1936 - Pages: 8
...[Type text] DATA WAREHOUSE Introduction In todays competitive and information packed age, management, executives and business users need well organized data for decision support and for the organizational planning. Decision Support Systems (DSS) were developed to aid executives and managers to better understand the information and facilitate at making better decisions. But DSS has its own limitations. Due to the information requirement and their complexity, it was difficult for Decision Support System to extract all necessary information from the data. Therefore to overcome this drawback, a new data storage system, called the Data Warehouse was developed. What is a Data Warehouse? A data warehouse is a relational database. It is not used for daily routine transaction processing. It contains remarkable (having great significance) data derived from the transaction data. It also contains data from other sources like external systems, or applications. It is designed for query and analysis. Copyright 2011. Do not copy or publish any part of this document without author's permission. [Type text] Data Warehouse Vs Traditional Database A database is a collection of the relational data. Database system comprises of a database and database software. Similarly, a data warehouse is a collection of relational data as well as supporting system. If we look closer at it, we will find that both the traditional database and the data warehouse have indexes, fields, keys, views...
Words: 2229 - Pages: 9
...Interorganizational systems • Systems that span organizational boundaries • e-Business applications – B2C – link businesses with their end consumers – B2B – link businesses with other business customers or suppliers • Electronic data interchange (EDI) systems 2 APPLICATION AREAS Intraorganizational systems 3 APPLICATION AREAS Intraorganizational systems (cont’d) 4 CRITICAL CONCEPTS Batch processing • A group (or batch) of transactions are accumulated, then processed all at one time 5 CRITICAL CONCEPTS Batch processing (cont’d) • In the early days of computers, all processes were batched • Major problem is the time delay before the master file is updated 6 CRITICAL CONCEPTS Online processing • Each transaction is entered directly into computer when it occurs 7 CRITICAL CONCEPTS Batch processing vs. Online processing • Significant delay before master • Master file updated within a file is updated (usually 1 day) fraction of a second • Much less expensive to operate • Much more expensive to operate Batch Processing Online Processing • Some applications naturally suited for batch processing (e.g., payroll) • Some applications need to be processed in real time Note: given the tradeoffs, hybrid systems that combine online data entry with batch processing are very common 8 CRITICAL CONCEPTS Online processing 9 CRITICAL CONCEPTS Functional information systems • Information systems that support one of an organization’s primary business...
Words: 2531 - Pages: 11
...* How the Internet works * Packet-switching technology Actual data transmission takes place sporadically, rather than continuously. Data to be transmitted is divided into small packets of information and labeled to identify the sender and recipient. These are sent over a network and then reassembled at their destination. If any packet did not arrive or was not intact, original sender requested to resend the packet. -> This enables millions to transmit data at the same time. * Understand the importance of standards (protocols) * What problems are and tasks are involved in networks and why are standards important for networks to run? Computers and applications of different kinds need to use the same network. Packets could get altered/lost/out of sequence. Many computers send packets simultaneously. There are lots of different destinations, routes, and sometimes some of them ‘close down’. * Bandwidth Bandwidth is transmission capacity of a computer or communications channel, measured in bits per second (bps). * TCP/IP protocol and layered standards of the Internet * Application layer, network layer (TCP/IP) Applications layer (e.g. HTTP, FTP) specifies how application programs communicate. Network layer (e.g. TCP/IP) consists of transport layer and internet layer. Transport layer (TCP) breaks, reassembles messages into packets. Internet layer (IP) specifies the address a packet is headed to. * Internet addressing and architecture:...
Words: 2137 - Pages: 9
