Power BI - Publish Reports from a remote SQL Server

[justinlane]

Hi,

I am new to Power BI. I have just downloaded Power BI Desktop to my PC and I have also looked at Power BI Service (which seems to be a browser version of the same thing). I can RDP to our SQL Server.

We have an ERP software that runs off an SQL Server Database that is sometimes installed in a cloud server (not Azure) and sometimes installed on-premises. We can RDP to the SQL Server and run queries in MS SQL Server Management Studio. Our users need remote access to reporting.

All the "how to" demos show Power BI connecting directly to MS SQL server (as if users can just RDP to our SQL server and create reports). That is great for a developer. But our users do not have access to the SQL Server directly.

For the purposes of this question - let's say we have the following tables:
* transaction header table (InvoiceNo / StoreID / TransDate / Customer / CustomerType )
* Line Item table (Stock Code / Description / StockCategory / Qty / UnitCost / UnitPrice / Total)

And say we have a stored procedure called "SalesSummary" that we want to use for pivot reporting, or charts or analysis or BI Reporting etc.
And this stored procedure returns summary data of: StoreID / TransDate / CustomerType / StockCode / StockCategory / SumOfQty / SumOfCost / SumOfTotal
Say we have 20,000,000 records for this result set over the last 5 years of data.
And say our users want to pick and choose fields from this result to analyze: pivot or report or chart etc.

Question 1: How can I setup Power BI to access this SQL Server database so that users can access the Power BI reports from anywhere (where the users are not able to RDP or connect directly to the Server running the SQL Server Database)?

Question 2: Can we make available the raw SQL tables or stored procedures so that users can get remote access to them and create their own Power BI Reports on these remote datasets?

Question 3: If the above is possible. How do we set security levels where some users have more access to data than others?  (For example some users only have access to say 3 stores)

Question 4: Say a user decides that today they want a chart of the last 12 months SumofTotal by StockCategory by Quarters (where this data is a sumarized subset of our SalesSummary stored procedure above). Say the result has 500 records. Is there a way to get Power BI to produce this sumarized subset on the server side and publish or transmit the end report to the user (rather than uploading 20,000,000 records to the user which would be a lot slower) ?

Question 5: If question 3 is possible. Can the user define the report on their workstation - for whatever they require - where the results are a summary or subset of our SalesSummary stored procedure?

Question 6: What is the pricing (in USD) for Power BI licenses that we need to achieve what we want above?

Question 7: What would the pricing be if we had our SQL Database on an Azure Server?

NOTE: We thought about running a scheduled task to extract the results from our SalesSummary stored procedure and save to CSV files on a OneDrive shared drive. Then using Power BI Desktop to access the CSV files from OneDive (shared to all users). BUT I do not really like this solution because of the size of the CSV file. Generating it nightly is OK. But I wonder if it would be slow to access at the users end? Also it just seems a bit clunky?

[justinlane]

Hi Stuart,

Thank you very much for all the useful information!!!

My main issue is that in most cases our SQL servers are NOT "on-premesis".  Instead our SQL Servers are installed on Virtual Private Servers (ie various Windows cloud servers).  

So how do I give remote access to Power BI Reports where the data is on these SQL Server (installed on cloud servers)?   Do we just install the "on-premiss data gateway" onto these cloud servers.  Would that work? 

Would performance be OK - given that the users are not on the same LAN as the SQL Servers? 

[Burningsuit]

Hi @justinlane 

You don't need a gateway for cloud-based data. You can just provide login credentials for the service, that's all that's necessary. However if you need more than simple login credentials to access the data or want a higher level of security and control you can install a Gateway on the cloud-based server and use that.

Performance should be fine, Power BI loads the data into the Dataset and then runs reports based from that. You can set up regular refreshes to keep the Dateset up to date. Report performance is good because it's not accessing the live data, it's reading the local Dataset copy.

hope this helps 

Stuart

[justinlane]

Hi Stuart,

Awesome!   Thank you.  Now just two last questions (sorry)...

When you refer to the "Service" how do I install that.  Can you please provide a link?

Or when you refer to "service" do you mean installing "PowerBIReportServer.exe"?

And how do I setup "simple login credentials" for this "service"?

[Burningsuit]

Hi @justinlane 

In this context "service" is whatever cloud-based system you are connected to. This could be Microsoft Azure, Google Drive, Dropbox or almost any cloud-based data storeage or database.

The "credentials" are the login details for that "service". For example, when I access my Microsoft SQL server on Azure my connection string is..

Server: <domainname>.database.windows.net

Database (optional): <dbname>

I then have to provide my credentials of login name and password. If they are correct I'm connected to my Azure Database and don't need an Power BI gateway to access or refresh the data.

