A BarCamp is where about 100 web geeks spend all weekend together discussing what’s hot and what’s not, and the latest one has just finished at Ebay’s very cool Richmond offices.  The idea is to give a chat on any topic , for example I saw one was called “The Art of Stabbing People”  - had I made a mistake in turning up in a Microsoft t-shirt, I wondered.

My personal favourite was by Tom Whitwell of the Times OnLine  headline writing. He gave a number of top tips to get traffic and interest in what you are doing by being specific, and not being deliberately funny or clever.

My headline of  “Ask Microsoft (no stabbing allowed) ” got a little interest but was up against “css3” and “Bring your own cool gadgets”.  Other sessions where on web anonymity, sleeping with the enemy (given by a couple where each is a designer for a competing web company), and loads of web tech hack stuff..

The most controversial session for me was a discussion about pseudonymity i.e. anonymity on the web by improbulus.  This person is working as hard as possible to remain invisible (except of course by going to BarCamps!). I can accept the need to be really careful about how much you reveal about yourself, to protect your identity from theft and because there trolls and stalkers out there. But if you are commenting on other peoples opinions or work then surely you should answerable and identifiable in some way. Finally, none of the measures discussed will protect you from any concerted attack on your identity whether lawful or otherwise.

So a really great weekend, I wonder if anyone would come if we did one at our office?

I hope you are sitting down, and don’t have a coffee in your hand when you read this, but SQL Server 2008 released at 17:00 GMT today!

This is good news for all the people who have asked me about release dates as they can now start to plan and deploy in earnest. 

It’s good for me as I can now start building the demo’s I need to for the next round of TechNet road shows, TechEd IT Forum, and even a quick demo for when Steve Ballmer pops over in a couple of months. 

And it’s good news for you even if you aren’t one of the 500,000 people who downloaded the various betas and ctps, but in order for you to find out why then you could do worse than come to a SQL Server event this autumn such as:

• Microsoft SQL Event 10th September  in Reading (not posted on the events site so pencil it in for now).
• SQL Bits Cubed 13th September in Hatfield
• SQL Server Under New Management TechNet Road show Manchester in 7th October (register here) and Exeter 23rd October (register here) and in Manchester.

You might also want to download it and this can be done from the main SQL Server site. 

Also don’t forget to get the feature pack which should also be updated for release. This has all the important add-ins like the upgrade advisor, command line utilities and best practice policies. 

Small Business Server (SBS) is like flying a single engined aircraft.  There’s a lot of stuff going on but you only have one box to worry about and backup and interoperability are all a lot easier because of this.

Flying a twin engined aircraft is far more complicated than a single engined one; how come they don’t go round in circles when one engine doesn’t work? The new twin engined version of SBS is SBS 2008 Premium which adds power and flexibility to the base edition by adding a separate physical server to just run with Windows Server 2008 and SQL Server 2008 standard edition for you to run any line of business application, Microsoft or not, on top of that.  That power means the server is just a bit more complex to get working as it has to join the domain and security between this new server and the clients has to be configured.

Moving up the complexity there is going to be a completely new server offering for mid-sized businesses called Essential Business Server (EBS), this comes in a basic three server version and a four server Premium version again with a separate SQL Server 2008 standard edition installation:

The Management Server is the one that looks after the user accounts and from which you manage the other servers with a basic version of System Center. There is a copy of SQL Server 2005 Express on here for supporting System Center and you could use this for other small databases if you decide premium is not for you.

The Messaging Server does what it says on the tin and so basically runs Exchange.

The Security Server in the diagram above confusingly mentions Exchange as a component, but this is the gatekeeper for the whole system and so it has  the Exchange Server 2007 Edge Transport Role which provide for all the good stuff to stop spam and provides message hygeine.

EBS will provide the back office IT for a company of up to 250 employees and that premium edition so that you can extend EBS to run Office SharePoint (SharePoint Services is still part of Windows Server 2008), Dynamics, or a thrid party application like Sage.

At this point you are wondering what is all this doing on my blog.  It may well be a way small/medium businesses will get their first exposure to SQL Server and I wanted to help out with the launch of this as I am pretty impressed with how integrated everything is. To start this off I  will be running two TechNet events this autumn:

• 22 October 2008, Bristol: Taking Care of Business Every day with Small Business Server 2008 and Essential Business Server 2008
• 29 October 2008, London: Taking Care of Business Every day with Small Business Server 2008 and Essential Business Server 2008

If you can’t make either of those then two other top resources on EBS/SBS are:

