Business Intelligence – ala Microsoft 2010 – Part I

For those that haven’t heard all the news yet … this year is going to see some of the most exciting product launches from Microsoft; particularly in the Business Intelligence space.

Now, Business Intelligence is a far bigger
concept than simply a sexy front end tool, or a super quick ETL tool. Rather it is that sum of the parts that creates a magic whole, and hopefully helps business people grow their businesses.

To support this, the Microsoft Business Intelligence picture includes three main products, all of which are getting new versions in 2010; namely

  • Office 2010
  • Sharepoint 2010
  • SQL Server 2008 R2

I’ve had the opportunity of playing with all of these products in pre-Beta and now in Beta releases, and have seen them improve with each new release. The key new features in each of these products that have got me the most interested include:

  1. Excel Slicers for Pivot Tables
    1. No more horrible unrelated drop down lists in Excel. Slicers really open up your data in an intuitive and ‘touch-ready’ way.
  2. Reporting Services Map Control
    1. Wonderful method to display geographical information, within the context of Reporting Services. No custom code. No messy data connections. Flexible, and very useful.
  3. Master Data Services
    1. One of the features that gets very little attention in the SQL space; but has great potential to help businesses manage their critical business data. No more messy Excel spread sheets, or phone calls to IT to update that mapping table.
  4. Decomposition Tree
    1. Still one of the most powerful visualisations for performing root cause analysis. Available within PerformancePoint Services. (Would love to see it introduced into Excel as well…)
  5. Powerpivot
    1. No Microsoft Business Intelligence blog would be complete without a reference to this wonderful in-memory tool. A plug-in into Excel 2010 as a fully-fledged modelling tool, as well as a Sharepoint feature allowing for online access and sharing. A must have for every financial manager J

These are just a handful of the vast array of features that are coming from Microsoft this year. I would encourage you to download the public betas and start becoming familiar with these products.

Sharepoint 2010 – download

Office 2010 – download

SQL 2008 R2 – download


, , ,

Leave a comment

Reporting Services 2008 R2: Lessons learnt from the map control


So, SQL Server Reporting Services 2008 R2 now supports maps as data layout controls. This allows us to create reports such as the one indicated below, with no custom development, natively within Reporting Services.


