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)"
}
]
}
}
}
}
... View more
v-xiaoyan-msft
on:
Possible Bug with Rounding
Idrissshatila
on:
Power Query Filter Rows Basic UI bug