This chapter gives a brief introduction on using Power BI Desktop to generate reports and dashboards and integrating the reports in SharePoint to achieve Business Intelligence.

Business Intelligence in SharePoint is vast and is achieved with the combination of SharePoint and Microsoft Power BI in particularly Excel. This combination of the PBIX file and SharePoint enables us to create dashboards, reports, and scorecards with simple as well as complex data.

The above-said process is two-fold.

• Creation of Power BI Desktop file
• Integration with the SharePoint

Power BI is a cloud-based Analytics Service that allows users to analyze and visualize data with greater speed and efficiency. The data is transformed into interactive reports, dashboards, or Compelling visualizations.

The Power BI Service is built upon Azure. The Power BI service architecture consists of 2 clusters. 

 

1. WFE Cluster
2. Back end Cluster

WFE Cluster manages the initial connection and authentications process. Whereas BE Cluster handles the user interactions. Power BI uses Azure Active directory for the purpose of user validation & authentication, whereas Azure Blob and Azure SQL manage the storage of data and Metadata.

Power BI Service is not available as a private internal cloud. However, using Power BI and Power BI Desktop, you can connect to your on-premises data sources using on-premises Data Gateway.

Power BI is a Microsoft product which is of twofold:

• Power BI downloadable Desktop program  
• Cloud service (online program).

This document elaborates the steps to install and create Power BI Reports using Power BI Desktop program

Power BI Desktop integrates the powerful query engine with the online Power BI Service. It is a Powerful analytics tool designed for windows. Powerful data connections, models, and reports are created using the Power BI Desktop. Whereas Power BI Service is used to shares the power BI Desktop reports to the users to view and interact with them

It is a powerful and highly accessible tool to build robust models and craft reports.

How Power BI Desktop Works

Power BI Desktop enables the user to connect to different data sources data, shape or refine the data and represent the data model in the form of reports. This centralizes the scattered and disconnected data and represents them in the form of reports. The user can save the work to a file, which is Power BI Desktop file format (.pbix) file format. This can be shared with other users by uploading the file to the Power BI SERVICE

Go to PowerBI.Microsoft.com and click on Products and click on Power BI Desktop. Click on the download button. A PBI Desktop MSI file will be downloaded to the computer.

Execute the MSI file by following the instructions. A shortcut icon will be created on the desktop.

To use the Power BI, few requirements need to be satisfied.

1. Power BI Account – The user has to register to PowerBI.COM and get created the account
2. Power pivots and power queries are used to generate reports.
3. A website is needed to upload the report. Website can be either SharePoint / WordPress / any website / web page.

To start up with, double click the Desktop icon, a welcome screen is displayed.

Click on Get Data Icon to select the raw input data. It supports many input data formats like files, Databases, Azure, or Online services.

In this current context, you will take up the SQL Server database as input data. Select the SQL Server database and click on the connect button. Enter the database details.

Click on the Load button to load the data to Report.

• Report View
• Data View
• Relationship view

There are three views found towards the left of the Power BI Desktop.

Report View is the View, which shows the actual representation of the data based on the visualizations of the chart selected.

Data View depicts the data in table format. You can cross verify the data to be mapped to the report. Intermediate changes are allowed to adjust the data using the query editor window and need to refresh the data for the same to get refreshed to the report. The below diagram depicts the sample data

Relationship View depicts the pictorial representation of the relationship between columns.

The user can also Edit / Combine more than one table using query editor. The query editor is used to shape and combine the data from multiple tables. However, the original data source is not getting altered. Based on the requirement, only the view of this data source is getting altered. This also has an option of merging queries.

All data manipulation activities are carried out in the query editor. Suppose the current data type of the column is decimal and need to convert to the whole number just right-click the column header and select change type and again select the whole number.

Finally, to apply the query Editor changes, click on the close & apply button in the Home Ribbon Tab. The Power BI Table is added to the report.

The Report View has five sections.

