If Business Intelligence (BI) is in your focus, you need two things to be a success. They are the knowledge and tools. If you target the SQL Server BI, you can benefit from the tools and technologies offered by Microsoft for this case particularly.
The current article highlights these tools and technologies. We are going to explore how they work to design Microsoft-based BI solutions and clarify why it is so crucial to use the using proven tools for serving the BI needs of an organization.
About Traditional Tools and Technologies
To apply business intelligence in your organization, you can apply several ways. One of them is implementing traditional tools and technologies.
Talking about the SQL Server business intelligence tools, we cannot omit the famous Microsoft BI stack with its integrated development environment for BI developers. It has been one of the most productive tools by Microsoft aimed to utilize all benefits of having a BI-powered system in an organization.
Microsoft BI Stack
Microsoft traditional BI stack, commonly known as MS BI stack, consists of the following tools and technologies:
- SQL Server Database Development Tools and Technologies including SQL Server Database Projects
- SSIS (SQL Server Integration Services)
- SSAS (SQL Server Analysis Services)
- SSRS (SQL Server Reporting Services)
These tools can help you to build the necessary solutions, such as data warehouses, instantly from scratch.
The BI Integrated Development Environment
You can manage the above tools successfully via the integrated development environment known as SSDT (SQL Server Data Tools). It allows you to design, test, develop, deploy, and manage all MS BI Stack.
Traditional Tools vs Modern BI Tools
The Traditional MS BI stack and modern BI solution offered by Microsoft are compatible, but we need to be a bit careful with that.
There is a major difference between modern and traditional BI tools. Traditional tools are successfully managed via the integrated development environment. Modern tools are typically scattered across different environments, although it is possible to manage them to some extent via the central control panel.
Another crucial point is that modern SQL Server BI tools focus on cloud architecture support, while traditional tools concentrate on supporting on-premises architectures. However, both traditional and modern tools can support cloud architecture with some exceptions. In some measure, the sort of mix and match is available.
One of the most recent examples of modern SQL Server BI technologies is Azure Synapse Analytics. It is totally on the cloud (Azure), allowing to save the upfront cost of managing in-house servers. It supports the pay-as-you-go model and offers tremendous scalability when required.
SQL/BI or DWH/DWBI Developer
In a typical environment, a business intelligence (SQL/BI) or a data warehouse (DWBI/DWH) developer mostly uses BI Tools to build BI solutions. Both the traditional and modern tools are applicable, it depends on your case conditions and preferences.
Developing BI Solutions using Traditional Microsoft BI Stack
The traditional Microsoft BI stack (set of tools and technologies) has been mesmerizing developers and organizations for more than a decade. Now, they sometimes call it a classic BI Stack. The thing is, the growth of the Microsoft BI framework is fast and rapid, but the classical BI Stack is still very much in use. Some of these traditional tools and technologies are inevitable even if you are building your BI solution with modern tools.
SQL Server Data Tools (SSDT) for Visual Studio
The BI journey begins from installing Visual Studio on the development machine. The built-in SSDT (SQL Server Data Tools) for Visual Studio provides you with the framework for the first step. It is to build the data warehouse database.
SQL Database Project
A database project strictly follows the declarative development style to design, modify, test, deploy, and manage the data warehouse databases.
You can create a table in your database project and deploy it to the target data warehouse database. If you need to change the table in the database project, modify it with the table designer and deploy the changes. You don’t need to write a change script, as the SQL Server database project compilation process will handle it.
See below how the SQL Database Project is created in Visual Studio:
You can create the data warehouse database project in Visual Studio too:
Alternative SQL Server Database Development Tools
SQL Server database development tools help the developers build specific SQL Server databases behind the BI-powered solution (data warehouse). This specific database in a typical BI-powered solution is called the data warehouse database.
An SQL/BI or DWBI developer can either use SSDT (SQL Server Data Tools) or SQL Server Management Studio to build the data warehouse database. Often it is the first step in the data warehouse development after the end of the design phase.
It is also possible to build the data warehouse database with third-party tools. Besides, such tools often provide more functionality to simplify the development task. One of the most functional solutions is dbForge Studio for SQL Server.
SSIS (SQL Server Integration Services)
SQL Server Integration Services is commonly known as SSIS build and control the data movement from several sources (OLTP databases) into the data warehouse database.
In simple words, the Integration Services Project which consists of many SSIS Packages is used to perform ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) operations to merge and validate data before loading it into the data warehouse database.
However, unlike with SQL Server database projects readily available in Visual Studio, you need to add SQL Server Integration Services from Visual Studio marketplace as a (VSIX) extension:
You can create an Integration Services Project after installing the required extension:
Go through the following article on How to Set up and Test-Run the Environment to Multicast Data with SSIS for more information about SQL Server Integration Services (SSIS):
SSAS (SQL Server Analysis Services)
SQL Server Analysis Services or SSAS is another integral part of the traditional BI Stack.
You have the data warehouse database optimized for its specific needs. However, that database requires further enhancements to become an Analysis Services database meant for querying, analysis, and reporting.
To build the analysis services database, you need to create a separate project in Visual Studio. However, before that, you need to add the Microsoft Analysis Services Projects extension from Visual Studio Marketplace.
Once installed, you as an SQL/BI developer have the following options (and some others) to choose from:
- Analysis Services Multidimensional Project
- Analysis Services Tabular Project
Analysis Services Multidimensional Project
Analysis Services Multidimensional and Data Mining Project is the type of project which lets you build a multidimensional analysis services database with data mining if desired.
This project results in building OLAP/ROLAP/MOLAP Cubes which you can query in MDX language along with few others.
The end product of this project is the optimized data Cube which can instantly process the analysis, reporting, and research requirements to give you insights into your data.
Analysis Services Tabular Project
Analysis Services Tabular project is a newer version with more improvements and compatible with cloud technologies (Azure). Azure allows this project to take advantage of many other new BI-supported products, most essentially – Power BI reports.
The end product of this project is the Data Model which you can connect behind some modern reporting technology (e.g., Power BI reports) to provide a seamless integration experience.
You can query the tabular data model in the DAX (Data Analysis Expressions) language.
Refer to the Introduction to Microsoft Power BI article for more information about Power BI.
Processing the data model or analysis services database means bringing the latest data from the data warehouse database into the analysis services database or data model.
You can automate the processing of analysis services, as described in Automating the Tabular Model Processing of Analysis Services (SSAS) Databases in SQL Server
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services or SSRS serves as the central interaction point between the data warehouse and business users in a traditional data warehouse architecture.
A SQL/BI developer builds SSRS reports from the Analysis Services database data. The Multidimensional mode is used traditionally, but the tabular project type is also welcome depending on the purpose of use.
In simple words, SSRS reports are based on the analysis services database. They are designed and deployed to the server. Business customers would further subscribe and get those reports delivered automatically by scheduling these reports through the server. Just like some other BI tools, SQL Server Reporting Services is also added as an extension to Visual Studio SQL Server Data Tools:
After you install this extension, you can create the SSRS report in Visual Studio by choosing the relevant project type.
Also, refer to the following articles to get some hands-on experience of using SSRS:
- SSRS Reports Development in Simple Terms
- Creating Customer-Focused SSRS Reports with Parameters
- How to Write Stored Procedures for Professional SSRS Reports
You can visualize these traditional tools and technologies working together to build a robust BI-powered solution. See the screenshot below:
A Word of Advice
Remember that traditional tools and technologies were once the first-hand choices to build a corporate-level cost-effective and reliable BI-powered solution. But things are changing fast in the Microsoft BI world. As a result of these rapidly growing changes, the cloud-powered BI solutions offered by Microsoft as Azure Synapse Analytics are taking over slowly and steadily.
That does not mean that traditional tools and technologies will be abandoned. However, one thing is for sure – Microsoft is not going to support them as the Azure (cloud)-based BI products, where it adds new features and issues patches regularly.
Still, there are many use cases for traditional tools and technologies. There, they have an edge over rivals and outshine newer technologies. They have the advantage of the proven solid native foundation.
Congratulations! You have completed the journey and gotten familiar with the most productive SQL Server business intelligence tools and technologies of the traditional style.
Things to do
Now that you know it, test your knowledge by answering the following questions:
- Explain what is the first step of building an SQL Server business intelligence solution.
- Distinguish between the Multidimensional cube and Tabular Data Models.
- In a typical data warehouse scenario, do you connect your SSRS report with the data warehouse SQL database or the data warehouse analysis services database?
- What is a data model and why we use it?
Feel free to share your thoughts in the Comments section below!
Read also
SQL Server Business Intelligence (BI) – Basic Understanding
Tags: business intelligence, sql server, SQL tools Last modified: September 17, 2021
