We've covered a lot of ground in our module on analytical technologies. We've talked about data storage, including a detailed look at the relational database. We've also outlined a number of different types of tools that exist in the world of data and data analytics, both traditional and emerging, including exposure to ideas like big data and cloud computing. In this video we're going to take a look at how data analysts actually use some of these tools. For now, we're going to do this at a really high level. You'll have plenty of opportunity in later modules and courses to go deeper and do exactly how certain operations and analyses are done. There are a lot of different tools and techniques out there and we couldn't possibly cover them all. However, we will cover just a few common ways analysts work with data and how one might think about using one type of tool versus another. We'll start by looking at three broad methodologies a data analyst might employ to access and analyze data. Let's call the first method the intermediate file approach. In this approach we extract data from a database or other location where data is stored, and we export the data we need into a standalone file like a text file or Excel file. This often involves writing SQL code against the database to extract just the data we need. We then import the data into an analytical tool like Excel, a business intelligence tool, or a statistical software package or programming environment. Once the data is in the analytical environment I can execute whatever type of analysis is desired. Note that this approach assumes that all the data I need is already integrated in one database environment. So why might I use this approach? For starters, you might not be able to connect your analytical tools directly to your database due to security or stability concerns. Or you may not have time to set up all the permissions and network connections required to make a direct connection. It might be the case that you need to extract the data at one point in time and analyze it later or analyze it offline. You might also want to drive the same data set into multiple analytical tools. In these cases, it's more convenient to store the data in an intermediate file and import it for analysis. Finally, by breaking access into two steps I have a little mores control and visibility into each steps, which might be useful for data validation and quality control. A second method might be called the direct connection approach. Wth this approach we connect our analytics tool directly to a database or other data source using what's called an open database connectivity, or ODBC connection, or some other application program interface or API connection. Broadly, APIs are standard mechanisms for exchanging information between programs, and ODBC is one special case of an API used to connect to databases. Most analytical tools have the ability to connect directly to the most common database systems and a number of other common data sources. In this approach, I use the analytical tool interface to set up a connection and identify the data I want to access. Usually this involves the same ideas as SQL queries on a database. The tool executes the extraction operation in the background, and that can proceed with whatever analysis is desired. There are a couple nice things about the direct connection approach. First, it cuts out a couple of steps in the process since I don't need to export and import data using an intermediate file. Secondly, a direct connection can be set up to automatically refresh as the underlying data changes. Meaning that my analytical work can be refreshed as well to reflect the most recent information. On the downside, it can require a bit of set up to establish the actual connection to the database. And it's a little bit harder to be sure that the extraction process is happening as intended. Offline analysis can be a bit trickier as well as we need to make sure we have a copy of our data stored locally. The last method we'll cover is what we'll call the downstream integration approach. It's quite often the case in data analytics that the information we need is located in a bunch of different locations and formats. While it would be nice to have everything on one warehouse, it doesn't always makes sense to spend the time and energy to put everything there prior to analysis. Especially if we're not entirely sure those sources will turn out to be important. The newer and more complex the analysis, the more likely it is that we'll need to do at least some integration in the analytical environment. In cases like this, we generally use a broader set of API and ODBC connections as our analytical tool of choice to connect to several sources concurrently. And use additional functionality in the tool to integrate the data construct analytical data sets. We then proceed, as we would, using the other methods. Of course, there are a number of other approaches that we might take when working with data to get the results we're looking for, including hybrids of the ones we've discussed here. For example, I might perform certain manipulations in Excel, and then import the results into a more sophisticated analysis tool. I can also do the opposite, using an advanced tool to isolate some set of data that I want to incorporate into Excel. Perhaps into a business or financial model. The approach you take in any situation will depend on what it is you're trying to do. But one thing we haven't really discussed is when you'd want to use one tool versus another. This is a really complicated question and the answer depends not only on the capabilities of the tool itself but on the skills of the analysts, the nature of the data environment and even the organization in which an analyst works. In our video on data and analysis tools we broadly discussed what functions each type of tool is designed to perform, but we also saw that there's quite a bit of overlap in the capabilities of different tools. What one analyst finds really easy to do in one application another analyst might find more intuitive in a different application. That having been said, here are a few ideas that you can start with that I've drawn from my own experience in leading analytical teams. But you'll have to discover what works best in your environment. Let's start with Excel. Excel is really great for quick and dirty analyses. Basic charts and graphs are for when you want to share your analysis with business partners who don't have access to more sophisticated tools. Excel also makes certain types of manipulations really easy, like calculations that depend on multiple rows of data. And it's a great environment for trial and error around really complicated calculations since you can see every formula in every cell. It also turns out that an awful lot of financial modeling and business case development is done in excel. When analysis outputs are used as the inputs to these Excel models, it can be convenient just to start in that environment. On the flipside, Excel is not necessarily a great tool for sharing data broadly or for developing standard reports or dashboards. There's also a size limitation. The baseline Excel product can only handle about 1 million rows of data, which may not be large enough for some analysis. Although with Microsoft's power pivot plug-in, larger data sets can be handled. Even with less than 1 million rows, performance can be sluggish on anything but high-end machine. Let's move on to business intelligence tools, which includes standard reporting, data visualization, and data exploration tools. These tools are a good choice for a wide variety of analytical needs intended to make complex manipulation of data easier and faster than other tools. It goes without saying that the analysis requires extensive exploration or advance visualization techniques. Tools suited to those operations will produce better results. Additionally, some organizations will have tools that permanently sit on top of a data environment and provide pre-built data structures like cubes, or predefined calculations that facilitate repeated analytic operations. Business intelligence tools are also preferable in cases when the output of analysis will be shared broadly or turned into a standard report since they typically include more advanced scheduling distribution functionality. Statistical modeling and advanced programming tools are the obvious choice when we need to do highly sophisticated analysis, especially using advanced analytic techniques. There are ways of incorporating more advanced techniques into both Excel and other applications using plugins or complimentary applications. In fact, we'll be using some of these in later courses. However, few of these work were really well at scale, where we need to analyze very large datasets, or where we need to drive the results of our analysis back into business operations. There are also cases where we want to have a really high degree of flexibility, where we need very fast performance or where we want to build analytics directly into other software or data processes. In these cases, we might want to code our analytics from scratch using a programming language adept at performing data manipulation. The downside of this approach is that we have to build things from scratch. We give up a lot of the simplification that's provided by more user friendly tools. So what should you take away from this discussion? First, you should recognize that there are a lot of different ways of navigating through the analytical process. But there are a few common ways that we'll apply more often than not. Secondly, when it comes to tools, there's always more than one way to accomplish anything. But there are some broad considerations that can help you pick which tool is right for the job. But like we said at the beginning of this module, the most powerful tool you have as a data analyst is the one right here between your ears. If you have a solid thought process and a good foundation of knowledge about your data environment, you'll be well down the road to success regardless of the tools you choose.
