October 2009 - Posts

This Thursday 29th is the London SQL User Group at Microsoft where we have Jamie Thomson talking about "Deploying SSRS Reports using MSBuild", we have one of our meeting sponsors Steve Hitchman from WhereScape demonstrating their Data Warehouse Development Workbench for SQL Server and we also have Andrew Sadler talking about "BI 2.0 - I'm searching.... give me a minute".

If you do not want't these emails then please just reply with remove in the subject and I'll update my list; you've got it because you are registered and still subscribed.

While I'm emailing you, don't forget the next Reading user group meeting is on the 11th Nov and I'll be talking about High Availability (Partitioning, File Groups (use of, backup/restore)) and an Introduction to SQL Server architecture and I'm currently looking for another one or two speakers, so if you want to come to that then get yourself registered -
http://sqlserverfaq.com?eid=171. Please make sure you are registered if you are coming so we know how much pizza we need! If you are already registered then this is also your confirmation.

Register and more details:
http://sqlserverfaq.com?eid=192.

This meeting is being sponsored by Microsoft and WhereScape.

The User Group meetings are very informal, very interactive and a really great method of expanding your knowledge, meeting other SQL Server professionals and getting answers to your problems.

17:30 - 18:00 Meet & Greet

18:00 - 18:15 Round Table discussion and Nuggets

Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have - perhaps a work problem, general guidance etc..

18:15 - 19:15 Deploying SSRS reports using MSBuild - Jamie Thomson, SQL Server MVP

Come and see how deployment of Reporting Services can be automated and integrated into msbuild.

19:15 - 19:30 Break and Pizza

19:30 - 20:00 Steve Hitchman - Demonstration of Data Warehouse Development Workbench for SQL Server

20:00 - 21:00 ‘I’m searching .... give me a minute.’ BI 2.0 - Andrew Sadler, Hitachi Consulting

Search functionality is all pervasive. Outlook, explorer, intranet, internet, its everywhere. Why shouldn’t BI be the same?

As the boundaries between structured and non-structured data get blurred, end users are expecting to see and get more context around their BI as well as being lead into their BI in an intuitive user paradigm, like search.

This session highlights some simple yet quite powerful ways to expose some of search functionality that Sharepoint offers, in relation to Mirosoft’s BI offering, using several real world use-cases.

Many thanks

Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com
http://sqlblogcasts.com/blogs/tonyrogerson

There is so much out in the cosmos for helping with the manageability of SQL Server, this particular download from Microsoft is the early concept that later became the Management Data Warehouse in SQL Server 2008 which frankly is way cool.

Anyway - this really cool download helps you find bad queries, check waitstats, anyway - here is the overview from the download page....

Overview

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention
 
 

Remember its SP2 of SQL Server 2005 you need.

Say you have a table and that table has a secondary history table so you can do windowing, for instance the settings on a specifc entity like the date when we started retaining fees on a debtor account say.

Ordinarily most people would resort to a large series of OR's to check the inserted and deleted tables to see what has changed.

Instead, you can use GROUP BY, DISTINCT and HashBytes to work out what rows (what key) has changed and actually requires sorting (updating the end date on the base table inserting a new row with the new settings).

The trigger below only shows the SELECT statement that gives you the complete logic I'm talking about, of course - this is test only - you should no longer be putting SELECT's in triggers but it highlights the behaviour...

create table some_base_table (

 

    batch_id        int not null primary key clustered,

 

    batch_name      varchar(250) not null,

    some_parm       int null,

    some_parm2      int null

)

 

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 1, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 2, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 3, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 4, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 5, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 6, 'hello fred', 1, 2 )

 

create trigger trg_some_base_table_check_for_changes on some_base_table for update

as

