Styles

Monday, November 7, 2011

Reporting Services Charts using OpenQuery to write MDX

There is currently a limitation in Reporting Services when working with MDX Cube queries.

Suppose we want to display the following report:


The challenge with the above chart is that the “Drivers” (i.e. NPS, Navigation, etc. found on the X-Axis) that are used for grouping are actual Measures in the Cube, and the “Days of Week” values (i.e. Monday - Sunday) are actually based off a single date Dimension.

In effect this means that when we create a new Dataset in the Reporting Services designer the following fields would be generated:


This won’t help us group the “Drivers” in the chart because we would actually need a single column to group by.
So within the context options of the report the following is what we would hope to achieve:


That means that our Dataset must look like this:


This is extremely easy to achieve in MDX where we would write the following keeping in mind that we want our Measures as rows and our single “Days Of Week” Dimension in the columns.
The following MDX query would provide what we are looking for:

SELECT
      NON EMPTY {
            [Measures].[NPS],
            [Measures].[Navigation],
            [Measures].[Accessibility],
            [Measures].[Availability],
            [Measures].[Checkout],
            [Measures].[Staff],
            [Measures].[Cleanliness]} ON ROWS,
      NON EMPTY CROSSJOIN({
            ([Days Of Week].[Order Index].[Order Index] ) },
            { ([Days Of Week].[Day Of Week].[Day Of Week] ) }) ON COLUMNS
FROM [Database]
This would return the following result:


You would think that this would then naturally be simpler to bind to the Chart now that we have the correct columns to bind to our “Category Groups” in the Chart’s context option.
Unfortunately, however, Reporting Services doesn't like this because having a Dimension displayed as columns would result in the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

In order to solve this problem we will need to generate a Dataset using OPENQUERY.
This can get tricky when working with multiple servers that host the SQL Server database and the Analysis Services database.

The first step would be to create a Linked Server on SQL Server. This will create a connection from SQL Server to Analysis Services so we can actually write MDX queries from a SQL Server query:



Once this is created we can now use OPENQUERY as follows:

SELECT * FROM OpenQuery([LINK],'
SELECT
      NON EMPTY {
            [Measures].[NPS],
            [Measures].[Navigation],
            [Measures].[Accessibility],
            [Measures].[Availability],
            [Measures].[Checkout],
            [Measures].[Staff],
            [Measures].[Cleanliness]} ON ROWS,
      NON EMPTY CROSSJOIN({
            ([Days Of Week].[Order Index].[Order Index] ) },
            { ([Days Of Week].[Day Of Week].[Day Of Week] ) }) ON COLUMNS
FROM [Database]
')
That will provide us with the following result in SQL Server Query Analyser:


Since Reporting Services will consider this a simple table rather than  a complex MDX query, it can now be used in the report when creating a dataset in order to generate the following fields:


Naturally that means needing to map the “Monday” field to “[Days Of Week].[Order Index].&[1].[Days Of Week].[Day Of Week].&[Monday]” and so on as shown below in the Fields section:


One important note to make is that Integrated security may seem to be the easiest security credentials to work with when connecting from, for example, a ReportViewer control in an ASP.NET site to the Reporting Services report, which then in turn connects to the SQL Server database, and then to the Analysis Services database via OPENQUERY. However this will not necessarily work since each connection along the path witll have its own Windows credentials.
Ideally the Data source’s connection string in the Report Server should actually be set to a SQL login so that both the Report can work either directly from a browser or through the ReportViewer.
The Linked Server settings should be set to use a specific security context for a login that has read access to the Cube. This can be set in the Linked Server as follows:



Tuesday, August 23, 2011

MSSQL recursive list in a Stored Procedure

The following scenario allows one Staff Manager to view all direct reports and their child member's direct reports recursively:

    WITH Hierachy(StaffNo, ManagerStaffNo, PreferredName, Level)
    AS
    (
        SELECT StaffNo, ManagerStaffNo, PreferredName, 0 AS Level
        FROM Staff s
        WHERE s.StaffNo = @StaffNo
        UNION ALL
        SELECT s.StaffNo, s.ManagerStaffNo, s.PreferredName, sh.Level + 1
        FROM Staff s
        INNER JOIN Hierachy sh ON s.ManagerStaffNo = sh.StaffNo
    )
    SELECT StaffNo, ManagerStaffNo, PreferredName, Level
    FROM Hierachy

Wednesday, June 15, 2011

Dynamic Themes in MVC and handling Page_PreInit

Another thing I learnt at Resonate Solutions:
Generally, when you create an MVC Application the project template will provide a Master Page that can be referenced by any Views you create.
That means when you want to implement generic code that executes for all page loads, typically the most logical place to put it is in the Master Page's code behind.
This would be ideal if you were to implement the following code to set a theme dynamically (based on the themes in the App_Theme folder):

this.Page.Theme = Request.QueryString["Theme"];
The page's theme, however, is processed during the initialization stage of the page's life cycle, and therefore if we need to set it, it must be done before hand i.e. in the OnPreInit event handler.
The problem is that the Master Page doesn't actually have an OnPreInit event because it isn't actually a page in its own right.
It is a user control that merges with the content page during the initialization phase of the page's processing, so just like a UserControl it wouldn't have an OnPreInit.
So therein lies our problem. Rather than copying and pasting the above code for every single View. We would need to create a BaseViewPage that is inherited by all the Views.
Take note that all Views inherit off the following class:

namespace Hub.Samples.Mvc
{
    public class BaseViewPage<T> : ViewPage<T> where T : class
    {
        this.Page.Theme = Request.QueryString["Theme"];
    }
}

That also means that each View we create needs to inherit off it. This can obviously be done in the page directive as follows:


<%@ Page Inherits="Hub.Samples.Mvc.BaseViewPage<dynamic>" %>

Tuesday, March 8, 2011

Linq to Entities Count() using IQueryable<T> in Extension methods

Something I learnt at Resonate Solutions while working particularly on performance tuning of Linq To Entities when I was digging a little deeper into the IQueryable<T>.Count() method.
I realised the when I was using SQL Profiler to see what the generated TSQL code was, it didn't have the equivalent COUNT(*) operator. It was actually evaluating the entire expression first then passed it back to the application so that the count could be made on the instance collection in memory, which would have a serious impact on the performance of the query because of all the data being sent back and forth.
The main reason it was doing this was because the Extension Method I used was for a type IEnumerable<T> as shown below even though the instance passed in was of type IQueryable<T>

public static int PageCount<T>(this IEnumerable<T> list)
{
    int count = list.Count();
    //rest of the code...
}

 
Since IQueryable<T> implements IEnumerable<T> I thought it would be good to use a "baser" Interface, but as it happened, it returned the entire collection then did a count on the inferred IEnumerable<T> instance in memory.

Looking into SQL Profiler, the TSQL that gets generated contains the following:

exec sp_executesql N'SELECT
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
FROM ( SELECT
      [Distinct1].[C1] AS [C1],
      [Distinct1].[C2] AS [C2]

However if I were to use:

public static int PageCount<T>(this IQueryable<T> list)
{
    int count = list.Count();
    //rest of the code...
}
That evaluates the TSQL with COUNT(1) which is exactly what I wanted as shown below:
exec sp_executesql N'SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
      COUNT(1) AS [A1]
      FROM ( SELECT DISTINCT
            [Project1].[C1] AS [C1],
            [Project1].[C2] AS [C2]
The main reason for this is that the inference on an interface is quite different than doing it on an abstract function or base class when everything is done in memory.
When passing in the instance of an object that implements IQueryable<T> through a parameter that is of type IEnumerable<T> the instance is forced to shape itself as an IEnumerable and therefore Linq to Entities will recognise that it needs to evaluate the whole collection first rather than building the expression prior to execution.