If I'm accessing an Excel file on Sharepoint (Sharepoint is the "service") I need to use the web connector in PowerBI and put in the URL of the file, like this ..

"http://tenant.sharepoint.com/sites/sitename/library/folder/excelfile.xlsx"

I'll then need to provide my "credentials" (logon details) to this "service" (Sharepoint) so I am authenticated and the data can be accessed.

Services that provide a login to their data via the cloud don't need to use a Power BI Gateway to refresh the data. Generally, if you can go to a web-page, login and see your data, Power BI can access that without needing a Power BI Gateway. If you firts have to tunnell in via a VPN, or the data in not exposed to the Internet (on an in-house server for example) then you'll need a Gateway.

Watch some youtube videos (these are good) (74) Guy in a Cube - YouTube or get some Training and hopefully this will all become clear.

Hope this helps

Stuart

PS. If this helped give some kudos, if it helped or solved the problem please mark it as a solution for others to find it. 

[justinlane]

Hi Stuart,

hnk you.  Again an awesome reply.  This whole thread is a great "how to get started" guide.  I really appreciate your patience.  Final questions - I Promise.

So what do I install on the actual SQL Server? 

I am assuming I need to install "PowerBIReportServer.exe"?

I started installing it and chose the trial version for now.  I presume the license is per user.  And pricing is here: Pricing & Product Comparison | Microsoft Power BI.  Or is the "PowerBIReportServer.exe" a separate license?

[Burningsuit · Accepted solution]

Hi @justinlane 

I think we need a quick overview of Power BI that'll help you understand what "PowerBIReportServer.exe" is. So here goes, get yourself a drink and some popcorn 😊.

In most cases Power BI is a cloud-based application. (Cloud-based = Software as as Service delivered over the Internet). You need a licence, let's start with a Pro licence, then you can go to https:://powerbi.microsoft.com click "sign in" on the upper right corner, enter the login name and password allocated to your Pro licence and you'll see your PowerBI home page.  You can begin to build Reports and Visualisations straight from the Cloud based Service, but most people use Power BI Desktop, this is a free Windows application that can be downloaded from Microsoft here. With Power BI Desktop you can connect to data and build Reports based on that data. With Power BI desktop you can then "Publish" the Report you generated. This logs you in to your Power BI account, then copies the Report up to the Power BI service. Once in the Power BI service the report can be shared with others (who also need a Power BI Pro licence to view it) and generally edited and manipulated. With Power BI desktop you can connect to data in SQL servers, Sharepoint, OneDrive and a whole host of other places, visualise that data, and publish it to Power BI service. Note that this doesn't need any other software installed other than Power BI Desktop in your PC.

Once in the Power BI service that data in the Reports you've created can be refreshed automatically on a time based system. So once a day, you can update the Report with fresh data from the SQL database, Excel File, CSV file etc. To refresh this data, the Power BI service in the Cloud needs to access the SQL server (for example). If the SQL server is also in the cloud (On Microsoft Azure, say) then this is simple, all Power BI Service needs is the login to the web-facing database and it will open the database and access the data it needs. If the SQL server if behind a firewall or not connected to the Internet. (on an in-house Server) You need to install a Power BI Gateway to transport the data from your in-house SQL database up to Power BI in the Cloud. This Gateway software can be installed on the SQL server or on a dedicated Server for large implementations. With the standard Power BI system that's all you need.

Now (if you're still reading) you'll remember at the top I said that in most cases Power BI is a cloud-based application That's because in particular situations you can run a "private" copy of Power BI on your own servers. This is mostly used by organisations who have highly sensitive data that they don't want anywhere near the Internet, or by organisations that don't trust Microsoft's Cloud security, or have a large infrastructure they are slow in moving to the Cloud. This is Power BI Report Server. It's an expensive system requiring a Premium Capacity Licence, although it can be purchased if your organisation has Microsoft Software Assurance and SSRS. That's what PowerBIReportServer.exe is. Ideally it needs to run on a dedicated in-house Server and you are basically replicating a private copy of Power BI service in the cloud.

I'd suggest this is not what you want (at least at this stage). Most implementations start with a couple or so Pro licences, publish some data to the Power BI online cloud and start to learn what it can do. Start with https:://powerbi.microsoft.com and a Pro licence, Download Power BI Desktop onto your PC, and begin a journey of discovery.

Hope this helps

Stuart

PS, finished the popcorn ?😂

[Anonymous]

Hi Stuart,
Thank You for this masterpiece Sir ,This has help me took 50% of my path for the similar proble i was about to face .
But now my issue is cleared from the POwer Bi Side but know questions arise is ,

1) How can i connect a POS(Point of sale system ) which is in cloud which updates every 10 mins to power Bi directly? (As per your explaination i just need the credentials from the customer and start preparing the Visuals as per the functional explaination)

