Power BI can be used to build reports adhering to the business requirements. With Power BI, user can:

• Connect to a variety of data sources.
• Can bring in the data from these sources and transform the data which matches the requirement or prep it for further analysis using tools like Power Pivot or Power view.
• Create custom views over the transformed data.
• Perform data cleansing/modelling/integration operations.
• Data can be imported from multiple log files.
• Create a visualization of data using numerous graphical designs that are already available.

You will mainly focus on connecting to the SQL database, generating required reports along with visualizations and mailing of reports to specific groups or individuals.

Power BI can be downloaded from the following link . The link also has other requirements that specify the software requirements for installation.

1. After installation, the Power BI Desktop icon appears on the desktop.
2. Sign in needs to be done.
3. Power BI account needs to be created if not already present.
4. After providing the correct credentials, sign-in is successful.

Getting Started with Power BI

You will consider the SQL database as your source.

There are mainly six parts to it –

• Connect to the database
• Data Load
• Data Refresh
• Report Generation using visualization
• Publishing the report
• Subscription and mailing of the report.

Connect to the database

Click on the ‘Get Data’ tab and a pop up showing all the data sources come up.

Click on the ‘Database’ option, choose the SQL Server database, and click on connect.

Provide the required SERVER (SID/database name) and QUERY in the highlighted text box.

Then provide the credentials of the database you want to connect in the highlighted text box.

Data Load

The user can see the data in a popup and load the data into the desired window.

Data Refresh

Then change the source to some other environment and edit the permissions by giving the credentials.

Select the report tab from the left. Select the objects from the right-side ‘Fields’ panel based on which the report needs to be generated.

Select the type of visualizations. One can also view the details for a particular object from the graph, which depicts that the report is user-friendly and interactive as well.

Once the report creation step is complete, just use the Publish button present in the Home tab to start the process.

The data and report, including all the queries, visualizations, and other features used are combined together in a package. All of them are then uploaded to the Power BI service.

As soon as the upload gets completed, a dialog box will open, which mentions that the publishing is completed. Also, there will be a link present in the dialog box, which helps in navigating to the report directly inside your web browser.

Report Sharing

The report can be directly shared with an individual or a certain group. Just fill in the details (Email Id and any optional message) and then Click on the share button.

1. Open the dashboard or report.
2. From the menu bar, Select SUBSCRIBE or select the envelope icon (present on the right side).
3. The yellow slider can be used to turn the subscription on and off. The trashcan icon can be used to delete the subscription entirely.
4. You can fill in the message details. The current mail id that you are using is pre-populated, but you can add others to the subscription as well.

NOTE: Email addresses in the same domain can be added.

For creating a Power BI Desktop report with AX 2012 R2, one needs to follow the below steps.

Before you proceed with report creation, make sure that the correct version of Power BI Desktop is installed based on the machine’s configuration, either 32 or 64 bit.

1. First of all, go to AX -> Organization administration.
2. Then navigate to Setup -> Document management.
3. Finally, move to Document data sources. Open document data source form.

Document data source form fetches data from three services such as Service, Query reference, and Custom query from AX and which can be displayed in reports at the end.

Here you will create a new document data source based on the required data from the related query.

Now that you have added an existing query of AOT “ CustTable ” as Query reference as the data source type and Customer Details as description.

Once the required query is selected and added on document data source form as above, go to browser/internet explorer and enter the URL of the AX Odata query service. Make sure this URL is running and displaying the XML code associated with it. This verifies the Q data query service is running fine from the AX perspective without any issue.

One can verify against query wise AX Odata query service URL by running it in the browser.

Once the AX Odata query service is successfully verified on IE, the next stage will cover report design steps in Power BI Desktop as below.

Open Power BI Desktop. Now click on Get Data option. This will open a screen which contains various available source data file supported by Power BI.

As you need to get data from AX using query service, you will select Odata Feed from the list and click on connect. On the next screen, it will ask to provide the URL of the AX Odata query service.

Once URL is entered on clicking OK, it will fetch and display all the query service registered in the AX document data source form.

Here you will select CustTable Odata feed as a data source. In the right side window, one can preview the data populated from the query. Once the required query is selected, click on the Load button.

Once data is loaded, it will appear in the Power BI report designer window.

Now, based on the selected visualization-basically report format like Table, Matrix, Pie chart, BAR Chart- you can display the data in the report. Kindly note here you can make various field related and report format related changes in detail as and when required.

Once data is added, you can see it with a preview page.

Also, you can publish this report on the cloud if you have azure subscription active, which is provided at an organization level.

Below are a few other types of reports created as a sample.

• Pie chart report - Currency wise Amount.
• Donut Chart report – Customer group-wise amount.

