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
- Locate the Excel you want to connect to your Power BI
- 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
- 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.
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 all of your data sources
- Transform necessary data
- Create relationships if necessary
- Create visualizations
- May need to do additional data loads, transformations and relationship connections as you continue to build
- Format visuals
- Publish to web
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'
- This will then give you a link to the dashboard
- EVERY TIME you make a change, you will want to re-publish to the same location and replace the existing version
- I would also recommend copy/pasting a link to the web version in your folder as a resource
- You can do this by going to the OneDrive Data reporting group, find the folder you want to save the link to. Select 'New' at the top and then 'Link', paste the link to the published PBI version there
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