• Blogs:
  • Dave Overton’s blog the SBS team blog and the one I have shamelessly linked to which is the EBS Team blog
• The click through demos on SBS (here) and EBS (here).

I have put Business Intelligence into a number of small businesses and these guys typically have one server set a side for BI, so how do you cram the database and the cubes into one box to get the best performance?

SQL Server databases and Analysis Services (SSAS) both love memory, and even windows server gets upset if you deprive it of more than certain amount so here’s some guidelines:

-

You can decide on different values based on your workload. Keep in mind that less memory will mean less pages in memory (more IO) on SQL and potentially less space for Analysis Services to cache queries. Processing will also take a hit depending on size since it copies the cube into memory for processing.

• SQL Server Database - Set SQL to use max 40% physical memory
• Windows - Set <LimitSystemFileCacheSizeMB> to 20% of total, 65022 MB * .20 = 13004 MB.  This limits Windows file system cache usage of memory.
• Analysis Services- Set SSAS to use max 35% memory, edit the msmdsrv.ini file (make backup first) and set <TotalMemoryLimit>35

Set <PreAllocate>35 (this means total percent of physical), to preallocate memory at server startup. This helps for NUMA enabled servers especially I found. More on SSAS properties can be found here and more tuning analysis Services can be found in this white paper.

If there are problems with runaway queries using too much memory, you can run ASGovernor to cancel queries if memory usage is greater than 40%.  (5% past target for SSAS)  It might be that this is too large and it needs to have a smaller threshold. 

I have seen some strange schemas in my time which look like a good idea on paper but not on disk. A common scenario is the schema that is created by a tool controlled by a user and so we end up with columns like user21 in usertabel7 and so on. Then I got an e-mail about the poor performance of user defined functions (UDFs) following Simon Sabin's talk on the CLR and how good they could be for replacing T-SQL UDF's at the SQL Community event last Thursday .

The database involved in the problem had several tables where generic columns where used for different purposes. Taking customer as an example there was a separate table CustomerStrings that looked like this:

CustomerID is foreign key to the main customer table and CustomerStringTypeID is another foreign key, this time to CustomerStringType which has an ID and a description, with values in the description such as  'Fax', 'Mobile', 'e-mail' etc.

So the CustomerString table would have multiple rows for the same customer for example a row for Fax, mobile, email and so on.  The good thing about this is that rows would only be created where a given attribute for a customer was used and so would be efficient on space.

Getting back all the e-mail addresses for a customer wouldn't be too bad either depending on how this was implemented and this is where the developer tried to implement a generic solution with a UDF:

CREATE FUNCTION dbo.GetCustomerString(@customerID int, @customerStringTypeID int) RETURNS varchar(50) AS BEGIN

DECLARE @returnValue varchar(50);

SELECT @returnValue = CustomerStrings.StringValue

FROM CustomerStrings

WHERE CustomerStrings.CustomerID = @customerID

AND CustomerStrings.CustomerStringTypeID = @customerStringTypeID

RETURN @returnValue

END;

The problem arises when this function is used in-line on multiple occasions to get three attributes for every customer:

SELECT

cst.LastName,

dbo.GetCustomerString(Customers.CustomerID, 1) AS Fax,

dbo.GetCustomerString(Customers.CustomerID, 2) AS mobile,

dbo.GetCustomerString(Customers.CustomerID, 3) AS email FROM dbo.Customers cst;

So the UDF gets called three times for every row in the customer table which is always going to be much slower than using a set based operation for example:

SELECT

cst.LastName

fax.StringValue as Fax

mob.StringValue as Mobile

eml.StringValue as email

FROM dbo.Customers cst

LEFT JOIN dbo.CustomerStrings fax ON cst.CustomerID = fax.CustomerID AND fax.CustomerStringTypeID =1

LEFT JOIN dbo.CustomerStrings mob ON cst.CustomerID = mob.CustomerID AND fax.CustomerStringTypeID =2

LEFT JOIN dbo.CustomerStrings fax ON cst.CustomerID = eml.CustomerID AND eml.CustomerStringTypeID =3;,

However maintaining all the code for the possible combinations of attributes required is a big headache for this company so how can they get the speed of the second query without writing mountains of code.  In my opinion the schema is the problem here.  I would either create sufficient columns in the customer table for all the attributes required or using another group of tables with one row per customer to store contact details etc. Of course if there loads of columns required (as in SharePoint) many of which are usually empty then it's time to have a look at Sparse columns in SQL Server 2008 and that is what I will be posting about next..

