PowerBI.com

Microsoft Power BI – Part – VI

Continuing from my previous post, if you have not seen yet 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

Microsoft Power BI – Part – III

Microsoft Power BI – Part – IV

Microsoft Power BI – Part – V

Today we will discuss how to use Power BI Content Pack:

PowerBI-60

Few things to keep in mind to get report Generated Successfully.

Point-1 You have required objects for this content pack in your database.

If you are using 2016, you can skip this as these objects are available in 2016. If you are using 2015 or 2013 then export these objects from 2016 and import in your database.

PowerBI-61

Above Pages will be present in your previous version of database, you need to import the 6 Query objects. These are necessary as these are used as source to fetch data from your data base by this content pack.

Point-2 Web Services need to be published.

PowerBI-62

Make sure you use same name for Services as you may get error while connecting and refreshing/ fetching data using this content pack.

Point-3 SSL port and web address is used to access the services.

https is used as this will not work on http.

Self-signed certificate will not work which we use for test purpose for publishing services and testing. It will throw Trusted relationship connection type error. If you have one use the same instead using Self-signed certificate.

No worries, don’t be disappointed. I will give you tip to overcome this requirement just follow as below:

You may remember that in our previous post we installed and used Power BI Gateway, actually we got a Trusted Certificate which we can use for this purpose.

PowerBI-63

Open IIS Manager on your system.

PowerBI-64

Select Serve/ Machine in Left Navigation Tree and Select Server Certificates.

PowerBI-65

Locate the Data Management Gateway SSL Certificate, this got installed when you installed Gateway. Double click to open the Properties of the certificate.

PowerBI-66

Switch to Details Tab, Scroll down to find Thumbprint, and Copy the Thumbprint and paste to Notepad finally remove all space leading, trailing and any in between.

It should look like above right bottom corner notepad window no space in Thumbprint signature.

Copy this Value and use as Thumbprint in you Navision Service.

PowerBI-67

Select Your Service.

Edit.

Paste the Certificate Thumbprint.

Make Sure SSL is Enabled for your OData Services.

Save.

Restart the Service.

We are done with the basic requirements now let us connect using Power BI Miscrosoft Navision Content Pack.

Now Login with your credentials to Power BI.

PowerBI-68

From Left bottom corner Select Get Data.

PowerBI-69

Select Get Data from Services.

PowerBI-70

Scroll to find Microsoft Dynamics NAV Services and Select Connect.

PowerBI-71

Copy your OData Service URL. Make sure you remove Service name from the URL and trailing ‘/’ after company name from the URL.

Select Next.

PowerBI-72

Select the Authentication Method and then Sign In.

PowerBI-73

Wait for few minutes you dataset will be loaded.

As a result you will get: Dataset, Report & Dashboard ready for you in few minutes depending upon the data volume and connection speed.

PowerBI-74

The report and dashboard view will be as per the available data in your database.

Hurray!!!

You are done with your practice session.

This is a Holi Gift for my reader from my side.

I will come up with more features explanation and other Tips & Tricks in my upcoming posts, till then keep practicing and learning from blogs and helps available online.

 

PowerBI.com

Microsoft Power BI – Part – V

Continuing from my previous post, if you have not seen yet 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

Microsoft Power BI – Part – III

Microsoft Power BI – Part – IV

Today we will discuss how we can schedule refresh for Dataset for Report which we published in our previous post.

We can schedule Refresh for our database which is in our premise.

In Previous post we saw how to enable Gateway to refresh the dataset. This Gateway must be running else we won’t be able to refresh our data from our database. We can Schedule the Refresh but however we can refresh the same whenever required as we saw in our previous post.

It very easy to get it done.

Step -1 Run your Gateway on the server so that Online Power BI can communicate with your server and request for updated data. This should be kept Running All the time just live any service keep running on your Server.

Step-2 Login to Online Power BI at PowerBI.com, using your credentials.

Step-3 Configure Refresh Schedule

For Step 1 & 2 please refer to earlier posts in this series, Link on top of this post.

We will discuss Step-3 here.

Once you are Login to the system, Locate the Dataset you published in our previous post.

PowerBI-57

Click on the three dots next to the Dataset name & choose Schedule Refresh from the resulting Menu.

PowerBI-58

Here the required information is self-explanatory, fill in the details as per the requirement and Apply.

Make some data changes in your database and test if it reflects in your Report.

