1 / 20

SmallBank Operations, BI & Analytics | Data Model & Queries

Learn about SmallBank Ltd.'s DBMS operations, BI, and analytics, including queries for business decisions and analysis. Understand operational transactions, BI reporting, analytics, and data warehousing concepts.

matsu
Télécharger la présentation

SmallBank Operations, BI & Analytics | Data Model & Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBMS and Operations,BI, and Analytics Stefano Grazioli

  2. Critical Thinking • Doing Well • No lab this friday • Easy meter

  3. Using the SmallBank DBfor Business Operations, BI & Analytics

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. Analytics is more sophisticated analysis (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.

  16. BI and Analytics Queries Slow Down the Systems that Run our Businesses • Idea: create a copy of the data and perform analysis on it • The DB that contains this offline data is called a Data Warehouse (aka data mart, data hub…)

  17. 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

  18. WINIT What Is NewIn Technology?

  19. Homework Demo

  20. You do the talking • Name, major • Learning objectives • Things you like about the class • Things that can be improved • Attitude towards the Tournament

More Related