Independent Again

Just a quick note that I am no longer with WARDY IT Solutions. My three years working there were fun, rewarding, and I learnt a lot. I thank Peter Ward and his team for the opportunity to work with them.

In the end I felt that I needed to have more control over what type of work I am doing, so I have left permanent employment again and am currently operating as an Independent Business Intelligence Consultant. I am looking for opportunities to assist clients improving their capabilities from Data Warehouse design all the way to end-user Analytics and everything in between.

Posted in Uncategorized | 1 Comment

My first project using Biml & Mist

I have recently started using Biml & Mist. Both of these products have been created and maintained by Varigence. Most experienced SSIS / BI developers that I’ve had the pleasure to work with have expressed irritation with how much time is spent recoding a familiar pattern, but for a different source system or a different set of tables, etc…

This is where BIML comes into the picture. Biml is a domain-specific XML dialect for defining business intelligence (BI) assets. On it’s own Biml allows you to declare BI assets without worrying about a lot of rubbish that is normally save with an SSIS package (e.g. the layout information). This has the added benefit of not having to deal with the quirks of the SSIS development environment (of which there are many). With the addition of BimlScript though, BIML becomes a very powerful SSIS generation platform. BimlScript adds the ability to embed C# or VB.Net into your BIML files. “BimlScript is used to merge changes into existing Biml assets, automatically generate Biml code, and apply changes across multiple Biml files during compilation”.

There are already plenty of blogs out there explaining what Biml is, so I won’t regurgitate that. What I’d like to talk about is my experiences learning Biml/Mist and how this has changed the way I view SSIS development.

My first project
For my first project, I luckily had a new project starting with an existing customer where there is a lot of mutual trust. This project was a new ETL process into a brand new Data Warehouse. The biggest challenge for the ETL development is that the source system was still under development and as such would have extensive and rapid change. They wanted us to start ETL development in the mean time, which meant that there would be a lot of revisiting existing ETL code to make some minor changes. I explained the potential benefits of using Biml to my customer and they agreed that in their case it was worth the risk of letting me implement a new (and to them untested) method of development.

Leaning Curve
Firstly, like any new language, there was a significant learning curve to start with. That is to be expected when learning anything new. Probably my strongest criticism of Biml & Mist is that it is currently a bit light on good training content. There are plenty of samples out there, but you basically need to figure it out for yourself. Having said that, as I got a grasp on the basics, it started getting quite easy. I will say that to be effective, you will need a decent grasp on SSIS & .Net . Luckily I have these so it was right up my alley. The production of good training material will hopefully be high on the agenda of someone in the community (or at Varigence).

The Interface(s)
To use Biml & BimlScript you are basically going back to basics. It feels like you are developing in a text editor. I don’t mind that, but I know many who don’t like it. In BIDSHelper, the intellisense and the overall experience is not ideal. From what I understand, all of these issues are due to limitations in Visual Studio. So using Mist was a breath of fresh air after first doing the development straight in BIDSHelper. James Beresford recently blogged about the Mist interface here. He was not very complimentary, but he did have several valid points. If Mist is going to be every Biml developer’s tool of choice it will need some improvements.

Outcomes
So given this was my first project using Biml/Mist, I am sure there are still a host of tips and tricks that I have to learn. However I have been very pleasantly surprised. I developed my own (rudimentary) meta-data layer to sit over Biml, and using BimlScript and Mist’s transformers I have built a framework whereby I no longer fear change in the source systems. My framework has a long way to go before it is perfect, but the real beauty is that you only have to solve a problem once and then it just works in all the applicable situations. I would now struggle to go back to the traditional way of SSIS development for anything but the smallest or most unusual projects.

Posted in Business Intelligence, Development, SQL Server | Tagged , , , , | Leave a comment

Software Development Estimates for Small Jobs

As a consultant, I regularly get asked to estimate the amount of work involved in software changes that customers want. In principle this is fine. I know that all work that is done in business these days is based on a quote. However, in Software Development we are often asked to quote without having any kind of real access to the system we are going to be changing.

As a BI consultant, a lot of the estimates I do are for Reporting, ETL, or Cube changes. Most of the time I have never had access to the customer’s database(s) and have little or no understanding about the changes they want done. Imagine for a moment if a Civil Engineer was asked to quote on building changes without first being allowed to inspect the site and current buildings… It wouldn’t happen!

My experience is that the bigger the job is, the more opportunity is provided to do accurate estimates. This means that on the small jobs (e.g. a couple of weeks of work), you’re often just putting a figure down (guessing) and hoping it covers it. But maybe in such cases a full investigation is just not warranted, or even financially viable? I’ve had situations where the estimate on an individual change takes as long as it would to just make the change.

Anyone out there found a solution to this? Is there a better way of estimating work when the work required is expected to be quite small? Or are we always going to be making “educated” guesses for the small jobs?

Posted in Business Intelligence, Development, SQL Server | Leave a comment

Beware: Renaming a database with non-schemabound views

For many years now I have been aware that views may need refreshing. If you create a view without using the “SCHEMABINDING” clause, you will need to refresh the view if any of the underlying objects change (e.g. change the length of a column). This can be achieved by using either sp_refreshview or sp_recompile.

This past week I got reminded about this in a most unexpected situation. At a customer who is using Microsoft Dynamics CRM, we had a need to create some views to abstract others from the sometimes complicated nature of a CRM database. Because the owners of the CRM database did not want us to add these views to the actual CRM database, we created a new database purely to house the views. This database is guaranteed to live on the same SQL instance as the CRM database so we simply created the views with cross database queries (e.g. select columnA from SourceDB.dbo.Table1). This solution has been working fine for months. I should mention that this environment was a test environment (NOT Production).

