Foodmart批发店销售分析系统

1、课 程 设 计 任 务 书课程数据仓库课程设计题目Foodmart批发店销售分析系统序号6主要内容基于Foodmart实例数据库，创建数据库仓库并进行数据分析，要求满足以下分析需求。1. 按时间级别分析不同商店的销售额、销售成本额；2. 按时间级别分析每个产品的销售额、销售成本额；3. 按时间级别分析不同批发店（每个、城市、省、国家，拥有者）的销售额、销售成本额；4. 按时间级别分析不同批发店类型的的销售额、销售成本额；5. 按时间级别分析不同商店（每个、城市、省、国家）销售产品的销售额、销售成本额。6. 按时间级别分析不同地区的销售额、销售成本额。7. 按时间级别分析不同类产品的销售额、销售

2、成本额；要求基本要求1. 按需求完成详细的设计，包括事实表和维度的设计；2. 按设计方案选择相关数据表以及各表的字段，表之间的关系；3. 有详细的数据准备方案，包括验证、清理、转换、装载等；4. 将表中有关人名（如：销售人）的姓、名进行合并；5. 按设计方案构建多维数据集；6. 按需求分析数据。高级要求可分析按不同汇率显示销售额和销售成本额。工作计划及进度提前两周：分组、选题；提前一周：明确需求分析、组内分工；第一天：与指导老师讨论，确定需求、分工，并开始设计； 第二四天：构建多维数据集、分析数据；第五天：完成设计说明书，答辩；第六天：针对答辩意见修改设计说明书，打印、上交。指导教师签字 谭义

3、红 赵碧海 陈治平 2011年05月20日教研室审定意见签字 年 月 日目 录第一章 系统需求11.1需求分析1第二章 数据仓库设计22.1 确定基本信息22.2 事实表与维度表的设计22.2.1 设计事实表22.2.2 设计维度表2第三章 数据准备53.1 数据验证53.2 数据清理53.2.1 重复行数据的清理53.2.2 空值的处理63.3 数据转换73.3.1 数据类型转换73.3.2 对象名的转换73.3.3 数据编码的转换73.4数据表及字段的选择7第四章 多维数据集构建84.1自下而上的构建多维数据集104.1.1 创建项目104.1.2 添加新的度量值104.1.3添加新的维度

4、114.1.4 构架生成114.2 多维数据集视图12第五章 数据分析135.1按时间级别分析不同商店的销售额、销售成本额135.2按时间级别分析每个产品的销售额、销售成本额145.3 按时间级别分析不同批发店的销售额、销售成本额155.4按时间级别分析不同批发店类型的的销售额、销售成本额165.5按时间级别分析不同商店销售产品的销售额175.6 按时间级别分析不同地区的销售额、销售成本额。185.7 按时间级别分析不同类产品的销售额、销售成本额19结 论20参考文献21结束语22第一章 系统需求1.1需求分析在本案例中，作为批发店的决策者，最关注的信息就是销售额以及销售成本额。批发店的决策者

5、可以通过Foodmart批发店销售分析系统，按时间级别的不同浏览不同地区、不同批发店、不同商店、不同类产品的销售额及销售成本额等信息。更透彻的了解市场，为今后更好地作出销售决策以及相应的调整策略提供较全面的辅助和支持。要创建好这一系统，使其满足批发店决策者的使用要求。通过分析知，需满足以下需求：基本要求：1、按时间级别分析不同商店的销售额、销售成本额；2、按时间级别分析每个产品的销售额、销售成本额；3、按时间级别分析不同批发店（每个、城市、省、国家）的销售额、销售成本额；4、按时间级别分析不同批发店类型的销售额、销售成本额；5、按时间级别分析不同商店（每个、城市、省、国家，管理者）销售产品的销

