What a load of Bl…

You may be wondering what Business Intelligence is. If you’re reading this article you probably have prior experience with Microsoft SQL Server or at least one other relational database management system (RDBMS). If this is the case you’re probably used to managing a lot of valuable data. If you’re a DBA you may be used to maximizing performance by rewriting stored procedures, creating indexes or running profiling traces.

SQL Server Business Intelligence, on the other hand, is about making sense of these numbers and letters and transforming them into presentable information that provides decision-making value to the right people at the right time.

Sounds easy enough right? Well, in reality, not really. It takes a mix of skills, tools, planning and a fair amount of time to put in place a large-scale BI solution to cover a modern company’s needs. Luckily, these requirements have led to the development of a large array of very handy tools which can be used to generate different types of reports in different formats.

Therefore, there are also ways of delivering rich business intelligence data to the end-user in relatively straightforward ways too.

The structure of SQL Server Business Intelligence

Central to all SQL Server Business Intelligence solutions exists a standardized method of preparing data to make it ready for consumption in the business intelligence world. This practice involves transforming data that is optimized for a heavily transactional system and moving it to one that is more suited for analytic querying.

To understand this difference it is important to realize that a good production server is generally geared towards OLTP (online transaction processing) which means it has a highly relational structure, indexes and data types that are conducive to INSERTs, UPDATEs, and DELETEs. This is not the case further down the workflow towards business intelligence optimized data. Broadly, there are two types of systems with one being even more optimized than the other and therefore the endpoint in a mature BI dataflow.

The first step away from an OLTP system is the Data Warehouse This is generally hosted in a normal SQL Server instance as a normal database however it has a de-normalized database model instead of a relational one. The most common structure is that star-schema model which has a central fact table containing measurable or calculable data surrounded by dimension tables that contain reference data that describe the fact, or measure, in the central table.

As mentioned previously it is possible to host both the OLTP instance and the data warehouse in the same SQL Server instance. It is also possible to start creating reports that query the data warehouse directly. It is therefore quite common for smaller companies to stop at this point. This is understandable as the less expensive license levels of SQL Server often do not include the Analysis Services tools required to move on the next stage of analytic processing.

This next stage involves the creation of a Multidimensional Online Analytic Processing cube (MOLAP). It sounds like a mouthful and behind the scenes, it is quite complex but in practice, you can think of this very basically as a spreadsheet in three dimensions (hence the word cube). In reality, this cube can have many dimensions, but without having a profound knowledge of quantum physics the human mind is not really capable of imagining such a structure. In any case, the result is the same.

You can pick a fact (or cell in your 3D spreadsheet) and drill down to a pre-calculated value along any chosen referential dimension.

Putting all the jargon to the side, when you get to this stage you are able to query a read-only optimized database and return business intelligence rich results for extremely large datasets in blisteringly fast response times (when compared to similar queries on a traditional OLTP database).

Being multidimensional and read-only in nature a cube not only takes up less space on a disk but also avoids concurrency issues completely by not requiring locks.

The flow of data

SQL Server Business Intelligence not only requires the underlying structure that supports these databases but also the ETL (Extract, Transform, Load) systems that allow the transfer of data between the OLTP database and the data warehouse. This is provided by SQL Server Data Tools (formerly known as SQL Server Integration Services or SSIS). This tool can be used for any operation that requires loading, transferring, enriching and sending data. It offers a bunch of handy tools to do anything from executing PowerShell files and running T-SQL scripts to more complex scripting in VB or C#.

The transfer between the data warehouse and the OLAP cube is dealt with in SQL Server Analysis Services (SSAS). This process entails mapping the data warehouse star-schemas to a cube structure by fact and dimension tables. This allows for the generation of an XMLA scripts which can construct the cube structure and process the transfer of data from the data warehouse to the cube.

Presentation

Once the underlying structure is created and the flow of data between the different levels is scheduled the business intelligence developer must now find a way to present this data to the end-user.

There are a number of ways to do this in SQL Server business intelligence. The most obvious and familiar being SQL Server Reporting Services (SSRS). This is a whole branch of SQL Server dedicated to building, hosting and presenting reports based on business intelligence data. Developers can create reports in Visual Studio and deploy them to the report server.

With SQL Server 2012 we saw a greater integration with SharePoint and through Power View and Performance Point it is possible for end-users to create their own rich and dynamic reports through a web interface.

One could also connection Excel spreadsheets directly to BI data sources of course. This is quite practical for the more adventurous end-user.

Conclusion

SQL Server Business Intelligence is a whole other can of worms. There are patterns to follow and tools to use to create BI systems, transfer the data between them and there are other tools to create report hosting servers and the reports themselves. In the next article, we will take a closer look at each feature Now that you have understood the basics of SQL Server Business Intelligence it is time to get your hands dirty and learn about Data Tools, SSAS, SSRS, SharePoint and Power View.

Resources:

Introduction to SQL Server Management Studio for Business Intelligence
Business Intelligence in SQL Server 2014 – SQL Server Data Tools (SSDT)