• The ribbon panel displays the tasks associated with the report.
• Report View pane enables the user to view the Visualizations and customize the report.
• Pages pane enables you to add a report page.
• Visualizations pane to customize the report like applying the different kinds of charts and axis fields and filters.
• Fields pane enables the user to drag query elements and filters to the report view.

In this context, as the Power BI Table data contains state data, let us create a Map report. Based on the input data and requirement of the user can create different types of visualizations like Pie chart, Bar diagram, Line chart, etc.

To create the visualization, Drag and drop the fields column to the location field, i.e., drag the state column to the report view, A map with the states defined in the table.

In this example, as you created the selected Map Report Chart in the visualization, the data available in the table column state is mapped to the Map control Chart. An in-depth view of the states can be obtained by applying filters for the states.

If the source data changes, you need to update the Excel sheet. The issue mentioned above can be rectified by using Microsoft Power BI by analyzing and building charts.

Step 1 – Power BI Desktop Activities and publishing

1. Please download the Power BI desktop version. Once the Power Bi desktop version is installed successfully, open the same using the Office365 credentials.
2. Select Get Data  SharePoint Online List. Then a pop up will open. Provide the Site link and then log in using your credentials for that application.

Note: Apart from SharePoint online, there are so many options available for which you have to analyze the data and create the charts. I have listed out a few of the options available there once you click on “Get Date.”

        SharePoint List – Choose this if your application is an on-premise SharePoint application.

        SharePoint folder

        Azure, SQL

        Oracle

        ODBC

        SAP HANA DB

        Microsoft Exchange

        Microsoft Exchange Online

        Dynamic 365

3. Select the list for which you need to represent that data (list items) in a chart.
4. Once the list is loaded successfully, select the Pie Chart option on Visualizations (right-hand panel). Drag & drop parameters (list columns) for which you analyze the data.
5. After completing your chart, you can save the file with the .pbix extension.
6. In the top ribbon, you can find the “Publish” button. Click on it.

Note: Once you published the chart using the desktop version, the same will be pushed to the Office 365 Power BI web version, where you need to perform some actions to set the Refresh time.

 

Step 2 - Connection update:

1. Open the Power BI web version. Log in using your Office365 credentials.
2. Once logged in successfully, click on Datasets  Data Connection. On that page, click on Data Source Credentials  Edit Credentials  OAuth2. Provide your/admin credentials on the authentication page.
3. Select “Schedule Refresh” and set the below refresh factors as per your requirement.

        Refresh Frequency

        Time Zone

        Refresh between

4. Click Apply.

Note: At this point in time, you are almost done with the chart preparation in which the data will be refreshed automatically according to the settings made above—the only thing you need to do to make use of this chart on the application page.

 

Step 3 - Sharing

You can share the chart with your organization or a particular set of people or use it in the application.

1. Share the Report via Dashboard
   1. In the web version Power BI page, you can find “Dashboard” in the left panel. Click on + plus sign near Dashboard.
   2. In the left panel under “Report,” click on the report which you want to share. Select the “Pin Live” page.
   3. In the Pine Live page, select the Dashboard where you want to share and finally click on “Pin Live.”

 

 

2. Share Report using Content Pack
   1. In the web Power Bi page, click on the gear icon at the top panel.
   2. From the various option listed, select “Create Content Pack.”
   3. Provide the list of users’ email addresses for whom you want to share the data analysis and select the respective Report.
   4. Click OK.
3. Share the Report using the URL or embedded URL:
   1. In the web version Power BI page, you can find “Report” in the left panel. Click on the report you want to publish.
   2. In the top panel, click on the “File” option.
   3. Then click on “Publish to Web.”
   4. A pop up will appear, which will provide you the two options.
   5. The link you can send in an email   the direct URL which you can share with users.
   6. Html, you can paste into your blog or website  the code which you can add in “Embed content” of your HTMLK page to display the data analysis.
   7. Size  You can select the required size. Once you select the size, the above “Html” code will change accordingly.

