Databases and Data Warehouses: Supporting the Analytics-Driven Organization

1. Databases and Data Warehouses: Supporting the Analytics-Driven Organization

2. LEARNING OUTCOMES List and describe the key characteristics of a relational database. Define the 5 software components of a DBMS. List and describe the key characteristics of a data warehouse. Define the 5 major types of data-mining tools. List key considerations in information ownership.

3. INTRODUCTION • Businesses use many IT tools to manage and organize information • Online transaction processing (OLTP) – gathering and processing information and updating existing information to reflect the processed information • Online analytical processing (OLAP) – manipulation of information to support decision making

4. INTRODUCTION • OLTP • Supports operational processing • Sales orders, accounts receivable, etc • Supported by operational databases & DBMSs • OLAP • Helps build business intelligence • Supported by data warehouses and data-mining tools

5. OLTP, OLAP, and Business Intelligence

6. How are OLTP and OLAP possible? Database Structures At its most basic level, a database is a collection of individual data items that are stored in a highly structured way that represents a model of reality. This collection of data items can be stored in a single location or hard drive or distributed across large networks with components in many physical locations. Regardless of where the data is physically stored, the database acts as a single entity to anyone or any system that makes use of it.

7. What are some of the advantages to having databases? A database can help in a variety of ways including: Sharing: different departments share the same data Security: databases can be used to restrict access Less redundancy: multiple copies of files are stored in one database Better integrity: changes are made in one place, improving the accuracy and relevancy of the data

8. Data Administration Subsystem • Backup and recovery – for backing up information and restarting (recovering) from a failure • Backup – copy of information on a computer • Recovery – process of reinstalling the backup information in the even the information was lost

9. Data Administration Subsystem Security management – for CRUD access – create, read, update, and delete Query optimization – to minimize response times for large, complex queries Reorganization – for physically rearranging the structure of the information according to how you most often access it

10. Database Characteristics Collections of information Created with logical structures Include logical ties within the information Include built-in integrity constraints

11. Database

12. Database

13. Interoperability • Interoperability is technology dependent • Accuracy • Standardized • Dependability • Accessibility

14. Interoperability The need for interoperability systems is evident in every organization.

15. Interoperability • Interoperability requires: • Creation • Acceptance • Implementation of data

16. Interoperability • Interoperability depends upon two important concepts: • Syntax • Semantics

17. Categories of Standards

18. Categories of Standards These standards are set by the organization who use them.

19. Standards

20. Standards Reality Use national standards Be informed leaders Build a collaborative culture Explore alternative financing models Use incentives to drive adoption Cultivate staff and knowledge

21. Database Management System

22. DATABASE MANAGEMENT SYSTEM TOOLS Database management system (DBMS) – helps you specify the logical requirements for a database and access and use the information in a database

23. DBMS Engine • DBMS engine – accepts logical requests and converts them into the physical equivalents, and access the database and data dictionary on a storage device • Physical view – how information is physically arranged, stored, and accessed on a storage device • Logical view – how you need to arrange and access information to meet your needs

24. 5 Components of a DBMS DBMS engine Data definition subsystem Data manipulation subsystem Application generation subsystem Data administration subsystem

25. Types of DBMS Structures Hierarchical Database Network Database Relational Database Multidimensional Database Object-Oriented Database

26. Relational Database

27. RELATIONAL DATABASE MODEL • Database – collection of information that you organize and access according to the logical structure of the information • Relational database – series of logically related two-dimensional tables or files for storing information • Relation = table = file • Most popular database model

28. Database – Collection of Information

29. Database – Created with Logical Structures Before you can enter information into a database, you must define the data dictionary for all the tables and their fields. For example, when you create the Truck table, you must specify that it will have three pieces of information and that Date of Purchase is a field in Date format. Data dictionary – contains the logical structure for the information in a database

30. Database – Logical Ties within the Information Customer Number is the primary key for Customer and appears in Order as a foreign key Primary key – field (or group of fields) that uniquely describes each record Foreign key – primary key of one file that appears in another file

31. Database – Logical Ties within the Information

32. Databases – Built-In Integrity Constraints • Integrity constraints – rules that help ensure the quality of information • Data dictionary, for example, defines type of information – numeric, date, and so on • Foreign keys – must be found as primary keys in another file • E.G., a Customer Number in the Order Table must also be present in the Customer Table

33. Data Definition Subsystem • Data definition subsystem – helps you create and maintain the data dictionary and structure of the files in a database • The data dictionary helps you define… • Field names • Data types (numeric, etc) • Form (do you need an area code) • Default value • Is an entry required, etc

34. Data Manipulation Subsystem Data manipulation subsystem – helps you add, change, and delete information in a database and query it to find valuable information Most often your primary interface Includes views, report generators, query-by-example tools, and structured query language

35. View View – allows you to see the contents of a database file, make changes, and query it to find information

36. Report Generator Report generator – helps you quickly define formats of reports and what information you want to see in a report

37. Query-by-Example Tool QBE tool – helps you graphically design the answer to a question

38. Structured Query Language SQL – standardized fourth-generation query language found in most DBMSs Sentence-structure equivalent to QBE Mostly used by IT professionals

39. Application Generation Subsystem • Application generation subsystem – contains facilities to help you develop transaction-intensive applications • Mainly used by IT professionals

40. Data Administration Subsystem • Data administration subsystem – helps you manage the overall database environment by providing facilities for… • Backup and recovery • Security management • Query optimization • Reorganization • Concurrency control • Change management

41. Data Administration Subsystem Concurrency control – what happens if two people attempt to make changes to the same record Change management – how will structural changes impact the overall database

42. DATA WAREHOUSES AND DATA MINING Help you build and work with BI and some forms of knowledge Data warehouse – collection of information (from many places) that supports business analysis activities and decision making

43. Data Warehouse Characteristics • Multidimensional • Rows, columns, and layers • Support decision making, not transaction processing • Contain summaries of information • Not every detail

44. The Tool Set of the Analytics Professional • Data-mining tools – software tools you use in a data warehouse environment • Query-and-reporting tools • Artificial intelligence • Multidimensional analysis tools • Digital dashboards • Statistical tools

45. The Tool Set of the Analytics Professional

46. The Tool Set of the Analytics Professional • Query-and-reporting tools – similar to QBE tools, SQL, and report generators • Artificial intelligence – tools to help you “discover” information and trends (more in Chapter 4) • Multidimensional analysis (MDA tools) – slice-and-dice techniques for viewing multidimensional information

47. The Tool Set of the Analytics Professional • Digital dashboard – displays key information on a computer screen tailored to the needs and wants of an individual • Key performance indicator (KPI) – most essential information used in any analytics initiative • Statistical tools – for applying mathematical models to data warehouse information

48. Digital Dashboard

49. The Analytics Life Cycle

50. Extraction, Transformation, and Loading (ETL) • ETL is a three-step process • Extract needed information from its source • Transform the data into a standardized format • Load the transformed data into a data warehouse