Technology

Many clients are technology agnostic. They are chiefly focused upon the end result, or if not, the tools are only important insomuch as they fit in with the local infrastructure.

For those who are interested in these things, here are the data tools that we like to work with.

excel

Microsoft Excel (all versions).

Excel is the most flexible utility that the data analyst can have in his or her kit bag; it is the data equivalent of the Swiss Army Knife. It can be used as a front-end to present data via pivot tables and charts. It can also be used to import data from a variety of sources – CSV files, ODBC sources; or even from enterprise database servers and OLAP cubes. Pivot table functionality enables slicing and dicing, while the Excel functions and VBA development environment allows for easy transformation of data, and automation of routine actions. Recent versions of Excel include the PowerPivot and PowerMap add-ins which are hugely powerful, adding pseudo-cube-building functionality and a graphical geo-spatial front end.

SQL-Server-2012

Microsoft SQL Server Stack (we most frequently use version 2008R2 and 2012)

At the heart of the Microsoft SQL server stack is the SQL Server engine itself. This allows for the storage and manipulation of data via tables, views, stored procedures, functions and triggers. Furthermore, the majority of the functionality of this mighty enterprise-class database can be coded using the Transact SQL language. This scripting capability is allied to the automation and scheduling capabilities built into the powerful SQL Server Agent.

SQL Server Integration Services (SSIS)

SSIS is designed to help a programmer pull data from a massive range of sources. These can be web sources, through local CSV, Excel and other files, to remote database repositories. In the course of the import flows, the data can be transformed in ways too numerous to go into here (pivot, merge, join, splits etc). Once transformed, the data can then be pushed out to an equally diverse types of destinations as well as stored locally in tables.

SQL Server Analysis Services (SSAS)

Another key component of the Microsoft stack. SSAS allows for the creation of fast multidimensional cubes, which when accessed through a front-end such as Excel, SSRS or QlikView, enables speedy and flexible slicing and dicing of the data – for instance, segmentation by date, customer attribute, product attribute and other hierarchies. SSAS also has data mining capabilities – regression, clustering and bayesian analysis can also be performed using this tool.

SQL Server Reporting Services (SSRS)

SSRS is the SQL Server stack reporting front end. The older versions are little clunky visually, but the functionality is all there. There are a wide range of ways to display the data including standard grids and charts, and rather more sexy looking guages. Data consumers can schedule reports so that they are delivered at regular intervals (for example, weekly on a Monday morning).  Parameters can also be set in the reports; dynamic date ranges, customer groupings and the like. Needless to say, the reports can also be built with both drill-down and drill-through functionality and consumers can save the output in a variety of formats including Excel and PDF.

Rlogo

Analytic Tools.

For complex statistical analysis we use SAS, SPSS and R. They all have their pros and cons, although R Statistics stands out by dint of its being free!

qlik_logo1

Other tools

We have delivered MI using Business Objects, Crystal Reports and QlikView in the past, and we also have a reasonable knowledge of Microsoft Access. However, with SQL Server Express Edition being a free download, we usually recommend that large Access database are ported to this more flexible and robust platform.