Latest Entries »

Windows Azure – DataMarket

As I look into the new technologies that is currently available in the cloud something which has peaked my interest is the discovery of the  Windows Azure DataMarket. The DataMarket place is essentially a store for public and corporate domain data that is available and can be consumed from client tools such as Excel PowerPivot. Example of data feeds that are currently available are Crime trends across US states, weather trends over the last decade, Real time Foreign Exchange rates and Social networking statuses and comments (coming soon).

I can foresee that this would be a great tool for a lot of industries, organizations, and also individuals in which they are able to  easily conduct performance benchmarking against public domain data, or view the changing landscape in the global market place and trends that are appearing.  Of particular interest would be those in marketing and sales.

For the BI professional it allows us to demonstrates what is possible with the current Microsoft BI platform i.e.  promoting self service BI and mashing up data from different data sources outside of the normal end to end data warehouse solutions (which the platform does very well already).

Fingers cross Microsoft gets more data providers.


Recently I’ve been taking a deep look into Master Data services which is Microsoft platform to tackle the master data management problem within an organizaiton. The product is built upon the code base which was acquired when Microsoft took over Stratature.  

Without going into each and every detail of the product which can be done through the links below, I would like to go through the strengths and weaknesses of MDS as a platform for managing master data.

The strengths are:

1) Generic Data modelling:  The data modelling tool is flexible enough to cater for a wide variety of master data that exists within an organization. In comparison other products may focus on either customer or products  data management.

2) Compliments Hub-Spoke architecture: MDS is a very open master data management platform. With the ability to be accessed via the Database layer or the range of Webservice API’s that are available. This makes for a very flexible solution in which MDS can serve as a HUB for obtaining and providing data from upstream and downstream systems using the preferred design pattern.

3) Integration with existing Microsoft technologies: In conjunction with the above, MDS integrates and leverages with existing Microsoft technologies such as SSIS, Biztalk, and Sharepoint 2010 for workflows, which is great for those organizations that have also invested into those technologies

However there are a couple of limitations with the

1) Stewardship UI: The UI that is shipped with the product is functional, but it is by no means up to the standards in which it is expected from microsoft especially in comparison to Sharepoint 2010. It good for the one-off updates but will be combersome for those that are heavy users which might be use to excel. However there is currently a 3rd party product called Master Data Maestro which fills this gap and provides additional functionality such as merging versions, and cut and paste to child and sybling hierarchies which is currently not present within the web UI. I would recommend taking a look at this product to address any issues with regards to functionality of the existing interface.

Below is a screenshot of the product:

2) Omission of deduplication engine: One of the main processes of MDM is the detection and removal or duplicates, which is completely omitted within the MDS platform. Yes it can be facilitated via SSIS or the API’s however this would mean a separate area for reporting on these items which is outside of the MDS platform. However it has been noted that it will be available in the next version of MDS.

 Thats just a little of my thoughts on the MDS product, just remember that MDM is not just about the technology but the success of managing master data within an organization requires a program in which processes are defined and agreed upon from the involved stakeholders.

Some useful link are:

