Reporting Results Using SQL Server Reporting Services

In our previous tutorial, we generated TPC-H Query 1 results with near to zero response time using SQL Server Analysis Services. In all our previous tutorials related with TPC-H Query 1, we only attempted to process the query to get the required results. Each query result is of importance for end-user. If your end-user is a business user and your query results are required for decision making. We should present our result using an appropriate reporting tool. In this tutorial, we will focus on SQL Server Reporting Services. For generating reports using SQL Server Reporting Services, we will be using SQL Server Business Intelligence Development Studio. For reports, we have a dedicated project type, i.e. Report Server Project. Create a new project for Report Server Project as shown in dialog below:

As the project is created, you will have to add report to your project.

As the report is added, you will end-up on Report Wizard dialog. This Report Wizard make it easy for developers to complete the most of report generation work.

As you move next, the first step as in any report generation process is to select the appropariate data source for your report. You data source could reside on any supported database or data services. In this tutorial, we will be connecting with SQL Serve Analysis Services as shown in figure below.

Once we are done with creating the connection, the next step is to write an appropriate query to extract the required data from data source to make it available on our report. For this purpose, we have a query designer, which facilitate us to create a query using GUI.

However, if you are comfortable with MDX query, which you should be after working with our tutorial on OLAP Query Languages then you can also write an MDX query by yourself as shown in figure below.

After completing the query part, on the next screen Report Wizard will ask you to select the report type. If you want to report a multi-dimensional cube data then Matrix (also know as cross-tab) is the best suitable report type. However, Tabular report type can also be used to report multi-dimensional cube data.

For Matrix report type, wizard will ask you to specify, what content will be displayed at rows and columns.

There are many different styles available for your report appearance. You can select one appropriate for your report audience and content.

On the next screen, you are done with your report creating task. Give your report an appropriate name.

After completing the wizard, you can view the report in design window.

To preview the report, you can make use of preview tab.

In report designer, at bottom you will find Row Groups and Column Groups sections. You can alter the row groups and column group settings from their. For example, in figures below, we are changing the group properties for Calendar_Year group.

We change the sort order of Calendar_Year.

TPC-H Query 1: Performance Optimization Using SQL Server Analysis Services

 

In our previous tutorial, we attempted to reduce the response time for TPC-H Query 1 for scale factor 10. In this tutorial, we will explain how the same query result can be made available using SQL Server Analysis Services to allow end-user to perform multi-dimensional analysis. To begin with, first we will create a view for our TPC-H Query 1 as shown in create view script below:

 
create view TPCHQUERY1VIEW as

SELECT

L_SHIPDATE

, L_RETURNFLAG

, L_LINESTATUS

, SUM(L_QUANTITY) AS SUM_QTY

, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE

, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE

, SUM(L_QUANTITY) AS AVG_QTY_SUM

, SUM(L_EXTENDEDPRICE) AS AVG_PRICE_SUM

, SUM(L_DISCOUNT) AS AVG_DISC_SUM

--, AVG(L_QUANTITY) AS AVG_QTY --We have done AVG_QTY_SUM above

--, AVG(L_EXTENDEDPRICE) AS AVG_PRICE --We have done AVG_PRICE_SUM above

--, AVG(L_DISCOUNT) AS AVG_DISC -- We have done AVG_DISC_SUM

, COUNT_BIG(*) AS COUNT_ORDER

FROM dbo.LINEITEM

--WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))

GROUP BY L_SHIPDATE, L_RETURNFLAG, L_LINESTATUS

--ORDER BY L_RETURNFLAG,L_LINESTATUS --invalid in views

go

Once you are done with creating the cube. You can move forward to create Cube using MS SQL Server Analysis Services Project in SQL Server Business Intelligence Studio. For detail on how to create a cube, you can refer to our previous post on this topic at Building Data Cubes: An Introduction to SQL Server Analysis Services. The only difference will be that we will be using the same TPCHQUERY1VIEW  view to create dimensions and cube.

While creating dimension, we will be using the same column as Key Column and Name column.

We will create three dimensions, i.e., L_SHIPDATE, L_RETURNFLAG, and L_LINESTATUS.

We will select all view columns with sum aggregation as measure except the TPCHQUERY1VIEW count. We will not need this for generating TPC-H Query 1 results.

Select all three dimensions that we have created earlier.

Once you are done with CUBE creation and processing. It is not time to view our results. Drag and drop the required dimension and measure in the browse window. TPC-H query extract all records with L_SHIPDATE on or before 1998-09-02. Therefore, we will restrict our result with a filter on L_SHIPDATE dimension.

As you can observe, we do not have any direct provision to calculate AVG_QTY, AVG_PRICE, and AVG_DISC as a measure. Therefore, we will make use of MultiDimensonal eXpressions (MDX) to calculate these three measures. MDX Query below gives us equivalent results as of TPC-H Query 1 using the CUBE that we have just created. We use WITH SET to restrict the records according to L_SHIPDATE dimension. Furthermore, we make use of WITH MEMBER to calculate three averages as required by TPC-H Query 1.

WITH

SET [myset] AS

{[L_SHIPDATE].[L SHIPDATE].&[1992-01-02T00:00:00]:[L_SHIPDATE].[L SHIPDATE].&[1998-09-02T00:00:00]}

MEMBER [Measures].[AVG_QTY] AS

([Measures].[AVG QTY SUM] / [Measures].[COUNT ORDER])

MEMBER [Measures].[AVG_PRICE] AS

([Measures].[AVG PRICE SUM] / [Measures].[COUNT ORDER])

MEMBER [Measures].[AVG_DISC] AS

([Measures].[AVG DISC SUM] / [Measures].[COUNT ORDER])

SELECT

([L_RETURNFLAG].[L RETURNFLAG].members, [L_LINESTATUS].[L LINESTATUS].members) ON ROWS

, 

{

[Measures].[SUM QTY]

, [Measures].[SUM BASE PRICE]

, [Measures].[SUM DISC PRICE]

, [Measures].[SUM CHARGE]

, [Measures].[AVG_QTY]

, [Measures].[AVG_PRICE]

, [Measures].[AVG_DISC]

} ON COLUMNS

FROM

QUERY1CUBE

Snapshot of results from out MDX query are provided in the Figure below.