Last active
September 22, 2023 02:42
-
-
Save theGOTOguy/dfcea71e2528b6ea24f9db710b59b244 to your computer and use it in GitHub Desktop.
Connecting PowerBI to Planetscale Read-Only With Over 100,000 Rows
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Today @MooreCar and I spent an hour figuring this out. | |
This only works on the PowerBI Desktop Application (not web). | |
1) In the Planetscale Web UI, create a new read-only connection string (https://planetscale.com/docs/concepts/connection-strings) | |
- Selecting "Mysql CLI", note the host (-h), username (-u) and password (-p). | |
2) In the PowerBI Desktop App, install the Oracle MySQL connector (https://learn.microsoft.com/en-us/power-query/connectors/mysql-database). Note that at the time of this writing, only 8.0.26 is supported. | |
3) Create a new MySQL Database data source in the PowerBI Desktop app. | |
- Host: The host from (a) | |
- Database: You can put the name of your database here. In our case, "simpleaq". I don't believe it matters. | |
- You cannot simmply select a table and proceed. You will get an error like "target: typebot.-.primary: vttablet: rpc error: code = Aborted desc = caller id: oqcm30z7knpwnk7x7mwq: row count exceeded 100000 (CallerID: oqcm30z7knpwnk7x7mwq)" if you have more than 100,000 rows in your Planetscale table. | |
- Under "Advanced", you will need to edit the SQL query. The query will look like: | |
`set workload=olap; SELECT * FROM YourTableHere;` | |
Or whatever query you wish to use to populate your dataset. | |
- In the next pane, under "Database," enter your username and password from step 1 above and connect. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment