bidw L01 BI Introduction

Business Intelligence Introduction to Business Intelligence : 

1 Business Intelligence Introduction to Business Intelligence

Lecture 1 objectives:: 

Lecture 1 objectives: Understand today’s turbulent environment and describe how organisations survive in such environment Understand the need fort computerised support of managerial decision making Describe the BI methodology and concepts and relate them to DSS (Decision support systems) Understand the issues in implementing BI 2

A Brief History of Information Technology: 

3 A Brief History of Information Technology The “dark ages”: paper forms in file cabinets Computerized systems emerge Initially for big projects like Social Security Same functionality as old paper-based systems The “golden age”: databases are everywhere Most activities tracked electronically Stored data provides detailed history of activity The next step: use data for decision-making The focus of this course! Made possible by omnipresence of IT Identify inefficiencies in current processes Quantify likely impact of decisions

Databases for Decision Support: 

4 Databases for Decision Support 1 st phase: Automating existing processes makes them more efficient. Why? Automation → Lots of well-organized, easily accessed data 2 nd phase: Data analysis for better decision-making. Why? Analyze data → better understanding → better decisions “Data Entry” vs. “Thinking” Data analysts are decision-makers: managers, executives, etc.

Question: 

Question Fact: organisations have more and more data. The amount of data the average business collects and stores is doubling every 12-18 months Question: Why there is such thing as “ information crisis ”? 5

Information crisis: 

6 Information crisis Organizations have lots of data. Information crisis IT systems are not effective at turning all data into useful information Operational data is event- driven Operational data is not directly suitable from different viewpoints

The need to make better decisions: 

The need to make better decisions Why? Competitive market! Decision are made concerning production, marketing and personnel making a decision on a difficult task is hard It can lead to success or failure Not being able to understand data and have the right information will affect the selection of a good decision 7

Effective decision making: 

Effective decision making Key ingredients: Set goals to work toward Goals must contain specific targets Find a way to measure whether a chosen course is moving toward the goals Provide means to measure Information based on those measurements must be provided to the decision maker in a timely manner 8

Decision support systems: 

Decision support systems Gained popularity in 80-s and 90s Targeted the large-scale organisations that needed help with large amounts of data Government , health system, car industries Early challenges: Customizability - tools developed from scratch Multiple vendors – mix of software, hardware, networking Uniqueness – no “how-to” guides Long deployments Expensive 9

What is BI?: 

What is BI? “The delivery of accurate, useful information to the appropriate decision makers within the necessary timeframe to support effective decision making.” Brian Larson “Data analysis, reporting and query tools can help business users wade through a sea of data to synthesize valuable information from it – today these tools collectively fall into a category called ‘Business Intelligence’” Gartner Group 10

Some more definitions: 

Some more definitions “ An umbrella term that includes architectures, tools, data-bases, applications and methodologies” ( Raisinghani , 2004) “The skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context” ( wikipedia ) A category of applications, practices and presentations that help users make sense of a mountain of data 11

BI – no exact definition: 

BI – no exact definition BI -a synonym for competitive intelligence. The action of defining, gathering, analyzing, and distributing Intelligence about products, customers, competitors and any aspect of the environment needed to support executives and managers in making strategic decisions for an organization It means different things to different people. Confused? Lots of acronyms and buzzwords E.g. BPM- business performance management. 12

Why BI?: 

Make more informed business decisions: Competitive and location analysis Customer behavior analysis Targeted marketing and sales strategies Business scenarios and forecasting Business service management Business planning and operation optimization Financial management and compliance Why BI?

Types of BI questions: 

Types of BI questions When we know what we are looking for Layout–led discovery We know the question and we know where to look for answers Reports for the retrieved data Data-led Discovery We know the question but we don’t know where to look for answers ( Eg Higher data level ) Interactive environment that enables the user to navigate at will. Discovering new questions Trends, correlations and dependencies that the user is not aware at first - Data mining 14

BI goals at many levels: 

BI goals at many levels 15 Long Term goals Short Term goals Daily Operational goals Upper management Mid-level management Forepersons, managers, TeamLeaders

BI measures at many levels: 

