DataSelf - Customizing Analytics for Sage X3

Hi everyone, this is Don Farber speaking on behalf of Data Self analytics. I'd like to welcome you to this webinar on customizing analytics for Sage X3. I am joined by Data Self founder and CEO Johnny Girardi. We'll be taking you through a presentation today that'll last between 25 and 30 minutes. We'll start out with an overview of the Data Self solution and technology and then Johnny will take us through the product in terms of implementing it and configuring it for use with Sage X3. And we'll also leave time at the end for any questions that anyone in the audience has for us. One last note, this presentation is for folks of a technical bend. That is we're going to be getting into the underlying technology and architecture and the steps required to set up Data Self analytics for clients. So with that, let's talk a little bit about the history of Data Self we've been around since 2005. As I mentioned, Johnny Girardi, who's joining us in this webinar, is CEO and founder. Ever since that 2005, our mission has been to enable clients to make more timely and more informed business decisions. Decisions out of their ERP circumstances, CRM and related solutions. We do that through a technology that's really a trinity of functionality. Starting out with a next generation proprietary data optimization tool. We combine that with the top most business intelligence solutions in the industry today and we complete that with a very robust library of sample reports and dashboards. So what is it that vexes Sage X3 clients today when it comes to designing and running reports? Well, the first issue is just the amount of time it takes to create those reports or to modify reports to individual specifications. Once those reports are created, often it takes very long for them to run a number of reports. You'll probably run overnight because it takes hours for them to complete. Complete with that is also the challenge of distributing reports. Very often individuals need to receive personalized copies of reports, such as different sales reps within an organization, and that can be quite time consuming as well. Lastly, challenges include blending of data from Sage X3, perhaps into a CRM or related application. And finally, last but certainly not least is that X3 users don't have a way to enable their managers and their executives to run their own reports, really on demand through something like a Google like search engine. So we'll talk about all of these challenges in a bit more detail. First, let me give you an overview of those three key components of the Data Self technology. And that starts with the underlying architecture, which is a combination of a robust Microsoft SQL Server data warehouse along with the Data self data self ETL technology for optimizing your sage x3 data. ETL stands for extract, transform and load. And the combination of ETL along with a SQL Server data warehouse ensures that you'll have the fastest, most highly optimized system for retrieving data and analyzing it from your X3 database. Second component, data self Analytics Embeds includes and supports the top two most highly rated BI solutions on the market today. And those are Tableau and Power bi. These really give you the assurance that whatever data you need to get access to to retrieve from your X3 database, you'll get it. And that includes an Analytics Self service component. We refer to that as a Google like search engine or natural language search technology that C level folks, managers, executives can use themselves can be empowered to do their own analytics. And the final component of the Data Self analytics solution is a very robust library of pre configured reports and dashboards for not just Sage X3 but for most other ERP and related solutions. And these pre built report templates come with the solution at no additional cost. You can use them, you can modify them and at current count they're over 8,000 of these pre configured report and dashboard templates that you can choose from. So at this point I'm going to hand over the proceedings to my colleague Johnny. He's going to walk us through an installation, initial configuration and the creation of a report all within our 20 minute time frame. And so with that Jonny, I'll turn the proceedings over to you.

Thank you. Don't can you hear me and can you see the website on screen?

We sure can. Thank you.

