Friday, August 24, 2018

SQL Reporting Services Tutorial (SSRS): T-SQL vs MDX vs DAX (with parameters)

SSRS Data Sources


 SQL Reporting Services (SSRS) in all of its versions offers variety of data sources and different ways how to retrieve data from the source. In the examples following in this article I will be using SQL Server 2016 Developer Edition (RTM) which has these sources available:

  • Microsoft SQL Server
  • Microsoft Azure SQL Database
  • Microsoft Analytics Platform System
  • Microsoft SQL Server Analysis Services
  • Microsoft SharePoint List
  • Oracle Database
  • Oracle Essbase
  • SAP BW
  • TERADATA
  • OLEDB
  • ODBC
  • XML
  • Report Server Model


 SSRS in the nutshell uses different drivers to connect and pull data from the data source and as developers we are more interested in how to efficiently use them than knowing how they actually work. Another important layer in data retrieval is querying language which is specific to each of these data sources.
Our focus in this case will be SQL Server on both, Microsoft SQL Server and Microsoft SQL Server Analysis Services (SSAS). Also, we will be using 3 different languages to fetch the data from the 2 sources: T-SQL, MDX and DAX. 

 The goal of this exercise is to get the same information using different querying languages from those 2 sources and to go through query basics and specifics for 3 of them as well as to learn how to build SSRS reports using parameters. 
Also, learning all 3 of them is from great benefit for every Microsoft BI full stack developer. Each of them can span from  basic data retrieval to complex queries or programing logic and definitely, they are worth of improving over time.

Sample data


 Databases used here are taken from Microsoft samples currently available at github:

Simple SSRS tutorials that I've created below will provide you enough of information to learn how to build some basic SSRS reports. In the most of cases, the report wizard has been used except for the queries which will be provided along with the exercises.

 The final project (Visual Studio 2017 version) that I've developed as well as tutorials are available and can be downloaded from:
Visual Studio Project - SSRS using TSQL, MDX, DAX


Quick query comparison

The goal of this demonstration was to highlight 
  • T- SQL - SQL Server Database Engine query language. 
  • MDX - SSAS for both Tabular and multidimensional OLAP instances. Performance wise, it will be slower than DAX in tabular model, especially if tabular model contains  
  • DAX - SSAS in Tabular model. It is still relatively new between the other 2 and probably, not intuitive for developers, but in syntax it is very similar to TSQL.
Probably, this could be another topic to compare the syntax of each language, but here is a quick snapshot for queries used in next sections:

T-SQL:



SELECT       
       pc.EnglishProductCategoryName AS CategoryName,
       ps.EnglishProductSubcategoryName AS SubCategoryName,
       p.EnglishProductName AS ProductName,
       SUM(s.SalesAmount) AS IntenetTotalSales,
    SUM(s.OrderQuantity) AS InternetTotalUnits
FROM FactInternetSales s
INNER JOIN DimProduct p
ON p.ProductKey = s.ProductKey
INNER JOIN DimProductSubcategory ps
ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey
INNER JOIN DimProductCategory pc
ON pc.ProductCategoryKey = ps.ProductCategoryKey
INNER JOIN DimDate d
ON d.DateKey = s.OrderDateKey
INNER JOIN DimCustomer c
ON c.CustomerKey = s.CustomerKey
INNER JOIN DimGeography g
ON g.GeographyKey = c.GeographyKey
WHERE       
       d.CalendarYear = @pYear
       AND g.GeographyKey IN (SELECT GeographyKey FROM dbo.DimGeography where EnglishCountryRegionName =  @pCountryRegion)
GROUP BY pc.EnglishProductCategoryName, ps.EnglishProductSubcategoryName, p.EnglishProductName
ORDER BY CategoryName, SubCategoryName, ProductName

MDX:

SELECT
NON EMPTY {
       [Measures].[Internet Total Sales],
       [Measures].[Internet Total Units]
} ON COLUMNS,
NON EMPTY {
       (
       [Product Category].[Product Category Name].[Product Category Name].ALLMEMBERS *
       [Product Subcategory].[Product Subcategory Name].[Product Subcategory Name].ALLMEMBERS *
       [Product].[Product Name].[Product Name].ALLMEMBERS
       )
} ON ROWS
FROM [Adventure Works Internet Sales Model]
WHERE
(
       STRTOSET(@pYear, CONSTRAINED),
       STRTOSET(@pCountryRegion, CONSTRAINED)
)

DAX:

EVALUATE
 (
    FILTER (
        SUMMARIZE (
            'Internet Sales',
            'Product'[Product Category Name],
            'Product'[Product Subcategory Name],
            'Product'[Product Name],
            'Date'[Calendar Year],
            Geography[Country Region Name],
            "Internet Total Sales", [Internet Total Sales],
            "Internet Total Units", [Internet Total Units]
        ),
        'Date'[Calendar Year] = @pYear
            && Geography[Country Region Name] = @pCountryRegion
    )
)
ORDER BY
    'Product'[Product Category Name],
    'Product'[Product Subcategory Name],
    'Product'[Product Name]


