Microsoft 70-556 Exam - MCTS Performance Point

Last week I completed and successfully passed the 70-556 exam - which gives me an MCTS PerformancePoint certification.

The exam was relatively simple - I scored highly in all areas but one. Obviously a weak point there on my part... my success in the other areas was due to a week and a bit of study using the Rational Guides to PerformancePoint (authored by my esteemed colleagues Adrian Downes and Nick Barclay) - the Planning volume and the Monitoring & Analyzing Volume. Much thanks to them!

The exam is strongly skewed towards Planning - only a few questions on Monitoring & Analysing - and the M&A questions were very simple. There are quite a few questions on Business Rules and Configuring Models & Dimensions - and these are complex areas so spend time on them in your study.

If you are doing the exam and using the Rational Guide books, don't ignore the Bonus chapters - you'll need them!

Next up is the 70-445 (MCTS SQL BI) which is much harder... I'm currently spending a lot of time with the Microsoft Training kit. Wish me luck...

Using TSQL Queries for AD Authentication

Yes you can directly query AD using TSQL queries. And it's REALLY easy.

Why you ask – well, LDAP can be painfully slow if you have to trawl through groups eg if you are storing the results in a table or outputting them to screen or suchlike. I've found this method to be pretty reliable and reasonable with regards to performance.

First things first… on a SQL server, you need to create a linked server to AD as follows:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

That's all the configuration you have to do. You are good to go!

Below is an example of the TSQL syntax.

Note that the DC is the domain in which the AD resides – in this instance it has three parts MyDomainName,DC=MyTopLevelDomain,DC=MyDomainCountryCode eg DC=microsoft,DC=com,DC=au. In this example, I am returning the username of all users who are a member of the AD group called GAMyADGroupName. This example checks if a particular user is a member of a particular AD group:

SELECT *
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName
FROM ''LDAP://DC=csr,DC=com,DC=au''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND memberOf = ''CN=GAMyADGroupName,OU=Other,OU=Global Groups,OU=Security Groups,DC=MyDomainName,DC=com,DC=au''
AND sAMAccountName = ''MyUserName''')

SSIS Synchronous/Asynchronous Script Transformations in a Data Flow

Asynchronous just means it consumes all rows before progressing to the next step in the data flow – unlike most other transformations where the entire data flow is executed for each row before the next row is commenced. Script transformations can be synchronous (eg Lookup) or asynchronous (eg Sort or Aggregate).

A synchronous script transformation is identified because the output is linked to a specified input. This is done in the Inputs and Outputs area of the task by entering the ID from the Input properties in the SynchronousInputID field of the Output properties. For each row of input, there will be one row of output.

An asynchronous script transformation is identified firstly because the SynchronousInputID field of the Output properties contains "None" or "0". When a new row is to be added, it is explicitly done using:

Output0Buffer.AddRow()

In order to aggregate data, flatten rows or concatenate data from different rows, you simply set a flag to identify when a new row should be added. To do this you need to declare a boolean in the class but outside the main sub:

Public Class ScriptMain

Inherits UserComponent
Private blnFirstRow As Boolean = True
Dim opMessage1 As String = ""

Public Overrides
Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

In this example, I'm taking all rows and flattenning them into one row so the first step in my sub is:

If blnFirstRow = True Then Output0Buffer.AddRow()
blnFirstRow = False
opMessage1 = opMessage1 & " " & Row.ipMessage.ToString

Note that I then concatenate a variable with a new value… Alternately, you could set a flag so that a new row is added when certain conditions are met such as the value of the ipCustomerNo changes.

One other thing to add - you need to use the PostExecute() sub to write to variables.

VS Db Pro – Script Deployment - Database Context

When deploying databases using Db Pro the scripts which are executed have a database context. Visual Studio will automatically include a 'USE [YourDbName]' where YourDbName is taken from the project's BUILD property. This is the context for the scripts to execute. This is important because if you change it in any of the scripts it can mess with your deployment.

For example, you create a script called 'AgentJobs.sql' in the Post-Deployment folder and add a reference to the script in Script.PostDeployment.sql. This means your AgentJobs script will be executed when you do a deployment. Now assuming you add a 'USE MSDB' statement in the script, your deployment runs successfully HOWEVER any scripts which execute after AgentJobs eg Data.sql (it executes in alphabetical order) will now have MSDB as the context rather than [YourDbName] as expected.

So make sure that you use fully qualified references to objects in other databases and DON'T use 'USE'!

Sharepoint 2007 (MOSS) – List “My” Sites

So you want to have a web part which lists the sites which "I" have access to… easier said than done.

Below is my code for doing this… except it doesn't work. Well, it works for me because I'm an administrator but not for other users. It iterates through all site collections in the farm and and does GetSubWebsforCurrentUser. Which is fine, because I have permissions to read the properties of all site collections – a normal user of course, does not!

I tried using elevated authority and all sorts of tricks but so far haven't been able to finagle it. I do have one idea… that is to drop all the info I need into a string array using elevated authority and then do GetSubWebsforCurrentUser on site collections where no errors are raised for the authenticated user. I haven't had a spare second to try that out yet but I will…

So for the benefit of others – and hopefully someone else will have a brainwave in the meantime – here is the errant code in question…

using System;
using System.Text;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Web;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Publishing;

namespace CSR.Sharepoint.WebParts

{

[Guid("0d546513-7447-4211-8fc9-75117af712b0")]

public class MyCollaborationSites : System.Web.UI.WebControls.WebParts.WebPart

{

public MyCollaborationSites()
{
this.ExportMode = WebPartExportMode.All;
}

protected override void Render(HtmlTextWriter writer)
{
SPWebApplication oWebApp = SPContext.Current.Site.WebApplication;
SPSiteCollection oSites = oWebApp.Sites;

foreach (SPSite site in oSites) {
SPWeb oWeb = site.RootWeb;
int ilevel = 1;
if (!PublishingSite.IsPublishingSite(site))//don't include portal
{
GetSubSites(ilevel, oWeb, writer);
};
};

}

private void GetSubSites(int ilevel, SPWeb oWeb, HtmlTextWriter writer)
{
Boolean alternate = false;
foreach (SPWeb oSPWeb in oWeb.GetSubwebsForCurrentUser())
{

if (ilevel != 1)
{
if (alternate == true)
{
writer.Write("<tr><td class=csr-RowGrey><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
}
else
{
writer.Write("<tr><td class=csr-Rowwhite><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
alternate = true;
}
}

if (oSPWeb.GetSubwebsForCurrentUser().Count > 0)
{
ilevel = ilevel + 1;
GetSubSites(ilevel, oSPWeb, writer);
}
}
}
}
}

Sharepoint 2007 (MOSS) - Unpublishing/Expiring a Site

It's not immediately obvious how to expire a site… particularly for used to SPS 2003. In 2003, you could just set the end date to sometime in the past or if you really wanted to, the start date to some time in the far off future. Unfortunately with MOSS it's a little more complicated.

You'd think that the "Unpublish" option on the Workflow menu of the Page Editing Toolbar would do the trick but sadly, no! What it does do is roll the page back to it's previous version. Definitely handy. But what if you want the page (which has several versions) to be entirely invisible to users? In theory, you could delete it… but I ask you, how many of your content managers will want to delete page just because it's expired now? None I'd wager!

So what's the answer? Page Scheduling (also referred to as Publication Dates). You set a start and end date for publication and the page is only visible during that period. The period may be from "Immediately" to "Never" so you don't have to think about it to much. Easy huh? Yeah, well….

Every site CAN have publication dates however not every site DOES have them. It has to be enabled. To enable Page Scheduling do the following:

• Go to the site in question
  
• Go to Site Actions > Site Settings > Modify Pages Library Settings


• From Versioning Settings, ensure Versioning and Content Approval are both enabled
• From Manage Item Scheduling, ensure Enable Scheduling is ticked
  

Unfortunately no, it does not apply to subsites of the current site. For the administrators who want to know why - because what you are effectively doing is amending the properties of the Pages list in the site content for that site.

So still you ask, how do I expire the page???? Ok…

1. Show the Page Editing toolbar
2. In the status section of the toolbar, it will have the version, the status and the Publication date. Click on the date (it's a hyperlink)
   
3. The page scheduling options will open
4. Set the end date to the desired date/time
5. Hit OK and from the Page Editing Toolbar hit Publish
   

Again, seemingly simple…. unless you're changing pages migrated from SPS 2003. Mostly I've found the Start and end dates are blank. You can't set the start date or the end date to some time in the past - you will get an error message telling you so. It's a slight irritation sure but is it really necessary? What's the big deal already… just let me set the date to a point in the past! So you can set the start date to 'Immediately' and the End date to 5 minutes from now and once you hit Publish and those 5 minutes have past, the page will magically no longer be visible.

SQL CLR .Net Function - Split

Having needed a split function many times in the past, I've always utilised patindex and done it the hard way…. And when it comes to nvarchar values, forget about it! So today I took a few minutes to write a SQL CLR function to do it.

The function takes a string and a delimiter and returns a table of string values.

Here are the instructions for those who may want to do the same thing…

1. Create a Visual Studio project of the type Database Projects \ SQL Server\ SQL-CLR \ C# SQL Server Project – I called it udfclrSplitString but you know, call it whatever you like!
   
2. Add the following code:
   

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   using System.Collections;

   public class CSRSplitString
   {
   [SqlFunction(FillRowMethodName = "FillRow")]
   

   public static IEnumerable udfclrSplitString(SqlString InputString,SqlString Delimiter)
   
   {
   
   return InputString.Value.Split(Delimiter.Value.ToCharArray(0, 1));
   }
   
   public static void FillRow(object row, out string InputString)
   {
   InputString = (string)row;
   InputString = InputString.Trim();
   }
   }
   

3. Build that sucker
   
4. Drop the dll onto the SQL server somewhere relevant
   
5. If CLR is not already enabled on the server, execute the TSQL code:
   

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO
   

6. In the desired database, execute the following TSQL code:
   

   CREATE ASSEMBLY CSRSplitString
   FROM 'D:\<YourDLLName>.dll'
   WITH PERMISSION_SET = SAFE
   

7. In the desired database, execute the following TSQL code:
   

   CREATE FUNCTION udfclrSplitString
   (@String nvarchar(4000),@Delimiter nvarchar(1))
   RETURNS
   TABLE (ArrayItem nvarchar(4000))
   AS EXTERNAL NAME CSRSplitString.CSRSplitString.udfclrSplitString;
   
   Where external name is MyDotNETAssembly.MyClassname.Method

8. To test it, try:
   

   select *
   from udfclrSplitString('Kristen,Patrick,Julian,Werner,Koe',',')
   
   You should get 5 rows back …

9. If you want to test it using a table column as the input try something like:
   
   Select a.name as DbNameOriginal,s.*
   from msdb.sys.databases a cross
   apply udfclrSplitString(a.name,'_') s
   where a.name like '%_%'
   
   You will need to have the function in the msdb database for this example to work
   
   
10. To make this function automatically available in all new databases created on that server, execute steps 6 + 7 in the model system database.