Learn Business Intelligence Skills

This article is in response to DBMS2’s How should somebody teach themselves database and programming skills? with a focus on Business Intelligence (BI).

I learned most of my BI skills after college without any formal structure. This article should provide some. The guide has two objectives:

  • Provide a step-by-step guide to business intelligence career development
  • Teach practical applications for high demand BI skills

It assumes the following

  • You have limited or no programming experience
  • You’re employed in a role that occasionally deals with data.
  • You want to become more technical and data-savvy, but not a serious engineer

First I’ll outline the core skills for anyone interested in BI. Then I’ll overview the technical concentrations.

Take a look at diagram below:

Learning BI Skills - Click to Expand

CORE SKILLS

  1. Master Microsoft Excel
  2. Learn Basic SQL
  3. Learn Basic Web Development

CONCENTRATIONS (in no particular order)

  • Databases
  • Report Programming
  • Web Programming
  • Unix
  • Statistical Applications
  • Enterprise Business Intelligence

Core 1: Mastering Microsoft Excel

Microsoft Excel lies in the heart of Analytics. No matter where data originated, it’s likely to end up in Excel for manipulation, analysis, or presentation. No other data product is used more extensively than Excel. No other product provides a better breadth of tools for solving analytics problem. While Excel may not be best solution for specific applications, it solves most issues reasonably well. As a result, Excel is the the backbone of Business Intelligence.

Mastering Excel, therefore, is the first thing anyone who wants to learn more about Business Intelligence should pursue.

Focus on the following five areas within Excel:

Navigation & Shortcuts
Advanced Excel users know how to navigate seemingly in the application primarily using keyboard shortcuts. Most tasks within Excel can be accomplished much faster using only the keyboard. This spreadsheet contains a list of many of the key shortcuts. Start by mastering the basic navigation shortcuts: moving in all directions, highlighting rows/columns, and moving between tabs. Follow up these with formatting (change to a %), filtering, and formulas shortcuts. Many of the keystrokes are intuitive (insert row: alt i r); some aren’t (paste special values: alt e s v). Fortunately, many of the navigation shortcuts are universal in other applications, such as in Microsoft Office and Google Docs.

You know you’ve mastered Navigation and Shortcuts: (a) You know the keyboard shortcuts for paste-special values/formulas (b) You can mostly navigate in Excel mostly without using the mouse.

Formulas
If you’re interested in learning how to program, Excel formulas are a great way to start. Because you write and execute formulas on the fly, learning formulas in Excel is easier than writing simple formulas in most programming languages. Start by navigating to the Formulas tab on the ribbon; Microsoft groups formulas by category. I suggest learning the following:

  • Date Formulas (date, day, month, year, date, weekday, today)
  • Text Formulas (left, right, mid, find, lower, upper, text)
  • Logical Formulas (if, and, or, not, iferror, iserror)
  • Aggregation Formulas (subtotal, sum, sumif, countif)
  • Lookup Formulas (vlookup, match/index)

Not sure what to do with them? Microsoft Help, Google, YouTube, and the Mr. Excel Forums are great resources.

You know you’ve mastered formulas when: (a) You can write nested if-statements, sumif formula, and a vlookup formula (b) You can do basic string parsing

Pivot Tables
Pivot Tables allow power users to easily analyze summarized information in Excel. Pivot Tables also serve as a powerful way to lookup data stored in memory. To begin, watch these videos on YouTube. Play around with the pivot table field. Move fields within the square matrix. Experiment with how the data is summarized. Try hiding, filtering, and grouping fields. When you get more advanced, build a calculated field and play around with pivot charts. Reference values using the GETPIVOTDATA function.

Pivot Tables are Excel’s most powerful data analysis feature. Moreover, Pivot Tables also provide an indirect introduction to other key programming topics such as storage on disk versus memory.

You know you’ve mastered Pivot Tables when: (a) You understand the difference between a pivot table calculated formula and a regular formula (b) You know when to use a pivot table to build a model vs writing aggregation formulas.

Charting
Charting in Excel is one of the more difficult areas to master simply because there are so many charting features. Creating a basic chart is easy; formatting is is tricky. The easiest way to modify a charts is to follow the right-click rule: if you want to change something, right-click, and select the most relevant-sounding option.

You know you’ve Charting when (a) You can switch a range to a different axis and (b) You can easily switch between xy, bar, area, and line graphs (b) You prefer pivot charts over regular charts

