Drastic problem querying data

User Support Data Management
,
1

We are querying the data of our projects daily into PowerBI. There have been failures once in a while but during the last few weeks, failure is more frequent than success.

We are not sure whether the following change on our side is increasing the problem: We are querying the data now with a time filter:
RP_BETWEEN = “/submissions/?format=json&query={”“$and”“: [{”“basics/date”“:{”“$gt”“:”“” & FromDate &“”“}}, {”“basics/date”“:{”“$lt”“:”“” & ToDate &“”“}}]}”

In any case, the issue seems to be an unstable kobo server (“Unable to read data from the transport connection: The connection was closed.”). In order to test, I just opened the Data view in one of our projects and selected "show 500* instead of 30. Result: crash…

Has anything been changed on the server side or are date filters known as unstable (or is the filter, as we use it, wrong)?

2

@york_rff, I am pinging @osmanburcu to see if he is available for your support!

3

Thank you!

1 Like
4

@york_rff Hi,

Can you please explain more details? How you are importing the data? Are you using parameters to change the link? If you elaborate more i can try to help

1 Like
5

Hi Osman

Happy to provide more details (because the last ten refresh attempts have failed, i.e. it is getting worse).

As mentioned, we are using PowerBI to query the data with the provided code.

What other details can I share?

KInd regards
York

6

Parameter Kobo-URL:
“KoboToolbox Form Building API” meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]

Parameter Kobo-Token:
“xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx” meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]

Parameter FromDate:
“2024-01-01” meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]

Parameter ToDate
“2024-12-31” meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]

Function GetKoboData:
(FormID as text) =>
let
RP_ALL = “/submissions/?format=json”,
RP_FROM = “/submissions/?format=json&query={”“basics/date”“:{”“$gt”“:”“” & FromDate &“”“}}”,
RP_TO = “/submissions/?format=json&query={”“basics/date”“:{”“$lt”“:”“” & ToDate &“”“}}”,
RP_BETWEEN = “/submissions/?format=json&query={”“$and”“: [{”“basics/date”“:{”“$gt”“:”“” & FromDate &“”“}}, {”“basics/date”“:{”“$lt”“:”“” & ToDate &“”“}}]}”,
Source = Json.Document(Web.Contents(Kobo_URL,
[
RelativePath=“/” & FormID & RP_BETWEEN, Headers=[#“Authorization”="Token " & Kobo_Token]
])),
#“Convert2Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in Convert2Table

Querying various country forms, like:
GetKoboData(“aQh6Ewp9sXVaeqT9A4tem9”)

Combining them and doing various transformations afterwards:
Table.Combine({KoboJSON_XX, KoboJSON_LS, KoboJSON_MW, KoboJSON_NA, KoboJSON_ZA, KoboJSON_ZM, KoboJSON_ZW})

7

Hi

Any news on this? The current quota is that we have to trigger a refresh 8 times to succeed once…!?

Kind regards
York

8

Hi @york_rff, it looks like there is no problem with your parameters. Something else must be causing it. How big your data? more than 30.000 rows?

1 Like
9

…no, definitely not as many (we just started the projects in 2024, so that the many data of the existing projects don’t need to be queried every day again).

Is it possible to monitor a refresh (due to some prior queries, every day about 6 minutes after 5:00 am and 11:00 CET) on your side?

I could also query a refresh manually at a time defined by you.

10

Any news or additional questions, @osmanburcu?

11

@osmanburcu, let me just add an observation: Is it possible that the failure in refreshing is almost 100% in the early morning (CET) and less likely in the afternoons?

12

@osmanburcu and @Kal_Lam, we are just spending too much time in clicking the refresh button (instead of relying on a timed refresh automatism), each time hoping that the PowerBI query of the kobo data succeeds – but seemingly it needs a lot of luck.

What possibilities do you have on you side to debug the process? And how can we help (e.g. by triggering the refresh when you are demanding for it)?

13

Hi @york_rff , i am sorry for my late response but its been really busy lately for me due to organization change. I tried to find sollution but nothing comes out because i can’t have the first hand experince with the problem. If it is fine for you, can you share the pbix file and username and password with me so i can check in first hand?

14

Thanks, @osmanburcu, for getting back.

Unfortunately, it is not a (sharable) pbix file, but the queries are done as Dataflow (where only developers have access).

Can we send you the transformations as email?

Kind regards
York

15

… I have just been told that you can import the (exported) DataFlow (see https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwjKzs-xtrKFAxWAiv0HHQjVAs4QFnoECBMQAw&url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DmteeKPNGIdM&usg=AOvVaw2uAu-9RZ2OP4UBQ7kbSPWe&opi=89978449).

Unfortunately, I am not allowed to upload a pqt file, so please find it here:

16

…yesterday, again the 11:00 automatic refresh went through, today, again the 4:00 refresh failed. Let me emphasize again:

  • the same DataFlow/Transformations go through sometimes and fail sometimes, so it is unlikely that the problem is there.
  • refresh in the earlier day (CET) fail much more often than later, so maybe the issue is about server parameters trying to handle too much traffic?
17

I believe i won’t be able solve the problem, i would suggest you to reach the Power BI community. They might have more information about the problem

18

Again, I can’t see why the issue should be on the PowerBI side – both because the same queries sometimes are not interrupted as well as because we run queries from several servers and only the kobo server is giving us these problems.

Can you provide us some technical data from the kobo server side on the successful and the failed access attempts?

19

@osmanburcu, @Kal_Lam, I noticed that the failure happens most of the time after 44 seconds. Is there anything on the kobo server side (re access management) which this time span points at?