310 likes | 556 Views
Architecting Business Intelligence System. Slava Kokaev Lead Business Intelligence Architect at Industrial Defender . Email: vkokaev@bostonbi.org Twitter: @ SlavaKokaev. Agenda. What is business intelligence? Business Intelligence Architecture
E N D
Slava Kokaev Lead Business Intelligence Architect at Industrial Defender Email: vkokaev@bostonbi.org Twitter: @SlavaKokaev
Agenda • What is business intelligence? • Business Intelligence Architecture • Understanding Business Process and System Architecture • Modeling Business Processes • Modeling Dataflow • Modeling Dimensional Model • Modeling Analytical System • ETL Architecture
Tools And Technologies • Microsoft SQL Server 2012 Database Engen • Microsoft SQL Server 2012 Integration Services • Microsoft SQL Server 2012 Analysis Services • Microsoft SQL Server 2012 Reporting Services • SQL Server Data Tools in Visual Studio 2012 • UML • IDEF0 Notation • SADT Methodology
What is Business Intelligence? Business intelligence (BI) is a set of theories, methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information for business purposes. "Business Intelligence - enables more effective strategic, tactical, and operational insights and decision-making."
What is Business Intelligence Technologies? BI technologies provide historical, current and predictive views of business operations.
Types of BI Systems • Batch processing Architecture • Scheduled • Large dataset size • Close to real time architecture • Scheduled or triggered • Small dataset size • Real Time architecture • Data stream • Messages
Enterprise Business Analysis System Vendors, Suppliers, Channel partners Customers Business partners Monitoring Systems Analysis Systems Business Processes and Operations Controlling Systems Strategy and Planning Systems IT providers Financial service providers
Business Conceptual Framework ETL Data Warehouse Data Presentation Data Sources Data Integration Data Storage Data Analytics (logic) Data Visualization Client Application
Data Sources Web Service FTP Server Cloud Customer Web App Order Database Server
ETL Data Sources Web Service FTP Server Cloud Customer Web App Order Database Server
ETL Extract, Transform, Load Web Service ETL Data Warehouse FTP Server Database Server
ETL Data Warehouse Extract, Transform, Load Web Service ETL Data Warehouse FTP Server Database Server
Data Warehouse OLAP Cube Data Warehouse Data Mining Structure
Data Presentation Data Warehouse OLAP Cube Data Warehouse Data Mining Structure
Data Presentation Dashboards Business Logic OLAP Cube Clients SharePoint Server Power User Data Mining Structure Custom Application
Use Case Mo Night Freight Sales Quota $1000 Boss Jack Amy Reseller Sales 06/18/2010 Amy Valdez E223084 06/17/2010 Jack Yo Mountain Bike -200
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
Business Process Business Rules Plans Standards Products Money Business Activity Customers Orders Materials Contracts Human Resources IT Resources Equipment
Functional Structure Feedback (Improvement) Plan Plans, Business Rule and KPI Input Data Process Output (Facts /Measures) Do Resources Check Result Data Act Data Mining SQL Server Reporting Services Analysis Services
Sales Business Process Balance Scorecards Sales corrections and Improvement Plan Sales Sales Quota and KPI Market Data Sale Orders (Facts /Measures) Reseller (Dimension) Resellers Sales Sales Result Monitor Sales Sales Summary Sales Transaction Analyze Sales SQL Server DB Sales Representative Sales Manager
Designing Dimensional Model Fact Dimension Dimension Dimension Dimension Dimension
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
Analytical System of the Data Warehouse BI Semantic Model Excel PowerPivot SharePoint Third-party Reporting Services Multi- dimensional Tabular Data model Business logic and queries MDX DAX Direct Query Data access VertiPaq ROLAP MOLAP Databases LOB Applications Files OData Feeds Cloud Services
ETL Logical Architecture Extract Data Load Staging Extract from Staging Send Notification Log ETL Process Transform Data Load Dimensions Load Facts Process Cube
Batch Processing Architecture Truncate Table Data Source Staging Table DWH Table ETL
Batch Processing Architecture (Table Switch) Truncate Table Staging Table 1 Data Source DWH Table Staging Table 2 ETL
This is the End of the presentation But not the end of the way