SQL Server 2005 @ Business Intelligence

Moved

2006-06-19T21:57:00.000+01:00

Well after a number of false starts i have finally moved this blog to

www.markhill.org/blog

See you there , oh and i will update more often now , promise !

New Post on MSAS blog

2006-02-03T19:55:00.000Z

New post on the MSAS blog...

On Technical Writing ...

2005-12-14T20:23:00.000Z

In my SSIS blog i criticised the quality of some of the documentation especially around creating custom tasks, Douglas from the MS SSIS technical writing team contacted me and asked that i post about ways in which the community can help improve any areas which people find fustrating. Douglas wrote...

"Please use the Send Feedback link that's available on every page of Books Online to let us know if you find an error, or insufficient information to do your job. And, if you have an opportunity, encourage your readers to do so also. While we might never see a blog posting that mentions a problem with the documentation, your feedback goes immediately to the responsible writer in the form of a bug that can't be ignored."

In the past i have never bothered to click that link, from now on every time i see an error i will be clicking away knowing that there is a team of people who care passionately about the quality and accuracy of the documentation.

He is ready to blog

2005-12-09T11:24:00.000Z

I have been on and on at Sutha to start his own blog for long enough. Expect some good stuff , and as he points out "Get ready to rock !!!"

Suthas Blog

New Post SSIS

2005-12-08T23:34:00.000Z

I have added a new post on creating custom components for logging rowcounts in the SSIS section.

SSIS Logging Component Post
Enjoy

I know i said no more ....

2005-11-17T09:08:00.000Z

Well SQL Server 2005 finally RTM'ed yay !!!!

Do recent announcements that Exchange will only be offered in 64bit versions mean that the same future is likely for SQL Server in the future ? I think it is only a matter of time, as always the reaction of customers will be key.

Me old mate smudge finally bit the dust, visit the link below to learn more.

http://spaces.msn.com/members/rubenhill/PersonalSpace.aspx?_c=

And for the gadget freaks reading, my new laptop is the ferrari 4005 one word Fantastic.

Short but sweet this time, more about a new home next time, plus some more technical stuff :O)

What no more posts ...

2005-09-23T08:00:00.000+01:00

I was out for a beer last night with a couple of friends and they reminded me that i had not updated my blog for quite a while.

There is a very good reason for this, watch this space for more details very soon.

Added A Post

2005-08-30T00:15:00.000+01:00

I have added a post about MSAS 2005 aggregations to the MSAS section.

I am breaking up ....

2005-08-29T21:04:00.000+01:00

I have created two new categories in my blog as i expect to making a larger amount of postings

markiehillmsis.blogspot.com will cover MSIS
markiehillas.blogspot.com will cover MSAS

If i get around to pocking more in data mining and reporting services i will create some categories for them.

General ramblings about weird stuff can still be found here at the main site. If you want to subscribe to the above then just stick a /atom.xml onto the end.

You can reach these sites from the categories link on the left hand side !!

Restricting Rows In Analysis Services 2005

2005-08-29T12:56:00.000+01:00

Quick Tip:

When working with a cube and you want to restrict the rowset that is returned for testing etc you can do this within the DSV.

Right click on the fact table and select the replace table with named query option. You will then be presented with a query editor, edit the select statement to include select top x where x is the number of rows you want to test with.

This saves you from having to ask for or change database objects / views etc , pretty cool !

Moving cube directories in SQL Server 2005

2005-08-27T12:39:00.000+01:00

I ran out of space in my VMware session and had to create a new drive for my AS cubes, this means that i had to move them from their current location to a newly created partition.

This is the process i used, i know you can edit some of this stuff through the GUI , but its far more fun to play with ini files (isn't it)?

All this information applies to the June CTP build, i am guessing it will remain relevant for future builds.

Moving cube directories in SQL server 2005 is fairly easy, all the options for the OLAP component of the software are stored in the installation directory under \MSSQL.2\OLAP\config in a file called msmdsrv.ini

Stop the SQL Server OLAP service.

Before editing anything you should take a copy of this file (store it as msmdsrv.bak) so you can find it later should you need it :)

Underneath the Configuration Settings entry you will see , ensure the service is stopped and then change this value to your new cube directory (remember to save it).
For example my new setting would be e:\as\data

Copy the contents of the old directory which are by default stored in your installation directory under MSSQL.2\OLAP\Data to your new directory and restart the service.

Job Done !!!

I quit !

