Thoughts of a Freelance Dolphin Trainer

Azure – It’s Not Big and it’s Not Clever

2009-11-02T12:37:00.001Z

I’ve played with Azure and yes it does what it’s supposed to but coming from a business intelligence background I’m struggling to see what kind of advantage my area of focus will gain. I would love to see huge databases accessible in the cloud attached to huge cubes that use huge ROLAP partitions to run huge queries but until that time comes along I’m going to sit on a huge fence, watch and wait.

One project I’m working on has dimensions with over 50 million members in. Due to the business and the way it works a Process Update has to be performed every night. Lots of attribute hierarchies and lots of members means lots of time taken to complete. Throwing more hardware at it makes this faster but

what happens when the cost of the infrastructure exceeds the value they receive from their analysis. This client will look at how they can reduce costs elsewhere whether it be licensing (open source perhaps) or the platform (Amazon EC2 perhaps) neither of which are going to help Microsoft.

When I first saw that the Azure platform was changing from it’s early form into what I considered a natural progression of the SQL Server platform from Express to Enterprise to Azure I was pretty positive. This coupled with Analysis Services could mean smaller companies that produce a lot of data could get top class analytics without having to break the bank with massive hardware expense. But I haven’t seen anything that makes me feel confident that this will happen. Perhaps the Azure OLAP platform will be huge 128bit version of Excel with PowerPivot (don’t get me started) who knows, someone should at least.

Azure, PowerPivot, Reporting Services, Performance Point, ProClarity, MDM. In my opinion all of these things should be part of a single cohesive strategy to allow users to access data in a simple way whilst ensuring organisations can maintain a level of data governance and I’m just not seeing it. Why do I have to still tell a client that they need to use this tool because that’s the one you can change the line colours in but you’ll need to use the other tool for pie charts and oh you want it integrated into your Portal then you’ll need this one instead. It’s scatter gun, not particularly professional and very worrying.

I’m seeing this from a very focused view but from my experience a real world view and I just can’t see Azure fitting in to it at the moment. I would love to have my mind changed so please do.

Technorati Tags: Azure,Business Intelligence

The Grand Microsoft Crossover

2009-07-30T09:50:00.000+01:00

The rumour on the grapevine is that Microsoft are preparing an all out offensive on rivals Google, Apple and Sony by drawing upon their expertise in the various groups around the Redmond giant.
With the positive reception gained by Project Natal at E3 earlier this year and the planned release of Windows 7 and Office 2010 over the next 18 months, Microsoft are embarking on a strategy that they hope will smite their competition.

Codenamed "Project Flap" Microsoft will make the Natal hardware the de-facto method for interfacing with all of their other offerings. Users will be able to use Natal to retrieve lost Word documents by either jumping up and down furiously in front of their screen or by using specially adapted four letter keywords. "This will revolutionise how people will work in the next decade" a Microsoft source close to Project Flap is quoted as saying. But it's benefits don't stop at improved interaction with the next generation of software "our research has demonstrated that looking like a complete tool in the workplace has an impact on how people are perceived". When pressed on whether this impact would be negative the source responded, "at this time we can't comment on the details of the research".

Before everyone pins their hopes on every office looking like a rehearsal room for Britain's Got Talent, Microsoft's rivals are sure to respond. Google is rumoured to be looking at a free camera add on for Google Docs that will work in a similar way to Natal. The major difference being that users will have to stand facing away from the screen as adverts are projected on to their backs.
Sony also has plans in this space. They will introduce a new PS3 / Vaio office application that will provide users with a cut down feature set that will only work with Sony's proprietary SAD (Sony Augmented Document) format. Whatever Apple will do will of course be White and expensive.

So as Microsoft's rivals prepare to return fire and stick 2 fingers up at Natal they had better take care, flicking the "V" is the new command in Windows 7 for formatting your C drive..!

Technorati Tags: Bored,Natal,Office 2010,Windows 7,Defective Train

Over The Last Several Weeks I Have Been Mostly Listening To…

2009-07-28T17:13:00.001+01:00

Hands – Little Boots

La Roux – La Roux

Lungs – Florence + the Machine

Spotify Playlist

Technorati Tags: Little Boots,La Roux,Florence and the Machine,Florence + the Machine

A Little Something About PPS Filters (part 3)

2009-07-28T16:57:00.001+01:00

It’s been a while since I started on this topic so hopefully I’ll remember what I was attempting to say.

For a particular client we have had issues with a multi level dimension hierarchy that contains several thousand members. As I’ve said before and numerous people have experienced PPS filter performance is not great (word on the grapevine is though this is no longer an issue in v2). For various reasons, creating multiple dashboards to satisfy different users and restricting the users using OLAP security weren’t options so we came up with a “third way”.

The third way was to provide the users with the ability to change their preferences and alter what they could see in the report hierarchy. First we needed to provide the users with a screen where they could make their selections. This was as simple as providing a PPS dashboard page that contained a filter and a report that used the filter.

The filter is based off of tabular data as explained in the last

Technorati Tags: PerformancePoint,PPS,Filters,Analysis Services

post. The data itself is a parent child data set generated from the dimension that I want to restrict turning my source table of the dimension from something that looks like this;

To something that looks like like this;

There are reasons I’ve done things like place a letter a the beginning of each of the ID’s. First it ensures that every member has a different ID and secondly it identifies the level of the original hierarchy the member is from without having to write queries to work out this out.

This has been placed into a view which is just a series of select statements from each level of the hierarchy with unions between. With that source view a tabular filter can be built and put into a dashboard page. At the top of the view though I’ve put a statement in for the “All” member. Remember this isn’t Analysis Services so there’s no dimension and no “All” member.

Again referencing back to the previous post on this, for the filter value to be retained in the ParameterValues table you will need to associate it with a report. A simple reporting services report will do and my preference is to provide the user with some feedback on the selections they’ve made. If they have selected the “All” member then they get a list of all of the locations otherwise only the members that have been selected.

The reporting services input parameter is from the filter passed into some SQL that in this case looks like this;

IF EXISTS (

    SELECT 1

    FROM vwDimTown

    WHERE 'ALL' IN (@Location))

BEGIN

   SELECT DISTINCT CountryDesc, RegionDesc

   FROM vwDimTown

END

ELSE

BEGIN

   SELECT DISTINCT CountryDesc, RegionDesc

   FROM vwDimTown

   WHERE 'D' + CAST(CountyID AS varchar(5))

      IN (@Location)

   OR 'C' + CAST(RegionID AS varchar(5))

      IN (@Location)

   OR 'B' + CAST(CountryID AS varchar(5))

      IN (@Location)

END

With the reporting services report on the page we now have a mechanism for capturing a users preferences like so;

Retrieving the preferences is the next part so for that we build on the SQL on the previous post that shows how to extract rows from the ParameterValues table in a meaningful way and for this we will put the logic into a stored procedure.

The procedure will perform a number of tasks in addition to pulling the selected members out of the PPSMonitoring database. We also need the final output to be formatted as an MDX set in the format {member 1, member 2, member 3, member n}. So lets break down the procedure into its constituent parts. First the declarations.

CREATE PROCEDURE uspGetFilterPreference

     @Login varchar(255)

    ,@FilterID varchar(50)

    ,@StrToSet varchar(max) OUTPUT

The first of the variables, Login, is the logon of the person whose preferences we are trying to find. FilterID is the GUID identifying the filter we are interested in. Finally StrToSet is the output variable that will contain my final MDX set string.

DECLARE @Keys table(

MemberKey varchar(255))

DECLARE @OutputString varchar(max)

INSERT @Keys(MemberKey)

SELECT

    REPLACE(

        REPLACE(

            CAST(d.Members.query('LocationID')

                AS VARCHAR (255)

                ), '<LocationID>', ''

            ), '</LocationID>', '')

    AS MemberKey

FROM PPSMonitoring..ParameterValues

CROSS APPLY SerializedXml.nodes

    ('/NewDataSet/MyDataTable')

    AS d(Members)

WHERE [Login] = @Login

AND ParameterUniqueName = @FilterID

This section gets the values out of the ParameterValues table and loads them into a table variable called Keys. Along the way we strip out any XML syntax (element names, tags, etc.) leaving a table variable comtaining the key values from the tabular filter.

IF EXISTS (

    SELECT 1

    FROM @Keys

    WHERE MemberKey = 'ALL')

BEGIN

    SELECT @OutputString =

       '[Town].[County].Members'

END

Here we check to see if the “All” member has been selected. If it has then we set the OutputString variable to be all members from the County attribute hierarchy of my Town dimension and NOT the user hierarchy. This is really important for later on.

If the “All” member has not been selected then we work out what members have been selected and using a set of union statements get a list of true dimension keys

ELSE

BEGIN

  SELECT @OutputString =

    SUBSTRING((

      SELECT ', '

            + '[Town].[County].&['

            + CAST(ID AS varchar(10))

            + ']'

      FROM (

       SELECT ID

       FROM DimTownTabular

       WHERE ParentLocationID IN

         (SELECT MemberKey FROM @Keys

          WHERE LEFT(MemberKey, 1) = 'B')

       UNION

       SELECT ID FROM dbo.DimTownTabular

       WHERE ParentLocationID IN

         (SELECT MemberKey FROM @Keys

          WHERE LEFT(MemberKey, 1) = 'C')

       UNION

       SELECT ID FROM dbo.DimTownTabular

       WHERE LocationID IN

         (SELECT MemberKey FROM @Keys

          WHERE LEFT(MemberKey, 1) = 'D')

) a

  FOR XML PATH( '' )), 3, 1000 )

END

This is why we have the ID column and the letter prefix. It makes this bit a lot easier to manage. The output of this and the value that get dropped into the OutputString variable is almost exactly what we want. The only issue is that by using the FOR XML PATH statement to produce a comma delimited string of the members it replaces “&“ symbols with “&”. A quick …

SELECT @StrToSet = '{'

                 + REPLACE(@OutputString,'.&amp;', '.&')

                 + '}'

… fixes that and puts the curly brackets around to finish off our set syntax and the procedure as a whole.

Run the procedure against the username and filter you’re querying and depending on how many members you selected in the tabular filter you should get something like this;

{[Town].[County].&[26], [Town].[County].&[63]}

Now I would recommend that everyone goes out and gets this book

MDX Solutions
(Amazon link)

And go to chapter 10 where there is a section called “Using Stored Procedures for Dynamic Security”. This explains how to use a simple analysis service stored procedure (DLL) to affect what members in a dimension users are able to see. All the code is doing is retrieving a number of rows from a SQL query and passing it into analysis services security as an MDX set object. In my stored procedure I made it simpler and just executed a stored procedure and output a string.

Unfortunately I haven’t got this all packaged up as a project but my recommendation would be to create a C# project build the DLL and also build a command line application that references the DLL just to test everything is working OK.

Also things like error handling and connection string configuration to where the PPS database should all be handled in this code. My function is called “GetPreferredCounties” which has two input variables. The username and the filter ID, both as strings.

After registering the assembly with the Analysis Server we can now setup the final piece. In one of the roles in your cube (you have created one right?), within the dimension data security for the County attribute of the Town dimension I can now put in the syntax to make the stored procedure call and retrieve the string.

In my example it looks like this.

STRTOSET(PPSFilterASStoredProcs.GetPreferredCounties(

    UserName()

   ,"822e399a-af44-4cf3-be19-f1602ca14afb"))

The USERNAME function will pass through the current users logon credentials to the function and the STRTOSET function will take the output string from the function and convert it into something analysis service will understand is a set.

If all has gone well Analysis Services is now using the output from the stored procedure to provide a restricted list of dimension members to a user. A list that the user themselves can control without any other system being put in the way or elevated privileges being provided.

Build another page on your dashboard and this time create and add a report that just displays some info about your filtered dimension. Attach this to a filter based on the analysis services dimension (I tend to use named sets) and now you will only see the members that match your preferences selection.

Now to tidy up that hierarchy a bit. All those parent members that are just getting in the way and making the navigation more complex than it needs to be. Pay a visit to Boyan Penev’s blog and take a look at a post on Filtering Unneeded Dimension Members in PerformancePoint Filters. By making the source query or named set for your filter based on the logic in the post you just get the top most level necessary which makes the filter a hell of a lot neater and with the added bonus of the top level member becoming the default when you first access it.

There’s a lot going on in this post and unfortunately I haven’t had the chance to package it up into something I can redistribute easily but if there’s any questions then ask away and I’ll help if I can.

A Little Something About PPS Filters (part 2)

2009-06-04T09:59:00.001+01:00

Here I wanted to go through what happens when you click apply on a PerformancePoint filter. Well first lets look at the simple OLAP based filter that I put together for the last post.

On my blank dashboard page with only a single filter I click apply and then I’ll have my selection displayed so the filter looks like it’s registered the filter change.

The problem is that when I refresh the page PPS doesn’t remember my last selection and will return the All member. This isn’t what PPS filters are supposed to do but the reason is simple, what’s the point of having a filter if there’s no report connected to it?

After putting a simple reporting services report on the page and trying the filters again no problems. I can close the browser, log out, reboot my machine no matter what you do the parameter selection is remembered. So lets ask the question, how?

