In the first Power BI blog, we looked at Power BI’s wide range of capabilities for unlocking data sources and answering business questions with them. We also showed how to unlock, aggregate and model data from source systems to get a good foundation for being able to create valuable reports and dashboards. In this second blog, we will look at how to actually create a Power BI report.
With Power BI, you can easily create a collection of queries, data connections, and reports that are simple to share with others. First, you access the data from one or more sources, then model this data into a data model, and finally create reports and dashboards that you can share with others.
For what is described here, we assume that data has already been accessed with Power BI Desktop, further refined for reporting, and modeled. See the previous blog for this. Accessing and modeling data is the foundation for creating reports and dashboards, which we will now cover.
When you click on the Report icon in the menu options on the left side of Power BI Desktop, you enter the section where you visualize the data on tabs. Returning to the example of revenue per location per month from the previous blog: if you want to compare the revenue of different locations, you can use a bar chart. If you want to see the trend of revenue for a single location throughout the year, you can use a line chart.
Building a Report in Power BI Desktop
Below is an example of a simple report. In it, you can see the number of remaining available hours per employee and per week (“18-31” means 2018, week 31). This report provides insight into occupancy rates and available resource capacity.
By clicking on the visualizations, you can gain more insights, such as zooming in on specific features. For example, clicking on the chart or selecting a different period in the timeline directly shows the corresponding data.
An example of some visuals in a Power BI Desktop report:
An example of a report published to PowerBI.com, displayed in the browser.
With the Publish button, you make the report available online in your workspace on PowerBI.com. You can also embed your Power BI report in your website and, depending on the possibilities, even integrate it into your application (e.g., CRM, SharePoint, Microsoft Project Online).
As described in the previous blog, Power BI works with data extractions. These are snapshots of data taken at a specific date and time, possibly over a certain period or for a specific category, region, industry, etc.
For management reporting, the data from yesterday, up to last week, or up to the previous month, usually suffices. You look back at the results. But this accessed data must be updated periodically to have up-to-date data for the reports. The free Power BI license offers automatic data refresh on PowerBI.com per dataset, for example, once a week or several times a day. Power BI can be scheduled to automatically access new source data at set times to keep reports up to date. With Power BI Pro, you can refresh the data more frequently and even, depending on the source, in real-time, for example, for planning when the data in the reports must be fully up to date.
The advantage of automated data refresh is that individual users don’t have to do it ad-hoc, and it can occur at set times (at night or during off-peak hours when source systems are used less). Accessing data for reports can place such a load on source systems that it is noticeable in the performance of normal application usage (e.g., noticeably slower system operation).
Scheduling Automatic Data Refresh in a Dataset
For automatically updating data in a dataset, it’s recommended to use a special service account. This account must have the proper authorization on the source systems to be accessed. If desired, multiple accounts can be used for different source systems to refresh a dataset in Power BI.
One of our clients uses a planning tool to manage resource capacity. However, the standard available reports from the planning system do not fully meet the information needs. The client wants to compare resource capacity requests from projects against the availability of resources. A dashboard is desired where, after clicking on a specific resource request for a project, it is immediately clear which resources (based on required skills) are eligible and what their remaining availability is in the requested period covered by the resource request. Therefore, we created a Power BI report in which (almost) real-time data from the planning tool is available. With Power BI, a correct match can be made between capacity requests and deployable resources, considering required skills and the number of hours to be deployed in a specific period. The resource deployment can then be processed directly in the planning tool. Since the data is available in Power BI in real-time, requests can be reviewed one by one, decisions can be made about which resources to deploy, and the planning can be updated, which is almost immediately synchronized in Power BI. This way, the resource planner handles resource requests from Power BI. A highly valuable client-specific extension to the planning tool.
How do you ensure that only people with the right authorization see your report or relevant data? We’ll cover this in the next blog.
)){kind=small}
)){kind=small}