Where it went pear-shaped was when they decided to rename the CRM database and restore from a recent backup. The restored database was named the same as the original CRM database name. Without giving it any major thought, I assumed that this would be a seamless change as far as the views go. I was wrong.

To cut a long story short, the views kept on working, but to our dismay we eventually realised that the data being returned was from the old renamed database, not the restored one. Since renaming a database is simply a metadata change, the views continued to point to the original database. Although I now understand why this is, it caught me off guard and I feel that this is a situation that Microsoft should address.

Below are some scripts that will allow you to view this behaviour for yourself. These assume that you are a sysadmin on the server you’re using. These scripts were run against SQL Server 2008 R2 SP1.

Firstly, create a source database with a simple table and one row of data (make sure that you adjust the physical location of the data files below):

use master
go

create database SourceDB
ON (NAME = SourceDb, FILENAME = ‘ <Folder>\SourceDB.mdf’)
LOG ON (NAME = SourceDb_Log, FILENAME = ‘<Folder>\SourceDB_Log.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(1, ‘Craig’);

Now create the database containing a view:

use master
go

create database ViewDB;
go

use ViewDB;
go

create view vSourceTable
as
select ID, Name
from SourceDB.dbo.SourceTable;

go

If you select the data from the view now you’ll see the data that we inserted into the database:

select ID, Name from vSourceTable;

image

Now rename the SourceDB:

use master
go
ALTER DATABASE SourceDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO 

exec SP_RENAMEDB ‘SourceDB’, ‘SourceDB_old’
Go
ALTER DATABASE SourceDB_old  SET MULTI_USER
GO

At this point some might think that the view should fail if you query it, but it doesn’t. It returns the same result set as before.

Now create a new database with the same name as the original but with different data:

use master
go

create database SourceDB
ON (NAME = SourceDb_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_New.mdf’)
LOG ON (NAME = SourceDb_Log_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_Log_New.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(2, ‘Bryden’);

If you query the view it is still showing the original values:

select ID, Name from vSourceTable;

image

In order to get the view to start working on the correct database, we need to refresh the view:

exec sp_refreshview ‘vSourceTable’

Now if we query the view, it is using the correct source:

select ID, Name from vSourceTable;

image

Strange but true!

Posted in Uncategorized | Leave a comment

I have joined WARDY IT

I have been a bit neglectful of my blog recently. I suppose I should have mentioned that I am no longer a contractor and am now working for WARDY IT solutions (www.wardyit.com).

I am super excited by this as WARDY IT has got a very good reputation in Australia and the opportunity to expand my knowledge is huge. My colleagues are all highly skilled and well-known people in SQL circles.

So if you are looking for excellent SQL Consultants in Australia, contact WARDY IT Solutions.

Posted in Uncategorized | Leave a comment

Error while installing Visual Studio 2010 SP1

I recently got given a new laptop to work on and soon realised that Visual Studio 2010 was not up to date. I started the installation of SP1, but the installation kept failing. I tried this three times and it failed three times. The thing that confused me was that it failed at random places. Luckily the logs that Microsoft gives for this install are very verbose and so I discoverred that the error was a common error message accross all the failures. Below is an example:

Returning IDOK. INSTALLMESSAGE_ERROR [The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. ]

After seraching for that error code, I quickly realised that the issue appeared to be access to a file that it was trying to replace. Given that this was happening at a random point in the install, I figured that the issue must be another piece of software running that was locking files.

After having a good look at the other apps I had running, I realised that there are two potential candidates. One was Microsoft Security Essentials and the other was Carbonite Pro (which automates my crucial file backups). I disabled both of these and the install succeeded.

I figure that Security Essentials was not to blame because i’ve never had a problem installing anything before. I’ve only been using Carbonite Pro for about a month, so I have to assume it was the culprit.

So if you’re getting the error above, check for file backup utilities that are running and disable them while doing your installation

Cheers

Craig

Posted in Uncategorized | Leave a comment

MS SQL Certifications

Today I passed my exam for MCTS: SQL Server 2008 Database Development. I am by nature quite nervous when I take exams, I don’t know why. With every Microsoft certification I have ever written I have finished the test and questioned whether I have passed prior to the results being displayed. In all cases I have passed reasonably comfortably. I have to admit that the Microsoft Certifications do appear to have gotten more difficult lately, but if you are prepared, you should pass quite easily.

The thing I really want to mention is preparing for this last exam…
I have a lot of past experience with SQL Server and therefore the preparation was a case of making sure that I am familiar with all the recent additions to the database engine. for this I purchased the official training kit (http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-433/dp/0735626391). Since I have been mostly focused on the BI stack of late, I thought it would be good to read the book cover to cover at first and then re-read and focus on sections I felt were my weakest. In between all this I was taking regular practice exams using the practice exams that came with the book. I have to say that this book and the supplied materials are almost shocking. For the most part, I spent time on websites or BOL to make sure I really understood all the new features. This book has four different authors and it shows! The style of some of the chapters were completely different to others. In some sections, the amount of detail was excruciating, while in others it was so light on detail that further investigation was definitely required. To make matters worse, I found at least four answers in the practice questions that were obviously wrong. That makes you start to question all of them.

I feel that if Microsoft are going to publish training kits, they need to be of a better quality than this. I would still recommend this training kit as one of the tools for preparation as it does outline everything you need to know. Do a lot of further reading on your weak topics and you should be OK. If you are new to SQL and are relying on only study to pass, you’ll need to do a lot more than just study this kit.

Cheers
Craig

Posted in Certification, Development, SQL Server | Leave a comment