微软数据仓库介绍

1、Module 1 Introduction to Data Warehousing,Module Overview,数据仓库概述 考虑数据仓库解决方案,Lesson 1: 数据仓库概述,商业难题 什么是数据仓库？ 数据仓库架构 数据仓库解决方案组件 数据仓库项目 数据仓库项目角色 SQL Server 作为数据仓库平台,业务难题,关键业务数据分布在多个业务系统 找到业务决策的信息是耗时的和容易出错的 基本的业务问题很难回答,?,What Is a Data Warehouse?,一个集中存放用于报表和数据的信息容器 通常，一个数据仓库: 包含大量的历史数据 优化了数据查询 (而不是插入和更新)

2、 定期加载新的业务数据 为企业商务智能解决方案提供依据,Data Warehouse Architectures,Departmental Data Mart,Components of a Data Warehousing Solution,从业务系统和其他数据源抽取数据加载 数据通常最终加载到数据仓库 数据清洗和重复数据的删除，确保数据仓库中数据的质量 MDM 提供确切的业务数据实体,Data Warehousing Projects,首先确定数据仓库需要解决的业务问题 确定回答这些问题所需的数据 识别所需数据的数据源 评估关键业务目标价值可行性，从现在的数据回答每个问题 对大量数据的项目

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,考虑数据仓库包括:,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,数据源连接类型 凭证和权限 数据格式 数据采集窗口,Extract, Transform, and Load Processes,临时表： 存放临时数据 所需的转换: 提取数据时所需的数据转换和清洗 增量 ETL: 数据的变化加载,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