Data-Modeling w/MVC Architecture
The Model-View-Controller Architecture is a design pattern commonly found in web applications. This design paradigm can be extended to the production of larger Excel Models / Applications. The goal of the architecture is to separate front-end (presentation or “view”) from the back-end (data or “model”). The basic idea is that the front-end rarely talks (view) directly with the back-end (model); rather, communication back and forth happens between a intermediary, the controller.

In Excel, this manifests itself by creating seperate tabs

  • The Model: The data tab. All raw data is stored here
  • The Controller: The data-aggregation tab. Generally contains pivot tables based off of the data tab
  • The View: The client-facing tab, aka the presentation. Contains client-facing information such as graphs, summarized tables, and trending information

This paradigm makes the maintenance of models easy. For example, lets say you were tasked with updating a weekly report. Every week, you’d simply cut and paste the raw data into the Model. From there, you’d navigate to the Controller and update the Pivot Tables. Finally, you updated any basic information in the view (such as a reference date).

Once you’re ready to submit the report, you only show them the view. Everything the powers the model remains behind-the-scenes.

You know you’ve mastered MVC when you find yourself setting up all Excel models using this framework.

Wrap-up
Each one of these subjects above prepares you for more detailed Business Intelligence Concentrations. Learning Keyboard navigation introduces you to operating in a unix environment. Mastering Formulas teaches tib-bids of programming. Learning pivot-tables teaches memory v storage trade-offs as well as basic SQL aggregation.

Speaking of SQL, learn that next:

Core 2: Learn Basic SQL

Databases lie at the core of storage across the web, and SQL is primarily language to get at it. SQL is not only incredibly useful, it’s a great introduction to programming for several reasons:

SQL is a Not a Procedural Language
SQL—in contrast to other programming languages is a non-procedural  language. This means that you write what you want, not how to go about getting information. For example, to get all customers from a database, you’d write something simple such as

’ Write WHAT you want
SELECT * FROM customers;

In a procedural programming language such as Java or PHP, you’d have to tell the computer how to pull customers, such as writing a for loop:

// Write HOW to get it
$customers = array(‘sergei’,’larry’,’eric’,’steve’,’mark’);
$total_customers = 5;

for ($i=0; $i<$total_customers; $i++) {
echo “customer ” . $i . “: ” . $customer[$i];

}

SQL is Universal
All companies have databases. Finance likely has a financial database, marketing a customer database, and engineering a transactional database. If you’re working with data, chances are you’re extracting data from a database, likely from a another source. Getting access is often as simple as asking.

SQL is Easy to Learn Incrementally
SQL is a very forgiving language. If you make a mistake, the interpreter will help you easily identify the error. In other programming languages, identifying the source an error can be much more difficult.

You’re convinced that SQL is an easy language to learn. Great. Now onto what you should learn to start:

1. Learn the Basic SELECT Statement Syntax
Reference the tutorials available on the internet. W3 Schools is a great place to start. Any basic SQL book will do, too.

2. Get Access to a SQL Database
SQL is best learned-by-doing. If you can get access to a database, do it! If you don’t have one available, then Download a Free Version of MySQL Community Server and install it.

3. Practice Learning SQL Statements incrementally.
First start by learning a basic statement, such as

SELECT names
FROM customers

then add to it

SELECT name
FROM customers
WHERE name in (‘John’,’Vin’,’Mark’)

and continue to add

SELECT name, sum(revenue)
FROM customers
WHERE name in (‘John’,’Vin’,’Mark’)
GROUP BY name

etc

Don’t learn Microsoft Access. Go straight to SQL on a traditional database.

Core 3: Learn Basic Web Development

Increasingly, business intelligence is moving to the web. Web Development can be incredibly complicated, especially when it comes to business intelligence. Fortunately, there are a number of ways to get started with understanding basic web development paradigms. While these won’t address BI directly, they should get you started with understanding basic development, principles that can be extended to web BI. Try the following

  • Learn Basic HTML & CSS Syntax
  • Experience editing the source code in Wikis
  • Install a Word Press Blog
  • Use Dreamweaver to Edit your Webpages

Once you’ve got something basic running, iterate. For example, start a word press page. Once you’ve written a few articles, try editing the actual source code. Then customize the template.

CONCENTRATIONS

The Core Skills are intended to prepare you for the concentrations. Ask yourself—what do I want to do next? If you have a strong background in Stats, consider learning one of the Enterprise Statistical Tools. If you want to pursue a career in Enterprise Business Intelligence, try to get access to a BI tool in your office. If you want to be able to develop your own BI Applications, pick up some web programming and Unix. The concentration you choose is entirely up you.

