I have noticed that there are a lot of IT People are guitarists out there.  It might even be a British standard as this shot was taken at a user group conference run by Entropy, a part of the British Standards Institute. Entropy is a suite of software built around health and safety compliance, risk management, environmental protection etc. and these guys are two of the developers.  One of the management team had a hit with Neanderthal Man with the Hotlegs (forerunners of 10cc).

They are not alone.  My mate Michael embarrassed me a couple of years ago in Selfridges by picking up a guitar I was looking to buy and laying into an impromptu "I saw her standing there".  The staff and customers were clapping and cheering which was a lot different than the thanks he got from all the support calls he used to resolve. 

So if you want to make a name as an IT Pro maybe it's time to work on those riffs.

DTI Survey 1000 selected companies every year about a wide range of topics. One interesting statistic from this is that 44% have no plan for business continuity and only 14% consider they have a robust plan in place. The survey also found that the No 1 cause of business continuity failure is of course IT with 22% of those surveyed affected by this in 2006. The horsemen of the apocalypse; flood, famine, disease, war don't get a look and even fire comes in at no 24 in the list.

In response to this the British Standards Institute (BSI) have introduced a business continuity standard, BSI 25999. Part of this is a specification (BSI 25999-2) out in November which will be the basis for certification to the standard.

Major businesses are looking to adopt this standard quickly and will in turn mandate that their suppliers have this standard to ensure that their business doesn't fail because of a third party on whom they rely.

So before your organisation contributes to the worsening of these statistics, make sure you can recover from your backups be  it from SQL Server, Exchange or indeed the file system.  If you need help with this then check out Steve Lamb's presentation on Data Protection Manager at the next round of TechNet road-shows.

For general information about the standard you can also contact Julian Thrussell at BSI.

Reporting Services can be a bit of a pain to configure so here's a top tip from Saleem Hakani who often sees this issue with his customers:

Did you know that when SQL Server service account is configured with a Windows NT account, SQL Server sets various Windows user rights and permissions on several files, folders, and several registry keys. However, when you use SERVICES from CONTROL PANEL to set SQL Server Service account these permissions are not set and you may run into serious issues due to lack of proper security on SQL Server files, folders, registry keys & Windows user rights.

Therefore, it’s strongly recommended that you use “SQL Server Configuration Manager” and not SERVICES from the CONTROL PANEL when changing SQL Server or SQL Server Agent service account.

However, if you’ve already made changes to the account using SERVICES from CONTROL PANEL then you may want to follow below steps to fix this:

Step1: Apply FULL Permissions on the following registry keys and its subkeys.

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>

Step2: Set Full Control for the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) on these NTFS folders.

• <Drive>:\Program Files\Microsoft SQL Server\<MSSQL.1>\MSSQL

However, instead of making these manual changes it’s recommended that you use SQL Server Configuration Manager for making changes to SQL Server/Agent service accounts.

Microsoft has launched it's own SQL Community site, which will be a one stop shop for our product teams, premier field engineers etc. to share their knowledge with IT Professionals.  There's shed loads of good stuff here from training, to forums, articles and scripts. 

I should just mention that this is not to be confused with the equally excellent UK SQL Community site run by MVP Tony Rogerson.  This is the place to go for all the UK community events like the recent SQL Bits day as well as all the experience the UK MVPs have.

While we are all eagerly awaiting the latest CTP of SQL Server, it might be good to check out this clinic :

https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=128041

oh and it's free by the way!

Into the Valley of Thames rode the three sixty ...

  I get to see all the statistics on how popular SQL Server is but the SQL Bits event on Saturday bought it home to me.

Over 360 intrepid developers and IT Professionals had given up their Saturday to get the inside track on how to work with SQL Server from the SQL community leaders. One guy in my session had come up from Cornwall the night before and I'm sure there were others who could beat this. 

Many people had to stand at some of the sessions which made all the effort put in by the MVP's worth while,  especially as some of them seemed to be distracted by some rugby match during the afternoon.

For those that missed this do watch out for future events on the SQL community site as its real world stuff you won't find anywhere else.  For those that were there thanks again to Tony Rogerson, Martin Bell, Chris Webb and Simon Sabin for organising it and to all the speakers and sponsors.

At the SQL Server community evening last week I was asked whether SQL Server is or is going to be SOX compliant.  There are probably some of you thinking that we were discussing baseball so I should explain that Sarbannes Oxley (SOX) is a piece of financial compliance  legislation introduced in the US following the Enron and Worldcom scandals.

SOX has no specific requirements regarding audit.  Interpretation of SOX and applying that to the processes and people in a particular business typically result in the need to put access controls in place and demonstrate who can access what, and what access has been exercised.  The existing audit capabilities in SQL 2005 are fully capable of supporting such usage.  

Of course things get easier in SQL Server 2008 as there is a much more sophisticated audit capability to track changes to permissions and policies.  Change data capture can be enabled to record changes to key tables.  Finally it is very easy to develop reporting services reports on top of these to provide whatever the auditors and regulatory bodies need.

There is also an example you can reference about this here around Credit Suisse and SQL Server 2005.

Many of you will know that James is a keen photographer, and one of the many reasons for this is that he hates having his picture taken.  In Technet for example his 300 x 300 image was probably shot from  2miles away by a freelance paparazzi from News of the World

This exclusive photograph shows James trying out o(wl)-mail at a recent event to ping Harry Potter about the recent success of Halo 3. 

The big advantage of o-mail over e-mail

is that it's almost impossible to accidentally select "reply to all".

James has now noticed I exist.  I have also noticed he has a post about What You Measure is What You Get and this neatly links into to my post on balanced scorecard theory i.e. If you only focus on one outcome you will achieve that but it is often at the expense of other equally important things. 

Applying this to another topical Microsoft offering, Halo 3, we get some interesting usage stats and no doubt there will be uproar that the kill count is a on there and more uproar that this is currently standing at over 1.6 billion!

Of course there isn't a lot else to do in Halo 3 but this is only one indicator of success and taken on it's own it could mean the game is too easy which mean that players get bored and go elsewhere.  The other indicators show that people are still on line after the initial launch and this is good for continued success of the game.

Money is one of the most popular indicators. Much has been made in the press about how Halo 3 revenues compare to Spiderman3 or Harry Potter so that's good too. 

It's also important that the X-Box is doing as well as this will underpin future success.  The indicators here are the add-ons that are bought and the average number of games per console sold.

So by looking beyond the body count we can see that all looks well for the Master Chief.

My colleague James has been hassling me for not posting anything of use to him...

A long time ago in a land far far away there was a cunning plan to develop a new file system around a database called WinFS.  This created quite a lot of positive reaction and there was a lot of flak when the project was taken out of Windows server 2008.  I am not going to argue the rights and wrongs of the approach or the decision to abandon it, but in my opinion that capability is alive and well and inside SQL server 2008. 

One of the goals of SQL server 2008 is "beyond relational".  This will show up in a number of the features:

• The filestream data type will allow any file to be stored in a filegroup like the filegroups used to hold relational data but also to treat the insert, update etc. as a normal transaction i.e. with commit and rollback.  However the file can be streamed quickly into an application as it will be in the file system and the current limit of 2Gb for varbinary(max) will no longer apply.
• The Spatial data type will allow geographic and location data to be stored
• Full text search has been overhauled
• Hierarchy ID allow the relational database to understand relationships between rows in the same table far better than a parent child relationship.
• Encryption will allow all of this data to be secured.

Add to this the access privileges that work in much the same way and inter-operate with active directory and the the fact that SQL server is behind SharePoint.

So SQL Server 2008 - a file system within an operating system. 

A big thanks to all the IT professionals out who spotted my poor English in the 27th Sep Technet flash from George and in this post.  Intelli-sense is still no substitute for intelligence, but I do quite like the idea of Illicit feedback rather than elicit feedback!

I also got an e-mail questioning whether end user reporting ever works in practice which included this paragraph-

"Trying to empower the users often backfires. The IT department ends up writing tons of SQL Views, queries, new tables, jobs to populate these tables, all in an attempt to present the data to the users in a format they can understand and work with. Add to that the cost of training the users. And they inevitably turn around and ask for something that isn't covered by the view/query/table. Until eventually everyone realises it was much more efficient and less error-prone just to send the report request to the IT department in the first place."

In my opinion this is certainly true in some organisations and comes back to one of my posts about being ready for business intelligence and the relationship between the IT department and the business.  There are many organisations where end user reporting is alive and well and this takes on several forms:

1. The users are given a tool such Business Objects or Cognos and the IT departments are involved by creating a semantic layer between the data and the tool.
2. The users are given raw access to the source data and connect to it from Excel or Access using MS Query.  Hopefully the IT department are aware of this and limit access to what they can do to avoid runaway queries etc.
3. The users get hold of sets of data from fixed reports or extracts provided by IT and then do whatever they like with this.  An extreme example I am aware of is where Business Objects reports are saved to Excel and then used to load up SAS statistical models so the reports have essentially turned into ETL scripts.

It is option 1 where I see the new report designer preview tool in SQL server 2008 being used provided that the data is sourced from a report model designed by the IT department.  I would totally agree that if a tool requires the author to write SQL, or MDX then the tool  is not an end user tool. 

In my view the partnership between the business and IT should manifest itself in a reporting environment such that:

• IT describe the data available, who accesses it and how.
• The business is free to concentrate on the presentation of content to support monitoring and analysis. 

Quoting Dale Vile's research in my last post - Information is a right not a privilege.  Feel free to return fire on this.

I got to present to bunch of BI partners yesterday with Shailan Chadusama.  Before our slot, There was a presentation on the state of the BI industry by Dale Vile from Freeform Dynamics and you can also see a webcast of it here.  This was based on  research he had done with the audience of Register users.

Two things caught my attention.  BI is still the number one priority for IT spend in a business and this slide:

One of my goals on this blog is to try and clear up some of the confusion.  I also think the same sort confusion and misunderstanding exists inside the IT Pro community because BI is very incestuous and other IT Pros are focused on the web, security, comms or whatever.

So hopefully the posts are helping but like any guitar hero I need feedback! 

In case you weren't at the excellent SQL Community yesterday evening, I thought I would share a top tip for getting on top of your fact tables in the next version of SQL Server.  Mark Hill of Edenbrook gave the evening an update of the relational engine as it affect BI and we then got into a debate about the merits of the new compression feature.  My understanding is that is particularly effective against fact tables and can improve performance by reducing I/O albeit at the expense of CPU usage.  So Sanjay Nayyar from IM Group pointed out that you would get even greater benefit from use of the Vardecimal storage format as there will be many numbers in a typical fact table that will not use the designated number of bytes and this will have no CPU impact.  This feature came out as part of SQL server sp2 and of course Sanjay's absolutely right. doh!

So as ever there are lots of choices and you need to consider what works for a particular problem.  if you haven't got time to do that then get along to the SQL community as someone there will know the score and you can have a beer and pizza while you learn.

Thanks again for a really successful evening to:

• MC and MVP Chris Webb on organisation
• Mark Hill for his hard work on explaining MERGE and Change Data Capture  
• IM Group for use of their offices and a quality introduction on Data Mining from Suranjan Som, and I hope he gets 5 minutes to post about it that would be great!

Flying as I mentioned in my last post is a difficult business,  If you have ever seen the flight deck of a modern aircraft it's a daunting array of instruments and switches with no obvious sense to them.  The screen grab above is just from a little light aircraft and even this looks confusing.  However an experienced pilot will concentrate on just six instruments and I have magnified and numbered these :

1. How fast you are going (airspeed indicator)
2. The orientaton of the plane in the sky (artificial horizon)
3. How high you are (altimeter)
4. How the plane is turning (bank indicator). This is how a pilot can turn the plane tightly while you don't notice and your coffee doesn't end up over the next passenger.
5. Which direction you are going (compass)
6. Whether you are going up or down (vertical speed indicator)

Notice too that they are right under the pilots nose and are grouped together.

It's a 70's disaster film, the pilots have had the fish and are unconscious.  You had the veggie option and are in the cockpit.  Looking at all the dials is just going to give you a headache.  Concentrating on just one dial, say maintianing altitude looks like a good idea, but the plane may be upside down and going around in circles.  So you need to keep watching all six and occasionally check other things like fuel and the radio.

Running a successful business is also difficult.  There is lots of information and stats available and like flying it can very hard to know what to focus on.  Not being focused and focusing on one thing are as bad as each other.  Lack of focus often equates to concentrating on lots of detail and this either impossible or can be seen as micro-managing by subordinates.  Focusing one thing like the balance sheet is also going to lead to problems in the future perhaps because all the best staff have left in search of better pay and conditions.  

So just like flying a balance has to be struck and each business to work out what is important to it, for example:

• Winning new customers
• Keep existing customers satisfied
• Grow the Business
• Be Innovative
• Keep the employees happy

The idea of a balanced approach to managing a business was developed by  Prof. Robert Kaplan  and  Dr. David Norton in a theory called the balanced scorecard.  It is in wide use throughout the industry and I have implemented several BI solutions around this theory.  Many BI vendors have toolkits to support this and you won't be surprised that Microsoft have had a product in this space for several years called the Business Scorecard Manager.  However a tool like this is only part of the story.  A good dashboard will show where the big issues are and give some indication of where the problems lie, but deeper analysis may be needed and then plans put in place to address the issues. 

There won't be another version of Balanced Scorecard Manager as is it is now going to be rolled up into the New Microsoft Office Performance Point Server (MOPPS).  MOPPS combines The Business Scorecard Manager with the analytics capabilities of ProClarity together with a new forecasting and planning engine so that diagnosis and analysis can lead to planning and action. 

MOPPS is being officially launched in London on 16th October with a keynote speech by that same Prof. Robert Kaplan and if you want to find out how all this is going to work from source register for the launch event here.

Reading the Metro yesterday, I noticed a short article about virtual flying using Google and this reminded me that Juliet, my wife, has told me to take-off.   She bought me a flying lesson at Abbas Air in Compton Abbas as I have been an armchair pilot for the last ten years using good old Microsoft Flight Simulator.

The latest version of Flight Simulator (FSX) has just about every place you can land in the world in including Compton Abbas, a bit like virtual earth except you fly over it in a plane. I set the current airport to Copton Abbasin FSX, but unfortunately the plane I was to fly , the Piper Warrior , isn' t included in FSX out of the box, so I bought one of the hundreds of third party add-ins, Flying Club X, with this included. 

After a few weeks of practice using FSX It's time to see how realistic the simulation is and off we go to darkest Dorset.   My instructor, Simon Schwarz, introduces himself and explains the agenada for the day. He also asks Juliet to join us, as there are seats in the back, so her cunning plan to claim on the inheritance wasn't going to work.  My virtual flying paid off  - Simon is impressed that I know the key instruments and basic flight theory and as soon as he takes off he tells me "You have control", and I do, sort of.

This is where 'real world' is a bit of an understatement.  In FSX the screen does move around a bit as you're 'flying', depending on how you have configured the weather and the controls did behave exactly as I expected. However in a plane doing 90 mph in a 30mph wind under a cloud full of turbulence it is the world that doesn't move but you certainly do!    

I have to say I thoroughly enjoyed it, but I suspect in future it will be Juliet who will be doing the armchair flying as she was a very odd colour when we landed. 

PS for those who are serious mouse pilots sp1 of FSX  came out back in May and is now available here.

In my opinion the weak link in reporting services is that there is no tool for information workers to design their own reports and so IT professioanls have to spend a lot of time doing it for them.  I can see there is a place for these reports:

• They will be accurate or at least give the user what they asked for but not necessarily what they want. 
• They will leverage all of the cool features in the tool such as document map, linked reports and actions to other tools.  But there is often a long gestation period for reports written in that way as the users insist on the correct formatting and layout and usually 'refine' the spec in the process.

Reporting Services in its 2005 release does have a lightweight tool for end user reporting, called Report Builder and I have implemented this for a very simple application.  The great thing about it is that relies on a Report Model, which is a bit like a unified dimensional model in analysis services in that it insulates the end user from the underlying relationships in the source database and has derived calculations in. A Report Model can either be built from the ground up against a relational source in the BI Development Studio (BIDS) or be generated from a cube in Report Manager by creating a data source to the cube and then selecting  generate model.  Once a Report Model is deployed to Report Manager the user can then immediately create their own reports.

However this tool is far too simplistic and there are no plans to enhance it in SQL server 2008.  Instead there is a new tool called Report Designer Preview (Name not determined yet).  It's included in CTP 4, which gives an idea of how it will turn out ,but at this stage it is severely handicapped:

• It only connects to a SQL Server database. 
• It has got no help and some of the menu options are not populated.

Hence the Preview, it is not supposed to be fully formed and this has been done to illicit feedback from those of you adventurous enough to try it.  For me what is really important about it are three killer features two of which you can see in the CTP and one you can't! 

Firstly It's a standalone install (not a smart client like report builder) and business users don't have to battle with Visual Studio. 

Then there is tablix. Although it looks from the tool that you still only get table and matrix reports in fact there is only one tool the cunningly named tablix (table + matrix) this gives the benefits of both as you can see from this:

So data can be show in one grid from two dimensions across the dimension they have in common and that is only one of the problems that can be cracked using this tool.  Note that you have to use the report designer preview as the designer inside BIDS hasn't changed to be tablix aware. 

The bit I can't show you is how this will help users and that's because in CTP4 the tool can only source data from SQL server and that means writing a query which isn't good for the business user. I understand that the plan is allow the tool to use report models, as well as source data from cubes and the usual raft of data providers that reporting services currently supports. 

So serious end user reporting will be available in SQL server 2008 reporting services.  Which means that while the users design their own reports we'll have a little bit more time to keep up with all the other new stuff in this release.

My post from yesterday got a comment which I sort of agree with along the lines of "My mate Nigel is Microsoft Certified and he is hopeless".  I think that this perception is largely based on the sort of exams that used to exist and I personally think they are a lot harder now.  They are also better aligned to the real world so as a Business Inelligence bloke I can do a qualification just in this area that does reflect the experience I have as well as my knowledge of SQL Server. 

This leads on to the next obvious queston - why bother doing them?  I find it helps me to learn some of the darker corners of the technologies that I don't know.  It also forces me to pick up the new stuff as the exams track the release of the products.  In short I use it to track my competence in a technology for my own benefit.

The official  Microsoft tag line for the certification program is "How They Know You Know", but for me  it's all about confidence in your ability. I have never been offered a job or a salary increase as a result of being an MCDBA.  I would like to see more recognition for the people who have taken the time to get certified, both from Microsoft and the IT industry.

However if the qualification is seen as a route to a better job then it will attract the exam crammer who is sees this as a shortcut and I am not sure what could be done to make the exams more like the real world, short of putting a candidate in front of a broken database, server or application and asking them to fix it!

I am not one but I would like to be.  I do have the MCDBA qualification because in the days of SQL server 2000 that was the only microsoft qualification in my specialism which had an albeit optional  BI element to it. However I never upgraded to the new IT Professioanl exmas for 2 reasons: 

• It took far too long for the new IT Professonal BI exams to appear after the launch of SQL server 2005
• I really hate exams and I did manage to fail one of the four by a gnat's wing.

However you can always find reasons for not doing things so I am getting with the program and swotting up in my spare time.  One helpful thing I did notice if you are also doing this was that there is a double jeopardy offer called Second Shot in force so that if you have a moment of muppetry in the exam you get another go for free.  Eileen has already got her post out there on this (and she has a list of qualifications as long as your arm) but to summarise:

This is a limited offer so plan now!  If you register for the Second Shot offer starting September 15, 2007, prior to taking any exam, you will receive a free Second Shot exam if you don't pass on your first try.

Here's how it works:

• Step 1: Register for Second Shot on the Microsoft site and receive an exam voucher number.
• Step 2: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site, call center  or test center locations.  (You must have the voucher number available prior to registering.)
• Step 3: Take your exam.
• Step 4: If you fail, register for your free retake exam via Prometric's web site, call center or test center locations using the same voucher number. NOTE:  Please wait one day after the failed exam to register to allow for test results to be entered into the system.

Like all good offers it won't be around forever, and with the nights drawing in you'll have something to do after work.

When you build a cube in analysis services it is very easy to expose every attribute in every dimension to the user.  You can also add-in every measure and if you don't feel that's enough then you can create your own calculated members to add to the users' fun. 

Once you have given the users every thing they asked for you can now sit back and relax just like on the IT crowd.

I would say that at this stage the work is less than half done.  A raw cube is like your digital music collection before you've tagged it.  It's all in there but you can't find anything.  What your users need even, if they don't tell you, is some direction and guidance to make sense of the monster you have created.

There are several things you can do in the cube to help with this:

• Use security to limit what users are allowed to use.  Security can be used to hide attributes and measures and can also limit the users to certain slices of a cube such as only allowing a regional manager to see data for their region.
• Create a perspective for each type of users.  Perspectives are another tab on the cube designer in BI Develpment Studio (BIDS).  Here you can limit which attributes and measures are available from the base cube in as in the screngrab below where I have created a perspecive called Internet which won't have the Reseller Sales measure group in.  Perspectives are not a security device and all perspectives are available to users who have access to the underlying cube and they respect the security of the cube on which they are built. 
• Organise your attributes and measures into display folders.  Display folders are a property of attributes and can also be applied to base measures and calculated memebers.  To do this for calculated members to select the associated measure group or display folder for them:

The other really big thing you can do to help your users is document what you have created.   This needs ot be done in two ways:

• You need a technical document to maintain and adapt the cube.  If your cube is successful you will be asked to change it as the users get more sophisticated and to meet new business priorities.  BI Documenter is a good tool for this as you can save the output as html and post it for all to see. Check out the trial and if you like buy it.
• More importantly you need to define and catalogue what is in the cube in a way that the users can understand.  An effective way to do this is to get them to do it for you by designing a standard for defining the layout of requirements documents so that these can be tagged and referred to from the end user tool.  Get back to me if this is of interest and I'll go into detail in another post.

To conclude business intelligence is there to make sense of the data in an organisation and so writing coool MDX and buying a stonking 16 way server is the easy bit. The hard bit is encouraging them to use it and to get the best out of it.

I got asked a very odd question today - "How do you use virtual cubes in Analaysis Services 2005?". I was momentarily speechless (a rare thing for those that know me!). This got me to thinking that there must be quite a few installations of the previous version analysis services 2000 (AS2000) out there run by IT professionals who may have had a look at the current version and gone wibble wibble. 

In AS2000 each database comprised a set of dimensions and cubes with each cube centred around a fact table.  Suppose we have two fact tables reseller sales and internet sales. Each of these has different dimensions related to it for example resellers sales has a foreign key to the reseller dimension which is not appropriate for intenret sales.  In AS2000 you could do two things:

• Create a view in SQL to union the two fact tables.  You would need to ensure the resulting view could be inner joined to all the releavnt dimensions or data would be lost when the cube was built as AS 2000 uses inner joins to get data.
• Build a cube based on each fact table and then build a virtual cube over the two cubes and bring in all of the dimensiosn and measures from both.  You would then have to write calculated members to combine the measures and possibly hide the source measures.

 This worked but was tricky to set up particularly if the two fact tables were aggregated at different levels.  For example think of sales v budgets.  A sale would have the day of the sale where targets  might only be set at the quarter level so you need to aggregate daily sales to the quarter level before comparing them to the budget to determine variance.

SSAS changed all that, possibly a copy of Ralph Kimball's Data Warehouse Lifecycle Toolkit materialised though a wormhole in Redmond, opened at page 271, and somebody thought that'll do nicely and so now multiple fact tables are combined in a single cube where each fact table becomes a measure group as you can see from the screengrab below.  The orange arrows shows where the date in reseller sales is at the level of date(day) whereas the sales targets are only at the level of quarter.

 So there are only 2 cubes in the adventure works sample and one of those is for mining whereas there were half a dozen in the old and less complicated Northwind sample in 2000.  Of course this means that there are tons of attributes and measures in an analysis services 2005 so the danger here is that the user gets overwhelmed wiht all the stuff in the cube where before a cube was more tightly focused on a particular function. 