BI measures at many levels 16 Highly Summarised KPI Summarised data with drill down Detail level data Upper management Mid-level management Forepersons, managers, TeamLeaders

BI main objectives…: 

BI main objectives… Enable interactive access to data (lots) Enable manipulation of this data Provide business managers and analysts the ability to conduct appropriate analysis so they can make the right decision at the right time “By analyzing historical and current data, situations and performances, decision makers get valuable insights upon which they can base more informed, better decisions” (Zaman 2005) 17

What BI does…: 

What BI does… Transforms: 18 Data Actions Information Decisions Question: Is any data good enough for BI?

Importance of data quality: 

19 Importance of data quality Data integrity Must conform to the business rules Data accuracy Data accessibility Intuitive access paths, responsive for the analysis Data precision Data credibility/reliability Every business factor must have one and only one value Data timely Must be available within the stipulated time frame Data format

What efficient BI does…: 

20 What efficient BI does… Quality data Quality decisions Quality information Quality BI/DW Transforms: Managers need the right information at the right time and in the right place.

Note that …: 

21 Note that … Source data quality will be only as good as the enforcement of quality processes in the operational systems. Mandatory quality processes should include data entry rules and edit checks in programs. If those processes are not enforced or do not exist, data usually gets corrupted, regardless of whether the data is in a relational database or in an old flat file.

Components of BI: 

Components of BI Database development and administration Data mining Data queries and report writing Data analytics and simulations Benchmarking of business performance Dashboards Decision support systems 22

Gartner Business Intelligence Predictions: 

Through 2012, more than 35 % of the top 5,000 global companies will regularly fail to make insightful decisions about significant changes in their business and markets By 2012, business units will control at least 40% of the total budget for BI By 2010, 20% of organizations will have an industry-specific analytic application delivered via software as a service (SaaS) as a standard component of their BI portfolio By 2012, one-third of analytic applications applied to business processes will be delivered through coarse-grained application mashups Gartner Research, Jan 2009, http://www.gartner.com/it/page.jsp?id=856714 Gartner Business Intelligence Predictions

Technologies Supporting BI: 

Database systems and database integration Data warehousing, data stores and data marts Enterprise resource planning (ERP) systems Query and report writing technologies Data mining and analytics tools Decision support systems Customer relation management software Product lifecycle and supply chain management systems Technologies Supporting BI

Moving the Control of BI into the Hands of the Users: BI 2.0: 

Leveraging new Web 2.0 technologies to: Enhance the presentation layer and data visualization Provide information on-demand and greater customization Increase ability to create corporate and public data mashups Allow interactive user-directed analysis and report writing Moving the Control of BI into the Hands of the Users: BI 2.0

Examples of BI Careers: 

BI careers cross over all industries: BI solution architects and integration specialists Business and BI analysts BI application developers and testers Data warehouse specialists Database analysts, developers and testers Database support specialists Examples of BI Careers

BI Skill and Knowledge Clusters: 

Database theory and practice Data mining and relational report writing Enterprise data and information flow Information management and regulatory compliance Analytical processing and decision making Data presentation and visualization BI technologies and systems Value chain and customer service management Business process analysis and design Transaction processing systems Management information systems BI Skill and Knowledge Clusters

Critical Technology Knowledge and Skills: 

Knowledge of database systems and data warehousing technologies Ability to manage database system integration, implementation and testing Ability to manage relational databases and create complex reports Knowledge and ability to implement data and information policies, security requirements, and state and federal regulations Critical Technology Knowledge and Skills

Critical Business and Customer Skills and Knowledge: 

Understanding of the flow of information throughout the organization Ability to effectively communicate with and get support from technology and business specialists Ability to understand the use of data and information in each organizational units Ability to present data in a user-centric framework Ability to understand the decision making process and to focus on business objectives Ability to train business users in information management and interpretation Critical Business and Customer Skills and Knowledge

Multidimensional Analysis: 

For rapid analysis and display of large amounts of data: On-Line Analytical Processing (OLAP) Multidimensional/ hyper cubes OLAP operations: Slice, Dice, Drill Down/Up, Roll-up, Pivot OLAP vendors and products Multidimensional Analysis

Data Warehousing: 