6、售额、销售成本额；6、时间级别分析不同地区的销售额、销售成本额；7、时间级别分析不同类产品的销售额、销售成本额；高级要求可分析按不同汇率显示销售额和销售成本额。第二章 数据仓库设计2.1 确定基本信息通过第一章的系统需求分析可以确定以下几点：（1）分析的主题：Foodmart批发店销售系统分析（2）分析的数值（事实）：销售额以及销售成本额（3）分析的角度（维度）：时间、商店、产品、产品类型、批发店、批发店类型、地区（4）分析的维度（维度级别）：l 时间（年、季度、月、日）l 商店（不同区域城市、省、国家）l 产品 l 产品类型l 批发店l 批发店类型l 地区城市、省、国家 2.2 事实表与维度

7、表的设计通过确立的7个维度级别和对数据库的数据分析，我们从foodmart2000数据库中的已给的24张表中挑选出了9张对我们分析销售有用的表，将inventory_fact_1997表、inventory_fact_1998表作为多维数据集的事实表。product表、product_class表、region表、store表、time_by_day表、warehouse表、warehouse_class表作为多维数据集的维度表。2.2.1 设计事实表字段数据类型来 源功能描述连接字段product_idintproduct表产品编号time_idinttime_by_day表订购时间编号wa

8、rehouse_idintwarehouse表批发店编号store_idintstore表商店编号度量值warehouse_salesmoneyinventory_fact_1997和inventory_fact_1998销售额warehouse_costmoneyinventory_fact_1997和inventory_fact_1998销售成本额表1 事实表批发店销售信息2.2.2 设计维度表1. product维度表的设计字段名数据类型来源表说 明product_idintproduct表主键product_class_idintproduct_class表产品类型编号product_

9、namenvarchar(255)product表产品名称表2 product维度表结构2.product_class维度表的设计字段名数据类型来 源表说 明product_class_id intproduct_class表主键product_subcategorynvarchar(50)product_class表子类型名product_categorynvarchar(50)product_class表类型名product_departmentnvarchar(50)product_class表产品部门product_familynvarchar(50)product_class表产品系

10、列表3 product_class维度表结构3. store维度表的设计字段名数据类型来 源表说明store_idintstore表主键region_idintregion表地区编号store_namenvarchar(255)store表商店名称store_citynvarchar(50)store表商店所在城市store_statenvarchar(50)store表商店所在州store_countrynvarchar(50)store表商店所在国家store_managernvarchar(255)store表商店管理者表4 store维度表结构4.region维度表的设计字段名数据类型

11、来 源表说明region_idintregion表主键sales_regionnvarchar(50)region表地域sales_citynvarchar(50)region表城市表5 region维度表结构 5.time_by_day维度表设计字段名数据类型来 源表说明time_idinttime_by_day表主键the_datedatetimetime_by_day表日期the_daynvarchar（15）time_by_day表日期the_monthnvarchar（15）time_by_day表月份the_yearsmallinttime_by_day表年day_of_month

12、smallinttime_by_day表每月的某日quarternvarchar（2）time_by_day表季度表6 time_by_day维度表结构6.warehouse维度表的设计字段名数据类型来 源表说明warehouse_idintwarehouse表主键warehouse_class_idintwarehouse-class表批发店类型编号warehouse_namenvarchar(100)warehouse表 批发商名warehouse_citynvarchar(50)warehouse表 批发店所在城市warehouse_state_provincenvarchar(50)w

13、arehouse表批发店所在州warehouse_countrynvarchar(50)warehouse表 批发店所在国家表7 warehouse维度表结构7.warehouse_class维度表的设计字段名数据类型来 源表说明warehouse-class-id intwarehouse_class表主键description nvarchar(50)warehouse_class表类型描述表8 warehouse_class维度表结构第三章 数据准备3.1 数据验证为了避免数据冗余，在数据被加载到数据仓库之前，须对数据进行有效性检查。在建立多维数据集之前对数据的参照完整性进行验证，并对不

