Tag Archives: SQL

Getting Started with Big Data in a Small Business

Data Analyst Sheriff Badge
Better data leads to better decisions, especially in supply chain and operations. Unfortunately, most small businesses don’t have enough analysts (or any at all) who can comb through databases to provide data-backed recommendations. In fact, that number crunching responsibility often falls to the operations team, who may not have much experience analyzing big data. Perhaps you are in a similar position. You recognize the need for more data in your decisions, but accessing that information seems a bit daunting. If so, then here are three easy levels of data-crunching proficiency that will help you earn your data analyst sheriff badge.

Level 1 – Pivot Tables

If you need to analyze a large, pre-made table of data, then you should to start with a pivot table. Microsoft Excel’s pivot table tool is the data analyst’s faithful and reliable six-shooter. It can handle almost any problem a set of data brings your way. Pivot tables allow you to transform a set of data into an easy-to-rearrange and organized summary grid. The grid of information it produces helps you quickly find patterns, trends, and outliers. In fact, when you hear the words “slice and dice,” you should instantly think pivot tables.

Pivot Tables in Excel

Most people I’ve met who have yet to use pivot tables just don’t know how to start. The easiest way to learn is to just jump in and play around with one in Excel. You can find lots of good online tutorials and videos that will get you up to speed with pivot tables in less than 15 minutes. For example, here’s a seven-minute YouTube video that does a great job at walking through the basics: Microsoft Excel Pivot Table Tutorial for Beginners – Excel 2003, 2007, 2010

Once you feel comfortable manipulating data someone else has provided, then you can take the next step and access the data yourself.

Level 2 – Database Queries

For many small businesses, the challenge with data often lies in how to access your own database. Depending on your accounting  or ERP system, the exact technique will vary. However, you can usually access most small business systems easily through Excel’s Microsoft Query tool. For your exact system, run a web search for “how to access QuickBooks database in excel”, replacing “QuickBooks” with your own system’s name. Most systems will have an ODBC driver, which basically means it has the ability to be queried by outside programs such as Microsoft Excel.

How to Access your Database if it has an ODBC Driver

If your system uses an ODBC driver, then the query process is straightforward. In Excel, go to Data > From Other Sources > From Microsoft Query

Bringing Data Into Excel 1

A list will appear of available databases. Choose the one you want to query and press OK. Below I chose the connection for our Sage 100 database (formerly known as Sage Mas 90 or 200). If you don’t recognize the name of your database, you may need to search the web a little more.

Bringing Data Into Excel 2

Next, you’ll want to select the fields and tables you want in your report. You’ll need to pick fields from tables that are linked together in order to pull the items correctly. It may require some trial and error, as well as some research into how your database is structured.

Bringing Data Into Excel 3

After selecting the fields, the query wizard gives you a few options to filter and sort your data. However, you can access more advanced query options by manipulating your query in the Microsoft Query program. On the last page of the Query Wizard, select that option and press Finish.

Bringing Data Into Excel 4

Inside Microsoft Query, you can add additional filter criteria, table links, and column labels.

Bringing Data Into Excel 5

Once you’ve fine-tuned your query to just what you want, click the “Return Data” button (the one with an arrow pointing to a door by the “Save” button). This will bring the data you’ve selected back into Excel.

Once I learned how to access our company’s database, I began running dozens of queries each day. This helped tremendously, and I learned a great deal from the data. However, I eventually reached the limits of what Microsoft Query could offer. To go further, I had to learn a bit of Structured Query Language, or SQL (pronounced “sequel”), to get the answers I needed.

Level 3 – Advanced Queries with SQL

For some strange reason, I was more eager to learn French in high school than computer code, despite the fact that I query databases much more frequently than I ask which café has the best baguette. Luckily, SQL is very easy to learn (there’s definitely no conjugation or verb tenses).

Although Microsoft Query and Microsoft Access have great query-building interfaces, knowing a bit of SQL can empower you to do much more. SQL functions allow you to manipulate or summarize data as you pull it from the database. SQL also will allow you to make advanced links that might give you data hard to get at any other way. Accordingly, even a little SQL will quickly empower you to more efficiently and effectively get the answers you need. SQL is also the language you’ll use to connect your database with most outside SaaS modules and programs. Knowing SQL can help make implementing such add-ons much easier and cheaper.

The best part of SQL is that you can learn most of what you need to query big data in an afternoon. I recommend Sams Teach Yourself SQL in 10 Minutes. Each lesson actually takes about 10-15 minutes, and after about twenty lessons, you’ll know everything you need to build advance database queries. The book also starts at the very basics, which is great for operations people like me who don’t have a computer science degree.

No matter what the next step is, each of these levels will help you to make data more accessible for your company. With more data, you’ll quickly be making better, fact-based decisions to meet your goals and improve your supply chain.