Concentration: Databases

You’ve already mastered basic SQL, and now you want to take it to the next level. The database concentration is a great place to start. Broaden your understanding of data-extraction by learning how to do inner/outer joins and optimize your queries. Learn the fundamentals of data-manipulation with INSERT, UPDATE, and DELETE statements. Understand the difference between a dimensional and faft table. Spend some time reading about how databases are structured and organized.

If you’re interested in learning more about databases, I strongly recommend reading the Bible for Data Warehousing by Richard Kimball The Data Warehouse ToolKit (Kimball). This book will overlay the fundamentals of relational databases.

Concentration: Report Programming

Are you interested in learning how to make better spreadsheets? Consider learning Excel VBA, the programming language for Microsoft Excel. VBA is one of the easist languages to learn because of the simplicity of the development environment and guidance of the macro recorder. VBA and Macros for Excel 2007 is a great hard-copy resource, and the Mr. Excel VBA Forums is a great place to get help.

If you have a colleague who knows VBA, ask for a tutorial. Step thru the source code. 30 minutes of their help will save you 3 hours of headache.

Bright Hub also has a great tutorial on VBA.

Additionally, you can create VBA code using the Macro Recorder—which records actions you make—and manipulate them afterward. A great way to discover the names of functions.

Purists will argue that Microsoft VBA is a terrible way to learn programming concepts. They are right. You’ll probably develop some bad habits. However, it is the easiest way to begin programming for data manipulation. If you’re short on time and want to start producing results right away, VBA is a great place to start.

Concentration: Web Programming

This should be a separate article.

1. Choose a language
Start by choosing a web programming language. PHP is the most popular, but not necessary the most “pure” programming language. Ease of use and plenty of documentation make it a popular first choice. Other popular weblanguages to consider are Python, Java, and Ruby.

2. Learn a Web Framework
If you want to learn how to development a web site to scale, consider learning a framework. A framework will help you organize your code, take care of common web tasks (authentication, sessionization), and set up your site for long term success. Some example frameworks for PHP are Cake PHP and Zend. This article outlines more.

3. Learn the Supplementary Skills
Advanced websites often have a number of different components. Developers may want to learn how to the Adobe Creative Suite to design creative for pages, Javascript/HMTL5/Flash to add bells and whistles, and AJAX/DHMTL/CSS/XML to make more dynamic pages. Picking up all of these skills at once can be tricky. Rather than tackling them all at once, consider installing web page templates and changing them incrementally.

Concentration: Unix

Unix is the operating platform or choice for programmers. Even if you don’t program on a unix-based computer, you’re likely to encounter servers running some flavor of it.

Learning the basic structure of the Unix file system will make you a better web programmer. Start by learning how to navigate within a Shell. If you’re using a Mac, open up the terminal (a bash shell) and experiment navigating around the file system. If you’re not using a mac but still want to learn how to use Unix, getting shell (ssh) access to a web-server will do.

Once you’ve mastered the basic navigation. Explore learning how to use tools such as vim and grep. Vim is the unix-text editor of choice that you use almost entirely with the keyboard (see why learning excel shortcuts is important?). Grep is a text-based search utility.

If you want to become a serious programmer—or even a lightweight web programmer—learning how to use the unix file system is a must. Get started early with vim—it takes a while to master it.

Concentration: Statistics

If you’re interested in statistical analyses, consider learning SAS, SPSS, and R. R is free but harder to learn. SAS/SPSS are used more often, but aren’t free. Start with whatever you can get your hands on.

Concentration: Enterprise BI Reporting

Enterprise Business Intelligence tools can do a lot. Some offer lightweight reporting solutions, and others are full back-end, middle-layer, and front-end reporting/dashboarding suites. If your company has access to a BI Reporting Suite (such as Crystal Reports, Microstrategy, Cognos, etc), try to get access.

Most applications have a web or lightweight desktop tool which will allow you to create basic reports. It’s the WYSIWYG equivalent for SQL. Great way to learn more about SQL as well as how the reporting layer for some of these tools work.

View Comments
  1. Charles says:

    Awesome post John, thanks!

  2. Dennis Yu says:

    John, this is the type of article I’ve been looking for to share with new data analysts!  People are afraid of SQL and it’s so easy to write a simple select.

  3. Kaintsiaba says:

    Thank you for this article. this is exactly what i needed to set my path.

Leave a Reply




XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">

blog comments powered by Disqus