To reslove this we can use perspectives and security to direct the user to their usefulk stuff and we'll look at that next.

It's coming up to London Fashion Week so I thought a post on models for all you dedicated followers would be good.  The models in this post are rarely size zero ! So what is one of these?  Technorati isn't much help but if you look hard you will find some posts e.g. by one of my predecessors Mat Stephen and one by   Mark Frawley.  So I thought a short post on what it is might be useful.

The UDM is at the core of analysis services.  It is the semantic layer between the source data and the end user experience irrespecive of the source.  The design surface of the UDM allows you to use any relational data to build a data source view.  The objects in this view can be tables or views in the source or a query over the top of these.  Thus you can create a sort of virtual data warehouse over an operational system.  This flies in the face of alot of accepted BI practice but Micrsosoft arealways providing choices and leaving it up to the user to decide what  is best for them.  

If we look at good old adventure works data source view using the BI Development studio (BIDS)

We can see it looks like an early design for an intel CPU.  Zoom in and each box is a query table or view.  The calculations for each of the derived attributes can be accessed from a right click and here we can see that the UDM has stored a SQL snippet to derive the CalendarQuarterDesc attribute.  A good tip is to create a separate view for each fact table and you will see that this has been done in the other views in adventure works.  Once of you have got this right you are ready to build dimensions, and cubes.  So not that hard after all and this is pretty much the same in analysis services 2008 form what I have seen so far. 

One of the most over used phrases in Business Intelligence is "one version of the truth" so I thought it would be good to discuss why it's important and what IT professionals can do to achieve it.  In an imaginary organisation like adventure works there would be several line of business systems such as a classic sales order processing system to track sales to resellers plus a shiny new web site for internet sales, while in the back office there would by supply chain management, finance and HR systems. 

It's Monday morning and the heads of department have their weekly meeting to review sales and operations. Going round the table:

• The reseller sales manager is delighted because one of the resellers has just placed a big order for more stock to cope with their branch expansion program and he is well on target to meet his bonus. 
• The internet sales manager is worried that although sales are up he is not on target.
•  The finance director is very worried that receipts are just not coming in and there is a serious cash flow problem.  In fact he has a raft of figures to show that sales are down compared with last year
• The managing director is confused

So what is going on here?  There is no big consistent picture of how the company is performing.  The sales manager is measuring his performance on the date a sale was made while the finance director only counts a sale when the invoice is paid.  Another issue might be that they all get their information from a different system at a different time and because a lot of systems have overnight batch jobs to post data the view of the data could be very different on last thing Friday night to first thing Monday morning.

This is where a data warehouse can be useful.  It will be designed to collect data form different systems at agreed times to give a  consistent set of snapshots of the business from which all reports can be sourced.  So that would be the one version of the truth sorted out then?