2005-08-25T21:08:00.000+01:00

Today i quit my job :)

Integration Services Puzzle

2005-08-18T00:14:00.000+01:00

A friend of mine emailed me today and asked how can you get SQL Server 2005 Integration Services to substitue a null to a value (like unknown).

He had been playing around with expressions and was struggling to work out how to make a conditional decision within the expression itself. So without getting to the argument of whether an expression is the correct place to do this, i thought i would take a look.

I have to say it was not immediately obvious to me how you would approach this, however after a little trial and error , the ? operator seems to work nicely and does the trick

(boolean expression?expression 1:expressio2)

So in our case we would have , ISNULL(CUSTOMERNAME)?"Unknown":CUSTOMERNAME

Which provides the desired result.

Desktop Linux, Myth or Reality

2005-07-25T11:17:00.000+01:00

Introduction

Before we start I am going to put my technical cards on the table.

I am an IT professional having worked in the industry for over fifteen years. My experience stretches from proprietary operating systems (Pick OS) through Unix System V and on to all the Windows platforms since 3.1. I am therefore fairly qualified to talk about Operating Systems and their relative usefulness on the desktop.

I have no particular loyalty to a technology stack, however I do believe that Microsoft have had a clear edge in terms of developer productivity and so therefore most of my development over the past few years has been focused in this space.

I think Windows XP is a great operating system which does what it says on the tin, yeah it has faults but don’t they all. I always work behind a router / firewall before I access the internet and I don’t assume any operating system is invulnerable, because its not trust me.

I am going to use the “could my dad do this test” in this article, it is fairly safe to assume I can fix everything that happens on a computer, but desktops are for the masses so lets judge how suitable Linux is on the desktop by my dads computing ability which is fairy average for a novice (sorry dad).

Background

Every year I install the latest incarnation of Linux, picking a desktop at random and attempting to replace my Windows XP desktop. So far I have been unable to do this without compromising some functionality and ended up reverting back to Windows XP. This is not always a problem with Linux, but usually down to a lack of support for hardware etc, I refuse to tailor my hardware to the operating system so I expect whatever OS to run on what I choose to purchase.

Why do I do this because I am certainly not a MS basher, in fact I have a great deal of admiration for what they achieved on the desktop. It’s namely interest in computing and a genuine interest in Linux as something unique (being free) If you don’t believe Microsoft themselves aren’t doing this, then shame on you.

Installation

The installation of XP is always painless for me, it will install from start to finish and provide me with a system that works. I have not had any installation issues since the Windows 9X series (yeuch).

My chosen installation of Linux for this experiment is Debian (Stable). Okay so I hear you Linux propeller heads shouting why stable, why not testing, it’s got the latest, greatest modules …. Well would you run your life on Longhorn (or Vista) Beta One..... probably not.

I downloaded the internet minimal install (roughly 130MB) and installed the base system which then should download the rest of the software over the internet. This in theory is a great idea as it saves me downloading a load of stuff I don’t need.

Okay so we need to get Windows XP and Linux to live together (you must always install Windows first in this scenario) or else you will lose access to your Linux installation. After a few nervous moments with Debian install tool, I have managed to partition and reformat some free space, yeah! After a sweaty reboot the GRUB menu appears and Windows is still there plus my new Linux kernel 2.4.

Could my dad do this, yeah sure he could.

Networking

Right off the bat, we have a small problem. My wireless card is not supported out of the box and there are no Linux drivers for it. Hey no problem I plug my machine into my router via a cable and we are off.

Roughly one hour after choosing all my software I have a working gnome desktop, with the minimum of hassle and no nasty moments.

Okay so this is where it gets a little messy, my wireless card still did not work at all and I don’t want to stay plugged into my router.

I am aware after my last adventure in Linux that there is a NDIS wrapper which you can use around your windows drivers and get your card working. Unlike last time I don’t need to go find it and compile it , this time I have a secret weapon (and one of the reasons I chose Debian).

The Synaptic Package Manager is something that I am surprised Microsoft has not picked up on. What a joy to use, all I need to do is to find the package name I need and it will be downloaded from the internet and installed.

Within seconds I had the NDIS wrapper installed, Windows drivers unpacked and my wireless card working, result!!!

Before you smile too much, I did have to open a command prompt and type some arcane commands which had to do with loading kernel modules and setting some of my wireless options which I had to go and find on the net somewhere.

Could my dad do this, I don’t think so

More Next Week, when i will take a closer look at the desktop.

