DataSelf Analytics - Customizing Acumatica Analytics

Don.

Good morning. Good afternoon everyone. My name's Don Farber with Data Self Analytics. I am joined by Data Self's CEO and founder, Johnny Girardi. Today we'll be talking about customizing Data Self analytics for Acumatica. This presentation will take just about 30 minutes or so. The first few minutes will be an overview of the Data Self analytics technology and architecture. I will then hand over the proceedings to my colleague Johnny to go through and live implementation of Data Self analytics for Acumatica. And this is going to be very cool because you're going to see from scratch Data Self analytics installed, connected to Acumatica and actually yielding meaningful analytics all within 20 minutes. So I think you'll really enjoy watching that process. So let's get started. A little bit of background about Data Self. We were founded back in 2005, as I mentioned, by my colleague Johnny Girardi. Our mission since then has been to enable organizations to make more timely and more informed business decisions through the implementation of an advanced reporting and analytics solution. Now, the Data Self solution is really a trinity of capabilities. We start off using a next generation data optimization tool. You'll hear more about that in just a couple moments. We joined that with the industry's top BI business intelligence technologies and finish that by joining that with a library of report and dashboard templates for Acumatica as well as for other solutions. And we'll talk more about that in a few minutes as well. But let's start off by talking about some of the challenges that you might currently be experiencing in your reporting for Acumatica. There are a number of different tools, of course, that you can use for doing so, including Acumatica's embedded reporting Techni Technologies. However, sometimes for some organization, it's just a little bit too challenging, a little bit too time consuming to create new reports or modify existing reports. And you'll see just how quick that process can be in Dataself. Perhaps you've got some reports out of Acumenica that you need to run overnight simply because they take hours to generate. And then when those reports are generated, how do you make sure they reach the right people at the right time? How do you distribute them? Often, if you've got a salesforce, each salesperson needs to get a personalized report showing just their data. We'll talk about that. And although we are focused on Acumatica for today's discussion, what about reports that need to pull data from Acumatica and from other sources? Whether you have multiple companies within Acumatica, perhaps you've Got a CRM application that you need to pull data from Excel, spreadsheets, etc. And finally, how about empowering your managers and executives to easily run their own reports through a Google like search technology so that those folks can actually get the analytics they need without asking you or other people in your organization to create them as needed. So let's talk about the Data Self technology. I do refer to it as a trinity of capabilities and that trinity starts out with really the power behind Data Self and that's a marriage of two technologies. The first is a next generation proprietary technology called ETL Plus. That ETL stands for Extract, Transform and load and that technology optimizes all of your Acumatica data to make it quick and easy to be retrieved. Now we join that ETL technology with a data warehouse using Microsoft SQL Server. So it's robust, scalable and really an underlying technology that you can rely upon. Now we joined that with the industry's top two business intelligence technologies, those being Tableau and Power bi. And you'll see that in Johnny's demo. Today those two technologies are rated the best because, well, quite simply they are. They've got a wonderful user experience. They also provide you with a natural language query technology, NLQ as it's referred to, and that's what I mentioned earlier, allows a executives and managers to generate their own analytics. Then finally, this trinity is completed with an underlying report and dashboard library. Over 8,000 reports, dashboards, KPIs that you can use as is you can customize. These reports and dashboards are obviously not just for acumatica, they're for other ERP and related solutions. But they ensure that once you've got Data Self implemented, configured, configured up and running, you can get analytics right out of the box which are very meaningful for your organization. So with that let's move on to our demo. And for our demo I just want to repeat, you're going to see an installation of Data Self, an initial configuration and the creation of a report all in under 20 minutes. At least that's the goal Johnny. So I'm going to turn things over to you.

Thank you Dan. Let me share my screen. Can you see my.

Sure can.

