本文作者：吴新星、倪翀儿

目录

当订单与销售明细在不同的事实表，千笔订单商品数量如何计算？

如何计算千笔订单商品数量

Kyligence Cloud + Kyligence MDX 实现跨事实表分析

前置准备

创建模型

创建数据集

连接 Excel 进行分析

---

当订单与销售明细在不同的事实表，千笔订单商品数量如何计算？

Curly's 是一家快速增长中的中型餐饮公司，在全国有十多家门店，提供优质低价的快餐食品，其中精品冰淇淋、咖啡、炸鸡等更是广受好评。公司目前希望利用数据更准确地洞察经营状态，如对比不同门店的同期销售状况。门店销量受城市、商圈、规模等多方面因素，如果仅以销售总量进行比较，未必能相对客观地展现实际销售状况。

餐饮行业常以每千笔订单的商品销售量衡量客户在千笔交易中购买的平均商品数量，数量越大说明千笔交易中客户购买量越大。此数值可用于对比不同门店销量；计算单品销量，统计商品畅销程度；设定促销目标等。

为此 Curly's 决定以各门店每千笔订单的商品销售量作为指标或零售 KPI，计算平均销售能力。当前公司的销售信息一般分为小票信息与小票明细信息两类存储，如何才能连接两类信息来统计千笔订单商品数量？

如何计算千笔订单商品数量

如前所说，Curly's 的销售事实数据以小票信息和小票明细信息存储，属于不同类型的事实记录。那么可以考虑利用 Kyligence 分别为小票信息和小票明细创建一张事实表。示例如下：

 其中基于小票的事实表，即订单头表的颗粒度仅到整张小票级别、每行代表一个小票。订单明细表则是每一个小票中含有的商品明细，该事实表的颗粒度为商品级别，每行代表一种商品和其数量，如 2 杯咖啡、4 个鸡翅、5 份冰淇淋等。要计算每千笔订单（小票）中含有的商品数量，我们需要关联两张事实表。

Kyligence Cloud + Kyligence MDX 实现跨事实表分析

要实现跨事实表分析，首先我们可以通过时间维度关联订单头表和订单明细表。此外，在单表情况下常常需用避免重复计数，但在类似本文的零售场景中，分别从订单头表中获取订单数量，并从订单明细表中获取商品数量的做法，省去了去重处理这一步，使用效率将远高于直接用 SQL 进行查询。另外，我们还可以通过 Kyligence MDX 更加便捷地创建时间层级结构，方便在 BI 应用端查看不同时间层级下的销售额信息，比如年、月或日。

基于以上分析，我们可以分三步完成跨事实表分析：

1. 在 Kyligence Cloud 平台分别用订单头表和订单交易表作为事实表，并分别与时间维表关联，创建星型模型。
2. 在 Kyligence MDX 中创建数据集，将两个模型用时间维表作为公共维表，创建星座模型。
3. 创建一个层级结构，方便基于日期维度来查看千笔订单（小票）商品数量。

前置准备

• Kyligence Test Drive：推荐前往 Kyligence 英文官网进行注册， 一站式体验模型和数据集相关操作。账号注册及相关操作，可以参考 Kyligence 快速上手指南。每次启动 Test Drive 环境有两小时的操作时长限制，请注意时间控制。

• Excel: 2007 及以上版本（Window 环境）
• 数据集：本案例中用的事实表和维度表如下，请保存为 .csv 文件方便后续上传。

