微软数据仓库介绍

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

2、供依据Data Warehouse ArchitecturesCentralized Data WarehouseDepartmental Data MartHub and SpokeComponents of a Data Warehousing Solution从业务系统和其他数据源抽取数据加载数据通常最终加载到数据仓库数据清洗和重复数据的删除，确保数据仓库中数据的质量MDM 提供确切的业务数据实体Data WarehouseStaging DatabaseETL Load ProcessETL Staging ProcessMaster Data Management1011000110

3、Data CleansingReporting and AnalysisData SourcesData Warehousing Projects1.首先确定数据仓库需要解决的业务问题2.确定回答这些问题所需的数据3.识别所需数据的数据源4.评估关键业务目标价值可行性，从现在的数据回答每个问题对大量数据的项目, 使用增量更新比较有效:把项目分解为多个子项目每个子项目处理一个特定的主题Data Warehousing Project RolesProject managerSolution architectData modelerDatabase administratorInfrastruc

4、ture specialistETL developerBusiness users/analystTestersData stewardsSQL Server As a Data Warehousing PlatformSQL ServerAnalysis ServicesSQL Server Database EngineMicrosoft SQL Server Integration ServicesSQL Server Master Data Services1011000110SQL Server Data Quality ServicesMicrosoft SQL Azureand

5、 the Windows Azure MarketplaceMicrosoft SharePoint ServerMicrosoft PowerPivot TechnologiesMicrosoft ExcelData Mining Add-InPowerPivot Add-InMDS Add-InPower ViewSQL ServerReporting ServicesReports, KPIs, and DashboardsInteractive data visualizationsInteractive data analysisData WarehousingBusiness In

6、telligenceLesson 2: Considerations for a Data Warehouse SolutionData Warehouse Database and StorageData SourcesExtract, Transform, and Load ProcessesData Quality and Master Data ManagementData Warehouse Database and Storage考虑数据仓库包括:Database schema Logical: typically denormalized for optimal read per

7、formancePhysical: often partitioned for performance and managementHardwareQuery processing and memoryStorageNetworkHigh availability and disaster recoveryHardware redundancyBackup strategySecurityServer accessData permissionsData Sources数据源连接类型凭证和权限数据格式数据采集窗口Extract, Transform, and Load Processes临时表

8、：存放临时数据所需的转换:提取数据时所需的数据转换和清洗增量 ETL:数据的变化加载Data Quality and Master Data ManagementData quality:Cleansing data:Validating data valuesEnsuring data consistencyIdentifying missing valuesDeduplicating dataMaster data management:Ensuring consistent business entity definitions across multiple systemsApplying business rules to ensure data validity1011000110Module Review and TakeawaysWhy might you consider including a staging area in your ETL solution?What options might you consider for performing data transf