技术发展迅速，我们正在享受它。我们以满足我们需求的形式重塑每天创建的数据。如今，SQL Server表格模型（简称SSAS表格）是最常用的技术之一。许多行业决定在他们的新项目中使用SSAS表格，并且一些定义好的新项目将其现有的SSAS多维缓慢地转换为SSAS表格。我知道，关于SSAS多维与SSAS表格有很多争论。但是我的目的是避免进行此类讨论。就像标题所暗示的那样，这篇文章是关于文档的，我认为它是每个项目中最重要的部分之一，也是牺牲最多的部分。在本文中，我将说明如何在Power 双 Desktop和Excel中记录SSAS表格模型。我知道，您可以在Internet上找到一些产品，这些产品可以生成Word，PDF，HTML等各种格式的文档。但是，如果您正在寻找一种免费且更直观的方式来使用Power 双记录SSAS表格模型，那么本文适合您。通过本文，我们将使用Power 双创建一个文档工具。我称它为SSAS表格模型记录器。在这种方法中，我们根本不使用DMV。对于那些不熟悉DMV的人，我将简要解释一下DMV是什么，如果您已经熟悉DMV，则可以跳过此部分。

您可以在本文末尾下载Power 双模板格式（pbit）的SSAS表格模型文档器的副本。这是给你的圣诞礼物。

什么是DMV？

动态管理视图（简称DMV）是检索有关SQL Server Analysis Services实例的元数据信息的查询。 DMV可在SSAS多维和SSAS表格服务器模式下工作。 DMV可用于监视服务器操作和运行状况。 DMV查询结构与T-SQL非常相似，因此使用“ SELECT”语句，后接“ $ System”，这是XMLA架构行集。 DMV查询如下所示：

选择*从$ System。<schemaRowset>

因此，您可以打开SQL Server Management Studio，连接到SSAS实例（为方便起见，使用表格模型），然后运行以下查询以获取有关表格模型中表的大量信息：

从$ SYSTEM.TMSCHEMA_TABLES中选择*

在此处阅读有关DMV的更多信息。

注意：此方法仅适用于SSAS 表格2016及更高版本。

没有DMV的文档SSAS表格

在SSAS 表格2016及更高版本中，有一个微型元数据数据库，可以将其加载到Power 双 Desktop或Excel中以记录相应的SSAS Tabular模型。该数据库是一个SQLite数据库。在上一篇文章中，我解释了如何 在Power 双中可视化SQLite数据。此职位的要求与 上一篇文章，所以我鼓励您检查一下。因此，我仅说明如何查找元数据文件以及如何在Power 双 Desktop中构建模型。我还将为想要在结果中添加一些注释或注释的那些人解释如何在Excel中执行相同的操作。

在哪里可以找到SSAS表格元数据数据库？

好吧，这取决于您的SSAS表格即时配置。如果您坚持默认设置，则可以在“数据”文件夹下的数据库文件夹中找到它。您必须寻找“元数据.sqlitedb”文件。例如，我要记录“ AdventureWorks2016”表格模型，而SSAS表格实例配置是默认配置。所以我可以找到“元数据.sqlitedb”文件放在这里：

C：\ Program Files \ Microsoft SQL服务器 \MSAS14.SQL2017TABULAR\ OLAP \ Data \AdventureWorks2016.0.db

• 对于不同的即时名称，上述路径的突出显示部分将有所不同。我的实例名称是“ SQL2017Tabular”，它很可能与您的实例名称不同。
• 的“AdventureWorks2016.0.db”部分是您愿意查看的数据库名称

Power 双中的文档SSAS表格

如前所述，我之前曾解释过 如何在中加载SQLite数据 因此，我假设您已经知道如何从“元数据.sqlitedb档案至。

建议： 我建议您复制“元数据.sqlitedb”文件，然后再在Power 双中获取数据。

在Power 双中浏览数据库后，可以选择所有表，也可以只选择一些文档所需的表。我个人是第一次加载以下表格：

“Model”

“Measure”

“KPI”

“Level”

“Hierarchy”

“Column”

“Partition”

“Perspective”

“PerspectiveColumn”

“PerspectiveHierarchy”