Master Data Services Team Blog (

Architecture of MDS @ TechEd 2010 (

One of the new features bought into PerformancePoint Services 2010 is a data visualisation tool called Decomposition Tree.

It allows for drilling down from a report or scorecard to investigate how a high level number was made up.

The below screen shot shows that the $11m sales from Europe is made up from $5.1m in the UK, $3.9M France and $2.3M in Germany. Drilling down further from the UK business unit $2.9m of it was from the first half of FY04.

To view a decomposition tree the client browser must support Silverlight 3.0 and above.

To enable within a PerformancePoint Services

  • For scorecards – the actual and target values must be driven from an SSAS Cube, and calculation-type set to ‘Data Value’.
  • For Reports – the report needs to be built against an SSAS cube from Design mode and not in query mode.  

Opinion on Microsoft PowerPivot

Recently I have been playing around with the new features within SQL Server 2008 R2 on my BI demo machine. One of these have been the much touted PowerPivot to enable Microsoft’s much publicized strategy for enabling self-service business intelligence.

 There are essentially two parts to PowerPivot the excel client which is the authoring tool to create PowerPivot applications and Powerpivot through SharePoint2010 which allows the PowerPivot application to be shared on a portal and the data set being consumed through other reporting tools such as PerformancePoint services and Reporting Services.

 Here are some screenshots

Initially when I heard about the Self-service BI strategy from Microsoft I have to admit that I was a little skeptical in the strategy as most of us in the Data warehousing profession are striving to have a single source of truth and to capture important business logic within a central source and to remove this important business logic from documents such as Excel.

Having played around with it for a little while and also talking to some heavy excel users, it has dispelled my skepticism on the product and I really think that it is a powerful tool for business users when coupled with the rest of the Microsoft BI Suite of products.

So why the change of mind? Well there are a few reasons which I have experiened with my dabbles with the product:

  1. PowerPivot adds value to your existing initial BI investment: With hundreds of SSRS reports out there in the organization wilderness all interfacing cubes and the data warehouse a lot of users still ask ‘If only I can get information from report xyz and join it to abc report CEO’.Traditionally this would mean a new change request in which the IT division will need to write new reports to cater for this request adding to the hundreds of reports already available. Not only this costs money but more importantly wastes time and in my opinion not very efficient use of resources.With PowerPivot this can be addressed. Coupled with exposing SSRS reports through Atom feeds and assigning relationships on the data sets within the PowerPivot application users are able to address these queries themselves leaving IT to get on with new projects. 
  2. POC to demonstrate and convince the power of BI: There are some organizations that are still not willing to invest heavily into BI, and are relying on spreadsheet technology within their teams to conduct performance monitoring on their organization. This might sound ridiculous but it happens.  Thus PowerPivot enables the end-user within these organizations to build BI applications themselves to use and share with their colleagues but to also demonstrate the power of BI in terms of time saved in building accurate reports, and the value provided when information is provided from a single source.
  3. Vlookup nightmare: This is not a specific BI issues but having worked with many excel users in the past, one of the things they do a lot of is use Vlookup in order to match data across spreadsheets or datasets. Well with the use of data relationships a lot of these unnecessary vlookup’s are removed as they are able to ‘connect the dots’ through explicitly building relationships, and then filtering on the total data set.  

As a wrap up PowerPivot is a very powerful tool which enables users to get access to information that they already know, in addition I really feel that it doesn’t take away the importance of a BI strategy that is already inplace, but actually adds to it if used in the correct mannar.

Thats my $0.02 let me know of your experiences.

 Some good links on PowerPivot are the following:

 Microsoft’s technical explanation of PowerPivot

 PowerPivot Excel add-in download

 PowerPivot – Sample data

 PowerPivot – Creating your first PowerPivot application Tutorial

This might be stating the obvious however I’ve been using SSRS in native mode for a while now and was scratching my head for a little bit when I kept on getting the ‘Reporting Services login’ dialogue box whenever I try to deploy a report from BIDS on my VM machine, and the simple answer to resolve the problem is WRONG DEPLOYMENT PATHS.

 So to avoid my mistake (and also for my personal record) the correct setup of the Reporting Services deployment path(s) within BIDS is the following :

SSRS deployment properties in Sharepoint integrated mode


  • TargetServerURL : URL for the sharepoint site collection
  • TargetDataSetFolder : URL for the document list to hold the shared data set
  • TargetDataSource : URL for the document list to hold the shared data source
  • TargetReportFolder : URL for the document list to hold the individual report items
  • TargetReportPartsFolder : URL for the document list to hold the shared report parts.  

Also just a side note, there are some nice new features within SQL Server R2 for reporting services which I will try and address in future posts which include Shared Report Parts, Shared Data Sets, and the integration with SharePoint 2010.

First ever post

The purpose of this blog is really to share what I have learnt in the field as a Microsoft BI consultant, whether it is a difficult problem,  stating the obvious or new findings. Apart from techy type of posts I hope to put some learnings from my MBA school which I am currently undertaking.

So sit back and relax and stayed tuned for future posts.