Agile BI with SQL Server 2012

I’m speaking at PASS SQL Saturday #210, Brisbane 2013. The event has an impressive line-up of topics and speakers and I am looking forward to be a part of it. I will be presenting on Agile BI with SQL Server 2012.

sqlsat210_speaking

You can find the event schedule here.

Advertisement

SSIS Script Task – Upgrade to SSIS 2012

I upgraded a set of SSIS packages from SSIS 2008 R2 to SSIS 2012 and all of them had a smooth upgrade except for one. I had script tasks in almost all the packages which ran succesfully after I upgraded and tweaked them.

There was one package which was giving me trouble and functionally it dealt with moving, loading and deleting files from various folders. I upgraded the script task, updated all references, modified the project paramaters (as I am using the Project Deployment Model) and when I tried to run the package (Debug mode), the following generic error popped up:

Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

errorssis2012

This error message was very generic and did not help me in tracking down the root cause. I decided to put a breakpoint into the Main() method of my script and tried debugging the package again. I was surprised when the script task opened up in VSTA and reached my breakpoint (which contradicted the generic error message – Invocation Error). The issue was with folder security to access the files.

Hope this helps fellow ETL developers who might come across similar issues with a generic invocation error.

SSRS KPI Indicators for non-numerical values

Today, I would like to share a quick and easy solution to display Traffic-Lights indicator for statuses in a workflow process. I recently implemented a workflow-driven report using SSRS which allows users to mark the receipt of documents and to further update the status of documents as “Signed Off”, “Submitted” etc. The requirement was purely to provide a workflow-driven report to the users. I took advantage of SSRS’ native KPI indicators to include a traffic-lights indicator to show the status graphically as it changes. This proved to be a quick win for my client users.

To start with, I designed the following report with link buttons for users to update the status. These links point to the same report with a dataset calling a stored procedure to update the status. Since this post focusses on the indicators, I shall stick to doing just that.

Image

I have an record for a given document and it is marked as “Received” by default. (Notice the yellow colour traffic light indicator). The user can change the status to “Current” which then turns the colour of the indicator to green as shown below.

Image

Lets take a closer look at the settings to achieve this.

Right click on the report body and select Indicator from the list of available report items.

Image

From the available Indicator types, select the “rimmed traffic lights indicator” and place it on the report. After resizing and adjusting the position of the indicator, right click on the indicator and select Indicator Properties.

Image

In the Indicator Properties dialog, select the Values and Scales tab and notice the preset numerical values for each colour. These range values can be modified to ranges that suit your needs. As discussed earlier, my requirement was to change the colour depending on a string field value which holds the status as “Received“, “Current”, “Submitted” or “Cancelled“. The value expression comes in handy to set numerical values to each of the string statuses.

I assigned a numerical value to each of the string values in my status field. The indicator then uses the corresponsing numerical value to change the colour based on the defined range.

From the expression above, if the status is set to “Cancelled” a zero value is returned which falls in red colour’s range. Thus, the report then displays the red coloured indicator. While this is a simple solution to use non-numerical KPIs to display indicators on your report, more advanced indicators can be implemented along the same lines.

SSIS Dynamic Connection String – DelayValidation

When I develop SSIS packages, I set the connect managers’ connection strings in one of the following ways:

  • Package Variables reading from an XML config file
  • Environment Variables
  • Package Variables whose values are passed from a SQL job invoking the package

I had a set of SSIS packages running for my client using the third option listed above. The packages worked fine for ages until one fine day when they failed. The logs showed the packages had failed validation and I discovered that all the packages had their connection managers’ DelayValidation property set to False. The variable used to set the connection string had a default value pointing to the DEV server. These packages in production were actually trying to validate against DEV database though the connection string was dynamically set via a variable to point to PROD. This was dangerous as the jobs will not run if DEV server was down, which is exactly what had occurred.

Whenever you have a connection string set dynamically using variables, the best practise is to set the DelayValidation property to True for all the connection managers and all the Data Flow Tasks. I found the following blog article to be useful with some quick handy tips.

http://www.louisnguyen.info/Home/ssis-best-practices

Hide-and-Seek with expressions in SSRS Reports

This will be a quickie as I’m posting from my tablet for the first time on this site and it is just before going to bed. It’s about an issue I came across with an SSRS report this evening. It was a perfectly running report in production for over a year. The report’s layout has a dashboard-like feel with a summarised tablix of budgets and expenses representing my client’s business expenditure. The highlight of the summary was a Gauge Panel to show graphical representation of the data in every row. I had to modify the dataset dsExpenses ( which contained the results of an MDX query) to reflect the changes I had made to the cube from which the report was generated. I sucessfully ran the new dataset’s MDX query from the dataset’s query designer and changed all references in the report’s tablix cells to use the new fields from the newly modified dataset dsExpenses. One of the fields that was changed in dsExpenses was Payment_Amount which is now referred to as Payment_Amount_Programs.

