如何找到 SSAS(表格模型)和 SQL 数据库之间的血统 [英] How to find lineage between SSAS (Tabular Model) and SQL database

问题描述

我们有 20 多个具有 300 多个维度的表格立方体，并且希望找到 - 如果有某种自动方法可以找到此维度的来源

We have 20+ tabular cubes with 300+ dimensions and was hoping to find - if there is some automated way to find what is the source for this dimension

是手动的，我可以进入表格模型>表格属性并可以找到用于填充维度的视图和表格.但是实际上不可能获得 300 多个维度.

Yes manually, I can get into Tabular model > Table Properties and can find what view and table are used to populate dimension. However not practically possible to get for 300+ dimensions.

是的，我知道一些 3rd 方提供商(例如 SQL Sentry)有一些工具可以找到该谱系.

And yes, I know some 3rd party provider such as SQL Sentry has some tools which can find that lineage.

但只是想知道，是否有任何 SSAS DMV 或任何其他脚本可以提供这些详细信息?

But just wondering, is there any SSAS DMVs or any other script which can give these details?

非常感谢您的帮助

注意:环境是本地 Microsoft SQL 2016 套件(SQL DB、SSAS 和 SSIS)

推荐答案

对于兼容级别 1200 或更高的 Tabublar 模型，$SYSTEM.TMSCHEMA_PARTITIONS DMV 的 QueryDefinition 列将显示每个分区的源 SQL 语句，如果未分区，则显示整个维度/事实表.如果使用完整的表或视图名称(而不是查询)，完整的 SELECT 语句将与对象名称一起显示.这可以通过连接到 SSAS 服务器并打开新的 MDX/DAX 查询窗口或其他工具(例如 Dax Studio)从任一 SSMS 进行查询.此 DMV 特定于您连接到的任何模型，而不是整个 SSAS 实例.在下面的示例中，Name 列是模型中维度的名称.由于 Name 是一个关键字，所以需要用括号括起来.

For compatibility level 1200 or above Tabublar models the QueryDefinition column of the $SYSTEM.TMSCHEMA_PARTITIONS DMV will show the source SQL statement for either each partition, or the entire dimension/fact table if it isn't partitioned. In the event that a full table or view name is used (instead of a query) the full SELECT statement will be displayed with the object name. This can be queried from either SSMS by connecting to the SSAS server and opening a new MDX/DAX query window or another tool such as Dax Studio. This DMV is specific to whatever model you're connected to, as opposed to the whole SSAS instance. In the example below the Name column is the name of the dimension from the model. Since Name is a keyword it will need to be enclosed in brackets.

SELECT QueryDefinition FROM $SYSTEM.TMSCHEMA_PARTITIONS WHERE [Name] = 'DimensionName'

这篇关于如何找到 SSAS(表格模型)和 SQL 数据库之间的血统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！