Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

September, 2007

  • SQL Server 2008 and end user ad hoc reporting

    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.

  • Lean and Mean

    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.


  • Mental Models - Attributes in Analysis Services

      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.

  • Hierarchies in Analysis Services

    ssas hierarchiesLooking 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:

    SSAS 2008 ar designer

    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! 

  • The Unified Dimensional Model (UDM)

    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. 


  • Certified Genius

    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.


  • The Balanced Scorecard - Flying on Instruments

    Flight Simualtor Cockpit   

    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. 

    Introducing Microsoft Office PerformancePoint Server 2007

    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.


  • Certified Muppet

    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!

  • Whatever happened to the Virtual Cube?

    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.


  • Cast and Convert

    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.

  • One Version of the Truth

    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.    

  • Unnatural Hierarchies in Analysis Services 2008

    ssas clip 2Continuing on from my last post, I explained what Natural Hierarchies were but I thought it would also be worth mentioning what the opposite was and the issues around that.  Unnatural hierarchies can be created between any two attributes for example in the world of human resources (HR) it is becoming more and more important not just to understand the mix of women in an organisation, but how that varies by grade.  So in analysis services we could respond to that by creating a hierarchy on the employee dimension of grade down to gender. 





    When we do this we have created an unnatural hierarchy and a blue squiggly appears for this because there is no relationship between Title and Gender as we can see by selecting the  Attribute Relationship tab:


    However we cannot create a relationship between gender and title because there simply isn't one, and if you do drag an arrow from title to gender this designer will let you, but it won't build.  So again you have choices:

    • Go with a less than perfect design and if you find users aren't making use of it remove it in a subsequent release.
    • Use many to many dimensions to model this
    • Use a calculated member to show a male/ female split.   

    There is no one right answer it all depends on the use of this hierarchy, and the volumes of data and infrastructure that are in place.

  • Making Hierarchies Natural

    I though it might be worth another post on hierarchies in Analysis Services 2008 CTP4 to show some more attribute relationship. If we look at the attribute relationships in the date dimension of adventure works below:


    the relationships match the hierarchies and so the design is well optimised and there are no blue squigglies.  However for this to work, there is some odd stuff that has been done in the date dimension.  If we go back to the dimension structure tab and look at the properties


    The key column for month name is a concatenation of Calendar Year and Month Number Year and this is important because it makes a month unique within a year i.e. January 2005 only belongs to 2005, where as if the key was just Month Name then January belongs to every year and every year would has every month in it so we couldn't put in a relationship between month and year.  This shows you an example of a work around to make your hierarchies natural.  However in my opinion you should design the data dimension from the ground up to support your analysis and reporting requirements, but that is another discussion for another post.

  • What are you doing with SQL Server 2008

    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.

    Technorati Tags: , ,

  • Hierarchies avoid arguments

    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.

  • Real Earth

    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.



    Technorati tags: ,
  • Compression without Compression in SQL server 2005 sp2 fact tables

    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!
    Technorati tags: ,
  • Pervasive Business Intelligence (BI)

    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! 

    Technorati tags: ,
  • Illicit Reporting

    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.