I changed all references in the summary tablix from Payment_Amount to Payment_Amount_Programs manually by checking on each cell’s properties. I avoided modifying the report’s code as there were other tables that had other datasets with the same field name Payment_Amount. After carefully checking on each cell’s properties and values, I ran a quick build of the report and to my surprise the Gauge Panel’s background colour expression was thrown on the error report as it still had references to the old Payment_Amount. I knew, for sure, that I had checked the background colour expression for any outdated references. But when I checked the report’s xml code, I found that there was an expression hiding under the gauge data series’ back colour. I changed the expression to refer to Payment_Amount_Programs instead of Payment_Amount and the report ran successfully. This was a good lesson learnt the hard way to always check for expressions hiding in the code holding old references after a dataset is modified.

Posted to The No-Brainer Lab using Android

BIDS Helper to the rescue (SSAS cubes)

Most often we think that the cubes we design and implement will process without any issues when the corresponding dimensions and facts have sensible data. This, however, is only a myth. I learnt this the hard way when I modified an existing SSAS cube which had some changes to the dimension attributes. The processing of the cube failed due to a mismatch of an attribute’s data type between a dimension in the data source view (DSV) and the dimension in the cube. That’s when BIDS Helper came to my rescue. I quickly installed BIDS Helper from here and ran its inbuilt Dimension Health Check feature to discover the hidden truth. I found BIDS Helper to be extremely useful to perform health checks on measures as well.

As soon as I run this feature on my measure group, it points out that the data type of measures contained within the group can be changed for optimised results. 

 

To keep this post short and quick, I would recommend BIDS Helper if you run into data-type problems when processing SSAS cubes.

 You can download BIDS Helper and access the product’s documentation from the following Codeplex site: BIDS Helper

NServiceBus – Simple Server and Client (Web) Tutorial

Finally!!! This blog was pending to be born ages ago, but its better late than never. I had been doing some research around NServiceBus for a week now and thought of putting up a simple tutorial for beginners like me who find it hard to step into this technology. Having worked on .Net and web technologies from the beginning of my career, I initially felt NServiceBus was just another component that can be made use of in bits and pieces of my projects. But I got that completely wrong as NServiceBus in itself is a complete architectural base for developers to implement and end-to-end solution(if you start liking it).

In this post, I am not going into the details of what NServiceBus does and how it is designed. The best source of information about it is Udi Dahan’s blog (the man who created NServiceBus) and NServiceBus official site.

Let’s step into the tutorial right away.
This tutorial describes a step-by-step way of implementing a simple Client-Server project using NServiceBus in .Net 4.0. For the most obvious reasons of dealing with real-time web applications, I have used a Web client here.

Before, we start implementing the project, please check the following pre-requisites.
1. Download the latest NServiceBus package from the official site.
2. Extract the zipped package into a folder and use the dlls from the folder \.net 4.0\binaries\ for referencing in our project.

I have divided the entire solution into 3 modules for simplicity of this tutorial.

Module 1 – Create a Message component
Step 1.1: Create a new Class Library project and name it “MyMessage” and name the solution “NServiceTest” (I have used simple and quick names and conventions for this tutorial. You can use your own names and conventions)

Step 1.2: Add reference to the NServiceBus.dll from the binaries folder in your downloaded packages. After the reference is added, make sure you set the “Copy Local” property to True in its properties window.

Step 1.3: Open MyMessage.cs file and enter the following code. For simplicity, the class contains two public properties id and name. The key point to note here is the using statement which includes NServiceBus and that the class inherits the IMessage interface. The IMessage interface here indicates that MyMessage class represents the message format that is to be sent across the bus.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NServiceBus;

namespace MyMessage
{
    public class MyMessage: IMessage
    {
        public int id { get; set; }
        public string name { get; set; }
    }
}

Step 1.4: Build the MyMessage project. The generated MyMessage.dll will now be available in the bin folder of the project (We need to use this dll as a reference in the following modules in this tutorial).

Module 2 – Create the Server
Step 2.1: Create a new Console Application Project and name it “MyServer”.

Step 2.2: Add references to the following dlls. Its better to copy the dlls from the binaries folder to the local bin\Debug folder of the project and add a reference locally. Please note that the Message.dll should be copied from the output folder of MyMessage project.

After the references are added, the References section would look like below.