Thank you Don. Thanks for the introduction again. This is Johnny Girardi, Data Sales founder and CEO. We've been working with Acumatica as a data Source for over 40 years now and as Don mentioned, we've been working with BI in the mid market for over 17 years. Acumatic is an amazing ERP system with an amazing reporting capabilities, but still Many companies, they need more, they need faster, they bit easier and Data Self has been a destination for many of them. So let me show here the deployment process Data Self how to customize a new report from scratch in how to customize the plumbing. You know, putting more data from from Acumatica into the BI solution. Keep in mind today's presentation is focused on the business analyst or the people who write reports that are more technical. A couple of weeks ago we had a webinar focused on the business users, how they can actually go to Data Self, find their reports, their KPIs, how to slice and dice their data and how to build their own reports from scratch like Don said, using national language query and the report editor. But today this presentation is for the techies. What I'm going to show here primarily is I'm going to show where to go to get Data Self deployed. I'm going to explain the process, then I'm going to show how to customize a report from scratch. You know, if you want to see out of the box reports like Don mentioned, we have thousands available out of the box. You can go to the other presentation we covered a couple of weeks ago. Today is customization is how to create something from scratch. And hey, we have some additional fields that are not yet in Data Self. How can I go back to the plumbing of data cell to the server side ETL and SQL and how can I add more columns, add more tables and things of that nature? So how do we get started with Data Self? Well, first of all, please contact your Acumatica, VAR or Data Self to discuss your reporting needs to see if Data Self is a good fit for you. We're glad to always discuss and educate the options. And in case you're a good fit for us, for you, we can help you deciding to go through a free trial or go straight to the project. Anyhow, assuming all that the discussion has been taking place and you're going to go straight into deploying Primarily you can go to our website DataShop, click the Acumatica logo. This has the page, if you scroll to the bottom you're going to get this deploy analytics. Now you click this button and you're going to have a form that usually in 15, 20 minutes you'll fill it out. Pretty much you put information, you have to create a user that Data Self will be using to extract your data. Assuming your ecumedica is in the cloud, if your Acumatica is not in the cloud, but in a private cloud or on premises still this form is useful, but the process will take longer because we have deploy with your on premises private cloud system. Most of our clients, they have Acumatica in the cloud. So this is it, you know, fill out this form. It has information about where to go, what to do. You give us a user license, a user access, reading access to Akumatica so we can extract data using what's called odata is a protocol to extract cloud to cloud data from acumatica into other applications like di. There's also some configuration around generic inquiries and we use different protocols. Anyhow, fill out this form. Once you fill out this form, you press deploy. Usually within hours or within one business day you get an email from Data Self telling Golden you're ready to start analyzing your data in Data Self. That's it. Fill out a form, provide the information, the form and when you get the email, you're going to get an email that has a link to the Data Self web portal. The default Data Self web portal is a tableau license that we host in our AWS cloud. When you log to that portal, you're going to go into something like this. So now you are inside of the Data Self web portal using Tableau as the reporting engine. And now if you want to build a new report from scratch, I'm going to show how to do it, how to build a new report out of Acumatica. Again, there are lots of reports available in this portal, but right now I'm going to start something from scratch. How can I start a new report from scratch? Well, you come to the homepage of my portal and I want to create a new workbook. A workbook is like an Excel file where you want to build the reports. So I click a new workbook, the solution will load and will show me all available data sources that are part of the out of the box solution. As you can tell there is, let me sort ascending, there's ap, ar, CRM, gl, inventory, manufacturing, purchasing, sales order. There's a bunch of things ready to go to do analysis. Well, I want to build a sales report. So I'm going to choose sales invoice, which is ar, tran, ar, invoice, your region, other tables and I say connect. This part that I'm showing right now, even business users can do. So let's say when I went over here, I can see my sales parameters, you know, customer branches, invoice information, item information, salesperson, sales order and measures. All right, I want to build a report showing sales by item, by year. Sure. I want to see My item description for instance, drag and drop. I want to see my sales, my sales over here and I want to see by year. So I'm going to take invoice, date and put in my columns. Just drag and drop the data points you want to see and right there you see sales by item, by year. Let me just show year as a filter and select only the past few years to make a little bit simpler this report. All right, so there was a very simple example of showing how to build a report from scratch. By the way, my sample doesn't have a lot of data in 2021. Let's remove 2021. But when I look at my list of fields here, say that for my report analysis I need to see if items are commissionable in what the tax category ID is. You know, I need to do some kind of, you know, sales commission calculation in tax calculation. Right now if I look at tax, there is no tax in my out of the box solution and if I look at commission, there are no commission information about at all. How can I add new columns that are not available out of the box in data cells? That's the backend part of data self that usually business users will not do and they'll call the business analyst or the consultant to change the plumbing. So I was showing you, let me go back here to my architecture. I was showing you how people are using Tableau to build a new report from scratch. But some fields were missing. Now a more technical person that understand tables, links and whatever you know in that regard is going to do the plumbing and that's what I'm going to show you next. To change the plumbing you need to have the right access and user licenses. You have to have access to ETL plus and usually also to SQL. I'm not going to go through the installation of these tools that usually takes about half an hour, one hour for your IT person to go through security firewalls and installing things and be sure that the data is properly secured, the access to your data is properly secured between your Acumatica and the data self cloud system when it's keep the security part of it. I'm just going to show from a BI standpoint how can I add more data, more fields that are not available out of the box. I'm going to open ETL plus and ETL plus is right here. You know, it's a small footprint application that you can download from the Internet. You connect to Acumatica, you connect to the data warehouse and this is it in like in a few Minutes you have your ETL available. Now what is how does ETL the extract, transformation and loading works from Akumatica. On the left panel we have the source the data sources. In this case, when you go with Data cell for Akumatica, we have two sources by default. An Akumatica source and your data warehouse. You know, if you want to reprocess the data, the center panel is what tables are pre configured out of the box, meaning when you fill out the deployment form and you press submit and when you get the data from Data, the email from data cell telling you ready all of these tables theoretically were pulled or extracted from your acumatica and loaded into the bi. So these are tables preconfigured out of the box. Now how is this connected to Acumatica? When you fill out the form, you actually provided ODATA credentials for ETL plus to pull data from your acumatica. Here in the Acumatica data source, if you click Properties it will show the typical ODATA credentials that are required to extract your data. This is part of the setup that usually takes, you know, 15 minutes to do. Anyhow, once the old data has been the ETL OData connector has been connected via Qematica, you pretty much say connect and that will make the ETL+ connect to a Qematica system and see all the available, I'm sorry, all data tables that are all the DAC tables that are available to be pulled from Acumatica. All right, so in this case I click connect and you can see now all the DAC tables from Ecumedica are listed on my far left. Meaning if you want to extract more tables, if you want to pull more fields, it's all available in the source. Well, in my example actually I want to add two fields to my inventory item table that has already pre mapped out of the box. And these are the fields available out of the box. The two fields that I want to add that are not out of the box, I have them in a, in a little document here that I have copied. These are the two fields I want to add to my mappings. I just take those two fields and I add my SQL statement. Let me just click over here. Actually let me close and reopen my ETL because I had open in the computer. Sorry about that. Just give me a second. There it is. All right, so what I was showing is I was going to the invoice inventory item table and I want to add two new fields that they were not available out of the box and it's the commissionable field in tax ID that are not available out of the box. They are part of the original table, but they are not available out of the box. All right, once I add these two fields. Now I need to load this table into the data warehouse. How can I load this table? I select it, I right click and I say load now. And now this table is going to be load the data into the cloud. So right now it's doing the loading and usually takes only a few seconds. There it is. If you notice very quickly. Let me just click the log here to see what happened because sometimes it's too quick. The log says that we loaded 342 records for my inventory item table. Just reloaded with the new with the new information. What else? Well, this data. Now the new inventory item table has the new fields, but I need those fields in tableau. How can I refresh tableau? Well, I click the job in ETL plus in the job is a section that I can control how often I want to refresh the data in data self. And you can come to Properties intel, refresh it every day, refresh it every hour, refresh it as often as you want. Anyhow, the inventory item table. I want to load the data into my sales invoice tableau component and I'm just going to say load it. Sure. I'm going to right click and say running out. So these two steps is just a way to tell ETL plus I added some new table, some new columns to the inventory item table and I'm telling tableau, please reload the data. And this process usually takes a few minutes to finish. So it just finished. Now when I go back to my report editor, right that I was before, I did not have tax id, I did not have commission. You know, I still don't. If I go here in tax, there's nothing here yet.

