Agenda: 01/30/2014

1. Agenda: 01/30/2014 • Discuss class administration items • Website and WebCampus Use • Declaration of teams for Internal Data Project #1 (by 2/6) • Structure of class: Skills (doing) and Concepts (knowing) • Today • Concepts: Data warehousing systems • Skills: Review of transaction database design in preparation for learning data warehouse design next week

2. Purpose of the course readings • Business Intelligence: A Managerial Perspective (BIM) • Introduce and explain concepts • Provide examples • Delivering Business Intelligence (DBI) • Learn how to use SQL Server business intelligence products • Learn a little conceptual information about data warehousing systems • Readings on WebCampus (numbered) • Learn specific skills not in BIM or DBI. For example readings #1 and #2 focus on learning how to design a data warehouse. • Learn concepts not in BIM or DBI. For example, reading #4 discusses information visualization methods.

3. A Business Intelligence “System” • A business intelligence system encompasses all processes, hardware and software necessary to extract data, transform it, integrate it, save it, and provide information made accessible by users to support decision making. • Some people equate the terms “data warehousing system” and “BI system”. Others believe that a data warehousing system is a type of BI system, using BI system as the umbrella term.

4. What are the components of BI system? • Data warehouse • Structured, unstructured, internal, external, transaction-level, and derived data. • Data storage repository. • Extract, transform and load methods • Methods of loading accurate and consistent data into the data warehouse. • Methods of integrating data from disparate sources. • Metadata repository • Data definitions and meanings. • Business rules and process decisions. • Analytical tools • OLAP: Online Analytical Processing • Statistical analysis. • Data Mining. • Data Visualization/End-User Presentation Tools • Dashboards. • Graphics, tables, pictures.

6. Our example data warehousing system is SQL Server 2012 • SQL Server 2012 offers an integrated set of products to create a data warehousing system. • Data warehouse: SQL Server Database Base • ETL Product: SQL Server Integration Services • OLAP Product: SQL Server Analysis Service • Visualization Product: SQL Server Reporting Services

7. Reporting Services (SSRS) SQL Server database (SSDB) Integration Services (SSIS) AnalysisServices(SSAS)

8. What are the steps of the tutorials? • Build the database through Management Studio. • Populate the database through SSIS. • Create a data mart “cube” with SSAS. • Look at the “cube” with SSRS. • Look at the “cube” with a pivot table in Excel. • Create a visualization method with Tableau Software. • Use the SQL Server data mining tools.

9. Data warehouse system architecture • Continuum of choices. • Basic architectural issues: • Where is the data stored? • Within the operational data stores and then concatenated on the fly? • Within a centralized data warehouse that is optimized for decision making? • Within a series of data marts? • When is the data cleaned (or whether the data is cleaned at all)? • How is the data accessed?

10. Data mart extraction data warehouse

11. Two-tier data warehouse architecture

12. Three-tier data warehouse architecture

14. What is an operational data store? • An operational data store consolidates data from multiple source systems and provides a near real-time, integrated view of volatile, current data. • Its purpose is to provide integrated data for operational purposes. It has add, change, and delete functionality. • Sometimes they are created to avoid a full blown ERP implementation.

15. Factors that may affect the architectural decision • Information interdependence among organizational units • Upper management’s information needs • Urgency of need for a data warehouse • Nature of end-user tasks • Identified role of the data warehouse prior to implementation • Compatibility with existing systems • Perceived ability of the in-house IT staff • Social/political factors

16. Remember the five components? • Data warehouse • Structured, unstructured, internal, external, transaction-level, and derived data. • Data storage repository. • Extract, transform and load methods • Methods of loading accurate and consistent data into the data warehouse. • Methods of integrating data from disparate sources. • Metadata repository • Data definitions and meanings. • Business rules and process decisions. • Analytical tools • OLAP: Online Analytical Processing • Statistical analysis. • Data Mining. • Data Visualization/End-User Presentation Tools • Dashboards. • Graphics, tables, pictures.

