May 2007 - Posts
I've been using Excel 2007 for about a month now and am I am strarting to
Ok so I suffered from the "Where the hell do I find option x" syndrome due to
the new ribbon bar, but, as I had heard before, this is a short term issue, you
will eventually understand how the options are configured and be able to find
things very easily.
This brings me to my first observation, the Ribbon bar. The way the ribbon
bar is configured does provide a much greater surface area for items, this means
that most items are only 2 clicks away. More importantly I have found many of
the options I use frequently are now accessible in 1 click compared to 5 or
more before. This includes, "Wrap text" , vertical text position and
It is obvious that the standard office toolbars haven't changed much over the
years and the development of the Ribbon has been influenced
by feedback from users so that the most used items are easily
The second major observation is formatting. This has taken a huge leap and is
just great, especially when dealling with large sets of data, where the addition
of vsiuals makes the data stand out from the page without the user having to
read every value.
Whats more I am someone that used conditional formatting extensively.
Previously this was stuck deep away in a menu, it is now an option
directly on the ribbon and provides access to builtin formatting and also a
means by which you can customise the formatting. If you've been to any demos of
Office 2007 or even BI in SQL Server 2005 you will have seen the data bars,
color scales and icon sets.
Finally functions, I haven't looked at depth into what new functions are
available I just know that 2 that have appeared are COUNTIFS and SUMIFS. These
allow you to specify multiple criteria for suming and counting a range.
I've often used data reader destinations as means of
dumping data from a data flow, because it doesn't need configuring. However I
always noticed that the package would hang for a while before completing.
I always put that down to some ADO.Net issue and just lived with it.
I am currently siting next to Tony Rogerson (who is badgering me about putting a link in this post to his
blog), at a Technet event where Peter
Blackburn is presenting on Advanced Reporting Services.
He's just covered using SSIS as a source of for a report. TO do this you have
to use a data reader destination. In doing so he highlighted that the data
reader is expecting something to be reading its data, when running the SSIS
package in the IDE nothing is doing that and so the data reader waits until it
times out. This timeout is defined as a property on the data reader
What you can do is to set the timeout so that it only waits a few seconds or
These events are always great for picking up great tips like this (even if
you I do have to endure sitting next to Tony ;)
Many people often ask how they can us a database in SQL
2000 that they have attached or restored to SQL2005.
Unfortunately this is a one way street, once upgraded the only way of using a
database in a previous version is to copy the schema and data manually. You
can't restore or attach a database even if the compatibility has been kept at
the previous version.
Paul Randal has sort of blogged about this, also highlighting the fact that
you can't even restore
a database to a version with a lower build, so that includes hotfixes and
Graham has also raised the question on his blog http://sqlblogcasts.com/blogs/grahamc/archive/2007/05/21/sql-server-2005-database-back-to-sql-server-2000.aspx
There is a little known feature of SQL Server 2005 is
that when you start SQL Server in single user mode local administrators of the
server have sysadmin access to the sql server.
You may feel this a security concern, but if a user can get local admin
of your server, and restart your service then having sysadmin access to your SQL
Server is the least of your problems.
So next time you forget your sa password and restart your server in single
user mode, login using sqlcmd and reset the password. And away you
I've just spent a morning trying to figure out why my
average measure doesn't average but sums.
I guess I should have googled straight away. In doing so I found this forum
Christian also mentions this behaviour but only for the other semi additve
measures, It does also apply to the AverageOfChildren measure as well http://blogs.conchango.com/christianwade/archive/2005/04/07/1252.aspx
To get wrong this I created a simple calculated measure by going into the
calculations tab. Clicking New Calculated Member, giving it a name and then
putting in an expression like,
[Measures].[Sum Of Ping Duration]/[Measures].[Pingstat Count]
Where the [Sum Of Ping Duration] is the sum of the measure I want the average
for and Pingstat Count is the number of instances of the measure.
I don't think you need to put in some divide by 0 handling because if the
count is 0 then the duration will be empty and so won't be calculated.
Happy to be told the best way to do this.
Update: I've just read Mosha's post on averages which is much more detailed and a very good read.
No i'm not talking about a new Harry Enfield sketch
about a posh bloke that's a bit thick, I 'm talking about tables in a warehouse.
Many people insist on naming tables DimCustomer, Dim Product etc and
FactOrderLine. What is the purpose of this, especially in a UDM model where
tables can easily be dimensions and facts. What happens then, should it be
DimFactOrder or should it be FactDimOrder, sounds like something from a Chinese
I for one don't like the idea of naming objects this way, if you don't know
what is in the Customer table then you shouldn't be touching it.
I wonder what silly names one could end up with.
I am doing my first speaking event outside of the
UK. The guys organising the Irish Microsoft Technology Conference kindly invited
me to speak.
Following on from and combing some previous sessions I've done I will talking
about Service Broker and how it can be used to achieve asynchronous processing
to greatly improve your systems.
The conference can be found here http://imtc.firstport.ie/
If you run this sql on a sql 2005 box then you will see
which indexes aren't used in queries compared with the number of times they are
from sys.dm_db_index_usage_stats s
join sys.indexes i on i.index_id =
and s.object_id = i.object_id
where s.database_id =
order by user_seeks,
Thanks to Donald for pointing out this great training onhow to do presentations.
My presentations will never be the same again
I remember seeing a very early CTP of Yukon and the new
DTS. I was very exceited to see a parallel property on the loop container which
would allow the contents of the container to be executed in parallel.
If you've used the RTM on Yukon you will know that there is no such property.
Unfortunately it was too complex to be implement.
However you can still get parallel execution by using multiple tasks or by
having multiple paths in a data flow.
For more details read this post
More Posts Next page »