Automatic Refresh Failure

[AndreaBrunello1]

Hi,

The automatic refresh of a report keeps failing but I am not able to indentify the reason. If I refresh it manually from the desktop app, it normally works. 

Here is the failure alert: 

02 Sales Reporting has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

AnalysisServices: The connection either timed out or was lost.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Sales.

Can you help me with that?

many thanks

[amitchandak]

@AndreaBrunello1 , If it's using in premise gateway, then please make sure that is up and running.

If not, make sure the power bi service able to access it.  You can create a new data flow(if allowed) and check it.

In the case, connection details are fine log and new issue - https://community.powerbi.com/t5/Issues/idb-p/Issues

[AndreaBrunello1]

Hi @amitchandak 

As far as I understood ( I am not a big powerbi expert ... ) there are no gateways, as you can see from the screen.

What do you suggest then?

many thanks

[v-kkf-msft]

Hi @AndreaBrunello1 ,

You can go to these links to find if they can be helpful:

https://community.powerbi.com/t5/Service/Internal-Service-Error-Refresh-Failed/td-p/1387800

https://community.powerbi.com/t5/Service/The-exception-was-raised-by-the-IDataReader-interface-Pleas...

https://rohitvangala.wordpress.com/2017/02/02/error-the-exception-was-raised-by-idatareader-interfac...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaBrunello1]

Hi @v-kkf-msft ,

unfortunately the problem is still not solved. Which other error details could I provide you? 

many thanks

[v-kkf-msft]

Hi @AndreaBrunello1 ,

What is your data source? Could you please provide the M code of the Sales table in the Power Query Editor?

Best Regards,
Winniz

[AndreaBrunello1]

Hi Winniz,

I guess here it is! 

bests

let
Source = AnalysisServices.Database("asazure://westeurope.asazure.windows.net/fbsalesprod", "SALES", [TypedMeasureColumns=true, Implementation="2.0"]),
Sales1 = Source{[Id="Sales"]}[Data],
Sales2 = Sales1{[Id="Sales"]}[Data],
#"Added Items" = Cube.Transform(Sales2,
{
{Cube.AddAndExpandDimensionColumn, "[Booking Date]", {"[Booking Date].[Booking Date.Year Name].[Booking Date.Year Name]"}, {"Booking Date.Booking Date.Year Name"}}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Booking Date.Booking Date.Year Name]) = "[Booking Date].[Booking Date.Year Name].&[2019]" meta [DisplayName = "2019"] or Cube.AttributeMemberId([Booking Date.Booking Date.Year Name]) = "[Booking Date].[Booking Date.Year Name].&[2020]" meta [DisplayName = "2020"] or Cube.AttributeMemberId([Booking Date.Booking Date.Year Name]) = "[Booking Date].[Booking Date.Year Name].&[2021]" meta [DisplayName = "2021"])),
#"Added Items1" = Cube.Transform(#"Filtered Rows",
{
{Cube.AddAndExpandDimensionColumn, "[Booking Date]", {"[Booking Date].[Booking Date.Month Name].[Booking Date.Month Name]"}, {"Booking Date.Booking Date.Month Name"}}
}),
#"Added Custom" = Table.AddColumn(#"Added Items1", "Test", each SalesForm([Booking Date.Booking Date.Month Name],[Booking Date.Booking Date.Year Name])),
#"Expanded Test" = Table.ExpandTableColumn(#"Added Custom", "Test", {"Booking Date.Month Name", "Shop Name", "Order Type Status.Order Item Status", "Order Type Status.Order Item Type", "Order Type Status.Order Item Type Group", "Order Type Status.Order Status", "Booking Date.Year Name", "Booking Date.Date Name", "Orders", "Tickets", "NOI", "Line Variation.Is Cooperation", "Payment Type.Method"}, {"Test.Booking Date.Month Name", "Test.Shop Name", "Test.Order Type Status.Order Item Status", "Test.Order Type Status.Order Item Type", "Test.Order Type Status.Order Item Type Group", "Test.Order Type Status.Order Status", "Test.Booking Date.Year Name", "Test.Booking Date.Date Name", "Test.Orders", "Test.Tickets", "Test.NOI", "Test.Line Variation.Is Cooperation", "Test.Payment Type.Method"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded Test", {{"Test.Booking Date.Date Name", type date}}, "de-DE"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Test.Booking Date.Month Name", type text}, {"Test.Shop Name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Test.Booking Date.Month Name", "Booking Date.Month Name"}, {"Test.Shop Name", "Shop Name"}, {"Test.Order Type Status.Order Item Status", "Order Type Status.Order Item Status"}, {"Test.Order Type Status.Order Item Type", "Order Type Status.Order Item Type"}, {"Test.Order Type Status.Order Item Type Group", "Order Type Status.Order Item Type Group"}, {"Test.Order Type Status.Order Status", "Order Type Status.Order Status"}, {"Test.Booking Date.Year Name", "Booking Date.Year Name"}, {"Test.Booking Date.Date Name", "Booking Date.Date Name"}, {"Test.Orders", "Orders"}, {"Test.Tickets", "Tickets"}, {"Test.NOI", "NOI"}, {"Test.Line Variation.Is Cooperation", "Line Variation.Is Cooperation"}, {"Test.Payment Type.Method", "Payment Type.Method"}}),
#"Inserted Start of Month" = Table.AddColumn(#"Renamed Columns", "Start of Month", each Date.StartOfMonth([Booking Date.Date Name]), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted Start of Month", "TargetMeasureValue", each Text.Combine({Text.From([Start of Month], "de-DE"), [Shop Name]}, ","), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Orders", Int64.Type}, {"Tickets", Int64.Type}, {"NOI", type number}, {"Payment Type.Method", type text}, {"Order Type Status.Order Item Type Group", type text}, {"Order Type Status.Order Item Type", type text}, {"Order Type Status.Order Status", type text}, {"Order Type Status.Order Item Status", type text}, {"Line Variation.Is Cooperation", type text}, {"Booking Date.Year Name", type text}})
in
#"Changed Type1"