好用的建站系统,wordpress置顶插件,个人网站页面设计作品,led视频网站建设Adventure Works案例分析
前言
数据时代来临#xff0c;但一个人要顺应时代的发展是真理。
数据分析的核心要素
那数分到底是什么#xff1f;
显然DT 并不等同于 IT#xff0c;我们需要的不仅仅是更快的服务器、更多的数据、更好用的工具。这些都是重要的组成部分…Adventure Works案例分析
前言
数据时代来临但一个人要顺应时代的发展是真理。
数据分析的核心要素
那数分到底是什么
显然DT 并不等同于 IT我们需要的不仅仅是更快的服务器、更多的数据、更好用的工具。这些都是重要的组成部分但还不是数据分析的全部。在目前这个信息过载的年代数分的核心价值尤为凸显甚至比以往更加重要。 我们尝试从这中文去理解“数据分析”简称数分的实质。数分是一门关于将通过分析于解释数字与凭据之间特征从而获取价值的学科。数分是一门综合性的学科你需要懂得多于一个角度的知识才能成为真正意义上的数分人员。 对于数分从业人员你需要掌握以下三方面的知识。你可以有偏重某一个领域但同时也应该意识到其他领域的重要性与必要性。 微软靠成熟的SQL方案
当需要处理规模庞大的企业级别数据时自助分析模式就显得力不从心存在明显的弊端。
1 非中心化数据模型设计无法形成唯一可信数据源。
2 自助模式缺乏企业复杂模型设计的能力。
3 无法满足企业规模化分析性能的需求。
4 缺乏表级别和列级别的安全控制。
为解决以上挑战企业方案会选择在数据仓库与前端工具中间安装SSASSQL Server Analysis Service微软SQL分析服务您可以理解SSAS为一个专门的OLAPOn-Line Analytical Processing在线分析处理工具当用户进行查询服务时OLAP分析系统可更快地响应查询请求支持稳定的性能输出。再者所有的模型与安全设置都在SSAS层面完成更符合了企业模型中心化设计的理念见下图SSAS的功能定位 。 图中的ETL/ELT代表两种不同的数据清理过程分别是Extract Transform Load 提取 转换 加载/ Extract Load Transform 提取 加载 转换。在企业级BI解决方案中这部分工作由专门的工具完成在自助式BI中Power BI中的Power Query可独立完成此功能。
Analysis Services是微软企业级BI的代表全称SQL Server Analysis ServicesSSAS是SQL Server产品中的一个组件。相信大多数读者都听过微软的SQL Server产品一款经典的数据库工具1989年出道的SQL可谓是IT老江湖了下图为经典SQL操作界面。
经过多年的摸爬滚打发展SQL Server从最初仅有的数据存储方案跃身成为数据功能丰富的数据解决方案。SQL Server提供了三大核心利器分别是SSISSQL Server Integration ServicesSQL服务器集成服务、SSASSQL Server Analysis Services SQ服务器分析服务、SSRSSQL Server Reporting ServicesSQL服务器报表服务[GY5] 它们提供的实质功能分别是数据准备方案SSIS、数据模型方案SSAS和分析报表方案SSRS。
对于没商务智能背景的读者而言这样解释也许稍显难以理解我们有一幅通俗易懂的来说明三者在BI领域中的作用下图将数据分析过程比喻为烹饪的三个必要步骤洗菜数据准备、烹饪数据建模、就餐数据呈现。
对于一个完整的数据分析过程这三步是缺一不可的。微软靠成熟的SQL方案占据市场的主导地位。时过境迁如今SSRS服务已经渐渐退出舞台取而代之的是Power BI服务后者在敏捷性与可视化能力层面皆全面明显地超越SSRS。SSIS服务也渐渐被Azure Data Factory与Databricks等新产品部分取代。而SSAS作为企业级中心化数据模型方案至今还是广受欢迎在短时间内还没有替代SSAS的产物。
数据仓库建模——维度表、事实表与星型模式
一、维度建模的基本概念
维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的方法。
它本身属于一种关系建模方法但和在操作型数据库中介绍的关系建模方法相比增加了两个概念
1. 维度表(dimension) 表示对分析主题所属类型的描述。 比如昨天早上张三在京东花费200元购买了一个皮包。那么以购买为主题进行分析可从这段信息中提取三个维度时间维度(昨天早上)地点维度(京东), 商品维度(皮包)。通常来说维度表信息比较固定且数据量小。
2. 事实表(fact table) 表示对分析主题的度量。 比如上面那个例子中200元就是事实信息。
事实表包含了与各维度表相关联的外码并通过JOIN方式与维度表关联。事实表的度量通常是数值类型且记录数会不断增加表规模迅速增长。
3. 联系
表可以分为维度表和事实表事实表做计算维度表做筛选维度表可以放上面事实表放下面模型表的关系中的1表示唯一*表示可以重复主页选项卡可以点“管理关系”可以快速对关系进行删除等操作模型表的关系的箭头表示筛选的方向
二、维度建模的三种模式 星型结构深度是1推荐 雪花结构深度大于1 1. 星形模式
星形模式(Star Schema)是最常用的维度建模方式下图展示了使用星形模式进行维度建模的关系结构 星型模式可以看出星形模式的维度建模由一个事实表和一组维度表组成深度是1且具有以下特点
维度表只和事实表关联维度表之间没有关联每个维度表的主码为单列且该主码放置在事实表中作为两边连接的外码以事实表为核心维度表围绕核心呈星形分布。
2. 雪花模式
雪花模式(Snowflake Schema)是对星形模式的扩展每个维度表可继续向外连接多个子维度表深度大于1。下图为使用雪花模式进行维度建模的关系结构 雪花模式
星形模式中的维度表相对雪花模式来说要大而且不满足规范化设计。雪花模型相当于将星形模式的大维度表拆分成小维度表满足了规范化设计。然而这种模式在实际应用中很少见因为这样做会导致开发难度增大而数据冗余问题在数据仓库里并不严重。
3. 星座模式
星座模式(Fact Constellations Schema)也是星型模式的扩展。基于这种思想就有了星座模式 星座模式 前面介绍的两种维度建模方法都是多维表对应单事实表但在很多时候维度空间内的事实表不止一个而一个维表也可能被多个事实表用到。在业务发展后期绝大部分维度建模都采用的是星座模式。
4. 三种模式对比
归纳一下星形模式/雪花模式/星座模式的关系如下图所示
三种模式的关系 雪花模式是将星型模式的维表进一步划分使各维度表均满足规范化设计。而星座模式则是允许星形模式中出现多个事实表。
三、建模技巧
通常在需求搜集完毕后便可进行维度建模了。Adventure Work Cycles案例即可采用星形模型维度建模。但不论采取何种模式维度建模的关键在于明确下面四个问题
1. 哪些维度对主题分析有用
Adventure Work Cycles案例中根据产品种类、销售区域、时间对销售额、销量进行分析是非常有帮助的。
2. 如何使用现有数据生成维度表
Adventure Work Cycles案例中样本数据已经生成维度表。
3. 用什么指标来度量主题
Adventure Work Cycles案例的主题是销售而销量和销售额这两个指标最能直观反映销售情况此外客单价、平均运费、平均税率等指标也可以进行分析。
4. 如何使用现有数据生成事实表
Adventure Work Cycles案例中样本数据已经生成事实表。 明确这四个问题后便能轻松完成维度建模。
一、项目背景介绍
Adventure Works Cycles是Adventure Works样本数据库所虚构的公司这是一家大型跨国制造公司。该公司生产和销售自行车到北美欧洲和亚洲的商业市场。虽然其基地业务位于华盛顿州博塞尔拥有290名员工但几个区域销售团队遍布整个市场。
1、客户类型
这家公司的客户主要有两种
个体这些客户购买商品是通过网上零售店铺商店这些是从Adventure Works Cycles销售代表处购买转售产品的零售店或批发店。
2、产品介绍
这家公司主要有下面四个产品线
Adventure Works Cycles生产的自行车自行车部件例如车轮踏板或制动组件从供应商处购买的自行车服装用于转售给Adventure Works Cycles的客户从供应商处购买的自行车配件用于转售给Adventure Works Cycles的客户。项目数据来源数据来源于adventure Works Cycles公司的的样本数据库。
二、需求分析与实现 项目目标通过现有数据监控商品的线上和线下销售情况并且获取最新的商品销售趋势以及区域分布情况为公司的制造和销售提供指导性建议以增加公司的收益。 项目任务
将数据导入Hive数据库 探索数据库并罗列分析指标 汇总数据建立数据仓库(Sales主题)
1、建表以及数据导入
目标为了操作方便不直接在hive里面去建表、导数而是把建表、导数语句写入shell脚本中然后在Linux服务器上运行脚本从而实现在hive库里面建表、导数。
AdventureWorksDW-data-warehouse-install-script.zip是数据源文件里面有一个.sql文件还有29个csv文件需要根据.sql文件内容创建hive表并把csv数据导入对应的表中。
注意这里csv文件的分隔符是 | 而建表格式用的是逗号需要用代码处理一下分隔符。
这里采用的是hive数据库它是一个用作数据仓库的数据库可以存储很大的数据量但是缺点是实时性不高、计算速度慢优点是可以进行大数据量的计算存储。生产里面一般用作底层数据的存储运算生成应用层汇总层的数据后再把数据推送到生成数据库如mysql等。
1提取建表语句
从.sql文件中提取建表语句存放到create_table.txt中。
CREATE TABLE [dbo].[DimCurrency]([CurrencyKey] [int] IDENTITY(1,1) NOT NULL,[CurrencyAlternateKey] [nchar](3) NOT NULL,[CurrencyName] [nvarchar](50) NOT NULL
) ON [PRIMARY];
GO2建表
使用python代码解析create_table.txt文件这里会用到文件读写、字符串的处理、正则表达式等方法。
定义一个字典从create_table.txt中逐行读取这29个表的表名和字段名并且存放在这个字典中。
create_fileopen(rcreate_table.txt) table_info {} contentcreate_file.readline() 使用re.search(pattern,string)即在字符串中寻找模式这里还用到了一个非贪婪匹配(.*?)re.I用于使匹配对大小写不敏感group()用于提取分组截获的字符串lstrip() 方法用于截掉字符串左边的空格或指定字符。
if CREATE TABLE in content.upper():seobj re.search(r\[(.*?)\].\[(.*?)\],content,re.I)if seobj:# 表名table_name seobj.group(2)
matOjb re.search(r\[(.*?)\] \[(.*?)\].*,content.lstrip(),re.I)
if matojb:# 字段名column matojb.group(1)# 类型type matojb.group(2)table_columns.append([column,type])创建create_table.sh文件解析字典中的表名和字段名在sh文件中写入建表语句hive -e “sql建表语句”。
#! /bin/sh 是指此脚本使用/bin/sh来解释执行#!是特殊的表示符其后面跟的是解释此脚本的shell的路径。
hive -v -e-v打印执行的sql语句-e后面接执行的sql语句无需进入hive。
shell_file open(rcreate_table.sh,w)
shell_file.writelines(#!/bin/sh\n\nhive -v -e\\nuse adventure_huangj;\n\n)shell_file.write()row format serde org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDewith serdeproperties(field.delim,,serialization.encodingUTF-8)stored as textfile;\n\n)
shell_file.write(\;)
shell_file.close()3数据格式转换
解析csv文件进行格式转换将分隔符 ‘|’ 转换为 ‘,’。
OS是操作系统比如Windows接口模块该模块提供了一些方便使用操作系统相关功能的函数。使用os.walk() 方法用于通过在目录树中游走输出在目录中的文件名。root 所指的是当前正在遍历的这个文件夹的本身的地址dirs 是一个 list 内容是该文件夹中所有的文件夹的名字(不包括子目录)即文件夹列表。files 同样是 list , 内容是该文件夹中所有的文件的名字(不包括子目录)即文件列表。os.path.join()用于拼接路径。
for root,dirs,files in os.walk(fromfile):for file in files:file_path os.path.join(root,file)使用read_csv()读取file_path路径下的文件sep“|”。然后用to_csv()输出成逗号分隔的csv文件sep 默认是,。
df pd.read_csv(file_path,sep|,encodingutf-16LE,headerNone,na_valuesnull,dtypestr)to_filepathos.path.join(tofile,file)df.to_csv(to_filepath,indexFalse,headerFalse,float_formatNone,na_repnull)4导入数据
创建load_create_data.sh文件将29个csv文件导入数据库对应的表中。
load_file open(load_create_data.sh,w)
load_file.writelines(#!/bin/sh\n\nhive -v -e\\nuse adventure_huangj;\n\n)
for key in table_info.keys():load_file.write(load data local inpath \/root/HuangJ/adventure/%s.csv\ overwrite into table %s;\n%(key,key))
load_file.write(\n\;)
load_file.close()2、探索数据并罗列分析指标
目的了解数据库包含哪些信息根据业务需要罗列可分析的指标。
1查看数据库了解包含哪些可用信息
数据库共有29个表主要可以分成两类
一类是维度表dim开头另一类是事实表fact开头。
两种表通过主键连接表的设计结构为星型模型。
维度表表示对分析主题属性的描述。
比如
地理位置维度表包含地理位置id、城市、州/省代码、州/省名称、国家/地区代码等描述信息产品维度表包含产品id、产品名称、颜色、尺寸、重量等描述信息。
通常来说维度表信息比较固定且数据量小。
事实表表示对分析主题的度量。
比如网络销售事实表包含客户id、下单时间、销售额、下单量等信息。
事实表包含了与各维度表相关联的外码并通过JOIN方式与维度表关联。事实表的度量通常是数值类型且记录数会不断增加表规模迅速增长。 2明确分析目标分解任务
结合项目目标和现有数据明确分析目标是要向老板以及项目团队展示产品的销售情况 整合数据仓库的数据构建E-R图挖掘销售事实表与各维度表的关联 构建与销售相关的指标体系。
3数据分析与初步整理
产品的销售渠道有两种
一种是线上销售网络销售数据存在factinternetsales事实表中另一种是线下销售经销商销售数据存在factresellersales事实表中。
a. E-R图
通过E-R图进一步分析事实表与各维度表之间的关联比如线上销售渠道
factinternetsales事实表中productkey、customerkey、promotionkey、salesterritorykey等字段与维度表有关联。
同时产品相关的维度表有三个它们之间也存在一定的关联。
factresellersales事实表与factinternetsales事实表的区别在于线上销售每一笔订单都直接面向最终客户因此通过customerkey与dimcustomer维度表关联。
而线下销售是通过经销商进行售卖每一笔订单都有记录经销商和销售人员的信息因此通过resellerkey与dimreseller维度表关联通过employee与dimemployee关联。
b. 指标体系
分析维度
时间维度——年、季度、月、周、日地区维度——销售大区、国家、州/省、城市产品维度——产品类别、产品子类推广维度客户维度经销商维度员工维度
分析指标
总销售额总订单量总成本产品标准成本税费运费总利润总销售额-总成本收入利润率总利润/总销售额客单价总销售额/客户总数税费运费销售额、销量目标达成率不同维度时间、地区、产品下的销售额、订单量
3、建立数据仓库汇总层 目的根据实际业务需要对已经建立好的基础层数据进行加工并存放到数据仓库汇总层。 数据仓库的设计分为两层一个是 ODS 基础层一个是 DW 汇总层 。基础层用来存放基础数据即前面使用shell脚本导入的数据而汇总层用来存放我们使用基础层加工生成的数据。
前面已经从实际业务出发分析了网络销售事实表factinternetsales、经销商销售事实表factresellersales与各维度表之间的关联并且罗列出销售方面的关键分析指标。接下来需要建立一个汇总层用于存放加工后的维度表以及新建的销售数据汇总表。
这里为什么要对维度表进行加工呢虽然不经加工、直接导入PowerBI也可以但是数据表较多、数据量较大加载速度会很慢。而且字段太多不是每一个字段都会用到。
所以这里的加工包括两个层面一个是对相同类型的维度表做连接减少表的数量另一个是筛选过滤提取需要分析的关键字段。
另外这里对网络销售事实表factinternetsales和经销商销售事实表factresellersales进行整合提取需要分析的字段销售额、产品标准成本、运费、税费等并且创建新的字段成本、利润等以便全面分析线上和线下的销售情况。
####1建立数据仓库
新建一个数据库用于存放加工生成的数据包括加工后的维度表和事实表。
####2维度表加工
a. 连接三个产品方面的维度表
连接三个与产品相关的维度表产品维度表dimproduct、产品子类别维度表dimproductsubcategory、产品类别维度表dimproductcategory。提取需要使用的字段产品id、产品名称、产品类别id、产品类别名称、产品子类id、产品子类名称。
create table product_dw as
select a.productkey,a.englishproductname,b.productcategorykey,c.englishproductcategoryname,a.productsubcategorykey,b.englishproductsubcategoryname
from adventure_huangj.dimproduct a
left join adventure_huangj.dimproductsubcategory b
on a.productsubcategorykeyb.productsubcategorykey
left join adventure_huangj.dimproductcategory c
on b.productcategorykeyc.productcategorykey;b. 连接两个区域方面的维度表
连接两个与区域相关的维度表区域维度表dimsalesterritory、地理位置维度表dimgeography。提取需要使用的字段区域id、销售大区、销售国家、销售地区、州/省、地理位置id、城市。
create table territory_dw as
select a.salesterritorykey,a.salesterritorygroup,a.salesterritorycountry,a.salesterritoryregion,b.stateprovincename,b.geographykey,b.city
from adventure_huangj.dimsalesterritory a
left join adventure_huangj.dimgeography b
on a.salesterritorykeyb.salesterritorykey;c. 从各维度表提取分析字段
客户维度表dimcustomer客户id、地理位置id、性别、婚姻状况并且这里对出生日期birthdate字段进行处理生成新的字段年龄age。
create table customer_dw as
select customerkey,geographykey,gender,maritalstatus,
year(current_date())-year(concat_ws(-,substr(birthdate,1,4),substr(birthdate,5,2),substr(birthdate,7,2))) as age
from adventure_huangj.dimcustomer;推广维度表dimpromotion推广id、推广名称、折扣百分比、推广类型、推广类别。
create table promotion_dw as
select promotionkey,englishpromotionname,discountpct,
englishpromotiontype,englishpromotioncategory from adventure_huangj.dimpromotion;经销商维度表dimreseller经销商id、地理位置id、经销商名称、年销售额、年收入、开业年份。
create table reseller_dw as
select resellerkey,geographykey,resellername,annualsales,annualrevenue,yearopened
from adventure_huangj.dimreseller;员工维度表dimemployee员工id、销售区域id、部门。
create table employee_dw as
select employeekey,salesterritorykey,departmentname
from adventure_huangj.dimemployee;3事实表加工
创建销售汇总表sales_total_dw这里使用union all连接网络销售事实表factinternetsales和经销商销售事实表factresellersales注意union all连接的两个表列名和列数必须完全一致否则会报错。为了区分每一笔订单是线上还是线下销售记录新增一个标签销售渠道sales_channel线上为“internet”线下为“reseller”。
有一点需要注意事实表中的日期都是string格式这里使用concat_ws()对字符串进行处理转换成yyyy-mm-dd的格式。
部分代码如下
create table sales_total_dw
as
(select ……
null as resellerkey,
null as employeekey,
internet as sales_channel
from adventure_huangj.factinternetsales)
union all
(select ……
null as customerkey,
resellerkey,
employeekey,
reseller as sales_channel
from adventure_huangj.factresellersales);三、报表制作
目的将汇总层数据导入Power BI建立各表之间的关联并制作销售报表。
1、数据导入
将Power BI连接到hive数据库将加工后的事实表和维度表导入。
2、建立关联
如果在hive数据库汇总层中没有使用JOIN语句连接事实表与维度表还可以通过Power BI-管理关系建立加工后的事实表与加工后的维度表之间的关联关系如下
事实表与维度表的关系
3、制作报表
选择合适的可视化工具从多个维度展示销售情况。
1数据清洗
数据格式hive数据库中的数据导入后可能需要进行格式转换。比如文本格式转换为日期格式文本格式转换为整数格式文本格式转换为小数格式小数格式转换为百分比格式。虽然在汇总层中已经将日期处理成yyyy-mm-dd格式但是在Power BI中默认是文本格式要进行设置。这里想说明一点虽然Power BI中可以设置格式但是最好还是回到hive中进行数据清洗否则刷新数据时可能会出现格式不匹配。 新建度量值对于新增的客单价、收入利润率等指标可以通过新建度量值的方式进行处理。当然最好还是在hive中创建字段这样代码对其他项目做销售数据分析更有借鉴意义。
2核心操作
可视化工具这里用到的可视化工具有折线图、柱形图、折线-柱形组合图、仪表、卡片、柱状图、切片器、地图等。可以根据需要选择图例、轴、列以及设置数据处理方式求和、平均值、最大值、最小值等。 筛选器有三种筛选器视觉对象、此页、所有页面。这里用于日期、区域等字段的筛选。 书签窗格这里将按钮和书签结合使用用于制作导航栏和动态图表。 选择窗格可以选择显示/隐藏视觉对象这里用于bike和非bike类商品图表的切换显示。
3报表展示
报表一共有3页包括主页、时间趋势图、区域分布图。
a. 主页展示内容
基本销售指标包括销售额、订单量、成本、收入利润率、客单价、销售额目标完成率等
不同销售渠道的销售情况商品的成本结构从时间维度分析年度、季度、月度、周、日销售情况从地区维度分析在各大区、各国的销售情况从产品类别维度分析各类商品的销售情况以及bike类与非bike类的对比分析 b. 时间趋势图展示内容
按照季度、月份展示销售额、订单量、销售目标完成率、收入利润率等指标不同渠道的销售趋势对比分析区域、商品类型切片器当然这里还可以增加更多的时间维度比如年、周、日。 c. 区域分布图展示内容
按照销售大区、国家、州/省、城市逐级展示销售额和订单量等指标不同渠道的销售占比对比分析区域、商品类型切片器 https://www.shangyexinzhi.com/article/4243141.html
