Transcript Business Process Modeling and Analysis for Data

Business Process Modeling and
Analysis for Data Warehouse
Design
Slava Kokaev
Lead Business Intelligence Architect at
Lockheed Martin
Email: [email protected]
Twitter: @SlavaKokaev
Agenda
Model and Analyze Business Processes
Design Dimensional Model
Dimensional Model Physical Implementation
Tools And Technologies
Microsoft SQL Server 2014
UML
IDEF0 Notation
SADT Methodology
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.
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.
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.
Element
7
Element
3
Element
6
Element
2
Element 5
Element 1
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.
Process
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.
Identify
Problem
Set Goal
and
Result
Define
Mechanis
m
Perform
Task
Analyze
Result
Improve
Process
Business Process
A business process isBatch
a collection
of related, Architecture
structured activities or tasks that
processing
produce a specific service
or product (result) (serve a particular goal) for a
 Scheduled
particular customer orLarge
customers.
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
Business Process Structure
Enterprise Business Analytics System
Vendors, Suppliers
Monitoring Systems
Customers
Analysis Systems
Business Processes and Operations
Controlling Systems
Strategy and
Planning Systems
Service Providers
Business
Partners
Business Conceptual Framework
Shipping Dept.
Sales Quota
Mo
Jack
Amy
Reseller
Order Date
06/17/2010
Order #
E223084
$1000
Manager
Sales
Reseller
Jack Yo
Sales Person
Amy Valdez
Ship Date
06/18/2010
Product
Mountain Bike -200
Price
Data Sources
Web Service
Customer
Web App
Order
Cloud
FTP Server
Database
Server
Data
ETL
Sources
Customer
Web App
Web Service
Order
Cloud
FTP Server
Database
Server
ETL
Extract, Transform, Load
Web Service
FTP Server
Database
Server
ET
L
Data
Warehouse
Data
ETL
Warehouse
Extract, Transform, Load
Web Service
FTP Server
Database
Server
ET
L
Data
Warehouse
Data
Warehouse
OLAP Cube
Data
Warehouse
Data
Mining
Structure
Data
Presentation
Warehouse
OLAP Cube
Data
Warehouse
Data
Mining
Structure
Data
Presentation
Business
OLAP Cube
Logic
SharePoint
Server
Dashboard
s
Clients
Power
User
Data
Mining
Structure
Custom
Application
Use Case
Mo
Night Freight
Sales Quota
Jack
Amy
Reseller
$1000
Manage
r
Sales
Order Date
Order #
Reseller
Sales Person
Ship Date
06/17/2010
E223084
Jack Yo
Amy Valdez
06/18/2010
Product
Mountain Bike -200
Identifying System Boundary
Business Intelligence System
Sales Mager
Analyze
Sales
Create
Sales Plan
Create KPI
Source System
Browse
Product
Catalog
Sale
Product
Get
Product
Quotes
Reseller
Process
Order
Order
Product
Receive
Product
Package
Product
Ship the
Product
Sales Person
Get
Processed
Order
Shipping Person
Business Process
Business
Activity
Functional Structure (PDCA Model)
Feedback (Improvement)
Input Data
Plan
Resources
Plans, Business Rules
Process Output (Facts /Measures)
Do
Result Data
Check
Act
Data Mining
SQL Server
Reporting Services
Analysis Services
Sales Business Process
Balance Scorecards
Sales corrections and Improvement
Analysis Data
Plan Sales
Sales Quota
KPI
Order Info (Facts /Measures)
Reseller Info (Dimension)
Product Info (Dimension) Sale Product
Sales Transaction
Sales
Result
Monitor
Sales
Sales Summary
Analyze
Sales
Sales Representative
Sales Manager
Designing Dimensional Model
Dimension
Fact
Designing Dimensions
Reseller
Name
Jack Yo
Address
…, NJ, USA
Phone
(555)333-7777
Business Type
Retailer
Product Line
Bikes
Designing Dimensions Hierarchy
Product
Name
Mountain Bike 200
Category
Bike
Sub Category
Mountain Bike
Color
Black
Price
$150.00
Category
Sub Category
Name
Designing Dimensions
Hierarchies
A dimension hierarchy is a collection of logically structured levels based
on attributes.
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
Product
DimProduct
Product ID
Product Key
1
Category
Product ID
MB124677
Sub Category
Category
Bike
Name
Sub Category
Mountain Bike
Price
Name
Mountain Bike - 200
Color
Price
Black
Color
$150.00
Designing Dimensional Model
DimEmployee
Full Name
Amy Valdes
FactResellerSales
Order
Date
DateSK
8/12/2013
8122013
DimDate
Date
8/12/2013
9/3/2013
ProductS
K
555
EmployeeSK
5
Shipping
DateSK
Date
9/3/2013
932013
Sales
EmployeeS
Person
K
Amy Valdes
5
DimProduct
ProductName
Bike-200, Mnt,
B
Measures
ResellerSK
Reseller
John
20Doe
Product
ProductSK
Bike-200, Mnt,
555
B
Price
$150.00
Order Qty
20
DimReseller
DateSK
8122013
932013
ResellerSK
20
Full Name
John Doe
This is the End of the
Slides
But not the end of the
presentation