Watch this webinar on Jet Analytics and Power BI on 22 Nov to find out how Jet Analytics is the perfect partner for your Power BI and will make it easy for you to take operational data and process it into vital dashboards.
Thank you, everyone, for joining today. We’ll be starting very soon.
Alright, Jan. Hold on a minute. We can get starting.
OK, Jan, let’s get going.
So, everybody, thank you today for joining ‘The Fuelling Power BI was Jet Analytics’ webinar with myself, Danusia Jolliffe from TVision Technology and Jan from Insight Software.
So, I’ve been the Marketing Director here at TVision and been with the business for nearly six years now, and also looking after the Customer Services team. And throughout the webinar today, I will be monitoring the chat and the question box. So, if you do have any questions, please do get in touch and we can ask those as we go along.
If you could introduce yourself to our audience.
Hi, I’m Jan De Dycker and I’m a Technical Account Manager for Insight Software, which means that I support partners with the demos and trainings and our software, which today will be Jet Reports and Jet Analytics right.
So just to summarise what the aims of today’s webinar are, so we’re going to talk to you, I say we, Jan is going to do the demo for you.
Taking operational data, making sure that you can able to have some self-serve custom dashboards, get some accurate govern data within that single source of truth that we’ll talk about, offering the rapid time to value through this installation, which will only take several hours. So, as I said, I’ll be monitoring questions and chat, do ask as we go along. Jan, over to you for the demo.
So yeah, today we’re going to talk about Jet Analytics and Power BI in that combination.
First off, a little bit about Insight Software, we make Jet Reports and Jet Analytics. We also do Bizview, which is more planning all for the Dynamics ERP systems. So again, today, we’re going to talk about Jet Analytics and how it works, how we set it up for Power BI and what are the advantages of doing that?
As the slide already says as well, it has all Dynamics ERP data.
We have a lot of experience in connecting to GP, AX, NAV and BC. So, a lot of our customers have legacy there, of course. These days, even, we see a lot of AX customers move to BC as well, so that that knowledge in our products comes in handy. A lot there.
So, what is the idea of Jet Analytics? Basically, it reduces your dependents, not just on IT, but on a lot of technical skill.
So, getting your data towards that BI solution, towards Power BI in this case and reducing technical skill level needed for that to set it up, but also, especially to maintain it over long periods of time.
So, what also are we trying to do, want to have a single version of truth, as mentioned before? Which means that everybody reports on the same numbers. Alright, so, those numbers are set up once, and you need everybody to report on those same numbers.
Which feels really constructive, when you’re setting it up, because every time you set something up, you set something up for a lot of future use. You don’t have to set it up every single time.
Again, when you’re making a new dashboard and making a new report, you can build on whatever was done before, which reduces maintenance a lot as well, of course.
On top of that, we facilitate ERP upgrades, or these days, a lot of acquisitions. For example, someone you acquire another company, they have another ERP system and we merge that data. Same goes, if you’re moving from an NAV two a BC, for example, we can merge a lot of that data in the BI system, which avoid having to migrate that data to the new system.
Because migrating that data to the new system, again, takes a lot, a lot of time. Plus, you got all that all old data in your new system, which you don’t always want.
And it doesn’t always matches that well, this, which is something we do constantly, of course, in BI.
So, what is Jet Analytics? I always split Jet Analytics in three parts.
Jet Analytics comes with Jet Reports, that’s an entire different sessions on its own Jet Reports. We deliver it with Jet Reports as a front end for Excel. Of course, most of our customers using Jet Analytics these days, use a combination of Excel and Power BI.
Where we see, for example, in finance and inventory, there also is always demand for some lists and Excel reports. Which Jet Reports is perfectly capable of automating while a lot of the analytics, of course, is done in Power BI. Another thing that people really like, or where Jet Reports really adds to the Power BI experiences.
Scheduling, something that it’s still very limited in Power BI, for example, being able to send out a certain report to every vendor by the end of the week. That’s something that’s really hard to set up in a Power BI.
Jet Reports, allow you to set up in Excel. Jan, is there a limitation on the scheduling that you can do, and those numbers of reports? No. OK, that’s completely unlimited. So, yeah. So, we see that that combination of Excel, which Jet reports and Power BI, really fills up all of the gaps let’s say. Alright.
So, the next part on Jet Analytics is prebuilt data warehouse and project with their OLAP cubes
or Tableau models, which means it’s a turnkey solution for Dynamics ERP systems. We have a turnkey solution, of course for Business Central, which we’re going to talk about today, which allows us to get going on on those systems in a matter of hours, less, a lot of the time. So really brings value to that, and I’d say the value in that is at least a couple of weeks work.
So, they’re really valuable.
And the last part of Jet Analytics, which is, by far the most important part, is the Jet Data Manager, which I’m going to show in the demo. And this is the solution that allows you to manage that solution. To go through a low code or even no code environment, and still create your entire back end, data warehouse and tableau models to be used in Power BI.
So that is very important, part of it. Of course.
Good, talking about BI a little bit.
Identified three big areas of data in there. There’s more, but these ones are important. So, the first one is, of course, our source data. Source databases could be multiple. Of course. They contain data.
But a lot of the time, these databases and the data structured data is completely optimised for data inputs, which actually is almost the opposite of making it optimised for data output.
So, what do we do?
We create a data warehouse, which is a database specifically optimised for reporting and data outputs.
So, the idea of a data warehouse is you have to imagine someone who’s never heard of BC, for example. It could be, you could turn them to a BC database because it’s all on the table. Names are quite normal. But still, he’s going to have a hard time to realise that the sales invoice lines and the sales grant number lines on two different tables, right?
So, what do we, what do we want to do with the data warehouse if you want a database? Where we can get someone for example, with Power BI knowledge and show him that database and he could build a Power BI dashboard without making too many errors that he would otherwise make directly to the BC database. So that’s a bit of the idea of the data warehouse.
It goes a lot further and we can do a lot more with it. Again, we could, in our financial transactions, for example, we could have financial transactions of several different systems at the same time. And the people reporting on it, they just have financial transactions.
So, for them, it’s all one big one, complete unity. Budgets, for example, could be from Excel file from another budgeting system. All in there and people reporting on it, they just use Power BI so they don’t need to know where all this stuff comes from. That’s the great thing about the data warehouse.
Coming back to that facilitating ERP upgrades is a very important part of it, because you have the data warehouse. And just because you change backend system doesn’t really mean that your data warehouse or reporting needs are going to change drastically.
So, you plug that new system into your data warehouse and do an acquisition, plug that data into your data warehouse. And, again, reporting just stays the same.
So, the data warehouse is really central and that solution, and a well set up data warehouse can take you a long, long way.
So, the last part on this is, as the data models, as we call them, used to be cubes for Microsoft these days its tabular models.
For the front-end users, they’re exactly the same. What we do, there is, basically, a perspective over that data warehouse. And a perspective, for example, in the sense of the finance perspective over the data warehouse and the idea of a tabular model, is that someone with no database knowledge, maybe even very little Power BI knowledge.
You can learn him to make their own dashboards in half an hour, just learn the basics of Power BI and they can make their own dashboards. But that is a great idea. Usually, when you see a Power BI demo, which I’m going to give in a second, people, and start off with their, they have the data model, and then you can collect together your dashboard in 30 seconds. And, of course, that’s the idea, that’s what we want to do for our customers or for your employees.
OK, building on that, we got the front-end visualisation talking about Power BI, for example. Power BI is a powerful visualisation tool.
That starts off. correct me if I’m wrong. I think it’s $10, Now it’s actually free. The desktop version is free, right? And then the Pro version is $10 a user month. I always say dollars, because I think Microsoft is 10 dollars cents a dollar that the changes. Yeah. So, it has a really powerful solution for that, for that price.
But again, when seeing those demos, a lot of what you see is the front end, and that’s also what we want to see.
But in reality, working on it and getting to a dashboard like that majority of your work, 80 or 90%, is in the backend. Getting that data from your Business Central, transform transforming it, so that it makes sense to put into a dashboard. And that is where Jet analytics comes in.
So, a Jet Analytics really plugs in the middle between your ERP system or ERP systems, and your front-end solution, in this case, Power BI or Excel.
Talking a bit about reporting in general, something. You see a lot of the time, it’s just Excel files. People doing stuff in Excel.
Usually, it’s multiple people. Doing multiple things in Excel, and it’s a bit all over the place.
Introducing Power BI, or Click, we’ve seen a lot with Click in the past before Power BI was introduced as well, a bit of Tableau.
Do you often end up in this situation?
Nothing’s really changed.
You just gave you a problem, a different name, right? So, people doing a lot of stuff in Power BI and it’s all a bit decentralised and everybody can make up their own through.
And, again, looking at the people connecting to Business Central, they can all make the same mistakes. Or they do. To correct those mistakes, they’ll have to do the same transformation, it’s all on their own.
Which requires a lot of knowledge everywhere, And I can talk for most partners, and I think for TVision as well.
So, you have this guy here, who knows Power BI knows it pretty well, because he is able to connect to several system that sort of form the data and make sense of it.
He knows all these backend systems as well, pretty neat.
And then he’s able to pump out dashboards for the front-end users.
So, for the operations, so, again, I can talk for TVision, if you have too many of these, send them to us. Right? This is a true bottleneck in a lot of organisations these days.
And it used to exist as bottleneck. But the problem was, 5 or 10 years ago, we had a couple of people within the organisation that got access to BI.
So, you have a couple of people pulling on this person to make more dashboards. These days, what you want is, what?
A lot of the time, you want to enable your users to do their work better, and you want to do it themselves. Yeah, give them access to all the data they can have.
Purchase, inventory, sales. They all want their own dashboards, their own reports, in BI.
And if all of those, start asking this guy, this guy’s already overworked right, it takes two weeks to do a small change in a dashboard.
And you want to get rid of data that you want people to enable to people to make their own dashboards.
So, the solution there is put a BI layer in the middle, which means that a lot of the technical knowledge is actually put in this part.
Where we can have are the knowledge of these source systems and the knowledge to transform all this data, and then prepare it for the end users. That is all done once.
So, still, someone has to do it, someone with technical, technical knowledge, but he makes it constructively and then enables everyone to do data models on that, to do dashboards on that.
And I’m going to show you now, how that looks when someone does that.
So just for the last thing on that.
In Jet Analytics, we have 6 or seven standard data models. Today, I’m mostly going to demonstrate a sales data model, because it’s pretty easy you want to understand, right?
So, I’m going to demonstrate to sales model. And first off, I’m going to show you what happens when you expose data model, and you connect to it and power BI, and what that looks like.
So, first thing I’m going to show you is a dashboard has already built. So, this is a Power BI dashboard. It’s pretty straightforward. Let’s see if everything works.
If I click anything, everything changes.
I click on this. Yep.
So, this is a pre-built Power BI dashboard. So how do we get to this connecting to a data Model? I’m opening up a new Power BI.
Again, I said, if you have any, um, you have any questions, meanwhile, type it into chat. Or type in the questions and I’ll see if they’re relevant at this point or we can answer them at the end of the webinar.
So, what I’m going do, I’m opening of new Power BI and get the data from Analysis Services.
So, what we do, with Jet Analytics, we just create Microsoft SQL databases. So, we got SQL databases, but we also got SQL server, analysis services could be Azure Analysis Services. Your prerogative, of course.
In this case, it’s SQL Server Analysis Services Database, and of course, Power BI has native connectors to that. So, I’m just going to connect to my data model. And as you can see, I’ve got two of them set up here, and I’m going go for the sales one.
So, what happens connecting to that?
As I said, this is a perspective over my data warehouse. So, everything should be fairly straightforward. And the idea is that someone with some sales background, again, you can give them half an hour introduction into Power BI.
If there are no pivot tables in Excel, you can do it 10 minutes, I think, in Power BI, they’re good to go.
And the idea is that it can come here and they can say, oh, look, I want my sales, both the transactions, my sales amount.
And they got the big amount, and then we can split it up by company.
Let’s put that company on the ledger.
Here we go. And let’s go to posting date.
I split it by posting date, and this is by year.
Let’s click ones that’s quarter, and these are months.
So, the idea is that someone, again, without all that database knowledge, can do a couple of clicks, has a nice, nice chart there. Let’s do 1 or 2 more.
Let’s do our little pie chart here.
Then some charts efficient artists will say, you can’t use a pie chart because I’m doing item category, and that’s too many items to have on a pie chart. But I’m OK with that. And here I go to Quantity.
So, all I did was click Item Category and click Quantity, and I got my Quantity by Item category. So that’s the idea of a Data Model. Of course, this is a lot of data. Maybe let’s put a little slicer in.
And let’s do it on that.
On the year, so that I can select I should have done a dropout slicer’s like one year and that. Here we go.
So, this is what I was talking about.
This is Power BI demo, and you can see in 30 seconds, I got an entire dashboard, that I could click the counter. And what facilitates that, that’s the backend that is, this data model. That is all pre-built. This is our out of the box data model.
Um, I’m going to show you what makes Jet Analytics, so special that were easy that we make that really easy to maintain, so.
Let me look at something I’m missing. So, the first thing we do, usually, we can get to this point in an hour or two on, and on, and on, on your own database. And we get to this point, and then we start off with this. And then we’re going to look at what is missing here, right?
What do you need to to make this work for you? A lot of the time, it’s something like some customer information or some item information needs to be at where we could split up that data.
So, for example, I got the item here, but I’m missing the product group.
So, there’s no product group. So, default field in an MDC, but we haven’t had it added it here. So how does that work? I’m jumping into the Data Manager.
And if you could look the Data Manager, here, it has big three columns. So, let’s collapse all of this as three big columns.
And these three columns roughly corresponds, correspond with my slide from earlier, which is this one.
Right? We got our source, or Data Warehouse, and our tabular model. So here, we have our data sources, or Data Warehouse. And our tabular model. There’s one extra here in this column, which is an intermediary database, where you can do some transformations, basically the playground to the BI person. But what we, what can we do now? We can jump into our data source.
As soon as I click my data source on the right, you can see our tables, all our tables, all our fields. From Business Central, we expose them all. So that means you have access to your entire business central database, including extensions, or anything you can think of.
So, I’m here, I’m going to look for that product group code.
There we go, we got the product group from the Business Central database.
And I just check that.
the moment I check that, it will be in my staging database.
And from there, I can find it.
And I can just drag it to the item table on my Data Warehouse. And from the item table on my data warehouse, I can drag to the item table on my Sales data model.
So, the idea of this product, as I said, is very low code or no code at all.
That looked very easy to do.
Yeah. So regular maintenance is usually done by our customer cells, because, otherwise, again, they would need someone from TVision. And correct me if I’m wrong, but you guys are not waiting to pick up the phone.
Like, I’ll add one field to my Power BI.
Thinking about Power BI on Business Central these days, a lot of it is just custom writing APIs, right?
And that’s just, yeah, takes a while to, to get all of this true. While we could do it as a couple of clicks now, there’s a lot more to this tooling. Of course, I don’t want to make it too technical, but the great thing about it is that it keeps track of data lineage.
So, it knows really well, which dependencies are between which fields, which is a big part of the power to be able to maintain all of this. Because it can get complex, right. You’re doing currency calculations. You’re doing a lot of transformation sometimes, but the software keeps really good track of it.
A lot of we call it a bit of self-documenting, where you can jump into this, or whoever built this.
And it’s easy to keep track of where everything goes. So, one thing, one way to illustrate here, I can pull up a data lineage and let’s do this product group. You guys saw me do it. I checked it, and then drag that and then dragged it again.
So, it basically made four steps, starting from the source.
I can visualization, I can do the impact analysis of that field.
And you can see it take those four steps. So, going for the source, to the staging, to my data warehouse, to my sales data model.
And, of course, that’s a very simple field. I just add, if it looked like the field that’s been used a bit more.
So, think some of you might know the value entry, which keeps track of all the value entries, all the inventory, Transactions, basically, that have an effect on cost.
Let’s do the sales a month on the value entry.
But you’ll see where it ends up in that sales data model, where it’s used in calculations like discount percentage, an average unit price. And so, you can easily keep track of all of it, what happens to feel like that. And that makes up a lot of the power to be able to maintain a project, when it gets a bit more complex.
Now, working in this, I can work in this in the entire day. At this point, nothing happened yet. On my SQL Azure database. Or on my Power BI, I can do all my changes. Maybe you have multiple things to do before something will work, right, before you want to add an entire table, for example, like item variant, or something like that. So, by the end of the day, or when I say this is ready, I can actually push this through to SQL.
And I can, I can even write a version note. I’d say, added product group code.
On a later stage, I can look back and see what I did back then, and I can even rollback my changes.
Maybe I did something wrong and even very easily, roll back a version or two, if necessary.
The last thing I need to do is push out that data model towards Power BI.
Towards SQL, actually, in this case, SQL Azure, or this is SQL Server.
And also, the same thing at one point. When we say, for example, as I said, this pushes out to a SQL Server, so, technology, it’s old technology. When you were at one point to and say, oh, let’s move to SQL Azure, Azure. We just changed the connection on this, and you push out the same data model to SQL. Sure. So, transformation, the transformation, that is very, very straightforward as well.
And let’s refresh my Power BI and you’ll see the Product Group code up here.
And I’ll use it straightaway. Let’s do the Product Group Code here.
Let’s do Profit and Discount on that product group. Here we go.
Select one category, and you just get that product groups for that category, of course.
So again, in a couple of clicks, you can very easily add.
Add a field to that, or even add an Excel file to data, contains budgets, and pull that in so you can use it in Power BI.
OK, last thing I want to show you here, or one of the last things I want to show you here is talking about Excel.
Because as I said, we have a big mix between Power BI and Excel users, but it doesn’t really matter. It’s a bit of the user’s prerogative from that sense.
And what we can do here, we can do basically the same thing.
We can pull data, get data, from analysis. Services. are exactly the same as we would do empower as we did in Power BI.
And connect to those data models.
And just to highlight it, I’ll do exactly the same chart as I did earlier, do a stack chart, and we have the sales on that per company.
And we put that company on the ledger.
And then we looked for the date.
And we put that date, not on the ledger.
There we go. And we open it up.
And the data you get here is exactly the same, I can even look at my item.
I got my product group quote here as well, because I just added it to the data model, on my Microsoft SQL, which means, yeah, it doesn’t really matter if your users are you prefer to use Excel. We see a lot in finance and inventory, right? And it doesn’t really matter.
You can access to the same data and the results are the same. An Excel, to be honest, has some merits when you want to analyse the numbers. You have minus 311 here for example.
You can double click it and you get to individual the postings for that number. So, Excel does allow you to navigate that data a lot faster while Power BI is a very powerful tool to build a dashboard and publish the visualization policies. Yeah. But again, we don’t discriminate on that.
We even have customers using Tableau or Click. It’s not sort of majorities Power BI, and Excel, of course.
But we don’t come in in that with just the layer between at front end BI and your, or Business Central in this case.
Let me see if I wanted to.
Oh, yeah, Moving back to this abyss.
A lot of the power is of course in the dependencies we have. We have a lot of advantages there. Because what we can do as well, we can refresh the data, of course. This isn’t real time sort of refreshing the data. But we got a number of customers refreshing every 15 minutes or even faster.
And you can you have unlimited refreshes on that. There’s no penalty or penalty on that.
So, we can refresh multiple times in an hour no issue. And another thing that we can easily do, we can pull on the other side of that threat.
So, you saw me do the impact analysis, but from the same end let’s take another data model here, the finance data model.
Finance is pretty simple, right? Because you’ve got your GL entries and your dimensions, and your chart of accounts, and then you’re basically there.
So, that thing is, if I want to refresh this, I need to refresh all these tables, and maybe it gets a lot bigger, right? Just to have that finance data model refresh.
We can do that the other way. We can look at the finance data model, we can ask the product, we can ask it, what do we need to refresh this finance data model?
And let me enable that just a second there.
And if we asked the product that, it’ll just tell us.
And this is completely dynamic.
If we work on the finance data model, it will just add to that, and it’ll refresh anything you need for that finance data model and nothing more. So, that allows us to do, for example, more of the time, it’s like inventory to do inventory every 10 minutes, while we do finance two times a day.
Right? So, it allows us to do very quick refreshes, but there’s a lot of advantages to doing it that way. And it scales really, really well.
because it scales with SQL.
Yeah, that’s that’s basically the the load down of it. Let me get back to the PowerPoint.
We went through that.
So, I think I’m I’m back to you Danusia.
Yeah. So, obviously, we’ve shown you that about taking that operational data and building those self-service custom dashboards, as you’ve shown us Jan. It doesn’t look really, really straightforward than people do have the option of looking at it.
In Excel, or in Power BI, as you’ve mentioned, think the single source of truth comment is very, very valid. And when multiple people are working on a system, you need that single source of truth. And this is a very good way to demonstrate that, and you said there about the turnkey installation within a matter of hours, if someone knows what they’re doing, they can do even quicker. And obviously, we can help that arrangement at the beginning.
And make sure that that integration, and that interface between the NAV or BC data, Jet Analytics and then the next stage looks whether that is in Power BI, whether that’s in an Excel.
So, I haven’t had any more questions, but the one that I do have is: How easy, and how quick is it for us to speak to our clients and then to get you to get that sorted out for them?
Well, what we usually do is, first off, I’ve got a video on this, where I set it up on a test database. Of course, the biggest table is one million records, and I do it in less than 15 minutes.
You can set it up really, really fast. But we do provide possibilities of a demo license, for example, even where we can just have, just have it, test it out, see that everything works, and see if it works for you.
But, yeah, it goes anywhere, usually for, for a real installation, I’d say anywhere between an hour and half a day, to set it up.
And then, of course, doing some modifications so that, that’s so that, yeah, the fields you want in there are in there for you.
As for the, as you set, the dashboards, a good example is, I had a partner who basically still sold individual Power BI dashboards.
And, the break-even point was, between creating manual dashboards or getting a Jet Analytics and between the breakeven point was about 10 dashboards, OK.
So basically, they asked their customers, do you want 10 dashboards or more?
And that was because it’s nothing but a productivity tool.
It just makes setting that up and maintaining that a lot, a lot easier.
There’s a lot of powerful functionality in it, as I said, as soon as you start adding a lot of complexity, almost a no-brainer to put something, something in between and with complexity.
I mean, you have a CRM or a budgeting system next to it or you have multiple ERP systems that you want to do, whether it’s upgrades or not.
Or even, it just have a complex vertical transport, retail, and that the verticals just got too complex to just maintain the majority it with a number of our clients as well.
Yeah, so, I’m using that, just gets too complex, too many tables to be able to maintain. And a simple, In a simple data model. Yeah, that’s where it really starts to shine.
And as I showed you, I just added that product group code. Could come from anywhere.
But once I set that up with my knowledge, where it comes from, everybody can use it right?
In Excel and Power BI, whoever looks at the data warehouse has access to that Product Group code now.
Sorry, I interrupt you.
No. No, that’s OK, you said about you can input and plug in a number of ERP systems. Is there a limit in any way?
Well, the licensing is per connector.
So, there is no user licensing, because everything we output is, as I said, Microsoft technology.
So, we put up Microsoft SQL, Microsoft SQL Analysis Services. So that means it’s the front end. If you need front ends like Power BI, you pay to Power BI because it. But there’s no extra cost for Jet Analytics.
Whether you buy it for five users or 500 users Jet Analytics data, same cost, but the number of systems to connect to. That’s where, that’s where the power, the Jet Analytics licensing is.
But other than that, there’s no limits on that. Let me just jump to it for a second. We got.
Well, what they are.
No, it wasn’t sample.
Was At… So, we worked together with a company called See Data
…and they make a lot of connectors and all of them are licensed within Jet Analytics. So, you can go to that website and scroll through it. I’m scrolling through fast for you to read if you have any of these systems, and there’s a lot of them, and they’re like Salesforce, Google, Google Analytics, YouTube.
Yeah, Chimp, a MailChimp, stuff like that. It’s all in there. We have access to all of those, and we have a number of them more. But usually, we just got a customer with (…) for example. And we got to our connectors for that as well.
So, the one we use a lot here, I don’t know if it’s in your customer, it’s relevant to them, the sales.
So, the CRM part.
So, that’s the one we always use, C data connector for CRM. Pretty good. So yeah, they have Business Central Connectors between others.
That’s great. So, no more questions have come through. So, thank you so much. And for that session today, I’m if anyone has any questions, then obviously we can put you in touch with you directly. And any, rather, the demo, this demo will be available online in the next few days.
And the link will get sent out afterwards to all of you who attended. And you can share with your colleagues. So once again, Jan many thanks for being here today with us.
Thank you for having me.
All right, thanks so much. Thank you everybody. Bye.