Meet you again via my next Post soon.

Till then keep practicing and learning about Power BI through available Blogs, Help available online with PowerBI.com.

 

PowerBI.com

Microsoft Power BI – Part – IV

Continuing from my previous post, if you have not seen yet 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

Microsoft Power BI – Part – III

PowerBI-49

In our previous post we publish Report from our BI Desktop which uses data from our in premise database. To keep our data sync to our report we will require provision via which our report is connected to our database.

 Gateway will be useful for this purpose.

Let me make you clear the data update to report is not real time in fact it is scheduled but can refresh anytime to get updated data on the report.

 

PowerBI-50

When you launch Power BI Gateway, you get above home screen. Login with your credentials.

PowerBI-51

If you are doing it for first time it will ask you to define the name for your Gateway as in my case it is Ash_TecturaGateway.

PowerBI-52

Make sure you don’t close this window else you will not be able to sync your data. This should remain connected all the time.

Minimize this window and keep it running, preferred this should be on the server itself, as local machines can be closed and accidently this may get closed.

Once you are done, Next step will be to add this Gateway to your Online BI account, so that it can be used to sync your data, as scheduled or when required.

Sign to your account using your credentials to tour online BI on PowerBI.com.

PowerBI-53

From settings choose Manage Gateways.

PowerBI-54

Provide the name that you provided in Power BI Gateway on your local machine.

Provide Data Source type as OData

Provide same URL one you provided to your Report in Power BI Desktop.

Key in the Credential Type as in my case I am using Windows.

Provide your credentials for the connection.

Once you are done it will show you the status, both the screen should be Online, You are good to go.

Additionally you can add users and Administrators to whom you wish to grant access to publish and manage reports using this Data Source.

Now your data source and Gateway is configured for sync.

PowerBI-55

This shows your Gateway is connected and now your data source can be synced with the in premise database.

Now let’s check quickly if our connection is working.

PowerBI-56

From your Left Navigation Pan, Select 3 dots next to your Data Source, and from resulting menu choose Refresh Now.

If there is no connection issue or Credentials issue your data will be refreshed, else you need to ensure there is no gap in connection.

I will come up with more details and how to schedule Refresh in my upcoming post.

Till then keep practicing and learn more using online help and Blogs available on PowerBI.com site itself.

 

 

PowerBI.com

Microsoft Power BI – Part – III

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

PowerBI-25

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:

PowerBI-26

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.

PowerBI-27

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

PowerBI-28

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

Select Get Data from the Home Screen.

PowerBI-29

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.

PowerBI-30

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

PowerBI-31

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.

PowerBI-32

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.

PowerBI-33

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

PowerBI-34

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

PowerBI-35

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.

PowerBI-36

Select the column Right Click and choose Remove.

PowerBI-37

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

PowerBI-38

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

PowerBI-39

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.

PowerBI-40

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.

PowerBI-41

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

PowerBI-42

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

Step-8 Login to PowerBI Online

PowerBI-43

You will require credentials to your PowerBI online to connect.

PowerBI-44

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

PowerBI-45

Once your Report is published successfully you will get below screen

PowerBI-46

Step-10 Verifying the Report on PowerBI.com

PowerBI-47

Sign in using your credentials to PowerBI.com

PowerBI-48

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.

 

PowerBI.com

Microsoft Power BI – Part – II

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.

PowerBI-15

Create a Dashboard and Pin our Visuals.

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

PowerBI-16

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

PowerBI-17

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.

PowerBI-18

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.

PowerBI-19

Select your report and click on Edit report

PowerBI-20

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.

PowerBI-21

Now open the Report to View.

PowerBI-22

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.

PowerBI-23

 

PowerBI-24

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

 

Corfu Navision 2016, Development Tips, Dynamics Content Package, How To, PowerBI.com

PowerBI.com content package for Microsoft Dynamics NAV 2016

Microsoft Dynamics NAV 2016 includes an entirely new integration with Microsoft Power BI.

In Microsoft Dynamics NAV 2016, using the new Power BI Content Pack, Microsoft Dynamics NAV end users can connect a correctly configured Microsoft Dynamics NAV instance to PowerBI.com and immediately be able to see full business performance insights in a dashboard.

The PowerBI.com content package for Microsoft Dynamics NAV 2016 empowers you to share and present data across an organization. Data can be defined centrally but be consumed, modified and presented to the local needs of users, while everybody still sees data from the same overall picture.