Using the Power BI, you can develop the reports with NAV 2016 data with pre-defined visualizations. You can easily retrieve the data from NAV and show it in reports. Before you develop the Power BI report with NAV 2016, you should verify the version of Power BI. Power BI version should be the same as OS configuration like 32 or 64 bit.

Steps to Develop a Power BI report with NAV 2016:

• Configure the Web service in NAV 2016.
• Configure the NAV 2016 Web service in Power BI.
• Creating visualization with NAV 2016 data using Web service.

Configure the Web service in NAV 2016:

1. Open the NAV 2016, then go the below path to configure the Web service.

Administration/IT Administration/Services

2. From here, open the Web services page. This page contains base web services.
3. By clicking the New button in the action page, you can create new web service with object type: Page
4. Add the below details to the page to create the Web service.

• Object Type: Page
• Object ID: 38
• Object Name: It will populate automatically
• Service Name: Any meaningful name
• Published: check this flag to update OData and SOAP URL’s
• OData URL: It will update automatically
• SOAP URL: It will update automatically

Configure the NAV 2016 Web service in Power BI.

1. Open the Power BI Desktop to start the configuration of the NAV web service.
2. Select the OData Feed from the Get Data option.
3. Now enter the NAV OData web service URL in the OData Feed and click on Ok.
4. You have to give authentication on Access an OData feed window as shown below then click on the connect button. Here you can give two types; one is present windows credentials or any other alternate credentials like different users' credentials.
5. Now Power BI retrieves all the data from Item Ledger Entry table and display them on the screen. Click Load .
6. Now Query will be added to the Power BI with all the fields from the Item Ledger Entry table.
7. From the Query, you can select the fields to get the data in the Power BI report. (Here I selected Item No., Location Code, Document No., Quantity, and Document Type.
8. You can see the Power BI report with selected fields.
9. You can change the font size also. You can add the filters to the Power BI report.
10.                      Now you have the final Power BI report with NAV data after using the filters and table style.

Creating Visualization with NAV 2016 Data using Web Service:

Using Power BI, you can create pre-defined visualizations like Stacked Column chart, Line Chart, Stacked area chart, Pie chart. Now you will create Item quantity Location wise.

Below are the steps to create charts using NAV data

1. Firstly select any chart type from visualizations.
2. Here you have to assign fields to X-Axis, Y-Axis, and values to be shown in the chart. Select Item No from Fields; it will move to under Axis. Now select the Location Code, it will move under Legend then select the quantity. (Here Axis represents X-Axis, Legend represents Y-Axis)
3. After selecting the required fields, the chart will be shown below. The X-Axis represents Item No., and Y-Axis represents Quantity.

Create and design a Microsoft Power BI Report using data source as SQL Server. You can use the Power BI report to manipulate the data and virtualize in many graphical and table representations.

You need a minimum version of SQL Server is SQL Server2005 and above. Consider the Power BI desktop is installed and configured to allow Power BI reports in the system.

Steps to create Power BI reports using SQL Server

1. Open the Power BI desktop application and click on the sign-in button.
2. Provide the User Id and password, which has power BI access, and click sign in to access the Power BI desktop.
3. You will be able to see the Power BI Screen after successful login.
4. In the Power BI desktop, click on the “Get Data” option in the action pane and then click SQL Server from the options listed.
5. A window will pop up, enter server and database details, and select data connectivity mode.

• If you want to use existing tables as a data source, select Import as data connectivity mode.
• If you want to write your own query instead of selecting tables, you can opt DirectQuery option as data connectivity mode. Here you are using the Import option as data connectivity mode.
• Select data connectivity mode as per your requirement and click on OK.

6. Once the Power BI desktop is connected to the SQL Server, it lists all the available tables. Select tables that are required to design reports and click on the Load button.

• Now the Power BI desktop application loads the table and all the columns within it on the right side under Fields section.
• 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.
• You can add multiple graph patterns for data visualization in your reports.
• Now save the report and click on Publish.
• You will get Success popup, once the report gets published to power BI.
• To check the published report, you need to sign in to Power BI online with the same credentials used for Power BI desktop and go to the Reports tab under “My workspaces,” and the published report could be seen here.

You can use this application to create and design quick reports with various gateways to get data. Directly you can access the SQL Server data into this application to get data. Manipulate with data and get the expected result to display in the report and design as required with the predefined graphs and formats.

Components of Power BI

Power BI QnA

Sometimes the fastest way to get an answer from your data is to ask a question using natural language. In this quickstart, we'll look at two different ways of creating the same visualization: first, building it in a report and, second, asking a question with Q&A. We'll use Power BI service, but the process is almost identical using Power BI Desktop.

Query and Data Management

Having a considerable amount of Pros, Power BI still has lots of challenges when to be implemented in small applications or accounts.

E.g.:

a) It’s still not the most powerful tool to handle the bulk data.