此数据集包含 FACT_HEADER (订单头表） 、FACT_DETAILS （订单明细表）、DIM_SIMPLE_DATE （时间维度表）三张表。数据如下：

事实表 - 订单头表（ FACT_HEADER ）

事实表 - 订单明细表（FACT_DETAILS）

维度表 - 时间维度表 （DIM_SIMPLE_DATE）

接下来让我们开始计算千笔订单商品数量吧！

创建模型

1. 在试用页面，点击 Start a New Trial （开始新的试用）。系统将跳转至在线试用环境界面，点击 Start （开始）。系统将开始准备您的试用环境，预计将在 15 分钟内准备完毕。当试用环境准备就绪，我们会向您发送通知邮件，您可以点击试用页面或邮件中的按钮（链接）开始试用。

 2. 我们接下来将开始数据源的准备，点击数据源下的管理。

 3. 点击创建表，选择向导模式，选择上传 CSV，上传前置准备中的表格。我们将针对本项目创建一个新的数据库，并命名为 MULTIFACTCAL，同时输入表名，点击下一步，在随后的页面中点击提交，至此，我们完成了第一张表的上传。重复这一步骤，完成第二张和第三张表的上传，唯一的不同是我们这次将选择已经创建好的 MULTIFACTCAL 数据集。

 4. 接下来我们需要继续进行表的加载，点击数据源 > 加载表，点击选择 MULTIFACTCAL，点击提交。系统将返回数据加载成功，至此，我们完成了数据准备工作。

 5. 在左侧导航栏，选择模型 > SQL 建模。

 将建模用的 SQL 语句另存为 txt 或 sql 格式的文件。

SELECT
    MultiFactCal.FACT_DETAILS.TRANSID,
    MultiFactCal.FACT_DETAILS.PRODNAME,
    MultiFactCal.FACT_DETAILS.QUANTITY,
    MultiFactCal.FACT_DETAILS.TRANSDATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.DATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.D_YEAR,
    MultiFactCal.DIM_SIMPLE_DATE.D_MONTH,
    MultiFactCal.DIM_SIMPLE_DATE.D_DAY,
    SUM(MultiFactCal.FACT_DETAILS.QUANTITY)
FROM
    MultiFactCal.FACT_DETAILS
    INNER JOIN MultiFactCal.DIM_SIMPLE_DATE
        ON MultiFactCal.FACT_DETAILS.TRANSDATEKEY = MultiFactCal.DIM_SIMPLE_DATE.DATEKEY
GROUP BY
    MultiFactCal.FACT_DETAILS.TRANSID,
    MultiFactCal.FACT_DETAILS.PRODNAME,
    MultiFactCal.FACT_DETAILS.QUANTITY,
    MultiFactCal.FACT_DETAILS.TRANSDATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.DATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.D_YEAR,
    MultiFactCal.DIM_SIMPLE_DATE.D_MONTH,
    MultiFactCal.DIM_SIMPLE_DATE.D_DAY
;
 
SELECT
    MultiFactCal.FACT_HEADER.TRANSID,
    MultiFactCal.FACT_HEADER.TRANSDATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.DATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.D_YEAR,
    MultiFactCal.DIM_SIMPLE_DATE.D_MONTH,
    MultiFactCal.DIM_SIMPLE_DATE.D_DAY
FROM
    MultiFactCal.FACT_HEADER
    INNER JOIN MultiFactCal.DIM_SIMPLE_DATE
        ON MultiFactCal.FACT_HEADER.TRANSDATEKEY = MultiFactCal.DIM_SIMPLE_DATE.DATEKEY
GROUP BY
    MultiFactCal.FACT_HEADER.TRANSID,
    MultiFactCal.FACT_HEADER.TRANSDATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.DATEKEY,
    MultiFactCal.DIM_SIMPLE_DATE.D_YEAR,
    MultiFactCal.DIM_SIMPLE_DATE.D_MONTH,
    MultiFactCal.DIM_SIMPLE_DATE.D_DAY
;

点击上传，选择保存的 SQL 文件，然后点击下一步。

8. 确认 SQL 完毕（ 状态为绿色），点击下一步继续导入。

 9. 将创建的两个模型分别命名为 MultiFactCal_Header 和 MultiFactCal_Details，同时确认勾选左下角的添加基础索引，然后点击确定。注：基础索引，可以省去为模型频繁手动创建索引的步骤。

 10. 进入模型菜单，分别点击打开新建的两个模型 MultiFactCal_Header 和 MultiFactCal_Details，可以看到创建的模型如下所示。

 11. 在两个模型上分别点击构建索引，选择全量构建。

              

至此我们已用两张事实表与维度表构建两个星型模型，接下来我们将通过 Kyligence MDX 关联这两个模型，创建星座模型，以便进行跨事实表分析。

创建数据集

1. 登入 Kyligence MDX 后，在左侧导航栏点击数据集，点击创建数据集。输入新数据集名称，例如 kuizhi_dataset。

2. 在定义关系页面，定义模型的关系：将 Kyligence Test Drive 中创建的两个模型 MultiFactCal_Header 和 MultiFactCal_Details 通过拖拽添加为关联星座模型，用时间维表 Dim_Simple_Date 作为公共维表。

        ​​         ​​​​​​ 

3. 在定义语义页面，创建层级结构：在维度部分，选中模型 MultiFactCal_Header 下的 DIM_SIMPLE_DATE 表，创建一个“年-月-日”的层级结构，命名为“Y-M-D”，并保存；层级结构包含 D_YEAR，D_MONTH 和 DATEKEY。点击保存。

通过添加年月日层级结构，在后续操作中我们可以基于日期的各级维度来查看千笔订单（小票）商品数量这个指标。

4. 修改模型 MultiFactCal_Details 的度量 SUM_FACT_DETAILS_QUANTITY，把度量名称修改为“商品数量”。

5. 选择模型 MultiFactCal_Header 的度量 COUNT_ALL_1，把度量 COUNT_ALL_1 的度量名称修改为“订单（小票）数量”。

 6. 创建计算度量，添加一个计算度量，命名为“千笔订单商品数量”，填写表达式后保存。

[Measures].[商品数量] / [Measures].[订单（小票）数量] * 1000

``````````````

后续选择默认选项，完成数据集的创建。

连接 Excel 进行分析

1. 在 Kyligence Cloud 平台点击左侧导航栏中的连接 BI，点击 Excel 下的查看教程，获取 Kyligence MDX 的连接信息和登录用户名 / 密码。

 2. 打开 Excel，新建工作簿，点击数据 -> 获取数据 -> 来自数据库 -> 自 Analysis Services。

 3. 输入 Kyligence MDX 的连接信息和登录用户名 / 密码。

 5. 选择您需要对接的 MDX 数据集，点击下一步（现在数据集已经被连接到 Excel 了）。

5.  勾选在文件中保存密码并勾选总是尝试使用此文件来刷新数据，点击完成。 现在我们可以在 Excel 里进行数据分析了。

6. 点击确定，创建数据透视表导入数据。

7. 在打开的数据透视表上进行字段的设置：设置行为 Y-M-D 日期层级结构；设置值为千笔订单商品数量，商品数量和订单（小票）数量。

8. 展开行标签的日期层级结构，可以按层级结构进行数据分析：每千笔订单（小票）中，含有的商品数量。

 如上图所示，我们可以查看到 2021 年 2 月 2 号和 3 号的千笔订单商品数量，此数据能够与同期其他门店的同类数据进行比较，更客观地展现各门店的销售能力。

除此之外，Kyligence MDX 还支持 YTD、QTD、MTD、YOY 和 MOM 等时间智能函数，方便用户以此作为业务指标，直接在 BI 产品中进行可视化分析。此外，您还可以在跨事实表分析时，加入更多维度，比如门店，查看具体门店每千笔订单中商品的销售分布，或者商品维度，查看特定商品的受欢迎程度等。

注：本场景旨在展示跨事实表分析的操作流程。如需进行数据验证，可在 Kyligence Enterprise 或 Cloud 平台录入 SQL 语句 select 1000 * SUM(商品数量) / COUNT_DISTINCT(交易ID) where 交易日期 = 'Y/M/D' 来计算），快速计算某时间的千笔订单商品数量，但尤其在多指标的情况下，此类做法远不如使用 Kyligence MDX 进行跨事实表查询简便灵活。

相关阅读：当商品跨多个分类时，如何精确统计总销售额？http://t.csdn.cn/jbblH