Benefits:

  • Business performance dashboard  Data from Microsoft Dynamics NAV is presented in an easy-to-use dashboard, providing a complete overview and important insights
  • Finance and sales insights  Users can drill into more details in the sales and finance reports, facilitating decision making
  • Personalizing the insights  Users can create a personalized dashboard from the existing KPIs and charts in reports
  • Modify and extend With this package, users can start using and exploring their own data using Power BI in just a few minutes. The dashboard gives users KPIs and charts on their sales and finance data. The setup consists of three easy steps:

Users can create their own KPIs and charts, select data from queries, set filters, select visualizations and more to create their own dashboards

  • Sign in to PowerBI.com.
  • Select the content package for Microsoft Dynamics NAV.
  • Connect a Microsoft Dynamics NAV instance to the content package. Today I will discuss about below:

It takes less than five minutes to get going! Depends upon net speed and volume of data.

Preparation:
PowerBI-1

Make sure OData Service is enabled and Port for same is defined in your Server Instance of Navision. Also you use valid thumbprint and SSL is enabled.
PowerBI-2

Check that the Page 197 Service powerbifinance is published in Web Services window. Copy the OData URL we will be requiring it while connecting to content pack for Dynamics 2016 in BI.

 

Go to PowerBI.com and sign in or go through the easy account setup process.
PowerBI-3

Sign in to Power BI using your credentials.

You first get to a page to select your data source.

The data sources are OData feeds, such as OData web services from any Microsoft Dynamics NAV solution.

  • To import your Microsoft Dynamics NAV data into Power BI, you need to have permissions to the tables where data is retrieved from, in this case sales and finance data. You sign in your account credentials.
  • The Power BI Microsoft Dynamics NAV content pack uses Microsoft Dynamics NAV’s web services to retrieve your data. If you have a lot of data in your Microsoft Dynamics NAV instance, a suggestion to minimize the impact on your web service usage is to change the refresh frequency from the current default which is once a day to only refresh on weekdays or every other day depending on your needs. Another suggestion is to have one admin create the content pack and share it with the rest of the team instead of having every admin in your organization create their own

In this case select Services to get to content packages. Refer below how to connect.
PowerBI-4

Select the content package for Microsoft Dynamics NAV.
PowerBI-5

You will be prompted to add the URL to your Dynamics NAV instance. 

An easy way to obtain this URL is in Dynamics NAV to go to Web Services, find the powerbifinance web service and copy the Odata URL, but leaving out the “/powerbifinance” from the URL string. 

When you paste in the copied URL, you must remove the last part of the web service-specific URL: “/powerbifiance” – you only need the company-specific part of it

After entering the URL You will be prompted to select an authentication method. 

Select the appropriate method and provide the relevant user credentials.
PowerBI-6

Here you provide the OData URL we saved above. Enter your Authentication Mode and provide your User ID and password to get connected to the data.

When you select Log on, it will take a few minutes for the content package to connect to your Dynamics NAV instance, get the data and create the dashboard and reports.

Troubleshooting

 

“Parameter validation failed, please make sure all parameters are valid”

If you see this error after typing your Microsoft Dynamics NAV URL. Make sure the following requirements are satisfied:

– The URL follows exactly this pattern https//instance.navserver.com:7048/DynamicsNAV90_Instance1/OData/Company(‘CRONUS%20International%20Ltd.’) replacing ” instance.navserver.com” with your NAV Server name, “DynamicsNAV90_Instance1” with your NAV Server Instance name and “Company(‘CRONUS%20International%20Ltd.’)” with your NAV Company name.

– Make sure all the letters are lower case.

– Make sure the URL is in ‘https’.

– Make sure there are no trailing forward slash at the end of the URL.

 

“Login failed”

If you get a “login failed” error after using your Microsoft Dynamics NAV credentials to login, then the account you are using doesn’t have permissions to retrieve the Microsoft Dynamics NAV data from your account. Verify it is an admin account and try again.

Let’s see what data exactly this page provides:
PowerBI-7

Depending on whether you use your own database or the demo database, the charts in the dashboard might have different content or none at all.

 New Post with details available, please check out : https://msdynamicsnavashwinitripathi.wordpress.com/2016/03/23/microsoft-power-bi-part-vi/

 

We will see rest details in my upcoming post.