begin

 

    select batch_id

    from (

        select distinct batch_id, hash_combined = hashbytes( 'sha1', combined )

        from (  select batch_id,

                       combined =(  select batch_id, batch_name, some_parm, some_parm2

                                    from deleted c       --  need old values

                                    where c.batch_id = d.batch_id

                                    for xml path( '' ) )

                from deleted d

                union all

                select batch_id,

                       combined =(  select batch_id, batch_name, some_parm, some_parm2

                                    from some_base_table c       --  need current values (could use inserted here)

                                    where c.batch_id = d.batch_id

                                    for xml path( '' ) )

                from deleted d

            ) as r

        ) as c

    group by batch_id

    having count(*) > 1

 

end

go

 

 

--  test

 

update some_base_table

    set batch_name = lower( batch_name )

where batch_id = 2


 

update some_base_table

    set batch_name = lower( batch_name )

Update 20091022:

HashBytes only takes 8000 bytes so if you have a long row with lots of column you break this and get a binary truncation error; so - instead of using HashBytes use CHECKSUM like discussed earlier in the comments, CHECKSUM works across the varchar(max) the for xml gives you. The important thing here is the use of FOR XML to get around the incompatible data type problem.

 

 

Lots of really useful information: http://support.microsoft.com/kb/2001270/en-us

A must read for anybody with or considering using database mirroring.

Tony.

I had an email today wanting a solution to a row concatenation problem, for those who already know where this is going - yes, its FOR XML, anyway....

Consider the data source:

PRODUCT

PROCESS

TOP

BOTTOM

RIGHT

LEFT

 

 

 

 

 

 

GLASS 1

CUTTING

 

 

 

 

GLASS 1

BEVELLING

 

YES

YES

 

GLASS 1

GRINDING

YES

YES

 

 

GLASS 1

POLISHING

 

 

YES

YES

 

 

 

 

 

 

GLASS 2

CUTTING

 

 

 

 

GLASS 2

BEVELLING

 

YES

YES

 

GLASS 2

MILTERING

 

 

 

YES

GLASS 2

SAND BLASTING

 

YES

 

 

 

 

 

 

 

 

GLASS 3

CUTTING

 

 

 

 

GLASS 3

POLISHING

 

 

YES

YES

GLASS 3

TEMPERING

 

 

 

 

You want the result:

PRODUCT

PROCESS

 

 

GLASS1

CUTTING/   BEVELLING (B,R)/   GRINDING (T,B)/   POLISHING (R,L) 

GLASS2

CUTTING/   BEVELLING (B,R)/   MILTERING (L)/   SAND BLASTING (B) 

GLASS3

CUTTING/   POLISHING (R,L)/   TEMPERING

The FOR XML extension is the perfect tool for the job...

declare @input table (

    Item     varchar(50) not null,

    Process  varchar(50) not null,

    P_Top    char(1) null,

    P_Bottom char(1) null,

    P_Right  char(1) null,

    P_Left   char(1) null

)

 

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Blah', 'P1', null, null, null, null )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Blah', 'P2', null, 'Y', 'Y', null )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Blah', 'P3', 'Y', 'Y', null, null )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Blah', 'P4', null, null, 'Y', 'Y' )

   

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Humbug', 'P1', null, null, null, null )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Humbug', 'P2', null, 'Y', 'Y', null )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Humbug', 'P3', 'Y', null, null, 'Y' )

insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )

    values( 'Humbug', 'P4', null, null, 'Y', 'Y' )

   

select *

from @input

 

select r.Item,

       Concatenated = substring( replace( replace( r.Concatenated, ' ()', '' ), '(,', '(' ), 3, 4096 )

from (

    select r.Item,

           Concatenated = ( select '/ ' + Process + ' ('

                                        + case when P_Top    = 'Y' then ',T' else '' end

                                        + case when P_Bottom = 'Y' then ',B' else '' end

                                        + case when P_Right  = 'Y' then ',R' else '' end

                                        + case when P_Left   = 'Y' then ',L' else '' end + ')' as [text()]

                            from @input i

                            where i.Item = r.Item

                            for xml path( '' ) )

    from (

        select Item

        from @input

        group by Item

            ) as r

    ) as r

           

 

