If you are someone who is tired of running SQL queries or waiting for days for your data teams to give you consumable data or even simple analysis then this post is for you.

What I mean by ‘without any tech background’ in headline is that I am a Chartered Accountant by qualification and I was able to implement this system in my organization with almost zero help from my tech teams. For sure I took a lot of help from Google, but trust me that was more than enough.

The Problem

In any transaction heavy organization data is scattered like shells on a sea shore, and its not very easy to pick those shells to find the hidden pearls.

Running SQL Queries and then taking dump in to excel is the only way for detailed analysis on data sets but as the no. of transactions grow, it gets difficult to manage in excel and again it is manual and slow in any case.

The Objective

To be able to answer the business questions with the speed of my thought process rather then to wait for hours / days to get required data and find answers.

The Tool

The tool I used for achieving my objective is ‘SQL Server Suite’. Head on to next section to see why I chose SQL server.

Though SQL server has many components, but I am using the following components:

Database SQL Server Integration Services (SSIS) SQL Server Analysis Services — Tabular (SSAS) PowerBI (though this is not exactly part of SQL server suite but it beautifully ties up with this setup and Microsoft bundles it up if you buy an Enterprise license)

Why SQL Server?

First question which comes is why use SQL server when there are so many other tools available to do similar jobs. Here are few of the reason why I decided to go with SQL Server:

Seamless integration with excel

Connecting the data pipeline (OLAP cube) to the excel is like a breeze and works seamlessly. This is like a gift for a finance person whose 50% of work life is tied around excel. With this integration you are able to process millions of rows with in an excel sheet.

GIF below will give you an idea of what I am talking about.

Windows Interface

For any person without any technical qualification the most dreaded thing about development is the command line. With SQL server you can do almost every thing without command line, just like using any other Microsoft product. Its components like SQL server Integration services (SSIS) offer drag and drop functionality which makes ETL process really easy.

SSAS / DAX is an extension of Excel

SQL server analysis service (SSAS) which helps in creating OLAP cube for fast analysis is very easy to learn and work on as in many ways it works like excel (at least the user interface). DAX the language which is used to defining calculations in SSAS again has a similar feel of defining formulas in Excel.

All in one tool

With SQL server all your needs starting from ETL to visualization are taken care with single license. If you go for different tools for ETL, OLAP and visualization then you will need different licenses which will be expensive as well as very difficult to manage.

Amazing user management

If your organization is using windows domain authentication for user management then adding users and managing access is seamless again. All you need to do is add the windows user id’s or groups to assign different levels of access.

Implementation Steps

Create a Data Warehouse Create OLAP cube Working with Excel Visualization using PowerBI

Data Warehouse

Biggest piece of the puzzle is to get all relevant data in one place so that it is easily available for further use. To be able to make a scalable system you need to put a little time in designing the tables of your data warehouse. This would be easy if you have good understanding of your business as that gives you a good idea of possible data sources and dimensions of data.

Once data warehouse structure is finalized and created next step is to set up workflow to push the data automatically to the tables. With the SQL Server Integration Services (SSIS), ETL becomes very easy. All the workflows have drag and drop flows which will set you up in no time.

The way I get the data from various sources is:

Use a python script to dump a csv file of data from all sources. For most of the data sources you have defined API. This is a part where you will either need to know a bit of python or you can take help from some one does.

Since you are reading this and In case you are looking for the scripts, you can get these scripts from my github. Run these python scripts on specified time every day to collect and dump that day’s data. This can be easily done with windows task manager. Pick these files using SSIS and load in to SQL server database. Currently I am extracting the data from following sources:

Multiple MySQL and PostgreSQL databases.

Google Adwords

Google Analytics

Google Play Store

Adding new sources is just about understanding the API documentation for the new source.

Creating the OLAP Cube using SSAS

Once data warehouse is ready, next step is to create the OLAP cube using SQL server analysis service tabular model. Data is simply imported from SQL server and processed in SSAS. There is a lot of documentation around how SSAS uses its vertipaq engine to process data effectively, you can google for more information on that.

Below is a snapshot of one of the tables post import in the SSAS. Didn't I said it is an extension of excel :).

You will need to do a lot of things after importing all the data in SSAS like defining the relationships between tables and using DAX for calculating the metrics you want to track. But all said and done it is easy and can be done by almost anyone.

Below is a quick view of how the tables look like in a simple model with all the relationships set. In a complex real life situation, no. of relationships grow very quickly. As I mentioned earlier for doing this part, you will need to understand your business to be able to cover all scenarios which effect your analysis.

Work Magically with Excel

Well, once your SSAS tabular model is ready then it is just a matter of seconds to create a connnection from your excel file and analyse the data in the ways you want.

You can quickly do even complex calculations like cohort analysis using DAX formulas (this might take a bit of learning time).

Attached is the GIF which I used in the earlier part of this post as well. This simply shows how easily excel integrates with

Visualization using PowerBI

Last step is to set up visualization using PowerBI. PowerBI comes with a server mode in which you can install it on premise and with the enterprise license and adequate infrastructure, you can add as many visualizations and users as you want.

PowerBI directly connects with SSAS and all it has to do is show your processed data in the kind of visualization you chose. There are a lot of pre-defined visualizations available and if you want more there are custom visualizations available as well.

Below is an example of dashboard from (clientsfirst-tx) just to give an idea of the kind of visualizations that can be done with PowerBI.

Here are a few resources which helped me to get all this done:

chandoo.org : His PowerBI course helped me in getting basics of DAX. sqlbi.com: Courses by Alberto Ferrari & Marco Russo helped me in further clearing the DAX concepts and SSAS implementation mssqltips.com: This website has a lot of free tutorials on SQL server, SSAS and SSIS which helped a lot in resolving a number of problems which I faced during implementation phase.

Also, thanks to Microsoft Development & Satya Nadella to put their energies in focusing on BI and developing SSAS tabular and PowerBI.