使用 Excel 进行报告的时间正在迅速消失 - 用于呈现和分析信息的便捷工具的趋势在所有领域都可见。 我们内部一直在讨论报告的数字化，并选择了Tableau可视化和自助分析系统。 M.Video-Eldorado Group 分析解决方案和报告部门负责人 Alexander Bezugly 谈到了构建战斗仪表板的经验和结果。
我马上说，并不是所有计划都实现了，但是这次经历很有趣，我希望它对你也有用。 如果有人对如何做得更好有任何想法，我将非常感谢您的建议和想法。
下面是我们遇到的情况和学到的内容。
我们从哪里开始？
M.Video-Eldorado拥有完善的数据模型：具有所需存储深度的结构化信息和大量固定形式的报告（查看更多详情 ）。 分析人员可以利用这些数据在 Excel 中制作数据透视表或格式化新闻通讯，或者为最终用户制作精美的 PowerPoint 演示文稿。
大约两年前，我们开始在 SAP Analysis（一个 Excel 插件，本质上是 OLAP 引擎上的数据透视表）中创建分析报告，而不是固定格式的报告。 但这个工具并不能满足所有用户的需求；大多数用户继续使用经过分析师额外处理的信息。
我们的最终用户分为三类：
高层管理人员。 要求以清晰易懂的方式提供信息。
中层管理人员，高级用户。 对数据探索感兴趣，如果有工具的话能够独立构建报告。 他们成为 SAP Analysis 中分析报告的关键用户。
大众用户。 他们对独立分析数据不感兴趣；他们使用自由度有限的报告，采用时事通讯和 Excel 数据透视表的格式。
我们的想法是满足所有用户的需求，并为他们提供一个单一、方便的工具。 我们决定从高层管理人员开始。 他们需要易于使用的仪表板来分析关键业务结果。 所以，我们从Tableau开始，首先选择了两个方向：零售和在线销售指标，分析深度和广度有限，这将覆盖高层要求的大约80%的数据。
由于仪表板的用户是高层管理人员，因此产品的另一个额外 KPI 出现了——响应速度。 没有人会等待20-30秒数据更新。 导航应该在 4-5 秒内完成，或者更好的是立即完成。 遗憾的是，我们未能实现这一目标。
这就是我们主仪表板的布局：
关键思想是将主要 KPI 驱动因素（总共 19 个）组合在左侧，并在右侧按主要属性呈现其动态和细分。 任务看起来很简单，可视化是合乎逻辑且易于理解的，直到您深入了解细节。
细节1.数据量
我们的年销售额主表大约有 300 亿行。 由于需要反映去年和前年的动态，仅实际销售的数据量就在1亿行左右。 有关计划数据和在线销售块的信息也单独存储。 因此，即使我们使用了SAP HANA的列式内存数据库，从当前存储中动态选择一周内所有指标的查询速度也约为15-20秒。 这个问题的解决方案不言自明——数据的额外物化。 但它也有缺陷，下面将详细介绍。
详情2.非累加指标
我们的许多关键绩效指标都与收据数量相关。 该指示器表示行数的 COUNT DISTINCT（检查标题），并根据所选属性显示不同的金额。 例如，该指标及其导数应如何计算：
为了使您的计算正确，您可以：
- 在存储中动态计算此类指标；
- 对Tableau中的整个数据量进行计算，即根据 Tableau 中的请求，根据所选过滤器以收据位置的粒度提供所有数据；
- 创建一个具体化的展示，其中将在所有示例选项中计算所有指标，从而给出不同的非相加结果。
很明显，在示例中UTE1和UTE2是代表产品层次结构的材料属性。 这不是一个静态的东西；公司内部的管理是通过它进行的，因为不同的经理负责不同的产品组。 当所有级别发生变化时，当关系被修改时，当一个组从一个节点移动到另一个节点时，我们对这一层次结构进行了许多全局修订。 在传统的报告中，所有这些都是根据材料的属性即时计算的；在这些数据具体化的情况下，有必要开发一种机制来跟踪此类变化并自动重新加载历史数据。 这是一项非常重要的任务。
细节三、数据对比
这一点与上一点类似。 最重要的是，在分析一家公司时，通常会与上一时期进行几个层次的比较：
与上一时期的比较（日与日、周与周、月与月）
在这个比较中，假设根据用户选择的周期（例如一年中的第33周），我们应该显示到第32周的动态；如果我们选择一个月的数据，例如XNUMX月，那么这个比较将显示四月份的动态。
与去年比较
这里的主要细微差别是，当按天和按周进行比较时，您不会采用去年的同一天，即。 您不能只将当前年份减一。 您必须查看要比较的星期几。 相反，在比较月份时，您需要采用去年完全相同的日历日。 闰年也有细微差别。 在原始存储库中，所有信息均按天分发；没有单独的周、月或年字段。 因此，要在面板中获得完整的分析横截面，您需要计算的不是一个时期，例如一周，而是4周，然后比较这些数据，反映动态、偏差。 因此，这种用于生成动态比较的逻辑也可以在 Tableau 中或在店面侧实现。 是的，当然我们在设计阶段就知道并考虑了这些细节，但很难预测它们对最终仪表板性能的影响。
在实现仪表板时，我们遵循了漫长的敏捷之路。 我们的任务是提供一个工作工具，并尽快提供测试所需的数据。 因此，我们进行了冲刺，从最小化当前存储方面的工作开始。
第 1 部分：对 Tableau 的信心
为了简化 IT 支持并快速实施变更，我们决定在 Tableau 中制定计算非累加指标和比较过去期间的逻辑。
第 1 阶段。一切都是实时的，没有窗口修改。
在此阶段，我们将 Tableau 连接到当前店面，并决定查看如何计算一年的收据数量。
结果：
答案令人沮丧——20分钟。 通过网络传输数据，Tableau 负载较高。 我们意识到非相加指标的逻辑需要在HANA上实现。 这并没有让我们太害怕，我们已经在 BO 和分析方面拥有类似的经验，并且我们知道如何在 HANA 中构建快速展示，以生成正确计算的非附加指标。 现在剩下的就是将它们调整为 Tableau。
第二阶段。我们调整展示柜，没有具体化，一切都是动态的。
我们创建了一个单独的新展示，可以动态生成 TABLEAU 所需的数据。 总的来说，我们得到了很好的结果，我们将一周内生成所有指标的时间缩短到了 9-10 秒。 老实说，我们预计在 Tableau 中，仪表板的响应时间在第一次打开时为 20-30 秒，然后由于缓存从 10 秒到 12 秒，这通常适合我们。
结果：
首次打开仪表板：4-5 分钟
任意点击：3-4分钟
谁也没想到店面的工作量会增加这么多。
第 2 部分：深入了解 Tableau
第一阶段.Tableau性能分析与快速调优
我们开始分析 Tableau 大部分时间都花在哪里。 为此有相当好的工具，这当然是 Tableau 的一个优点。 我们发现的主要问题是 Tableau 正在构建的非常复杂的 SQL 查询。 它们主要与：
— 数据转置。 由于 Tableau 没有用于转置数据集的工具，为了构建包含所有 KPI 详细表示的仪表板左侧，我们必须使用案例创建一个表。 数据库中的 SQL 查询大小达到 120 个字符。
- 时间段的选择。 这样的数据库级别查询的编译时间比执行时间要长：
那些。 请求处理12秒+执行5秒。
我们决定简化Tableau端的计算逻辑，将另一部分计算移至店面和数据库层面。 这带来了良好的结果。
首先，我们动态进行转置，根据 wiki 上描述的这种方法，我们在 VIEW 计算的最后阶段通过完整的外连接来完成转置 и .
也就是说，我们制作了一个设置表——转置矩阵（21x21），并按行细分接收所有指标。
是：
后：
几乎没有时间花在数据库转置本身上。 本周所有指标的请求继续在大约 10 秒内得到处理。 但另一方面，根据特定指标构建仪表板则失去了灵活性，即仪表板的右侧展示了特定指标的动态和详细细分，以前展示柜的工作时间为 1-3 秒，因为该请求基于一个指标，现在数据库总是选择所有指标并过滤结果，然后再将结果返回到 Tableau。
结果，仪表板的速度下降了近3倍。
结果：
- 5 秒 – 解析仪表板、可视化
- 15-20 秒 - 准备编译查询并在 Tableau 中执行预计算
- 35-45 秒 - SQL 查询的编译及其在 Hana 中的并行顺序执行
- 5 秒 – 在 Tableau 中处理结果、排序、重新计算可视化
- 当然这样的结果不适合业务，我们继续优化。
第 2 阶段：Tableau 中的最低逻辑，完全具体化
我们知道，在运行 10 秒的店面上构建响应时间为几秒的仪表板是不可能的，因此我们考虑了在数据库端专门针对所需仪表板具体化数据的选项。 但我们遇到了上面描述的一个全球性问题——非累加性指标。 我们无法确保在更改筛选器或向下钻取时，Tableau 能够在针对不同产品层次结构预先设计的不同店面和级别之间灵活切换（在示例中，不带 UTE 的三个查询，使用 UTE1 和 UTE2 会生成不同的结果）。 因此，我们决定简化仪表板，放弃仪表板中的产品层次结构，看看简化版本能有多快。
因此，在最后阶段，我们组装了一个单独的存储库，在其中以转置形式添加了所有 KPI。 在数据库方面，对此类存储的任何请求都会在 0,1 - 0,3 秒内处理。 在仪表板中，我们收到以下结果：
首次打开：8-10秒
任意点击：6-7秒
Tableau 花费的时间包括：
- 0,3秒— SQL 查询的仪表板解析和编译
- 1,5-3秒。 — 在 Hana 中执行 SQL 查询以实现主要可视化（与步骤 1 并行运行）
- 1,5-2秒。 — 渲染、可视化的重新计算
- 1,3秒。 — 执行额外的 SQL 查询以获得相关过滤值（Brand、Division、City、Store），解析结果
简单总结一下
从可视化的角度来看，我们喜欢 Tableau 工具。 在原型设计阶段，我们考虑了各种可视化元素，并在库中找到了它们，包括复杂的多级分割和多驱动瀑布。
在实施具有关键销售指标的仪表板时，我们遇到了尚未能够克服的性能困难。 我们花了两个多月的时间，收到了一个功能不完整的仪表板，其响应速度处于可接受的边缘。 我们自己得出结论：
- Tableau 无法处理大量数据。 如果在原始数据模型中您有超过 10 GB 的数据（大约 200 亿 X 50 行），那么仪表板将严重变慢 - 每次点击从 10 秒到几分钟。 我们尝试了实时连接和提取。 运行速度相当。
- 使用多个存储（数据集）时的限制。 无法使用标准方法来指示数据集之间的关系。 如果您使用变通办法来连接数据集，这将极大地影响性能。 在我们的案例中，我们考虑了在每个所需视图部分中具体化数据并在这些具体化数据集上进行切换，同时保留之前选择的过滤器的选项 - 事实证明，这在 Tableau 中是不可能做到的。
- 无法在 Tableau 中创建动态参数。 您无法在数据提取中或在实时连接期间填充用于过滤数据集的参数，该参数只能使用数据集中的另一个选择的结果或另一个 SQL 查询的结果，而只能是本机用户输入或常量。
- 与使用 OLAP|数据透视表元素构建仪表板相关的限制。
在 MSTR、SAP SAC、SAP Analysis 中，如果将数据集添加到报表中，则默认情况下其上的所有对象都是相互关联的。 Tableau 没有此功能；必须手动配置连接。 这可能更灵活，但对于我们所有的仪表板来说，这是对元素的强制性要求 - 因此这是额外的劳动力成本。 此外，如果你做了相关的过滤器，比如过滤一个地区时，城市列表仅限于该地区的城市，那么你会立即对数据库或Extract进行连续查询，这会明显减慢查询速度。仪表板。 - 功能上的限制。 批量转换无法在提取物上完成，尤其是在 Live-connecta 的数据集上。 这可以通过 Tableau Prep 来完成，但这是额外的工作，也是需要学习和维护的另一个工具。 例如，您不能转置数据或将其与其自身连接。 通过对各个列或字段进行转换来关闭什么，这些列或字段必须通过 case 或 if 来选择，这会生成非常复杂的 SQL 查询，其中数据库花费大部分时间来编译查询文本。 该工具的这些不灵活性必须在展示级别解决，这会导致更复杂的存储、额外的下载和转换。
我们没有放弃 Tableau。 但我们并不认为 Tableau 是一个能够构建工业仪表板的工具，也不认为是一个可以用来替换和数字化公司整个企业报告系统的工具。
我们现在正在另一个工具中积极开发类似的仪表板，同时尝试修改 Tableau 中的仪表板架构，以进一步简化它。 如果社区感兴趣，我们将告诉您结果。
我们也在等待您关于如何在 Tabeau 中针对如此大量的数据构建快速仪表板的想法或建议，因为我们的网站上的数据比零售业的数据多得多。
来源： habr.com