Essential skills any BI Consultant should have. Or why a fool with a tool is still a fool.
Today I’m gonna be mean.
Before we start though, here is my definition of a BI consultant. In my eyes, a BI consultant is the guy at the frontend of a data warehouse/business intelligence project. He creates and designs reports, and is responsible for setting up the logical layer between the data source(s) and the ad hoc query tool. His main task is to retrieve a s**tload of data in an ultra fast way to keep the business folks happy. In this narrow definition a BI consultant is not a data integration specialist or a data warehouse designer, and certainly not a data miner.
In medias res: A while back I was working for a consulting firm. They had this principal BI consultant and one day I had the “luck” to work in a project with him. We ran an INSERT statement to load a couple of million rows into a table. After a minute or so my friend got nervous and started to query the table to check on the progress of our data load. He got very pale when his query did not return any records and seriously thought he had discovered a major bug in the database system. But this guy had a tool. A BI tool. He was the hero of charts, ad hoc, and drag and drop. The master of the Business Objects universe. Was he able to string a simple query together? No. Did he have the skills to get him out of his performance mess. Nope.
Why is it not enough to just master the BI tool? And more importantly what skills do you need to become a true master?
Let me try and explain with an analogy. Airline pilots are highly paid individuals. Rightly so. Do you think this is because of their fabolous auto pilot skills? Or has it to do with the fact that they are trained to do the right things in extremely precarious situations? If you want to become a pilot you need years and years of training and experience. But once you have mastered the art of flying, it shouldn’t matter much whether you fly around in a Boeing or an Airbus. It’ll take a while to adapt but at the end of the day a plane is just a plane. It obeys the laws of gravity. The same is true for BI tools. Once you have mastered the core skills and concepts you can quickly and easily transfer them to any BI tool in the world and be a great success. For example with my knowledge of Business Objects, it took me a long weekend to learn the core functionality of OBIEE.
So what are these skills? As we have learned, the main task of a BI consultant is to query data fast, ultra fast that is. A report should not take longer than ten seconds to run. Having an excellent knowledge of SQL is imperative for this. At a minimum this means to be able to write complex sub-queries and multi table joins. In a data warehouse environment, however, this is usually not enough. Often a BI consultant needs to be able to query data recursively and do complex inter-row calculations. In the past we had to use expensive self-joins for this type of requirement. Not anymore though. Most if not all RDBMS today allow you to use analytic functions to perform inter row comparisons and aggregations. I am still perplexed that few consultants out there have mastered this essential skill. I myself first learned about the real power of analytic functions from my very good friend Maciej Kocoon (Maciejk, pints next weekend?). A true magician with SQL. Another great source to learn more about analytic functions is the O’Reilly SQL Cookbook.
Of course, a BI consultant also needs to be able to troubleshoot performance problems. This normally means that he needs to have a good understanding of database concepts in general, and the particular database system that is used in the project. At a minimum he needs to be able to trace queries and to read and interpret explain plans. Data modelling should be another core competency of a BI consultant. This includes both 3NF and dimensional modelling techniques. It also can’t do any harm to have business analysis skills to understand report requirements and visualisation techniques to create killer reports. Throw a good understanding of web technologies into the mix to customise the reporting environment, and you should be able to excel in any project. Ahh, I almost forgot OLAP, which should be easy once you have mastered analytic functions.
If you are an employer and have to choose between a guy who’s had years of experience in a particular tool but knows precious little about the inner workings of a database, data modelling, and SQL and someone who is a database expert with excellent knowledge of SQL, but has little knowledge of the BI tool at hand, always go for the latter. It will pay off. Guaranteed. And it will save you from creating embarassing support tickets..