Basics of data warehousing design and management Data warehouse architectures Data marts and data stores Data structures and data flow Dimensional modeling Extract, clean, conform and deliver Server management tools to package, backup and restore Database server activity monitoring and performance optimization Data Warehousing

Data Mining: 

Data mining: the extraction of predictive information from large databases. Data trend, connection and behavior pattern analysis Data quality Data mining tools Predictive and business analytics Descriptive and decision models Statistical techniques and algorithms Data Mining

Data Visualization: 

Data representations Information graphics Data representation techniques and tools Visual representation – trends and best practices Interactivity in data representation Tools and applications The user perspective on information presentation http://www.smashingmagazine.com/2007/08/02/data-visualization-modern-approaches/ Data Visualization

Working with Business and User Requirements: 

Capturing and documenting the business requirements for BI solution Translating business requirements into technical requirements BI project lifecycle and management Key Performance Indicators (KPIs), actions, and stored procedures User education and training Data-based decision making Effective communication and consultation with business users Working with Business and User Requirements

Sample BI Role: : 

Business Intelligence (BI) Specialist works with business users to obtain data requirements for new analytic applications, design conceptual and logical models for the data warehouse and/or data mart communicate physical designs to the database group. develops processes for capturing and maintaining metadata from all data warehousing components. Sample BI Role:

Sample BI Role: : 

Business Intelligence Developer responsible for designing and developing Business Intelligence solutions for the enterprise. works on-site at the corporate head quarters. Key functions include designing, developing, testing, debugging, and documenting extract, transform, load (ETL) data processes and data analysis reporting for enterprise-wide data warehouse implementations. Responsibilities include: working closely with business and technical teams to understand, document, design and code ETL processes; working closely with business teams to understand, document and design and code data analysis and reporting needs; translating source mapping documents and reporting requirements into dimensional data models; designing, developing, testing, optimizing and deploying server integration packages and stored procedures to perform all ETL related functions; develop data cubes, reports, data extracts, dashboards or scorecards based on business requirements. Sample BI Role:

Sample BI Role: 

The Business Intelligence Report Developer responsible for developing, deploying and supporting reports, report applications, data warehouses and business intelligence systems. Primary responsibilities include creating and automating quality control processes and methods, providing maintenance and enhancement of data warehouse reports, creating ad hoc data warehouse queries, solving data related reporting issues and documenting all reports created. must have experience in user facing roles (e.g. gathering requirements, establishing project objectives, leading meetings) and in developing, selecting and conducting user training as needed. also participates in all aspects of data warehouse projects including conceptualization, design, construction, testing, selection, deployment and post-support implementation. Sample BI Role

Architecture of BI: 

Architecture of BI 38 Data Warehouse Environment Business Analytics Environment Performance and Strategy Data sources Technical staff Business users Managers BPM strategies User Interface: Browser, Portal, Dashboard Build the Data Warehouse D W Access Results Future component: Intelligent systems Artificial BI ?

Data warehouse: 

Data warehouse Used for medium/large BI systems Special db or repository of data prepared to support decision-making applications Includes historical data Organised and summarized Lately it includes current data, as well Why? Real time decision support 39

Business analytics: 

Business analytics A collection of tools for manipulating and analyze the data in the DW Reports and queries Advanced analytics Statistical, financial and other models Originally known as OLAP – OnLine Analytical Processing Includes Data mining process of searching unknown relationships or information in large DBs or DWs using intelligent tools (e.g. neural computing) 40

Business Performance Management: 

Business Performance Management Also referred as Corporate performance management CPM – another acronyms just for you  Objective: optimise the overall performance of an organisation Monitor performance Compare it to standards and goals Shows performance graphically Link top-level metrics, like the financial information created by the CFO with the actual performances Usually on dashboards Not really covered in this course 41

Benefits of BI: 

Benefits of BI Faster, more accurate reporting Improved decision making by having accurate, current and relevant information available Improved customer service Increased revenues Time savings Single version of truth Improved strategies and plans answers to those "what if" questions Find latent problems by building a picture of the information you can't see 42

What BI is not: 

What BI is not Transaction processing - OLTP The online transactional processing systems handle a company’s routine ongoing business. 43