When a user accesses a report linked parameter their selections are stored in the PPS monitoring database in a table called ParameterValues. This table consists of a filter id, which is a GUID as a string, the users login credentials, the last update time and finally a column called SerializedXml.

SerializedXml is an xml typed field that contains a users actual selections for that particular filter. Select * from the table in management studio and click on one of the values in the SerializedXml column and you can see the xml displayed. Forget the schema tags and looking lower down in the xml document to the elements called ‘MyDataTable’.

<MyDataTable>
<Key>c431349de280b5a3fc3f921d5ebffd67</Key>
<DisplayValue />
</MyDataTable>
<MyDataTable>
<Key>18eb638b9e0c1c9ab64aaacdcb21d4bd</Key>
<DisplayValue />
</MyDataTable>

This is where one particular difference between OLAP and tabular filter appears. Look at a the SerializedXml value for an OLAP based filter and you’ll see a GUID appear in the key element. This would seem to be the dimension property MEMBER_GUID but I haven’t been able to confirm this on Analysis Services 2008 but it seems logical enough.

When looking at the SerializedXml for a filter based on tabular data the differences are quite clear.

<MyDataTable>
<LocationID>England</LocationID>
<LocationDesc />
</MyDataTable>
<MyDataTable>
<LocationID>Scotland</LocationID>
<LocationDesc />
</MyDataTable>

With tabular filters the key assigned within the parent / child relationship is the value stored in the xml and its possible to query it as well using SQL Servers xml query features. The example above can be queried using the following syntax;

SELECT d.Members.query('LocationID')
AS MemberKey
FROM ParameterValues
CROSS APPLY SerializedXml.nodes
('/NewDataSet/MyDataTable')
AS d(Members)
WHERE [Login] = @Login
AND ParameterUniqueName = @FilterID

By providing the login and filter id parameter we can get the selection for a single users filter. The result set is again xml based returning a row for each filter selection.

MemberKey

<LocationID>England</LocationID>

<LocationID>Scotland</LocationID>

The element names and tags can be cleared away easily enough with some string manipulation or if anyone know how the SQL xml query could be tweaked to use return just the values themselves I would be interested to hear.

In the next post I’m going to show how this can all be put together to demonstrate how these ideas can be applied to real world scenarios.

This Week I Have Been Mostly Listening To…

2009-06-04T09:54:00.001+01:00

Kitsuné Maison Compilation 7 – Various Artists

Spotify Playlist

A Little Something About PPS Filters (part 1)

2009-06-03T15:02:00.001+01:00

If you have a lot of members, say in the thousands, that you want to display in a PPS filter then you’re going have problems. The current version of PPS doesn’t handle them to well and I surmise that it’s one of the reasons that there’s a configurable setting in the web.config allowing to set the maximum number of members returned in a filter.

Things are compounded when you have hierarchical filters as the number of members is increased by parent members. The obvious way to get around this off the top of your head would be cascading filters but this has its own issues one in particular is that I want the user to be able to use all of the hierarchy in the reports from the all level to the leaf level members.

Hopefully the architecture of PPS v2 will have corrected this little foible but in the meantime we have to work around this. I’m going to talk in the next few posts about the filters and demonstrate a potential workaround but wanted to start off with a bit of an overview on two types of filters available in PPS and that’s OLAP based and tabular based.

The majority of people will have put together an OLAP data based filter. It’s very simple and you can choose to either select the members directly from the dimension, use a named set or a MDX query. I would hazard to guess that most developers will use either named sets or MDX queries.

I’ll use a simple MDX query to generate a set from a dimension I’ve got called “Towns”. This dimension contains a single user hierarchy that looks like this;

Country
- Region
  - County
    - Town Prefix
      - Town

As I only want to display data to the county level in my filter I can set this up with this MDX;

DESCENDANTS(
[Town].[Town].[All] // The top level member
,[Town].[Town].[County] // down to the County level
,SELF_AND_BEFORE) // including all members

When the filter is built and deployed you’ get something that looks like this;

To achieve the same with a tabular filter you need to put together a parent child structured data set. As my source table has individual fields for each of the hierarchy levels this will need to be created manually using a series of union statements. For example;

SELECT
'All' AS Location
,'All' AS ParentLocation
UNION ALL
SELECT
Country AS Location
,'All' AS ParentLocation
FROM
dbo.DimTown
UNION ALL
SELECT
Region AS Location
,Country AS ParentLocation
FROM
dbo.DimTown

Now none of what has been written above is new, Earth shattering or of any particular benefit to anyone apart from those who have never touched PPS before but I wanted to mention the distinction between OLAP and tabular data sourced filters as it will become quite important in the next post around how PPS remembers what your last selection was.

This Week I Have Been Mostly Listening To…

2009-03-10T15:44:00.001Z

The Airing of Grievances – Titus Andronicus

Spotify Playlist

Crash, Bang, WOLAP..

2009-03-04T18:17:00.001Z

I’ve been working on a SQL Server 2008 project that uses proactive caching on some partitions to speed up the availability of the data to the end user.

For all the things that have been said and written about ROLAP in the past apart from some issues around traces I’ve found it to behave itself pretty much. Well that is until now.

Since moving into a test environment we’ve been getting a lot these errors;

The description for Event ID 22 from source MSSQLServerOLAPService cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Internal error: An unexpected exception occurred.

the message resource is present but the message is not found in the string/message table

Actually these errors would come thick and fast every couple of seconds or so until the Analysis Services service decided to stop. Profiling the Analysis Server showed the problem from the OLAP server point of view where it seemed to happen when the aggregations were being merged.;

Internal error: An unexpected exception occurred. Errors in the OLAP storage engine: An error occurred while processing the 'Current Day' partition of the 'Fact Table' measure group for the 'AS2008 Bug Test' cube from the AS2008 Bug Test database. Server: The operation has been cancelled.

I took the OLAP database that I was using and broke it down until I had the minimum amount of code and objects left in there. This meant stripping out the calculations, all of the measure groups, apart from 1, and most of the dimensions. Finally I was left with a cube that had 2 dimensions (one of which was a date dimension) and a single measure.

Within the measure group were 2 partitions, one was standard MOLAP the other a real-time HOLAP partition looking at the same fact table but different date ranges (current day and everything else). Now here’s the thing, while there is data in the HOLAP partition there are no errors and no crashing. With the HOLAP partition not processed, again no errors but as soon as the HOLAP partition was processed and had no data to retrieve then there would be errors galore resulting in the inevitable crash.

A further step back showed that this would all behave as long no aggregations were applied to the HOLAP partition. As soon as a single aggregation was put on the partition and the partition processed the errors would appear until either data was inserted into the source table, to subsequently be picked up in the HOLAP partition, or Analysis Services crashed.

Curiously converting the partition to real-time ROLAP didn’t deliver any errors with or without aggregations and same is true for any of the MOLAP types of storage but as soon as it moved back to HOLAP the errors returned.

Some extra detail. I’ve observed this on both 32bit and 64bit systems. The 64bit server this was first spotted on has the cumulative update package 3 for SQL Server 2008 installed and the 32bit is RTM so it looks like the issue is throughout the codebase. We haven’t gone as far as to install the SP1 CTP due to environment constraints. This looks like it might be a bit of a bug so I’m in the process now of packaging up something to send to Microsoft.

This Week I Have Been Mostly Listening To…

2009-03-03T17:57:00.001Z

Technorati Tags: Good Shoes

Think Before You Speak - Good Shoes

Spotify Playlist

Wanted: Data Dude-esque Features for the Rest of the SQL Server Stack

2009-02-25T09:12:00.001Z

I’m a big fan of Visual Studio Team Developer Edition for Database Professionals (mouthful..!). But what has practically always taken the sheen off of my BI deployments is the clunky way in which the other components get deployed. I like the idea of being able to perform a one click deployment and so do many of the production administrators I deal with.

DB Pro really provides complete control over all aspects of the database development and deployment so why can’t I have the same thing for my Analysis Services deployments. Between the deployment features of Visual Studio and the Analysis Services Deployment Wizard everything appears to be there to do exactly what I would like so all I’m missing is that close nit development environment integration.

Reporting Services suffices to a certain extent apart from being able to control the connection strings in data sources depending on the project / solution configuration. Yes I know that you can re-deploy the data sources or use the scripting capabilities but yet another external mechanism when Data Dude (now in its second iteration) shows exactly how it should be done.

SSIS deployment is something I’ve never been particularly fond of but you can build the files into the folder of your choice so I’ll swallow that…

Maybe there’s something coming in SQL Server 2010 / Visual Studio 2010. One can always hope…

SQL Server 2008 SP1 CTP…

2009-02-24T14:53:00.001Z

… is out and available from all good record stores or if you don’t have a good record store near by you can always try here.

This Week I Have Been Mostly Listening To…

2009-02-24T10:28:00.001Z

The Golden Spike – Sky Larkin

The Mystery of the Disappearing Packet

2009-02-17T08:56:00.001Z

No it’s not Halloween come early but a story straight from “Tales of the Unexpected”.

So we have a scaled out PerformancePoint architecture with Windows SharePoint Services on one box, the PPS Monitoring services on another and Analysis Services 2008 and SQL Server 2008 on separate machines. In this environment the dashboards deployed to the SharePoint server talk directly to the Analysis Server but in this case they didn’t.

Because there is a firewall between the SharePoint server and the other servers in this environment my immediate thought was firewall but after, a) checking the firewall rules were in place, b) checking Analysis Services was listening on the default port, and c) that we could telnet on port 2383 from the SharePoint box to the Analysis Services machine there was still no indication as to the issue.

Plugging in Microsoft’s Network Monitor (great tool, I thoroughly recommend getting it and using it). I could see the request coming in from the client but nothing going out at all on port 2383. I knew there needed to be something going out on that port as I had checked all of this with the same tool on a similarly configured environment.

Next I moved on to the OLEDB providers themselves. As this is totally SQL Server 2008 it appeared all of the correct drivers had been installed but as you notice when configuring PPS Monitoring, the installation does like to have ADOMD and SQL Client for AS 2005 and SQL 2005. After consuming some information on the interweb about SQL Server 2008 / PPS SP2 installations (here and here) I thought I would give the latest 2005 components a try. To my amazement without even a reboot packets started to trickle to my Analysis Server on port 2383.

Now I will leave you with a blast from the past. Just one thing to note for anyone outside of the UK, this is actually how everyone dances over here. On Saturday nights the Ritzys and Hippodromes up and down the country are filled with this kind of thing.

Enjoy..!

Client Tool Curiosities

2009-02-17T08:25:00.001Z

Had a bit of a weird one the last couple of days where a cube I had built was demonstrating different behaviour in a variety of tools. Debugging the cube script in Visual Studio provided the values that I expected from the script but when I then viewed the same script in ProClarity I had a very different behaviour.

I then tried the same thing in Management Studio and got a different set of numbers and then finally Excel 2007 provided me with a slightly different behaviour. So how did all of this come about. Well firstly there was an error in my calc, I was adding an integer value to a column where null occurred but this wasn’t producing an error in the visual studio debugger. In fact even though the debugger numbers appeared correct it wasn’t evaluating my script in the way I expected.

ProClarity wouldn’t show the error, that was fine but when I put same MDX that ProClarity was generating into Management Studio the correct numbers appeared. Browsing the cube in Management Studio produced a completely different set of values but that was due to me setting filters in the browser rather than navigating to the values I wanted.

Finally I gave Excel 2007 a go and actually saw the error “#VALUE” which kind of gave away what the problem was and where it was.

So there’s a moral to this story in there somewhere which I’ll leave to yourselves. But I’ve taken away from this the saying that if the number looks right and the number smells right then it’s probably right, maybe, in the right type of tool….. perhaps!

If You’re Going To Buy A Couple Of Singles From One Band This Week, Make It…

2009-02-12T14:39:00.001Z


Austere	Cradle

The Joy Formidable

How Many Departments Does It Take

2009-02-06T09:54:00.001Z

Sounds like the beginning of a music hall gag and when you think about it, it could well be.

Discussing the the merits of the Microsoft BI strategy in the wake of that PPS announcement with Mark (so as not to show any type of favouritism more comment can be found…)

here (Chris Webb)
here (Adatis)
and here (SSAS Info)
but surprisingly little here (Microsoft PPS blog)

… the conversation moved towards things as simple as charting. Now as I see it charts, graphs and the general graphical representation of data is pretty important for what we do in business intelligence and some companies understand this and specialise in providing just that. Looking at what Microsoft do with charting from the top of my head we have;

Reporting Services (Licensed from Dundas?)
Excel
PerformancePoint
ProClarity

All of this sitting under one group and three of them focussed on BI. So I again ask the question / present the gag, how many departments does it take? Do I really need to fudge my ProClarity chart to look like a PPS chart because I can’t set the colours in the PPS chart? Should it really be necessary to mock up excel micro charts in reporting services?

The overall scattergun approach here does net lend itself to a cohesive strategy and if the PPS announcement and this simple example of charting is anything to go by we can’t expect anything soon. Come on Microsoft, SQL Server and Analysis Services are bloody fantastic pieces of software. Lets get the front end strategy working as well as the back end does.

