200 likes | 304 Views
Operations, BI, and Analytics . Stefano Grazioli. Critical Thinking. Doing well No lab Easy meter. Using the SmallBank DB for Business. Operations, BI & Analytics . Data Model: SmallBank,Ltd. Legend “zero/none” “one” “many”. LO id f name l name phone.
E N D
Operations,BI, and Analytics Stefano Grazioli
Critical Thinking • Doing well • No lab • Easy meter
Using the SmallBank DB for Business Operations, BI & Analytics
Data Model: SmallBank,Ltd. Legend “zero/none” “one” “many” LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Enrolling a New Customer LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Selling an I.P. to a Customer LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Changing an Address LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Granting a New Loan LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
The Previous Queries Implement Operational Transactions • Directly related to business operations • Single customer, single contract, deal, service… • “Real time” • Often INSERTs • “Small” amount of data • Large numbers of fast, “simple” queries
Finding our TX Exposure LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Finding our Top Three Customers LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Finding the Average Interest Rate by City LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
The Previous Queries Generate Reports and Answer Aggregate Questions (BI) • Relate to decision making more than business operations • Aggregate customers, contracts, deals, services… • Not necessarily “Real time” • Mostly Selects • “Large” amount of data • Small number of “large”, “complex” queries
Assessing the Relationship between Loan Rate and Loan Size LO id f name l name phone InsurancePlan C_id coverage premium Loanofficer L id principal rate date due LO_id C id f name l name city state Loan Customer C_idL_id Customer inLoan
Analytics is more sophisticated stats (typically non-SQL) • Questions relate to decision making, more than business operations • SQL provides the input, but is not sufficient. Require additional software (SPSS, SAS, R, Data miner…) • More similar to BI queries than operational queries.
BI and Analytics Queries Slow Down the Systems that Run our Businesses • Idea: create a separate copy of the data, including historical to perform analysis • The DB that contains this offline data is called a Data Warehouse (aka data mart, data hub…)
BACK TO The Big Picture… Informational (BI/Analytics) Historical, aggregate, decision Transactional (Ops) Right now, individual, action Operational environment Source: TDWI Smart Companies Report 2003 + sg edits
WINIT What Is NewIn Technology?
Homework Demo
You do the talking • Name, major • Learning objectives • Things you like about the class • Things that can be improved • Attitude towards the Tournament