“PerspectiveMeasure”

“PerspectiveTable”

“Table”

Power 双自动检测这些关系，但是， 他们都是不正确的。因此，您必须删除关系并手动重新创建它们，如下所示：

关系映射如下：

从表	从列	到表	到列
模型	ID	透视	型号
模型	ID	表	型号
表	ID	测量	表格ID
表	ID	层次结构	表格ID
表	ID	柱	表格ID
表	ID	划分	表格ID
层次结构	ID	水平	层次ID
透视	ID	透视表	透视ID
透视表	ID	透视层次	透视表ID
透视表	ID	透视栏	透视表ID
透视表	ID	透视测量	透视表ID
测量	ID	关键绩效指标	测量ID

现在，您可以创建一些度量，例如“表数”，“透视数”，“度量数”等，并创建图表和表以创建出色的报告。

查看“度量”表，您会发现它包含用于定义度量的DAX表达式。您还可以在“分区”表中看到分区查询，这真的很棒吗？

到目前为止，我们已从“元数据.sqlitedb”文件真的很棒。但是我们还没有完成。我们需要在查询设计器中进行一些数据转换。在进行下一步之前，我将所有名为“名称”的列重命名为更合适的名称，例如“表名称”或“透视名称”。

功率查询中的数据转换

如果查看“ 透视表”表，则会看到类似以下内容的内容：

如您所见，这里有一个“ 表格ID”列。因此，如果要查看透视图中使用的表，则需要具有表名。我们可以通过多种方式来处理。我个人更喜欢在Power Query中执行此类操作。特别是如果我需要添加新列，则最好在Power Query中处理它，而不是DAX。

我要做的是在“ 透视表”中添加“ 表 Name”列。 “表名”列来自“表”表。为此，我们必须以某种方式查找“表”表以获取“名称”。 功率查询中有一个非常方便的功能“ 表.NestedJoin” 在用户界面中称为“合并查询”。

打开查询编辑器，将“ 透视表”查询与“ 表”中“ ID”列的“ 表”和“ 透视表”中“ 表格ID”的合并。联接类型为“内部联接”。

• 在“查询”窗格中单击“ 透视表”
• 在右上角单击“合并查询”
• 在“合并”窗口中，从下拉列表中选择“表格”
• 从“透视表”中选择“表ID”列，从“表”中选择“ ID”列
• 将“加入类型”设置为“内部加入”，然后单击“确定”

现在，您有了新的“表格”列。

展开列，然后从列表中选择“表名”列，然后单击“确定”。

现在将“表名”列添加到表中。

您可以在“ 透视栏”，“ 透视测量”和“ 透视层次”表中执行相同的操作，以添加具有相关对象名称的相应列。

参数化数据源

查询参数已经存在很长时间了。我写了一系列有关 查询参数。为我们的模型参数化数据源是明智的，这样我们就可以轻松切换到其他数据源。

• 在查询编辑器中，单击“管理参数”
• 点击“新建”
• 输入“名称”和“描述”
• 勾选“必填”
• 对于我们的模型，我们可以按原样保留“类型”和“建议值”
• 在“当前值”中输入“ 元数据.sqlitedb”文件路径，然后单击“确定”

现在，您需要修改查询以使用参数

• 点击“高级编辑器”
• 如下修改“源”行

点击“完成”

• 点击“关闭”&应用”以将更改加载到模型

定义有用的措施

现在是时候定义一些简单而有用的措施了。显示隐藏表或隐藏列的数量，已定义的KPI的数量，在SSAS表格模型中定义的层次结构的数量等的度量。我将所有度量都放在度量表中。 单击此处以了解有关度量表的更多信息.

这是我定义的度量，您可能想要添加更多。  

Hidden 柱s = CALCULATE(COUNTROWS('Column'), 'Column'[IsHidden]=1)

Hidden 表s  = CALCULATE(COUNTROWS('Table'), 'Table'[IsHidden]=1)+0

Hierarchies  = COUNTROWS('Hierarchy')

关键绩效指标s  = COUNTROWS('KPI')

测量s  = CALCULATE(COUNTROWS('Measure'), 'Measure'[KPIID]=BLANK())

