ISQS 6339 Business Intelligence Anatomy of Business Intelligence

ISQS 6339, Business Intelligence Anatomy of Business Intelligence Zhangxi Lin Texas Tech University

Outline • BI case study • BI framework, applications & tools • Data warehouse overview • Data warehouse architecture • Data integration 2

ISQS 3358 3 Control of Dems vs. Reps Big data Presidency data Primary results Election history

More applications ISQS 3358 4 • Premier Bank Card 3’ 46” • Mobile Business Intelligence with i. Phone 2’ 58” • Business intelligence with i. Pad 2’ 29”

• • What is the relationship between BI and MIS? What are main applications in BI? What are main BI technologies? How should we start with? • • Data collection. Dataset 1, 2, 3, 4, 5, 6, 7, 8 Data warehousing Data visualization Data analysis ISQS 3358 5 Questions

• 6 There are many different users who can benefit from business intelligence • Executives – Those who focus on the overall business • Business Decision Makers – Usually focused on single areas of the business (finance, HR, manufacturing, and so forth) • Information Workers – Typically managers or staff working in the back office • Line Workers – Employees who might use BI without knowing it • Analysts – Employees who will perform extensive data analysis ISQS 3358 Different Users of Business Intelligence 6

Different Users of Business Intelligence • 7 There are many different users who can benefit from business intelligence • Executives – Those who focus on the overall business • Business Decision Makers – Usually focused on single areas of the business (finance, HR, manufacturing, and so forth) • Information Workers – Typically managers or staff working in the back office • Line Workers – Employees who might use BI without knowing it • Analysts – Employees who will perform extensive data analysis 7

Business Intelligence BI Applications: - Data warehousing -Data mining - BPM - OLAP - etc. Data Business Analytics Executives Managers Operators BI Users Data Business Environment Decisions 8

BI vs. MIS • MIS were used by a select few in the organization, due to the efforts involved in collecting data and preparation of summaries from the same. Use of Computers in the MIS process helped in speeding up the process and increased its reach. • Then came the phase of BI • BI has been privilege of the TOP segment of the pyramid. • The cost involved prohibited the expansion of BI to the Middle tier Business users. • Operational BI is expected to do the task of making it available to masses, as they are the ones who need BI, more times in a day. 9

BI Framework, Applications & Tools

A Framework for Business Intelligence (BI) • The Business Value of BI • How BI Can Help • Assess their readiness for meeting the challenges posed by these new business realities • Take a holistic approach to BI functionality • Leverage best practices and anticipate hidden costs • Key Issues and Framework for BI Analysis • How can enterprises maximize their BI investments? • What BI functionality do enterprises need, and what are they using today? • What are some of the hidden costs associated with BI initiatives? 11

A Framework for Business Intelligence (BI) • BI’s Architecture and Components • Data Warehouse • Business Analytics • Automated decision systems • Performance and Strategy 12

A Framework for Business Intelligence 13

Main BI Topics • Data warehousing – Making historical data available for analytics • Data preparation – Extraction, transformation and loading • Query - a collection of specifications that enables you to focus on a particular set of data. • Online Analytical Processing (OLAP) - a capability of information systems that supports interactive examination of large amounts of data from many perspectives. • Reporting - generates aggregated views of data to keep the management informed about the state of their business. • Data mining - extraction of knowledge by utilizing software that can isolate and identify previously unknown patterns or trends in large amounts of data. • Visualization 14 14 ISQS 3358 Business Intelligence 14

Business Scorecards We see this everyday 15 15

The Purpose of a Scorecard • • • A scorecard should give an executive a visual representation of the health of an organization in a single glance The scorecard is of sufficiently high level to represent major business operations and their goals The data in a scorecard should be as recent as possible to make them more actionable 16 16

The Contents of a Scorecard • Scorecards usually contain some or all of the following elements: • • • Key Performance Indicators (KPIs) KPI actual values compared to historical values (for trend analysis) KPI actual values compared to a forecast or budget amount Rankings of different departments, locations, products, and so forth Developing KPIs and Scorecards with Share. Point 17 17

Dashboards 18 18

The Purpose of a Dashboard • • A dashboard is designed to allow decision makers to see a variety of data that affects their divisions or departments This data may be in the form of scorecards, charts, tables, and so forth The dashboard is generally customized for each user More targeted and detailed than a scorecard 19 19