Thank you. Appreciate it. All right, again this is Johnny Girardi, Data Sales founder and CEO. And what I'm going to cover is exactly the content around what techies, business analysts or report publishers need to learn in order to customize Data Self extensively. And just showing again here the architecture that Don was showing. Most business users will be focusing on the on these two layers of Data Self, the ease of use and time to value. But a lot of the tech is actually they will be working a lot in the power step, being able to heavily customize the Data Self server side and tackling many of the challenges that quite often x3 presents when it comes to complex reporting. So I'll be showing how to deploy Data Self and then how tactics gets customized the back end. So first of all, to deploy Data Self I'm going to spend about five minutes showing that part of the solution. I'm not going to show today things that are lead security. You know, we use a very mature, insecure way to get your data from your Sage X3 in other databases into a SQL Server data warehouse that can be on premises or in the cloud, your cloud or our cloud, and then get the data from there into the next layer, which is usually Tableau, Power, BI and Excel. So I will not cover anything security and usually security is where most of the initial setup is spent, you know, figuring out the best way to secure the data. I'm going to be focusing primarily in the BI problem, how to install the out of the box and then how to customize it. So to deploy the data self out of the box, you know, of course you have to order contact your data self var or data self directly to discuss your needs. Once you place the order, you get an email with instructions to get your data self deployed. We can assist you, but quite often if you have it, you can deploy it. And that's what I'm showing in that email. Primarily going to go to our knowledge base and I'm going to show how you can search for the information. So just search for ETL for X3, going to get an article, go to that article, read it. You know, again, it's a fairly straightforward process. I'm just going to go straight and deploy it because I know how to do it. So I'm going to go straight to the instructions. I'm going to download this application, which is ETL plus, ETL plus, the application that pretty much drives the whole analytics behind it itself. So I just downloaded ETL plus now is on my computer, I just run ETL plus, I accept the eula. I'm going to enter my credentials, which is the email, going to enter a new password. Then I log in, sometimes I have to run it again. All right, so this is ETL plus already installed locally on my cert on a server where I have access to my X3 database. So I'm a client, I have X3 and I want to connect data self to my X3. Primarily here in ETL plus we have three panels. The left panel we define or you define where your data sources are. X3 is going to be one of them. Your data cell data warehouse is going to be another one and you can add more sources. The center panel tells what out of the box comes with data self. So if you see from the actual database we already have tables that are commonly used to do AP, AR, GL inventory, sales or Order purchasing, there's a bunch of things ready to go. And if you choose a given table, you'll see what the SQL statement is on the right panel. Pretty straightforward to understand. Is T SQL driving the process? Now my X3, if I want to configure to my own X3, I just select the X3, right click and choose Properties. So I want to select my own X3 database and here if you are a SQL person, you just pretty much put the connection information right now for my sample I already have pre configured in your case you'll be choosing the server authentication database and then just click connect and that's it. Now ETL plus is connected from a read only standpoint from your X3. All right, that was the first step. Now I just need to refresh it. I go to my jobs and then I tell to refresh it. And sometimes refreshing you have to wait overnight, you know, out of business hours because sometimes refreshing can take a lot of data from your system and maybe you don't want to do that during business hours. You can absolutely schedule when to run things. There is a schedule functionality built in right now because of my sample data is not too big. I'm going to just say run my refresh now when I click run refresh now what's going to happen is ETL plus is going to run through a procedure to pull all the data that is pre configured out of the box into the data warehouse. So as you can tell for every table like a texture has like 600,000 records being pulled locally and pushed to the in this example to the cloud data warehouse. The initial load can take quite a bit of time if you have lots of data. And I want to make a note, all the tables we have loaded for the first time, everything right now is running tableau processes, meaning it's taking the data from SQL and load it into a tableau framework for easy analysis. Now one thing that I want to mention before I go to the next step is this was the first load and if you remember the eight extra table that I mentioned, which is a fairly big in my sample database, I mean you know 600,000 records is not a lot but you know, it's not too shabby either. In data self the first load is everything but the next load uses what we call upsert. Upsert means update, modify records and insert new rows. So by default it comes pre configured to do upsert meaning the first load is everything. And if I go now to SQL Server that is connected to the data warehouse and it says show me my table that I just loaded. Now as you can tell, all the tables that were loaded in the previous steps are listed here in SQL Server. And if I were to run for the second time, you know right now I'm going to run again the same job for the second time and click run. What you're going to see is the load is much faster. The eight extra table that before imported 600,000 records, now it only brought two because there's only two records that have changed since the last time. So ongoing refresh dramatically faster because we only bring deltas on an ongoing basis. The good news is the deployment is done, the refresh is done. Now you can go to your business users and give them access to data self reports out of the box. Of course in the next three quite often there's some Quite often there are customizations required before you give managers information ready for all their analysis. But what I'm going to show you now is now that your tech user job of the point that itself is done how easy it is to data self web portal, in this case tableau and start building reports and customizing for users. Deployment is done and it took me seven minutes and I refreshed the data twice by the way. Now I go to a browser where I'm already logged into the tableau portal. How is it possible? Well you receive an email with instructions on how you can log into your tableau portal that will have your data refreshed by the process that I just finished. Once you log into the tableau portal you get to this homepage and I'm going to go to the explore and enter into the project that has the out of the box solution. And just here to show all of these dual cylinders they are we call data sources. Data coming from UX3 to analyze AP, AR, GL, inventory purchasing in other modules. So all of those things already ready to be analyzed. We also have workbooks and a bunch of things as Don mentioned, we have actually thousands of reports ready to go ready to be analyzed by business users. But today because you are the techies, I want to show you how to customize how to build a new report from scratch using the tableau web designer that is part of the data self complete solution. And I'm going to take the sales and create a new workbook. A new workbook is like a new Excel file. We're going to be building new reports from scratch and on this report designer that only people with access to the report designer can actually make changes. You'll see on the Left the out of the box structures for sales analysis. And as you can tell here we have business friendly names. You know in X3 table names and column names are pretty complicated for business users. Out of the box ready call customer id, customer name, item category, item description, sales amount and whatnot. So here is already friendly to business users and let's say if I'm a builder report showing sales by customer by year pretty much you and even users just need to drag and drop. The basics are very simple. If I want to see customer, I drag customer to my rows. These are all the customers. If I want to see sales, I find sales dragging my report. So sales by customer and I want to see by year I can take my invoice date and put in my columns. So super simple. Sales by customer by year. Let's make my year a filter to only show 2020. So make a little simpler. All right. This report is something that even business users can learn how to build drag and drop point in. Click now as a techie I realized that I need dimensions for analysis in many times out of the box. Many of the dimensions you have built in x3 are not there yet. How can you add a dimension into this whole framework? Adding dimensions is part of the work that you have to go back. Let me go back here to my to my server side and go back to my architecture. If you don't have a list of certain fields for analysis in your tableau power bi in Excel you the tech you have to use ETL plus in SQL to customize the power step to pull new tables and pull more columns. And that's what I'm going to show now how to bring a customer dimension that is not available out of the box. So I'm going to go back to my ETL plus I'm going to go back to my main page where it shows the ETL object and for my customer I want the CCE3 is the third dimension for analysis. And as you can tell right now we did not bring out of the box. How can I edit? You come to SQL statement and you just edit it CCE underscore three if you know the actually source database. That's how it's called. You confirm that's added. Now you just say load it. So I'm loading this field to my raw BP customer table and then I have to feed now the same field into my sales invoice. Big SQL statement. This SQL statement. As you can tell if you are familiar with X3 it's complicated. Yep, X3 reporting complicated it has salesperson table S invoice V. There's a bunch of things. The point is the BP customer is part of my framework. Then I'm just going to be adding to my SQL statement here. The customer CCE03 field. I confirm. And now I load this table as well. And I probably misspelled it. Sorry, let me just see if I misspelled something. Oh, I put zero. There's no zero. There we go. There's no zero here, just EC3. These are fun things of practice. Be sure you spell things correctly. All right, looks like it loaded. It was pretty quick. All right, now we go and we tell to refresh the tableau data source that has the latest step of the process. And once I finished, that should be it. It finished. I want to take a look, quick look at the log to see if everything was done. I loaded the sales invoice 3,000 records. It refreshed tableau. Beautiful. That was it. What I just did is I came to the back end. I told ETL to add a new field to the customer table. I got that field into the table that feeds Tableau. I refresh tableau and now I can go back to my tableau portal here, right? And if you see right now the CC is not. I cannot find CC here yet in Tableau because Tableau has cached my data. But if I tell Tableau to refresh this data, so forget the cache and reload it. CC3 was just being added and now if I put this part of my report, I can see now CC3 grouping as part of my reporting. So doing customization, the plumbing is, you know, if you know SQL and if you know the X ray database, welcome. The ETL plus will dramatically simplify the process to add tables, to add fields, to add joins, to add table unions, whatever you want and easily allow you to push those changes into the next layer to allow easy customization. Once you finish this report, if you want to publish, pretty much you click the publish button. I'm not sure if it's going to work because this is a test environment and sometimes in test environment I forgot to set up security. Let's see if it's going to happen. Oh, it worked. So test123, I was able to save this new report. I'm going to get out of the edit mode, which is mostly for Takis and I'm going to find look for the test 1, 2, 3 report. There it is. So this report was just created using the CC3, which requires some plumbing in all the customer sales in 2020. Now, if you want to automatically distribute these reports to a bunch of users on schedule, primarily you go to watch and you click subscription. And this can be done by users you don't need to call the tackies. And then you're going to put here who's going to be accessed in this report. I'm going to select myself and more people. I'm not sure if I have more people in my database, but I would add all the usernames here. I define how to distribute the report. It's going to be on a PDF and I'm going to say how often. Let's say I want to send this report monthly on the last day and I click subscribe and that's it. Automatically on this schedule. This report is going to be refreshed in email to all the people you added over here. And that's pretty much the presentation that I want to show you today covering what kind of, you know, job techies need to go through in order to customize the back end in the front end of Data Self to allow your business users to make more informed decisions. Don, back to you.

