Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KogerMD
New Member

How to access SQL data on secure server

For security reasons, our end users do not have direct access to our SQL server, so they are not able to create data connections in Power BI Desktop that pull data directly from SQL.  With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature). We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.

 

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers? 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User


@KogerMD wrote:

With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature).


SSRS Shared datasets do exist in Power BI Report Server (since it is built on top of SSRS). What does not exist yet in PBIRS is the ability to share datasets from pbix reports.

 


@KogerMD wrote:

We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.


This should still work, but with the same limitations. The shared datasets have no way of efficiently implementing top, select, filter etc as they can be based on things like stored procs or file based data sources so there is no way to consistently "fold down" these operations to the underlying data source.

 


@KogerMD wrote:

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers? 


One other option is to create a set of one or more tabular models hosted on a local SSAS instance. Then you can have the users report off those instead of hitting your SQL servers directly.

View solution in original post

1 REPLY 1
d_gosbell
Super User
Super User


@KogerMD wrote:

With SSRS, we were mostly able to get around this through the use of Shared DataSets, but this concept doesn't exist in Power BI Report Server (although I see that it has been requested as a new feature).


SSRS Shared datasets do exist in Power BI Report Server (since it is built on top of SSRS). What does not exist yet in PBIRS is the ability to share datasets from pbix reports.

 


@KogerMD wrote:

We currently use a combination of SSRS Shared DataSets and connect to them over OData from PBIRS to allow our users the ability to create reports that pull data directly from the SQL server. The problem with this is that the SSRS REST API implementation doesn't support the standard OData methods such as top, select, filter, etc, so they are very slow when there is a lot of data being pulled in.


This should still work, but with the same limitations. The shared datasets have no way of efficiently implementing top, select, filter etc as they can be based on things like stored procs or file based data sources so there is no way to consistently "fold down" these operations to the underlying data source.

 


@KogerMD wrote:

Is there a better solution that we can use that will allow our users to report on the SQL data without having to give them direct access to the SQL servers? 


One other option is to create a set of one or more tabular models hosted on a local SSAS instance. Then you can have the users report off those instead of hitting your SQL servers directly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.