Top 5 things you need to know about Power BI
Welcome and introductions
Welcome, and thank you, everyone, for attending today’s webinar on the top five things you need to know about Power BI.
Just before we start the webinar, just a few housekeeping rules. All the attendees will be on mute and will remain for the whole webinar. Please use the question box to ask any questions related to the topic.
Your questions will be answered after the demo.
If we run out of time or your question requires a longer answer, the question will be answered offline directly.
To use the chat box for questions related to the webinar such as if you can’t hear the audio or if the presenter’s going too fast.
Please note that a link to view the webinar recording will be sent to all attendees after the demo.
So, this is our monthly webinar on key areas of NAV and Business Central. You can sign up to as many as you like on our website.
I’ll show you the list of upcoming webinars at the end of the session.
We’ll also send out e-mails to register for the next one. My name is Tiziana Giardina, also known as Deetz, and I will be facilitating the webinar today. I’m an Account Manager here at TVision.
Ben will be doing the demo today and his is a consultant at TVision. He’s been working at TVision for 4 years and has been using NAV since 2013, having implemented NAV as a customer.
Today’s webinar is about Power BI which is part of Microsoft’s Suite of Power Applications which also includes PowerApps and Power Automate. Using Power BI, you can create dashboards and data connections that give team’s instant access to key performance indicators, designed for secure collaboration. Power BI lets you connect to NAV and Business Central in order to view performance, customers, sales, and finance metrics in a visual and interactive way. 1:30
So, this webinar will help you answer the following questions using some Power BI Report Examples – What is Power BI? What is available out of the box? How do I connect Power BI with Business Central? How do I create a simple report? How do I build a suite of reports?
So now I’ll hand over to Ben for the demo, but just a reminder, please use the question box if you wish to ask a question and this will be answered after the demo. Okay, over to you Ben.
OK. Thanks Deetz.
So, I guess the first question is what is Power BI?
Usefully Microsoft have a great website, which gives you a lot of information about it. So, my first starting position, to kind of look at it would be to go to their website and take a look at it.
Because it does have a lot of information on there.
And then there is a thing about what is Power BI? So, you can see how Microsoft view it.
I think, from my point of view, having worked with it and having worked with other reporting solutions in the past is, it’s about opening up the power of reporting.
And I guess, business intelligence, if you want to use that term to a wider audience and making it really graphical as well.
In terms of how the service fits together, you use something called Power BI Desktop to author reports. And you can see if you click on Products, it’s got a link to Power BI Desktop. Popular Desktop is free. So, you download it, and it’s a reporting tool.
You can actually do an awful lot within that, so you can build out all your reports. You can connect it to your data and things like that. Where you draw the line and come to the limits of that is when you come to want to share reports, that’s when you want to start using the Power BI Service, which is here on the Power BI Pro.
And what you then do is you push you all the reports that you’ve built and the data into Power BI Service. So that’s hosted in the cloud by Microsoft. You don’t have any infrastructure or anything to maintain or manage. When it goes into there, you can then set refresh policies. Say, for example, typically, people will refresh the data overnight. And then other users can go to the service. They go into Power BI.
Here, for example, and access the reports. So rather than sort of publishing reports to users individually. So typically, you’re going to be doing that by e-mail or something like that, sending potentially large Excel files around.
It’s then just about having a central repository for reports; which people can access as they want.
There are ways that also they can do subscriptions and things like that to get notified and things update.
So, I think, from a point of view of the platform, it’s about giving you a way to author reports, make them very visual, and hopefully, we’ll see that as we work through this, some examples. Put them to the Power BI service, where you don’t have to worry about servers, remote access, sending massive files by e-mail or something like that.
And your end users can then access those reports to understand how the business is performing, understand how their individual performance might be, depending on kind of how you’re measuring things.
Now, I guess we think about Business Central now, Business Central, there’s two flavours of that primarily. Today, we’re talking about the SAAS. As a solution it starts where it’s hosted by Microsoft in the public cloud.
So, what you’ve got now is two hosted services both by Microsoft. Obviously, there is some, some interconnectivity between those.
Which means that essentially you have Business Central in the Cloud. You’ve got Power BI in the Cloud, and you can therefore connect those together to get your Business Central data out, and put it into Power BI and make some reports.
So, I, think the next step, really, is to sort of start having a look at some of these, How this works. Now, one thing to understand is it is a subscription service.
So, you do have to pay for it but unlike a lot of solutions, the pricing is actually, I would say, very reasonable. I’m going to see if I can get the UK pricing up.
And, you know. This is the US pricing, just got the wrong website on connection here. But it’s currently £7.50 per month for a Power BI Pro licence per user. So, what does that mean?
Well, it means that, for everyone that needs to access and share reports, you’re going to pay £7.50 a month. There’s no additional cost on top of that. So, typically, when you’re looking at these kind of reporting solutions, which might give you the capabilities of Power BI. So, ingest lots of different data. Create lots of complex calculations. Potentially make it very visual. So, whether it’s maps or graph, and again, we’ll have a look at some of these examples. Usually you’re going to have a quite a hefty upfront cost for the platform, if you’d like, and perhaps, and server costs, and things like that.
If you go with the Power BI Pro model and host, it dealt with by Microsoft, your cost is literally just £7.50 per user per month, and you don’t even have an infrastructure to worry about, so you don’t have servers to maintain. You don’t have to worry about things like VPNs, remote access, because it all goes to Azure Active Directory. So, it’s all kind of secured by this standard Microsoft Toolkit to do that.
So, if you’re using things like multi factor authentication, the same as Business Central in fact, there are other flavours of it as well. So, they have this premium service as well. Really, that’s aimed at large organizations which have needs for kind of, specific performance requirements. So that’s where they are.
You’re paying a much bigger fee to give you capacity, which is dedicated to you.
So, it is a shared service. What does that mean? Well, it means that if you’re going to have really massive reports, and by massive, well, I mean massive, then you might, you know, hit some limits on that. At which point, you might then look at a premium service.
I think in reality for life, there are some nice to haves within the premium tier, if you like. But for most users, Power BI Pro is more than enough for what you need.
So, in summary, you’ve got Power BI Desktop, that’s free, And I’ll show you through some examples about what you can do within that.
So, you can download that for free. You can connect it to your data. And we’ll give some examples so that you can build out reports. And you can kind of utilise them and look at them yourself, if you’d like.
The benefit of publishing to service is, one, you can set refresh policies, so therefore, the service is handling all the data. And refreshing of that, you don’t have to hit refresh and wait for it. So, if you set it to refresh overnight, well you come in in the morning, you access that. You’ve now got up to date information rather than having to hit refresh and sit there for a period of time waiting for that data to catch up. And then you can also share it with other users. Because they’re accessing it through the Power BI Service, rather than you sending them a file which they then have to refresh, etcetera.
But, as I said, there is quite a lot you can do with Power BI Desktop. If we think about then, Business Central. So, how does, how does that fit together? So, well? There’re a few options for that.
So, if we just drop into my Power BI here. If I come into here, there are some out of the box apps.
So, if I do a quick get apps, have a look in there, and Search for Business Central, So, it’s a few options here. I can use Microsoft one. If I could spell that would really help, So I’ll come down here. We’ve got a few down here. So, I think the one that I was going to have a look at here is this one here, which is finance. This one, which is slightly under my screen, which is for Sales.
So, I’ll go here, just going to connect me to my data, OK, it wants some information from me, some issues with screen sizes at the moment with us. Fortunately, I prepared this earlier.
And, I’ve got the set. So, this is basically the out of the box Microsoft offering for connecting to Business Central, OK. So, it’s quite simple. And, really, I think the best way to think about this is, it’s kind of a proof of concept, if you’d like.
So, to give you some idea about what we can do. You can see here, this is using sample data at the moment. And when you first download it, you do get something slightly meaningful and you can just connect to data and authenticate through there. You see a couple of charts and things like that. OK, on this dashboard, and then there’s some other ones. You’ve got things like item sales. Got things line item sales dashboard, and there’s some filters here so I can change my date range and things like that.
OK, as I say, for me, this is really a proof of concept, It’s a very simple demonstration of what’s available.
Clearly it will depend on your own set up of Business Central. So, you know, do you sell products, do you sell services, or you’re using it more purely as a finance system.
So, you know, there are some considerations around that as well. So, that’s all well and good, but really, I think what we’re then looking at is we want to then connect some of our actual data.
So, if I drop into Power BI Desktop here, so I’ve downloaded this earlier, OK, how do I connect? Well, fortunately, and this has been done with June update of Power BI Desktop.
So, if you have Power BI desktop that you previously downloaded, if you just go to the Power BI website, search for the latest download and update. The thing just to check is if you come to here and click on About. Just check the one that you’ve got is June 2021 or later. Why is that important? Well, because what’s happened is Microsoft are continually iterating and releasing a new version of the Power BI Service and Power BI Desktop every single month.
And what they’re doing within that is adding new features. So, it might be new visualizations, so new ways of viewing the data, but also, they’re updating connectors. So that is connecting to a database or a service.
Up until now, the only way to connect to Business Central SAAS was through web services. So typically, you would be using an older data end point, which is has kind of got a table structure.
But they’ve updated that just recently, to use the Business Central APIs. Well, that’s great. What does that actually mean? The main benefit of that is it’s much more performative. So that means it is quicker to access data.
From a technical point of view, it gives us, Tvision, a greater toolbox, to be able to kind of build out datasets that would help you to kind of unlock your data.
What do you just click on data, what could get data even? I’ll click on more here. Like, I need to find it. So luckily, I can search for Business Central. Here, we go, you can see there’s lots of different connectors here are for Dynamics Business Central. We’ll use this one here, which is a SAAS version.
Just bear in mind, I’m demonstrating here. We’ll talk a lot about Business Central SAAS today. There are loads of other connectors.
So, if you are an on-premise installation, then SQL Server databases will be your best bet. And if you look at the documentation on the Power BI website, there’s something called on premise data gateway, which is really easy to install and setup, which allows you to access your SLQ server database. We will be primarily focusing on Business Central today. So, I select Business Central here, great? What now happens?
It’s going to ask me to authenticate. So, it wants me to sign in, which I’ve already done on here. And then I’ve got the environments which I actually have access to. So, because I logged in as my user, I have a number of environments which I have access to. So, I don’t have to go through putting in complex connection strings, URLs, and things like that, because it’s all done through the Cloud, through the Azure Services.
It knows where I’ve got access to Business Central. So just using an example, come to this one. I use my demo one here. I’ll pick Bevica which is one of all vertical solutions today but what I am showing you is applicable to any installation of Dynamics. Business Central, So, I’ve got some options here. So, Web services Legacy which used to be the only connectors, OK. So, I can use those still. I’ve got Advanced APIs. Now, the advanced APIs – a lot of those are actually to deal with interconnectivity or integration to other systems. So, what that does mean is that if you come to this map, Group one, for example. You could connect to it. It would be largely meaningless, because it’s designed as a framework for other systems and other solutions to integrate with that.
But we’ve got some here. We’ve got one here for Bevica which we’re in the process of releasing at the moment. But I’ve also got just got standard APIs, which are the ones that come out of the box, from Business Central. And you can see, here, I’ve got a number of them in here.
So, I’m going to pick up items, because I want some access to items. OK. And I’ll get a little preview here.
OK, got some funny data, but I can scroll over to the right and say, it starts making a bit more sense. I’ve got item number, some names, et cetera.
The other thing, I’ll probably one here is item ledger entries, because I want some entries. So, if I scroll down here, can’t find it.
So, need to find that another way. There are some things to just bear in mind with the API.
So, some of these things, like, if I come down to Sales Invoices and Sales Invoice lines, sales invoices will work, you’ll get the header details out, so who have I sold to. Sales invoice lines is actually more of an integration framework. So, if you try and connect to that, you won’t be able to get update. You will get an error. So, again, feel free to explore. You’re pulling data out. So, it’s kind of non-destructive and non-dangerous. So, I’ve got my items here. And the next thing I want is some item ledger entries.
Now I know that within the legacy Web Services, actually, if I scroll down here, I’ve got some item ledger entries here.
So, we are good. I could transform the data. So, if you go through and manipulate that, if I wanted to, but for now, I’m just going to load quickly.
So just going to go through. So, what’s it doing now, it’s connecting to your instance of Business Central. It’s using the API and/or web service to pull the data out, and putting into the Power BI desktop.
When you initially connect, wanting to bear in mind, as you’re putting data from through, over the internet. So, do you need to be considerate of data volumes. So, if you’ve been using Business Central for a couple of years and you pull out your general ledger entries in full or your item ledger entries in full, that is a lot of data.
So, you may find that it may take a while to do that. So, I think just, if you’re going to start out and have a kind of explore with this, I suggest picking things that are limited in the amount of data that you’re going to get out. So, items, for example, you’re only going to have the item list. Item ledger entries, General ledger entries, anything like that, you are going to have every single entry that is in your solution or in your system, so just bear that in mind.
OK, so now I’ve got two data points, or what does that mean?
So, this is where we ended up with a bit of a difference between what I’d say, is what people are used to with Excel and how Power BI kind of works. So, I’ll just drop it into an Excel example. This would be, to me, a fairly standard example of, I want to do some sales analysis.
And because it’s in Excel, that I’m going to create a pivot table or something like that, I’m going to try one flat table. So, by that I just mean one table, which has all the data that I possibly need.
So, from this, I’ve got a posting data, item number, sales quantity, a sales value, cost value.
I’ve got something that tells me who the customer is Maybe I then want some additional information about the item for example, customers in this case, country.
OK, that’s fine, I could have a big lift like that. I can create a pivot table. I can create quite a nice report if I wanted to. But then what happens after that is typically someone comes along and they say that’s great.
I now need to see that by the item region.
So, what you have to do is, you have to come back to your data set. And you have to go, OK, I’ll add item region on to here, which I do.
And then I need to find out the item region. So, you start building out this big, huge table of data.
You’re probably bringing in lots of disparate sources. So, you’re either doing lots of kind of hacks in Excel via lookups and things like that. And you possibly end up with a very big set of data.
The thing is that for every item that I might have in here, I’m going to also have all these item metadata. So, if I’ve thousand items, And I’ve got 20 data points per item.
I suddenly got 20,000 bits of data that are going to be every, single row where that item has been existent or exists.
So, how does it work with Power BI? It’s actually, what you do is you separate these out into separate tables. Here, I’ve got my dates, OK? So, it’s just a date table.
I’ve got a date key so I think of that as a date. I’ve got item ledger entries here. And I’ve got a date key in there and there is a join between these two that says, take this value and match it to that one basically. I’ve then got an item table here and a customer table here. And you can see here, I’ve got a join between the item and a join between the customer.
So, if I just come back to Power BI and we’ve now got item ledger entries, OK, that’s great. And if I just open this out a little bit, and probably on here, I’ve got an item number. Brilliant. So, I can drop that in. Let’s just see what kind of visualisation we’ve got here, OK, that’s good. Bit small so we can just make it a bit bigger. So again, I’ve dropped in this is a table kind of view.
So, you can see all the different visualisations. Item number, we’ll just make it a little bigger. OK, so now I’ve got my items and maybe I want quantity. OK, so I’ve got quantity value here, OK. What you notice is there’s got this symbol that, so what does that mean?
Click on this, click the column up here. It’s telling me that actually it’s automatically going to sum that. So, if I now drop that in here, what it’s actually doing is it’s looking at all the entries for this item and summing up the quantity against them.
Actually, I’ve managed to create a very simple report at this stage, with my items, and how much we sold them, how much quantity I’ve sold them. And I haven’t actually had to write, any form of or piece of code, quite handy.
But, I’ve got my item number here and I’ve got an item table here. Actually, what I’d like to do is bring through the name. OK, so, what I’ll do, I’ll come back here, I’ve got my display name. I’ll drop that in.
Oh, hang on a minute, I’ve got a problem now, OK, it will try to give you some help on that. So, you can see details. I know the issue is so, expediate things a bit, I’ll clear that. And, now, I’m just going to build out a bit of a data model.
And this is fairly simple to do, as long as you kind of understand how the relationships between the two tables. So, if we go back to my dodgy Excel example here, I’ve got an item and an item so item number, to me, is going to be the logical thing to join those on, because that’s going to appear in both. So, I’m going to create a new relationship.
If we just go back a stage. This is also auto-detect. So, if you have item number in one table, and item number in the other, Power BI is going to go they are probably the same.
Don’t think it is going to on here, because probably have different names. But it’s OK, so I’m going to have to manually do it, or create a new one.
Where do I want to come from? I want the items and the number and what I am going to match it to. Item Ledger Entries with item number there?
Have these funny things here about cardinality and cross filter directions, OK? I’m not going go into any detail on those at the moment, but usually what you have is your item list, for example has one record per item, your item ledger entries, same for GL entries, anything like that, is going to have multiple records or multiple entries per item because it is a transaction table.
So that’s why, it basically means that if I filter the item, it’s going to filter through to the item ledger entries. So now I’ve created this relationship.
What does that allow me to do? Well, actually, now I can merge these two things. So, when we did before, with display names, now, if I put in the display name, now, I can start making a bit more meaningful data to my end users, because I’ve now got my items.
I’ve got my name, and then I’ve got quantity coming from item ledger entries. And then actually I want to then do something and be able to filter down, so I want to be able to see some sales by item category code. OK. So, I’ll drop this in, make it a bit bigger.
There it’s a bit bigger. And actually, now it’s interactive. So, if I click on Lager, say for example. OK, suddenly, the lager on the item here is filtering the data in my item ledger entries. So, it’s filtering down to the Item Number and giving me a total on that, and actually, I could do liqueurs if I wanted to or London Dry Gin or any of these.
And this is still a table one. There is actually a particular slicer on this site, a filter to do it. And you can see how I get some drop downs though it is very small. I can do multi selection and things like that.
If I left that as a table, I would also add in a summary if I wanted. I could come back to my Item Ledger Entries, find my quantity one, drop that into there and now, actually, I can even get a mini summary as we go along. So, I’ve got a summary here and I’ll just close these filters down.
So, I’ll make this a bit smaller and drag it to the left a bit. Great. So now I’ve got some detail here, And I’ve got a summary one here. And again, if I filter, I can see those two numbers match ups.
Probably then you want to start doing thing with dates and things like that. So that’s when you can start dropping in dates. So, I’ve prepared a slightly more complex data model here.
So, pick this one here. I’ll just bring it up. So now I’ve built something out a little bit more complex, and I’m actually had to write a little bit of code in here as well for the measures. So, a couple of things on here. I’ve got item ledger entries, items still. I’ve got a new table in it called dates.
Now what Power BI will do is it can do something with auto dates. So rather than you having to put in a date table. So, what’s the purpose of the date table is a good question, Purpose of a date table means I can start filtering down my values by dates.
So actually, I’ve got a list of items here. Across the top I’ve got some years and I’ve got a Sales quantity and a Budget quantity. And then I’ve got Sales V Budget Value Formatting, on which I’ll show you very quickly, but if I stick on 2020 now, actually I’m just seeing the 2020 entries.
Actually, if I expand the outlet, I can then drill down to specific days if I wanted to. Doing this wasn’t actually that much more complex. So, what have I done? Well, I’ve got my item ledger still, exactly as we were looking at before, I’ve got my items, same as before. I’ve got my dates. It’s done some grouping here. Click on my date table, there’s little function in here. And again, there’s loads of documentation to find these things. Calendar auto. What does that do? It looks through the data in there.
Gives me a calendar, a list of dates starting from the earliest dates it finds and ending on the latest dates it can find. OK, and then I’ve used a couple of functions here.
Um, so, data groups. So, I can create a new data group. So, I did month and year. Really simple, so that, rather than me having to put something in there it gathers months bins. What I’ve done, I’ve done a bin. How I’ve done? I’ve done it by sorting one bin per month. You can see it because it understands it’s a date. I could have done that in different ways. Just allow things and build things out.
Example, I’ve got my visualization here. Maybe I want to click on this one.
Year, month. So, actually, I’ll go Year, Month. Then, I can start drilling down into these things. So now, I’ve got the year at the top and the month down here, and I’ve got some sales and budget kind of figures.
Then you can see I can have a little chart for different things, and it’s all interactive. So, I’ll click on botanicals. Everything gets filtered to botanicals, which has no sales, but I’ve got liqueurs and there is some stuff there.
Hopefully if I click on still wine, one of these will have some data. So now I’ve got some budget values in here as well. Where does the budget come from, actually? It was just an Excel file.
So, I can upload any data into here as well. Just find my Excel file for a second, and show you what that looks like.
OK, so here is really simple, I’ve got an item number, I’ve got a date, and I’ve got quantity.
So how has that happened? Well? You go back to this, get some data, import an Excel workbook. Put in a relationship, so again, I’m joining the Budget item number. So, my number in here to my items in there, actually put in a few calculations in here as well. So, I’ve put in a couple of formulas in here.
Great thing about Power BI is that the language is quite similar to Excel. So, if you’re used to working with kind of Excel formulas, it’s very similar to that. So, lots of things to be able to do and kind of build out.
The idea, then, is that once you’ve done that, I can then publish it to the service, which means it’s available to my other users.
So, those are just some very quick and simple examples.
The end result is, hopefully, at some point, you end up with something that’s a bit more detailed. Say, something like this, where, you know, you’ve got some sales by item, using Bevica data with some date filters up here, I’ve got some search boxes, so I can start to find items, I’ve got some little graphs. I’ve got Maps, and it’s all interactive.
So, you will have a look at my sales of French products, All the other data gets filtered down.
The final thing is, Well, that’s great, but can I see it in Business Central? Short answer is yes, you can say, Oh, I’m in Bevica and I’ve published it. You see now, the Power BI report actually nested as a factbox within Business Central.cAnd as I change through here. So, I can start to see other data.
So again, one of the great things about that is it means that you can build out your own meaningful things. Doesn’t have to be a chart, could be a table or anything like that, just to give you additional information.
And just finally as well, you can access it directly from the client. If I come into here within another Role Centre you’ve got access to some of my Power BI reports here when it refreshes.
OK, and those are some of the ones which I’ve published before. And again, these are all interactive as well.It really does allow you to get access to your data and be able to surface it to your users, so your users could have access to those Power BI reports as well.
So, there was a lot to cram in to today’s webinar, I hope picked up something that was useful and give you some food for thought, and I’ll hand back to Deetz now.
Questions and Answers
Thanks Ben. We have had a few questions. So, someone has asked – you mentioned a restriction about sales lines. Just missed that. would you mind repeating that just quickly, Yeah, The Standard APIs are available for Business Central, so produced by Microsoft. There’s a sales invoice header, sales invoice lines or sales invoice lines won’t give you any data. It’s actually more of an integration API than a data one.
Now, what we can do, TVision which is what we’re doing, and we’ve done for Bevica and we have it available for other implementations as well, is create our own APIs. So, to pull out that data.
So, that’s much more of a kind of, you know, our own dataset, which is still pointing at standard tables, it’s giving us data, which we think is more meaningful, lots ways to do that. But, yeah, it’s just some of the limitation that standard APIs currently is that they’re not designed for Power BI reporting. So, just something to bear in mind, and you may get a few errors if you try to connect to them.
We’ve got time for one more question. So, Do I need to have an Office 365 licence to use Power BI? You need to have a Power BI licence?
So, you don’t need to have an Office 365 licence or Microsoft 365 licence, but you do need to have a Power BI licence to use the service, so that where I published it into the service, and other users are going to access the data.
For free, no licensing cost, download Power BI, Desktop, Connect. Build out your Cool. Let’s explore it. It’s only really when you want to publish it to the service, which means, share it with other users where you are going to require licensing.
Great. Thank you.
Five key takeaways and close
OK, we’d like to leave you with the 5 key takeaways which we hope will assist you:
Firstly, it’s easy to get started. There’s loads of guided learning and you can create impactful reports from even simple datasets. https://powerbi.microsoft.com/en-us/learning/
It’s a subscription service so it’s constantly being added to and improved, like the Business Central API connector.
It really is any data. You’re notlimited to just Business Central data and can bring in nearly anything. So, if you do your budgets outside of Business Central then you can bring that data in as well.
Have a plan. So, when you start to build reports for the rest of the business think in advance what is required and what will be useful to your users.
Data is key. To really unlock the power and potential of Power BI having a well-structured data set and data model is crucial and is the platform from which all the reports get built from. This is where it does become more IT led and some knowledge and expertise is usually required.
We hope you found this webinar interesting and informative. We will be sending out emails soon to register for the next one and we shall also put a reminder about the webinar in our newsletter as well.
Thank you very much for attending. Once I close the session, a survey will appear. It will be great for you to respond with some feedback.
You have any further questions? Please feel free to e-mail them to me at firstname.lastname@example.org.
Thank you, Bye bye.