Step 2.3: Rename the file Program.cs to ServerEndpoint.cs. Open ServerEndpoint.cs and enter the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NServiceBus;
using MyMessage;

namespace MyServer
{
    public class ServerEndpoint: IHandleMessages
    {

        public void Handle(MyMessage.MyMessage message)
        {
            Console.WriteLine("Received Id = "+message.id.ToString());
            Console.WriteLine("Received Name = "+message.name.ToString());
        }
    }
}

If you look at the code above, you would notice that this class implements the IHandleMessages interface, which indicates NServiceBus that this class has a handler for incoming messages. The Handle method performs the operations with the received message. I have kept it very simple without any exception handling.
Also note that we must include both MyMessage and NServiceBus namespaces here.

Step 2.4: Add a new class file and name it EndpointConfig.cs. Simply inherit the interfaces IConfigureThisEndpoint and AsA_Server to enable NServiceBus to know that this is configured as the server.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NServiceBus;

namespace MyServer
{
    public class EndpointConfig: IConfigureThisEndpoint, AsA_Server 
    {
    }
}

Step 2.5: Add the following sections to the App.config file. The two main sections here are MsmqTransportConfig and UnicastBusConfig. The MsmqTransportConfig section species the name of the queue that this server represents. This name is then used by client to send messages to. The UnicastBusConfig section is left blank here as the server does not need to be explicitly configured to send messages back to the client queue, if the server needs to respond back that is. Again, for the simplicity of this tutorial, we only consider a one-way client-server communication here.

Step 2.6: An additional step that is required here for the server is to enable NServiceBus host to load from remote sources. I faced a runtime error when building the project without explicity configuring this. Add a new App.Config file and rename it as NServiceBus.Host.exe.config. Enter the following config code in this file.

Build MyServer project and move on to the final module of this tutorial.

Modile 3 – Create a Web Client
Step 3.1: Add a new Web Application project.

Step 3.2: Add references to the following dlls. Its better to copy the dlls from the binaries folder to the local bin folder of the project and add a reference locally. Please note that the Message.dll should be copied from the output folder of MyMessage project.

After the references are added, the References section would look like below.

Step 3.3: Modify the Default.aspx page to include the following two textboxes and a submit button.

Step 3.4: Configure the global.asax file to include the settings for NServiceBus as below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using NServiceBus;

namespace WebApplication1
{
    public class Global : System.Web.HttpApplication
    {
        public static IBus Bus { get; set; }
        void Application_Start(object sender, EventArgs e)
        {
            Bus = NServiceBus.Configure.WithWeb()
                .Log4Net()
                .DefaultBuilder()
                .XmlSerializer()
                .MsmqTransport()
                    .IsTransactional(false)
                    .PurgeOnStartup(false)
                .UnicastBus()
                    .ImpersonateSender(false)
                .CreateBus()
                .Start();
        }

Step 3.5: Write the following simple piece of code in the code behind file of Default.aspx. The button_click handler creates a message object for MyMessage and sends it over the bus configured in global.asax.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyMessage;
using NServiceBus;

namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            var message = new MyMessage.MyMessage { id = Convert.ToInt32(txtId.Text), name = txtName.Text };
            Global.Bus.Send(message);
        }
    }
}

Step 3.6: Modify the Web.config file to add the following sections along with the exisiting configuration settings.
The only difference between the config section for the server and this client is that we explicitly specifiy the server’s queue name in the UnicastBusConfig to send messages to. without this, NServicesBus will not know which queue to send messages to. Also, give a name “MyWebQueue” to the client queue in the MsmqTransportConfig section.

With this step, Module 3 is completed and is all ready for Build.

Final Review – Before you build the entire solution and run it, you need to perform a quick checklist to ensure that you do not encounter run-time errors (which I had initially faced).

Check 1: In the NServiceTest solution properties, select Multiple startup projects and set Action as Start for MyServer and WebApplication1

Check 2: Open each project’s properties and verify whether the Target Framework is set to .NET Framework 4 and the Platform Target under Build tab is set to Any CPU for all the three projects. Any inconsistency in these settings might result in run-time errors.

That’s It! You are all set to run the solution. When you hit the run button, your server would open up in a console and will be waiting for a message from the client. If you look at the MyServer’s console, you would see a message MyServerQueue initialized which indicates the server queue is ready to receive messages.On the browser, the web client will open the default page ready to send a message.

Once you enter the values in the text boxes on the form and hit the submit button, the server’s console will immediately display the values sent.

That brings me to the end of this post. I hope this helps beginners of NServiceBus to kick-start their implementation in real-time projects.