Encryption, Keys and certificates etc. are complicated  and confusing judging by the number of support calls coming in from DBA's.

As I said in my last post, Transparent Data Encryption is there to stop someone walling off with your data.  A database can be encrypted using a key which is stored in a certificate, again protected by a key in the master database.  So what's to protect your master database? 

Well that's not the top of the hierarchy for keys as this diagram shows from the security section of SQL Server Books On-Line :

So the Service Master key can be encrypted as well and this is done using the Windows Data Protection API.  My good friend Steve Lamb will be posting about this shortly.

Finally MSDN has the complete up to date books on-line for SQL Server 2008 here, just so you know

I am ploughing through my inbox now I am not on the road anymore, and I have picked up quite a lot of flak around the costs of Performance Point.  I have passed specific queries on to the licensing guys, but the sort of thing I was getting both on-line and off-line form the TechNet road shows was

• Can't I just buy ProClarity and therefore cut down my licensing costs?
• It's going to cost megabucks to roll out in my company so I can't afford it.

Dealing with each of these in turn:

ProClarity licensing.  When Microsoft bought this into the Office product stack, the cost was reduced by over half for the whole product and if memory serves Performance Point is scarcely more expensive than ProClarity 6.3.  ProClarity did/does have a dashboard server so this function is now superceded in Performance Point by the functionality of Business Scorecard Manager.

Performance Point Costs are too high.  I worked for a couple of Gold partners before joining Microsoft in July and I got this sort of response whether I was working with Business Objects, Cognos or Performance Point.  I am not going to debate license costs here, so I will assume they are the same for each product.  However from my own experience Performance Point can be be deployed, developed and maintained for less, in a Microsoft based environment. 

I realise that this is contentious, but Performance Point builds on well known components:

• Windows Server 2003r2 including Active Directory and Internet Information Services.
• SQL Server 2005, including reporting services and analysis services.
• SharePoint whether this is Windows SharePoint Services 3  (in Windows Server 2003) or SharePoint 2007.
• Excel 2003 or later.

So the only unknowns are how to use the front end tools to deliver reporting and analytical content in the Dashboard Designer, and how to model the business in Business Modeller.

I would note that the true cost of implementing proper performance management in a business is the change management needed to get everyone using the new tools in a coordinated way.  This requires a big investment in education, not just the use of the tool, and applies no matter which vendor you decide on.

The costs of implementation of a performance management solution, must of course be measured against the benefits and the big challenge is identifying and quantifying those. Only rarely do I see an organisation effectively measuring those benefits before and after new solution is in place, to confirm the project is a success.

A possible answer is to predict and measure the cost and impact of decisions that will be made with a performance management tool in your business i.e. using the tool to measure its own success as well as that of the business.  Of course any success will have many friends and any failure is usually be the fault of the system, but I would submit the costs of success or failure in  your business are far higher than the cost of the system supporting those decisions.

I am manning the ask the experts SQL server booth at TechEd this week. One of the many interesting questions I was asked was how the reporting information from the standard reports in SQL server could be integrated into one set of data from multiple servers.

I couldn't think of anything immediately to help him, so I am posting my thoughts here.

The reports are using Reporting Services from a SQL source, but I couldn't find out what this was without running profiler - so that's what I had to do.  I setup a new trace to watch for  RPC complete events with a column filter on application name set to "Microsoft SQL Server Management Server".  I then ran the Activity - All Session report:

and then stopped the trace and looked at the relevant event:

as you can see the SQL looks long but it's mainly columns, there are only four tables involved:

• sys.dm_exec_sessions
• sys.dm_exec_connections
• sys.dm_exec_requests

Of course in some of the other reports there are multiple charts and data regions so the underlying query will be much more complex, but this can be easily broken down and will only hit four or five table for each sub-query.

The next step is how to combine and extract this.  Personally I would use Integration Services  to run this query for each server with the query as the source and put this out  to one table. On the way throw you would tag every row with the server it came in a new column. the final step would ne to fire this out to excel and schedule it to run as required.

Another approach would be to do the who;e thing in one stored procedure which would link to each server in turn and would run on demand.

Like Salvador Dali I have hung up my watch. I am in Barcelona to enjoy a bit of culture now TechEd is over. 

However I did notice that the next beta of SQL Server 2008 (CTP5) which has about 75% of the functionality of the final release and is now available on the connect site to download.

Some of the new and interesting stuff in there includes:

• The new spatial data,type indexes and functions to enable location intelligence.
• Database encryption to protect the database 'at rest'.
• The new filestream data type which extends allows for flexible storage of unstructured data.

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. 

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.

  I had many happy debates with a business analyst on one of my recent projects because he was bought up an an early olap product called HOLOS and he couldn't see how the dimensional model I was presenting was ever going to work.  If we had been using HOLOS, and pretty well any other OLAP engine including analysis services in 2000, then he would have been absolutely correct.    

The key thing that's different in SQL server Analysis Services 2005(SSAS) from any other product I have come across is the way that any field/column that is made into an attribute is essentially a dimension in its own right.   If you open BIDS (BI Dev Studio) and open the product dimension in the adventure works sample project You will see this:

The left hand pane shows all of the attributes that have been used from the three product related tables (the blue boxes) .  There are 5 hierarchies defined in this dimension so if we were in analysis services 2000 for example this would show up as five separate dimensions in whatever browser you were using e.g. excel, ProClarity. 

What's interesting here is that all of those attributes on the left hand side are also dimensions even if they're referenced in a hierarchy.  In the screen grab above, the focus is on the weight attribute so the properties on the right will relate to it.  The second property from the top, AttributeHierarchyEnabled, is set to true and this means that it can be used to slice the data and so there will be a dimension called "weight". As it stands weight will have a level, All, which will then expand out to one member for each distinct weight value, so we could easily end up with a dimension that has a leaf level row for each product which is either completely irrelevant or at best not at all meaningful. 

If you want to be able to get at the weight of each product then you could set AttributeHierarchyEnabled to false in which case it will behave like a member property in analysis services 2000.   If you do want to make a dimension from it then you might wish to put the weights into bands e.g. less than 1 kilo, 1-5 kilos etc.  This will give meaningful analysis without seriously affecting the cube. 

For product this situation isn't to bad, but if you have a streeline1 attribute in a customer dimension with 6 million rows in it then that attribute will not only be of little value but when a user clicks on "all" to expand the attribute this will probably kill theirPC as all 6 million possible streeline1 values are downloaded.  You couldn't do this in the old version of analysis services, as a member could only have 65,0000 children but now you can go large with your dimensions and attributes, which needs to be treated with the same caution as large quantities of  burgers. However My main point here is that you may not have wished to do this at all, you may have followed the dimension wizard without realising the implications of all of those steps and checkboxes, or  you may not be aware of how the users see these attributes.

As ever, in the next version things get better and analysis services 2008 introduces the blue squiqglies which will underline performance issues and let you know about best practice where red squiqqlies will indicate actual errors.

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. 

 Hugh McLeod is quite correct, On-Line Analytical Processing (OLAP) is actually about business, it just sounds like a science project.

Basically OLAP is an awful name, Nigel Pendse, author of the OLAP report calls the same thing FASMI, which I think is a far better term :

• Fast - 90% of queries back in under 10 secs and no query takes longer than 30 secs.
• Analysis - Drill down, multiple aggregation techniques, sophisticated graphics, trends all form part of this
• Shareable -  good security at the back end and available to a wide community of users.also multi currency, multi lingual to cope with the global economy.
• Multi-Dimensional - Excel pivot tables but more so. The ability to have any multiple dimensions of information on each axis of a cross-tab with other dimensions being used to further filter the results returned.
• Information - Real world KPI's rather than raw numbers.

In  OLAP the cube is the database structure that is queried on and to get a handle on how this works below is a simple 3 dimensional cube

 The coordinate system in a cube not only has a reference to a point in multidimensional space it also has an understanding of hierarchies.  So the cube 'knows' that January 2007 has a parent called 2007 in the example above.  This forms a key part of the OLAP concept - that the results of calculations can be stored at the parent level rather than using on the fly aggregation of all the children e.g. the sales total for 2007 is stored in the cube for bike, components etc. as is the cost of sale.  The profit margin % has to be worked out on the fly for bikes for 2007 but this is quick as the cost of sales and the sales that contribute to this calculation are pre-calculated.   This gives OLAP it's speed while allowing for rich calculations to be stored.  As always in IT there is a catch, and in my opinion that is the complexity of the  language used to query a cube and that is MDX or multi-dimensional expressions.  Like SQL, this is an open standard that also runs over cubes in such products as Oracle/ Hyperion Essbase, SAS, Cognos Powerplay and Microsoft's SQL Server Analysis Services (SSAS). More on MDX in future posts but if you can't wait, the go to MVP on this in the UK is Chris Webb and the book he has co-authored

