cancel
Showing results for 
Search instead for 
Did you mean: 

XMLA endpoint deployment resets gateway


I have a Power BI dataset deployed to a premium workspace through publishing a PBIX file. I have to configure an on-premises SQL Server gateway for the dataset.

 

If I make a change to the file and republish, the gateway "sticks" but if I make the same change via XMLA endpoint, the gateway disconnects and I have to set it up again. This defeats the purpose of programmatic deployment of XMLA endpoint.

 

Specifically, I have a PBIX file with a table running a piece of SQL query against our on-premises server. If I change the SQL and republish, it is all good - I can refresh the dataset. But if I right click "Script create or replace" in SSMS, make the same change to the SQL text, refresh fails with a no gateway error. If I go to the PBI workspace, I see that the gateway has indeed "disconnected" and needs re-configuring.

 

XMLA attached below (with identifies redacted). If I change the SQL from ">5" to ">6" or whatever and redeploy, it resets the gateway. The same change does not when published via PBI desktop.

Is there an issue or am I doing something wrong?

 

 

{
  "createOrReplace": {
    "object": {
      "database": "gateway"
    },
    "database": {
      "name": "gateway",
      "id": ".........................",
      "compatibilityLevel": 1520,
      "model": {
        "culture": "en-AU",
        "dataAccessOptions": {
          "legacyRedirects": true,
          "returnErrorValuesAsNull": true
        },
        "defaultPowerBIDataSourceVersion": "powerBI_V3",
        "sourceQueryCulture": "en-AU",
        "tables": [
          {
            "name": "Table",
            "columns": [
              {
                "name": "ABC",
                "dataType": "int64",
                "sourceColumn": "ABC",
                "formatString": "0",
                "summarizeBy": "sum",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "Automatic"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"NumberWhole\" Accuracy=\"0\" />"
                  },
                  {
                    "name": "DataTypeAtRefresh",
                    "value": "Int64#####not a type"
                  },
                  {
                    "name": "PBI_NameAtRefresh",
                    "value": "ABC"
                  },
                  {
                    "name": "PBI_WasHiddenAtRefresh",
                    "value": "False"
                  }
                ]
              }
            ],
            "partitions": [
              {
                "name": "Table-c04db6d2-31be-4096-bfdd-c9d97f37e9f3",
                "mode": "import",
                "source": {
                  "type": "m",
                  "expression": [
                    "let",
                    "    Source = Sql.Database(\".....\",\".....\", [Query=Sql])",
                    "in",
                    "    Source"
                  ]
                }
              }
            ],
            "annotations": [
              {
                "name": "PBI_NavigationStepName",
                "value": "Navigation"
              },
              {
                "name": "PBI_ResultType",
                "value": "Table"
              }
            ]
          },
          {
            "name": "DateTableTemplate_6f542e29-c3fe-423f-8f33-2ded242a5f97",
            "isHidden": true,
            "isPrivate": true,
            "columns": [
              {
                "type": "calculatedTableColumn",
                "name": "Date",
                "dataType": "dateTime",
                "isNameInferred": true,
                "isDataTypeInferred": true,
                "isHidden": true,
                "sourceColumn": "[Date]",
                "formatString": "General Date",
                "dataCategory": "PaddedDateTableDates",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"DateTimeGeneralPattern\"><DateTimes><DateTime LCID=\"3081\" Group=\"GeneralDateTimeLong\" FormatString=\"G\" /></DateTimes></Format>"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "Year",
                "dataType": "int64",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "YEAR([Date])",
                "formatString": "0",
                "dataCategory": "Years",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "Year"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"NumberWhole\" Accuracy=\"0\" />"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "MonthNo",
                "dataType": "int64",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "MONTH([Date])",
                "formatString": "0",
                "dataCategory": "MonthOfYear",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "MonthNumber"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"NumberWhole\" Accuracy=\"0\" />"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "Month",
                "dataType": "string",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "FORMAT([Date], \"MMMM\")",
                "sortByColumn": "MonthNo",
                "dataCategory": "Months",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "Month"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"Text\" />"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "QuarterNo",
                "dataType": "int64",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "INT(([MonthNo] + 2) / 3)",
                "formatString": "0",
                "dataCategory": "QuarterOfYear",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "QuarterNumber"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"NumberWhole\" Accuracy=\"0\" />"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "Quarter",
                "dataType": "string",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "\"Qtr \" & [QuarterNo]",
                "sortByColumn": "QuarterNo",
                "dataCategory": "Quarters",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "Quarter"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"Text\" />"
                  }
                ]
              },
              {
                "type": "calculated",
                "name": "Day",
                "dataType": "int64",
                "isDataTypeInferred": true,
                "isHidden": true,
                "expression": "DAY([Date])",
                "formatString": "0",
                "dataCategory": "DayOfMonth",
                "summarizeBy": "none",
                "annotations": [
                  {
                    "name": "SummarizationSetBy",
                    "value": "User"
                  },
                  {
                    "name": "TemplateId",
                    "value": "Day"
                  },
                  {
                    "name": "Format",
                    "value": "<Format Format=\"NumberWhole\" Accuracy=\"0\" />"
                  }
                ]
              }
            ],
            "partitions": [
              {
                "name": "DateTableTemplate_6f542e29-c3fe-423f-8f33-2ded242a5f97-4ff5232f-c97e-4582-8614-c8b5db110151",
                "mode": "import",
                "source": {
                  "type": "calculated",
                  "expression": "Calendar(Date(2015,1,1), Date(2015,1,1))"
                }
              }
            ],
            "hierarchies": [
              {
                "name": "Date Hierarchy",
                "levels": [
                  {
                    "name": "Year",
                    "ordinal": 0,
                    "column": "Year"
                  },
                  {
                    "name": "Quarter",
                    "ordinal": 1,
                    "column": "Quarter"
                  },
                  {
                    "name": "Month",
                    "ordinal": 2,
                    "column": "Month"
                  },
                  {
                    "name": "Day",
                    "ordinal": 3,
                    "column": "Day"
                  }
                ],
                "annotations": [
                  {
                    "name": "TemplateId",
                    "value": "DateHierarchy"
                  }
                ]
              }
            ],
            "annotations": [
              {
                "name": "__PBI_TemplateDateTable",
                "value": "true"
              },
              {
                "name": "DefaultItem",
                "value": "DateHierarchy"
              }
            ]
          }
        ],
        "cultures": [
          {
            "name": "en-AU",
            "linguisticMetadata": {
              "content": {
                "Version": "1.0.0",
                "Language": "en-US",
                "DynamicImprovement": "HighConfidence"
              },
              "contentType": "json"
            }
          }
        ],
        "expressions": [
          {
            "name": "Sql",
            "kind": "m",
            "expression": [
              "let",
              "    Source = \"select 123 [ABC] from sys.objects where object_id > 5\"",
              "in",
              "    Source"
            ],
            "annotations": [
              {
                "name": "PBI_NavigationStepName",
                "value": "Navigation"
              },
              {
                "name": "PBI_ResultType",
                "value": "Text"
              }
            ]
          }
        ],
        "annotations": [
          {
            "name": "PBI_QueryOrder",
            "value": "[\"Sql\",\"Table\"]"
          },
          {
            "name": "__PBI_TimeIntelligenceEnabled",
            "value": "1"
          },
          {
            "name": "PBIDesktopVersion",
            "value": "2.85.985.0 (20.09)"
          }
        ]
      }
    }
  }
}

 

Status: New
Comments
Community Support

@matthias-bi 

 

It seems that you may use Alter command instead.

Regular Visitor

@v-chuncz-msft  thanks very much. I am not deeply familiar with the ALTER command. But I didnt think I can use it to change the "expressions" component of the model?

Community Support

@matthias-bi 

 

You may also check Partitions object (TMSL).