powerbi - How can I retrieve the list of measures contained in a SSAS connected Power BI report?

Question

I am working on a PBI report created by someone else in the organisation and I need to do some auditing of all the measures (50 or more) contained in the report itself.

The report connects to an on-premises instance of SQL Server Analysis Services.

I am trying to get the list of all measures contained in the report. To achieve that, in previous occasions, I used DAX Studio to connect to the running instance of the PBI Desktop as described in https://exceleratorbi.com.au/getting-started-dax-studio/ .

However, as this report connects to SSAS, when I try to connect DAX Studio to it, I get an error: "No Databases were found when connecting to PBI Desktop. If your PBI file is using a Live Connection please connect directly to the source model instead."

Is there another known method I can use to extract all measures from the PBIX itself?

标签： powerbidax

Answer 1

如果您将 .pbix 文件重命名为 .zip 并将其作为 zip 文件打开，您将看到一个 Report 文件夹，然后是一个名为Layout. 如果从 .zip 文件中复制该文件并在文本编辑器（最好是可以格式化 JSON 的应用程序）中打开它，您将看到以下内容：

{
  "id": 0,
  "resourcePackages": [
    //some packages here
  ],
  "sections": [
    //some sections here...
  ],
  "config": "{\"version\":\"5.3\",\"themeCollection\":{\"baseTheme\":{\"name\":\"CY19SU06\",\"version\":\"5.5\",\"type\":2}},\"activeSectionIndex\":0,\"modelExtensions\":[{\"name\":\"extension\",\"entities\":[{\"name\":\"DimDate\",\"extends\":\"DimDate\",\"measures\":[{\"name\":\"My Report Measure\",\"dataType\":3,\"expression\":\"DIVIDE(99,100)\",\"errorMessage\":null,\"hidden\":false,\"formulaOverride\":null,\"formatInformation\":{\"formatString\":\"G\",\"format\":\"General\",\"thousandSeparator\":false,\"currencyFormat\":null,\"dateTimeCustomFormat\":null}}]},{\"name\":\"DimCustomer\",\"extends\":\"DimCustomer\",\"measures\":[{\"name\":\"My Report Measure 2\",\"dataType\":3,\"expression\":\"99 + 100\",\"errorMessage\":null,\"hidden\":false,\"formulaOverride\":null,\"formatInformation\":{\"formatString\":\"G\",\"format\":\"General\",\"thousandSeparator\":false,\"currencyFormat\":null,\"dateTimeCustomFormat\":null}}]}]}],\"defaultDrillFilterOtherVisuals\":true,\"settings\":{\"useStylableVisualContainerHeader\":true,\"exportDataMode\":1,\"useNewFilterPaneExperience\":true,\"allowChangeFilterTypes\":true},\"objects\":{\"section\":[{\"properties\":{\"verticalAlignment\":{\"expr\":{\"Literal\":{\"Value\":\"'Top'\"}}}}}]}}",
  "layoutOptimization": 0
}

如果您查看该config属性，则其中有一个包含 JSON 的字符串。如果您提取 JSON 并对其进行格式化，您将获得：

{
  "version": "5.3",
  "themeCollection": {
    "baseTheme": {
      "name": "CY19SU06",
      "version": "5.5",
      "type": 2
    }
  },
  "activeSectionIndex": 0,
  "modelExtensions": [
    {
      "name": "extension",
      "entities": [
        {
          "name": "DimDate",
          "extends": "DimDate",
          "measures": [
            {
              "name": "My Report Measure",
              "dataType": 3,
              "expression": "DIVIDE(99,100)",
              "errorMessage": null,
              "hidden": false,
              "formulaOverride": null,
              "formatInformation": {
                "formatString": "G",
                "format": "General",
                "thousandSeparator": false,
                "currencyFormat": null,
                "dateTimeCustomFormat": null
              }
            }
          ]
        },
        {
          "name": "DimCustomer",
          "extends": "DimCustomer",
          "measures": [
            {
              "name": "My Report Measure 2",
              "dataType": 3,
              "expression": "99 + 100",
              "errorMessage": null,
              "hidden": false,
              "formulaOverride": null,
              "formatInformation": {
                "formatString": "G",
                "format": "General",
                "thousandSeparator": false,
                "currencyFormat": null,
                "dateTimeCustomFormat": null
              }
            }
          ]
        }
      ]
    }
  ],
  "defaultDrillFilterOtherVisuals": true,
  "settings": {
    "useStylableVisualContainerHeader": true,
    "exportDataMode": 1,
    "useNewFilterPaneExperience": true,
    "allowChangeFilterTypes": true
  },
  "objects": { "section": [ { "properties": { "verticalAlignment": { "expr": { "Literal": { "Value": "'Top'" } } } } } ] }
}

您将My Report Measure在 DimDate 表中看到表达式DIVIDE(99,100)，My Report Measure 2在 DimCustomer 表中看到表达式99 + 100。这些都是简单的例子，但这给了你想法。

显然，这都是无证的，可能会发生变化。但这是我知道的将这些度量添加到 PBIX 的唯一方法（而不是在 SSAS 模型本身中度量）。