微軟數(shù)據(jù)倉庫介紹

1、Module 1 Introduction to Data Warehousing,Module Overview,數(shù)據(jù)倉庫概述 考慮數(shù)據(jù)倉庫解決方案,Lesson 1: 數(shù)據(jù)倉庫概述,商業(yè)難題 什么是數(shù)據(jù)倉庫？ 數(shù)據(jù)倉庫架構 數(shù)據(jù)倉庫解決方案組件 數(shù)據(jù)倉庫項目 數(shù)據(jù)倉庫項目角色 SQL Server 作為數(shù)據(jù)倉庫平臺,業(yè)務難題,關鍵業(yè)務數(shù)據(jù)分布在多個業(yè)務系統(tǒng) 找到業(yè)務決策的信息是耗時的和容易出錯的 基本的業(yè)務問題很難回答,?,What Is a Data Warehouse?,一個集中存放用于報表和數(shù)據(jù)的信息容器 通常，一個數(shù)據(jù)倉庫: 包含大量的歷史數(shù)據(jù) 優(yōu)化了數(shù)據(jù)查詢 (而不是插入和更新)

2、 定期加載新的業(yè)務數(shù)據(jù) 為企業(yè)商務智能解決方案提供依據(jù),Data Warehouse Architectures,Departmental Data Mart,Components of a Data Warehousing Solution,從業(yè)務系統(tǒng)和其他數(shù)據(jù)源抽取數(shù)據(jù)加載 數(shù)據(jù)通常最終加載到數(shù)據(jù)倉庫 數(shù)據(jù)清洗和重復數(shù)據(jù)的刪除，確保數(shù)據(jù)倉庫中數(shù)據(jù)的質量 MDM 提供確切的業(yè)務數(shù)據(jù)實體,Data Warehousing Projects,首先確定數(shù)據(jù)倉庫需要解決的業(yè)務問題 確定回答這些問題所需的數(shù)據(jù) 識別所需數(shù)據(jù)的數(shù)據(jù)源 評估關鍵業(yè)務目標價值可行性，從現(xiàn)在的數(shù)據(jù)回答每個問題 對大量數(shù)據(jù)的項目

3、, 使用增量更新比較有效: 把項目分解為多個子項目 每個子項目處理一個特定的主題,Data Warehousing Project Roles,Project manager Solution architect Data modeler Database administrator Infrastructure specialist ETL developer Business users/analyst Testers Data stewards,SQL Server As a Data Warehousing Platform,SQL Server Analysis Services,S

4、QL Server Database Engine,Microsoft SQL Server Integration Services,SQL Server Master Data Services,Microsoft SQL Azure and the Windows Azure Marketplace,Microsoft SharePoint Server,Microsoft PowerPivot Technologies,Microsoft Excel Data Mining Add-In PowerPivot Add-In MDS Add-In,Power View,SQL Serve

5、r Reporting Services,Reports, KPIs, and Dashboards,Interactive data visualizations,Interactive data analysis,Data Warehousing,Business Intelligence,Lesson 2: Considerations for a Data Warehouse Solution,Data Warehouse Database and Storage Data Sources Extract, Transform, and Load Processes Data Qual

6、ity and Master Data Management,Data Warehouse Database and Storage,考慮數(shù)據(jù)倉庫包括:,Database schema Logical: typically denormalized for optimal read performance Physical: often partitioned for performance and management,Hardware Query processing and memory Storage Network,High availability and disaster rec

7、overy Hardware redundancy Backup strategy,Security Server access Data permissions,Data Sources,數(shù)據(jù)源連接類型 憑證和權限 數(shù)據(jù)格式 數(shù)據(jù)采集窗口,Extract, Transform, and Load Processes,臨時表： 存放臨時數(shù)據(jù) 所需的轉換: 提取數(shù)據(jù)時所需的數(shù)據(jù)轉換和清洗 增量 ETL: 數(shù)據(jù)的變化加載,Data Quality and Master Data Management,Data quality: Cleansing data: Validating data val

8、ues Ensuring data consistency Identifying missing values Deduplicating data,Master data management: Ensuring consistent business entity definitions across multiple systems Applying business rules to ensure data validity,1011000110,Module Review and Takeaways,Why might you consider including a staging area in your ETL solution? What options might you consider for performing data transformatio