划分s  = COUNTROWS(Partition)

透视 关键绩效指标s  = CALCULATE(COUNTROWS(PerspectiveMeasure), 透视测量[KPIID]<>BLANK())

透视 测量s  = CALCULATE(COUNTROWS(PerspectiveMeasure), 透视测量[KPIID]=BLANK())

透视 表s  = COUNTROWS(PerspectiveTable)

透视s  = COUNTROWS(Perspective)

表s  = COUNTROWS('Table')

创建报告

最后一步是最有趣的一步。现在，我们可以看到SSAS表格模型中发生了什么。

我建立了一个如下所示的报告，其中包含两个页面。一个报告页面提供有关模型的常规信息，第二个报告页面显示有关度量和KPI的更多详细信息，包括表达式和格式。

缺点！

嗯下面列出的当前解决方案有一些缺点。你们可能有补救措施，如果可以，请在下面的评论部分与我们分享。

数值日期/时间

查看Power 双中加载的任何表格，您会发现至少有一个列应该显示相关的日期/时间。该列是“ ModifiedTime”，“ StructureModifiedTime”或“ RefreshedTime”。实际上，这些列包含重要的日期/时间相关信息。但这是将数据加载到Power 双中时看到的：

我知道，我知道...它们看起来像Unix Epoch时间戳，但不是！

我使用1970和1900作为起始日期将值转换为DateTime，这就是我得到的，这两个都是很错误的：

这是我使用的Power Query（M）表达式：

1900年Unix纪元日期= #datetime（1900,1,1,0,0,0）+＃duration（0,0,0，[ModifiedTime]）

1970年Unix纪元日期= #datetime（1970,1,1,0,0,0）+＃duration（0,0,0，[ModifiedTime]）

实际上，模型中加载的值是错误的整数值！让我解释。当您通过ODBC连接到SQLite数据库时，有两种选择可以将所有数值加载为 整数32 要么 整数64。如果您浏览SQLite元数据数据库，则会看到“ ModifiedTime”和所有其他日期是 大整数 不 整数.

这是“ 元数据.sqlitedb”文件中“ 表”表的屏幕截图，该文件在“SQLite的的数据库浏览器”。

您可能已经注意到这些值看起来不像正常的Unix Epoch，我们可以将它们转换为日期/时间。它们确实是Unix时代 纳秒，是的！纳秒，自1601年1月1日起！

为了在Power 双中获得正确的值，我们必须启用 大整数 ODBC连接字符串或DSN中的数字。

您可以通过添加轻松地做到这一点“BigInt = True”在连接字符串中，或​​者您也可以在ODBC中创建一个用户DSN，然后勾选相应的选项。

但是，启用BigInt后，所有数值（无论其原始数据类型如何）都将转换为 大整数。当您将数据加载到Power 双中时，情况变得更糟，因为Power 双会将所有BigInt值都视为Binary。这意味着您必须将所有二进制值都转换为 整数 要么 整数64 在查询编辑器中，这真是令人头疼。但是，它毕竟有效！

这是正确转换的Epoch时间戳到Date值的屏幕截图：

I’我不会进一步扩展它，因为这会使本书更长。

如果您有更好的主意，我真的很想知道，所以请在下面留下您的评论。

注意：如果运行DMV，则不会获得Unix Epoch值。您确实可以得到很好且整洁的日期/时间值。

加密数据源

元数据文件中有一个“ DataSource”表，我上面没有讨论过。 “ DataSource”表包含与SSAS表格模型中使用的源数据的连接字符串。连接字符串已加密（Base64），我无法解密该值。

如果运行DMV，则会在ConnectionString列中获得解密后的值。

再一次，如果您知道该怎么做，请在本文下方的评论部分中留下您的评论。

结论

我仍然使用DMV获得一些有用的信息，例如连接字符串和正常的日期/时间值。但是，该工具还有空间可以快速浏览“ 元数据.sqlitedb”文件，并获得有关SSAS表格模型的大量见解。

我提供了该工具供您免费下载。

这是我给你的圣诞礼物。

圣诞快乐，2018年见。

干杯!

在Power 双中下载SSAS表格记录器