ITEC423 Data warehousing introduction to the course

1. ITEC423 Data warehousingintroduction to the course Asst. Prof. Dr. NazifeDimililerSpring 2010-2011

2. Information • Class : CTL002 • Schedule • Tuesday 12:30-14:20 • Thursday 12:30-14:20 • Office : CT 206 • Phone : 630 1034 • Email : nazife.dimililer@emu.edu.tr • Books • Ponniah P., Data Warehousing Fundamentals for IT Professionals, John Wiley & Sons, 2010 • MS SQL server Analysis services

3. Assesment • Attendance • Attendance is mandatory. Missing more than 60% of classes disqualifies you from make ups • Grading • 4xQuizzes : 20% • Midterm :30% • Final : 45% • Lab performance (Attendance??) 5% • Optional Work upto 5-10% • Project • Research • Design Homework

4. Objectives and Learning Outcomes of the course • Objectives • Provide a solid background in data warehousing • Show the differences between databases and data warehousing • Define the process of designing a data warehouse • Design and implement a data warehouse • Learning outcomes • Describe the differences between OLTP systems and data warehouses. • Describe the need for data warehousing • Analyze and transform business requirements into a dimensional model in order to build a data warehouse • Transform the dimensional model into a physical data design • Implement a high quality data warehouse or data mart • Understand multidimensional query concepts

5. Schedule

6. Learning Procedures • Lectures • Power point slides • Discussions • Applications • Step-by-step tutorials • Case studies • Homework/Project • Problems • Research/Homework

7. Operational Databases (OLTP Systems) • Every company uses a number of operational databases to store daily transactions • All activities are recorded • Performed by users • Stored in databases • Operational databases are designed and optimized for insert/delete/update • Majority of transactions involve single records

8. Operational Databases (OLTP Systems) abcd abcd abcd 1234 1234 1234 dfsfh dfsfh dfsfh data data data Accounting Accounting Software Market Sales Market Sales Software Estate Sales Estate Agency Software

9. What is Business Information? • Information contained in the operational databases and external resources of a company • Utilized for gaining insights that drive strategic and tacticalbusiness decisions • Help make decisions faster • Encompasses a broad category of technologies • gather, store, access, and analyze data

10. What is Business Intelligence? • computer-based techniques used in spotting, digging-out, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes • broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help clients make better business decisions.

11. What is business Intelligence? • environment in which business users receive information that is reliable, secure, consistent, understandable, easily manipulated and timely • enable business users to conduct analyses that yield an overall understanding of where the business has been, where it is now, and where it will be in the near future. • empowers knowledge workers to make more informed, smarter business decisions faster

12. Key concepts in Business Intelligence • Management makes decisions • Requires information from various/diverse sources • Information should be in required format • Past data is important • Results should be produced immediately • Managers should be able pose ad-hoc queries DATA WAREHOUSING

13. Is there a correlation between apt sales and dairy product sales? Prepare a graph showing amount of dairy products and number of apts sold in each month for the last 5 years. Business Intelligence I need the number of dairy products sold by each branch per month for the last 10 years! I need these NOW!!! Accounting Accounting Software Business Intelligence Query Query Query ? Market Sales Market Sales Software Query Estate Sales Estate Agency Software

14. BUSINESS INTELLIGENCE Business Intelligence Accounting Accounting Software Data Warehouse product branch Market Sales Market Sales Software All bills price Extract Transform Load company All market sales category employee Contains historical data as well All property sales Estate Sales Estate Agency Software

15. STAR SCHEMA

16. What Can a Data Warehouse Do? Some of the benefits of a DW are: • Immediate information delivery to management • Data integration from across and even outside the organization • Future vision from historical trends • Tools for looking at data in new ways • Freedom from IS department resource limitations

17. Example of Data Warehouse Applications-I • Sales Analysis • Determine real-time product sales to make vital pricing and distribution decisions. • Analyze historical product sales to determine success or failure attributes. • Evaluate successful products and determine key success factors. • Use corporate data to understand the margin as well as the revenue implications of a decision. • Rapidly identify a preferred customer segments based on revenue and margin. • Quickly isolate past preferred customers who no longer buy. • Identify daily what product is in the manufacturing and distribution pipeline. • Instantly determine which salespeople are performing, on both a revenue and margin basis, and which are behind.

18. Example of Data Warehouse Applications-II • Financial Analysis • Compare actual to budgets on an annual, monthly and month-to-date basis. • Review past cash flow trends and forecast future needs. • Identify and analyze key expense generators. • Instantly generate a current set of key financial ratios and indicators. • Receive near-real-time, interactive financial statements.

19. Example of Data Warehouse Applications-III • Human Resource Analysis • Evaluate trends in benefit program use. • Identify the wage and benefits costs to determine company-wide variation. • Review compliance levels for EEOC and other regulated activities. • Other Areas • Warehouses have also been applied to areas such as: • Logistics • Inventory • Purchasing • detailed transaction analysis • load balancing • …

20. What is Data Warehouse? • A decision support database that is maintained separatelyfrom the organization’s operational database • Supports information processing by providing a solid platform of consolidated, historical data for analysis. • Consolidated/integrated view of corporate data drawn from disparate operational data sources • Depending on the purpose of the data warehouse, it may contain atomic data, summary data, or both. • A range of end-user access tools capable of supporting simple to highly complex queries to support decision-making.

21. What is Data Warehouse? • A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect. • Data warehousing emphasizes the capture of data from diverse sources for useful analysis and access • Data warehouse helps get information to answer questions. • It is not meant for direct data entry; • batch updates are the norm for refreshing warehouses. • Data mart is a subset of a data warehouse based on a specific department, function or subject • Applications of data warehouses include data mining, Web Mining, and decision support systems (DSS), Business Intelligence (BI).

22. What is a data warehouse? “A data warehouse is a • subject-oriented, • Integrated (consolidated) • time-variant,and • nonvolatile collection of data in support of management’s decision-making process.” W. H. Inmon