Why?

Because in my report editor I'm seeing the cache before my refresh. So I need to refresh the cache to reload everything in my data source. And the way that I do that is clicking data and say refresh my cache. Just refresh my cache. Now my cache has been refreshed and if I say tax, there it is. Tax category has been added. I'm going to add my report. So for every item I can see which ones are exempt or not. And also I want to see my commissionable field and I'm going to put my commissionable also as part of my report. 0 is non commissionable, 1 is commissionable. And suppose that this is my report. Or maybe I just want to see items that are commissionable. So let me take my commission. Also a filter. I only want to see the one. So I don't want to see items that are not commissionable. So there it is. I filtered the report to only show conditional items. Now I no longer need to see this column because it's all repetitive. Let me get it out of the way and let's say that this is my report. In a production deployment I could click Publish. Now I'm going to give it a shot. Sometimes I don't have security enough. Security sales by item for commissions I create a report, I click publish and I forgot to provision some space in my cloud so I wasn't able to save it. But if I hadn't done this at this point, when I click Publish, this report will be saved. Now how can I take a report that has been published or saved and share with other people? I'm going to have to use another report because this one I couldn't publish because of the security rights. I'm going to go to a second tab where I have an existing report pre configured out of the box. So this report already existed before and this is a kind of flashy one, has a lot more things going on. But when they look at a report in Data self. As a business user now I'm no longer a business analyst, how can I share this report with other people automatically on schedule? Well, once you have the report ready on screen, you click Watch and you click Subscriptions. Subscription means automatic distribution reports. You click Subscriptions, you type in the people's name. The people names that you want to share with, they want to see with Zach, I want to get with Clark. I'm going to include myself and all the people you just put in this list. To do this you do not need to be a business analyst. You can be a business user and just add peers, all of them. You select how the report is going to be distributed by PDF for instance, and at the bottom, how often I want to say every week, every weekday at 7am and click subscribe. And that's it. Automatically. This report is going to be distributed to all these people on that schedule by PDF. All the things that I'm showing you right now. I'm outside of Acumatica. Can I have everything that I showed you inside of Acumatica on the report side? Yes. This layer here, this layer here, and this layer can all be embedded inside of Acumatica. So users don't need to leave Acumatica to get their reports and dashboards. But all those users that do not need to be in Acumatica, you know many managers, they don't need to be to go, they don't need to go to Acumatica. Sometimes they don't even care about Acumatica, they just want the numbers. So you can have Data Self outside of Akumatica or inside of the Akumatica user interface. Right now I'm using the outside anyhow. Easy to deploy, easy to build a new report from scratch, easy to distribute reports. And also like Don mentioned, you can actually create new reports using natural language query. Just type it in a question and the tool will build from scratch a report right in front of you guys. And that was pretty much your presentation. Don, back to you.