14、完整的数据进行必要的处理（补充主码值或删除不符合参照完整性约束的外码记录，可用SQL语句来验证），之后建立事实数据表和维度表之间的关联关系。将Foodmart装载到数据库管理系统中，分别对存在参照的表进行参照完整性检验并补充必要的主码值：需要进行参照完整性验证的表有批发店销售信息表、产品信息表、商店信息表、批发店信息表。 1） 在product(产品信息表)中编写SQL语句验证其参照完整性: select distinct product_class_id from product where product class_id not in (select product _class_id

15、from product_class)2） 在store(商店信息表)中编写SQL语句验证其参照完整性: select distinct region_id from store where region_id not in (select region_id from region)3） 在warehouse(批发店信息表)中编写SQL语句验证其参照完整性: select distinct warehouse_class_id from warehouse where warehouse_class_id not in (select warehouse_class_id from ware

16、house_class) 经过验证，符合参照完整性要求，无须再进行其他操作。3.2 数据清理在建立表的时候，我们选择的是对该项目分析有帮助字段，所以无用字段的清理这一步骤不需要。经过对数据表的分析我们需要对重复行数据和空值进行处理。3.2.1 重复行数据的清理1.Product （产品信息表）：select product_id from productgroup by product_id having count(product_id)12. product_class（产品类型信息）表：select product_class_idfrom product_classgroup by p

17、roduct_class_idhaving count(product_class_id)1 3. region（地区信息）表：select region_id from regiongroup by region_id having count(region_id)14. store（商店信息）表：select store_id from storegroup by store_id having count(store_id)15. time_by_day（时间信息）表：select time_id from time_by_daygroup by time_id having count

18、(time_id)16. warehouse（批发店信息）表：select warehouse_idfrom warehousegroup by warehouse_id having count(warehouse_id)17. warehouse_class（批发店类型信息）表：select warehouse_class_idfrom warehouse_classgroup by warehouse_class_id having count(warehouse_class_id)1经查找发现，该七张表都没有重复行数据，所以不需要再做其他处理。3.2.2 空值的处理 Store（商店信

19、息）表:Sql语句：select store_id,store_name,store_city,store_state,store_country,store_manager from storewhere store_name is null or store_city is null or store_state is null or store_country is null or store_manager is null结果：经过查找发现：商店信息表中字段store_id值为0的一行中store_manager的值为NULL针对以上结果我们将商店信息表中的store_id为0的一行删

20、除，SQL语句为：delete store where store_id=03.3 数据转换3.3.1 数据类型转换存在时间类型的表只有时间信息表中，其中的字段the_date 的类型是日期时间存储格式，因此不需要做该处理。3.3.2 对象名的转换 由于已经挑选出对我们分析有用的表，所以不需要进行对象名的转换。3.3.3 数据编码的转换在关系数据库中，很多数据都是以编码的形式保存，而编码的具体含义则保存在数据字典中，这给分析者分析数据造成了一定的困难，因此需要将这些编码的数据转化为易于理解的中文文字。我们将时间信息表中的quarter字段转换陈其对应的季度名称,由于quarter字段的长度只有

21、2个字符,在转换之前还应该将数据类型改为varchar（10），代码如下：Alter Table time_by_day Alter Column quarter varchar(10)UPDATE time_by_day SET quarter= CASE quarter WHEN Q1 THEN 第一季度 WHEN Q2 THEN 第二季度 WHEN Q3 THEN 第三季度 WHEN Q4 THEN 第四季度 END3.4数据表及字段的选择根据系统设计需求，设计数据仓库需要用到的表有：，inventory_fact 表、inventory_fact_1997表、inventory_fac

22、t_1998表、product表、product_class表、region表、store表、time_by_day表、warehouse表、warehouse_class。Product表：列数据类型为空性说 明product_idint非空主键product_class_idint空产品类型编号product_namenvarchar(255)空产品名称Product_class表： 列数据类型为空性说 明product_class_idint空主键product_subcategorynvarchar(50)空子类型名product_categorynvarchar(50)空类型名pro

