《第五章 范例.ppt》由会员分享，可在线阅读，更多相关《第五章 范例.ppt（60页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第五章第五章 范范 例例o 设计一个数据仓库首先要面对的是哪些是设计一个数据仓库首先要面对的是哪些是事实数据，而哪些是维度数据。在一个大型事实数据，而哪些是维度数据。在一个大型的的OLTP系统中字段众多，要在其中决定事系统中字段众多，要在其中决定事实与维度数据并不容易。实与维度数据并不容易。u识别事实与维度识别事实与维度u设计事实表设计事实表u设计维度表设计维度表识别事实与维度识别事实与维度1）在整个）在整个OLTP系统中搜索最基本的交易，系统中搜索最基本的交易，它们极可能是事实数据它们极可能是事实数据2）决定搜索每一个事实数据的外键，它们极）决定搜索每一个事实数据的外键，它们极可能是维度数据
2、可能是维度数据3）检验每一个可能事实数据的字段，确定它）检验每一个可能事实数据的字段，确定它不是嵌入在事实数据中的维度数据不是嵌入在事实数据中的维度数据4）检验每一个可能维度数据的字段，确定它）检验每一个可能维度数据的字段，确定它不是嵌入在维度数据中的事实数据不是嵌入在维度数据中的事实数据设计事实表设计事实表o一个事实表是由一个事实表是由OLTP系统转入而生成出来的，数系统转入而生成出来的，数据仓库的数据并不包含据仓库的数据并不包含OLTP系统中所有的数据。系统中所有的数据。o在设计一个事实表的时候，考虑下面的事项：在设计一个事实表的时候，考虑下面的事项：1）为每一项功能决定数据仓库的时距（十
3、年，五年还是其）为每一项功能决定数据仓库的时距（十年，五年还是其它？）它？）2）为每一项功能决定其采用原则）为每一项功能决定其采用原则3）决定在事实表中应包含哪些字段）决定在事实表中应包含哪些字段4）尽量缩小事实表中字段的大小）尽量缩小事实表中字段的大小5）将时间因素加入事实表）将时间因素加入事实表设计维度表设计维度表o在设计之初要确定不会更新数据的主键，否在设计之初要确定不会更新数据的主键，否则一旦修改，事实表要一起更新。则一旦修改，事实表要一起更新。o维度表常是违反正规化的。维度表常是违反正规化的。o时间一般都是一个事实表的维度时间一般都是一个事实表的维度例子 Northwind数据库数据
4、库o一个贸易公司商业所使用的数据库（一个贸易公司商业所使用的数据库（SQL Server 2000的范例）的范例）o数据库架构数据库架构（1）Categories表：表：存储产品所有类型的存储产品所有类型的相关信息相关信息字段名字段名功能描述功能描述CategoryID产品类型的识别码产品类型的识别码CategoryName产品名称产品名称Description产品类型描述产品类型描述Picture产品图片产品图片数据库架构数据库架构（2）CustomerDemo表：表：存储了顾客所属存储了顾客所属类别信息类别信息（3）CustomerDemographics表：表：存储存储了顾客类别的描述信
5、息了顾客类别的描述信息字段名字段名功能描述功能描述CustomerID顾客识别码顾客识别码CustomerTypeID顾客类别识别码顾客类别识别码字段名字段名功能描述功能描述CustomerTypeID顾客类别识别码顾客类别识别码CustomerDesc顾客类别的描述顾客类别的描述数据库架构数据库架构（4）Customers表：表：存储了顾客所有相关存储了顾客所有相关信息信息字段名字段名功能描述功能描述CustomerID顾客识别码顾客识别码CustomerName顾客姓名顾客姓名Address地址地址City城市城市Region地区地区PostalCode邮政编码邮政编码Country国家国
6、家Phone电话电话Fax传真传真CompanyName顾客单位（公司）顾客单位（公司）数据库架构数据库架构（5）Employees表：表：存储了员工所有相关存储了员工所有相关信息信息Extension公司内部分机号公司内部分机号Photo照片照片Notes员工信息描述员工信息描述字段名字段名功能描述功能描述EmployeeID员工员工识别码识别码EmployeeName员工姓名员工姓名BirthDate出生年月出生年月HireDate雇佣日期雇佣日期Address地址地址City城市城市Region地区地区PostalCode邮政编码邮政编码HomePhone家庭电话家庭电话Title员工职
7、务员工职务数据库架构数据库架构（6）EmployeesTerritories表：表：存储了存储了员工所负责的区域员工所负责的区域（7）Territories表：表：存储了员工所负责的存储了员工所负责的区域区域的基本数据的基本数据字段名字段名功能描述功能描述EmployeeID员工员工识别码识别码TerritoryID员工负责区域识别码员工负责区域识别码字段名字段名功能描述功能描述TerritoryID区域区域识别码识别码TerritoryDescription区域描述区域描述RegionID所属地区识别码所属地区识别码数据库架构数据库架构（8）Region：存储了员工所负责的区域所存储了员工所
8、负责的区域所属区的基本数据属区的基本数据（9）OrderDetail：存储订单下的单项商品存储订单下的单项商品信息信息字段名字段名功能描述功能描述RegionID地区地区识别码识别码RegionDescription地区名称地区名称字段名字段名功能描述功能描述OrderID订单识别号订单识别号ProductID产品识别号产品识别号UnitPrice产品单价产品单价Quantity订购数量订购数量Discount折扣折扣数据库架构数据库架构（10）Orders：存储订单的全部信息存储订单的全部信息Feight货运价格货运价格ShipName接受货品人姓名接受货品人姓名ShipAddress送货地
9、址送货地址字段名字段名功能描述功能描述OrderID订单识别号订单识别号CustomerID顾客识别号顾客识别号EmployeeID承办员工识别号承办员工识别号OrderDate订购日期订购日期RequiredDate订单的需要日期订单的需要日期ShippedDate送货日期送货日期ShipVia送货公司识别码送货公司识别码ShipCity送货城市送货城市ShipRegion送货地区送货地区ShipPostalCode送货区邮政编码送货区邮政编码数据库架构数据库架构（11）Shippers：存储了货运公司的相关信存储了货运公司的相关信息息字段名字段名功能描述功能描述ShipperID货运公司识
10、别号货运公司识别号CompanyName货运公司名称货运公司名称Phone货运公司电话号码货运公司电话号码数据库架构数据库架构（12）Products：产品的相关信息产品的相关信息UnitOnOrder一次订货量一次订货量ReorderLevel重新订货最低库存量重新订货最低库存量Discontinued是否停售是否停售字段名字段名功能描述功能描述ProductID产品识别号产品识别号ProductName产品名称产品名称SupplierID供货商识别号供货商识别号CategoryID产品分类识别号产品分类识别号QuantityPerUnit每单位数量每单位数量UnitPrice单价单价Uni
11、tInStock库存量库存量数据库架构数据库架构（13）Suppliers：供货商的相关信息供货商的相关信息phone电话电话Fax传真传真HomePage公司网址公司网址字段名字段名功能描述功能描述SupplierID供货商识别号供货商识别号CompanyName公司名称公司名称ContactName联络人姓名联络人姓名Address地址地址City城市城市Region地区地区PostalCode邮政编码邮政编码表间关系Employeer员工表员工表EmployeerTerritoriesTerritories区域表区域表Region地区表地区表Customer顾客表顾客表CustomerC
12、ustomorDemo顾客类别顾客类别CustomorDemographics顾客描述顾客描述Orders订单表订单表OrdersDetail订单详情表订单详情表Shippers货运公司货运公司Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表需求分析o经过调查后，假定得到了下面的需求：经过调查后，假定得到了下面的需求：n希望能够针对每一个员工做销售业绩分析希望能够针对每一个员工做销售业绩分析n希望能够针对每一产品做销售分析希望能够针对每一产品做销售分析n希望能够针对每一分类产品做销售分析希望能够针对每一分类产品做销售分析n希望能够针对每一供
13、货商做销售分析希望能够针对每一供货商做销售分析n希望能够针对每一顾客做销售分析希望能够针对每一顾客做销售分析n希望能够针对每一地区的顾客做销售分析希望能够针对每一地区的顾客做销售分析n希望能够针对每一城市的顾客做销售分析希望能够针对每一城市的顾客做销售分析n希望能够针对年、季、月做销售分析希望能够针对年、季、月做销售分析识别事实和维度o经归纳发现，索引基准点为经归纳发现，索引基准点为5类：类：n顾客顾客n员工员工n产品产品n供货商供货商n时间时间分析过程Employeer员工表员工表EmployeerTerritoriesTerritories区域表区域表Region地区表地区表Custome
14、r顾客表顾客表CustomerCustomorDemo顾客类别顾客类别CustomorDemographics顾客描述顾客描述Orders订单表订单表OrdersDetail订单详情表订单详情表Shippers货运公司货运公司Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表8个记录个记录0个记录个记录0个记录个记录91个记录个记录9个记录个记录49个记录个记录2155个记录个记录830个记录个记录4个记录个记录53个记录个记录29个记录个记录3个记录个记录77个记录个记录货运在分析货运在分析中不出现，中不出现，可去掉可去掉分析过程Emplo
15、yeer员工表员工表EmployeerTerritoriesTerritories区域表区域表Region地区表地区表Customer顾客表顾客表CustomerCustomorDemo顾客类别顾客类别CustomorDemographics顾客描述顾客描述Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表8个记录个记录0个记录个记录0个记录个记录91个记录个记录9个记录个记录49个记录个记录2155个记录个记录830个记录个记录4个记录个记录53个记录个记录29个记录个记录
16、77个记录个记录员工负责的区域及其员工负责的区域及其区域所属地区与分析区域所属地区与分析无关，去掉三个表无关，去掉三个表Employeer员工表员工表Customer顾客表顾客表CustomerCustomorDemo顾客类别顾客类别CustomorDemographics顾客描述顾客描述Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表8个记录个记录0个记录个记录0个记录个记录91个记录个记录9个记录个记录2155个记录个记录830个记录个记录29个记录个记录77个记录个记
17、录顾客类别及描顾客类别及描述在分析中不述在分析中不会感兴趣，而会感兴趣，而且为且为0个记录，个记录，所以也不考虑所以也不考虑分析过程Employeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表字段名字段名功能描述功能描述CategoryID产品类型的识别码产品类型的识别码CategoryName产品名称产品名称Description产品类型描述产品类型描述Picture产品图片产品图片分析过程Employeer员工表员工表Cus
18、tomer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表UnitOnOrder一次订货量一次订货量ReorderLevel重新订货最低库存量重新订货最低库存量Discontinued是否停售是否停售字段名字段名功能描述功能描述ProductID产品识别号产品识别号ProductName产品名称产品名称SupplierID供货商识别号供货商识别号CategoryID产品分类识别号产品分类识别号QuantityPerUnit每单位数量每单位数量UnitPrice单
19、价单价UnitInStock库存量库存量分析过程Employeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表phone电话电话Fax传真传真HomePage公司网址公司网址字段名字段名功能描述功能描述SupplierID供货商识别号供货商识别号CompanyName公司名称公司名称ContactName联络人姓名联络人姓名Address地址地址City城市城市Region地区地区PostalCode邮政编码邮政编码分析过程Emp
20、loyeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表HomePhone家庭电话家庭电话Extension公司内部分机号公司内部分机号Photo照片照片Notes员工信息描述员工信息描述字段名字段名功能描述功能描述EmployeeID员工员工识别码识别码EmployeeName员工姓名员工姓名BirthDate出生年月出生年月HireDate雇佣日期雇佣日期Address地址地址City城市城市Region地区地区PostalC
21、ode邮政编码邮政编码Title员工职务员工职务分析过程Employeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表字段名字段名功能描述功能描述CustomerID顾客识别码顾客识别码CustomerName顾客姓名顾客姓名Address地址地址City城市城市Region地区地区PostalCode邮政编码邮政编码Country国家国家Phone电话电话Fax传真传真CompanyName顾客单位（公司）顾客单位（公司）分析过
22、程Employeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表Feight货运价格货运价格ShipName接受货品人姓名接受货品人姓名ShipAddress送货地址送货地址字段名字段名功能描述功能描述OrderID订单识别号订单识别号CustomerID顾客识别号顾客识别号EmployeeID承办员工识别号承办员工识别号OrderDate订购日期订购日期RequiredDate订单的需要日期订单的需要日期ShippedDate送
23、货日期送货日期ShipVia送货公司识别码送货公司识别码ShipCity送货城市送货城市ShipRegion送货地区送货地区ShipPostalCode送货区邮政编码送货区邮政编码分析过程Employeer员工表员工表Customer顾客表顾客表Orders订单表订单表OrdersDetail订单详情表订单详情表Products产品表产品表Categories产品类别表产品类别表Suppliers供货商表供货商表字段名字段名功能描述功能描述OrderID订单识别号订单识别号ProductID产品识别号产品识别号UnitPrice产品单价产品单价Quantity订购数量订购数量Discount折
24、扣折扣分析过程识别事实与维度数据识别事实与维度数据o顾客维度顾客维度o员工维度员工维度o供应商维度供应商维度o时间维度时间维度o产品维度产品维度产品维度产品维度oCategories:产品类别描述产品类别描述oProducts:产品类别描述产品类别描述字段名字段名功能描述功能描述CategoryID产品类型的识别码产品类型的识别码CategoryName产品名称产品名称CategoryID,CategoryName:维维度数据，属于产品维度数据，属于产品维度度字段名字段名ProductIDProductName产品名称产品名称SupplierID供货商识别号供货商识别号CategoryID产品
25、分类识别号产品分类识别号QuantityPerUnitUnitPrice功能描述功能描述产品识别号产品识别号每单位数量每单位数量单价单价每单位数量、单价是同公每单位数量、单价是同公司运营有关的数据，不随司运营有关的数据，不随时间改变，也不会以每单时间改变，也不会以每单位数量和单价为基准来分位数量和单价为基准来分析数据，所以是事实数据析数据，所以是事实数据顾客维度oCustomers:顾客描述顾客描述字段名字段名功能描述功能描述CustomerID顾客识别码顾客识别码CustomerName顾客姓名顾客姓名City城市城市Region地区地区Country国家国家CustomerID、Custo
26、merName:属于顾客维度数据属于顾客维度数据City、Region、Country:属于顾客维属于顾客维度数据，且具有层次关度数据，且具有层次关系系员工维度oEmployees:员工描述员工描述字段名字段名功能描述功能描述EmployeeID员工员工识别码识别码Name员工姓名员工姓名Title员工职务员工职务都是维度数据，属都是维度数据，属于员工维度于员工维度事实数据oOrders:订单描述订单描述字段名字段名功能描述功能描述OrderID订单识别号订单识别号CustomerID顾客识别号顾客识别号EmployeeID承办员工识别号承办员工识别号OrderDate订购日期订购日期订单号是
27、因公司经营而产订单号是因公司经营而产生，不随时间变化，不会生，不随时间变化，不会以订单号为基准分析，所以订单号为基准分析，所以是事实数据以是事实数据CustomerID:顾客维度数据顾客维度数据EmployeeID:员工维度数据员工维度数据OrderDate:时间维度数据时间维度数据事实数据OrdersDetail:产品项描述产品项描述字段名字段名功能描述功能描述OrderID订单识别号订单识别号ProductID产品识别号产品识别号UnitPrice产品单价产品单价Quantity订购数量订购数量Discount折扣折扣随公司运营产生的有订单号、产品随公司运营产生的有订单号、产品单价、订购数
28、量和折扣，它们不随单价、订购数量和折扣，它们不随时间变化。也不会以订单号、产品时间变化。也不会以订单号、产品单价、订购数量和折扣为基准分析单价、订购数量和折扣为基准分析数据，所以是事实数据。数据，所以是事实数据。ProductID是产品维度是产品维度数据数据设计事实表o事实表名称：事实表名称：Saleso数据源：数据源：Orders,OrderDtails,Employees,Products,Suppliers,Customerso索引：索引：EmployeeID,来自来自Employees表表ProcutID,来自来自Products表表CustomerID,来来自自Customers表表
29、OrderDate,来自来自Orders表表o事实表度量值字段：事实表度量值字段：nUnitPrice,来自来自OrderDetails表表nTotal=Quantity*UnitPrice*(1.0-Discount)nQuantity,来自来自OrderDetails表表nDiscount,来自来自OrderDetails表表数据抽取SQL语句SELECT e.EmployeeID,p.ProductID,s.SuppliersID,c.CustomerID,o.OrderDate,od.UnitPrice,od.Quantity*od.UnitPrice*(1.0-od.Discount
30、)Total,od.Quantity,od.DiscountFROM Orders o,OrderDetails od,Employees e,Products p,Suppliers s,Customers cWHERE o.OrderID=od.OrderID ANDo.EmployeeID=e.EmployeeID ANDo.CustomerID=c.CustomerID ANDod.ProductID=s.ProductID ANDp.SuppliersID=s.SuppliersID设计员工维度表o员工维度表名称：员工维度表名称：Employeeo数据源：数据源：Employees表
31、表o数据字段：数据字段：nEmoloyeeID,来自来自Employees表表nEmployeeName,来自来自Employees表表nTitle,来自来自Employees表表oSQL语句语句 SELECT EmployeeID,EmployeeName,Title FROM Employeeso数据架构：数据架构：星型架构星型架构o主键字段：主键字段：EmployeeID设计产品维度表设计产品维度表产品维度表名称：产品维度表名称：Product数据源：数据源：Products表表数据字段：数据字段：ProductID,来自来自Products表表CategoryID,来自来自Produc
32、ts表表ProductName,来自来自Products表表QuantityPerUnit:来自来自Products表表SQL语句语句SELECT ProductID,ProductName,QuantityPerUnitFROM Products数据架构：数据架构：雪花式架构雪花式架构主键字段：主键字段：ProductID设计产品维度表o产品分类维度表名称：产品分类维度表名称：Categoryo数据源：数据源：Categories表表o数据字段：数据字段：nCategoryID,来自来自Categories表表nCategoryName,来自来自Categoies表表oSQL语句语句 SEL
33、ECT CategoryID,CategoryName FROM Categorieso数据架构：数据架构：雪花式架构雪花式架构o主键字段：主键字段：CategoryID设计供应商维度表o供货商维度表名称：供货商维度表名称：Suppliero数据源：数据源：Suppliers表表o数据字段：数据字段：nSupplierID,来自来自Suppliers表表nSupplierName=CompanyNamenCompanyName,来自来自Suppliers表表oSQL语句语句 SELECT SupplierID,CompanyName,SupplierName FROM Supplierso数据
34、架构：数据架构：星型架构星型架构o主键字段：主键字段：SupplierID设计顾客维度o顾客维度表名称：顾客维度表名称：Customero数据源：数据源：Customers表表o数据字段：数据字段：nCustomerID,来自来自Customers表表nCustomerName,=来自来自Customers表表nCity,来自来自Customers表表nCountry,来自来自Customers表表oSQL语句语句 SELECT CustomerID,CustomerName,City,Region,Country FROM Supplierso数据架构：数据架构：星型架构星型架构o层次字段：
35、层次字段：Country|Region|Cityo主键字段：主键字段：CustomerID设计时间维度表o时间维度表名称：时间维度表名称：Timeo数据源：数据源：Orders表表o数据字段：数据字段：nOrderDate,来自来自Orders表表oSQL语句语句 SELECT DISTINCT OrderDate FROM Supplierso层次字段：层次字段：具有先天的层次结构具有先天的层次结构o主键字段：主键字段：OrderDate订购日期订购日期月月季季年年时间时间维度表维度表员工识别码员工识别码员工姓名员工姓名员工职务员工职务员工员工维度表维度表员工识别码员工识别码销售销售事实表事
36、实表产品识别码产品识别码供应商识别码供应商识别码订购日期订购日期产品单价产品单价总价总价订购数量订购数量折扣折扣顾客识别码顾客识别码产品识别码产品识别码分类识别码分类识别码产品名称产品名称每单位数量每单位数量产品产品维度表维度表顾客识别码顾客识别码顾客顾客维度表维度表顾客姓名顾客姓名城市城市国家国家地区地区供应商识别码供应商识别码供应商供应商维度表维度表供应商名称供应商名称分类识别码分类识别码产品分类产品分类维度表维度表分类名称分类名称星型雪花架构星型雪花架构多重维度架构多重维度架构o分析数据时并不总是以单一的维度为基准，分析数据时并不总是以单一的维度为基准，而是以多个维度为根据，而是以多个维
37、度为根据，5个维度要考虑成个维度要考虑成一个多维数据集。一个多维数据集。例：例：n查询春兰于查询春兰于2002年提供了多少金额的产品？年提供了多少金额的产品？n查询张三员工在查询张三员工在2002年销售了多少金额的手机年销售了多少金额的手机？SQL Sever 2000的数据转移的数据转移o数据转换服务数据转换服务DTS(Data Transformation Service)。这。这是一套功能强大的数据转换工具。它可以在是一套功能强大的数据转换工具。它可以在SQL Server与其与其它的它的OLE DB、ODBC数据源以及文字文件之间执行数据导入、数据源以及文字文件之间执行数据导入、导出以
38、及转换的服务。导出以及转换的服务。oDTS提供了：提供了：n（1）DTS向导：简单的转换功能向导：简单的转换功能n（2）DTS设计器：可以设计更复杂、功能更强的转换任务设计器：可以设计更复杂、功能更强的转换任务o数据转换的步骤：数据转换的步骤：n设置数据源设置数据源n设置数据目的地设置数据目的地n设置转换方式设置转换方式n将数据转移任务存储为一个包将数据转移任务存储为一个包n执行包进行实际数据转换执行包进行实际数据转换选择数据源 选择目的地数据转移过程o用用SQL Server工具为工具为Northwind新建一新建一个数据库，命名为个数据库，命名为Northwind_DWo使用使用DTS工具建立一个包工具建立一个包（1）数据源：）数据源：Northwind（2）目的地：）目的地：Northwind_DWo转移维度表转移维度表员工维度供货商维度产品维度（雪花型：分类表加产品表）o分类表产品维度（雪花型：分类表加产品表）o产品表顾客维度时间维度事实表创建多维数据集 o1）选择事实表创建多维数据集o选择度量值创建多维数据集o创建维表 创建多维数据集o添加计算成员 示例的数据仓库构架 基本的OLAP操作示例