BI Performance - DataSelf / Tableau

Hi, I'm Johnny Giuardi. Let's talk about reports and dashboards. Performance very common complaint, it just takes too long to refresh reports. They press the refresh button and good luck. And what is too long? Well, some people talk about minutes, others about hours and sometimes even days. I've heard many stories that people would have to run reports overnight because it was just so slow. And when they ran reports it would slow down their source system so they only could run reports overnight. So fairly common complaint about people on the report side in our experience, actually we have the 10 second rule. If a report takes more than 10 seconds to run, you know, when you press the refresh button, a lot of business users will just not wait. People no longer have patience. They have a lot of things to do. And waiting for computers to run things, it's something that people are getting less and less patient about. So for us, you know, more than 10 seconds to run a report, it becomes something that is usually highly undesirable and not acceptable. Now when I talk with people and I ask why are reports taking so long? The main culprits are people say, hey, I have a lot of data. And I ask, okay, how much did you have? And people say, oh, I have millions of records. Some people say I have tens of millions of records. Some people say I have hundreds of millions of records or terabytes of data. So large data volume is usually a main culprit that people say, why the reports run so slowly? Other people say, well, you know, when we link the data, the different tables is just a very complex table system linking and it just runs slowly. That's life. Sorry, it's slow. Other people say, well, are the complex metrics, they have very complex calculations to be processed and when they ask to run the report, it just takes a lot of processing power and time to do the job in all of those things in many organizations are part of the reason why reports run slowly. Now there is like a magic wand for performance when it comes to reports and dashboards. It's amazing, but it's like a magic wand. The first thing is, well, you have to have the right BI platform to tackle really performance issues. If your reports are running slowly, the first thing is probably the BI tools you're using are not appropriate to your BI needs, meaning data volume, complexity, table link, whatever it is, you're probably not using the right tools and or you don't have the right BI expertise to use those tools to do the job. If you have the right BI platform and if you have the right BI expertise. Guess what? The magic one happens and pretty much you can guarantee that when you press run a report they'll run under 10 seconds. So it's like a magic wand, you know, sometimes it's not possible. There are some technological limitations to run some reports, but the great majority, usually 100% of reports should run under 10 seconds if you're using the right BI platform and you have the right BI expertise to put the pieces together. So let's take a look at data self analytics, how we do it and how we tackle performance overall. I'll show the architecture and then I show a little demo on how to the system works. So right here I am@the dataself.com website. I went to solutions and I chose architecture. In our Dataself platform we actually use enterprise grade BI components to really deliver on speed and ease of use. We use SQL Server data warehousing technology and then we use Tableau or Power BI for analytics. And by knowing how to use these tools, there are so many pieces of this puzzle that when you fine tune it, you can guarantee the speed of being under 10 seconds. When you run a reporter dashboard, we have clients where the tables bringing from different systems are several hundreds of millions of records and and we still deliver reports in seconds. So anyhow doesn't matter, you know, table links doesn't matter how much data you have, doesn't matter how much how complex your calculations are. As long as your team knows how to tackle them using data warehousing analytics tools, you can deliver you know, super fast speed. We have clients that came back to us and complained, you know Johnny, data self speed wise is super slow. It's taking hours to run our refreshing reports. And we had one particular client that when they told us that that complain they just had upgraded all of their servers to run things faster. So they got brand new servers and pretty much the overnight refresh, you know, when we do the data crunching overnight it was taking over 15 hours to process which was really super slow. And when they upgraded the hardware to the latest and greatest they brought down to 12 hours. So they saved about 20% of the time by bringing a much more to bring a much faster server. And then we went over there and we fine tuned the process, we optimized the system for performance for the overnight refresh and we brought down from 12 hours to 2 hours to refresh the whole data self system. So we spend about half the cost of the new server that they purchased. So if they have asked us before to fine tune the process before they have purchased the servers. Maybe there's no need whatsoever to install the servers. Now you might be wondering why is still taking two hours when I'm talking about 10 seconds. Well, data crunching, especially when you have large data volumes, usually you have to do on a batch basis. And when you do this batch basis data processing, usually overnight, we take all the data, we link all the tables and we do all the pre, most of the pre aggregation of your metrics, let's say on a schedule basis. So when you press, when you, when you open the report next day and you press the refresh button, there's no need for calculating things, it's all ready to go. You just take a few seconds to show the data. So data crunching on the server can take hours, but it's usually done overnight. And while during the day, you know, reports will run quickly, some of our clients actually need to see reports run being refreshed from the Source system every 15 minutes, every 10 minutes or even live. And yes, our framework is an enterprise level framework that we can do partial refreshes, let's say for smaller tables, every 5, 10, 15 minutes or even less. And we can also connect directly to the data source. So let's say in a dashboard you might have several quadrants that are bringing historical data with a lot of data. So it's only refreshed overnight. While some of the quadrants, let's say quantity on hand, open sales orders into that nature, it can come live from the source system and then you have a blend of historic analysis and live analysis together in the same dashboard. And everything will run under 10 seconds once you put together the right components. So by using data warehousing and enterprise level analytics, we can deliver on the promise of delivering reports and dashboards under 10 seconds. Now let's see an example. Now I'm going to the data self analytics platform and I mean my data sources, I'm going to start a new report from scratch from my SA revenues data source. So just a new report from scratch and right here I'm looking at my dimensions and measures from this sales. Now this data source here is actually from like a. We're mimicking one of my clients that they have three different organizations actually with two different ERP systems that we need to blend their data together so they could see their data analysis. The total data once we blend together for sales analysis has more than 30 million records. So right now I'm going to be showing you an example of the performance querying a multi company system with more than 30 million records on the file right now. And I'm going to build from scratch using this decent size sample database a dashboard showing three pieces of information. I want to see by individual of this individual company. I want to see sales and gross profit. Then I want to see some sales details by customer this month and then I want to see sales by geography. So I'm just going to put together this dashboard from scratch and showing you that things runs in seconds even though I'm querying 30 million records. So let's get started. First thing I'm going to see is sales by company id. So right now for each of my companies I'm looking at sales and I also want to see gross profit from a color representation. So the gross profit is represented by the colors. And let me make this actually horizontal query. So that's my first report just showing by company sales and gross profit. The second report I want to see is I want to see sales by customer this month. So I want to see customer. These are my customers. I just drag and drop customer, I bring sales. So this is all the sales by customer. Who's my top customer? I just sort descending and these would be my top customers in a descending order. Well, I want to see sales this month. So let's take invoice date and put in my columns. So right there I see sales by year. Well, I want to go to the month level. I can drill down from year to go to the quarter level. I can drill down from quarter to go to the month level. And if I was looking at fiscal period monot by week by day I could go out that level of detail. So I'm looking at the whole company history, all 30 million record so of transactions. Well, I just want to see this month so I can bring my invoice date, put in my filters. So now I have a filter right here and I'm going to say show me relative date. So right now by default shows today sales but I actually want to see this month. So very easy to use this filter to choose this month, this quarter, this year. Maybe I want to see the last three months. There it is. Maybe on the last two months. So point and click, very very quick and easy. Now let me go back to only this month. What if I want to see also sales by product? Well you just find the product here and let's put product ID in my report. So there it is by customer, by product. I see sales this month. All right, they are pretty quick Dr. Drag and drop. The last part would be sales by state. So what I want to see is by, let's say ship to state. I also want to add, let's put here invoice size and sales. So I just pick those three fields and I ask the tool to show me this on a map. There it is. With a few clicks I see information on a map. Let's make the bubbles a little bigger. So bigger bubbles, bigger sales. And the bubbles are sliced by small, medium and large invoices. And I want to put all of these three views that I just created into a dashboard. So now I'm going to put just drag and drop. I want to see the map, I want to see the details at the bottom and then my other part right here, let's make this a little bigger. And that's it. Now this is very interactive. If I was looking at this dashboard, let's say on a mobile device and whatnot, I could use my fingers to just select some states in this map and it will automatically show at the bottom and change and filter those two queries to represent only in this case the monthly sale this month, sales, and also how each company is doing that particular area. And as you can tell, even though I'm querying 30 million records right now, on the fly, reports run in pretty much seconds. If you want to discuss your report and dashboard performance issues, or if you learn more about data self analytics, you can find here my contact information in our website. Thanks for watching.