DataSelf Analytics - Customizing for Sage 100

Good morning. Good afternoon for those who it is indeed afternoon. My name is Don Farber. I'm joined by Data Self CEO Johnny Girardi for today's presentation on Data Self analytics, specifically customizing analytics for Sage 100. This webinar will take about 25 to 30 minutes. We'll start off with a brief overview about the Data Self technology. We'll follow that with an in depth technical demonstration of the Data Self solution by my colleague Johnny and then we'll leave about five minutes or so at the end for any questions and answers. And we'll also share with you some pricing details and information on upcoming promotions for the Data Self analytics solution. So with that, let's kick things off. Just a little bit of background about dataself we've been around since 2005. As I mentioned, my colleague Johnny Girardi is the CEO of dataself. Our mission is to enable organizations to make more informed business decisions, to make those decisions in a more timely manner and thus increase revenues, decrease errors, reduce costs, etc. And this is done via a next generation technology. Now this technology starts off by optimizing your Sage 100 data. It then leverages industry leading BI tools to get the information that you need when you need it. And finally, it's complemented with a large library of reports and dashboard templates for Sage 100. So what are some of the challenges that organizations face on reporting out of Sage 100? Well, first of all, just the process of creating and modifying reports, regardless of which technologies you're currently using to do that is it's to add. Difficult, takes a lot of time, and often you simply don't have the resources to provide the reports that are being asked of you. When reports are created, often they take a very long time to run. For example, if you're using crystal reports, you know that certain reports need to be run at night simply because they do take hours to complete. Let's talk for a moment about report distribution. A lot of reports need to be sent to a number of different people, perhaps different sales reps need to receive personalized reports only with their particular activity or data on it. That process can be quite time consuming. And next, blending data although Sage 100 is the focus of our conversation today, very often you need to pull data from multiple sources, not just Sage 100, perhaps multiple companies within Sage 100, perhaps from a CRM application or from Excel spreadsheets. And that's a challenge. And last but not least, one of the challenges certainly has to be that with current reporting solutions for SIG 100, you really can't empower users, executives to run their own reports to simply say, I need to know how our sales are going this year, broken down by month, by product. And so in our demonstration, we'll talk about how Data Self addresses each of those challenges. But before we do so, let's just take a moment to talk about the trinity that is the Data Self architecture and technology. It starts with an aspect that we title the Power of Data Self. And that is a unique combination of a proprietary technology called ETL Plus. ETL stands for Extract, Transform and Load. And it optimizes your Sage 100 data so that Data Self can retrieve the information that you need when you need it. Now, we combine ETL plus with an underlying SQL Server data warehouse, and that ensures that your data is using the most powerful tool for optimization, for retrieval. And it's that combination of ETL plus and SQL Server that really is the plumbing behind your analytics solution. And often it is that plumbing that can take a very long time to create before you actually start getting meaningful reports. With data itself, that plumbing is taken care of for. For you. Secondly, the actual BI tools that Data Self analytics leverages. First of all, we give you the option of using either of the top two most highly rated BI tools on the market today, and those are Tableau and Microsoft Power bi. And as you'll see in Johnny's demonstration, you'll see how those tools can be leveraged to empower executives and managers to run very powerful analytics on their own without asking someone else to create a report. And then finally, our third aspect underpinning the Data Self analytics solution is a report and dashboard template library filled with KPIs and fully configured reports and dashboards. As I mentioned, we've been at this since 2005. That library now consists of over 8,000 templates and KPIs that you can leverage. And the good news here is that not only are there lots and lots of templates for Sage 100, but if at some point in the future you should move to a different ERP solution, we've got the report and dashboard templates for those as well. You can use them, you can customize them, and as Johnny will show you, you can also create new reports and dashboards of your own. And so with that, Jonny, let me turn the proceedings over to you so that you can show the technology that I've been talking about.

Thank you, Don. Let me know. You can see again the diagram.

Sure can. Perfectly.