SQL Server data source and T-SQL


 Transaction SQL (T-SQL) is definitely, the most popular and probably, still the most used between the other 2 and it's dedicated to only relational SQL Database.
Here it goes, step by step tutorial:

1. Add new data source:




2. Create report (using report wizard):



**script used in the following section:
SELECT       
       pc.EnglishProductCategoryName AS CategoryName,
       ps.EnglishProductSubcategoryName AS SubCategoryName,
       p.EnglishProductName AS ProductName,
       SUM(s.SalesAmount) AS IntenetTotalSales,
    SUM(s.OrderQuantity) AS InternetTotalUnits
FROM FactInternetSales s
INNER JOIN DimProduct p
ON p.ProductKey = s.ProductKey
INNER JOIN DimProductSubcategory ps
ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey
INNER JOIN DimProductCategory pc
ON pc.ProductCategoryKey = ps.ProductCategoryKey
INNER JOIN DimDate d
ON d.DateKey = s.OrderDateKey
INNER JOIN DimCustomer c
ON c.CustomerKey = s.CustomerKey
INNER JOIN DimGeography g
ON g.GeographyKey = c.GeographyKey
WHERE       
       d.CalendarYear = @pYear
       AND g.GeographyKey IN (SELECT GeographyKey FROM dbo.DimGeography where EnglishCountryRegionName =  @pCountryRegion)
GROUP BY pc.EnglishProductCategoryName, ps.EnglishProductSubcategoryName, p.EnglishProductName
ORDER BY CategoryName, SubCategoryName, ProductName











3. Rename main dataset:




4. Add year and country region datasets:


**script used in the following section:
SELECT
       DISTINCT
       CalendarYear
FROM dbo.DimDate
ORDER BY CalendarYear


**script used in the following section:
SELECT
       DISTINCT
       EnglishCountryRegionName as CountryRegion
FROM dbo.DimGeography





5. Bind year and country region datasets to parameters:







6. Slight formatting to the report (settings not explained here, but available in the project attached above):




SQL Server Analysis Services (Tabular model) source and MDX


 Multidemnsional Expressions is a query language used against Online Analytical Processing (OLAP) and it can be ran against both, multidimensional and tabular instances of SSAS.

1. Add new data source:



2. Create report (using report wizard):




**script used in the following section:
SELECT
NON EMPTY {
       [Measures].[Internet Total Sales],
       [Measures].[Internet Total Units]
} ON COLUMNS,
NON EMPTY {
       (
       [Product Category].[Product Category Name].[Product Category Name].ALLMEMBERS *
       [Product Subcategory].[Product Subcategory Name].[Product Subcategory Name].ALLMEMBERS *
       [Product].[Product Name].[Product Name].ALLMEMBERS
       )
} ON ROWS
FROM [Adventure Works Internet Sales Model]
WHERE
(
       STRTOSET(@pYear, CONSTRAINED),
       STRTOSET(@pCountryRegion, CONSTRAINED)

)







3. Rename main parameters:



4. Rename main dataset:




5. Unhide parameters and review datasets:



6. Format report (settings not detailed here, but available in the project attached above) and review results:

SQL Server Analysis Services (Tabular model) source and DAX


 Data Analytics Expressions is both, formula language and querying language dedicated only to tabular model instance of OLAP.

 **data source "AdventureWorksTabular" already created in previous tutorials

 1. Create report:






**script used in the following section:
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            'Internet Sales',
            'Product'[Product Category Name],
            'Product'[Product Subcategory Name],
            'Product'[Product Name],
            'Date'[Calendar Year],
            Geography[Country Region Name],
            "Internet Total Sales", [Internet Total Sales],
            "Internet Total Units", [Internet Total Units]
        ),
        'Date'[Calendar Year] = @pYear
            && Geography[Country Region Name] = @pCountryRegion
    )
)
ORDER BY
    'Product'[Product Category Name],
    'Product'[Product Subcategory Name],
    'Product'[Product Name]








2. Rename main dataset:



3. Create year and region country datasets (to be consumed in parameters):



**script used in the following section:
EVALUATE
(
    SUMMARIZE(
        'Date',
        'Date'[Calendar Year]
    )
)
ORDER BY
    'Date'[Calendar Year]






**script used in the following section:
EVALUATE
(
    SUMMARIZE(
        'Geography',
        Geography[Country Region Name]
    )
)
ORDER BY
    Geography[Country Region Name]




4. Bind datasets to parameters:







5. Format report (settings not detailed here, but available in the project attached above) and review results:


Posted by at
Labels: , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)