2) Customer wants a data copy(Replica) for this particular POS Data  Cloud  into Azure enviroment which arise a Question for me what Azure service or component  will i need to  execute the same ?( I mean a AZURE SQL DATABASE, DATA FACTORY , DATA LAKE)

3) ALSO the base server in the stores are some POSTGREL DB and SOME SQL DB for the customers.

4) The other source of data is EXCEL,Business Center and Instagram for sentimental Analysis.
Hope you got what i mean to achive.
Waiting for the guidance.

Thank You in Advance!!

[justinlane]

Hi Stuart,

Yee hah!  Got it.  All questions answered. 

We are on our way to offering Power BI reporting for our ERP customers.

Thank you very much

[Burningsuit]

Hi @justinlane 

Whoa! that's a lot of questions! Let me see if I can give some basic answers and signpost you to the detail...

Firstly we need to make a distinction between Report Developers and Report Consumers. Developers can use Power BI Desktop to connect to data, then build and publish Datasets (data stores held in the Power BI Service in the cloud) and Reports (Visualisations of data held in Datasets). Developers can build Reports based on Data in Datasets and then share them through the Power BI cloud Service, with Consumers who can analyse, slice and dice and manipulate the Reports.  Consumers can access whatever Reports have been built for them and use whatever facilities the Developer has built into that Report to filter and analyse the Reports. They can also use ad-hoc query facilities (called Q&A in Power BI) to ask questions of the Data in the Datasets, if the Developers give them this facility.

Q1: If you have Data "on-premise" in SQL Databases on your own servers, or simply Excel files on your own File Servers, you can use a Power BI Gateway to give Power BI Access to this data to build Datasets, then once the Datasets have been created, you can set up Automated Refreshing, through the Gateway to update the Dataset several times per day. Your users, as Developers can log in to Power BI and access the data through the gateway to build Datasets from the Data available there. Your users, as Consumers can log in to Power BI to view and interact with reports that have been built for them which are based on Datasets, which are buiilt and refreshed through the Gateway. 

See On-premises data gateway - Power BI | Microsoft Docs

Q2: Your developers, through a Power BI Gateway can access the raw database tables,  and through a tool called Power Query (Part of Power BI) can filter, clean and transform this data into a Dataset, which may contain several, related, tables. Your Consumers, through Power BI can access Reports (Visualisations) of these Datasets, you can also (subject to security) allow Consumers to connect to Datasets to build their own reports.

Q3: There is a features called Row Level Security (RLS) in Power BI that allows a Developer to assign securirt to Rown of Data, and for Consumers to see data restricted to themselves only. For example, you can create one Stores report and through RLS each Stores manager only sees their Stores data when viewing this Report.

See: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Q4: Developers can use Power Query to limit the amount of data they want in a Report. If the Data source is an SQL source, Power Query will do "Query Folding". That is, it creates an SQL query from the filtering and cleaning required and runs this against the SQL server to only request the data required.

See : Query folding | Microsoft Docs

Consumers access Reports that run on a particular Dataset, they can filter the data, (within the limits set for them by Developers) but this only affects the Dataset, it does not hit the SQL server. Think of the Dataset as a "buffer" or "cache" which servers the Report and is regularly refreshed by Power BI.

See: Data refresh in Power BI - Power BI | Microsoft Docs

Q5: Yes, Power BI can access Stored Procedures, Database Tables, and many other data types. All of the above facilities can be applied to any data type (except Query Folding with only works for SQL style data sources)

Q6: License prices are here: Pricing & Product Comparison | Microsoft Power BI

Basically you're going to need a Power BI Pro Licence for every Developer AND Consumer. (There is no anonymous viewing for Power BI that provides security) Reports may be made public, but anyone can access them and RLS will not work with public Reports. You're going to need a licence for each Consumer to keep the Data secure and allow use of Row Level Security. 

If you need more facilities and power than the basic Power BI Pro licence you can purchase Premium Per User licences, these allow much bigger datasets, and a number of other facilities like Paginated Reports (Think SSRS style reports). If you have more than 400 (or so) users, it may be more cost-effective to get a Premium Capacity licence. This allows Secure access by free licences and many other large scale benefits, but at an appropriate cost.

See: Power BI Free vs Pro | What's The Difference | Burningsuit

Q7: There is no difference to the Licence cost if you use Azure Server. The Licence costs are the same irrespective of where the Data comes from. 

Power BI is growing into a large complex system. Spend some time on the Documentation, and ask questions here, but also approach your local Microsoft Partner to get them to help you navigate the options.

Hope this helps

Stuart