Mark Hill thinking about ….

We are all touched by cancer either personally or via the experiences of friends and colleagues, sadly the team at Edenbrook lost Jim Wright to cancer over the christmas period.

One of our colleagues Sutha … Is attempting to climb Kili later this year to raise money for the Ian Rennie Hospice. This hospice provided invaluable care for Jim during his final days and in my opinion is a very worth cause.

Please consider supporting Sutha in his climb attempt ! http://www.justgiving.com/sutha

If you have a blog or other mailing list please also post a link to his just giving page , then we can raise as much money as possible for the hospice.

I am going to stay away from the emotive issues which mostly relate to time invested by partners in growing skills around the product set, or partners advising customers that this was a good strategy to embark on in the first place (from a planning point of view).

For more reading on this, go here .. http://blogs.adatis.co.uk/blogs/timkent/default.aspx

I wanted to take a quick look at where this leaves the Microsoft BI platform today.

The major news for the M&A portion of Performance Point (Monitoring and Analytics) is that the product has now been moved into the SharePoint suite.

Is this a good thing for Microsoft BI? My personal opinion is that from a product perspective it will stifle the growth of the product and here is why.

SharePoint is currently tied into the Microsoft Office release cycle which at best churns out a new version every few years, given the current economic situation you could deduce that the next version of office (after 14.0 is released) may well be on a longer schedule. (I have no knowledge of the Office release cycle so this is just wild conjecture on my part).

Version 1.0 of M&A that has been rolled into SharePoint was not a very mature product compared to the competition (including ProClarity) plus SharePoint needs to be on a longish release cycle by its very nature.

So we have a piece of software which needs more agile development and which is still playing catch-up in the market place now having to move at a slower pace than it should.

My only hope is that we get feature packs for M&A which can advance it between Office releases or else I worry that that it will never advance quickly enough to beat off the slick demos from Business Objects and Cognos that I have to deal with on a day to day basis.

A bit like my last post really this is probably known to a whole bunch of people, however it is a useful tip if you use Excel 2007 to create your reports.

You can use this technique to get at the MDX that Excel 2007 generates, essentially just referencing the PivotTable object and retrieving the MDX property.

http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx

You cannot set this though, so extracting is okay, re-running from a MDX string is not possible.

Here is a simple way of making a calculated measure the default measure in a cube.

For cube developers this is probably old news, however it came up in a client conversation the other day and I thought it was a useful tip to post on this blog !

ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]

KB Article is here: http://support.microsoft.com/kb/915856

No that is not the sound of a large pink pig flapping past your window.

It is really true !

http://www.osnews.com/story/20548/Windows_Research_Kernel

This may have a real impact on projects like this which are trying to build (with some success) a completely open source version of Windows from the ground up.

http://www.reactos.org/en/index.html

In a previous post I touched on the problems that lack of bandwidth can cause BI applications. Way back when I worked for a large blue chip we had some pretty sophisticated kit for network emulation and testing. However thankfully these days there is a much easier and cheaper way of testing performance.

VMWare 6.5 brings the concept of a team (I am aware this was in previous versions but it did not work properly). Essentially you can group a number of machines together and build custom LAN segments between them , the really cool thing is that for these segments you can actually define the maximum bandwidth and the packet loss.

This allows you to get a feel for how the application will perform on a slower network. Unfortunately it does not have a setting for latency which is one of the huge killers for performance over a WAN.

Over the next few postings I will talk you through how to set this up and the results you can expect with SQL Server 2008, so stay tuned …

I was sitting with a colleague the other day and we were discussing the merits of open source BI in the current economic climate and the perceived expense of implementing a BI solution with the major vendors. Being a Microsoft architect I quite flippantly said I could design a credible BI solution which would serve a small to medium organisation with 20-30 BI users for under £30,000 … hardware and software costs all in.

Whilst any true cost comparison should look at cost of development, I am going to assume that you have an in house team who can assist in the installation and development of the solution. If you do not have these skills then a Microsoft Gold Partner can assist you in the design and build of your solution.

The prices listed below are prices that I have found on the web for the various components, in reality if you have existing relationships with these suppliers you may be able to get much better prices.

In my experience most Business Intelligence solutions need the following components

• · Portal software for delivery of data.
• · Relational Database Engine
• · An ETL (Extract, Transform & Load) tool
• · OLAP, multi dimensional server for ad-hoc slice and dice.
• · A tabular reporting tool.

We are going to start by looking at the most cost effective way of deploying a solution like this.

First of all we are going to need the following components from the Microsoft solution stack.

• · Windows Server 2008 (Standard Edition)
• · SQL Server 2008 (Relational Database Engine).
• · SQL Server Integration Services (ETL Tool).
• · SQL Server Analysis Services (OLAP Server).
• · Microsoft Office Performance Point Server (MOOPS).
• · SQL Server 2008 reporting services

If you reading this article it is likely that you have a small number of users, we are going to base our design around 20-30 ad-hoc users.

Microsoft best practice guides will say that you need separate servers for the relational & OLAP layer. You should also consider a separate server for the Portal Layer, in total we are probably looking at around three servers.

However we are trying to put together the minimum costs here, so let’s assume that we can use virtualised environments and that we can use HyperV rather than the more expensive VMWARE solutions.

