This is the second part in a series of data warehouse presentations I’ve been giving. This one concentrates on the physical design of the underlying SQL Server database, some information on the SQL Server Fast Track Data Warehouse and finally a one slide guide to tuning the storage engine performance. The tuning slide is only really a first step and I plan a more in depth session some time in the future.

Also I apologize, this deck is a little word heavy. I prefer more graphics in a presentation but there is a lot of specific guidance to list here.

As a photographer I don’t really agree with this amendment which came into force on the 16th February (bold text mine):

A person commits an offence who—

1. elicits or attempts to elicit information about an individual who is or has been—
   1. a member of Her Majesty’s forces,
   2. a member of any of the intelligence services, or
   3. a constable,
   which is of a kind likely to be useful to a person committing or preparing an act of terrorism, or
2. publishes or communicates any such information.

A person guilty of an offence under this section is liable—

1. on conviction on indictment, to imprisonment for a term not exceeding 10 years or to a fine, or to both;

Now what about Street View? I won’t publish this picture but this link should take you to a page on Street View.

Note that Google may edit the image after I’ve posted this but right now the left hand policeman is clearly visible and happens to be standing outside the residence of a well known politician.

Does this fall foul of the Terrorism act?

[Edit: that was quick, they have already removed the image but you can move up and down the street to still get a clear image of the other policeman.]

I recently gave a presentation at our community event on Dimensional Modeling. It covers the logical design and user model phase of a data warehouse project.

One of the big problems I have with some new customers is the knowledge gap between where they are and where they need to be in order to be successful. Given that people can read much faster than they can receive presented information it makes sense to have a reading list.

I’ve blogged previously some Analysis Services resources but new and better content is appearing all the time. In addition to that article here are my current reading recommendations for anyone planning a BI or Analysis Services project. Know this lot backwards and you should have a good head start.

Everyone

SQL Performance Tuning with Waits and Queues

Analysis Services Performance Guide

Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services

The Data Loading Performance Guide

Report Server Catalog Best Practices

Reporting Services Performance Optimizations

New Data Warehouse Scalability Features in SQL Server 2008

Scaling Up Your Data Warehouse with SQL Server 2008

Architects and Developers

OLAP Design Best Practices for Analysis Services 2005

Best Practices for Data Warehousing with SQL Server 2008

Analysis Services Processing Best Practices

Top 10 SQL Server Integration Services Best Practices

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Operations and Support

Storage Top 10 Best Practices

SQL Server Best Practices

IIS Performance Tuning

Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios

In fact pretty much anything on the sqlcat website is pure gold.

Canon 400D, Canon EF-S 10-22mm f/3.5-4.5 lens - 1/60 sec, f/11, ISO 100

I've been a bit lazy with photos lately. Some have gone to Flickr but not blogged. I also haven't been taking as many as I would like. I blame the cold weather mainly but also I'm doing about 14+ hrs travel a week at the moment for work and the last thing I want to do in the evening is go out and take photos. Hopefully things will pick up as the weather gets warmer.

I'm also torn between the new Canon 5D MkII and swaping all my kit and going with Nikon. Tough call - if I was starting out and knew what I know now about the two systems I'd go with the Nikon in a heartbeat but I have a lot of Canon gear which I'd have to trade in at a loss to swap over.

I'm probably going to sit on the fence for a while and see what happens. Unfortunately prices are set to rise due to exchange rate issues but there is a rumour of a new Nikon D700x so I'd be interested to know if they can stick the final nail in Canon's coffin.

There are no hard and fast rules but the goal is to reduce the time taken to extract data from a source system and reduce the amount of work you have to do with the extracted data. The numbers quoted here are the ones I use as a starting point but you need to measure to determine the best values.

Don't do an incremental extract if:

• There isn't much data in the source table (less than 100k rows)
• There is enough change in the source table to require that you read most of it each time (for example if more than half the rows change between extracts)
• The data in the source table is used for periodic snapshots (for example a balance sheet) and you need to track how a table changes at particular points in time

Do an incremental extract if:

• There is a lot of data in the source table
• Rows are only ever added to the source table (i.e. rows are not updated)
• You need to track each and every change to a source row
• The source data is updated a number of times before being closed and once closed is never updated again (also known as an accumulating snapshot)

In general dimension tables match the first set of rules and are not extracted incrementally where as fact tables normally match the second set of rules.

