Insufficient data from Andrew Fryer

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

July, 2009

  • Access - Office angel or spawn of satan

    My first introduction to Access was a painful experience, not because of the product itself but the way it was used, so it was mixed feelings that I read up on the official Access blog what it will look like in Office 2010. 

    Yippee it’s going to be 64 bit , so it will be easy load data form it and excel 2010 into 64bit SQL Server.

    Oh [insert expletive here]! users are going to be able to create really huge database outside the control of the IT department, that is until they break. 

    The front end will have the latest office treatment and for me this is where Access shines , forms data logic and reporting all in a box. They have also done all sorts of other thing like putting in trigger like functionality


    Another interesting area will be how all of this will work with SharePoint (itself a whizzy front into a database) but that will have to wait until the SharePoint conference in October and so you probably won’t see that functionality in the initial technical previews.

    If all this is making you wonder about the future of the lower ends version of SQL Server, even the lowliest version of SQL will still be a better in a variety of ways.

    • It’s a true database with proper logging and rollback of transactions
    • It has sophisticated locking to allow a high degree of concurrency.
    • It has a rich set of diagnostics to tune and fix problems often without bringing down the database

    .. albeit without the built in front end.

    Of course Access isn’t in every version of Office, but the kind of users who love this sort of tool often have ways and means of purchasing what they need (they usually have the cheque books!).  So don’t assume this user friendly tool won’t turn up on a desktop near you. SharePoint integration might mean that the more sophisticated users will park their data on centralised servers but if they loose their data or they run into trouble then who are they gonna call..  the dba!  So you might want to keep an eye out for what the Access team are up to whatever your own views are about how good it is.

    Technorati Tags: ,
  • Dimensional Modelling

    This simple design, popped up in my inbox today asking for advice…


    What you need to understand about this diagram is that there is a concatenated key (keys are in BOLD) on each of the orange dimension tables, with one part of that key being CompanyID. The exception is dCompany which is just keyed on CompanyID.  You can imagine that this is a ledger fact table for multiple sub companies in a large organisation (BTW there is a time dimension as well but I want to keep the post simple).

    I can understand that each product, location and so on belongs to particular company and that there might be occasions when you might need a complete list of the locations, cost centres etc for that one company. This design allows each of the dimensions to yield that list in a simple 2 table query (by joiing directly to dCompany on CompanyID).

    The fact table has no redundant columns because even CompanyID is needed to link to the company dimension.

    So all I would do is add in surrogate keys, i.e. a key with no relevance to the data in the dimension which would be the primary and clustered key of each of the dimensions..



    For example in dProductGroup the ProductSK of 1 is just a made up number – it might refer to CompanyID4, ProductID20, rather than choosing ProductSK on some transformation of the two business keys.  This will make queries faster and speed up cube processing if you plan to put analysis services on top of this.  In my projects I nearly always use surrogate keys and I simply use the identity property on the column that will be the surrogate key on the dimension to do this, reserving 0 for a an unknown row e.g. unknown Location.

    Whether or not it’s worth doing this is going to depend on 2 factors:

    • The shelf life of the solution, if it’s just a quick tactical fix then go with plan A, but remember these kinds of thing can hang around far longer than originally planned if they prove to be useful.
    • The size of the data and the performance required. 

    A final thought is that the design in the second diagram will also reduce the size of the fact table in SQL Server 2005 and standard edition of SQL Server 2008, but data compression in SQL Server 2008 enterprise edition will remove this advantage.

  • Work Experience

    A lot of teenagers are on work experience at the moment, and as I don’t have any of my own children I got to be cool uncle for a week and took my nephew Harry to spend a week inside Microsoft.  We run a full week course of lectures and practical exercises along the lines of the apprentice, to teach them presentation and influencing skills,as well as basic selling technique.  They also got to play with our latest stuff and I managed to blag a new HP TouchSmart PC for Harry to try while he was staying with us.  This cool gadget allows some of the surface technology to work in windows 7, although it relies on touch rather than the cameras in Surface to work.

    If you haven’t seen one of these you can watch Harry trying it out …

    Dell also have the Latitude XT laptop with similar capabilities which would be great especially if (a Big IF) Office 2010/ SQL Server 2008 r2 has special bits in to use this interface for slicing and dicing!

  • Probably the most unlikely phishing e-mail in the world

    I just got this..

    Tax Refund Notification

    After the last annual calculations of your fiscal activity, we have determined that you are eligible to receive a tax refund of 188.50 GBP. Please submit the tax refund request and allow 6-9 days in order to process it.

    Click Here to submit you tax refund request

    Note : A refund can be delayed a variety of reasons, for example submitting invalid records or applying after deadline.

    Yours Sincerely

    HM Revenue & Customs

    | © Crown Copyright | Terms & conditions | Privacy policy |

    ..and I nearly fell of the chair laughing.  The idea of HMRC informing you of a refund is many very unlikely, and doing it via e-mail is stretching credibility to the limit.

    Of course it’s another phishing exercise and takes you to a site Romania (which I have removed the link to) and was identified as such by our systems and sent to my junk mail sans links and pictures.

    My point is that if it’s too good to be true it is.

    Technorati Tags: ,


  • SQL Server 2008 on Windows 7 / Windows server 2008 r2

    I haven’t had many problems myself when installing SQL Server 2008 on either Windows 7 or Windows server 2008 r2 but there are a few things to watch out for which I have shamelessly lifted from KB955725:

    You should install SQL Server 2008 sp1 as this is the supported verion on htese new operating systems (they included fixes specific to a couple of issues including the fact that Windows 7 doesn’t use the Required Kerberos property to determine if Kerberos is enabled for a network name resource). 

    .Net Framework. You’re going to need the .Net framework 3.5 sp1. If it isn’t there then the SQL installer will do it for you in Windows 7 but it will then restart the installation once this has been done which may look a little odd and confuse you.  In Windows Server 2008r2 you should add the application role using server manager, servermangercmd.exe or the new Deployment Imaging servicing & management tool (DISM) prio to installing SQL Server.



    I mention this because I know it’s easy to be swamped by KB notifications, a lot of you are trying these new operating systems, and because the SQL Server install is very picky when it runs.  This is not a moan, if it installs properly it’s working and you can rely on it which is what databases are all about.

  • Report Builder Licensing – you do need to

    I have done a couple of posts on licensing SQL Server  before , but I just got off the phone from my good friend and MVP Simon Sabin and I realised I need to clear up the issues around Report Builder 2 licensing.

    Simon has noticed that you can build reports (which are stored as .RDL) files and run these reports in Report Builder 2 on a standalone machine without any reference to a SQL Server:

    • Report Builder 2 is still available as an installation (.msi ), here, as part of the SQL Server 2008 October feature pack.  All other versions of Report Builder ;Report Builder 1, and Report Builder 2 in SQL Server 2008 sp1 are ClickOnce installations from Report Manager which comes with the Reporting Services component of an install of SQL Server.
    • The data could come from any relational source e.g. MySQL, Oracle etc.
    • you don’t need a reporting service service to run a report in Report Builder and so he wanted to discuss how this tool was licensed.

    However this doesn’t get around the fact that you have to be licensed to connect to SQL Server to use these tools , and this is made quite clear (well I understood it) in the licensing screen that comes up when you install Report Builder from the install file..

    Microsoft SQL Server 2008 Report Builder 2.0 for SQL Server 2008
    PLEASE NOTE:  Microsoft Corporation (or based on where you live, one of its affiliates) licenses this supplement to you as part of and for use with your validly licensed copy of the Microsoft SQL Server 2008 software (for which this supplement is applicable) (the “software”).  This supplement is part of the “additional software” and the license terms for the software apply to your use of this supplement.  You may not use this supplement if you do not have a license for the software.  The term “software” does not include Microsoft SQL Server 2008 Express or SQL Server 2008 Express Advanced Editions.

    I have to say that even if this wasn’t a license violation it would be a messy implementation with users saving report definitions (the rdl files to a share) with each having it’s own connection string all of which would make for a lot of management, a few security headaches and a rather poor user experience all for the cost of copy of SQL Server standard.

    For detailed queries on this or any licensing question please contact Emma Healey (aka “Lady Licensing”)

  • No SQL

    One of the fundamental dichotomies in development is the relational world of the database and the object orientated world of services, which sadly spills over into developers and DBA’s not always being the best of friends.  Developers don't always see the need for a relational database, and the expense of coding to use one is a frustration even if the database engine itself is free.

    I picked up this article on the NoSQL movement (I track SQL on tweets Twiiter) and it got me thinking would the the world in general and the IT industry be better off without it?

    So what does a true relational database give you that you can’t get anywhere else:

    • Referential integrity, gives you the guarantee that the customer and product on that sales order detail do exist. 
    • Transactions mean that if I pay James fifty quid it will only leave my account if it left his.
    • Relational structures mean that you only have to change a customer’s details in one place in the system.

    Of course not every system wants or needs these capabilities, but the image of a database as a safe haven for data has seen their use extended into all sorts of areas where another data storage engine might better meet this purpose.  A good example is Microsoft Exchange which doesn’t use SQL server.

    Transactions can also make web sites appear slow as the database has to commit, and in the world of business intelligence I want all of the referential integrity but not much else as my whole refresh of the data warehouse is one transaction as far as I am concerned. 

    So I am pleased to see some push back against SQL – it definitely isn’t the answer to life the universe and everything, equally the relational database has survived the PC revolution, the millennium, and the internet, virtualisation.

    The next big thing for architecture is of course the cloud, and for Microsoft this means SQL Services, part of the Azure platform. What is interesting here is that Microsoft shied away from a REST-ful database and has essentially implemented this service in such a way that it behaves exactly like SQL Server on your PC/Server (BTW I have a short interview on this on TechNet Edge).  Why did this happen, essentially pressure form customers. I am also sure one of the many reasons Oracle bought Sun was because of not despite MySQL  

    So I don’t think it’s time to consign SQL to the recycle bin just yet, but I would be interested on what you think and perhaps I could setup a Live Meeting on this if I get enough interest.

    Technorati Tags: ,,,