Author Archive

Avanade on Tech Ed Australia 2010

July 15th, 2010
Comments Off

A few days ago I got asked by the IM Director for Avanade Australia Lionel Gomes Da Rosa whether I would like to co-present with him on Tech Ed Australia 2010 on a very interesting topic – “Budgeting, Forecasting and Performance Management with the Microsoft BI Stack”.

I have had a few planning and forecasting implementations in the past (one of which got presented on the Microsoft BI Summit 2007 – not by myself unfortunately) and I am looking forward to the chance to co-present at this exciting event this year. It will be a 300 session (meaning that it will not be an intro and the audience would need some technical knowledge to grasp the concepts in their entirety) and this makes it even more attractive from my point of view as it will be targeting devs rather than the general public. Nothing wrong with the general public, of course – just that I am a dev at heart :)

See you there!



PivotViewer meets World Cup 2010

July 5th, 2010
Comments Off

Just got this link in my inbox and thought the BI community may be interested (especially if you live in Germany, Spain, Netherlands or Uruguay – the semi-finalists of this year’s Fifa World Cup) in this demo of thenew PivotViewer SilverLight control:

Even if you are not a soccer fan, you will surely still find it very interesting.

The official site where you can download the control is here:

Visualisation , ,


Passing database names to SSIS stored procedures

June 29th, 2010
Comments Off

In the rare cases when we use dynamic SQL and want to use a database name in our code, we are better off avoiding hard-coding them. Unfortunately, I could not find an easy way to access a connection manager’s database name and on my current project the catalog name is not in the SSIS configurations XML file. Therefore, I had to resort to a little trick to pull the database name out and pass it to a stored procedure. In brief we can do the following:

1. Create a user variable database_name

2. Create an Execute SQL Task using the connection manager we want to get the database name from, which does:

 SELECT db_name() AS database_name

3. Map the Single Row result set to our database_name variable 

4. Place the task created in the previous step before any components which would be using the variable.

5. Pass the variable to our dynamic SQL stored procedure



There we go – a stored procedure configured in the SSIS package configurations – a bit better than just hard-coding the name.

SSIS , ,


PowerPivot DAX Measures in a Data Source

June 8th, 2010
Comments Off

OK, my doubts aside, this is my first post about PowerPivot. Obviously the demand is high and the perspectives bright – so here we go…

There are a number of posts about using PowerPivot as a datasource in PerformancePoint and Reporting Services. You can find two links below (both at TechNet) and I will not repeat the content there:


There you can find a detailed step-by-step approach to using published to SharePoint PowerPivot workbooks in your reports. One thing which is not explained, though, is that you can also use any published DAX measures as report data sources, as well. They appear as physical measures in the PowerPivot Analysis Services instance and can be directly used in the reports. I found this fascinating, as now we can actually integrate even user logic into our reports.

In example, today I was working on a small demo of PowerPivot and I created a PowerPivot report integrating a relational data source and an OLAP data source from two completely different systems, which then got related to each other by State/Province . After that, I created a DAX ratio measure, which showed some relation between the Sales measures from each database. After publishing the workbook to SharePoint, I opened Report Builder 3.0 and to my surprise (well, I expected to see only physical measures for some reason), I was able to pull the DAX measure which I had just created and beautify the map of the USA based on its value.

The integration between the various components in the Microsoft BI stack is continually improving and with the latest few versions of the various components (labelled 2010 or R2), we are being empowered with richer and more powerful tools, covering a larger array of users – proving that the toolset is the best out there for both enterprise-level and relatively smaller customers.

PowerPivot ,


An ETL Framework from BI Monkey

May 28th, 2010
Comments Off

Today I found out about a cool ETL framework being developed by my colleague in Sydney – James Beresford, a.k.a BI Monkey. He is also writing a blog, which I have seen and read in the past.

Check out his take on what an ETL should look like. He told me that the current version (v1) is suitable for smaller projects and that a new one v2 is coming out soon – so if you like v1 definitely stay posted for v2! And in case you have suggestions – he is pretty cool to chat to and very keen to get the framework further, so don’t hesitate to contact him!

SSIS , ,