17. What is a data warehouse? • A data warehouse is a collection of data gathered into a database specifically designed to support decision making. • Types of decisions supported by data warehouses: • Operational • Short-term • Long-term • Strategic • An organization may have one or may have multiple data warehouses designed to suit multiple applications and/or decisions.

18. Characteristics of a data warehouse • Subject-oriented. • Integrated. • Time-variant. • Non-volatile.

19. Other potential characteristics of DW • Summarized (or not...) • Not normalized (or normalized...) • Web based (or not...) • Real-time (or batch...) • Single version of truth (or one of many...) • Enterprise-wide (or not…)

20. What is an enterprise data warehouse? • A data warehouse that is created to encompass multiple subject areas. • Is usually normalized. • Can be used for decision making in multiple organizational areas.

21. What is a data mart? • A data mart stores data for a limited number of subject areas. • An “independent” data mart is loaded directly from operational data. • A “dependent” data mart is loaded from an enterprise data warehouse. • Usually not normalized.

22. SQL Server Database (SSDB) • Relational database management system. • Aligns with rules of a relational DBMS. • Transact-SQL. • Includes metadata repository. • SQL Server Management Studio. • Accessible from UNR COB labs through remote desktop; a college resource rather than a university resource. • Remote desktop server: sts.coba.unr.edu • SQL Server instance: ISSQL\students

23. MaxMin and SQL Server BI (DBI pg. 21)

24. DBI: pg. 108

25. BI Tutorial #1: Building the database • Can use SQL CREATE statements or follow the wizard instructions in the book. • Issues to be aware of: • No constraints other than primary keys. • Referential integrity is not maintained.

26. Metadata repository • A metadata repository contains information about all data objects stored in the data warehouse. • Contains the following segments: • Business segment: describes the business definition of the data element. This is frequently the context/meaning for the data element. • Technical segment: describes the computer-related technical properties of each element (size, data type, unit of measure, etc.). • Process segment: describes how the element is processed before being placed in the data warehouse. • Usage segment: describes the relative usage of the element including who accesses it and how often and in what manner. Used for performance tuning.

27. Extract, Transform, Load (ETL) • Extract • Take data from source systems. • May require middleware to gather all necessary data. • Transformation • Put data into consistent format and content. • Validate and fix data – check for accuracy, consistency using pre-defined and agreed-upon business rules. • Convert data as necessary. • Load • Use a batch (bulk) update operation that keeps track of what is loaded, where, when and how. • Keep a detailed load log to audit updates to the data warehouse.

28. Online analytical processing tools • Provides multi-dimensional data analysis techniques. • Works primarily with data aggregation. • Provides advanced statistical analysis. • Provides advanced graphical output. • Supports access to very large databases. • Provides enhanced query optimization algorithms. • The key objective of basic OLAP functionality is to speed up query processing.

29. Data mining tools • Data mining tools: • analyze the data; • uncover patterns hidden in the data; • form computer models based on the findings; and • use the models to predict business behavior. • Proactive tools, used for prediction and discovery of behavior. • Some are based on standard statistical tools of correlation, regression, factor analysis and structural equation modeling. • Most are based on artificial intelligence software such as decision trees, neural networks, fuzzy logic systems, inductive nets and classification networking.

30. Contrast between OLAP and Data Mining Decision Support Questions

31. Data visualization/End-User Presentation • Text portrayal of data. • Tables. • Graphical portrayal of data. • Graphics include: • Standard graphics (bar chart, pie chart, line chart, etc.) • Pictures • Scatter diagrams combined with pictures. • Animation. • Cool 3-D images… • Audio. • Video.

33. Tools • Excel!! • Query generators • Report writers • Dashboards (we will use Tableau Software) • A very long list of possibilities from a very long list of ever-evolving vendors!