Given Half a Chance, This Weekend I Will Be Going All Retro And Playing…

2009-02-06T09:53:00.001Z

R-Type Dimensions - XBLA

Proactive Caching Weirdness

2009-02-06T09:00:00.001Z

In both 2005 and now 2008 I have both heard other people, and experienced myself, a problem where proactive caching gets itself into a bit of a loop. Running profiler against the analysis server you will see a number of trace notification received message and errors that don’t contain anything useful.

Whilst haven’t been able to find a solution for this I have got a work around and that is to change the object id of the table that the trace is against. Un-processing the partition that is causing the issue and moving the data to an identical table and changing the names appears to get round the problem although it’s a little concerning that this behaviour is common on both SQL Server 2005 and 2008.

PerformancePoint Dashboard Performance

2009-02-05T11:39:00.001Z

I’m currently working on a project that is mixing PPS, Reporting Services 2008, ProClarity Analytics Server and Analysis Services 2008 with a lot of proactive caching….. Nice…!

The development environment we’re using does the job but I had been experiencing some really wild performance issues. One dashboard deployment would be fine and then the next it would run like a dog. This then drove me down a path of tuning every aspect of the solution so I thought I would share a few of my findings up here for posterity.

The cube has a number of measure groups that in turn have a number of partitions. We’re partitioning by month due to the data volumes with a current day partition that is HOLAP on each of the measure groups. I’m using the SQL Server notifications (AS sets a trace against the target table) so everything is pretty much automatic.

Dashboards in the solution consist of a mix of reporting services, PAS and PPS native reports all joined together using PPS filters. First off the filter queries themselves, they weren’t dynamic so we went down the route of putting some server side named sets in and just calling them from Dashboard Designer. Nice and simple so far but this is where the performance problems started to manifest.

With a simple dashboard up and a small number of members in the filter named set, the dashboard was taking far too long to return. There were four filters on this particular page. Looking at profiler I was finding that a refresh of the page after a database update (remember proactive caching involved here) was causing a query to be fired against against Analysis Services and every partition in a single measure group would be read, four times.

The measure group in question happens to be the measure group that contains the cubes default measure. This I considered to be a bad thing as the production environment would have a lot more data and a lot more partitions to be read, four times, when ever a dashboard page was opened with filters.

To confirm the behaviour I created a dummy partition with a dummy measure in and made that the cube default measure. Running the same test showed four queries being fired but this time the partition being read was tiny and the filter response in the dashboard immediate. Un-processing the partition though caused everything to behave correctly without obviously the dummy measure being read.

So point 1 is be careful where you put you default measure. Next up are the reporting services reports. Creating parameters will create hidden data sets in the report that are there to support the available values list in the report parameters. Whenever the report is executed it’s going to want to get the data for those data-sets to populate the list and multi-selects. If, as is the case on this project, the reports are designed to be accessed purely through dashboards using PPS filters there is little point in having these data sets so removed them.

First for each parameter set the available values to none then remove the related data sets (you will most likely have to show the hidden data sets) and redundant data sources. Although these data sets are small the reporting services reports on the dashboard were observably more responsive.

The final overall improvement is to reduce the number of members in the filters and make sure that you have selected the right type of filter for the job. In the case of this project we’ve avoided multi-selects and opted for single select lists where possible as they seem to take less time to render on the client. Improving the performance of the filters is ultimately going to be based on the number of members you can select from. With SP1 of PPS you can now (I think) have cascading filters but in an OLAP environment where you want to select a member at any level within a hierarchy that’s not useful.

Try using clever MDX or security to reduce the member count, it really makes a lot of difference.

This Week I Have Been Mostly Listening To...

2009-02-05T10:55:00.001Z

Nights Out - Metronomy

This Week I Have Been Mostly Listening To...

2008-11-26T13:04:00.001Z

Little Death - Pete & the Pirates

Data Dude 2008

2008-11-26T13:00:00.001Z

It's finally been released.

Get it from here for all of your database development delights. You will of course have to uninstall any previous CTP previous versions you have but details of what it will or won't upgrade can be found here.

Attribute Processing Errors

2008-11-17T13:09:00.001Z

Just something to remind myself that when processing a dimension attribute make sure that there are no NULL's in addition to empty strings. One or the other is ok but when you start having both in an attribute key you will get processing errors as Analysis Services will do whatever it does and effectively make them the same value therefore you will get duplicate key problems.

This Week I Have Been Mostly Listening To...

2008-11-14T10:53:00.001Z

Another Late Night Tale - Matt Helders

It's me again.....

2008-11-14T09:56:00.001Z

Somebody asked me yesterday if I'd been updating this recently and of course I haven't as I'm a slacker so I thought I start the process off again by posting about one of my fave subjects and that's that's the continued battle 32bit / 64bit mixed environments.

I'm currently working on a real life SQL Server 2008 project with all the bells and whistles and everything so we have VS 2008 in the mix as well. Now let me set the stage....

Dev server = 64 bit

SQL Server = 64 bit

Source Database = doesn't matter (I think)

Now I'm going through a firewall so I've had to setup an alias in the configuration manager so I can specify the port of the instance of SQL server that is my source database. In there you have 32 bit and 64 bit configurations so because I'm setting everything up to make sure I can talk with the server I've gone through management studio.

SQL Server Management Studio connects fine as I'm guessing it's a 32bit process and I've set up an alias in the 32bit bucket. I can also create a SSIS package that connects to my destination server, is able to browse the source data, and will execute from the dtexecui application. But what can't I do, well for starters I can't debug as I get an error telling me that it cannot connect to the source database.

Now I create an alias in the 64bit configuration bucket and now I can debug. So what else can I do? Well next thing is to set the project property Run64bitRuntime to false and try to debug with the 64bit alias deleted. Guess what, it runs.

So the moral of the story is don't expect everything to work nicely for you out of the box all of time even if you're in a single technology environment.

Things you should remember but kind of forgot that then come back and bite you on the arse that make you just want to curl up into a little ball and cry out for mommy..... Part 1..!

2008-05-19T10:21:00.003+01:00

In SSIS lookup component is case sensitive....!

Apologies about the picture. Looks like my link got hijacked. It has been removed

This Week I Have Been Mostly erm... Playing...

2008-05-19T10:15:00.001+01:00

Say no more......

What's Been Going On The Last Few Months...

2008-05-19T10:10:00.001+01:00

I have been asked by a few people what I'm doing lately and I have been a bit vague on the details not because I want to hide away but because I really haven't been too clear on that subject myself.

Anyway here's a short update.

I left Edenbrook back at the end of February to do some bits and pieces (still MS BI stuff) for myself which has gone quite well, I'm now working on some other projects that will hopefully see the light of day over the next few months. I dropped out of the consulting game really because I decided to spend more time at home with the family and less time in hotels in various parts of the country.