Thank you, Johnny, Appreciate that. And let me make sure that I'm sharing my screen with everyone. You know, Johnny, I have to, I'm going to have to change my presentation now because I promised people that they'd see the X3 implementation of data self in, in 20 minutes and according to my watch, you just did it in 17. Very impressive.

In real life things take longer because security lots of data and actually is usually highly customized. Takes a lot more work in real life. But what I tell people is when you compare Data Self analytics versus competing reporting platforms in competing BI solutions, we have made BI much easier and faster. It's not easy, it's not fast. No, but it's much easier and faster than competing solutions. We really take BI to the next level.

I would have to agree. You've taken the scariness of BI and made it digestible. So let me finish up our presentation by talking a little bit about pricing and where you can go from here in terms of getting additional information. Data Self has a number of different license models that you can choose from. Just want to share a couple of the very common ones with you on the current slide. Advanced Edition allows you to get started with DataSelf and your Sage X3 system for just under $400 a month. There are some additional additions for clients using perhaps multiple applications, multiple companies within X3. Maybe you want to bring in some Excel spreadsheets. Maybe you want to bring data from a CRM solution, so please do contact us and we can provide a quote for your particular implementation. I also want to mention that Data Self is currently having a number of year end specials around our licenses, a number of different offers, including one for a annual license subscription that gives you an extra month for free. So basically pay for 12 months and get 13. That particular promotion is good for both the advanced and Pro or professional editions of Dataself. But by all means please do visit our website and contact us to learn about all of the promotions that are available. Also, very importantly, you can try out Data Self before you reach a decision whether or not to purchase. So just go to the Data Self website, there's a free trial full function available for you. And of course, of course we're here to help you get that trial up and running. And so with that, let me just take a quick moment to look at our chat sessions to see if we've received any questions. Jonny, I do see one question that's come in and it's on a subject that's very near and dear to my heart, which is alerts. Can alerts be set up to be triggered off of Perhaps thresholds or KPIs that are met within the X3 system?

Yep, good question, Don. Absolutely. Both Tableau and Power BI and even SQL, but Tableau and Power bi, they have robust and easy to use alerts functionality. Actually, many business users learn how to set up alerts themselves. Primarily they take a report. You know they take a report, they select what measure they want to build an alert on. Let's say sales, profitability, expenses. And they say if the value goes above or certain threshold, send an email to someone, to myself and other people. So they pretty much can do this by themselves. Save it and the alert functionality becomes part of the Data Cell framework.

Perfect. Thanks for providing that answer. And so I think that brings us to the end of this webina webinar on Data Self analytics for Sage X3. This presentation did focus on some of the technical or more technical aspects of actually setting up Data Self for use with Sage X3. Hope you all found that useful. If you'd like to view a past webinar that talks more about the business uses of Data Self analytics, including going into the application and looking at oh 3, 4 or 5 different dashboards and reports, please go to dataself.com you can go to the Sage specific page and view those webinars on your own. They each run about 20 to 25 minutes in length, so with that again. My name is Don Farber with Data Self analytics, and on behalf of Data Self CEO and founder Johnny Girardi, I'd like to thank you very much for your time and interest today. We very much look forward to working with you. Thanks very much.