HCOM Power BI - Getting Started

Overview

In an effort to streamline Power Bi storage and reduce locally stored files, a OneDrive to store all Data Reporting files and resources has been created. Like most Microsoft products, there is a desktop version with more functionality and a scaled down function web version for Power BI. The desktop version should be used for Power BI creators, this is where you will load, transform and model data. When ready for end user viewing, you'll publish it to the web and that web link is what you will share with end users. Any changes you make to the desktop version, you will continously publish to the web version. This will be outlined in the below instructions.

OneDrive - Data Reporting

• Go to the OneDrive Data Reporting Group - HCOM - Data Reporting - Documents - All Documents (sharepoint.com)
  • Click on 'Go to Site' on the right hand side
  • Create a folder for all of your resources (Power BI, Excel files, etc.)
    • This will store your Power BI report, the link to the published version of the report and any data resources used. 

Power BI

• From your Power BI desktop app, create a new report
  • File > Save As > OneDrive > Select 'HCOM - Data Reporting' group
• This will store the file in OneDrive rather than locally

Power BI - Connecting to Salesforce as data source

• From your Power BI report, go to 'Get Data', select 'More'
• Navigate to 'Online Services' on the left
• On the right select one of the following:
  • Salesforce Objects - This will allow you to select one or more Data objects/sets. For example, the entire Contact record. This is generally the most preferred as it will automatically make new fields created available to you.
  • Salesforce Reports - This will allow you to select a report you've already created in Salesforce. This is sometimes really helpful if you want just a small set of data, rather than the entire object/data set. This can also be super helpful if you need to group data or include related data in a certain way.
    • NOTE: If your report will ever be over 2,000 rows of data, DO NOT USE REPORTS, Power bi will truncate all rows above 2,000.

• Once you've made your selection, click 'Connect'
• You'll make sure 'Production' is selected, then 'OK'
  • If you're already logged into Salesforce, it should automatically authenticate. 

• From here, you can select one more data objects/sets or reports. Then choose 'Load' if you're ready to start using it or 'Transform' if you need to make adjustments to the data before building visuals.

Power BI - Connecting to Excel files as data source

• In OneDrive > Data Reporting Group, make sure you have a folder for your project created and any Excel resources already loaded to that folder. It is not recommended to use local Excel files.
• Locate the Excel you want to connect to your Power BI from your OneDrive Data Reporting group folder
• Click the ellipsis to the right of that file and select 'Details'

• On the right hand side, a navigation pane should appear, scroll to the bottom to file 'Path'
• Select the icon to the right of 'Path' to copy the link to it. 

Note: VERY important that your files are NOT moved after this point, it is looking specifically for this path. If you need to replace the file make sure the name is exactly the same or copy/paste the new date over the old. Your Power BI may error if the path is altered.

• Once you've copied the path, go back to your Power BI report
• Go to 'Get Data'
• Select 'Web'
• Paste the link you copied from details here and click 'OK'

• From here, you'll need to choose an authentication method. I typically try to select 'Organizational Account' using my OU credentials. 

• Again, from here you can choose to directly load or transform the data before you create data models. 

Power BI - Connecting to SharePoint List as a data source

• From your Power BI report go to 'Get Data' > Select 'SharePoint Online List'
  • Go to your SharePoint Online List and from the url link grab JUST THE ROOT of the url. Below are a few examples, you need just the beginning bold part of the url, everything before /Lists part of the url link:
    • https://catmailohio.sharepoint.com/sites/HCOM-Hcom-DataReporting/Lists/OMET%20Project%20Requests/AllItems.aspx?env=WebViewList&siteid=%7B3347311C-8AC3-4483-98DC-B081F408CE87%7D&webid=%7B4FE1ED22-5AB8-443F-AE52-84191A85D11E%7D&uniqueid=%7B2CE9442F-4916-4462-AAD5-2CBB05286188%7D
    • https://catmailohio.sharepoint.com/sites/HCOM-StudentAdministrationCurriculumProject/Lists/eMedley%20Customization%20Database/AllItems.aspx?env=WebViewList&sortField=AcademicYear&isAscending=true&viewid=67ca91c2-f5a1-4a3e-b4f3-fce3
    • https://catmailohio.sharepoint.com/sites/hcom-oilt/Lists/OMET%20Budget%20Tracking/AllItems.aspx?env=WebViewList
  • Enter that link in the 'Site URL', select 2.0 and leave the view mode to All, click OK

• In the display options, find the name of your Sharepoint List, select it and click OK. It may ask you to enter your OU credentials to connect to that SharePoint site

• More than likely, you'll want to use your Microsoft Account to sign in and save

 

Creating a Power BI 

There are a lot of functions to cover when creating a Power BI report, these knowledge base articles go into more detail: Knowledge Base - Power Bi (ohio.edu). Once you have your folder created in the data reporting group, a very basic overview of to creating Power BI reports is as follows:

• Load and Transform necessary data
• Create relationships if necessary
• Create and format visualizations
• Publish to web and schedule refreshes if necessary (see below)

Publishing a Power Bi to a web version

It is highly recommended to publish Power Bi projects to the web for end users. The web version is much more user friendly for end users. It prevents end users from modifying the desktop version of the report. It enables the developer to hide a lot of items from the web version; tabs you're still working on, filters you don't need end users to see, etc. 

• To publish a web version of the Power BI, from your Power BI, Home tab, select 'Publish'
  • Select 'HCOM - Data Reporting' or whichever workspace you are using.
    • EVERY TIME you make a change, you will want to re-publish to the same location and replace the existing version
    • To generate a viewable link with managed permissions, I recommend reviewing this article, Article - HCOM Power BI - Manage Perm..., to create a viewable link.

Scheduling a refresh

For most Power BI projects, you will want to schedule the report to automatically refresh. This will save your end user time from having to figure out how to navigate that and will ensure that the information is routinely up to date. Note: the refresh icon in the top right hand corner (below), will NOT actually update the data set. 

To setup a refresh, click on the ellipsis in the top navigation bar, then select 'View semantic model'

From here, you can see the date and time it was last refreshed. 

• If you need to manually refresh it, you can click 'Refresh now'
• To schedule a refresh, click on 'Schedule refresh'

• Locate the 'Refresh' section on the page and expand it
• Toggle on the 'Configure a refresh schedule'
• From there you can choose your refresh frequency; daily or weekly. Daily will often be the most preferred
• Then you can select up to 8 refreshes (this is a Power BI limitation). I often select the even hours; 6 a.m., 8 a.m., 10 a.m., 12 p.m., 2 p.m., 4 p.m., 6 p.m., 8 p.m.. Use 'Add another time' to keep adding these.
• Make sure the Send refresh failure notification is selected to both the Semantic model owner and add hcomtech@ohio.edu so the OMET group is also notified.
• Be sure to click 'Apply' when done

To get back to your report, scroll to the top of the page and select 'View semantic model'

This will take you back to the schedule page where you can click to get back to the report 

Get help from HCOM IT

• Visit the Medical Education Technology page to create a ticket.