Power BI is one of the Microsoft tools. Here you will explain the process of publishing a Power BI report to the Power BI online dashboard and to embe . After installing Power BI desktop, sign in with your enterprise office 365 account for accessing Power BI Application, which is also available for a free trial version, also paid one as per business requirement.

Power BI desktop can connect a variety of data sources like files such as Excel and CSV, Azure (Azure Services), Databases (SQL, and Oracle Server database) and SharePoint Online, etc. and create simple and complex customized business reports.

When the installation is complete, open Power BI Desktop and connect to the data source and create reports. Save the report in Power BI Desktop format, which is the “.pbix” extension. Finally, publish reports to Power BI Application and embed it to SharePoint.

Power BI frequently updates the data in real-time, if you are using a paid version of Power BI and providing options to refresh the data by schedule refresh in a timely manner. If you are using a free version of Power BI, can schedule refresh by timely manner (eight times per day) not frequently.

Steps to Create Power BI Reports

This section explains how to connect to SharePoint online data for creating reports.

Connect to SharePoint Online

1. Open Power BI Desktop and click on ‘Get Data.’
2. Click on “More…” and Select Online Services -> SharePoint Online List and click on “Connect.”
3. Enter the SharePoint site URL and click on “OK.”
4. Select Microsoft account -> Select a site -> Sign in.
5. A login window will open. Provide Microsoft credentials and click on “Connect.”
6. Select the lists to generate reports and click on “Load.”
7.    Selected lists get loaded on the right side under “FIELDS.”

Create Reports using Different Visualizations

You can create different types of reports using different visualizations. Ex. Pie chart, Donut chart, stacked column chart, etc. You can import charts from the store, from the file. Also, it is possible to import custom visuals to Power BI Desktop. Below are some of the sample charts.

ChicletSlicer: This chiclet slicer has to import from the store.

1. Click on … under visualizations and select Import from the store.
2. Search for ChicletSlicer and click on “ADD.”
3. Click on the ChicletSlicer, it gets added to the page.
4. Drag and drop fields from lists as per requirement. Here the selected field is dragged and drop into “Category.”
5. Select the “Format” tab and add the header, change the background color, font, etc. as per requirement.

In the same way, you will create other reports as per business needs.

You can create a relationship between two tables in the Power BI. So, based on the selection of one chart, other charts data get filtered in the reports. Below are the steps to create a relationship.

1. Click on “Manage Relationships.”
2. Select the tables.
3. Double click on the selected tables and select the unique value field in both tables and click on “OK.”

You can create calculated columns in the tables. For example, while creating a chart using two tables or one table, that particular chart needs a column value that needs to be customized for chart representation. Below are some of the columns which will be used in Power BI charts.

Calculated column using the DAX (Data Analysis Expressions) formula in Power BI and visualization tools. For row-by-row calculation calculated column will be used. For adding Calculated column,

1. Go to “FIELDS” and right-click on the table and click on “New Column.”
2. Rename the column and write the formula as per requirement. Use that column for creating charts.

Create Measure Column

Measures are used to calculate aggregates, such as sum or average of a column.

1. Go to “FIELDS” and right-click on the table and click on “New measure.”

Sample Formula: Sum of XY = CALCULATE(SUM(‘X’[a]), (‘Y’[b]))

Conditional Column and Custom Column

The custom column uses the M language. In the custom column, you cannot perform functions like SUM, AVG, etc. So, it is recommending to use Measures and Calculated column in Power BI for calculations. For creating custom and conditional column.

1. Go to “Edit Queries” and select “Edit Queries.”
2. Under “Add Column,” select “Custom” or “Conditional Column.”

Publish to Power BI App and Embed in SharePoint online

This section explains how to publish the Power BI file to the Power BI app and embed it in SharePoint online.

