Guest post by Barrie Pike, an analytics executive based in Redmond, WA.
I have relayed this story to many of my colleagues in the world of business analytics and without exception they always nod their heads in agreement; at some point the limitations of spreadsheets and basic business intelligence tools combined with the ballooning quantity of raw data forces analysts to migrate to the world of grown-up databases and the adoption of SQL.
So, how did I end up discovering SQL, realizing the need for a SQL IDE, and acquiring basic query writing skills?
Technical Limitations
Whether they be desktop or online versions of their spreadsheet software, the most popular vendors have imposed technical limitations on their products e.g. Excel can handle just over 1m rows, Google Sheets has a limitation of 197k. However, it’s not only the software that can limit you. The power of your computer and its memory configuration will also max out if you populate the spreadsheet with too much data or introduce some processor intensive calculations within it. Furthermore, the more you expect the spreadsheet to do for you, the slower it gets.
With some of the analysis on point-of-sale data that I was attempting to execute in one of my spreadsheets I quickly hit limitations and performance problems. I found myself wasting hours when doing something as simple as loading 400k rows of data into a spreadsheet tab.
I therefore began looking for better ways to ingest and manipulate data.
Simple Desktop Database
My first step to a better experience involved teaching myself one of the free desktop databases. As I already had a Microsoft Office license I simply clicked on and loaded up Microsoft Access.
My initial experience was very encouraging. I was able to prepare and ingest data in different formats and do so in a drag and drop or wizard driven way. I was also able to link different data sets using common keys and to create quite complex queries. I also bought myself a teach-yourself SQL book and began to ‘look under the covers’ at what SQL was being generated by the drag-and-drop queries I was creating in Access.
This was great until I started overloading my database with too much data to the point it began to grind to a halt. Furthermore, I was not able to create the type of analytics and visualizations in the desktop database that are possible in a spreadsheet. If I exported a significant number of rows from my Access database with the intent to create the reports and visualizations in a spreadsheet then I just found myself bumping up against the previously described limitations of that technology.
Pre-built Data Analytics
The next step of my journey involved taking more of a look at the data in the applications that our IT department had pre-built and made available to us via a Business Intelligence (BI) tool.
My initial experience was also very positive. Our IT department had done a great job joining all the data together and made a very good attempt at creating the analytics, insights, and visualizations that they believed would answer the questions to meet our business needs.
However, our business needs were constantly changing, and the questions being asked by our customers were very ad-hoc in nature. Also, while the data was very well structured it was not very personalized to my own sphere of responsibility and didn’t match the exact way that I wanted to analyze the data.
In order to perform my role as a business analyst I therefore found myself once again having to export data from the BI tool and back into a spreadsheet so that I could merge it with my own data view of the world and answer our ever-evolving set of business questions.
I also discovered that there are limitations on the amount of data that you can export from some BI tools, so I again found myself just bumping up against the previously described limitations of the spreadsheet technology.
The Leap to SQL
My teach-yourself SQL book and some basic online videos had given me an understanding of the true power of databases and their ability to manipulate and query data. So, I approached my manager and got approval to attend some in-person and online training courses. After that my manager got approval from IT to grant me a license to Coginiti SQL software, read-only access to some critical data sets, and a workspace of my own where I can store ad-hoc queries.
I don’t consider myself an expert or at a developer level, but I am now immensely more empowered, less frustrated by the constraints I was working under, and much more responsive to the needs of the business.
Taking into account my previous frustrations and the feedback I received, I am happy to report that Coginiti, our company’s preferred SQL IDE, has additional capabilities that specifically address those concerns:
- Querying millions of rows of data without breaking a sweat, which eliminates the limitations of spreadsheet row capacities and empowers me to work with larger datasets.
- Built-in BI capabilities that allow for creating insightful visualizations and analytics within the SQL IDE itself, reducing the need for exporting data to external tools.
- The ability to load and join my own data to existing datasets
- Grouping and filtering functions so if I do need to export to spreadsheets I can do so in manageable chunks
- The ability to share my queries with colleagues without having to copy files to a file store or send a large file via email
Not only has my experience with Coginiti been positive, but it appears that I’m not alone in appreciating its capabilities. A recent Gartner Peer Community poll asked technology leaders for their SQL IDE of choice. No surprise, Coginiti came out on top.
As I continued to explore the capabilities of Coginiti’s analytics workspace, I began to realize just how transformative it was for my daily work as a business analyst. The frustrations and limitations I had faced with other tools seemed to melt away, replaced by the power and flexibility of a robust workhorse that could handle the increasingly complex analytical tasks I encountered.
With all of these problems solved and new capabilities at my fingertips, it didn’t take long for me to fall in love with Coginiti and start using it on a daily basis for everything analytics-related in my work. Want to get started with Coginiti? Grab your free trial and check out these SQL tutorials. I’d love to welcome you to the powers of what SQL can do for you!
