This tutorial will take you on exploration of different BI tools that are available in market. The focus of this tutorial will be to focus on reporting features of different tools. We will start with SQL Server Reporting Services and then we will move forward with other in-demand tools in business intelligence market. To start with our exploration, we needed a dataset. We just selected AdventureWorks database of SQL Server, however, any interesting dataset could be used to achieve the desired outcome. To keep us focused, we targeted the Shipment data of AdventureWorks. Our interest is to identify, what type of shipment method is frequently used and how their usage and cost has varied across years. In this connection, we formulated our initial query to extract the total purchase orders shipped each year using each of the shipping methods and their associated total cost. It is just a scenario taken at run-time to have a starting point. You can try/explore your own scenario, if you want. The SQL query that is used in this tutorial is as shown in Figure 1.
 |
| Figure 1: Shipment method exploration query |
Now it may vary on individuals, how they prefer to use this query. Few might want to use them directly as part of report. Few might find it feasible to create a view of the query. If similar query is to be executed on large database, then creating a materialized view or an SQL Server Analysis Cube might also become feasible. We will just create a stored procedure of the query and we will use it for creating our reports. Stored procedures are more scalable, maintainable, and better in terms of performance.
SQL Server Reporting Services provides us the provision to create report using tabular as well as matrix layout. Suitability of each layout depends on the requirements of the report. Similarly, how the report designer wants to display their data may very according to business requirements and designer thinking style. We will start with very simple matrix layout based report with out visualization features. We kept it for simplicity of the tutorial and author understand that the use of visual artifacts will be more beneficial for our targeted analysis of different shipment methods. Our first report on SQL Server Reporting Services looks like the one shown in Figure 2.
 |
| Figure 2: Shipment method analysis report using SQL Server Reporting Services |
PowerBI is another important business intelligence tool, highly in-demand in business intelligence market at the moment. We make the same data available to a customer using PowerBI as shown below:
A powerful feature of PowerBI is that it allows us to upload the PowerBI workbook online and then making it accessible to other users on application webpage. As we identified earlier that we created a stored procedure of our report, we will need a simplet T-SQL script to make use of our stored procedure in PowerBI. the script is provided below in Figure 3.
 |
| Figure 3: Calling SQL Server Stored Procedure in PowerBI |
Microstrategy is another important tool that can be used for creation of similar report on this data. A simple report developed in Microstrategy will look like as show in Figure 4.
 |
| Figure 4: Shipment Method Analysis Report using Microstrategy |