OLTP vs. OLAP: 

44 OLTP vs. OLAP OLTP: On Line Transaction Processing Describes processing at operational sites (sources) OLAP: On Line Analytical Processing Describes processing at warehouse CS 245 Notes12 44

OLTP vs. OLAP: 

45 OLTP vs. OLAP Mostly updates Many small transactions Mb-Tb of data Raw data Up-to-date data Consistency, recoverability critical Clerical users Mostly reads Queries long, typically complex aggregations Gb-Tb of data Summarized, consolidated data Decision-makers, analysts as users OLTP OLAP

OLTP vs. OLAP 2: 

46 OLTP vs. OLAP 2 OLTP: Queries touch small amounts of data (one record or a few records) Updates are frequent Concurrency is biggest performance concern Used to run day-to- day core business “bread and butter” Put data IN the database OLAP: Queries touch large amounts of data Updates are infrequent Individual queries can require lots of resources Watch how the business runs Info for strategic decisions Information OUT of the database

OLTP vs. OLAP: 

47 OLTP vs. OLAP OLTP Many short transactions (queries + updates) Examples: Update account balance Enroll in course Add book to shopping cart Take an order Process payment Generate invoice OLAP : Long transactions, complex queries Examples: Report total sales for each department in each month Identify top-selling books Count classes with fewer than 10 students Tell me why (drill down) Let me see other related data (drill across)

Question: 

Question Can you give another 3 examples (each) of OLTP and OLAP transactions? 48

Why OLAP & OLTP don’t mix (1): 

49 Why OLAP & OLTP don’t mix (1) Transaction processing (OLTP): Fast response time important (< 1 second) Data must be up-to-date, consistent at all times Data analysis (OLAP): Queries can consume lots of resources Can saturate CPUs and disk bandwidth Operating on static “snapshot” of data usually OK OLAP can “crowd out” OLTP transactions Transactions are slow → unhappy users Example: Analysis query asks for sum of all sales Acquires lock on sales table for consistency New sales transaction is blocked Different performance requirements

Why OLAP & OLTP don’t mix (2): 

50 Why OLAP & OLTP don’t mix (2) Transaction processing (OLTP): Normalized schema for consistency Complex data models, many tables Limited number of standardized queries and updates Data analysis (OLAP): Simplicity of data model is important Allow semi-technical users to formulate ad hoc queries De-normalized schemas are common Fewer joins → improved query performance Fewer tables → schema is easier to understand Different data modeling requirements

Why OLAP & OLTP don’t mix (3): 

51 Why OLAP & OLTP don’t mix (3) An OLTP system targets one specific process For example: ordering from an online store OLAP integrates data from different processes Combine sales, inventory, and purchasing data Analyze experiments conducted by different labs OLAP often makes use of historical data Identify long-term patterns Notice changes in behavior over time Terminology, schemas vary across data sources Integrating data from disparate sources is a major challenge Analysis requires data from many sources

Data Warehouses: 

52 Data Warehouses Doing OLTP and OLAP in the same database system is not often practical (or is it?) Different performance requirements Different data modeling requirements Analysis queries require data from many sources Solution: Build a “data warehouse” Copy data from various OLTP systems Optimize data organization, system tuning for OLAP Transactions aren’t slowed by big analysis queries Periodically refresh the data in the warehouse

What is a Data Warehouse ? : 

53 What is a Data Warehouse ? Simple concept: Take all data from organisation Clean it Transform it Provide strategic information. An Environment, Not a Product For data analysis and decision support Flexible 100% user driven Ask-answer-ask-again pattern Complex, unpredictable questions

What is a Warehouse?: 

54 What is a Warehouse? A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Contains historical data derived from transaction data Can include data from other sources Enables an organization to consolidate data from several sources

Data Warehouse Definition: 

55 Data Warehouse Definition The Data Warehouse is an integrated, subject-oriented, time-variant, non-volatile database that provides support for decision making. [W Inmon]

Integrated data: 

56 Integrated data From several operational systems. Different databases Files, etc How you integrate data before storing it in DW: Inconsistencies must be removed data from disparate sources into a consistent format Standardize the data elements resolve such problems as naming conflicts and inconsistencies among units of measure Transformation, consolidation