Canon 400D, Canon EF-S 10-22mm f/3.5-4.5 lens - 1/2 sec, f/7.1, ISO 400

I haven't posted anything for a while so here is a view from Westin Bonaventure Hotel in Los Angeles. I was over in LA at the end of October for Microsoft's Professional Developers Conference (PDC).

Canon 400D, Tamron AF 55-200mm f/4-5.6 Macro lens - 1/40 sec, f/8, ISO 100

Since I can't afford nor make enough use of a dedicated tilt-shift lens I have to fake it but one of the main benefits is you get images that look like miniature models. This is my method...

First you need to start with an image that will end up looking realistic. It needs to be taken from quite a distance and with a long lens to compress perspective. You also need to be above the subject just as if you would be when taking a photo of a model.

Next take the image into Photoshop and duplicate the background layer (I always work non-destructively).

We are going to use the lens blur filter with a depth map to indicate where the blur should be so add a layer mask to the duplicated layer.

Select the gradient tool and select the black to white gradient, reflected (4th icon from the left) and reverse options as shown below.

  Then draw your gradient onto the layer mask. As we have the reverse option ticked you start at the point where you want the focus to be and move to the top or bottom of the screen. In this sample I put a slight slant in to ensure the focus hits both the people and the hut.

If you Alt-Click and then Ctrl-Click on the layer mask you should see something that shows a narrow band of white with a slow fade to black at the top and bottom of the image. Note that for the best effect you should have a long smooth gradient and not a short transition to black.

Hit Ctrl-D and then Alt-Click on the layer mask to switch back to the source image. Then make sure you click on the image icon to the left of the layer mask before invoking the lens blur filter (Menu > Filter > Blue > Lens Blur...). You need to select "Layer Mask" for the source and, since we have a white to black gradient, ensure that Invert is checked. The rest of the options just control what kind of bokeh you are after. When you are happy click OK and wait, possibly for a long time as the filter is quite expensive to run.

When the filter completes, just delete the layer mask to reveal the miniaturised image.

To make the result even more believable you can try and emulate the kind of lighting used when photographing miniatures. Boost the saturation and contrast with curves and you will have the final result at the top.

This is really the tale of three processes. I originally shot this with HDR in mind but when I processed it I came up with:

...which I think looks awful. I almost fell out with HDR at this and took the middle exposure to do a standard "7-point system" process in Photoshop to get this:

...which is much better but a little plain when sat next to the HDR version.

I wondered how I might get the best of both worlds so ended up loading the two images as aligned layers in Photoshop. My thought was to take the main image data from the plain one and use either the luminosity, hue, saturation or a combination from the HDR image.

The result wasn't great as it just became garish again so after messing around with different blending modes I settled on 'overlay' to make this image which I personally think is the best of the three.

So what do you think? I would love to know if you agree so please look at all three images and fave the one you like the most.

Best viewed large on black.

Canon 400D, Canon EF-S 10-22mm f/3.5-4.5 lens - 5exp (1.6-20sec), f/8, ISO 100

As promised, here is my pick from yesterday's early morning shoot. There seems to be something about HDR that can turn an ordinary picture into somthing magical without too much effort.

If you look beyond the pier you will see a smear of lights which was the boat that appears in other pictures. I left it in to illustrate how long this one took as the boat was travelling very slowly.

You may also have noticed I've borrowed a naming convention from Thomas Hawk, that is "Plate 1", "Plate 2" etc because I take a lot of photos of the same thing and need some way to differentiate them.

Best viewed large on black.

Canon 400D, Canon EF-S 10-22mm f/3.5-4.5 lens - 10 seconds, f/8, ISO 100

Since Claire got up way before dawn to catch a plane this morning I decided that since I was awake I would go and grab some dawn pictures.

I was at the beach about half an hour before dawn so I took some test shots of which this is one. The orange light is from a sodium street lamp just out of shot which, I think contrasts nicely with the blue sky.

I almost called this one "Oh my god, it's early" but the face in the life guard hut made me change my mind.

In the end I didn't get a nice sunrise because of the overcast sky. I saved the best for last though and will upload those shots tomorrow.

I ran across an interesting Facebook feature just now. When clicking on a link in one of the group forums I was presented with this:

Nice catch! I suppose they could have just removed the post from the forum though...