1. Click on “Publish” and Select a Destination and click on “Select.”
2. Click on “Open ‘document name.pbix’ in Power BI.”
3. Sign in to Power BI Account; the Power BI app will open with the published file.
4. For embed into SharePoint, click on “File” and click on “Publish to the web.”
5. Click on “Create embed code.” Click on “Publish.”
6. Copy the “Html you can paste into your blog or website.”
7. Go to the SharePoint page. Click on “Edit Page.” Click on “Edit Source” and paste the HTML code.
8. After saving the page, you will get a report on the page.

Schedule Refresh

This section explains how to refresh the data in reports. You can schedule refresh in a timely manner. Reports can be refreshed eight times per day. Follow the steps to schedule refresh.

1.       Open the Power BI app and gear icon and click on “Settings.”

2.       Click on Datasets and click on the file which you need to schedule refresh.

3.       Go to “Scheduled refresh” and change it to “On” and add the time, click on “Apply.” You can add another scheduled time.

This is an important feature of PowerBI. If data source (In your case SharePoint List) gets updated, your report should get updated on SharePoint Page. You can schedule refresh for your dataset. Click on “…” of your dataset and select Schedule refresh .

Schedule Refresh

• Keep your data up to Date: On
• Refresh Frequency: Data can be refreshed daily or weekly.
• Add another time: You can set your own time when you want to refresh your data.
• Send refresh failure notification email to me: Check this option for getting the mail notification when your automatic refresh gets failed.

Change Data Source

This section explains how to change the data source. Consider the SharePoint site is migrated to another environment, the Power BI report also needs to be migrated, in that case, you have to do some changes in the Power BI desktop file and publish it to SharePoint. Make sure that whatever lists used for reports should be the same in the Migrated site also. Below are the steps to change the data source.

1. Open the provided “pbix” file in Power BI.
2. Click on ‘Home’ in the Ribbon menu and go to Edit queries. Choose ‘Data Source settings.’
3. Click the ‘Change Source’ button.
4. Provide the URL of the SharePoint site and click close.
5. A pop-up to sign in to the portal opens. Choose the Microsoft account. Select the URL from the dropdown and click sign-in. Provide the credentials and click Connect.
6. Click on ‘Apply changes’ in the ribbon. A popup listing all the errors will open. Click on Close.
7. Click the ‘Home’ menu again. Click on the ‘Edit Queries’ option and choose Edit Queries.
8. A page with all the lists with errors in the left navigation pane will open.
9. Click on ‘List.’ From the Query settings in the right pane, click the gear icon near “Navigation.” Select ‘List’ from the pop-up and click OK.
10.                      The Query Settings will now have “Renamed Columns1” in addition to “Renamed Columns,” which was present earlier. Delete “Renamed Columns.” (If there are more than 1 Renamed columns with different suffixes, delete all the older ones and retain only the newly created one.)
11.                      Repeat steps 8 and 9 for Waves, Sites, and Libraries. [Note: The warning sign in the left navigation will be changed to the list icon].
12.                      Click on Close & Apply.
13.                      Once the page is loaded, click on Publish and follow the steps explained in Publish to Power BI App and Embed in SharePoint Online.

SharePoint page can be shared with office 365 users (SharePoint Online users). All users should have Office 365 account with PowerBI service. However, Power Bi report is embedded into this SharePoint page, to view these reports to SharePoint Online users must have access to PowerBI reports also.

It means that you need to manage permission on both locations, PowerBI report, and SharePoint Online Page.

Access to SharePoint : Open SharePoint Page where your PowerBI report is integrated. On the right hand, there is a SHARE button. Click on it. One popup will open to add users for whom you want to give permission to this page.

Enter name or mail ID’s of office 365 users, select permission level as per your requirement, and click on Share

PowerBI Permission : The user also needs permission in PowerBI to access the report. To handle the permission, you can use Manage Permissions. Go to Datasets and click on the manage permissions. Click on Add Users.

Enter office 365 user’s name or mail ID whom you want to share the PowerBI report. Click on Add. After assigning permissions on both locations, the user can view or edit the report as per the permission level.