16 October will be the UK launch for Performance Point 2007 which is going to illicit three reactions:

• Fantabidozoo it's out at last!
• Oh oh Microsoft have released another thingy that costs half as much as our whatsit so let's get marketing to tell everyone that it doesn't scale for the enterprise and it won't be any good until sp1 comes out.
• What? Never heard of it!  So you do a live search on "Performance Point" and you get a lot of car stuff and there's also an outfit in California providing business coaching and actually this is quite relevant.

This coaching company Performance Point have identified a number of symptoms for ailing businesses:

1. You have a vision for your business, but you don't know how to get there.

2. Sales are flat or declining. Sales are below budget.

3. Long-term customers have left. They are doing business with your competitors.

4. Profits are declining.

5. Companies in your industry are expanding or changing. Your business is not.

6. You are working longer and longer hours.

7. There is never enough time in the day to accomplish all of the things that must get done.

8. The business no longer meshes with your values and interests.

9. The business has become too complex for you to handle by yourself.

10. The business is no longer any fun.

11. There are frequent problems with your staff.

12. The best employees are leaving.

13. Problems are occurring in your personal life.

14. You feel you as though you are in a rut.

15. You dread going to work each day.

I am not going to pretend that a new piece of software is going to crack all of these issues but it will help with the first dozen, so you will have more time to work on the last three yourself.  But how is a piece of software going to help? well it does three things:

• It allows you to analyse and monitor your performance. so you can see what is contributing to falling sales and reduced profits.
• Set and plan your balanced scorecards and have these cascade through the organisation so everyone can see how their efforts contribute to the success of the organisatons.
• Plan and forecast your budgets from a top down or bottom up approach so you can see what resources are needed to meet objectives.

Whether you decide to get some coaching or buy some business intelligence software  you do have to actually apply the findings and lessons learned to your situation before anything will change.

You would expect there to be a record and hopefully only one record for each sales person in a typical sales system and another record in another table for each of the customers.  But what happens when a one of these sales people gets assigned to a different territory or is promoted to be a regional sales manager?  The usual answer is that the record is edited to reflect the change.

That's fine until you decide to start to look at trends over time and analyse how each person in the sales team is performing against target .  In the example above, all of the sales in the system will appear to have been made by a person in their current role, so when a report is run the answer will be distorted.  This is where a separate data warehouse can help, as choices can be made about how to deal with these kind of changes independently of the line of business system.  There are three industry standard approaches to the problem of slowly changing dimensions:

• Type 1.  This is pretty much what happens in the line of business system - the new version of the record overwrites the old version.  This is easy to implement and for many attributes it isn't important to know the historical value e.g. the last name of the sales person might change when they get married.
• Type 2.  In this approach there is a row added to the dimension table every time a change is made to the sales person e.g. when assigned to a different territory or on promotion.  
• Type 3. This technique adds extra columns to each row which hold the previous version of the information so you might have [original sales territory] or [old job title] columns and that can work where only the original and latest versions of a record are needed, but you have to decide which columns to use in which situation.

The type 2 scenario requires the generation of a new unique key on the dimension as there will be multiple versions of the same source record each with the same source or business key. This new surrogate key is how the dimension is joined to the sales facts made for that version of the sales person as you can see below

For those of you in black and white, Steve's sales in E region are in orange and those when he moved to SW region are in blue.  We can sum and group a join of these 2 tables by [Name] to see all Steve's sales or we can do this for [Territory] to get a true picture of sales in either scenario. 

Note that the business key doesn't appear in the fact table and that the surrogate key is just an arbitrary number -  usually a sequence number incremented as new rows are added to the dimension.  It is also good practice to add  extra columns such as [Is Current], above  to mark which record is the current one. Alternatively  [effective from date] and [effective to date] columns can be used to show when the row was in force.

So using type 2 slowly changing dimensions is the most work but the most versatile approach, and the good news is that there is a wizard  for this in SQL Server 2005 Integration services (SSIS).

The terms Business Intelligence, Data Warehousing, and Data Marts are used interchangeably by many people so I thought it would be good to explain the differences.

The data warehouse is the repository for all the data to be analysed and reported on without the means to do so. A good analogy would be SQL server 2000 before reporting services was added to it, it could store all the information you needed but there was no mechanism for getting information out.    

Data Warehouses can be really big and can provide analysis and reporting for the whole enterprise.  These big projects got a bad press for taking too long to implement and for being too difficult to change.  The response to this was the data mart, which is not simply renaming the same thing to deflect criticism. 