Thanks very much Johnny. And let me share my screen and just finish up with a couple final points on pricing, we'll answer any questions, etc. And Johnny, I have to congratulate you. I just looked at my clock. You did that in exactly 20 minutes. So you did make a liar out of me. You got Data Self analytics reporting on data from acumatica within that 20 minute time limit. Well done my friend. All right, so let's talk a little bit more about or a little bit about pricing. So Data Self analytics, first thing I want to mention is that you can try out Data Self analytics for free. So you can go to our website, fill out a form and you'll get a 30 day trial license, full function license of Data Self. And obviously if you need any assistance in terms of setting up that trial, we'll be glad to help. We have a number of different licensing options available to you, starting with the advanced edition for as little as 399amonth, going up from there depending upon your needs, configuration, number of applications to be monitored for reports, etc. But very importantly, I do want to mention that we currently have a number of year end promotions taking place. You can find out more about those by either visiting our website or just popping us an email or calling us. Those include such things as if you purchase a Data Self analytics subscription license, you actually get 13 months for the price of 12. So one extra month for free. But that's only one of a number of different promotions that are available to you. So by all means do contact us to find about find out about some of those other options. At this point I'd like to open up the lines for Q and A. So please feel free to use the chat session or the Q and A box to send any questions to Us. I see one that has come in. Johnny, you showed ETL plus doing a refresh of a table in Acumatica and the question that we have is when those data refreshes are done by ETL plus, let's say once a day or a couple times a day, does it always load in the entire contents of the Acumatica database And if so, about how long does that take?

