Dynamics 365 Business Central: Microsoft Stack , Power BI

Dynamics 365 Business Central: Microsoft Stack, Power BI

We're keeping on the path of Power BI and Business Central and the whole Microsoft stack, I want to change the diagram a little bit. We have the cloud like this, I'm going to say that the Microsoft stack is in the cloud, some of it is in the cloud and some of this outside the cloud, before, I kind of connected everything to the cloud like the cloud was connecting the data together but this time around, I'm basically going to say that we have BC inside the cloud, all right, and then we have  Power BI inside the cloud. So they are connecting together inside the cloud, the reason why I'm doing this is because we have something outside the cloud, here, which is Power BI desktop.

The desktop engine is outside the cloud, it's going to be on your desktop and it's going to communicate with Power BI inside the cloud, which communicates with Business Central inside the cloud. So, this is an important distinction, whether you're inside the cloud or outside the cloud, now this all is Microsoft stack, whether you're inside the cloud outside the cloud. This is Microsoft Stack inside the cloud, Microsoft Stack outside the cloud.

Alright, what we're going to do today is, we’re going to work with tables and you're going to see it in the app, we wanted to connect two tables together. In this case we're not working with tables, even though you're probably used to tables if you work with reporting or something like that before, we are you using web services. It's called web service or web services and the web services that we are going to be accessing is items, which has number as a field, description and some other fields, but basically the items web service is the items list right.

We also have something that we created, which is called ILE which is the item ledger entries and that's the item ledger entries page in Business Central, and that one has something called item number as a field and then a bunch of other fields that we are very interested in, what we want to do inside Power BI desktop; we can actually probably do this also in the cloud; but inside the Power BI desktop we are going to connect the number to the item number because these fields connect and once we connect these together; again these are not tables these are web services but they behave similarly to tables; once we connect them together we can report one diagram; so a visualization like tree map or a pie chart system a pie chart like this which might show quantities where we can see here chair instead of the item number, so this would be the item description so we're taking quantities or sales amounts or some kind of numerical data out of the item ledger entries and we're taking the description out of items and as we connected it together all we have to do is just connect the visualization to ILE and then pick the description or items and it will show up automatically. There's nothing you need to do specifically except connect these two together and understand how that works. Let's see that in the application

Item Ledger Entries in Power BI
We were in Business Central and if we go here into all apps, we can get into Power BI and that's what we're working on right now. I actually left where we had created a list by item category, inventory and also the sales amount by item number now the problem is this is by item number and I'm taking the item ledger entries here, and the item ledger entries do not have the description of the item in them. If I wanted to show the description of the item here, I couldn't pick that here out of the Item Ledger Entries, so what do I do? what we need to do is actually pick another record or table in and we already have it's called the items, we have the item list right here, and the item list has the description. How do I get something from the item list into a visualization of the item ledger entries? How can I get the data in here? So, if I pick something out of here the system doesn't know how to connect that with this one.

This is where we actually get into table relations, if I get into relationships over here, I have these two tables this one which is the items and the Item Ledger Entries, I need to tell the system that these are related, the item ledger entries have a relationship with the items. What is that relationship? Well, the item number filled in the item ledger entries is the same as the number in items. They are connected in a one-to-many relationship meaning, there are many item ledger entries one or many connected to the item. If I just pull this number to the item number right here, the system will create this connection, it just does this line here and this is one and then many, if you're not really sure how this is connected because it doesn't say anything except this line, I can go into manage relationships and it shows me here that the item ledger entries item number is connected to the items number field. I can of course edit this or whatever I like but oftentimes I like to actually create everything in here rather than drag and drop.

If I close this out, I go back into the visualizations, I highlight this visualization and pick the description from the items list, now it actually works, it understands that that the items are connected to Item ledger Entries and therefore the description describes the item which I'm picking right here, so I have connected these two tables together and the description actually works. You can actually do all these connections over here in a relationship and then pick them again; I'm not doing any joints like with if you're familiar with sequel queries, I'm not doing programming not doing any unions, you know this is just clicking and connecting in a simple way and you get pretty far with that; ok so we created this, so let's go ahead and save that. I'm going to go ahead and publish this. I'm going to publish this into the University test which is my workspace and now I'm publishing this page up to the cloud again, I was working on my desktop and I have now published it back to the cloud , now it's gone up to the cloud I can just go ahead and log into my Power BI here and just refresh it. Here I have some workspaces, I go into University test and I have some reports right here, here is my report so this is now available in the cloud so in the next video let's explore how I can share this to other people.