One of the main benefits about Power BI is the ability to connect to multiple data sources and combine the information onto one report. It eliminates the need to look at different systems to gather pieces of information regarding how the business is performing.
In this post we are going to look specifically at the main data sources I have used in working with clients; however, this is a small part of what is available. To view all data connection possible, select “Get Data” within Power BI Desktop.
OData
OData contains REST APIs that allow you to connect to the data within your source. Generally, I use these connections with Customer Relationship Management Systems (CRMs); however, it is something that is available for those using Great Plains and other ERP systems as well. With Great Plains the OData connection must be set-up, but for a lot of cloud-based ERP solutions the OData connections are available out of the box.
When using an OData connection you will first enter the URL and then select your credentials. For this connection I recommend using an organizational account credential. Power BI will ask you to sign in with the username and password you use to access the system you are connecting to. It is important that your credentials have the appropriate security level to access the data necessary for the report.
SQL Server
This is the primary way I connect to Great Plains tables and other SQL backed ERP software systems. With the ability to create custom views within SQL it is sometimes easier than performing the data transformations within Power BI. These views can be pulled into Power BI and used for the reports.
With SQL server connections you will be required to enter the SQL server name and potentially the database.
Once you have this entered you will be prompted to select credentials and I recommend using the current windows credentials with this connection as security access to the SQL server is generally attached to your windows log-in.
Files and Folders
Although many reports use dynamic data there are still times that require the use of static documents or folders. I have used excel files as reference tables and folders to manage how many documents have been added to a particular folder, but these are just a couple of ideas on how you can use these connections.
When connecting to any file or folder you will need to have the location of the data. This does not require a set of credentials, but it is important to select where to house the files. If others need to access the files for updating you will want them stored in a shared location.
Websites
I have seen direct website connections to monitor current weather conditions, track top news stories and current scores. If your business is affected by these things it can be helpful to have an active web connection.
With websites I use anonymous credentials for public websites. If using an internal website that requires a log-in I will use an organizational log-in for credentials.
Entering Data Directly
This is an ability I rarely use but wanted to touch on in this post. Within Power BI there is the ability to enter table data manually. In the past I have used this for creating reference tables or assigning values to particular data sets.
Once you select to enter data a pop-up box appears that will allow you to type in column and row data.
Again, these are just a few of the possible data connections, but I have found these to be the most common in all the reports I have created over the years.
-Jerica Coleman, CRM and Power BI Consultant
