Errors when referencing SQL source in subsequent queries

[KierenPorter]

Hi everyone!

Until recently I've only used Excel and .csv datasources for my .pbix work - and have typically followed the convention:

Query1 = get & do some general cleaning of the source data (set to unload to report)
Query* = reference Query1 to build fact and dim tables (set to load to report)

...so that the "get" steps are removed from the fact & dim queries so I can do single swap (or edit) at the highest-level, without repetition. I'm not sure even if this is "the proper way to do things", but to date it has served me well.

I am now slowly getting my head around accessing a MySQL database - and thought to repurpose this thinking - for example, first I get the source data:

GetDataExample

let
Source = MySQL.Database("ServerName", "DatabaseName", [ReturnSingleDatabase=true])
in
Source

then reference that "get" query in subsequent queries, for example:

GetTableByReference

let
Source = GetDataExample,
DatabaseTable = Source{[Schema="DatabaseName",Item="DatabaseTableName"]}[Data]
in
DatabaseTable

and whilst I don't always get them, I am seeing (on refresh in the report) the following errors:

OLE DB or ODBC error: [DataSourceError] MySQL: Too many connections.
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

seemingly far more often than when I leave the source lookup hardcoded in each query:

GetTableDirectly

let
Source = MySQL.Database("ServerName", "DatabaseName", [ReturnSingleDatabase=true]),
DatabaseTable = Source{[Schema="DatabaseName",Item="DatabaseTableName"]}[Data]
in
DatabaseTable

It's not a deal-breaker, but the "shorthand" of define-source-once-then-reference (that I use w/ .csv and .xlsx) certainly seemed to make sense to (repurpose and) reduce repetition.

I know I'm probably going about this all the wrong way from a strict SQL standpoint (!?) but until I've time to get my head around writing calls to get what I want from the SQL directly, I'm really looking to "just" get some useable columns & rows to work with for now... and am a bit confused why the source-direct queries don't raise errors as often, as it would seem (to me) they are making more connections than the referenced ones?

Or perhaps the errors aren't related to my use of this referencing method at all? They did only start to flare up the second I implemented it though... 

Any and all advice, steering toward reading, or insight would be greatly appreciated!

[KierenPorter]

3 impatient bumps would get you told off by mods on any other forum - so not sure why community support are getting away with it here?

I am also curious how someone else can accept a solution on the behalf of an OP?

I am grateful for Kees' contribution, but still waiting in hope for more insight by others - and do not recognise the solution accepted.

PS: what kind of calendar format is that?!?

 

[v-prasare]

@KierenPorter, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@KierenPorter  As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@KierenPorter  As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

@PwerQueryKees , Thanks for your promt response.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[PwerQueryKees · Accepted solution]

Can't help you with the MySQL connector and how it manages connections to MySQL.

Your way of working to avoid duplication by breaking up your queries is exactly what I am doing with CSV files of with the DataVerse connector without issues.

You could try a middle ground by creating parameters for the servername and database name. The most likely to change values in your query and the do the mysql.database() call for each individual table.

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[KierenPorter]

no worries about the SQL, your input is welcomed! I am already using a "blank" query to hold single text values for those "hotswap" values, and had had in mind moving to using a parameter (for on-open prompts for when I make the .pbix a .pbit for use by others) - so its good to know I'm not alone in my thinking (t)here!