In my role as a BI Analyst I have tinkered with many different main-stream business analytics and visualization platforms but so far my favorite is Microsoft’s PowerBI. Even though it seems like this platform is constantly in development (Microsoft releases monthly updates to both PowerBI Desktop and the web application), it feels like Christmas every single time. All of the functionality Microsoft is building in is very impressive. You can create your own visualizations while programming in R, you can create and manage report parameters, manage and edit queries and see your results in real time, etc. but one of the coolest parts, to me, is that you are able to create and manage all report data sources and datasets in one central location through what Microsoft has called the “Data Gateway”.
Data Gateway
This gateway hold the information needed for connection strings to your SQL instances and data sources (much like in SSRS where you are able to utilize shared data sources). Of course, Microsoft also has created a downloadable program that will establish your own personal gateway or the gateway manager for your local machine. This makes sense to install directly to the server housing your instance because ideally you never want your servers to go down and the personal gateway requires a constant connection for scheduled refresh, otherwise the refresh will fail. How easy is it to manage your sources using the application? I’ll show you.
First, log into app.powerbi.com and log in using your Microsoft account. Once you’re there navigate to the “Manage Gateways” page through the gear at the top of the initial work-space. Once there select “Add Data Source” and begin filling out the information to connect to an instance of SQL Server and the administrators for this connection. Filling out department and description are optional.
Next create your connection to your data source(s). Under “Data Source Settings” place all of your credentials and your data source type into the page and test your connection to make sure PowerBI can reach your data. You can even set privacy and authentication modes!
Now that your gateway is established go ahead and add in whatever data you wish to use for a report. Upload it directly to PowerBI either through PowerBI Desktop or import the file into PowerBI and select it. Right click and select “schedule refresh” to create your refresh schedule and set a data gateway for your new data set.
Once you are looking at your datasets select one and begin allocating a gateway and how often, if ever, you wish your data to refresh and stay up to date.
It’s that simple! Now you are ready to show your reports and not have to worry about old, stale data.
Side Notes
PowerBI scheduled refresh and on-premise refresh are only available in the ENTERPRISE version of PowerBI. If you do not have this then you should get it. The increased space from 1GB to 10GBs per work space is worth it alone.
PowerBI does not handle any pbix files (PowerBI report files) over 1GB. So make sure that when you create your reports that you aren’t importing more than 1GB of data into your report. Keep your datasets as simple as possible to improve performance and save space.
That One SQL Blog 