Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I would like some clarity on that points:
- PowerBI Online does not allow queries from the Google Analytics API (only PowerBI Desktop)?
- What is the maximum number of rows that displays a table of the dashboard?
- Is possible to use sort and filters for the Analytics API queries?.
Hi agarcia,
I understand your questions as I have tried to figure out the same thing some time ago.
- PowerBI Online does not allow queries from the Google Analytics API (only PowerBI Desktop)?
You can only use the pre-built data source to connect to your data. Querying the GA-API with custom queries is not (yet?) supported in PowerBI Service. You can do some workarounds using Post Requests and functions in PBI desktop, but I found it not feasible.
- What is the maximum number of rows that displays a table of the dashboard?
PowerBI does not seem to have a limit that would be applicable to one query of GA data. But you have to distinguish between the data table that lies behind your dashboards and a table you would actually want to display. Displaying >10k rows to the dashboard consumer make no sense at all.
The GA API does have a limit of 10,000 result rows per API query. If your GA query exceeds the 10k row, it seems that PowerBI does make the extra queries to retrieve all rows. I have just made a test and my countrows(datatable) measure returns 165k rows of data. So your data should be "complete", if I understand your worries correctly.
- Is possible to use sort and filters for the Analytics API queries?.
Unofrtunately, no.
To everyone else: The GA API allows you to specify filters, segments and sort orders that will alter the result of your GA Query even before it arrives in PowerBI. This is not supported by the PowerBI service.
My suggestion to you (easy setup):
- Use Google App Script to insert your query data into a Google Drive spreadsheet.
- Make the script to get only new rows of data and add it to the sheet.
- Then use PowerBI to connect to that spreadsheet as a data source.
My second suggestion to you (more advanced setup):
- Build an application that uses a serverside language to query your data
- Store the results in a database
- Then use PowerBI to connect to that database.
Unfortunately I do not see a better way at the moment.
Thank you all for the answers, you have really helped me.
There is a Content Pack in the Service for Google Analytics
I am not sure I understand the second question, I have a table visualization that displays every day as a row in the table for the last 3 years
Yes, in Desktop you get to choose exactly what measures and dimensions you want and have the full power of "M" to filter and sort as desired.
Thanks for your reply smoupre.
I mean if I use, for example, three years (Data range) 50,000 rows of data appear to me. If I create a data table with powerbi, ¿will it be able to reflect me these 50,000 rows? It has a maximum data to display the table that I created? If the maximum are 10,000 rows (for example) in the table you could not see the 40,000 rows missing. To use the query of Google Analytics I go to: https://ga-dev-tools.appspot.com/query-explorer/ .
And with respect to the first question, I like to use the data that interests me, no predefined dashboard.
The number of rows the underlying PowerPivot engine can handle is quite high. I have seen files with 100mio+ rows. It gets quite slow depending on the calculations of your measures but its possible.
If you query a lot from Google Analytics it might take some time to load the data and you should watch your query limits but with updates once a day you are fine.
Not sure if I understand your questions correctly.
Why would you display a table with more than 10k rows? Is there a real necessity for this / would an end user want to scroll down thousand of rows?
Most people would be better of with aggregated tables, lets say "sessions by source site" which can then be filtered using slicers.
Or do you just want to handle the 50k rows in an aggregated visual? Depending on the visual there are some upper limits. Funnel displays for example can display dozens of values but become unreadable due to the shrinking bar sizes.. some regular tables without aggregation features can show a few hundred values but seem to cut of rows after that. The calculations are not affected by that - just the visual itself.
I found that by connecting directly to Google Analytics from the Power BI service works well for small datasets. It provides a nice set of reports and dashboards that are mostly helpful. However on larger datasets, the refresh times out and there is no way to change the timeout settings.
By creating a Power BI Desktop report and then connecting you get more control over what measures and dimensions you actually want to use. You can also combine these at this point with other data sources. eg. We only bring the Date value from GA and then combine with a local Date Dimension table to get reporting periods that suit our business and not google.
I've yet to have a timeout issue when using Power BI Desktops connector to GA and this is my preferred method.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
37 | |
27 | |
17 | |
16 | |
8 |
User | Count |
---|---|
45 | |
38 | |
34 | |
18 | |
16 |