Practical Business Intelligence

May 23, 2011

Configuring Constrained Delegation in SharePoint 2010

Filed under: Business Intelligence, Kerberos, SharePoint — Duncan Sutcliffe @ 1:58 pm

I’ve been doing a *lot* of environment build and configuration work recently, specifically focusing on authentication and the right way to configure Kerberos so that all the business intelligence components of a SharePoint deployment can pass user credentials between themselves and down to data sources.  One of the key things for SharePoint 2010 is that you will need to configure constrained delegation, whereas in earlier versions you could get away with unconstrained delegation.

There will be a proper series of posts about Kerberos coming along (it’s a big subject with a lot of words to write – I teach a two day course if you are really interested), but in the interim here’s a pictorial guide to the delegation you will need to set up.

Constrained Delegation Pathways Map

The key points are:

  • Web site account delegates to itself, to any other web site accounts and to SSRS account over HTTP
  • SSRS account delegates to all report data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc
  • Claims to Windows Token service account delegates to all PerformancePoint, Excel, Visio and InfoPath services data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc
  • PerformancePoint, Excel, Visio and InfoPath services all delegate to all their required data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc

April 15, 2011

Listing all the SPNs for a service

Filed under: Kerberos — Duncan Sutcliffe @ 6:26 am

This came up as a request on the Twitter hashtag #sqlhelp yesterday: how can you list all the SPNs for all the SQL Server instances in a domain? This is pretty easy to achieve using setspn.exe:

setspn -T (forest) -F -Q (service)/(server)

Both the service and the SPN can use wildcards so the following command will give a list of all the SQL Server SPNs in the entire forest “my forest”.

setspn -T myforest -F -Q MSSQLSvc/*

January 13, 2011

Hiding Subreports from the List of Available Report Documents in SharePoint

Filed under: Business Intelligence, Reporting Services, SharePoint, SharePoint Integration — Duncan Sutcliffe @ 9:11 am

Does the SharePoint library which contains your SSRS reports look a bit like this?

SharePoint list with reports and subreports

The user is asked to look at a list of RDL files and guess which one is the top level report, and which are subreports which are only intended to be consumed inside a ‘container’ report or are used as the target of hyperlink actions inside another report.  But it’s easy to hide reports which are not intended for users to run individually by making use of SharePoint’s View features.  This example has screenshots from SharePoint 2010 but you can also do the same thing in MOSS 2007.

The goal is not to physically move the subreports, but to make them hidden from the list a user is presented with by categorising them in a managed way.  So, from the Library ribbon, click “Create Column”.

SharePoint list create column button

In the next dialog, fill in the following values:

  • Column Name: "Report Type"
  • Type of information: "Choice"
  • Require that this column contains information: "Yes"
  • Enforce unique values: "No"
  • Choices: "Report", "Subreport"
  • Display choices using: "Drop-Down Menu" or "Radio Buttons"
  • Allow ‘Fill-in’ choices: "No"
  • Default value: "Choice", "Report"
  • Add to default view: Checked

You should end up with a page something like this:

Add column to categorise as report or subreport

After you create the column, you will be returned to the library. As long as you checked the “Add to default view” box when you created the column, you should now have an additional column headed “Report Type”, with no values for any of the reports.  So, edit the properties of each report and set the value for the new “Report Type” property to either “Report” or “Subreport”.

Add report type property to the document in the SharePoint list

Once you have completed categorising your reports and subreports, create a new view by clicking the “Create View” button in the library ribbon.

SharePoint list create view button

Select “Standard view”, then create the view with the following settings:

  • View name: "Reports only"
  • Make this the default view: Checked
  • View audience: "Create a public view"

You can add in any additional columns or sorts as you wish, then in the “Filter” section, enter the values you need:

Add filter for reports only

After creating the view, you will be taken back to the report library but now you are only seeing the top level reports.

Library with reports only shown

December 8, 2010

Determining if an Email Address is Valid in SSIS

Filed under: ETL, Scripting, SSIS — Duncan Sutcliffe @ 10:47 am

Had a requirement to establish whether a provided string in a source system matched a valid pattern for email addresses today.  Not to actually try to send a mail to the address, just to check that it was theoretically valid.

There is a useful bit of regular expression code available from Microsoft at so all I had to do was work it into a script component.  Here’s how.

Add a script component to the data flow and set the properties you’ll need.  The input column will be the email address field you want to check, then add an output column with the DataType Boolean.


In the script itself, you need to add the regular expressions library to the script’s using statements:

using System; using System.Text.RegularExpressions;

Immediately after this, add a new class to actually parse the email address and use the regex to determine its validity:

public class RegexUtilities { public static bool IsValidEmail(string strIn) { // Return true if strIn is in valid e-mail format. return Regex.IsMatch(strIn, @"^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))" + @"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"); } }

Finally, inside the ProcessInputRow add some code to execute the validation class on each row:

public override void Input0_ProcessInputRow(Input0Buffer Row) { string emailAddress; emailAddress = Row.EMAILADDRESS; bool valid = false; if (RegexUtilities.IsValidEmail(emailAddress)) valid = true; else valid = false; Row.ValidEmailAddress = valid; }


The field ValidEmailAddress will now be on the dataflow.

December 7, 2010

Star Schema Modelling Errors–Dimensions with the Same Cardinality as the Fact Table

Filed under: Business Intelligence, Data Modelling — Duncan Sutcliffe @ 12:00 pm

What’s wrong with this data mart model?


The sales fact table has an associated dimension which describes the sale record.  There is a surrogate key relationship between the fact and dimension tables but there’s also the business key (SaleNumber) from the source system.

Why is this bad?

  • There will be a one-to-one relationship between the fact table and the dimension, which means the dimension is potentially going to be huge.  Joining between fact and this dimension will be slow at query time.
  • The dimension is very poorly built for Analysis Services.  If Status only has three possible values but those three are being repeated on every line instead of normalised into their own dimension, SSAS build times will be bigger than they should be.
  • If you have facts that get updated in the source, you are going to have to manage this in two places in the data warehouse.  A type 2 SCD with millions of rows in it like this one is going to be painfully slow to populate on your ETL runs.
  • Some of the attributes should have their own dimensions – status, payment method, order method, carrier – or maybe a junk dimension.  This will compress the dimension to the unique values of each of these fields rather than making it as big as the fact table itself.
  • The CarriageWeight field is meaningless as an attribute because no-one would ever want to analyse orders by their carriage weight.  So it should be included on the fact table, though in an SSAS cube its AggregationType would be set to None.
  • Delivery address fields could go in several places:
    • Into a delivery address dimension
    • Onto the fact table if they aren’t used for analysis purposes
    • Into the customer dimension if each customer only has one delivery address

So – dimensions which describe facts in a one-to-one way should be avoided.

Older Posts »

The Shocking Blue Green Theme. Create a free website or blog at


Get every new post delivered to your Inbox.