Thank you, Don. So again, this is Johnny Girardi, I'm Data Sales founder and CEO. I'm going to just start with the diagram again because I'm going to walk you through several technical steps of Data Self. I believe most of you are business analysts or SAGE consultants and you want to see under the hood how Data Self works. A couple of weeks ago we actually did a webinar focused on the business users, showing how they can access the reports and dashboards anytime and anywhere in making changes to those reports. But business users, you know, using the Tableau Power BI user interface. Well, today I'm going to show how the rest works behind the scenes. I'm going to show ETL plus, I'm going to show a little bit of SQL Server, I'm going to show a little bit of how things link behind the scenes on the server side and how easy it is to customize the solution. Very frequently when we deploy Data cell for new sage 100 well, guess what? UDFs, most of them, they have UDFs and out of the box we don't know what UDFs are available. So someone more technical, usually a consultant, usually a business analyst, can go to the Data Self backend if they have the right licenses, and easily add UDF fields, more tables and things of that nature. So today this presentation is for the techies. If you're a business user, it might be over your head, but if you're technical, hopefully you'll get a little bit of, you know, the pieces and I'm going to walk you through the whole experience. Not a whole, but you know, the main piece of the experience from the technical standpoint. And the first one is, well, how can I get Data Self started, right? So typically people contact us, we talk about your needs, we spec out the right version of Data Self and then the ball gets rolling. Typically where you can order Data Self like a free trial, you can click on pricing. There's a bunch of information here you can click Free Trial we always recommend to talk with your var Sage VAR talks with Data Self before you go for the free trial to be sure we'll put your company in a trial or in the actual deployment that is going to be successful for you. So we're here to assist, we're here to discuss your needs and then if it feels like a good fit and if a feed free trial is the right thing, we'll absolutely help you get through the pre trial. But anyhow, here you can actually order Data Self pre trial or not. And once you order, you receive an email with Instructions to install ETL+ in your system. And that installation will pretty much kick off the whole deployment of Data Self. And I'm going to walk you through what happens when you order Data Self, assuming you're going to be installing it. So for the next five minutes, very technical, a little bit of kind of it related how to install Data Self. So I'm going to switch gears now and I'm going to show a remote desktop session into a Sage 100 server. The job here is usually done by a Sage 100 consultant or someone that knows how to extract data out of Sage 100. I'm talking about ProvideX or SQL Server databases. Anyhow, someone in your company is going to receive an email that has instructions. Mostly you're going to go to our knowledge base where we have a bunch of documentation around how to deploy Data cell for Sage 100. For instance, if you say search for ETL for Sage 100 and just search on the knowledge base, going to get a few articles. If you have seen the providex, you click here. If you have SQL, you click here. Lots of companies have providex, so I'm going to click on that one. Usually we budget one hour or you should budget one hours to deploy Data Self on your network. And usually most of the time, most of the hour is around security settings. We're very concerned. We're very focused on being sure your data is securely transferred to our cloud and that your people have secure access to your data. No one else will. We don't cut corners. We use AWS and Azure as our cloud framework. If you want to have everything deployed behind your firewall, we also can do that. Most clients are using our cloud framework and again, security is very critical. Anyhow, today my presentation, I'm not going to go into the security part of this deployment because usually it is involved to be sure everything is done right. I'm going to focus on the actual BI pieces of the problem, getting the data of the Sage, putting the data warehouse or fetching it and doing reporting anyhow. So here there's a bunch of instructions that you can read to see how it works. What I'm going to do is pretty much going into how to get ETL deployed on this server. I'm going to click Deploy ETL Deployment Instructions into my Sage 100 server and I just want to download ETL and get the job done. If you read all what I'm going through here is going to be what I'm going to be doing now manually and the instructions are documented, but I'm going to do it Manually without reading because I know what to do. So I download ETL Plus. We have for pervasive two different bit versions, 64 or 32 depending which one you have. You got to choose the right ETL version. Most people have 64 nowadays. So I'm going to download that one. Simply download ETL plus. Super simple. You're going to get it. It's a standalone file. You just run ETL+ on your Sage 100 data server or on another server that is connected to your sage 100. You don't need a new box, you don't need new hardware, you don't need new software. It's a very clean, lean, mean application as you can see. 15 megs simple. You need pretty much one CPU core, half a gig of RAM, a gig of disk space and you're golden. It's very simple. Now how can I get this running in deploying data self? Well you double click it, you accept it. In a real production environment we're going to have some IT steps. You're going to be creating your email and password, which I already did. Security has already been taken care of. We also go through an IP to IP white listing to create a tunnel between your SAGE server or your company's server in our AWS cloud. So we go to that security setting and again that's usually where most of the time is spent with your IT to be sure secure has been taken care of. Once those pieces are in place, you're going to be logging to your ETL entity. So when you log in with your credentials you're going to get into a pre configured ETL system for your Sage 100. I'm going to show high level what this is. On the left we have what is called the ETL sources and pre configured we expect that you have sage 100 and you're also going to have your access to your data self data warehouses. So by default you already have two sources pre configured for your data analysis. The center panel is what tables from Sage 100 that are pre mapped out of the box. And if you look at this list here you can tell there's a bunch of tables that are very popular for reporting AR tables, bill of materials per manufacturing. If you have GL I am and whatnot. And on the right there is SQL statement that we'll be talking in a minute. Now how can I connect ETL to my sage 100 that is running on this server? Well you select the sage 100 in my ETL source, you click the properties icon and it's going to ask me, you know and I already defined this is an odbc mini probiotics sage 100. Which odbc should I read sage 100 from now? What the heck is that if you don't know? Well that's why I mentioned it must be involved or maybe your sage on the VAR because the provider called the BC and would be SQL server. If you have your 100 SQL server. It's a very standard way to pull data out of Sage 100 and I'm not going to show that because that's again a security setting. It is very common. Again if you have sage 100 resources or if you want to use our consulting services to help you with the ODBC connection. But it's a very standard procedure. I'm just going to pick one of the ODBCs available in this case in my sample database. I'm going to use this one and when I click connect, bingo. That's it. ETL plus is connected to my Sage 100 and if you see on the left now all SAGE on the tables are available to be extracted. This is just show me what's available. As I mentioned out of the box we already have mapped many of them. Let's say the vendor table. When I select the vendor table in my pre mapped table it shows me which columns have been pre configured. This is what is going to be extracted out of the box. If you want to take a look at the actual data you can click a preview or right click on any of these tables say preview and then it gives a preview of your data. In this case right now, my sample data. Anyhow that was it. I just connected, I downloaded TL connected to your Sage and now what? Now I need to load the data to data self cloud. How can I do it? I can click on the jobs. Jobs is the second solution that allows us to load the data to the cloud and if you want to run it right now. Sometimes people decide to do the data load overnight out of business hours because Sage might be super busy. But you can simply click the run job button and it will go through a whole procedure that will pick all the tables from preconfigured data box solution going to start loading them into the data warehouse and that's it. At the end of this process your data self analytics with a data warehouse with ETL plus and dozens sometimes hundred thousand reports and dashboards will be live with your data. So deploying data self analytics again I didn't go to the security side. That sometimes takes half an hour Take an hour, sometimes even take days. If you have a very crazy security setting, no problem, we handle that. And once we get over the security setting, the actual connection, loading, refreshing, securing is all done in a few minutes. Now data load, if you're looking right now, there's a bunch of tables that's kind of loading. There's tableau, data warehouse, Everything is loading behind the scenes into the cloud. If you have lots of data in your sage 100 by the way, if your sage 100 is in providex, usually in minutes up to about an hour, we do the first load, meaning all the data that has been pre configured goes to the cloud. And just to show here, if I click the log I'm going to see what happened with my extraction. It shows in my daytime all the tables in how many rows were loaded. Again, this is a sample data, so there isn't a lot of data in my sample data box. But if you have ProvideX, even if you have lots of data, usually under one hour, the first load will take place. If you have SQL with a lot more, it might be over an hour, but usually it's under one hour to do the first load. What happened after? Well, we can go to the job configuration and tell how frequently we can refresh your data. It can be once a day, every night, it can be every hour depending on the settings you can go every minute. We can easily configure we meaning we can teach you how to easily configure the data refresh. Now what if you're worried about taxing your sage 100 when we're refreshing the data? Well, good news. Let me show the invoice header table that very often Let me show actually the glass posting table. This table from some customers has millions of records. We have some companies with that have tens of millions of records. And yes, the first load, it can take a while because you may have lots of data. But after we do the first load we have special load types to only refresh deltas. In this case we're using the replace meaning on an ongoing going basis only add records since the last refresh. So if the first load has 10 years of data with 10 million records, yep, it might take a while. Next day when you run it again, it only brings records since yesterday meaning the ongoing refresh is super duper fast. Using the special load types here, we can usually make refresh run usually in minutes after the initial one. The ongoing in minutes no matter how much data you have. All right, now that I have loaded the data, how does it look in data self, I'm not going to show the out of the box reports and dashboards because that was the gold up last section. I'm just going to show here in the web portal which I'm showing the tableau web portal how to create a new report and how to customize it. So I'm going to say create a new report. I'm going to put my sales invoice which is my sales data. And right here I have my sales. I want to see sales by year. I want to see my customers just drag and drop. And I put my sales data Right now I built a very simple report showing sales by customer that I just pulled from my sample database. All interconnected. Now what if I want to add some UDF here you can see if I search for UDF, there are no UDFs. How can I add UDFs to my data sales system? I go back to my server. In this example, I have a few UDFs here, a list of UDFs. Sorry, where's my UDF list? There it is. I want to pull this two DF fields from the customer table all the way into the data self. How do I do it? I go back to my ETL, I go to my customer table, I add the UDFs, I save it. So just add a UDFs. You can add UDFS all over the place. All right, now refresh it. I'm going to say just refresh it again. Now this is going to go over again. Refreshing everything. I could have just refreshed the customer table in just my sales information in my tableau server. But right now I'm lazy. Just refresh everything. I don't have a lot of data. It's going to be easier to do that than manually checking one. Refresh that table only going to the other one, refreshing that only. Right now I'm just taking the easy route because I don't have a lot of data in my sample database. So this is the second refresh as it's loading. By the way, let me just show the log. In the log if you notice before everything when it was loaded, all the tables had loaded, meaning initial refresh, everything loads completely on the second refresh. You can see that some tables they have replaced. Meaning for this table invoice issue detail that I could have millions of records. It only brought records since the last refresh. So that's why I was talking about the delta refresh being super fast. All right, looks like the refresh has finished. This refresh role here completed in 57 seconds. Now if I go back to my tableau that before I didn't have a UDF and I say show me again the latest data that I loaded to my cloud. And there it is. UDFs just appeared in my table report. And now if I'm going to see my reports by customer category, I drag and drop. These are my customer categories. Put my market drag and drop. That's it. So what I showed you now in a few minutes was pretty much how to use ETL to load the data to a SQL Server. By the way, do you want to see your data in SQL? Let me show you here. Now I'm opening the SQL Server Management studio and if I reload my SQL Server, I'm going to see all the Sage tables that I just refreshed, loaded into SQL Server that are then connected to tableau and then showing the reports and dashboards now in this report here, that is in tableau, the web server that I just created. What if I want to save this report and automatically share with my users? So I just created the reports from scratch. I'm going to click Publish, meaning make a new report. My first report. I publish my first report. Ah, beautiful. I might not be able because maybe I didn't provision space. Let me try again. Yeah, I didn't provision enough space for my, my demo box. But anyhow I do. I would have saved this report as a new report. I'm going to show now I'm going to go into a demo box, not the one that I was using and showing a kind of a more flashy out of the box dashboard. But suppose that this dashboard here was actually this report here that I just saved. How can I save, how can I take any report in data, self existing or new one, and share with other people? You go to the report and then you click watch, click Subscription. Subscription means report distribution schedule. You choose the subscriptions you add here who should be receiving this report. When you get Clark, I'm going to get Zach, I'm going to add myself. So you just pick the users that will be receiving this report. You tell that you want to distribute as a PDF and then you say how frequently you can distribute the report. I want to send monthly on the first day at 6am Pacific and I click subscribe and that automatically on this schedule, all the users that are selected here will get a PDF report automatically in their inbox. And that's the power of the whole. Deploying, customizing, refreshing. And as I mentioned before, if that system is going to be refreshing automatically and with ETL plus You can even install ETL plus on other computers on your own computer. And if you want to refresh Data Self remotely, you can open ETL+ anywhere that you have ETL+ and tell to refresh the job in whatever the ETL is on your server. When you tell refresh now even if you're sending this command from the beach in Hawaii is going to tell our cloud is going to tell on your server that ETL plus load the job. Now in a few minutes you get the latest data. That's pretty much my presentation. I hope you have enjoyed. Don, back to you.

