Business Process Modeling and Analysis for Data Warehouse Design

1. Business Process Modeling and Analysis for Data Warehouse Design

2. Slava Kokaev Lead Business Intelligence Architect at Lockheed Martin Email: vkokaev@bostonbi.org Twitter: @SlavaKokaev

3. Agenda

4. Tools And Technologies

5. What is Business Intelligence? Business intelligence is a set of theories, methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information for business purposes.

6. Business Intelligence Goal To optimize organizational Key Business Processes and Operations by providing critical to business Information at Right Time and Right Format to all levels of employees.

7. System Definition System is a group or set of functional elements that interact together to produce desired result or achieve a goal We scope a system by defining its boundary; this means choosing which entities are inside the system and which are outside – part of the environment.

8. System Process A system can also be viewed as a bounded transformation process, that is, a process or collection of processes that transforms inputs into outputs. Inputs are consumed; outputs are produced. Control Input Output Process Processor

9. System Formation System is a set of detailed methods, procedures and routines created to carry out a specific activity, perform a duty, or solve a problem.

10. Business Process A business process is a collection of related, structured activities or tasks that produce a specific service or product (result) (serve a particular goal) for a particular customer or customers. • Batch processing Architecture • Scheduled • Large dataset size There is two types of business processes: key processes and supportive processes. Key Business process is a process that adds value to the product Supportive process adds value to the process itself, but not to the product

11. Business Process Structure

12. Enterprise Business Analytics System Vendors, Suppliers Customers Business Partners Monitoring Systems Analysis Systems Business Processes and Operations Controlling Systems Strategy and Planning Systems Service Providers

13. Business Conceptual Framework Sales Quota $1000 Shipping Dept. Mo Manager Jack Amy Reseller Sales 06/17/2010 E223084 Jack Yo Amy Valdez 06/18/2010 Mountain Bike -200

14. Data Sources Web Service FTP Server Cloud Customer Web App Order Database Server

15. ETL Data Sources Web Service FTP Server Cloud Customer Web App Order Database Server

16. ETL Extract, Transform, Load Web Service ETL Data Warehouse FTP Server Database Server

17. ETL Data Warehouse Extract, Transform, Load Web Service ETL Data Warehouse FTP Server Database Server

18. Data Warehouse OLAP Cube Data Warehouse Data Mining Structure

19. Data Presentation Data Warehouse OLAP Cube Data Warehouse Data Mining Structure

20. Data Presentation Dashboards Business Logic OLAP Cube Clients SharePoint Server Power User Data Mining Structure Custom Application

21. Use Case Sales Quota $1000 Mo Night Freight Manager Jack Amy Reseller Sales 06/18/2010 Amy Valdez E223084 06/17/2010 Jack Yo Mountain Bike -200

22. Identifying System Boundary Business Intelligence System Analyze Sales Create KPI Create Sales Plan Source System Browse Product Catalog Sale Product Get Product Quotes Process Order Order Product Get Processed Order Package Product Receive Product Ship the Product

23. Business Process Business Rules Plans Standards Products Money Business Activity Customers Orders Materials Contracts Human Resources IT Resources Equipment

24. Functional Structure (PDCA Model) Feedback (Improvement) Plan Plans, Business Rules Input Data Process Output (Facts /Measures) Do Resources Check Result Data Act Data Mining SQL Server Reporting Services Analysis Services

25. Sales Business Process Balance Scorecards Sales corrections and Improvement KPI Plan Sales Sales Quota Analysis Data Order Info (Facts /Measures) Reseller Info (Dimension) Sale Product Product Info (Dimension) Sales Result Monitor Sales Sales Summary Sales Transaction Analyze Sales Sales Representative Sales Manager

26. Designing Dimensional Model Fact Dimension Dimension Dimension Dimension Dimension

27. Designing Dimensions Reseller Jack Yo Name …, NJ, USA Address (555)333-7777 Phone Retailer Business Type Bikes Product Line

28. Designing Dimensions Hierarchy Product Name Mountain Bike 200 Category Category Bike Mountain Bike Sub Category Sub Category Black Color $150.00 Price Name

29. Designing Dimensions

30. Hierarchies A dimension hierarchy is a collection of logically structured levels based on attributes.

31. Surrogate Keys Primary key purpose Identifies uniqueness Relates to foreign keys in a fact table Consolidates multiple data sources Consolidates multi-value business keys Allows tracking of dimension history Target DW Table Source OLTP Table DimProduct Product 1 Product Key Product ID MB124677 Product ID Category Bike Category Sub Category Mountain Bike Sub Category Name Mountain Bike - 200 Name Price Black Price Color $150.00 Color

32. Designing Dimensional Model DimProduct DimEmployee ProductSK EmployeeSK ProductName Full Name 555 Bike-200, Mnt, B Amy Valdes 5 FactResellerSales Measures ResellerSK Reseller Product Order Qty Order Date Price Sales Person DateSK Shipping Date DateSK ProductSK Reseller Product Order Date Sales Person EmployeeSK Bike-200, Mnt, B John Doe 20 8/12/2013 Amy Valdes $150.00 20 9/3/2013 555 Bike-200, Mnt, B John Doe 8/12/2013 Amy Valdes 5 8122013 932013 DimReseller DimDate ResellerSK Full Name Date DateSK 20 John Doe 8/12/2013 8122013 9/3/2013 932013

33. This is the End of the Slides But not the end of the presentation