Tag: MS

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.

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

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.

 

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

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.

 

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

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.

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.

From settings choose Manage Gateways.

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.

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.

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.

 

 

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.