Thanks very much Johnny. Let me once again share my window and we'll just put our wrap on what we've talked about. You know I want to share a personal story here in that Johnny, when you first showed me Data Self and you went through these similar steps to actually have Data Self from installation through initial configuration to being able to create meaningful analytics off of the Sage 100 database within I think it was either 10 or 12 minutes. I remember I timed you was was most impressive. So let's just finish up. I know Johnny, you touched upon pricing. Very important for folks you can try out Data Self before you make the decision whether to purchase, go to the Data Self website. That trial period is good for 30 days. If you need help setting it up, we're here to help you. And you can get started with Data Self as well for as little as $399 a month as you can see from the advanced edition over on the left left hand side of the current slide. Also very importantly, we do have year end promotions, a number of them including if you decide to purchase Data Self, we'll give you one month subscription for free. But that's just one of a number of promotions that are available. So please do reach out to us and to our website for details on some of those other promotions. Jonny, that brings us to the Q and A session. I haven't seen any questions come up thus far from our audience, but I do have one of my own because when you talked about scheduling reports for distribution, as you know my background is in the alerts workplace and so can you tell me, is there a way for Data Self to be able to send alerts to people when certain KPIs or thresholds are met?

Yep, absolutely. Both the Tableau and Power BI user interfaces, they have very elaborate yet easy to use alert engines where actually a business user, you do not need to be a consultant, you do not need to be a technical person. A business user can take a pre configured report and select a measure and define if the measure goes above or a certain threshold. Send an email to whoever they want to send to. So absolutely alerts is part of the functionality and it's fairly straightforward for business users to set them up.

Perfect. Thanks very much Johnny and I don't see any further questions coming in through the web session. I know we're up against our 30 minute timeframe, so on behalf of Johnny Girardi and myself, Don Farber of Data Self, like to thank you all very much for joining us. I do want to remind you that if you'd like to see more examples of Data Self dashboards and reports coming off of Sage 100 and other examples including Executive Self service analytics, please go to the dataself.com website and you'll see additional recordings, including business level recordings of Data self for Sage 100 that goes into much more detail in terms of the available out of the box reports and dashboards. So without any further ado, thank you all very much for your time and interest today. We very much look forward to working with you and I hope you have a very happy holiday season. Thanks everyone. Take care.