The main prerequisites to host the Power BI Report in SharePoint are:

• The SharePoint site should have been created to host on the site.
• The Power BI Account should be created

To Host the power BI Reports in SharePoint, you need to publish the report. To publish the report, open the report in Power BI and select publish to Web option. This will open up with a dialog box showing Embed code and iframe details. Make a note of these details.

• Go to the SharePoint site to which the Power BI Report needs to be hosted.
• Click on the settings icon in the site and click on Add a page option to create a web page.
• Give proper Name and open the page in Editable mode.
• Click on the insert web part option from the Ribbon.
• Go to the Media and content category and in the parts select Script Editor option.
• Script Editor is added to the page.
• Click on the Edit Snippet link in Top Right of the page.
• Now copy the HTML iframe code and paste it to the snippet editor.
• A representation view of the image will be shown on the page.
• Finally, click on insert, save, and publish the page in SharePoint.

Microsoft Power BI is a suite for business analytics. You can use Power BI to analyze the data, explore data, and create rich reports quickly.

A business analyst or a developer can use open data protocol (OData) endpoints to create Power BI reports. Consider the Dynamics 365 for Operations application is configured to allow Power BI reports.

1. Open the Power BI desktop application and click on the sign-in button.
2. Provide the User Id, which has power BI access and clicks the sign in.
3. A window opens prompting for the password. Enter the password and click on the sign to access the Power BI desktop.
4. In the Power BI desktop, click on the “Get Data” option in the action pane and then click OData Feed from the options listed.
5. A window pops up for OData Feed prompting to enter the URL. Provide the URL of the Dynamics 365 application by appending “/data” at the end and click on the Ok button.
6. An Access window for OData opens where the Sign-in can be changed, or you will be prompted to sign- in case it is not done in the first step when opening the Power BI Desktop. Now click on connect.
7. Once the Power BI desktop is connected to the OData URL provided, it lists all the tables available from the dynamics365 application.
8. Select the required tables from the list and click on “Load.” Now the Power BI desktop application loads the table and all the columns within it on the right side under Fields section.
9. Select a graph pattern from the visualization section by clicking on it. Drag and drop the fields into Axis and values section. The graph gets updated based on the fields and values selected for the visualization.
10.                      Now Click on Publish. You will be prompted to save the report if it is unsaved. Once you save the report, it gets published to power BI.
11.                      Sign to Power BI online with the same credentials used for Power BI desktop. Navigate to the Reports tab under My workspaces, and the published report could be seen. Click on the report published from Power BI desktop. Once it opens, click on the Pin Visual option.
12.                      A window opens, asking if the report has to add to an existing dashboard or a new dashboard. Select New Dashboard and provide the name for it. Click on the Pin button.
13.                      Navigate to dashboards under My workspaces, and the newly created dashboard is available. Click on the SalesLine Analysis dashboard once it is available in the Power BI online dashboards, Open Dynamics 365, for operations.
14.                      Create a new Workspace by right click > Add Workspace. Provide a name for the New Workspace.
15.                      Now click on the Workspace “Power BI Report Space” that is created.
16.                      Now for the power BI control to be available in the workspace, navigate to the below path in the workspace created. Options> personalize this form> click on + sign> Allow Power BI control> click OK.
17.                      You can see a Power BI Tile in the workspace. Click on the “Get Started” option. It opens a Tile Catalog, showing all the Power BI dashboards that can be used in dynamics 365.
18.                      Now select the dashboard by clicking the title. Now click on the report, so it is Ticked and Click on OK. The dashboard selected is now displayed in the New Workspace created. You can add multiple graphs/reports to the same dashboard.

You can use this tool to analyze and create quick reports and remove the performance bottlenecks to improve the performance of the whole application.

These reports are not only rich and interactive and but also users can make changes without having to rely on another person. Users can pin reports to workspaces themselves.