b) It has lots of in tool options hence making its learning a bit complex.

c) It is not a freeware and requires an Individual account cost, based on no. of persons using it.

Solution: Well, the possible solution to attain all the great features Power BI provides is to use the Excel embedded Power BI functions using the add-ins.

Power Bi Implementation over Excel Through Plugins

Below are the add-ons which you can use to attain the corresponding Power Bi Desktop functions:

For instance, you can use Power Pivot Plugins to attain Power BI Sites functionalities.

Power Query can be used to get equivalent features to Power BI Q and A and So on,

How to Enable these Power Plugins

Now, before adding the plugin, they must be downloaded and installed:

Power Map:

Power Query:

Power Pivot :

Once The Add-Ins have been installed. There are several steps to be followed to enable the plugins over Excel 2013

1. Go to  File  >  Options  >  Add-Ins .
2. In the  Manage  box, click  COM Add-ins >  Go .
3. Check the Plugins which you want to Enable and click “OK.”

Once Done, Notice that you will get a Power Pivot option over the Excel Tabs.

If you see carefully, you now have the option of Power Pivot with features such as

a) Pivot Chart

b) Power Map

c) Power View

d) Power Query

Here you will understand Power-BI charts and dashboard. You will get to know some of the tips in sorting month names and displaying data in the geographical charts to show distribution. And it also provides an insight on configuring schedule refresh to update chart periodically.

You intend to work on Power-BI charts and sorting month names based on month sequence and geographically representing data to show distribution way.

Before you perform the steps, make sure that your environment meets the following prerequisites:

• Installed Power-BI desktop in your machine ( )
• Have Power-BI access to share reports

Sorting Month Name

The month name column is a string column. If you add month name to any of the filters like “Filter” or “Bar Chart,” then the month names will be sorted based on alphabets. But as a result, you expect month names should order by months which comes first.

Steps to Follow

1. If you have a data column to pull data from, then create two columns, Month and Month Name directly in Power-BI. To get options to pull Month and Name, you must select the date column and then click on add column tab. These operations must perform on “Edit Queries  Edit Queries.”
2. Once you created new columns with the name “Month” and “Month Name,” replace the “Month” column name with “Month Number” and “Month Name” with “Month.” And close edit queries window and apply changes.
3. Create a filtered chart for testing and add a Month column for filtering the data.
4. Now, select the Month column from rightmost columns and select Sorting by column as “Month Number.” This operation performs the sorting of month column based on month number.
5. After updating the sorting, the filter chart that you created earlier represents data in the way months come.

Showing Data in a Geographical Way

If your data is having geographical information like country, latitude, and longitude, then a chart can be displayed with few clicks to show information based on geographical location.

Steps to Follow

1. In Power-BI desktop application from Visualization section on the left select “Map” chart.
2. After adding a chart to the page, you will see the properties of the map chart below the charts. Add location property with location related column from the data set, Size property with the data column like sales, count, etc. Below are the sample property values. If your data set has only longitude and latitude, then add those properties and leave location property blank.
3. After adding the above properties, the data will be represented on the maps based on the geographical location and the size.
4. To represent the data based on a specific column, then the column should be added to the “Legend” property. After adding legend property to the chart, each circle will represent data based on legend column property.

Schedule Refreshing Data on Power BI Dashboard

Power-BI web site provides different operations to show reports on the dashboard and sharing with different groups of users, both internal and external organization users. And also, the most important is the data on the dashboard to refresh periodically so that the end-users see always updated information.

Steps to Follow

1. Connect to your power-BI site.
2. Select appropriate data set from the available data sets.
3. Click on the … on the left side of the data set, and it shows available properties.
4. Click on “Refresh Now” if you want to do manually.
5. Click on “Schedule Refresh” to schedule the data refresh periodically. This property allows refreshing the data based on time zone at a time, either daily or weekly. And it required authentication for the data sources.
6. Set credentials under “data source credentials” to access the data source. If there are any issues in the connectivity, that error will be listed there. To schedule an auto-refresh, there should not be any authentication issue.
7. Click on “Schedule Refresh” and provide “Refresh frequency” either daily or weekly. Specify “Time Zone” based on your time zone. Provide Time only if required else can be left empty or can be removed. By default, the time will be 12:00 AM.
8. After setting all the required properties, then click on “Apply.”
9. Now data refresh happens daily or weekly based on the setting automatically.

So this is how you can schedule data refreshing on the Power BI dashboard automatically on a daily or weekly basis. This saves a lot of time and manual effort.