See attached for the deck and scripts, any questions or you want to talk further just email me @ tonyrogerson@torver.net.

For the event notifications stuff see Martin Bell's blog: http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/22/Event-Notifications.aspx

 

Don't forget tomorrow night is the first Cardiff based user group meeting where I will present on Ensuring Business Continuity (Backups, Mirroring and Monitoring) and Allan Mitchell will do SQL 2008 Integration Services - some of the things that make it better, details: http://sqlserverfaq.com?eid=198.

Also tomorrow night there is an Edinburgh user group meeting where Rob Carrol will present on "Your own Performance Analysis Toolkit" and Martin Bell will do an "Introduction to Powershell", details:
http://sqlserverfaq.com?eid=202.

Mark Whitehorn is organising a MSc in Business Intelligence - a 1 year full time or 2 year part time course at the Dundee university, for more information check my
blog about it.

There is a permanent DBA job going at the Old Mutual Asset Managers (
see details), for more information just email me Tony Rogerson and I'll give you the details - no agencies though!

I posted the decks and examples from my talk at the SQL Server Data Management Conference last Tuesday -
click for the link.

Allan Mitchell and myself will be starting weekly LiveMeetings again from the end of this month - I'll get the details and abstracts on the site later in the week.

Next week we have a Reading meeting on Wednesday at TVP which will have a developer/dba focus (agenda will be confirmed this week); also we have a Manchester user group meeting on the Thursday which will be a Reporting Services evening.

If you don't want these emails just reply to me with remove in the subject.

Many thanks

Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com
http://sqlblogcasts.com/blogs/tonyrogerson

I was talking with Mark Whitehorn at dinner after last Tuesdays Microsoft Data Management Conference and we got talking about the MSc in Business Intelligence he's setting up through Dundee University.

Looks really interesting and something I'm going sign up for today, just hope they'll take my 23 years of experience instead of a degree (I come from the school of life) starting in IT at 16, serving an apprenticeship and working my way up the ranks.

Anyway details below; if you are interested then email Mark directly: markwhitehorn@computing.dundee.ac.uk

http://www.dundee.ac.uk/postgraduate/courses/business_intelligence_msc.htm

http://www.computing.dundee.ac.uk/study/postgrad/coursedetails.asp?13

The MSc comprises six modules:

1 BI Systems – introduction and overview
2 Relational database design
3 Dimensional database design
4 Designing BI systems
5 ETL (Extract, Transform and Load) – Theory and practice
6 MDX (Multi-Dimensional BLOCKED EXPRESSION


1 BI Systems – introduction and overview
Why do we need BI systems?
Background and history of their development
Characters, data, information, knowledge, wisdom
Turning data into information
Why this is so important
Two main problems
Data is physically dispersed
Bring it together with a data warehouse
Data is not information
Turn it into information

2 Relational database design
User, logical, physical models of transactional systems
Primary keys, foreign keys and joins
Indexing
Implementation

3 Dimensional database design
Turning analytical requirements into a data model
User, logical, physical models of analytical systems
Surrogate keys
Slowly changing dimensions – types 0-3
Degenerate dimensions
Data mining

4 Designing BI systems
Kimball vs. Inmon
Teradata vs. Microsoft
Choosing the initial projects
Sponsor
Politics
The role of the BI architect
Good interface design

5 ETL – Theory and Practice
Systems of record
The meaning of the data
Bulk insert
Pipeline vs. Staging

6 MDX
The language of analysis
SQL vs MDX
Queries vs. Data manipulation
MDX expressions
Tuples vs. Sets

That's the 11th consecutive SQL Server MVP award from Microsoft for my work within the SQL Server community.

I love it; award aside - I just love doing what I do, ok, if I didn't do the user group, presenting, helping folk and blogging I may actually have a life but doing all those things helps keep my skills current and my skill set and problem solving ability nice and sharp.

Tony.