Into the Alien frontier

2005-06-03T10:12:00.000+01:00

I am now delving into the alien world of Siebel Analytics to look at a poorly performing system. This should be quite interesting and a departure from the Microsoft technology stack.

Have any of you looked at Siebel and do you have any pearls of wisdom to share with me !!!

My Blood Is Boiling.

2005-05-18T15:20:00.000+01:00

Nothing technical about this post, this makes my blood boil

http://news.bbc.co.uk/1/hi/england/shropshire/4559173.stm

My wife has just been done for driving 36mph in a 30, this guy drives 100%+ over the speedlimit and what happens.

One Care Couldn't Care Less

2005-05-13T09:50:00.000+01:00

This i believe is a huge mistake http://67.19.9.2/?article=23217

Microsoft had a big chance here to provide a great value add service to its loyal customers and give them another good reason for owning Windows....

Instead if the article is to be believed, it has become a short term revenue generating excercise ! Why do i say short term, well if they were thinking medium to long term a better strategy IMHO would be to look to give customers that warm feeling of security, this would help retain current customers as other rival OS's make up ground and also give new customers that nice warm feeling of being loved.

Not impressed at all.

The Never Ending Connection

2005-05-12T14:57:00.000+01:00

We had a situation a few weeks ago when Analysis Services was not responding to any user connections. Thinking that this was perhaps a return of some of the historical problems that we have had in terms of locking, we promptly logged a call.

This particular situation was slightly unusual in that it appeared to come and go with no warning which made tracking it down very difficult.

During our investigations with MS it became apparent that something else was going on here, not only was AS affected, SQL Server was also demonstrating the same symptoms. Connections appeared to either take in excess of 50-60 seconds or else they did not work at all.

Many memory dumps later and after plenty of diagnostic work with MS it would appear that the user connection was being help up inside LSASS.EXE when talking to the primary domain controller.

After a bit of internal searching we eventually found that one user was logging onto the server with a NULL domain name, which was causing the old NT 4 PDC to broadcast to all the other domain controllers and wait for a response to see if they could validate the user. When this failed control was eventually returned to LSASS.EXE and some logons could be serviced before the user tried to connect again, and the whole vicious cycle started all over again.

I have never heard of a problem like this before, so I thought it worthwhile to share. If you need any further details on this please contact me.

Say Aye To A Pie

2005-05-09T22:36:00.000+01:00