Data Integration is Hard – why?: 

57 Data Integration is Hard – why? Data warehouses combine data from multiple sources Data must be translated into a consistent format Data integration represents ~80% of effort for a typical data warehouse project! Some reasons why it’s hard: Metadata is poor or non-existent Data quality is often bad Missing or default values Multiple spellings of the same thing (Cal vs. UC Berkeley vs. University of California) Inconsistent semantics What is an airline passenger? What is 1 + 1? What is a customer?

Subject Oriented Data: 

58 Subject Oriented Data Example: You need to build a DW to accommodate one of the questions of the company’s manager is: "Who was the best customer for a specific item in a specific year?" Organized along the lines of the subjects of the corporation. Subjects critical for the enterprise

Question: 

Question Q: What typical subjects for a DW can you think of? customer, product, vendor etc 59

Time-Variant Data: 

60 Time-Variant Data To discover trends in business, we need large amounts of data OLTP :Data contains the current values DW focuses on change over time: Every record in the data warehouse has some form of time variance attached to it. Allows for analysis in the past Relates information to the present Enables forecasts for the future

Question: 

Question Q: Can you explain why, generally, OLTP contains only current values ? 61

Non-Volatile Data: 

62 Non-Volatile Data Refers to the inability of data to be updated. Every record in the data warehouse is time stamped in one form or another Snapshot over time The purpose of a data warehouse is to enable you to analyze what has occurred

PowerPoint Presentation: 

For an organisation: Q1: Where do you add/change/delete data? Q2: Where do you read the data from? 63 Question

DW –simple concept, but…: 

64 DW –simple concept, but… Not easy to build Some people think that a warehouse is a data store that contains all data within the enterprise, is built within a couple of weeks, and thousands of people can use it for years to come without any additional effort or expense.

Data Warehouse Lifecycle : 

65 Data Warehouse Lifecycle Determine the reports that DW is supposed to support. Identify data sources. Extract data from their transactional sources. Populate a staging area with the data extracted from transactional sources. Build and populate a dimensional database. Build Extraction Transformation and Loading (ETL) routines Build and populate Analysis Services cubes. Build reports and analytical views by: Using a third-party application. Creating a custom analytical application Maintain the warehouse by adding/changing supported features and reports. CS 245 Notes12 65

Question: 

66 Question When is a DW “finished”?

Motivating Examples: 

67 Motivating Examples Forecasting Stock market; discover correlated stock trends Comparing performance of units Supply chains; dealer networks Monitoring, detecting fraud Credit card usage (time & location) Visualization … CS 245 Notes12 67

Why a Warehouse?: 

68 Why a Warehouse? Ship and integrate data from different sources to the analyst Approaches: Database federations (legacy) Warehouse (eager) MCBR Source Source ?

Federated Databases: 

69 Federated Databases An alternative to data warehouses Data warehouse Create a copy of all the data Execute queries against the copy Federated database Pull data from source systems as needed to answer queries MCBR “lazy” vs. “eager” data integration Data Warehouse Federated Database Query Answer Query Extraction Rewritten Queries Answer Source Systems Source Systems Warehouse Mediator

Warehouses vs. Federation: 

70 Warehouses vs. Federation Advantages of federated databases: No redundant copying of data Queries see “real-time” view of evolving data More flexible security policy Disadvantages of federated databases: Analysis queries place extra load on transactional systems Query optimization is hard to do well Historical data may not be available Complex “wrappers” needed to mediate between analysis server and source systems Data warehouses are much more common in practice Better performance Lower complexity Slightly out-of-date data is acceptable

Advantages of Warehousing: 

71 Advantages of Warehousing High query performance Queries not visible outside warehouse Local processing at sources unaffected Can operate when sources unavailable Can query data not stored in a DBMS Extra information at warehouse Modify, summarize (store aggregates) Add historical information CS 245 Notes12 71

PowerPoint Presentation: 

72 DUL: http://www.gartner.com/it/page.jsp?id=856714 http://altaplana.com/olap / http://www.smashingmagazine.com/2007/08/02/data-visualization-modern-approaches/