Power BI and Microsoft Dynamics NAV 2016

Hello and welcome to The Coffee Mug Tutorials. Today I wanted to go through Power BI. Power BI is a fantastic product from Microsoft. It’s relatively young, I think it’s just over a year old however it has been progressing fast and we are actively connecting that up to NAV for very nice dashboards and all kinds of manipulation of data.

There are many ways to connect Power BI and I want to go through sort of the simplest way to connect it up to NAV and I want to show you what can be done quickly.

So right now I’m looking at my Inventory screen and I see here I have my Items, sort of test items that I have here...or I’m sorry, actual items, and then I have my Quantity on Hand and I got the Item Category which is filled out for some, not all.

And what I want to do is actually bring over the Item list into Power BI with the Quantity on Hand. Now Quantity on Hand is actually a calculation from the Item Ledger Entries so if I click on Quantity on Hand I get the Item Ledger Entries, and it’s really a sum of the field here - Remaining Quantity. Either way, when you sum up the Quantity or Remaining Quantity you should get the same.

So for Power BI when we’re setting things up we have to think about the Item Table is what we want and we also want the Item Ledger Entry table.

So let’s fire up Power BI and I am running right now the desktop version which you can download for free on Microsoft’s website. So you can just Bing it and you can find the link.

Now I’m going to go in here and say Get Data SQL Server. So I can get data from a lot of different sources but normally NAV is running on SQL server so I can connect straight to the SQL server. That has some benefits and also some not-good things as well. I’ll explain that a little bit as I go through it.

So the server that I’m looking for is just a local host server, I’m actually running it locally. And I’m going to pick the NAV 2016 demo database; you would obviously put in your server and your database here. Then it asks me how I want to connect...oh actually it just connects, it’s happy with the credentials.

Now here we get a lot of different tables and you can see I have several companies in my database. And I end up with 7533 tables which is a lot. And you can see here some of them have this ‘vs’ or ‘vsift’ - those are calculated fields and they are really not useful for the day-to-day operations for us querying.

What I can do is just type in here - Item - and then it will filter out just the Item tables and what I want to do is go to the coffee mug, and I want to pick out the Item table, which is right here so it’s Coffee Mug International, dollar sign, item, which is the item table.

And remember I also need another table which is called the Item Ledger Entry and we are going to just look for that one. That should be right here - Item Ledger Entry, check that off, and then I just hit Load. Alright. So it brings in all of the table, or those two tables, and now you can see on the right hand side, I have...let’s just make this a little bit bigger...I have the Coffee Mug International Item with all of its fields and then the Coffee Mug International Item Ledger Entry with everything.

Ok so what we want to do right now is display information from this. I’m going to start by showing you how quickly you can actually display data. If I go into the Item I can just go ahead and find the Number field which is somewhere here and notice it will start rendering out the number.

Then I can go ahead and try to find, for example, the Description which is right here. When I have that, how about the Item Category so Item Category Code which is right here. Now you can see that I have all my Items, Item Category Code. I can sort by Item Category Code and I got a bunch of things there.

Ok so what we want to do...let me just sort it again like this...is calculate the quantity available. Now showing the quantity available, that’s in a subtable, you are not going to find that in here in this table, so what we’re going to do is actually connect the two tables.

So I go here into Manage Relationships. I haven’t defined any relationships, Ok, so we’re going to create a new one. And what are we going to connect? We’re going to connect the Item, the Number to the Item Ledger Entry Item Number, so it’s one to many and I’m connecting the Item Ledger Entries to the Item Number, I’m sorry, the Item Number to the Item Number field in Item Ledger Entries.

And I’ll activate that and now I close. You can also see this in here and now it’s connected in a little bit of a graphical way. I actually like the other way better because it tells me what it’s hooking up to, here I kind of have to guess, anyways, let me go back to the report. And now if I go ahead and select the Item Ledger Entry and let’s go in here and look for something called Remaining Quantity, I get the remaining quantity of all of the items which is kind of nice.

If I had not connected the two together the remaining quantity would be totals for all items in all items so that would not have worked.

And now I can take this and change it into different formats like that. You can see what has the highest quantities. I can also just take out the Item Number and Description, take that out and then I just have the Item Category, let’s see where is the Number...right here. And now I can see how we’re doing. And then I can see we have Produce has 22,100... probably pounds.

Got some coffee in here. And then I really have to do some work in categorizing my items because I have a lot of items that have no category. So this is how you can start playing around with Power BI through NAV. As you can see it’s very simple and I’ll probably be posting more videos on this, but at least this is a good start. I hope you enjoyed it.

Thanks for taking the time to watch this video. As always we welcome any questions or suggestions, so leave comments or of course if you can Subscribe that would be awesome. We are trying to build our fanbase over here at Inecta and if you want to look for further information please go to Inecta.com.