The Contents of a Dashboard • • • A Dashboard generally contains a variety of different views of data The data is generally KPIs and shows trends, breakdowns, and comparisons against a forecast or historical data The dashboard often consists of charts and tables, and may include scorecard elements as well 20 20

Reports 21 21

The Purpose of Custom Application Integration • An application used by line workers may include business intelligence without the worker realizing what is happening • A sales clerk may get a list of targeted recommendations to make based on what the customer is buying • A loan officer may be presented with the level of risk associated with granting a loan to a particular customer 22 22

The Contents of Custom Application Integration • • • Custom applications may include predictive output from data mining models Custom applications can show history and trends for the current customer, supplier, and so forth Custom applications may allow easy ways for users to explore the data for relationships 23 23

Analytic Applications 24 24

The Purpose of Analytic Applications • • • Analytic applications free analysts from building complex models and writing complex queries Analysts are free to focus on the data and discover relationships and drivers behind numbers Rich visualizations allow much easier understanding of trends and relationships 25 25

The Contents of Analytic Applications • • • Analytic applications typically have no limits; analysts can see everything Analytic applications can view and analyze all of an organization’s data in a number of ways Analytic applications are powerful, but not as easy to use as other mechanisms 26 26

OLTP vs. OLAP • Online transaction processing systems (OLTP) Systems that handle a company’s routine ongoing business • Online analytic processing (OLAP) An information system that enables the user, while at a PC, to query the system, conduct an analysis, and so on. The result is generated in seconds 27 ISQS 3358 Business Intelligence 27

Visualization Cases • Economic Inequality 4’ 22” • Stock market performance 1’ 15” • Ocean Environment Animation 5’ 13” 28

Example 29

Example 30

BI Tools

BI Products & Providers • Traditional Providers • • • Microsoft SAS IBM Oracle Sy. Base Business Objects • Big Data solution providers • • • Cloudera Yahoo! IBM Amazon Google • BI Tools Survey 32 32 ISQS 3358 Business Intelligence 32

Structure and Components of Business Intelligence MS SQL Server 2016 SSMS SSIS SSAS BIDS SSRS SAS EG SAS EM 33

Data Warehouse Overview

• Data warehouse • Video – Overview of data warehouse 2’ 38” A physical repository where relational data are specially organized to provide enterprise-wide, cleansed data in a standardized format • Benefits of data warehouse 3’ 18” 35 Data Warehousing Definitions and Concepts

Data mart • Dependent data mart A subset that is created directly from a data warehouse • Independent data mart A small data warehouse designed for a strategic business unit or a department 36 • Definition A localized data warehouse that stores only relevant data to a department or even an individual

Data Warehousing Characteristics • • Subject oriented Integrated Time variant (time series) Nonvolatile (not allow to change) • Others • • • Web based Relational/multidimensional Client/server Real-time Include metadata 37 • Basic characteristics of data warehousing

Data Warehousing Process Overview • Organizations continuously collect data, information, and knowledge at an increasingly accelerated rate and store them in computerized systems • The number of users is constantly increasing. • The number of users needing to access the information continues to increase as a result of improved reliability and availability of network access, especially the Internet • The organization using data warehouse relied on DW more and more 38 • Data in DW are constantly accumulated.

• Operational data stores (ODS) A type of database often used as an interim area for a data warehouse, especially for customer information files • Enterprise data warehouse (EDW) A large-scale data warehouse used across the enterprise for decision support. It integrates different sources of information into a consolidated information system. • Metadata (Video 1’ 41”) Data about data. In a data warehouse, metadata describe the contents of a data warehouse and the manner of its use • Syntactic metadata, structural metadata, and semantic metadata 39 Data Warehousing More Concepts

40 Data Warehousing Process Overview

Data Warehousing Process Overview • • • Data sources Data extraction Data loading Comprehensive database Metadata Middleware tools 41 • The major components of a data warehousing process

Data Warehouse Architectures

• The data warehouse that contains the data and associated software • Data acquisition (back-end) software that extracts data from legacy systems and external sources, consolidates and summarizes them, and loads them into the data warehouse • Client (front-end) software that allows users to access and analyze data from the warehouse 43 Three Parts of Data Warehouse

44 Three-Tier Data Warehouse

45 Alternative Data Warehouse Architectures (1)

