Category: PowerBI.com

If you have not seen please check it out it will help you continuing with this post.

You can check:

Microsoft Power BI – Part – I

Microsoft Power BI – Part – II

If you remember in our first post of this series we have downloaded an installed 2 Component/Tool

Today we will discuss and explore Power BI Desktop. It allows you to create the reports from in premise database and create visuals as we saw in our previous posts.

When you launch Power BI Desktop application opens as below:

Which more or less looks like one we saw online PowerBI.com.

Let’s try building one simple report using this tool.

Step-1 We will require some services to be published before we can access data from our in premise database.

For my example I have published two pages Item Card & Vendor Card as a Web Service.

We will use OData URL to fetch data.

Step-2 Launch Power BI Desktop

Step-3 Connect our Data source to create our reports.

Select Get Data from the Home Screen.

Copy the OData URL from the Web Service Page, in my case: http://INDEL-AXT5283N1.TecturaCorp.net:8048/DynamicsNAV80/OData/Company(‘CRONUS%20International%20Ltd.’)/Item_Service

Please take up to Company Name no Service Name required, it will list you all the services exposed from your database installation. In above remove the service name Item_Service from the URL.

On selecting OK you will get list of all Services available in form of Single Query.

If you notice sometimes you may not get the latest list of services available, reason the connection is not refreshed. See the highlighted part in above screen.

Refresh the connection to get latest list of services available.

In above screen show us the currently available services which we can use for creating our report/ visuals.

Step-4 Add available tables to our Dataset.

Right Click on the Table Link and choose “Add as New Query”

Once you add you can find Table Query in the Left Navigation Bar.

You can Browse the Table from the Left hand Navigation Bar and change its Name if required in the Right side “Query Settings”. On the Top of the Query you may find information if available data is old, you can Refresh it to get the updated one.

Here you can perform other operations too to tweak the table as per the requirement.

You can Add new columns deriving data from existing columns using formula and other available methods. You can drop unnecessary columns etc. We will look into same in future posts.

For now we will remove some additional columns which got added due to service.

Select the column Right Click and choose Remove.

Finally we will remove the Source Query created after connecting to OData, Right Click to the Query and choose Delete.

As a Final to this Step we will choose Close & Apply from the Top Left Corner Tool Bar.

You will be returned to main Report Builder Window.

This screen is similar to one we saw while Online Power BI window.

Step-5 We will build Relation between our tables added above.

Let us build the Relation between the Tables we selected.

To define the Relationship Select Relationship view from the Left Navigation Bar.

You will see both the Tables in the Relation Window.

To define relation Click on Vendor No field in Item Table and Drop on No field of Vendor Table.

You will see Many to One Relationship is established between these two Tables. For now we will use this only and come in more details of Managing Relationship in future posts.

Step-6 Create our Report/ Visuals

Return to Report Canvas and add few Fields as we did while our First report using Online Power BI.

For my example I have added Item(No, Inventory, Vendor No, Vendor Item No) Vendor(Name, Location, City, Country Region Code).

Rest I leave up to you how you further categorize and Visualize the report. My objective was to demonstrate how you can create Reports in BI Desktop Tool using your in premise Navision Database installation.

Step-7 Save my Report

Save the Report *.pbix which we can access to modify report in future if required.

Step-8 Login to PowerBI Online

You will require credentials to your PowerBI online to connect.

Provide your credentials and Sign in.

Once you successfully sign in to your account you can now proceed with next step.

Step-9 Publish the Report to Online Power BI

Once your Report is published successfully you will get below screen

Step-10 Verifying the Report on PowerBI.com

Sign in using your credentials to PowerBI.com

Great, the report we published from BI Desktop is now available Online.

That’s all for today.

Truth is we have created the Report using in premise database, it should be in sync with latest data from our database. But this will not at this moment.

In our next post we will work on how to keep the data of this dataset in sync with the data from our in premise database.

Till then keep exploring and learning new features available with Power BI.

 

Dear Friends I will be continue from my previous post where we left. If you have not seen please check it out it will help you continuing with this post. You can check here.

Let’s save the report we created in previous post and continue their after exploring more features.

Create a Dashboard and Pin our Visuals.

To create a Dashboard either you can use below any of the option.

Once you Pin your Visuals to you Existing or New Dashboard, upon accessing the same it should appear as below.

Ask a question

On your dashboard you may find “Ask a question about the data on this dashboard” let us understand this feature.

Here you can ask to analyse the data present on dashboard using simple English language include some column names or data values that are present. I will take as the filter for those value and present you the analysed data.

Let’s take an example I want to know all the customer who are associated with Red Location.

See I have entered Red in Question to Ask, it suggested me what valid question I can ask “Show Red customer lists” not only this it has analysed the data present on my dashboard and presented me the list of customers who are associated with RED Location.

Good feature and powerful too.

Filtering data across Visuals

Let’s add one more Visual on our Report, for this we edit our Existing Report.

Select your report and click on Edit report

We select Credit Limit, Currency Code and No. field.

In value we will select “Count” for No. field. How to do it we have discussed in our previous post.

From Visualization we will select “Funnel”

Position the Chart at desired place available on your canvas.

Save the Report.

Now open the Report to View.

Click on the Funnel Chart on Currency Code = ISK.

Since all the tables/Visuals on this canvas share same data source/ dataset the data gets filtered.

See other Visuals are also showing the effect of same.

On Card Visual it Shows 3 Customers are which shares Currency Code = ISK.

On Treemap it shows these 3 Customer belongs to Yellow Location.

Yet another powerful feature for analysing the data.

Let’s add all of the above Visuals to our dashboard and arrange as below.

How to Pin the Visuals we have discussed above.

 

That’s all for today. We will learn and explore some more features in our upcoming posts.