Business Intelligence in SQL Server

There are 3 tools in SQL Server that people in general overlook and that are worth mentioning. I want to introduce them in this post. These tools belong to SQL Server’s Business Intelligence toolset and as the name indicates they are tools to assist in making decisions.

Normally these 3 tools are integrated to transfer information from the database we use on a daily basis (OLTP) to the datawarehouse database (OLAP). Daily databases are transactional and they use updating commands like SELECT, UPDATE, DELETE or INSERT and they are normally normalized with primary and foreign keys. OLAP databases are designed for analysis, this means, as a historical reference. Their star design is not normalized. Select sentences are normally used to work with them. Their main purpose is obtain information from them, not updating them.

Business Intelligence – Data migration between an OLTP database and an OLAP one.

The idea behind this is that companies use the OLTP database for daily operations (for example the information of the last year (for performance reasons for example) and when that period is over data is moved/copied/transferred to the OLAP or Historical Database where instead of the last year we have, let’s say, 10 or 20 years of information.

Here is where BI tools make sense: Analysis Services are used to generate a Database Analysis cube, the Reporting Services are used to obtain graphical reports of both the OLTP and the OLAP databases, and the Integration Services are used to transfer information from the OLTP to OLAP databases. These tools, unlike other SQL Server tools, use a different graphical interface from the Management Studio and run from the Business Intelligence Development Studio, which is an embedded Visual Studio for SQL Server purposes with tools to manage BI.

SQL Server Business Intelligence Development Studio

Business Intelligence Templates

Analysis Services:

This tool allows us to generate a data analysis cube and allows us to execute it from the own Development Studio (and also from the Management Studio). Once the cube is generated, we can use Excel to obtain data from the cube and display them as pivoting tables.

Generation of a cube with Analysis Services

Report Server:

The tool is a report designer, similar to the software tool Crystal Report or the report designer included in Access. It is a graphical tool that allows to add a corporate logo, headings, page number, columns, lists, from a SQL Server query or to display them via a URL. These outputs are basically designed for being printed.

Designing a report with Report Services

Integration Services:

This visual tool allows SQL Server to generate scripts graphically dragging visual components to the integration services environment desktop. These projects are later compiled and a package is generated in a package that is loaded in SQL Server and is run at scheduled hours. For example it can be used to program a daily export job from tables to external files or as we said before migrate records from OLTP database tables to OLAP databases.