I decided in a sudden rush of home sickness a few weekends ago that we needed to go back to Scotland for a long weekend (not as far as Kilmarnock, before you ask me why i didn't drop in), which was probably a mistake in itself. Firstly the car broke down on a lonely Scottish road and after spending a few hours in the worst thunderstorm ever waiting for the AA to arrive i was in a fairly foul mood. However a single moment of humor was to be found next day when walking past a local butcher, there was a picture of a rather chubby little boy stuffing his face with a pie , with the slogan "say aye tae a pie" displayed proudly underneath.

And you wonder why Scotland is the heart attack capital of the world....

Let me tell your fortune....

2005-05-09T22:30:00.000+01:00

If you were really having a bad day and you just happened to work in a fortune cookie factory what do you think you could do to cheer yourself up ? Well if your anything like me you would probably tinker with the software which produces the fortunes to emit messages based on your own evil brand of humor

You can imagine my amusement where opening a fortune cookie over the weekend i was confronted with "I hope you are not planning on driving home, because your car has been clamped".

Somebody was having a real bad day ....

Analysis Services Locks

2005-04-20T13:36:00.000+01:00

In a high stress environment where you are trying to process a partition whilst your users are actively querying the cube you may have reached the situation where Analysis Services appears to lockup and no queries are resolved, and connections are not honoured.

This can be caused by the fact that AS is waiting for the reader to finish its query before the writer (in this case AS itself) is able to commit the changes which have been created by the partition processing.

Depending on the nature of your application, holding up the entire system for one user to complete his/her query may not be optimal.

If you think you are suffering from this issue you can easily check this by keeping a close watch on the lock waits counter of AS, you will find that this will be increasing and not returning to 0 quickly (in our situation we found it stuck at around 50 for extended periods of time), you may also find the connections in progress counter creeping up and up as your users are caught in limbo .

Luckily there is now a great KB article which covers this issue and provides a neat fix.

http://support.microsoft.com/default.aspx/kb/872934

Analysis Services Network Performance Part III

2005-04-06T14:54:00.000+01:00

Analysis Services Network Performance Part III

Many people have raised a few good points in reply to my last positing. The consensus is that many people would assume that naturally verbose XMLA would be a slower solution than the binary connection protocol that PTS uses.

In fact if you try to connect with the uncompressed XMLA stream that will be the case, IIS compression reduces the network traffic by around 80-90% due in part to the repetitive nature of XMLA.

However for me the key difference is more fundamental, it is the model of connection and querying that is different.

We treat the server almost like a web service in that we submit a query and then get back a response with pretty much what we asked for. This is wildly different to PTS which will return what it thinks you asked for, plus a load of other stuff.

My understanding is that PTS on the client will take your MDX query and break this down into a number of queries; it then gets the results of these queries and then passes them back to the client tool.

In this process you may find that the data shipped back is at a lower level that you requested in your query, or contains more members than you asked for because PTS is trying to be smart about caching on the client.

This model makes sense on a LAN because you have a large amount of bandwidth, however on a WAN this model gets messy real quick. What the XMLA SDK does is give us the opportunity to do the PTS bit back on the server and then just ship back the data that we asked for in the first place, this gives you a much thinner way of providing your users with the service that they require.

If you want some more specifics in info of report times vs location etc please contact me

Analysis Services Problems Part II

2005-03-04T13:40:00.000Z

In my last posting I talked about the trouble that you can get yourself into trying to run PTS in poor network environment. We discussed how to get XMLA as far as the desktop however did not discuss how we can get client tools to consume this data with the minimum of hassle.

This was one of the biggest challenges that had to be overcome, I thought about writing an OLEDB provider for XMLA which the client software could use and be fooled into thinking it was running from PTS… As luck would have it, at the same time I was introduced to a company called Digital Aspects (http://www.digitalaspects.com), who had built their first version of something similar and we were keen to test it.

I could clearly see that the potential for a great tool was there, although a little rough round the edges, when used alongside compressed XMLA documents, we could see that significant performance gains would be possible if we could get the provider to cater for the many different types of queries that our users run.

Its fair to say that we embarked on a fairly long journey at this point in partnership with Digital Aspects to really come up with something that worked for us. After probably six months of versions shipping backwards and forwards, I like to think that the provider as it stands now caters for all the weird and wonderful queries that our users like to chuck at it. Of course getting the provider to perform was not the whole story, a lot of changes had to also be made to our client application Intelligent Apps to work effectively with the new provider, however the difference is staggering!

So let’s talk about performance and the improvements that we can quantify.

Connection to our main cube used to take in order of around 10-20 minutes in some locations even with all the different PTS connection properties set on the client, with the new provider around 10-20seconds is pretty representative now.

In terms of queries themselves, we are seeing on average over 500% performance increase, however in some cases reports which we could not run at all or would have taken all day are now taking 5 minutes. All in all a success story from the user perspective.

I hope this was an insight into a way of tackling performance in a large diverse org, next time I will talk about the Analysis Services locking model.

Analysis Services Problems

2005-03-04T10:07:00.000Z

Have you ever had a problem with no obvious solution?

I inherited a problem like this in my current role. To quickly paint the picture, we have a large Analysis Services deployment which suffers badly from poor network performance caused mostly by the poor infrastructure and compounded by the way that MSAS uses the network.

For those of you that don’t know MSAS uses PTS. PTS is short for Pivot Table Services and is the software on the client which talks to the Analysis Services server. PTS is great when you have a pretty capable network as it performs a load of caching and delivers a pretty good experience. The first cracks in this architecture appear when you introduce latency and bandwidth into the equation, by restricting these you find that PTS becomes rapidly unworkable below speeds of 64Kbps, latency of over 200ms also has a huge impact on the user experience.

So how do we look at solving this issue without looking at SQL Server 2005 which has a completely different client model? Also purchasing a thin client like Novaview is out of the equation for various reasons.

To solve this problem, you have to tackle a few areas ….

Network Traffic

Microsoft released their XMLA SDK 1.1 kit last year; this provides the ability to ship XMLA data back to the client. At the time of writing none of the major clients can consume this natively and I suspect they will all wait for Yukon before doing so. So why would I suggest XMLA? If you look at the output it’s actually very verbose and makes network performance even slower.

The first problem is how to reduce the size of the XMLA document which is shipped back to the client, this is actually quite easy. IIS has an option which allows you to turn on compression; this means that if the server and client can negotiate a compression protocol then your data will be compressed before transmission, this results in a compression ratio of around 90%.

In reality though I found the implementation of compression in IIS to be less than reliable and restored to a third party provider (http://www.pipeboost.com). This is what we use in production today.

Great so now we can submit a query to the XMLA SDK on the server and get back a response compressed but that does not really solve any real world problems. Within our company all of our users use one of two main tools, Proclarity or IA. The next issue is how we present this data to the existing clients.

To be continued …..

Do you have a pocket pc....

2005-01-31T21:51:00.000Z

If like me you always get the connection prompt when moving your pocket pc between home and office here is a really neat utility to make one of the machines always be the guest without prompting.

http://myitforum.techtarget.com/articles/9/view.asp?id=4131

Ohhh god i am a nerd.

2005-01-07T00:03:00.000Z

I plucked up the courage and took the nerd test ....Results below.

It was a pretty rubbish New Year for us, both the kids and her indoors were loaded with the cold, closely followed by me :(

On the technical front i have finally ditched IE as my primary browser and now use firefox, not just for the tabbed browsing but also the finer degree of control.

Microsoft have launched their spyware checker today as Beta 1 , there is a rumour that the final version will be subscription only, i hope not, this would be the final insult to many, as it would be asking customers to pay MS to fix the weak areas of IE and Windows. (As you know i am a big MS fan , but think this would be a step to far).

Finally i got a bit of a suprise after installing Linspire (AKA Lindows), i like to keep tabs on all the major Linux dists, to see where the industry in general is heading. Linspire is very slick and shows a lot of promise, driver support will always be the weakest part of Linux, until it will work with all my hardware with no recompiling and fumbling around on the command line it will never make the big time on the desktop for the avg joe. IMHO

Its getting colder...

2004-09-19T10:29:00.000+01:00

Yesterday i finished: Exiles Return

http://www.amazon.co.uk/exec/obidos/ASIN/000224683X/pd_ka_1/202-2894546-2872623

After a few dissapointing books , i get the feeling that Raymond is returning to his best writing, there is still non of the depth of Magician here, however a welcome return for Tomas & Pug who i consider to be some of his best and well developed characters.

Feist fans will not be dissapointed with the latest instalment , and if your not a Fiest fan, do yourself a favour and read the Magician trilogy.

Also i am supporting the www.veronicastephensontrust.com (i designed the website for them), and i believe there cause is a good one, if you have time, please visit and donate.

As the title of this blog suggets , its getting colder, for the first time we have had to put the heating on and stock up with fuel for the winter (we are oil fired).... brrrrr time to buy some logs for the fire i think.

Find my old stuff

2004-09-17T10:54:00.000+01:00

If you can be bothered reading my old stuff, then you can have a look at http://markiehill.mindsay.com.

My archive is here, perhaps one day i will move it over to www.blogger.com

Mark

I have moved house

2004-09-17T10:39:00.000+01:00

I have moved house from midsay to blogger.com mainly because the mindsay service was not what i expected after the upgrade. Best of luck to them however i think i shall make blogger.com my home for now.

Its been once of those weeks, two people who i have worked with for the past two years have moved onto new roles and new challanges, i wish them both the best of luck for the future. No doubt i will be seeing them shortly anyway.

Next week brings a new challange as i pick up the role of Chairman for the Microsoft Analysis Services user group in the UK, this event is due to be held at the directors institute on the 24th and while this session is completely booked , we are always looking out for new blood (and speakers) for the next session(s).

I am drooling with anticipation at the release of the new Star Wars DVD box set which i preoreded on http://www.amazon.co.uk , although by all accounts he has played around with the movie again , the benefits of having an official version on DVD far outweigh the small changes, i shall post a review once i have watched it.

Okay i am sad, but i do watch Most Haunted Live , (www.livingtv.co.uk), this is a very interesting program where some evidence for paranormal activity has been picked up on camera, its worth a look for the open minded amongst you.

Last week i had the pleasure of wathching i-robot, having read the original set of storys by Issac Asimov , i can't help with being i little dissapointed at both the movie and also the inclusion of Will Smith. All in all though taken in isolation not a bad film, just not a great film either.

On the technical front i continue to be impressed with what is coming out of the SQL Server 2005 development program , and every release i recieve looks like a real winner in terms of advancement from 2000 , i believe you can download a beta version now from http://www.microsoft.com. You owe it to yourself to do this even if you are not a SQL Server person !!!!!!