A data mart is a subset of information in an organisation that meets a specific business need or is just for a part of the business.  Individual data marts are implemented on the basis of need and return on investment. The idea is that the individual data marts are joined together to form the whole data warehouse.

This sounds great but has been beset with problems for 2 key reasons:

1. Each department does its own thing to implement tactical solutions, which leads to 'mart madness' where there are several implementations using different technologies.

2. Each data mart is designed differently and so they cannot be joined together for consistent enterprise reporting.

This really is just two sides of the same problem, but it is so important I thought it would be good to mention twice!

So just like every other area of IT, architecture and design must be properly addressed or all the later stages will fail.  

Looking at that product dimension for my last post, I noticed that there were five hierarchies defined for the product dimension.  A hierarchy is a very important part of any OLAP engine and allows users to drill down from summary levels down to detail levels much as you might want to zoom in on a virtual earth map to see your house from looking at the county you live in.

A good example of a hierarchy is Product Model Categories.  All hierarchies start with an All level not shown here as it's a property of the hierarchy and you can decide what it's called e.g. All Products etc.  The next level down is Category then Sub-Category and then Model.  This type of hierarchy is called a natural hierarchy because a given Model only has one Sub-Category and a given Sub-Category only has one Category.  In other words there is a one to many relationship between product and subcategory and another between subcategory and category.  This makes for an efficient cube design which will be reflected in fast processing and query response times.

However how do you tell Analysis Services to do this.  In its 2005 vintage you would change the attribute relationships by dragging attributes underneath each other on the attributes window above.  There isn't a lot of help on this and it can be quite difficult to get the relationships correct.  However the screen grab above is form CTP4 of SQL Server 2008 and no there is a New Attribute Relationships tab to show all this more clearly:

As I mentioned in my previous post blue squigglies are not good and in this diagram we can see that the sample product dimension has a  redundant relationship between product and subcategory.  As you expect hovering over either of these gives the nature of the warning:

Surely you could get rid of that and then you're sorted. Well no because if we do and then go back to the Dimension Structure tab you see that there is now a red squiggly under product and this is because Product is ordered by Sub-Category and now there is no relationship to get at this.  So you have choices:

• Put up with a less than perfect design and leave the redundant relationship in
• Order product by something else and ignore your requirements spec
• Derive a new column to order products in the table or data source view based on Sub-Catgory.

So the blue squigglies are there to highlight potential performance problems, but what you do about them is up to you just like advice from any expert, your doctor, your accountant or your personal trainer! 

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.

If you are left handed like me you notice how much of our language is right minded or right thinking.  The same thing happens all over Europe; it is good to be adroit from the french (droit) for right and sinister is from the Italian (sinestre) for left. 

At this point any leftie reels out all the cool people from Leonardo to Einstein and Hendrix  who were also left handed (& my boss Eileen).

You also get gadgets for left handed people from scissors to guitars and golf clubs, but these aren't always high quality so Hendrix simply bought a right handed stratocaster and played it left handed.

However this clock has to be one of the strangest ideas  and this is hanging up in our office in Prague.I couldn't find a vista gadget like this so maybe some left handed developer could help me out.

Try it yourself - simply smash up your right hand like Steve and see how much fun it is to be left handed!

Warm up your users this winter with heat maps in SQL Server 2005:

This report was created by one of our testing gurus, Patrice Brassard, and uses a  to show up a dll he has written in c#. The code was used to simplify the process of applying conditional formatting in every cell of the report.

The DLL contains a static public method:

public static string GetColor(decimal redStartVal, decimal yellowStartVal, decimal greenStartVal, decimal val)

where:

• redStartVal represents the value where a pure red would be used
• yellowStartVal represents the value where a pure yellow would be used
• greenStartVal represents the value where a pure green would be used
• val is the value where you need to get a heat map colour

The function returns a string corresponding to the Hex value of the colour.

The example report above represents test results so Patrice wanted only 100.00% to be shown green (i.e.99.99% should be yellow) so he used the following values:

WLX_SSRS_Utils.HeatMap.GetColor(70.0, 99.99, 100.00, (Sum(Fields!RawPass.Value) / (Sum(Fields!RawPass.Value) + Sum(Fields!RawFail.Value) + Sum(Fields!RawNotRun.Value))) * 100.0)).

Please note that the attached DLL that needs to be on both your dev machine and the SSRS server:

• On the dev machine (say your PC), it needs to be deployed in %programfiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
• On the reporting server, it needs to be deployed in %ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin