Gateway - Data Source best practice

[PhilWalker]

I have a report that takes data from a number of Excel files. These files are all stored within the same shared folder, and the report is published to the service with a scheduled refresh setup.

When creating data sources in the Gateway, are there any advantages / disadvantages / limitations of using a single folder data source, or should multiple file data sources be used?

Likewise, if I have multiple reports in that same folder structure, some of which take data from Excel files, would there be any disadvantages (performance??) in creating a single folder data source, based on the root of that folder structure, that could be used for all of them?

[lbendlin]

My vote is strongly in the folder connector corner.  In fact, in our company we do not permit any file based connectors.

[PhilWalker]

Thanks for the reply.

So, could I ask, if you had a folder structure something like this...

- Reports

     - Report 1

          - Data Files

     - Report 2

          - Data Files

     - Report 3

          - Data Files

     - Report......

Would you have a data source for each "Reportn" folder, or a single data source at the top "Reports" level? I'm not sure if there are any performance or other implications of using a single data source, especially if more than one report is being refreshed at the same time.

[lbendlin · Accepted solution]

I would use a single source for the top most folder. Performance will be the same but maintenance will be simpler.

[MarkPalmberg]

Thanks for this reply, @lbendlin . In the instance of SQL server data sources, we're thinking of cleaning up our existing sources to have only a single source per server which specifies the Master database. Then, any specific SQL we write against that datasource will include the database name in the object reference. E.g., FROM DATABASE.SCHEMA.TABLE instead of FROM SCHEMA.TABLE. That saves us (I believe?) from having a separate data source for each SERVER/DATABASE combination in use.

Are you aware of any MSFT or other documentation on the best practice here? Thanks for your time!

[lbendlin]

@MarkPalmberg That would be nice but is doomed to fail for two very different reasons

1.  Power BI Service does not support Sql.Databases()

2. Real life will mandate user permission controlled schema access.  So not only can you not consolidate connections, you will have scenarios where you have to duplicate the exact same connection because different user credentials have different access scope.

Keep in mind: A gateway connection is created with credentials. These credentials control the access scope, regardless of the end user eventually consuming the connection*.  This creates a risk of oversharing that you must be aware of, and mitigate accordingly

* with the exception of connections like SSAS that allow credentials pass-through

[MarkPalmberg]

Thank you for this reply, @lbendlin . I really appreciate your time and thoughts.

In our current state of affairs (I think often of Ellie Arroway's response to Congress when asked what she'd ask an intelligent being, should she encounter one on her trip through space: "...how did you survive this technological adolescence without destroying yourself?"), we have a system user permissioned at the gateway who that then, in turn, has server/database/schema access to our various SQL servers (down to EXEC permission by schema). This doesn't necessarily address the oversharing issue, but we're only a team of 4 on the reporting team, and we're trying to come up with ways to leverage dataflows that we can use to enable self-service access for the other folks in our org (premium capacity).

I think our infrastucture team was looking at the proliferation of data sources on our main gateway and thinking, "there's got to be a better way." But maybe there's not? Lots still to learn. Thanks again for your time!

[lbendlin]

We are pushing the rope on that one too.  For example we do not allow file connections on our enterprise gateways (we tell users to use folder/sharepoint folder connections).  But we also have data sources where we absolutely must separate the users, so we have no choice but to duplicate the connection.

The gateway management pages have gotten a bit better at handling many hundreds of connections. The REST API also helps to mitigate some of the extra effort (although it is missing critical parts like connection user role management or connection test skipping)