Good question. In just clarification on the promotions, the one month free is only for orders of the advanced and pro editions. The enterprise has different discount structure. Just want to be sure that's clearly stated. Anyhow, back to the question here. So the first time that the data is loaded into data self, you know, once you fill out the form you press submit. The process to deploy data self and extract your data starts in our cloud, you know, cloud to cloud. Assuming you have an akumatic in the cloud and depending how much data you have, it might take minutes, hours and sometimes even days. You know you have lots of millions of records in your largest tables, it can usually take more than a day to refresh the first load. So the first load can be taxing, not taxing can take a while. Our system does not tax ecumatica system. Our system is very light in getting data out, but if you have lots of data it can take a while. Now the good news is once the first load takes place, the ongoing refresh, we use delta refreshes meaning we only bring what's called, we use the upsert delta refresh. Upsert means update existing records that have changed in insert new records meaning usually even if our client with the largest ecumatica data volume in a single day in their business day, they ship more than half a million sales order lines. It's a lot. And still the data self refresh only takes minutes to load because we're only pulling records that have changed since the last time. So just summarizing first load can take a while because it's bringing lots of data. Ongoing only bring deltas which is really really fast.

And Johnny, thank you for that answer. And one more question has come in from one of our attendees. Is it possible to see a data self report right in Acumatica?

Absolutely. The way we can embed the data self reports in Acumatica is using a technology called iframe for some people. Sounds complicated, but pretty much in Acumatica when you use the sitemap configuration in sitemap it takes about two minutes to take a data self iframe link from one of these reports and embed it in Acumatica and if you use single sign on in Acumatica like with Azure or Google Active directory then it completely embedded. When you go to Acumatica log into Acumatica and you click Sales report and when you click sales report the Data self report shows with the information and the user has no clue that that report is actually coming from Data Self as a user interface and not a Acumatica built in report. So Data Self reports can be completely embedded using the Iframe framework which goes into the Acumatica sitemaps.

Perfect. Thank you for that answer Johnny. And I know we're just about up against our time limit. Two last quick comments. Number one is that previous webinar recordings of Data Self analytics for Acumatica are available, including a business users webcast which really hit upon a number of the most popular reports and dashboards in Data Cell such as a cash flow analysis, inventory optimization, year over year variances and others. So if you or your colleagues want to see more examples of some of those pre configured reports and dashboards, by all means go to dataself.com and you'll be able to access them through previous recorded webcasts. And last comment is that Acumatica themselves use Data Self analytics for their own reporting purposes and they reviewed a number of the available technologies out there. They decided upon Data Self. So I think that that speaks volumes for the appropriateness of Data Self to the Acumatica environment. So without any further ado, thank you everyone for your time and interest today in Data Self analytics. Again, check out our website for current year end promotions. Give Data Self a try and we'll be very happy to help you get that up and running. Hope you all have a very happy holiday season. On behalf of Johnny Girardi and myself, Don Farber, thank you very much your time and interest. We very much look forward to working with you and the Data Self analytics solution. Take care everyone.