The first thing we need to do is to spec up a reasonable server which can support a few virtual machines.

The specification that I have chosen is

As you can see this is a pretty robust server and comes in at a total cost of £3,346.00 ex VAT.

Now let’s take a look at the software requirements.

We are going to need to run 3 virtual machines.

· SQL Server 2008 RDBMS

· SQL Server 2008 MSOLAP

· WSS and Performance Point / Reporting Services

The diagram below shows how I would layout these machines.

The first thing we need to do is determine what version of SQL Server 2008 you will need. Typically for a small deployment you will have no problems with SQL Server Standard Edition, unless you require more advanced features such as partitioning or scaling out.

You’re going to need three of these SQL licences one for each server

The typical cost of each a SQL Server 2008 standard licence with 10 CAL’s (desktop access licences) will be £2,000.00 ex vat; this means a total of £6,000, giving you a total of 30 CAL’s.

You will also need a copy of Windows Server 2008 Enterprise Edition with Hyper V which will cost £1600.00 ex vat

Additionally you will need three copies of Windows Server standard edition X64 at £400.00 per copy, total cost £1200.00

Performance point server will cost £12,000 for the server and you will also need 30 CAL’s at £100.00 each, total £15,000.

So in summary….

This gives a grand total of: £27,146.00 which leaves  £2854.00 for incidentals, job done!

There are some caveats around this design that should be understood.

Most BI solutions should consider DR or Failover in the design if they are business critical, this has not been taken into account with this pricing, however with the addition of an extra machine you can have a standby switched off ready to go.

The usage of Virtual machines for SQL Server & MSOLAP is okay to a certain point, some careful consideration of your data sizes and expected throughput needs to take place before you commit to the virtual route.

The choice of base operating system for the virtual machines limit’s clustering later on; you would need to “upgrade” the OS version if you decided to implement a cluster.

There is no shared disk space with this solution, all space is local on the virtual machine and you should consider whether a shared array is more appropriate. This is particularly important if you decided to have a standby server in case of server failure.

Okay I am going slightly off topic with this one …. However I nearly spilled my coffee when I read Jon Honeyball from the UK’s PC Pro magazine has called for the driver signing  feature to be a mandatory feature of Windows 7.0 much as it is in the 64 bit version of Windows Vista.

You can read Jon’s proposals here…

http://www.pcpro.co.uk/features/125639/jon-honeyballs-wishlist.html

Why is this something you should care about? Essentially it means that every driver that is installed has to pass a certification process by Microsoft and be digitally signed.

Microsoft would argue that this increases computer security as it prevents malware writers from installing kernel drivers onto your machine to hide themselves and perform other nasty activities. It also prevents device drivers from being written that could intercept the DRM functionality within Vista, but we will stay off that topic in this blog.

Whilst this is a laudable goal, its impact is felt in an area much further afield than security. This change would have a huge effect on the open source and freeware development community. For instance take the open source freeware client Calibre that was written by the community for the Sony PRS500 (the first Sony e-reader available commercially in the West). This software requires a driver to be installed to communicate with the reader, hence the reason why there is no version which will work on X64 Vista.

Why is this a problem for freeware and open source software? Well the signing process costs money which is the very thing that these projects do no not have. Whilst the community would fund signing for popular software, many projects would fall at the first hurdle and never achieve the critical mass to find funding.

I propose one of the following solutions, either:

1) Provide a free trusted community signing body for non commercial software so that their drivers and applications can be signed. This could be a lightweight process if the project is open source.

2) Allow the user to bypass the signing process on a driver by driver basis and build safeguards into the operating system to prevent this happening silently without the users consent. Perhaps a dialogue box saying something along the lines of… “If you are not installing software to support a new or existing hardware device at this time then you should cancel this install and contact the software manufacturer”.

In summary Jon is asking for a functionality change which may impact the future of non commercial development for Windows, and by looking at Vista X64 we know that this is a path that Microsoft is treading quietly.

Microsoft and Oracle have both announced technologies that will allow the end user to have access to larger volumes of data. The recent demonstration of Gemini by Microsoft poses some interesting architectural questions.

As I write this , I am trying to prove that a large deployment of MSAS within my client will not actually bring their network to it’s knees. I fairly happy with the footprint of performance point, however the power users who need direct access to the cube are a different story.

Dimension sizes within these organisations are getting larger and larger , and the expectations of the end user are increasing with every product announcement and industry event that they visit.

There are some interesting debates around whether Gemini is a sane path from a IT perspective, and what business problems it can solve (I actually believe there are a number of cases where Gemini can be a provide huge business benefit, more on this another time !).

However the single most limiting factor in the Enterprise is that bit of cable that plus into the back of your machine.

For most Enterprise organisations the network is a problem, not in the central locations but out in remote offices where investment is low and business intelligence is even more relevant !

In some cases you will have multiple users sharing a 256K connection with email, web browsing, line of business app’s and BI all fighting for a slice.

Increasing network capacity is expensive ! So before you roll out the latest and greatest tech, spare a thought for the network and do your homework before deployment.

Granted this is the web version of Office, which will offer a lightweight version of the office tools, with fierce competition from Google Docs it will be interesting how long this remains “lightweight”.

Full article here …

http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9120143