The hobbies are the same with the addition of Tennis which I've taken up as I'm not allowed to play football anymore :( and any spare time is being taken up tearing it up in Liberty City.

I shall still be going to the V festival this year at Hylands Park as they appear to have pulled their finger out and got what appears to be their best line up in years, although Glastonbury having Jay Z seems to have helped with that.

I don't do Facebook, Bebo, MySpace, or any of the other so don't think me rude for not replying to friend requests etc on there. I may change my mind but then again I might not. I want a 3G iPhone, a new laptop and a quad core PC for doing all the stuff I do at home.

Oh and I went to the Natural History museum yesterday. My daughter and I thoroughly enjoyed it and here is a picture from our day.......

Apparently the Blue Whale is a lot sleeker than that but when they built this no one had actually ever seen one swimming....!

This Week I Have Been Mostly Listening To...

2008-05-19T09:23:00.000+01:00

The Duke Spirit - Neptune

This Week I Have Been Mostly Listening To...

2008-02-05T10:33:00.001Z

Laura Marling - Alas I Cannot Swim

This Week I Have Been Mostly Listening To...

2007-11-27T20:43:00.001Z

The Wombats - The Wombats Proudly Present..A Guide To Love, Loss and Desperation

This Week I Have Been Mostly Listening To...

2007-10-24T18:18:00.001+01:00

Overpowered - Roisin Murphy

Technorati tags: Roisin Murphy, Overpowered

Organising SSIS

2007-10-24T18:01:00.001+01:00

I think this has probably been blogged about and documented elsewhere to death but it's well worth posting something about (even as a reminder to myself).

SSIS package configuration is great but as always, as people use something heavily, its usage gets more sophisticated and so do the problems. One thing I like is to be able to centrally log and also centrally get configuration values but to do this I need to be able to support multiple environments (dev, test, etc.) and also multiple applications (the same variable used in different packages). This is where I've found the configuration to be little limited so in true developer fashion we came up with an alternative configuration mechanism. I won't go into the details but effectively we get all configuration values from a call to a database.

The only problem with this is that I want to be able to tell the package where the configuration database is, what environment I'm executing in or the application name I'm running under but I can't do that with hard coding a config file into the package. Of course there are alternatives. A number of server variables or registry entries but really I just want to do this the once and have control over everything else.

In my perfect world I would want to structure my data in something similar to this;

This contains pretty much everything I need all in a single location. I can have multiple configuration files for different SSIS based systems, a place for source files and if I wanted to drop in XSD files I could create a new folder or just stick it in a suitable existing location. But this is when these little issues creep in.

How do I tell my package where the SSISRoot folder is? I don't want to use configuration files as that defeats the object of having a single location where I keep my configuration files, very chicken and egg. So what alternatives are there? An environment variable can be used to point to the config file location but then I need an environment variable for every config file I have once again defeating the object. What about creating an environment variable that points to SSISRoot, that would deliver what I need. I tried this. I created the variable and called it from Start>Run %SSISRoot%, it worked and I get the folder location that I wanted. The next thing to do was to put "%SSISRoot%/Config/SSISConfigFile.dtconfig" into the path for the file however that had little success, and when I say little I mean none whatsoever.

So I'm back to square one. Hard coding the package config file into the packages. This is one of those issues I would be grateful to hear any clever workarounds that anyone has so please let me know as this is getting increasingly frustrating.....!

Technorati tags: SSIS, Organise

UPDATE to Multiple Pick List Filters in PPS 2007? Get RTM, Quickly..!

2007-10-11T14:26:00.001+01:00

Technorati tags: PerfromancePoint, PPS

Yesterday I was over the moon with joy after finding that the RTM version of PPS solved my little issue. How wrong I was as performing the upgrade on another server presented me with exactly the same problem.

After some mad panicking and searching for a solution we discovered that the version of PAS on the PPS virtual PC was different to the version on our server. Another little hunt around presented us with this....

Microsoft ProClarity Analytics Server 6.3.2209 Hotfix

...which subsequently lead us to...

A hotfix rollup package is available for ProClarity Analytics Server 6.3

...and tucked away on there was this little gem...

Issues that this hotfix rollup package fixes

This hotfix rollup package fixes the following issues:

blah

blah

blah

When you run a query in Microsoft SQL Server Analysis Services, the query takes a long time if multiple parameters are specified. Additionally, the query may time out in Internet Information Services (IIS).

Hey presto it all behaves itself now.

To summarise, you don't have to install the RTM PerformancePoint Server to get rid of the poorly performing MDX, just patch PAS but I would still recommend getting it for things like better performance on the UI and the fact it now supports integration of Reporting Services on SharePoint.

Technorati tags: PerformancePoint, PPS, Monitoring, MDX

Multiple Pick List Filters in PPS 2007? Get RTM, Quickly..!

2007-10-10T16:35:00.001+01:00

PerformancePoint Server 2007 RTM has been up on MSDN subscriber downloads for a while now, well several days anyway, so I would imagine there aren't too many people who haven't got hold of it who are doing some serious mucking around in PPS.

If you're one of those people who are on the CTP but don't have access to the RTM then this may be of some interest.

As some of you may be aware one of the report types you can embed into a PerformancePoint dashboard is a Proclarity Analytics Server Page. This effectively provides the user with a Proclarity briefing book allowing some interactivity and as with almost all report types within PPS filters can be applied externally by PPS allowing say a PPS analytics grid, PAS page or reporting services report to all be displayed with the same parameters / dimension members being passed through.

The particular filter type that this post is concerned with are multi-selects. This is a typical dimension selection technique you've seen in plenty of tools before so I won't teach you to suck eggs on that one. Anyway, to cut a long story short after the dashboard has been published it's possible to preview the dashboard with your embedded PAS based report and filters.

Select a single member from your filter and apply and the report return pretty quickly, not lightening fast but remember this a CTP and debugging is switched on etc. so that has to be taken into account. Select 2 members from the filter and when the report is say performing a non empty on a dimension with members numbering in the thousands, it takes a little longer than it should, in fact uncomfortably longer.

Profile the poor performing MDX and here's what you get....

WITH

SET [<##<SET!smchugh!{E3EF8F62-CD2B-422D-881F-ABAFB4AA3E16}>##>]

AS '{[Product].[Product Categories].[Subcategory].&[31]}'

SET [<##<SET!smchugh!{844FB37C-1DA6-4536-B7A4-297650572576}>##>]

AS '{[Product].[Product Categories].[Subcategory].&[1]}'

MEMBER [Product].[Product Categories].[All Products].[ Aggregation]

AS ' AGGREGATE( EXISTING { [<##<SET!smchugh!{E3EF8F62-CD2B-422D-881F-ABAFB4AA3E16}>##>],

[<##<SET!smchugh!{844FB37C-1DA6-4536-B7A4-297650572576}>##>] }) ',

SOLVE_ORDER = 0

SELECT

NON EMPTY { [Measures].[Internet Tax Amount], [Measures].[Internet Order Quantity] }

PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,

NON EMPTY { [Customer].[Customer].[All Customers].CHILDREN}

PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS

FROM

[Adventure Works]

WHERE

( [Product].[Product Categories].[All Products].[ Aggregation] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Take particular notice of the new member called [ Aggregation] that uses the 2 sets. If they are stripped out then and the MDX run in a much more sensible manner such as ....

WITH

MEMBER [Product].[Product Categories].[All Products].[ Aggregation]

AS ' AGGREGATE( EXISTING {

[Product].[Product Categories].[Subcategory].&[31],

[Product].[Product Categories].[Subcategory].&[1]}) ',

SOLVE_ORDER = 0

SELECT

NON EMPTY { [Measures].[Internet Tax Amount], [Measures].[Internet Order Quantity] }

PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,

NON EMPTY { [Customer].[Customer].[All Customers].CHILDREN}

PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS

FROM

[Adventure Works]

WHERE

( [Product].[Product Categories].[All Products].[ Aggregation] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

...same data returned but with a massive time difference. It's worth actually running both these queries on [Adventure Works] as it's a good indication as to what a difference little changes in your MDX can make.

Suffice to say the second query is now how it's structured in PerformancePoint Server 2007 RTM so it's well worth getting this installed as soon as possible as not only will queries return faster but some nasty timeouts on your dashboard will be avoided.

Technorati tags: PerformancePoint, PPS, Monitoring, MDX

PPS CTP4 VPC DOA

2007-10-10T12:48:00.001+01:00

If there weren't enough acronyms in the world already....

A little tip from a colleague who has been working closely with Performance Point Server over last couple of months and had the misfortune to use the Virtual PC from Microsoft to demo the toolset.

It performs like a dog. I can confirm this as I'm watching it "not respond" for no reason after an "indeterminable" amount of time. After somebody complained about this they were told that you have to defrag the hard drive within the virtual PC 10 times. I'm currently on defrag number 5 of 10.....

8 out of 10.....

10 out of 10.....

......

Well after a little play around I have to admit to some non-scientifically measured performance benefits that have been noted, oh, until it crashed on me with the dashboard designer deciding that the task of saving needed 100% CPU and to deny any interaction whatsoever.

Unfortunately what I cannot answer with any clarity is....

9 clicks will make it perform worse.
11 clicks will make it perform better, or worse.
the dashboard designer needs a quad core xenon to save anything......

Technorati tags: PPS, PerformancePoint, Budgeting and Planning

Reporting Services and Virtual Directories, a Warning

2007-10-09T11:04:00.001+01:00

Just a very quick post to make people aware of a little thing that happens with reporting services when you install it on a server and install it under the same virtual directory (e.g. you install it on a SharePoint server and don't allow SharePoint to control the reporting server, e.g. when you have Performance Point Server 2007 CTP4 and are using it with reporting services - mail me if it's confusing).

We found that Report Manager (http://servername:1234/reports) worked correctly but any access via the web service (http://servername:1234/reportserver) would return an error along the lines of...

Microsoft.ReportingServices.UI.WebControlConnection,
ReportingServicesWebUserInterface, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 does not implement
IReportServerConnection or could not be found

Spurious to say the least. The solution for us was to remove a couple of lines from the web.config file within the <appsettings> tag in the reporting services virtual directory...

This gets the web service running but guess what, reports via the report manager won't work anymore.

The problem, I am reliably informed on 'tinternet, is that web config's are inherited in IIS therefore the reporting services web service web config settings are inherited from the report manager web config which means that the setting you removed is needed for report manager but breaks the web service.

The solution, is to add the values back into the web config for the report manager but make sure that the web config for the web service virtual directory has the value...

Hey presto, both the report manager and the web service now function correctly.

Apologies if my descriptions of web things are a little on the vague side but I'm reading it as it happens.

Technorati tags: Reporting Services, IIS, web.config, web service

This Week I Have Been Mostly Listening To...

2007-10-09T10:05:00.001+01:00

Radio One Established 1967 - Various Artists

(and not just for the Girls Aloud cover of Teenage Dirtbag...)

Technorati tags: Music, Radio 1, Girls Aloud

SQLBits

2007-10-09T09:40:00.001+01:00

Waking up early on a Saturday morning is not one of my greatest traits but I managed to overcome that weakness to attend SQLBits at the Microsoft campus in Reading at the weekend.

There was a track on BI as well as one on SQL Server 2008 so plenty for everyone to be interested in. The best moments for me were seeing my friend and colleague Sutha present on managing early arriving facts and the new spatial data types in SQL Server 2008.

I need to go through the details of Keith Burns presentation again before I can post about it with any real conviction but the general consensus within the room was that "right here right now I don't know what I'm going to use this stuff for but I'm going to find a way to implement it". In other words how we developer types define "cool".

Technorati tags: SQL Server 2008, Katmai, Spatial, SQLBits

BIDS and TFS

2007-10-09T09:07:00.001+01:00

I'm a big fan of Team Foundation Server and how it can be used to better organise analysis, development and delivery. I've been especially impressed with a number of the features of Data Dude (or Visual Studio 2005 Team Edition for Database Professionals for the full mouth full), certainly since SR1, and how, with some work, deployments can be made a lot more efficient.

Let me give you an example. I have multiple environments, dev, UAT and production. All of them will have the same code base but different configuration settings for example we will have a table containing configuration settings or source file locations and these are all likely to change depending on what we are testing or deploying. Now Data Dude allows you to embed variables in your pre and post deployment scripts meaning that I don't have to change anything when it comes to things like allowing a accounts read access to data on dev that should not have read access on prod.

All pretty simple really and well worth installing for these kind of features (notice how I gloss over the cost....!)

Now the point "Visual Studio 2005 Team Edition for Business Intelligence Developers". has a nice ring to it but stick it into Google and at the time of writing nothing comes back. I can think of a number of scenarios where non intrusive deployment would be useful without have to rely on post deployment XMLA or scripts using rs.exe or even the wonderful package deployment wizard.

Some of the things I've named may not seem too reasonable but should you really have to go a separate utility every time you want to release code having multiple settings depending on the server or the environment or the fact it's a 64bit deployment or not. Analysis Services seems a prime candidate for this kind of behaviour as user access is defines in roles but I don't want to have to define them within role membership then remove them or go to the Analysis Services Deployment Wizard and define different configuration files when I've invested in a nice shiny Team Foundation Server and trained everyone up on it's use.

Anyway I would be most interested in hearing any thoughts on the above subject as this kind of thing is taking up a lot of time and more and more clients are jumping onto the TFS / agile bandwagon one even demanding to know why this can be done with the databases and not the cubes. You see the point..!

Technorati tags: TFS, release management, BIDS, data dude

SAP have BO....!

2007-10-09T08:31:00.001+01:00

I couldn't resist the almost register like title for the post....

It appears after many many months of hearsay and conjecture someone is finally going to stump up the cash and buy Business Objects. There's been a lot of talk around this area especially with Hyperion being absorbed by Oracle and further rumors about the future of Cognos.

Microsoft seem to be content with buying the smaller players and partnering with the similar smaller vendors to compliment their own technologies and now they've gone their own route for budgeting and planning it seems very unlikely they will do anything else but carry on that trend.

It will be interesting to see what effect this has, if any, on the close ties that SAP and Microsoft have had recently. We shall see.

Technorati tags: SAP, Microsoft, Business Objects, Business Intelligence, Vendor

This Week I Have Mostly Been Listening To...

2007-09-26T09:07:00.001+01:00

Air Traffic - Fractured Life

Technorati tags: Air Traffic, Music, 2007

Developing with MDM

2007-09-26T08:50:00.001+01:00

Technorati tags: SSIS 2008, MDM, Bulldog, Stratature

There has been some recent announcements on Microsoft's acquisition of Stratature that have begun to solidify their approach and roadmap for Master Data Management. The MDM tools, as most people had previously guessed, will be very much aligned with the Office toolset and made part of the SharePoint. Personally I think the key thing about this is how this data will be accessed in my day to day job pulling data from here and putting there for user consumption.

Jamie Thompson has a video on YouTube (embedded below) detailing how to consume web services in SSIS 2008. Couple this with Bulldog's (Microsoft's name for absorbing Stratature's EDM+ product) ability to expose the master data and meta data as web services and all kinds of pre-built SOA components become possible.

YouTube - Consuming Web Services in SSIS 2008v2

Although possible with the existing set of tools in SSIS, I would be surprised if a data source component didn't appear at some point for SSIS 2008 that was specifically designed to retrieve master data from Bulldog. After the constant battles I've had getting master data out of organisations due to the cost of managing and maintaining it, having a tool that looks after the work flow and provides simple generic interfaces to the data will be very beneficial indeed.

Bring on the CTP next year....

The Last Month or so I Have Been Mostly Listening To...

2007-09-04T08:43:00.001+01:00

Interpol - Our Love To Admire

The Young Knives - The Young Knives Are Dead... and Some

The Rumble Strips - Girls and Weather

Kings of Leon - Because of the Times

Technorati tags: Music

Extended Absence.....

2007-09-04T08:33:00.001+01:00

After what appears to have been an eternity of non posting (which coincidently seems to occur every August, holidays, V festival etc.). I shall attempt to make up for lost time.

So lets ease into this gently with music posts first....

Technorati tags: phoenix, flames, return, triumphant

Sorry it's been a bit quiet...

2007-06-29T09:23:00.000+01:00

The lack of posts recently is down to my laptop being in the repair shop.....

No fault of mine just general wear and tear. As soon as I get it back then I'll have some stuff on SQL Server 2008, more MDM bits and some interesting things I've been up to with SharePoint 2007, not forgetting the things that i've been listening to. There's quite a lot of those as we enter festival season and yes I will be making the traditional trip to the V Festival in Chemlsford if anything just to see McFly...! (If you know me you know the truth).

Laters

Steve

Just when I was about to carry on talking about MDM...

2007-06-08T12:39:00.001+01:00

...Microsoft go and announce this.

http://blogs.msdn.com/knight_reign/archive/2007/06/08/microsoft-completes-stratature-acquisition.aspx

Looks good for an end to end Microsoft architecture but it will be interesting to see what happens when it's working outside of its comfort zone.....

Technorati tags: Microsoft, MDM, Metadata

What do they say about boys and their toys....

2007-06-05T08:56:00.001+01:00

Well now I have my new version of writer I'm

posting like a demon....

Anyway let's get up to speed with some SQL Server 2008 web casts coming in July...

SQL Server 2008 LiveMeeting Schedule

Analysis Services - Dimension Design 06/12/07 11:00 am PDT

Change Data Capture 06/13/07 11:00 am PDT

Star Join Query Optimizations 06/19/07 11:00 am PDT

Table Valued Parameters 06/22/07 11:00 am PDT

Declarative Management Framework 06/26/07 11:00 am PDT

MERGESQL Statement 06/29/07 11:00 am PDT

Technorati tags: SQL Server, Katmai, vNext

Windows Live Write Beta 2

2007-06-05T08:49:00.001+01:00

I've blogged about it in the past and it's still a great little tool. You can get it from here.....

http://writer.live.com/

Technorati tags: Blogging, Live.com, writer

SQL Server 2008...

2007-06-05T08:21:00.001+01:00

The June CTP is out....

Get it while it's hot....!

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

Technorati tags: SQL Server 2008, SQL Server, Katmai, vNext

This Week I am Mostly Listening To...

2007-05-23T15:39:00.001+01:00

The Rakes - Ten New Messages

The Evil That is Master / Meta Data - Part 2 (or the one where Steve talks about Ireland..!)

2007-05-23T15:35:00.001+01:00

In the last post on this subject I talked about some of the key attributes of master data and meta data management and how it is intrinsically synched with what I call the Information or Data lifecycle. Now I would like to elaborate on this and identify some of the advantages that investing in this kind of strategy can provide.

Embarking on corporate wide gathering of all things data requires investment at all levels. Time, effort, money and most importantly commitment are essential. But ensuring a business receives any kind of ROI (return of investment which in the early part of my career I thought meant Republic of Ireland, hence the title) before that kind of commitment takes place can prove daunting and a little difficult to say the least. Let's forget the cons for a moment and look at some examples of the key advantages a data and information strategy can provide.

Everyone on the same page.
Information means the same thing throughout the business.
Reduced cost of new reporting and / or analytical requirements.

This doesn't look like a very extensive list and to be honest if someone presented me with a pitch like this I would be showing them the exit pretty rapidly but when you further examine the nature of each of these bullets you see that they are rooted incredibly deeply in practically all business processes and systems in place within an organisation. Key to the whole concept is that meta data and master data are not only for use within reporting systems. It's just that any projects that tend to drive this kind of requirement are also implementing some kind of reporting mechanism.

Lets take a step back and look at a simplified implementation of a number of reports. First we gather the requirements for the reports which would be based upon an existing set of data, possibly sheets of paper possibly a database storing transactions. Then the nasty business of performing analysis on said data, conforming it into your existing dimensional structure or creating new dimensions from scratch. On defining the model from which you will base your reporting you can finally start building reports.

So how could we improve this process and reduce the time taken to turn around a reporting requirement. First having some degree of knowledge of the system prior to a reporting requirement coming along would be advantageous but that's not the way the world works. Looking at a single report as a deliverable we would need to understand where the constituent data is sourced from. The report, for example, has customers, geographical breakdown, product type, number of orders and order value. Very simple but already pulling data perhaps from CRM, product catalogue and ordering systems.

When building a picture of the data held within the company it is very important that ownership is established. Who owns the customer data? Who is responsible for maintaining the product catalogue? These are the people that own these data elements within the organisation and are therefore ensuring the quality of not only the data in their own systems but also the reporting that is based on this data.

The point to this is that data quality needs to come from the top down. BI projects are generally just the the catalyst for this but should also be used as means of improvement in the source systems. Too often has data cleansing been hooked on to the back of a BI project and weighed it down with responsibility that should lie elsewhere.

Ok enough of this business type talk of responsibility and stuff. Next time I'm going to go into what master data and meta data are actually made of.

Unit Testing SSIS

2007-05-23T15:20:00.001+01:00

SSIS packages are almost like a mini application in their own right. On most occasions there is one or more input items of data that may consist of either a single variable value or an entire data set. This will follow a transformation or validation process before a final output that could again be in a number of different formats.

Unit testing these packages should involve minimal change to the structure or behaviour of the package itself. The risk of influencing the code behaviour through the testing process is as great as an incorrect deployment or missing a particular testing scenario.

The most important factor in the process of testing a package is to understand how it will react in controlled circumstances and be in a position to test the success of the anticipated result. Testing this using the natural output of the package, for the reasons discussed previously, will provide the most robust results.

Due to some of the current debug limitations of SSIS and taking into account the need to keep the package structure and design static; it is only really possible to effectively test the control flow of a package whilst remaining ‘hands off’.

Let's look at a simple package example;

The same type of output would be taken from the other standard control flow tasks. The execute SQL task would have an initial state, a completion state and a resulting success state based on the other outputs. The initial state of n rows in the destination table before and 0 rows after the step has executed. This is measured by examining the row count in the table before and after execution and comparing the value with the expected result. For the archive file system task all states would be measured using a different mechanism and so on and so forth.

Essentially this means that whatever the task there may be numerous methods of gathering the data necessary to confirm whether the test process has been successful. Simplifying the process of measuring the results of a test would make applying a standard testing mechanism far easier to implement.

Currently packages provide the ability to perform some manual logging as I've posted about in past. This can be used to establish whether tasks have completed successfully or not but where a measurement is needed to confirm this, this type of logging is lacking. For example, truncating a table will not provide you with row count confirming the number of rows affected or the subsequent amount of rows left in the table whilst a delete statement would. It would not be wise to change all truncates to deletes to allow this information to bubble up to the logging process and therefore allow capture of the state of the task before and after execution.

There are a number of different ways of trying to perform strict, robust SSIS unit testing of which I've generalised them into 3 options.

First is to create a more robust testing methodology that has processes particularly for unit testing SSIS packages based on the control flow. The success criteria should be based on use cases defined at the package design stage. Testing each of the steps within the control flow of the package with documented pre-execution criteria, expected output and the subsequent result.

This does not lend itself to any kind of automated testing approach and would involve stepping through the package control flow and logging the results.

The second option allows for the automation of tests and the capture of results but would require another application to control the execution of the process and establish pre-execute criteria in addition to measuring the post-execute result.

Using the unique identifier for each of the control flow tasks, during the pre execute and post execute process of each step a lookup would be made to either a database or configuration file containing the method call that would initiate the processes necessary to prepare the execution and subsequently validate and measure the success on post execute.

A change like this would mean integrating a procedure call, perhaps using the CLR, to execute such tasks based on an internal variable indicating that unit test, or debug mode was enabled within the package. Whilst providing a number of advantages in automated testing and capture of the test results there would be a great deal of work required in the preparation of the test. This would all have to be completed in addition to that suggested in the first option as the pseudo code necessary to design each of the test criteria would be based on the use cases defined at the package design stage.

The final option would be to remove the more advanced mechanism from option 2. The pre analysis and use case definition would still be required but in this option additional test scripts would be placed in the pre and post execution events of the package. This would mean embedding code into the package that would only be used during unit testing that could possibly be switched off using a similar variable to that in the second option.

Thought it would be possible to automate a great deal of the testing for the package it would mean changing the structure or behaviour of the package directly and increase the danger of introducing problems with script tasks that have previously been experienced on 32bit to 64bit transitions.

So there you go. There are ways of doing SSIS unit testing. There are even ways of automating the process but it's not a quick win. It doesn't remove the need to establish good formal testing strategies and it certainly isn't going to appear out of a box, well not soon anyway.

This Week I am Mostly Listening To...

2007-05-16T09:38:00.001+01:00

The Pigeon Detectives - Wait For Me

This Week I am Mostly Listening To...

2007-05-01T15:20:00.001+01:00

The Maccabees - Coulour It In

The Evil That is Master / Meta Data - Part 1 (or the one where Steve talks about socks..!)

2007-04-03T07:58:00.001+01:00

Master data and metadata. A subject close to my heart due to it's significant importance in what I call the data lifecycle. Data? Lifecycle? What on earth is he talking about now, I just wanted to get Oracle talking to SSIS? Well let's go a little off subject here and use a bit of a euphemism.

Take something simple that I think we all learnt at school, the water cycle. This is the continuous movement of water as it shifts location and state from ocean to atmospheric to ground water. Now I liken this to the way data moves through an entity whether it be an organisation or group of systems.

A good example of this is a common scenario in financial reporting. An accountant (that's the cloud up there) will read their profit and loss report for a particular department and use this as to calculate the following years budget or forecast. These estimates will then be entered into the budgeting and planing system (that would be the mountains, more likely though it's Excel :). The budget and forecast are imported into the data warehouse where the profit and loss report (the ocean perhaps?) is generated which is read by another accountant looking at the companies performance, who........ ad infinitum.

A very typical example but it demonstrates the fact that the behavior of data within an organisation is very organic and in a constant state of flux. Just because the original piece of data is sitting in a table somewhere it doesn't mean it hasn't evolved into a different beast elsewhere with different properties and meanings. Simple as it sounds, this makes life a little complicated when you add influencing factors such as SOX (Sarbanes Oxley) compliance that requires the demonstrability of internal controls. In BI speak this could be someone changing an attribute of a dimension member and proving who did it, when and why. One tiny change which to a developer may be minor but to a CEO moves them from the red to the black, exactly the kind of thing SOX tries to stop.

Now all this talk of oceans, cycles and socks is all very good but doesn't bring us any closer to knowing what the hell to do about managing master and meta data. Ok, lets break down some of things I've mentioned in to some key bullets.

Dimension Management
Compliance
Process Flow

This list identifies some of the major reasons for having and requirements of any meta data and master data management mechanism.

In the next part I'll cover the these elements in more detail and how they can contribute to a more streamlined data strategy.

This Week I am Mostly Listening To....

2007-04-02T08:22:00.001+01:00

Arcade Fire - Neon Bible

If an Alien Dropped Down, Right Here, Right Now....

2007-04-02T08:19:00.001+01:00

I have just had the displeasure / pleasure (please delete as necessary) to have spent a little time in Barcelona witnessing the English football teams victory over Andorra.

According to Wikipedia Andorra is a co-principality with the President of France and the Bishop of Urgell, Spain as co-princes.

According to the majority of the traveling supporter base of the England team, Andorran's are either taxi drivers, estate agents or butchers with the odd police man thrown in for good measure.

So where does the alien in the title come in? Well, the alien that dropped into the Estadi Olimpic on Wednesday night would have probably thought;

The human population is 98% male
The human population is 98% hairless
The human population is unable to construct sentences coherently
The human population are masochists.

For the love of God, lets hope the aliens go to a Brazil game instead...!

This Week I am Mostly Listening To...

2007-03-16T15:39:00.001Z

The View - Hats Off to the Buskers

rsInternalError - Now This One's a Doozy...!

2007-03-16T15:32:00.001Z

If you're running Reporting Services and keep getting random rsInternalErrors that may or may not return errors in the logs concerning things like "There is no data for the field at position n", before you start to rip apart your reports looking for bugs check the make of the processors on your server.

There is a little problem-ette that manifests itself as these kind of errors when you have AMD processors in your machine. We have 2 servers on a clients site that have the 64bit Opteron chips in that were both demonstrating these errors with different reports, data sources and pretty much anything you can imagine. After a little hunt around the information hyper global mega net we found some newsgroup postings points to this Microsoft knowledge base article.

http://support.microsoft.com/default.aspx/kb/895980/en-us

Adding the /usepmtimer switch in the boot.ini file appears, for now, to have cured the problem that was causing very simple reports to fail 20-30% of the time on an under stressed production server.

After my recent problems with SSIS 64bit this was the last thing I needed but suffice to say my cup of joy was overflowing as a dev server that pretty much gave me an rsInternalError on command has worked flawlessly for several hours. If anything changes I'll be sure to mention it.

This Week I am Mostly Listening To...

2007-02-15T08:48:00.001Z

Bloc Party - A Weekend in the City

Other Blogger's

2007-02-06T08:06:00.001Z

A colleague of mine has started blogging recently mostly in and around the SSIS area and I can categorically say he know what he's talking about.

Check it out here....... Colin's blog

This Week I am Mostly Listening To...

2007-02-05T14:19:00.001Z

Cold War Kids - Robbers & Cowards

The Adventures of SSIS and Oracle in 64bit Land..!

2007-02-05T14:16:00.001Z

There's plenty of information out there spread out amongst the web concerning migration of packages to a 64b bit platform. I thought in the interests of fellow professionals out there I should share some of my experiences.

Firstly installation. Look at the C: drive on a 64bit Windows Server 2003 installation and you'll notice 2 "Program Files" folders one of which is suffixed (x86). Any 64bit applications are installed in the Program Files folder and 32bit applications are installed in the Program Files (x86) folder. So far so good and pretty simple but this is where you will encounter the first of several problems getting Oracle connectivity to behave on a 64 bit server.

Before I go into any detail here's a list of things that you need to know when you have installed 64bit SQL Server on an x64 server.

Visual Studio (BI Dev Studio) is a 32bit application.
SQL Server Management Studio is a 32bit application.
dtexecui.exe is a 32bit application.
dtexec.exe has both 32bit and 64bit versions.
There are x64 and x86 versions of the Oracle provider available.
SQLAgent is a 64bit process.

OK with all this information let's begin. Firstly if you develop packages on a 32bit platform, which is likely, and you are connecting to Oracle, use the Oracle OLEDB Provider, NOT the Microsoft provider for Oracle. For starters there is no 64bit version of this and secondly MS has no plans to create and support one.

Now you're using the Oracle OLEDB Provider we should be able to avoid a few problems later on although I would like to make note of one problem I had but couldn't find a resolution for. In a project I'm working on we needed to execute an Oracle stored procedure before we could access the data. When we ran the procedure using the MS provider (MSDAORA) everything was fine but as soon as we moved onto the Oracle provider (Oracle.OLEDB.1) the package would just hang as soon it attempted to access Oracle. This magically rectified itself when we moved servers. Just letting you know so beware.

Launching the 64bit Oracle universal installer will provide you with four options. Instant client, runtime, custom or administrator. Now I am not any kind of Oracle expert so I didn't go near the "custom" option and stuck with the other three default selections but I could only get the Administrator install to provide me with a working registered Oracle provider. Now this may work for someone else but on this server, only Administrator worked.

With the Oracle provider installed and my connection string configuration using the Oracle.OLEDB.1 provider there's a series of interesting little test you can run. First create a package that just sucks some data out of an Oracle table and places it in to a row count data flow task. We don't want to do anything, just get the Oracle end of this process working. Execute the package in the following ways;

Double click on the package and execute.
Execute it through the command line interface (using the dtexec.exe in C:\Program Files (x86)\Microsoft SQL Server\..\dtexec.exe".
Execute it through the command line interface (using the dtexec.exe in C:\Program Files\Microsoft SQL Server\..\dtexec.exe".
Execute it as a SQL Server job.

The interesting thing is that the first two methods of executing the package will fail and the second two will be successful. Why? Well this is because a 32bit process cannot see the 64bit drivers. The first method initiates dtexecui.exe which is a 32bit process the same as the dtexec.exe in the (x86) program files folder. Running the process using dtexec.exe in the normal program files directory or as a SQL Server job executes the package as a 64bit process which quite happily sees the 64bit Oracle drivers.

This can prove to be a problem if you want to debug your package as Visual Studio is 32bit and can't see the Oracle drivers meaning you'll get messages about things not being registered even though you know you did. Fortunately you can install the 32bit and 64bit Oracle drivers on the same machine meaning each process type then has the ability to see the Oracle drivers. Run the same four methods of executing our test package and the same thing will happen. The first two will succeed and the second two will fail.

This issue is down to a problem with the Oracle provider. The Oracle provider appears to have a particular dislike of being used by processes that have any mention of a bracket, Program Files (x86) being one of them. So even though you have correctly installed the 32bit Oracle providers on the server, you can't use them. The solution, copy the Microsoft SQL Server folder and Visual Studio folder into a new folder on your C: drive called ProgramFilesx86 and all of a sudden you development environment can see the Oracle drivers and work. You need to use the dtexecui.exe in there as well if you want to interactively execute packages.

Remember though this is only an issue if you need to perform any debug of packages that use Oracle providers on a 64bit server. Quite a specific problem but quite common none the less.

Now you've got over that problem solved(ish) you can start to build beautiful complex packages utilising every control and data flow task available to you. But then you get strange errors. Ones that are centered around script tasks especially BUT not always.

Personally I've had a couple of issues moving packages onto 64bit servers, excluding the Oracle problems mentioned above. First script tasks sometimes need to be recompiled on the 64bit hardware. This is kind of hit and miss but appears to be common in scenarios where you have a template package and you copy and change the definitions in data flows. The recommendation is open up each package and script on 64bit to be on the safe side although I have had a number of occasions where we've had to delete the script task and recreate it get the same code working correctly. Very odd.

This issue doesn't seem to be isolated to script tasks though. Execute package tasks seem to sometimes have similar behavior where reopening the package on the 64bit hardware corrects it.

Overall the benefits of SSIS and 64bit are obvious but be careful during development and if you can't develop in 64bit then try to at least have a server that is 64bit for testing this kind of thing and if you do get any kind of strange issues cropping up, try to bear in mind the apparent inconsistencies in the two platforms.

Update:

Corrected some spelling mistakes and disgustingly bad grammar..!

Technorati tags: SSIS, Oracle, 64bit, Provider, OLEDB

Making The Client Happy - The Real Reason For Yesterdays Post That Rapidly Turned Into Some Kind Of Rant...!

2006-12-11T18:18:00.001Z

If there's one thing that users hate it's being kept in the dark. So communication is essential and to be honest BI is all about communication. Articulating a series of 0's and 1's via OLAP or reporting something tangible that an end user can get real benefit from is what it's all about.

So we have a process that may rely on another process outside of the control of the BI infrastructure. This is a typical scenario and due to the 24 hour worldwide mannerisms of a lot of the BI implementations I've worked with, that process may be smack bang in the middle of someone's working day.

Scenario established, what are the problems we have here. Well first there's the problem of processing whilst people are working but with the performance tuning techniques available to us in the Microsoft BI space that shouldn't have so much of an impact on your user base. Really you just want to delivery new data to the user as quick as possible.

The real problem here is a user not knowing when their new data is available. Does that balance include yesterdays transactions yet or not? A typical question that may get asked in financial departments at month end something that would be asked a lot more regularly than you would like.

So communication is a problem, or lack of anyway (see the way I did that, started off talking about it and brought it round again to the subject, you're impressed, I can tell..!).

What can be done about it? There are a number of clever little things we should be able to do as part of our BI profession. Use our skill set and let the consumers of our data know the data is there to be consumed. First method, deploy a report at the end of processing to the user or send an information e-mail. Database mail in SQL Server 2005 makes this a pretty simple task these days. This could be a standard link telling the user base to go to a reporting services link and open a report telling them what has happened in the data processing. Problem with this though is it is reliant on a patient user base who will happily stare at the mail box until that little shining light of hope gets sent from "SQLSVR978BSMNT" (I do miss the days when there were fewer servers and they had names based on characters from The Simpsons or something like that.).

OK so your client / user isn't patient or it's 4:30 on the Friday before Christmas (topical huh..!), either works, and you don't want to have to go answer 20 calls from finance departments around the world demanding to know where the data is. Let's look at what they need to know. Take a typical end to end process, it may go something like this;

Extract data from one or more source systems.
Process said new data into the warehouse / mart / etc.
Process data into cube.
Distribute reports (or warm cache, it's useful have this catch all at the end)

Now this is high level generalisation of what may be going on but being able to let the uses know this much gives them a massive increase in the perception of the system. Even to just know that the sources system data has not ben extracted empowers the user, if there are aware of where the data is coming from, and why shouldn't they be, to get on the phone and ask New York why their figures haven't been entered yet.

I've already talked about making this happen via sending reports out but lets face it, this isn't the ideal way of doing this when there are numerous users and numerous steps within the process to communicate. So we make the user pro-active. Go to the portal or website and find out what the hold up is or where the problem lies. All we're doing is taken something that has been happening in customer service departments for years and applying it to the BI systems that we put in place and to be honest with our experience we really should know how to provide someone with beneficial information in a number of formats.

So what is my point in all this? There has been an idea bubbling around my head for a few months now around this subject and visualising end to end processes for clients, mostly based on SSIS and the simple logging model I've posted about previously. The idea was to use Visio to expose data in a diagrammatic format to the users showing them something similar to the SSIS control flow, you know greens, reds, yellows. This became all the more interesting after the recent UK BI user group at Reading where David Parker was presenting discussing visualising data in Visio, essentially the same topic.

There, that's the idea, now I'm going to put it into practice. Stay tuned and if anyone else has had any experience in doing what I've said then I would be really interested in hearing.

This Week I am Mostly Listening To...

2006-12-11T07:25:00.000Z

Raconteurs - Broken Boy Soldiers

Making The Client Happy...

2006-11-30T19:13:00.001Z

Being a consultant involves meeting a lot of different people in a lot of different situations. Most of the people I meet are by an large pretty nice but not all. I shall stop there for fear of incriminating myself :).

One thing is consistent throughout everything I do in my job though and that is that I try to deliver the best solution possible within the constraints placed upon me, whether that be ambitious requirements, time, money, equipment, coffee, etc, etc. I do take a certain pride in what I do and the best thing to come out of it at the end is the successful delivery of a project with the users grateful for the spanking new BI implementation my colleagues and myself have put in place. And hey, if you get to play around with a few extra toys then that's a bonus...

This may counter the myth that as a consultant my role is to bleed the client dry and ensure that the people that command my time have enough cash to bathe in asps milk, ordering people to remove all of the blue M&Ms from large jars made from empty Playstation 3 carcasses whilst drinking fine wine made from the grapes of pandas! (In my head I know what I mean).

I do what I do because I'm interested in it and it pays fairly well at the moment. Ideally I would be a tester at a major games developer, but I can't see that happening until the mortgage is paid off, until then I'll just keeping BI-ing my heart out in the interest of the client.

There was a technical reason for this post but I think my rant went on long enough so I'll perhaps share that tomorrow when I've had a chance to investigate further..

Evening After The Night Before....

2006-11-30T18:53:00.001Z

Last night I had the pleasure of attending the 2nd UK BI User Group and happened to ask Chris Webb when the next one might be and he answered probably about 6 months. Now go onto one of the IT job boards and type in BI, SSIS, OLAP or any of the many acronyms that relate to this kind of thing and you'll be overloaded with agencies and other organisations offering large sums of money and incentives to come and join them.

What's my point?

Well with that kind of level of interest in BI over the last 18 months and that interest not looking like it will wane in the foreseeable future I think there are a couple of things that should happen....

Firstly more people should attend, now that may be location as TVP (Microsoft UK, Reading) isn't the easiest of places to get to but MS kindly offer their place so who's going to refuse it.

Secondly more regular, things are moving pretty fast, people are coming out with new tips tricks and ways of adapting and implementing the MS BI platform so surely there are plenty more things that can be discussed, one look at a PASS event shows there a hell of a lot of ideas out there.....

I should do my bit as well I suppose, I could make some Turkey sandwiches to take, there might be some left overs in the next month or so..!

Failure - Data Source Does not Exist..! - Update

2006-10-17T14:47:00.001+01:00

I got asked to post the demo project I had mentioned in the original post a little while ago but unfortunately I had lost it. Luckily I was able to pull enough from memory to build it again and make it available for you pleasure. Whilst not embedded with premier coding standards it certainly provides some direction on how this kind of thing is achievable.

The project is here, any questions then mail me at the usual address.

Apologies for the delay :)

This Week I am Mostly Listening To...

2006-10-15T17:17:00.000+01:00

Morning Runner - Wilderness is Paradise Now

A Note From The Editor....

2006-10-05T15:13:00.001+01:00

Just a little clarification here. I am not a professional Dolphin trainer nor am I an amateur one either although I did once swim with a dolphin in the Florida Keys and found it to be a quite unfulfilling experience as the Dolphin was in a pool that I would consider to be the Dolphin equivalent of one of those Japanese capsule hotels.

The freelance dolphin trainer title comes from a time in my youth when life consisted of doing the things from this song below.

Thank you for your attention in this matter.

Editor out..!

Next Gen Blogging

2006-10-05T14:48:00.001+01:00

I really wish I could blog more but I do find that the things I want to blog about do tend take a large chunk of time so when things come along that make blogging a bit more streamlined, I' first in the queue. Firstly the blogging support in Word 2007 is pretty good but I found it a little restrictive on occasion so now I've switched to Windows Live Writer.

I'm not going to write anything about it but point you in the direction of this, where you can get it, and the video below. Direct from YouTube and embedded with a couple of clicks from Live Writer.

Enjoy....

Simple SSIS Logging - Part 3, Bad, Bad Rows..!

2006-10-04T17:47:00.001+01:00

It’s all well and good knowing that there has been a failure in your package which has lead to a bleary eyed support technician getting phoned at 3 in the morning but sometimes it’s not really necessary to get Barry (we’ll call him Barry for the purposes of this post) out of bed. Your package may be bullet proof and work perfectly but the nature of data is that it will change and can quite often come back and bite you on the backside.

Take for example early arriving facts. When you start integrating a number of systems it is possible for things to start happening that don’t conform to the world according to the system owner. Take the example of an in-house order system operating completely separately to the companies CRM system. During the analysis phase of the data warehousing project it was determined that CRM was the source of Customer ID therefore that’s where customers get created and that’s where customers get maintained. But a new customer phones up and makes an order before the customers details are completed in the CRM mechanism due to one thing or another. Your data warehouse is quite likely to be collecting that fact row before the relevant CRM data has been correctly populated and passed on to the customer dimension. So what happens in your SSIS package when looking up the Customer key for that dimension, well not a hell of a lot.

To be fair there are numerous techniques for dealing with early arriving facts. Some involve the population of the Customer ID and retrieval of the key so that when the correct dimension member arrives in the dimension table, its attributes are populated automatically. The problem is that things aren’t always as clear cut as that. You have managed to capture the early arriving fact data and correctly allocate it to members in the Customer dimension but when board reports run that specify a particular customer type the data is incorrect, the numbers don’t roll up and for 24 hours you could be running around like a headless chicken trying to figure out where the missing data has gone, oh that’s until the next day when the customer dimension member is correctly updated and things mysteriously fix themselves .

So where does logging fit into all of this? Well knowing that we have an early arriving fact would be a good idea. So we have to log the early arriving fact and let the data pass through to allow it into the fact table.

This looks quite simple but when you’re trying to handle a number of dimensions and any of these could have early arriving facts then you can get have a lot of data destinations. In addition to all this there is also the fact that each package could have a different error table at each dimension lookup making logging, which should really be centralised and in a defined structure, a little reactive and difficult to deal with. Dealing with this may not be as difficult as it seems though and for the first time I’m going to mention a customer SSIS task that has been written with this in mind. A colleague of mine has written a component for just this type of situation. Whilst I would have liked to have completed all 3 parts of this Simple SSIS Logging piece without referring to any external code, this really does make life a lot easier. The full source code is available on the site and I recommend its download.

The Xmilfy component (to give it its officially christened name) will turn a series of columns and convert them into a single XML column that has some obvious benefits. In the above example the data is then written to a single table in my SSIS logging database which has a data type of XML allowing the data to be viewed in a number of ways. Extra information can be added into the data flow that specifies where the error occurred, the number of rows etc, again creating detailed and useful reporting. Want to know the number of early arriving for a particular package or even a particular dimension, easily done using the data in the table. Finally a warning can be raised using script to inform Barry, remember him, that some data problems occurred but now it’s a warning and can be handed over to the department in charge of wielding the large stick that is used to stop sales people logging sales before the customer is correctly created. Furthermore the system can be automated to alert said department taking Barry completely out of the loop. But wait a second, Barry is now worried about losing his job but luckily he’s been learning SSIS in his spare time.

It’s ok people, Barry is fine…!

This Week I am Mostly Listening To...

2006-10-04T13:32:00.000+01:00

Boy Kill Boy - Civilian

SQL Server 2005 Metadata

2006-09-29T19:41:00.000+01:00

Hidden in the depths of Microsoft’s useful download division in Redmond is a handy pair of utilities you may have discovered that analyse dependencies within and across SSIS and Analysis Services. The first tool, Dependency Analyzer, examines the meta data contained within SSIS packages and Analysis Services objects collating their inter-dependencies writing the results to a pre-defined database structure.

The second tool is the Dependency Viewer and provides a graphical interface displaying relationships between the analysed objects .

The amount of data is considerable and has some quite powerful possibilities. There has been a distinct gap in the MS BI product set, dependency and data analysis being two key areas that their competitors eulogise in large amounts about. This appears to be the beginning of some gap plugging before the release of Katmai (SQL Server 2007). In the meantime all the source code, examples and even some reporting interfaces are provided in the download.

I’m quite looking forward to plugging this tool into some of my end to end 2005 projects and looking at the results. Once I've got some good examples of how everything integrates together on this I'll post ome of the results.

Dynamic Partition Creation and Processing – Part 2

2006-09-29T18:52:00.000+01:00

The first part of this post discussed how to identify whether a partition existed and create a partition dynamically. Although this post is a little on the suck eggs side I thought it would be better to write it for completeness. Processing a package can be performed using the exact same techniques that are used for dynamic partition creation thus the suck eggs comment.

First let’s look at the XMLA query for processing a partition similar to the one created in the previous post.

<DatabaseID>Adventure Works DW</DatabaseID>

<CubeID>Adventure Works DW</CubeID>

<MeasureGroupID>Measure_Group_Name</MeasureGroupID>

<PartitionID>200609</PartitionID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>

</Batch>

Note there’s no mention of the partition name anywhere. The partition is processed using its ID and that is all we are really interested in. Replacing just that value in the Source expression in an SSIS Analysis Services Execute DDL Task will point us to the correct partition for processing.

Of course if the entire cube structure is built on the same partition naming strategy then creating s single script to process all of this periods partitions is relatively easy. The added bonus of being able to process partitions in parallel can prove a huge advantage in reducing the time taken to update the cube on whatever schedule you have.

These examples are based on cubes structured with monthly or period based partitions. Partition strategy is an important subject for both cube querying and processing performance. I’ll leave that to another post but I just wanted to state that employing a rules mechanism that say merged period based partitions into a single year partition whilst creating new partitions on the fly does not need to be a massively complex process. The methods I’ve discussed in the two posts are probably some of the simpler ways of implementing this.

This Week I am Mostly Listening To...

2006-09-26T17:09:00.000+01:00

The Young Knives - Voices of Animals and Men

Dynamic Partition Creation and Processing – Part 1

2006-09-26T15:55:00.000+01:00

Processing cubes in SSIS is pretty simple if all you want to do is pick a partition and process it but if that partition is time based, which is quite likely then the partition you process each day, week or month will change. So what are your options?

First you can change your package each month, or use a variable with the partition name. Probably not the best of options as someone is going to have to physically make the change each month or come up with some clever method of changing your variable using something like VBA, Excel, etc. Inefficient if you ask me and defeating the object. Couple this with creating a years’ worth or partitions or creating them every time a new one is required and the whole task becomes quite laborious.

So what else can be done? My personal favourite is to use dynamic XMLA but this has a few of its own pitfalls you have to be aware of. First let’s look at the XMLA script for creating a partition and some of the key tags you have to be aware of.

<DatabaseID>Adventure Works DW</DatabaseID>

<CubeID>Adventure Works DW</CubeID>

<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

</ParentObject>

<ID>Internet_Sales_2001</ID>

<Name>Internet_Sales_2001</Name>

<Name>AggregationPercent</Name>

</Annotation>

</Annotations>

<DataSourceID>Adventure Works DW</DataSourceID>

<QueryDefinition>SELECT * FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= '184'</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<Slice>[Date].[Calendar].[July 2006]</Slice>

<AggregationStorage>MolapOnly</AggregationStorage>

<NotificationTechnique>Server</NotificationTechnique>

</Source>

</ProactiveCaching>

<AggregationDesignID>AggregationDesign</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Create>

Above is the create script of one of the partitions from the Adventure Works cube. There are some obvious elements in there but the ones that you need to be aware of when building your script dynamically are as follows;

ID – The most important one. Even though this is the same as the name of the partition there’s a couple of things to remember. Firstly they don’t have to be the same, secondly by default it’s named after the partition name. Why is this important? When you first create a measure group in your Analysis Services project (in Visual Studio) the default partition is named after the measure group name and subsequently the ID. The ID is the unique identifier of a partition within a measure group so if your partitions are named MeasureGroup1_200608, MeasureGroup1_200609 you may find the ID’s are MeasureGroup and MeasureGroup_200609 and processing a partition in XMLA is based on the partition ID.

The partition ID is something you can’t change in an Analysis Services project, well I haven’t found a way yet so in my little world it can’t be changed..! The first thing I would do is create a duplicate partition in the cube design and name it what I would like the ID to be. Once created, you can change the name to whatever you like, the ID won’t be changed.

The Name element becomes pretty arbitrary but you still need to ensure that every partition you create has a different name, again if you want it to be the same as the ID then name it accordingly.

The final two are the QueryDefinition and Slice. This is where you may have to step back and think about the overall design of cubes and their dimension structures. For the purpose of this post I’m talking about partitions being sliced by date, this is because in my experience that’s how most cubes will get sliced. Where it’s suitable I would try to make the key of the date dimension a meaningful value i.e. 20060926 = 26^th September 2006. Before we go into multiple discussions about why this is a good or bad idea stop, it’s something I do, I know its pros and cons but I’ve found it to be beneficial in a hell of a lot of occasions, this example being one of them.

My QueryDefinition value will be the query that forms the dataset my partition is to be processed from. Generally you’ll see something with a where clause like “WHERE DateKey BETWEEN 20060900 AND 2006999” if the partition is query bound. If the partition is table bound, more than likely you’re using views to distinguish the different data sets. Finally the partition slice. Even though this is documented as being for information purposes only there have been some other well documented grey areas when not populating it. My recommendation is to put a value in anyway, even if it is for information purposes. The slice value for hmmmm let’s say the September 2006 partition in my cube would look something like this “<Slice>[Date].[Calendar].[September 2006]</Slice>“
or ideally “<Slice>[Date].[Calendar].&[200609]</Slice>“. The key for the slice makes it clear how you need to be considering this kind of thing when designing your dimensions at the data mart as well as OLAP stage as we’re making the month level of the dimension a critical value.

Looking at those key elements of the XMLA statement for creating a partition we get something like this;

<Name>Measure_Group_Name_200609</Name>

SELECT *

FROM FactTable

WHERE DateKey BETWEEN 20060900 AND 20060999

</QueryDefinition>

<Slice>[Date].[Calendar].&[200609]</Slice>

Each of the elements has the common value of “200609” which can be replaced allowing partitions to be created before processing, in this is example at month end. To actually execute this code within a SSIS package I populate the Source expression of an Analysis Services Execute DDL task. This can be either done via direct input or variable (selected by the SourceType property). The expression takes the variable, or direct input XMLA, and evaluates it based on any parameters you need to input. The parameters in this case would be replacing any reference with 200609 to correctly create the new partition.

Couple of things to remember when creating expression of XMLA like this, the script will have to be in double quotes and any double quotes within the script will have to be replaced with ‘\”’. That’s forward slash and double quote.

Finally, before you run the XMLA it would really be good practice to know if the partition already exists before you attempt to create it. AMO (Analysis Management Objects) is the answer to that. The script below, kindly mixed, matched and cribbed from the those lovely Project REAL chaps, will populate a SSIS boolean variable with the answer to whether the partition exists or not. Don’t forget to add AMO as a reference in the SSIS script task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public
Class ScriptMain

Public
Sub Main()

Dim WellIsItThere As
Boolean

Dim ASConnection As ConnectionManager

Dim ASServer As
String

Dim ASDatabase As
String

Dim ASCube As
String

Dim ASMeasureGrp As
String

Dim ASPartition As
String

ASPartition = Now().ToString("yyyyMM") ' This will give us “200609”

ASConnection = Dts.Connections("AnalysisServer") ' This is the name of the connection manager that you're going to connect to in the package

ASServer = CStr(ASConnection.Properties("ServerName").GetValue(ASConnection))

ASDatabase = CStr(ASConnection.Properties("InitialCatalog").GetValue(ASConnection))

ASCube = "Adventure Works"
' How you populate this is really up to you

ASMeasureGrp = "Internet Sales"
' And again the population of this is up to you

WellIsItThere = IsPartitionThere(ASServer, ASDatabase, ASCube, ASMeasureGrp, ASPartition)

Dts.Variables("PartitionExists").Value = WellIsItThere

Dts.TaskResult = Dts.Results.Success

End
Sub

Public
Function IsPartitionThere(ByVal sServer As
String, _

ByVal sDatabase As
String, ByVal sCube As
String, _

ByVal sMeasureGroup As
String, _

ByVal sPartition As
String) As
Boolean

' By default, we will assume that it isn't there

' Only if we get all of the way to the end and everything is

' found, will we set it true

Dim bIsPartitionThere As
Boolean = False

Dim oServer As
New Microsoft.AnalysisServices.Server

' connect to the server and start scanning down the

' object hierarchy

oServer.Connect(sServer)

Dim oDB As Database = oServer.Databases.FindByName(sDatabase)

If oDB Is
Nothing
Then

MsgBox("Did not find expected database: " & sDatabase, _

MsgBoxStyle.OkOnly, "Error looking for partition")

GoTo Done

Else

Dim oCube As Cube = oDB.Cubes.FindByName(sCube)

If oCube Is
Nothing
Then

MsgBox("Did not find expected cube: " & sCube, _

MsgBoxStyle.OkOnly, "Error looking for partition")

GoTo Done

Else

Dim oMG As MeasureGroup = _

oCube.MeasureGroups.FindByName(sMeasureGroup)

If oMG Is
Nothing
Then

MsgBox("Did not find expected Measure Group: " & _

sMeasureGroup, _

MsgBoxStyle.OkOnly, _

"Error looking for partition")

GoTo Done

Else

'-- This is the real test -- to see if the partition

' is really there

Dim oPart As Partition = _

oMG.Partitions.Find(sPartition)

If
Not oPart Is
Nothing
Then

' This is the only place to set the value to TRUE

bIsPartitionThere = True

End
If

Done:

oServer.Disconnect() ' disconnect from the server -- we are done

NoConnection:

Return bIsPartitionThere

End
Function

End
Class

This Week I am Mostly Listening To...

2006-09-18T23:22:00.000+01:00

Captain - This is Hazelville

Simple SSIS Logging - Part 2, Devil in the Detail

2006-09-12T23:12:00.000+01:00

Ok so now you know when a package process starts, when it finishes and its final status (See Part 1). What we don’t have is the ability to accurately analyse the individual steps within a package and make effective decisions based on the information available.

First you will have to decide what you are looking to track within the system. Obviously errors are the most important element as fast resolution is essential. Next should be warnings. The warnings generated within SSIS are key to identifying areas that may have performance issues. A common warning in SSIS would be where a column is brought into from a data source but subsequently not used throughout the package. SSIS will raise a warning at every transformation informing a user that these unused columns may hinder performance but does not effect the final outcome of the package.

For these examples we’re writing our log to a database so it’s necessary to have a structure to hold it. A nice simple structure should let you know the type of alert, timestamp, source task and any other descriptive information (such as error code and error description).

When you have the table structure you can write an insert statement, the most sensible option is of course a stored procedure with a number of parameters serving your needs, something like this;

usp_WriteSSISEvent @ServerName, @EventType, @SourcePackage, @SourceTask, @ErrorCode, @ErrorDesc, @TimeStamp

So how do these variables get populated? Well for the most control on what gets written and where (and it’s pretty simple to boot, hence the title), I’m first going to put an Execute SQL task in to the OnError event handler. After setting the connection manager set the SQL statement via an expression. If you’ve not done this before it’s a very useful thing to know about but to cut a long story short it’s possible to set any number of task properties via an expression.

The expression will effectively be the constructed SQL statement with your parameters populated via a combination of system variables and direct input. Here’s The statement for an OnError event based on the above stored procedure example.

"EXECUTE [usp_WriteSSISEvent]

@ServerName = '" + @[System::MachineName] + "'

,@EventType = '" + "OnError" + "'

,@SourcePackage = '" + @[System::PackageName] + "'

,@SourceTask = '" + @[System::SourceName] + "'

,@ErrorCode = " + (DT_WSTR, 15)@[System::ErrorCode] + "

,@ErrorDescription = '" + @[System::ErrorDescription] + "'

,@EventTimeStamp = '" + (DT_WSTR, 30)GETUTCDATE() + "'"

Yeah I know it looks a bit of a mess but trust me, this works .

The pain with this method is it’s not possible to populate the @EventType via a system variable meaning that you have to populate it manually for each event type you wish to record. The other gotcha is that the system variables differ dependent on the event type. For example of the 4 I usually use, OnError, OnWarning, OnPostExecute and OnPreExecute, only errors and warning have the error code and description. Something to be aware of.

If you want to see it working then I have an example project all ready to send, just mail me at mailto:steve@stevemchugh.com?subject=Simple%20SSIS%20Logging%20Part%20Deux and I’ll send you a copy. It has the database structure as a script so there’s not too much effort involved in having a look.

Yet Another Tip as Cunning as a Fox Who's Just Been Appointed Professor of Cunning at Oxford University

2006-09-12T21:39:00.000+01:00

SSIS package deployment, in my opinion is a right royal pain in Aris (I'm sure there's some kind of Wiki out there for cockney rhyming slang, there certainly is for Blackadder quotes).

This became evident whilst trying to successfully deploy password protected packages to folder locations on SSIS servers. So again I came up with a plan to make my life a little more bearable. A plan as as cunning as a fox that used to be professor of cunning at Oxford University, but has moved on, and is now working for the UN at the high commission of international cunning planning..!

All my SSIS projects, of significant size, now include a package called sys_DeployPackages. This does something very simple. deploys packages. A couple of variables such as the source folder and the destination, the password I want to use to encrypt the packages, the location folder on the SSIS server and hey presto. Put the variables into a config file and then you can deploy the same files to another server with just a double click.

All this with just the joining of the Execute Process task and dtutil.exe. a combination so cunning you could brush your teeth with it.

This Week I am Mostly Listening To…

2006-09-12T19:47:00.000+01:00

The Dears – Gang of Losers

Another Little Tip

2006-09-12T17:31:00.000+01:00

I don’t know if you sometimes have a package that contains lots of execute package tasks. Lord knows I do and often too. This is especially the case when pulling a number of tables out of a source system into a staging database.

Well all those individual packages have to have their properties set and their names changes and quite frankly double clicking on each of those tasks waiting for them to spring into life is something I’m not that keen on.

As an expert in trying to makes life a tad easier I now set the package name property via an expression that cunningly grabs the Integration Server path from a variable along with the task name, the combination of which is the full location of the package on the Integration Server.

All I have to do then is copy and paste the task X amount of times and change the name of the task to the package I want to execute. Lazy I hear you say, and there was me thinking I was working “smarter”…!

A little tip...

2006-08-30T11:26:00.000+01:00

...you know in SSIS when you have to create a parameter based OLE DB command (UPDATE tablename SET column1 = ?, column2 = ?, etc) well an easy way of getting the SQL statement together without the pain of typing those ? in yourself is this.

Right click on the table in SQL Management Studio and create a script to update the table you’re looking to update. Then do a Ctrl H (search and replace) which then gives you the option to replace based on a wildcard search. Search for “= <*>” and replace with “= ?” and voila one update script ready for pasting into the OLE DB command.

Mind you it doesn’t help when you then have almost 100 columns to map out :(

Simple SSIS Logging - Part 1, The Load ID

2006-08-03T15:51:00.000+01:00

Logging in SSIS gives you plenty of options. You have the built in event handling as well as the purpose built logging options. How this is used tends to be pretty different dependent on what project you’re working on and in the majority of cases you’ll find yourself implementing something very similar every time, then you’ll have to implement something very similar into every package on every project. This is time consuming and a pain and you can lose out on a lot of information that to be honest you will need later on for administration.

Let me break it down into a few simple key elements that I believe are important in BI based SSIS projects. Firstly you need to be able to capture the start and end time of a particular process. Secondly, absolute traceability of the data from the source system interfaces through to the warehouse storage itself. Finally, organising the output from any logging into a known structure for later analysis.

The concept of a load ID is fairly familiar to anyone moving data out of a source into a staging database, or if you’re skipping the middle man, straight into a warehouse. The load ID can be a unique indicator based on the specific process execution that extracted the data or identifies a number of loads to different tables. In the second case the source dataset itself becomes part of the identifier.

Typically the start load process will populate a known table in a logging environment somewhere and return to the package a Load ID. On a recent project this was built in to its own component that had properties such as the connection. The returned Load ID will get tagged onto each of the records that are written in the Data Flow Task. Using this information, along with the data source or file, you have full end to end coverage of where a record came from and where it has ended up in, for the purposes of this say, the staging database.

Obviously there’s a little more to this than just putting a script at the top and bottom of the control flow but not much more. The structure of the table that both provides the Load ID and logs the start time, end time and also the status of a particular load is pretty simple.

click on image to enlarge

There’s a bunch of other information you can attach to this table such as the package name, source server, pretty much anything you like. The important thing is capturing the core data and the real power comes from the reporting available from such a narrow dataset. Add the environment type and you can make comparisons between development, test and production performance. Add in a process identifier and you can see if one of your processes for transforming BI data is affecting a daily load task performed on one of your transactional systems.

Although this provides a very simple mechanism for logging and tracking SSIS packages, that’s all it does. For even more detailed information and to track problem areas within a package, a whole new level of logging is required. That I’ll go into in another post. I am of the view that structuring logging in this fashion not only provides benefits for administration after us consultant types have cleared off site, but also speeds up implementation when you have a few simple SSIS components and a single logging database to deploy. All this and able to apply the K.I.S.S. * rule to the process.

* Keep It Simple Stupid

Failure - Data Source Does Not Exist..!

2006-08-02T10:51:00.000+01:00

One of the SSIS interfaces I have been working on a project pulls data from 10 identically structured databases. The task we had was to pull data for a particular day, supplied via a parent variable, and place the data into a single file for import into the staging database of the BI infrastructure.

The problem was that if one of the data sources was down, the WAN connection playing up or there was any other problem that would cause one of the data sources to fail, the entire package would fail. We didn’t want this behaviour because the client wanted as much data as possible rather than a complete failure.

For starters the OLEDB source wasn’t going to help us in this situation so we had to go for a scripted data source and changing the Acquire Connection code with a simple Try-Catch statement. This worked well and does the trick. We can run the entire package without any problems building up the connection string as variables that are passed into the script and connecting to the required single data-source. But then another requirement popped up.

How do I know when a data-source has failed, I don’t want the package to fail but a warning would be nice and enable us to capture the event in our logging mechanism. Enter the FireWarning method. This little diamond, along with its cousins FireError, FireInformation and FireProgress allows you to place custom entries into the SSIS logging.

I have an example package built on Adventure Works if anyone wants it and, although quite simple, I have come across a number of instances where connecting to multiple data sources (including flat file connections) where this behaviour is needed.

Secret Source of Pleasure..

2006-08-02T10:41:00.000+01:00

... or not so secret if you check out some of my other links over there -->

I am the proud owner of a launch day purchased Xbox 360 and in my spare time enjoy the odd thrash on there. My recommnedations at the moment are

Geomtery Wars - for a quick thrash on the machine.
Table Tennis - so simple yet so enjoyable.
Street Fighter II - coming to Xbox Live Arcade today, god I wasted so much of my time at college on that game..!

If anyone fancies a round or two on anything my gamertag is one1eg (that's a one in there).

See you online.

Steve

This Week I am Mostly Listening To...

2006-08-02T10:11:00.000+01:00

The Rifles - No Love Lost

Other Places to go...

2006-08-02T09:42:00.000+01:00

In my first proper contribution to this years blogging (yeah, yeah, I know) I was going to give a quick overview of some of the blogs I regularly read and why.

First up is a Mark Hill (http://markhill.org/blog). I've known Mark for a number of years and worked with him for almost all that time. His blog is in his own words "a ramble through Microsoft BI technologies". Give the guy a little credit here, it's not a ramble but quite an insightful comment on Microsoft’s BI platform and essential viewing if you're looking for information on the problems you'll face implementing large scale SSIS and Analysis Services systems and especially if you're looking to put 64 bit servers within your architecture.

Next is Thiru Sutha's SQL Server 2005 BI blog (http://tsutha.blogspot.com/). Again someone I've know and worked with for a number of years has been working on the same projects as Mark Hill and significantly contributed to stabilising 64 bit SSIS. Definitely worth a viewing and contains some good example SSIS packages for your pleasure in addition to postings on known SSIS bugs.

Chris Webb (http://cwebbbi.spaces.live.com/) is a pretty well known individual in the world of Microsoft business intelligence and is your first port of call if you need to know anything about using MDX in the field. His collaboration in the latest version of MDX Solutions (along with George Spofford amongst others) gives the book a lot more weight and makes it an excellent guide to anyone looking to enhance their MDX knowledge.

Off the BI band wagon slightly here The Joy of Code (http://www.thejoyofcode.com/) is a collaborative blog that includes Josh Twist, a colleague I’ve worked with recently on a SQL Server 2005 BI architecture implementation. Josh is .Net developer by trade but was huge benefit to the projects custom SSIS component development. A number of the SSIS components he worked on are available there with example code for download, some of which I’ll discuss in further detail later along with some recommended applications.

Finally another SSIS orientated blog from Jamie Thomson (http://blogs.conchango.com/jamiethomson). I have, lets say, borrowed plenty of SSIS code from this site recently and his postings on things like what you could do in DTS but aren’t as obvious in SSIS are very useful for anyone making that transition.
Well that’s it for now hopefully you’ll find these sites as useful as I have.

Steve

Confession of a Freelance Dolphin Trainer...

2006-08-02T08:59:00.000+01:00

Forgive me Blogger for I have sinned.

I've slacked in the blogging world in recent months so I thought I would get my ass into gear and whilst my time isn't complete monopolised with the daily grind I thought I would contribute to the amount of information currently in existence.

The next few post will be general updates on what is happening in my world be it work related or not.....

Steve

A Storms a Brewing

2005-09-30T15:31:00.000+01:00

To say things have been quiet on here would be an understatement but there is a very good reason for this, content is going to be moved somewhere else and a lot more of it will be produced (minus the things I'm currently listening to).

So what more can I say but watch this space............

Back to the slog

2005-08-23T11:14:00.000+01:00

After what has been a pretty wasteful month so far culminating in an excessive trip to the 2005 V Festival I'm back on the more serious subject of work starting with a small tip in Analysis Services that I see get missed a lot on my travels.
You've got a dimension, say a time dimension that has levels of fiscal year, fiscal period and fiscal year. You have 2 cubes, one that looks at data at week level and another looking at period level for one reason or another. On many occasions I've seen a second time dimension added for the period level cube (or similar scenario) when all that needs to be done is disable the fiscal week level in the time dimnesion in the period cube.
It's pretty easy to find, in the cube editor open up the dimnesion whose level you want to disable, highlight the level and in the advanced settings set 'Disabled' to Yes and there you have it, one less dimension, member properties and performance hit to worry about.

This Week I am Mostly Listening To...

2005-08-16T09:48:00.000+01:00

The Subways - Young For Eternity

Normal Service Has Been Resumed

2005-08-01T15:38:00.000+01:00

My deepest apologies for anyone who takes the slightest bit of notice of this blog but I am currently sitting in the "internet cafe" within the complex I'm staying at in Lanzarote on my honeymoon (there's dedication for you) and have found out that my blog was not functioning in anything but Firefox, whoops. I believe those problems have now been rectified but would appreciate a mail to yourblogisrubbish@stevemchugh.com if you spot any other bloopers. As a good friend recently commented, you#ll never be a web developer steve....

This Week I am Mostly Listening To...

2005-07-27T08:45:00.000+01:00

Interpol - Antics

This Week I am Mostly Listening To...

2005-07-18T09:35:00.000+01:00

Stellastarr - Stellarstarr

New W3 Tag

2005-07-13T19:58:00.000+01:00

If you're doing any courses on SQL Server 2005 make sure it's based on the June CTP.

2D 3D

2005-07-12T21:06:00.000+01:00

Last week was a titanic one for me to say the least. My ongoing battle shoe horning Analysis Services data into Reporting Services continues with the completion of another 40 reports to add to the others completed so far on the project I've been working on.

To try and make my life a little easier I decided to spend a little time working on a way of easily tweaking each report to minimise the amount of work necessary to complete the report suite.

Bit of background, the report I'm working on has 3 alternate hierarchies in a store dimension which need to be drillable (whilst displaying different subreports at each differing level of each alternate hierarchy). On another axis is a crossjoin of a subset of the measures and what could be best compared to as a product dimension. A better explanation maybe would be to try and visualise the report....

Standard matrix with the first row group displaying a list of the children of the current store hierarchy parameter value. Row group 2 is a list of the children of current product hierarchy parameter unioned with a list of measures which effectively gives you the sales value for product sub categories A B and C and then things like number of stores and number of units sold for the parent category of A B and C.

Finally the column group was driven from a named set containing the previous 14 fiscal periods. As I previously said the report was drillable by store but also by product. This meant that a single report to the user was in fact built up of 40 reports linked and passing parameters between themselves to cater for every level in every dimension multiplied by 3 due to the alternate hierarchies.

What I ended up doing was removing the 'database' fields from the matrix and replacing them with calculated fields meaning I didn't really have to touch the matrix for the changes but only the calculated field for each separate report.

This may seem like going the long way around but for this requirement and this set of data this was quite literally the only solution and meant I was producing reports in several minutes along with thorough testing. Anyway I just thought I would share that one.

Thursday 7th July

2005-07-11T22:36:00.000+01:00

It's been quite an eventful past 10 days here. My stag do, London winning the Olympics and finally the bombings in London which has devastated so many innocent people.

After so many highs it's difficult to conceive how low things can get. Much as I doubt that anyone this statement is directed at will ever see this I just wanted to give my condolences to all the friends and family of the people whose lives were lost because of Thursday’s event.