Yes and no.  Yes it is consistent and accurate (a big assumption and a book in it's own right) , but it can still be distorted by the tools that access the date warehouse.  Any front end tool that connects to the data warehouse will have additional business logic in. For example a report will be based on a query with logic in it and may have calculated fields that are derived from that query . 

If the business is writing reports, than each department could create it's own version of a report and these could be inaccurate become so by not being kept up to date.  If the IT department writes the reports then they will understand the data and have the tools to test the report is accurate and reflect what the user wants, which is why Reporting services at the moment doesn't have a good tool for end user reporting.    However there are still problems here:

• The IT department is always seen as taking too long to deliver so there are often ad hoc solutions in the more IT aware departments
• It is difficult to track dependencies and calculations in them. Some IT Pros try to resolve this using stored procedures for the source of all reports and keep all the logic in a database.  However these can be difficult to read and often a developer will copy an existing procedure for their report and adapt it. So now the same calculations can appear in multiple procedures which makes maintenance a nightmare and puts the one version of the truth in jeopardy.

So what else can be done to crack this problem?  In my opinion you need a semantic layer between the data warehouse and the reporting tool.  This will contain several vital features for consistent reporting:

• Joins between tables are described.
• Things get named properly for every one to understand.
• Hierarchies can be created to support relationships between attributes. Check my posts from last week for more on this. 
• Derived measures and attributes which cannot be held in a relational model can be described e.g. closing stock at period end, like for like sales, profit margin% etc.
• Fine grain security can be applied to limit what any user can see e.g. regional sales managers only see sales for their region and only HR can see measures related to salary and bonuses. 

The Microsoft approach is to stuff all of this into a Unified Dimensional Model (UDM) and that is the subject of my next post.    

In SQL server the Cast and Convert functions change one type of data to another.  A pod cast is the art of converting knowledge into sounds so that's the tenuous link for this post.  

Pod casting is not something I have ever tried, but the security expert on our team, Steve Lamb sees himself as the next Jeremy Paxman, so I ended up being his latest interview subject. 

His starter for ten was the usual introduction with bonus questions on analysis services and cubes. The session seems to be quite popular judging from the stats we get, so if you find my blog a bit difficult to read on your regular commute why not listen in either in  wma or mp3 format.

Just when you thought you had heard the last of hierarchies there's another post.  This one is a little different - it's about managing confrontation and arguments, not with the code in the SQL server CLR but with other humans!

In a hierarchy you navigate upwards to see information at a summary level e.g. from January to 2007 to all time, and from 1.9dcti ghia to Mondeo to Ford. How does this apply to real life? Imagine chatting to an imaginary Australian friend about the indifferent result of England in the rugby against the USA:

The annoying accurate jibe from the supremely confident antipodian 

"Mate, England won't stand a chance against us if they play like they did against the USA"

to which you reply

"Well, they're playing better than they did in their last six games"   

So rolling up negates the one bad performance or establishes a trend.  Another example can be found on radio 4 with John Humphrys having ago at some crime statistic to a Ministry of Justice official.  The spin would come back " Well John if we look at the overall figures I think we'll found that levels of crime have come down since our government has been office" .  The point here is that both of them are right; at a certain level of detail things could be really bad, but looking at the big picture the trend is improving. 

Back to business intelligence, what does this mean for us IT professionals?  We could argue that "It's not my problem" - We are just building the data for the business to explore.  But a more helpful approach is to show both the detail and the high level information but also to pick out the weakest links and exceptions.  The technique is called bubble-up exceptions and the easiest way to expose this ability to the users is simply to use the built in capabilities in ProClarity/ Microsoft Office Performance Point Server (MOPPS).  This does require that power users are trained to use this tool but they also have the considerable benefit of knowing what they are looking for.

I have heard SQL Server 2008 described as SQL Server 2005 sp4 implying that there isn't a significant amount of new features in the new release.  This perception has partly come from the Community Technology Preview (CTP) program has changed for the next release.  For SQL Server 2005 each CTP showed the latest state of play of every feature now matter what state it was in. That gave you all of you a good idea of the totally of the product, but the downside was the performance and reliability of these new features was very patchy.  That seemed to cause a lot of concern so the product team only add in fully fledged feature into a particular CTP.  For example, in CTP 4,  key changes were made to the reporting services architecture   and the Dundas charting engine was added in.  There is one partially developed feature and that is the report builder and the reason for this is for you to try the new tablix control (I'll cover that separately in another post).  The full list of the changes is in the what's new section of Books On-Line in each CTP. 

There have been tens of thousands of downloads of the various CTP builds from the Connect site, So there is a lot of interest in it.  What I want to find out is what you are actually doing with it, so please comment, e-mail me or phone.

If it's sitting on your hard drive and you would rather find out from the experts, then there are two upcoming events, run by the SQL Server Community:

On 26 Sep come along to IMG offices in Holborn to listen to what partners and MVP's are excited about in the new release. Register Here.

If that's not enough take out a Saturday to come to SQL Bits at the Microsoft campus in Reading where there will be a track on SQL Server 2008. Register Here.