46 Alternative Data Warehouse Architectures (2)

47 Alternative Data Warehouse Architectures (3)

48 Alternative Data Warehouse Architectures (4)

49 Alternative Data Warehouse Architectures (5)

50 Architectures Comparison

51 Teradata’s EDW

Data Integration

Data Integration • Integration that comprises three major processes: • data access, • data federation, and • change capture. • When these three processes are correctly implemented, data can be accessed and made accessible to an array of ETL and analysis tools and data warehousing environments • ETL Tools 4’ 56” 53

Data Integration • Enterprise application integration (EAI) A technology that provides a vehicle for pushing data from source systems into a data warehouse, including application functionality integration. Recently serviceoriented architecture (SOA) is applied • Enterprise information integration (EII) An evolving tool space that promises real-time data integration from a variety of sources, such as relational databases, Web services, and multidimensional databases • Extraction, transformation, and load (ETL) A data warehousing process that consists of extraction (i. e. , reading data from a database), transformation (i. e. , converting the extracted data from its previous form into the form in which it needs to be so that it can be placed into a data warehouse or simply another database), and load (i. e. , putting the data into the data warehouse) 54

Data Mart - The IMW Case IMW, standing for Internet Media Works!, is an ASP in real estate information services. It is headquartered in Austin, Texas. CEO is Gary Anderson. Web page: http: //www. inetworks. com

Why need Data Mart? • Data mart complements the centralized data warehousing based on UDM model, for the situations where UDM cannot be used • • Legacy databases Data are from nondatabase sources No physical connection the centralized data warehouse Data are not clean 56

Data Mart Structures • Fact tables • Measures • Dimension tables • Dimensions and Hierarchies • Attributes (or columns) • Dimensional modeling – Stars and Snowflakes 57

Measures • A numeric quantity expressing some of the organization’s performance. The information represented by this quantity is used to support or evaluate the decision making and performance of the organization. • A measure is also called a fact • The table holding measure information is called as a fact table • Dimensions vs. Measures 2’ 38”

Commrex Real Estate Operational Database • Users: property listors, webmaster, marketing manager of IMW • Objective: Encourage realtors to use the online ASP services with the best information services to increase IMW’s revenue. • Value Chain • Listors create their account • Listors post their real estate properties to the web-based database services and pay listing fees • Property buyers search the website-based database and buy properties from listors. This is the incentive for listors to use the ASP services • Business Processes • Listor sign up • Listor account management • Property data posting • Property search • Property database maintenance 59 59

IMW’s Database ERD Model Property Listing Database Transaction. ID M: 1 User. ID Property ID Prop. ID Listor ID M: M Property Type Membership Database M: 1 Listor ID Listor Name Property Type Name Address Company ID Subtype 1 City Subtype 2 Chapter Update. Date Feature Subtype n Legends Primary Key M: M Functions Specializations Company ID Comp Name Address Secondary Key Telephone # Link to a table ISQS 6339, Data Mgmt & BI, Zhangxi Lin 60

Commrex Data Warehousing • Users: CEO of IMW, IMW business analyst, IMW marketing manager • Analytic themes • Fast retrieval of business key performance indicators (KPIs) • Decision making on business promotions • Applications • Geographic distribution of property listings • Scorecard for main performance indicators • Dashboard • Questions • How to model data warehouse? • What are required in data transformation and preprocessing? • Any missing dimension for data ware housing? • How to perform routine data warehouse updates – frequency, timing, etc.

IMW’s Data Warehouse Dimensional Model Property Listing Fact Property Type Dimension Membership Dimension Property ID Listor ID Listor Name Prop. Type Sub. Name Address Company ID City Chapter … Update. Date Functions Features Specializations Year Legends Primary Key Company ID Quarter Comp Name Month Date Secondary Key Company Dimension Address Telephone # Link to a table ISQS 6339, Data Mgmt & BI, Zhangxi Lin 62

Exercise 1 – Walk through data warehousing process • Learning Objectives • To gain a general impression how to use SQL Server 2008 to implement a data mart • Tasks • • • Create your database with SSMS, named as ISQS 6339_lastname Import data from Commrex_2011. xls Use SSMS to create a ERD diagram Create a SSAS project using BIDS Define data source, data source view, and cube • Deliverable: • One-page printout of the screenshot of the cube diagram 63