I've just spent the day tinkering with SQL 2005 to build a very quick prototype of my Timesheet Project. It only has four tables but was enough to enable me to create a SQL database, load some data with DTS and build an analysis cube. Here are some initial thoughts...

The lack of a database diagram tool in all three studios (Management, BI and Visual) is a pain in the neck as I usually start a database by creating a few tables on a diagram, adding some relationships and changing them around until I'm happy. It's quite an iterative process and very difficult to achieve when you can't see the whole database at once.

The table editing features in the Management Studio are quite easy to use. It's functionality is almost the same as the old Enterprise Manager in this respect but it feels more natural. On the other hand, I don't like the database project feature because it's a poor effort when compared to the facilities provided in other projects (DTS, C#, etc). It was hard work as there is no "create folder" and more importantly the scripts created as the result of "script to new query window" can't be added to the project. You have to create a query file in the project and paste into it. Visual Studio has had "add to project" on the right click menu since before VC6.

The Universal Data Model concept is going to take a little getting used to. It was extremely easy to create the cube using the intellicube wizard but they look strange when compared to SQL 2000 cubes. The dimensions have multiple hierarchies - one for each field in the dimension table. There is no neat star/snowflake schema, just a bunch of tables that look more like an OLTP database.

Finally, DTS development is a dream. The environment is great and there are loads of useful components to use. I did notice, however, that CSV files written by the File Destination component can't be read by the File Source component due to the final CR/LF. I have to assume this is a bug - one for Beta Place...

Three new blogs about SQL Server 2005:

• Bob Beauchemin's Blog
• Hitachi Consulting Yukon BI
• Scalability Experts Blog

Well worth reading...

I was just tinkering with our schema and thinking about time - specifically UTC offsets and the time dimension. Our current scheme is for two dimensions; one to hold time of day and the other to hold dates. It works pretty well as we are normally querying either inter-day or intra-day. Not both. It also keeps the dimensioning code and tables under control.

As we are currently using UK time, the only issue to be aware of is the hour when the clocks go back in October. It doesn't affect queries though - we just get some data put in the wrong time-slot.

As the next version is going to be culture aware and support multiple time zones then this model fails because any query needs to take into account the possibility that the time zone offset may cause a difference in date members.

So for example, assuming the data is stored in the database in UTC, to get the facts for a particular day is:

select * 
from Facts f 
join Date d on d.DateID = f.DateID 
where d.Date = '2004-07-17' 

But with TZ offset (e.g. PST = -8)

select * 
from Facts f
join Date d on d.DateID = f.DateID
join Time d on d.TimeID = f.TimeID
where d.Year = 2004
and   d.Month = 7
and   (   
          (d.Day = 17 and t.Hour < 24 - 8)
       or (d.Day = 16 and t.Hour > 24 - 8)        
      )

Note that this can spill into months and years as well (imagine a time zone offset query around New Year's Eve). I don't think it gets any simpler with MDX.

If, on the other hand, we were to combine the date and time dimensions then the query becomes a lot simpler (here we can offset globally):

select * 
from Facts f
join DateTime d on d.DateTimeID = f.DateTimeID
where cast(floor((cast(d.Date as float) - 
    (8.0 / 24.0))) as smalldatetime) = '2004-07-17'

I think it's even easier in MDX with the Lag() function.

One problem to consider though is the explosion of size in the time dimension. The time dimension now has 525600 members per year as opposed to the old scheme with 365 (Date) + 1440 (Time) members.

Also, the SQL Server 2005 Intellicube wizard only has room for a single time dimension.

I have a meeting tomorrow to discuss a number of deployments we have in the next couple of weeks. One thing to note is that all installations come with their own set of problems and I don't always have the answers. However, preparation is key; as they say - "pathetic preparation makes for a piss poor presentation".

One thing I'm going to try is a bit of brainstorming. I'm going to draw up each installation on a white-board and we'll list up anything that could possibly go wrong. From that we should be able to plan for nearly every eventuality. Hopefully it won't be a too depressing experience .

The main stay of my deployment plans is practise. If you have done it in the office without a customer breathing down your neck then the real thing is usually much easier. We try and get hold of a backup from the customer as they usually have datasets containing unforeseen combinations of data (especially as the source database doesn't have any constraints - shame on you Cisco!).

The last thing that commonly trips us up is firewalls - most of our customers are in retail or banking so security is sometimes taken to the extreme. It would be costly for us to reproduce their firewall configuration (one customer has DMZ's around each server), so a quick tip is to use personal firewalls such as ZoneAlarm to lock down your test servers similar to a DMZ deployment.

It's August and it's hot so, by tradition, the British public head to the beach. As a slight alternative I went to Hurst Castle which stands on a spit of land between the south cost and the Isle of Wight. It was originally built by Henry VIII as part of a coastal defence chain. The castle was used right though to the Second World War with various upgrades and additions along the way.

As the castle on a shingle spit there is no road and you have to walk 1½ miles down loose rocks. Very tiring but worth it. I took the camera and have loads of pictures so see what its all about.

I noticed this whilst browsing the SQL Server 2005 online help. It creates a permanent (until you DROP SYNONYM) alias to an object in SQL Server. This could be very useful in shortening the amount SQL you need to type. Imagine you have a remote database containing a schema and table. You can either:

SELECT * FROM REMOTE_SERVER.SCHMEA_NAME.THE_TABLE_IN_QUESTION

Or:

CREATE SYNONYM THE_TABLE
    FOR REMOTE_SERVER.SCHMEA_NAME.THE_TABLE_IN_QUESTION
GO
SELECT * FROM THE_TABLE

Useful? Yes, but even more so when you need to use a database that has daft object naming conventions like Remedy managed systems.

CREATE SYNONYM Customers
    FOR dbo.C0183
GO
SELECT * FROM Customers

I've just started reading "Agile Project Management" by Jim Highsmith and it's looking very promising. Whilst reading chapter one the point slapped me like the Tango Man - it's the way in which agile projects are different to others.

Traditional project are all about deciding what you are going to build and then carrying out your plan whereas with agile projects you have a plan but it's about how, not what, you build. In fact the key point made by Jim is that agile projects are all about experimentation and adaption.

He cites a comparison with the pharmaceutical industry which makes things very clear. A few years ago they used to sit down and design molecules for a specific task whereas now they synthesise thousands of molecules at a time and use automated testing to screen for desired properties. The results are fed into a database which the scientists use to make their next steps. This rapid experimentation enables them to generate new drugs much faster even though they don't know the end result until it presents itself.

Consider this in the context of software development. Rapid cycles of experimentation with unit tests to screen the results and ensure forward motion. These two concepts enable a project to progress in a more efficient way than if a solution were "designed".

man·age·ment

The act, manner, or practice of handling, supervision, or control.

lead·er·ship

Capacity or ability to lead, give guidance and direction.

There is a big difference between the two.

Bradley Greathead, a fellow Exony employee has started blogging. He is responsible for our application server and, in particular, the middle tier development. I foresee lots of useful posts - subscribed!