This opens up massive possibilities, as it supports the display of geospatial data in one of three map formats:

  • Built-in maps
    • Maps of the USA are supported
    • I saw a demo of world maps at SQL PASS, looking forward to CTP3 to seeing them in real life
  • ESRI
    • Any ESRI shapefile can be used. This is particularly powerful as you can represent data for your country down to any level supported by the particular shapefile employed at the time.
  • SQL 2008 Geospatial
    • This is the most exciting option for me. While SQL Server 2008 introduced GeoSpatial data types in the database, there was no simple method to display these shapes to users. So, now it is possible to expose geography and geometry data types visually through reports.
  • Lesson Learnt

    With that preamble, I have been experimenting with the ESRI shapefile option. I needed to employ this approach as I wanted to represent country level data for the world (and the CTP2 doesn’t support non-US maps out of the box, really looking forward to CTP3). I was able to develop the report through the wizard, and assign the relevant data fields to the relevant shapefile fields with no problem. However, when rendering the report, I received the following error:

    System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.
       at System.Web.HttpRequest.GetEntireRawContent()
       at System.Web.HttpRequest.get_InputStream()
       at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
       — End of inner exception stack trace —
       at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
       at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response)
       at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)

    Turns out that the Report Server service limits the HTTP Request size by default. Not exactly sure what the default limit is, however it is definitely smaller than the 6Mb required for my shapefile. To fix this setting, simply update the httpRuntime tag in the web.config file in the ReportServer folder. Specify a maxRequestLength attribute to the size required and you’re in the game. So the tag should look something like:

    <httpRuntime executionTimeout=”9000″ maxRequestLength = “1024000” />

    Watch this space for SQL 2008 R2 lessons learnt in the real world, as I use CTP3 in a prototyping environment over the next few weeks.


    Leave a comment

    SQL PASS: DAX, the language of PowerPivot

    PowerPivot, the topic that has been getting all the big attention here at PASS, has introduced ‘yet another’ expression language to define calculations. So, introducing DAX, Data Analysis eXpressions.

    The idea behind this language is to provide OLAP type query functionality to the Excel end user. So the basic syntax looks and feels exactly like standard Excel functions. A set of Excel functions have been implemented (80 in total), with additional functions from the Vertipaq engine to allow OLAP functionality.

    Ed Note: The additional functions in DAX actually come directly from the VertiPaq engine. Meaning that it is not a wrapper for MDX, it is actually the language used to define and execute the calculation within the engine.

    DAX can be used in two places

    • Calculated Columns
      • Similar to creating measures in fact tables, or in the DSV in SSAS. Basically used for line-by-line calculations. These values are calculated and stored at design time, and not recalculated at execution time.
    • Measures
      • Similar to Calculations in SSAS. Calculated at run time, in the context of the current pivot table or query. Best place to define ratios or custom aggregations that don’t make sense in a line-by-line method.

    IMHO, the single most important aspect of this language is the underlying relationships defined within the PowerPivot model. As most calculations require some form of navigation between tables, whether it be date calculations or aggregations over groups.

    The basic groups of functions that are supported include:

    • Excel Functions
    • Relationships
    • Aggregation
    • Table Functions
    • Time Intelligence

    The Time Intelligence ones are by far the most fun ones for me. it is possible to define MDX-type calculations including Year on Year growth, Period to Date and Parallel Period navigation. A bit of a different model though, as the “time dimension” is assumed based on date fields. Only limitation is that it assumes calendar time.

    e.g. YOY Growth looks like

    Fact([Sales]) – Fact([Sales])(DateAdd(Time[Date], -1, YEAR)

    Overall impression is very positive. Full descriptions available at


    Leave a comment

    SQL PASS: Dashboards in PerformancePoint Services 2010

    So, PerformancePoint is slowly but surely growing up into a fully fledged dashboarding environment.

    Key new feature that I have seen today is the inclusion of the Sharepoint Connection Framework within PerformancePoint 2010. This basically means that the filters and links between dashboard items are implemented through the Sharepoint glue as apposed to the current custom PerformancePoint 2007 glue.

    This opens up a number of new possibilities for filtering dashboards. Including:

    • The ability to automatically tie the current user’s login to the dashboard elements. Meaning that finally dashboards can be “seamlessly” targeted to the current user with no fancy filter logic.
    • Also means that it is possible to link to practically any Sharepoint Web Part as this is a fully supported sharepoint interface.




    SQL PASS: PowerPivot Server Best Practises

    Just sat in a fascinating session with Dave Wickert and Denny Lee, chatting about the deployment considerations for PowerPivot Server.

    Very entertaining, I got my first session swag, and had a lot of laughs.

    So, they ran through a large number of points, and I’m quite sure that they will produce full whitepapers with all the points… here are my personal top 6 lessons


    1. PowerPivot plugin within Excel client is an instance of the Vertipaq OLAP engine.
      1. This engine defies some of the basic laws of traditional SSAS, primarily  aggregations simply don’t exist in PowerPivot.
    2. To minimise the Excel file sizes, check this cool trick…
      1. While this runs in memory, it also has a disk based version of the data, which can be found in User Appdata Local Temp IMBIXXX
      2. Look out for the DICTIONARY files in this folder, these files store the “dimensional attributes”
        1. If you have excessively large DICTIONARY files on attributes that are not used, simply drop those attributes


    1. Read the manual when installing! There are a number of server components involved in PowerPivot, so there are many dependancies, including
    2. The Powerpivot Service can be located in all sort of places within the Sharepoint Server farm
      1. and you don’t necessarily need kerberos!! Sharepoint 2010 introduces Claims Based Authentication, which keeps the user token within the Sharepoint farm.
      2. Recommended to use the New Farm option when beginning, all configuration is handled for you.
    3. Most important aspect of capacity planning is RAM.

    General (and possibly most important…)

    1. Powerpivot does not replace SSAS
      1. Although Powerpivot happens to use SSAS as it’s internal engine, this was done for calculation abilities, and not as a replacement roadmap for ‘traditional’ SSAS. Yes, all you BI professionals out there, we still have our jobs!

    Leave a comment

    Data Compression + DW

    Compression Overview

    I’m sitting in the SQL CAT Compression session, and it’s fascinating. It’s really nice to get some insight into what types of compression to use in the real world.

    To quickly recap, there are two types of compression:

    1. Row Compression

    This type is a storage option. Basically, SQL will only use the number of bytes required at any point in time to store the data that is currently in the row. e.g. an INT data type field with a value of 1 would normally uses 4 bytes all the time. When row compression is turned on for this field, only 1 byte is actually used. In fact, if the value was 0, no bytes would be used. Nice option as reads do not require additional CPU cycles.

    2. Page Compression

    This is the more interesting version of compression. Zip-type technology is used as patterns are used in the page, and these patterns are stored in a central dictionary with pointers in the data itself to these shared patterns. The trade off here is that any reads / writes will require additional CPU cycles to uncompress the rows required. Although, the CPU overhead is between 5%-10%, which isn’t terrible.

    Compression Lessons

    So, seeing as I approach the world through Data Warehouse (DW) glasses, let’s summarise some of the best practises in relation to compression in a DW:

    1. Compress all tables in your Data Warehouse. Using PAGE compression.
      1. Thinking here is that the majority of the workload in DW is read operations. (i.e. no ongoing CRUD operations that would cause massive CPU overhead)
    2. To estimate the amount of space that will be saved, use the [sp_estimate_data_compression_savings] stored procedure.
    3. DW tables should be compressed in offline mode.
      1. Thinking here is that the DW itself has quiet times where users are not querying your tables. Especially in scenarios where Analysis Services is implemented, where users actually never hit your tables directly anyway.
      1. Recommended, and if SQLCAT say so, who am I to argue 🙂
    5. Order of table compression, well start with the smallest table first.
      1. Thinking here, is that you need additional space when compressing a table, roughly table size plus compressed table size. So, as you compress tables, you are progressively saving space in your DB, and by the time you compress the really big tables, you should freed up enough free space as you’ve gone along.
    6. When reclaiming all the space you’ve saved, which is normally through a DBCC SHRINKFILE, make sure you run a REORGANIZE on your indices.
      1. As the shrink algorithm is not aware of indices and introduces fragmentation.
      2. There is talk of making the shrink algorithm cleverer in time, let’s see how that goes.
    7. When bulk loading, try bulk load into an uncompressed heap. Then use a CREATE CLUSTERED INDEX statement to create the Page compression.
      1. Significantly quicker than inserting into a table that is already marked for compression
    8. Transparent Data Encryption (TDE) and Compression play well together.
      1. i.e. TDE has basically zero impact on compression performance.

    Leave a comment

    SQL PASS – Morning I

    So, I’ve just experienced my first morning at SQL PASS. First impressions have been very good. The vibe is very similar to Tech Ed South Africa, about 2,000 people registered, and we’re staying in a hotel just next to the Convention Centre. I’m so glad we’re indoors, it was a chilly walk this morning 🙂

    So, key lessons so far:

    1. SQL Server is definitely not a simple departmental DB any more. They demonstrated a rack server live on stage with 192 processors. ridiculous processing power.
    2. Visual Studio 2010 is very cool. Looks good, everything is now a slick Midnight Blue.
      1. Maybe that’ll result in better looking applications as an end result.
      2. Also, it supports Data Tier Applications. This is a project type which supports deployment of databases using a file type called a DACPAC. Looks like a great way to deploy changes from dev to live. Have we really seen the end of the humble SQL Scripts?
    3. Sharepoint 2010 supports publishing of data directly from any document library through ATOM feeds. This is fantastic for PowerPivot, as it is now possible to create full reporting solutions without a database?
        1. Ed Note: Not sure if it’s a good idea… but is an architectural option, and it’s always good to keep your choices open.
    4. SQL Azure is close. New release coming through mid November. It’s getting closer and closer to on-premise SQL, in that you can now issue CREATE DATABASE statements directly from SSMS, instead of provisioning DBs through the browser.
      1. Also possible to synchronise on premise and hosted DBs directly through SSMS. Interesting concept. A bit of a parallel to merge replication, just when you think you know it all, MS introduces another method.

    Leave a comment

    The States – The trip part V

    From a humble B&B to the Sheraton. From East coast to West coast. From Planes to Trains to Automobiles. That’s been the story of the day, good thing I can handle change 🙂

    And what a day it’s been! It’s about 18:15 Seattle time as I sit and start to type this, and we’ve been up since about 01:00 Seattle time. And we’re on our way out at about 19:00 to have dinner on the Space Needle. I will sleep well tonight.

    Anyway, before I head off to eat a little, let me recap the day…

    As I mentioned, I woke up at 4:00 a.m. NYC time, with Ryan shouting (well, it sounded like shouting… anything sounds like shouting at that time of day!) After lying groggily for 5 minutes, I figured I’d better get the day on the go. After finalising our packing, we headed off to use the public transport system of NYC to get from Brooklyn to JFK. About 20kms or so. So, yes, he were literally walking along the streets of Brooklyn with our suitcases in the pitch dark. And despite the Johannesburger in me being incredibly nervous, we were actually in no real danger at all, as there were a whole bunch of people up at that time of day.

    It took 3 trains, and about 2 hours of subway time to get from our stop at Avenue H to get through to the Air Train at JFK. I really did enjoy the Air Train. As the name suggests, it runs on air so it was seriously smooth. Also, it’s seriously necessary. JFK is massive. There are around 8 terminals. And each terminal is about twice the size of OR Tambo. (Each terminal has around 30 gates…) When taking off, I counted the number of planes in queue behind us… 13. Yes, 13 planes at any point in time waiting to take off just on one runway. busy place.

    The plane ride itself was awful. I felt like tuna. Shredded, squashed, ugh. 6 hours. The only highlights of the flight included:

    • Wireless internet access on the plane. Absolute miracle. Loved it. Until my battery died after 10 minutes. (who needs to charge your laptop before a plane ride right ?!?!)
    • The music available on board. I got to sample the new Dave Matthews as well as Michael Buble live in NY. Both fantastic albums.

    So, we got into Seattle around lunch time. To sunshine, and beautiful views of the city in autumn. The city is very much like Johannesburg in that the suburbs are tree lined, and they looked really nice from the air. The sunshine has since disappeared. The clouds are in full force, and I’ve experienced my first walk around the city in drizzle. I feel like a local. Fortunately, our hotel is literally 1 block from the CC where the conference is being held.

    So, bring on SQL PASS. Loads of talks lined up on all the latest and greatest in Microsoft BI. Will post impressions as we go along.

    Leave a comment

    The States – The trip part III

    So, day 2 in NYC has come to an end. I’m parking in our B&B, putting my feet up after really walking Manhattan flat.
    Well, kind of flat. So, let’s recap…

    The day all started with a fascinating breakfast around a communal dining room table at our B&B. Met a professional clown, who arrived at breakfast with a banana skin for a nose, and calling himself Kevin Schmevin. Fortunately his prosthetic nose came off with a sneeze. Very entertaining.

    We then headed out to Manhattan to see what we could see. The general idea being to get off at the first subway station, and simply walk our way up to central park. So, we saw some really cool landmarks like…

    • Brooklyn Bridge in the daylight. Much cooler view.
    • China Town. Yes, all the signs are in Chinese.
    • World Trade Centre. Absolutely massive area. A couple of city blocks. Really not much to see though as it has become a construction site. Seems like they’re going to build on tower, let’s see what happens.
    • Statue of Liberty. Nice statue, for time reasons we didn’t do the ferry though. Interesting area around battery park though, loads of war memorials. humbling.
    • Wall Street. Pity we hit it on a saturday, I didn’t see any high power investors running around.
    • Charles Schwab bank. Interesting because it’s one of the Wall street banks that got wiped out in the last year. And yes, the building is definitely deserted.
    • Greenwich Viallge. By far my favourite area of NYC. Very much like Melville + Cape Town + NY magic sauce. Had lunch at a Mexican restaurant. And took a stroll along a ‘park’, which was basically an old upper level train track that had been overhauled with plants. Ingenious idea. It was on this park that Ryan had some photos taken of his feet. He’s gonna be famous. We also saw a bunch of guys who had a band setup on their balcony. Cuban Jazz. Fantastic. Wish I could get away with that at home without my neighbours shouting at me.
    • Oh yes, only negative to Greenwich is the high percentage of people living ‘alternative’ lifestyles. This is really a concern because it means that Ryan and I were presumed to be a couple. It even went as far as someone offering us a gay wedding. And yes, we declined, much to our wive’s approval 🙂
    • Central Park. Huge, Humungous, Tremendous. Full of people having a great time; from dads playing ball with their kids to cyclists to runners to Japanese tourists. Wonderful area. Saw a performing group doing some break dancing, very impressive. Also got to play checkers with Ryan at the famous Chess and Checkers conservatory. I beat him, convincingly 🙂
    • Apple Store. Wow. Our local shops are pittance compared to this shrine of beautiful technology. Managed to walk out empty handed. But only because I went in with my Microsoft jacket on.
    • Fao Schwartz toy store. 3 level toy store with almost every conceivable toy under the sun.
    • Pretzel vendor. Not a highlight. Just because it’s big and sold in NYC, doesn’t guarantee that it’s nice. ugh.
    • 5th Avenue. Yes, it’s as snobby as it sounds. Everything from Tiffany and co. to tuxedo weddings. I just felt good about life walking down this street. Bumped into a bunch of fire engines though, so we changed course… will check the news in the morning to see what that was all about.
    • Dinner in Brooklyn. Had it at a French Canadian place a few blocks from our B&B. Absolutely beautiful.

    So, 30 hours left in NYC. Gonna sleep for 8 of them, and see what we can fit in tomorrow.

    Leave a comment

    The States – The trip part II

    Arrived in NYC last night. Definitely right up there with the most surreal experiences I’ve ever had.

    Firstly, Ryan and I were totally sleep deprived after spending 24 hours in a steal metal tube. But I wasn’t gonna let that simple fact get in the way of us and our first walk around times square. So at about 8PM local time, which is 4AM on our body clocks, we stepped out to catch the subway from our humble B&B in brooklyn south, to get into central Manhattan.

    First hurdle… tickets. Man, there are just way too many options. Took about 10 mins just to decide which ticket to get. So, once we had made it through the turnstiles with our well acquired metro card, we shot up to the train station.


    Riding a subway is a blast. In one train carriage we had people from almost every walk of life. From business people in suits to people with their bicycles. Come on SA, let’s get this thing working at home, it’s incredible!!

    Crossing Brooklyn bridge was a bit underwhelming. Way to much reflection from the windows to really appreciate the view. Hopefully we’ll get a better view today. See the shot of the subway below, try look past the reflection of the girl and the bicycle, and you’ll see the lights of the city.


    40 minutes or so of a tube ride later, and we get out at 42nd street, and hit Times Square.

    I have one word for NYC proper… speechless.

    There is no way I can describe the scale of the buildings, the people, the energy, the vibe. It seriously felt like we were on the set of a movie (and in fact, there were a few camera crews running around last night)

    So we saw Times Square, which is such experience in it’s own right. Just to sit and watch the people and the lights. Some highlights… (even Windows 7 made an appearance)

    • Hard Rock Cafe NY (yes Dad, I found you something nice!!)
    • Levis NY
    • Starbucks, it tastes just as good in NY as it does in Seattle 🙂
    • Europa Cafe (incredible chicken sandwiches!!!)

    IMG_9445 IMG_9448

     IMG_9460 IMG_9466

    So, today we hit it again. Let’s grab some breakfast before hitting the streets!

    Leave a comment