23、duct_departmentnvarchar(50)空产品部门product_familynvarchar(50)空产品系列Region表： 列数据类型是否可为空说明region_idint非空主键sales_regionnvarchar(50)空地域Inventory_fact_1997表和Inventory_fact_1998表：列数据类型是否可为空说明product_idint空产品编号time_idint空时间编号warehouse_idint空 批发店编号store_idint空 商店编号warehouse_salesmoney空批发店销售warehouse_costmoney空

24、批发店花费：Store表：列数据类型是否可为空说明product_idint空产品编号time_idint空时间编号warehouse_idint空 批发店编号store_idint空 商店编号warehouse_salesmoney空批发店销售warehouse_costmoney空 批发店花费Time_by_day表：列数据类型是否可为空说明product_idint空产品编号time_idint空时间编号warehouse_idint空 批发店编号store_idint空 商店编号warehouse_salesmoney空批发店销售warehouse_costmoney空 批发店花费Wa

25、rehouse表：列数据类型是否可为空说明warehouse_idint非空主键warehouse_class_idint空批发店类型编号warehouse_namenvarchar(100)空 批发商名warehouse_citynvarchar(50)空 批发店所在城市warehouse_state_provincenvarchar(50)空批发店所在州warehouse_countrynvarchar(50)空 批发店所在国家Warehouse_class表：列数据类型是否可为空说明warehouse_idint非空主键warehouse_class_idint空批发店类型编号wareh

26、ouse_namenvarchar(100)空 批发商名warehouse_citynvarchar(50)空 批发店所在城市warehouse_state_provincenvarchar(50)空批发店所在州warehouse_countrynvarchar(50)空 批发店所在国家第四章 多维数据集构建4.1自下而上的构建多维数据集4.1.1 创建项目4.1.2 添加新的度量值4.1.3添加新的维度：4.1.4 构架生成4.2 多维数据集视图第五章 数据分析5.1按时间级别分析不同商店的销售额、销售成本额1.浏览器查看2.MDX查看select crossjoin(时间.年.年,时间.月

27、份.月份) on 1,crossjoin(商店维度.Store Name.Store Name,Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集5.2按时间级别分析每个产品的销售额、销售成本额1.浏览器查看2. MDX查看select crossjoin(时间.年.年,时间.月份.月份) on 1,crossjoin(产品维度.Product Name.Product Name,Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集5.3 按时间级别分析不同批发店的销售额、销售成本额1.浏

28、览器查看2.MDX查看select crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月份)on 1,crossjoin(crossjoin(crossjoin(批发店维度.Warehouse State Province.Warehouse State Province,批发店维度.Warehouse Country.Warehouse Country),批发店维度.Warehouse City.Warehouse City),Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集5.4按时间级别分析不同批

29、发店类型的的销售额、销售成本额1.浏览器查看2.MDX查看select crossjoin(crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月份),时间.日期.日期) on 1,crossjoin(批发店类型维度.批发店类型维度.members,Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集5.5按时间级别分析不同商店销售产品的销售额1.浏览器查看2.MDX查看select crossjoin(crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月份),时间.日期.

30、日期) on 1,crossjoin(crossjoin(crossjoin(商店维度.Store Country.members,商店维度.Store State.members),商店维度.Store City.members),Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集select crossjoin(crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月份),时间.日期.日期) on 1,crossjoin(商店维度.Store Manager.members,Measures.costs,

31、Measures.销售额) on 0 from foodmart多维数据集5.6 按时间级别分析不同地区的销售额、销售成本额1.浏览器查看2、MDX查看select crossjoin(crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月份),时间.日期.日期) on 1,crossjoin(crossjoin(地区维度.Region Id.members,地区维度.Sales Region.members),Measures.销售成本额,Measures.销售额) on 0 from foodmart多维数据集5.7 按时间级别分析不同类产品的销售额、销售成本额1.浏览器查看2.MDX查看select crossjoin(crossjoin(crossjoin(时间.年.年,时间.季度.季度),时间.月份.月