Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Refresh Timeout

Good morning everyone,

 

I have a report that frequently times out while refreshing automatically in the online workspace. 
I think what slows down things is a Google Analytics connection and several merges between pretty large tables.
I've seen a few times by other users a line of code to be included in the advanced editor that would extend the time before failing to one hour.
Can someone please suggest how to include that for all tables in my dataset?
For example, the GA connection starts with:

let
Source = GoogleAnalytics.Accounts(),
#"xxx" = Source{[Id="xxx"]}[Data],
#"xxx" = #"xxx"{[Id="xxx"]}[Data],
#"xxx" = #"xxx"{[Id="xxx"]}[Data],


Thanks!

Alessio B.

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    When the refreshed data source is too large, it may cause refresh timeout.

     

    First, you need to download the latest version of powerbi desktop. When the date range is large, only a subset of the data will be returned. If you want to get more or even all the data, you need to append multiple queries with different date ranges.

    There are two options to get more data.

    let
    Source = GoogleAnalytics.Accounts(),
    #"<ID>" = Source{[Id="<ID>"]}[Data],
    #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
    #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
    #"Added Items" = Cube.Transform(#"<View ID>",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
            {Cube.AddMeasureColumn, "Users", "ga:users"}
        })
in
    #"Added Items"

    without data column

    let
      Source = GoogleAnalytics.Accounts(),
      #"<ID>" = Source{[Id="<ID>"]}[Data],
      #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
      #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
      #"Added Items" = Cube.Transform(#"<View ID>",
          {
              {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
              {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
              {Cube.AddMeasureColumn, "Organic Searches", "ga:organicSearches"}            
         }),
      #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2019, 9, 1) and [Date] <= #date(2019, 9, 30))
   in
      #"Filtered Rows"

     

    related document link:Power Query Google Analytics connector | Microsoft Docs

    Best Regards,
    Henry

     

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

3 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Anonymous ,

     

    When the refreshed data source is too large, it may cause refresh timeout.

     

    First, you need to download the latest version of powerbi desktop. When the date range is large, only a subset of the data will be returned. If you want to get more or even all the data, you need to append multiple queries with different date ranges.

    There are two options to get more data.

    let
    Source = GoogleAnalytics.Accounts(),
    #"<ID>" = Source{[Id="<ID>"]}[Data],
    #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
    #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
    #"Added Items" = Cube.Transform(#"<View ID>",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
            {Cube.AddMeasureColumn, "Users", "ga:users"}
        })
in
    #"Added Items"

    without data column

    let
      Source = GoogleAnalytics.Accounts(),
      #"<ID>" = Source{[Id="<ID>"]}[Data],
      #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
      #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
      #"Added Items" = Cube.Transform(#"<View ID>",
          {
              {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
              {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
              {Cube.AddMeasureColumn, "Organic Searches", "ga:organicSearches"}            
         }),
      #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2019, 9, 1) and [Date] <= #date(2019, 9, 30))
   in
      #"Filtered Rows"

     

    related document link:Power Query Google Analytics connector | Microsoft Docs

    Best Regards,
    Henry

     

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

  • ReportBuilder07's avatar
    ReportBuilder07
    Helper III
    2 years ago

    Hi Anonymous were you able to find a solution? I think in this case you can explore another way to pull your data. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA connector in the data sources list:

     

     

    After that, just grant access to your GA account using your credentials, then on preview and destination page you will see a preview of your GA fields:

     

     

    There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello! Yes it's no longer a problem as we built a Data Lake in Azure and utilize big query now to aggregate data.