18 October 2010

Project Phoenix - ANZ

Project Phoenix, started by SQL Server MVP Arnie Rowland, is a program which awards software, tools and training materials to under/unemployed developers who propose a software project for a non-profit agency, school or church.

More info: http://www.rodcolledge.com/rod_colledge/project-phoenix.html

28 September 2010

To PowerPivot, or not to PowerPivot?

We’ve all seen many presentations about PowerPivot and its great features like in-memory processing which enables Excel to handle millions of records. But the question is when to use PowerPivot as opposed to using a SSAS cube?
Here are some points you need to consider when choosing the tool:
  • PowerPivot is a powerful tool to gather data from different data sources. It can be used to create POCs or demos in a quick (and possibly dirty) way.
  • It can be used to answer some ad-hoc questions or to play with data (data analysis).

  • PowerPivot doesn't support data level security. Whoever gets access to the file can see all data inside it. With SSAS you can implement security at data level.
  • Complicated dimension types (like parent-child) are not supported in PowerPivot
  • Using PowerPivot workbooks as data source for reports may cause version management issues.
  • PowerPivot runs in memory, when you use PowerPivot for all your enterprise data you would need a huge RAM
  • Saving PowerPivot files in SharePoint may cause some issues when the size is too large.
  • SSAS allows you to fine tune your cube but with PowerPivot you can’t.
  • Data can easily be out of control as soon as it is saved on workstation. There also might be huge security risks involved. Even with the SharePoint implementation users have to download data to workstation first, and then publish it to SharePoint.
  • PowerPivot for SharePoint requires Enterprise license and SQL Server 2008R2 EE license. The cost (which is very high) may affect the SharePoint implementations of PowerPivot. By using PowerPivot without SharePoint (Excel add-in which is free) we may end up having even bigger Excel hell!
  • Source data is not always in high quality. We may need to validate and do data cleansing and also more often we need to apply some business rules which can be handled better in SSAS or at ETL level.
  • Working with data from different data sources may require high technical knowledge. Some data isn't that easy to join. A key isn't always available that lets you join data from different sources. It will make it hard for a non-technical person to work with the tool without any IT support.


From what I've seen so far, PowerPivot is not a replacement for Analysis Services, but it certainly has a role for tactical solutions. It also can be used by power users to quickly analyse data, or perhaps to build prototypes of larger scale SSAS implementations.

So I think PowerPivot is great for certain scenarios, but for Enterprise level data warehouse implementation SSAS is the way to go.

23 August 2010

New joy of my life...

This little man has delayed this blog for 4 months! Hopefully I’ll get more time to update this blog more often.

I started using SharePoint 2010 & SQL Server 2008 R2 in my new project and will share some thoughts on them very soon.

19 April 2010

SQL Server 2008 R2 - free ebook

You can download this free ebook from Microsoft Press to learn about new features of SQL Server 2008 R2.

Introducing Microsoft SQL Server 2008 R2, by Ross Mistry and Stacia Misner

Enjoy it!

08 February 2010

Gartner Magic Quadrant for BI Platforms 2010

Once again, Microsoft has been positioned in the Leaders Quadrant of the Gartner Magic Quadrant for BI Platforms (Gartner RAS Core Research Note G00173700 - 29 January 2010)

The full report can be accessed on Gartner’s web site here:

Figure: Magic Quadrant for Business Intelligence Platforms

05 January 2010

Microsoft BI in 2010 – and my New Year’s resolution!

It’s the first week of 2010 and people have got great intensions to stick to their resolutions (I’ve been to the gym once since 1st Jan which is a good start!)

But what about the “Learning” resolutions?

I have been learning about SQL Server 2008 R2 these days and found that there are lots of new things happening in MB BI space. Whether you are a techie who loves learning new stuff, passionate about MS BI, or just want to add weight to your CV, it would be worthwhile watching out these MS technologies in 2010.

1) Master Data Services
2) PowerPivot

3) PerformancePoint Services for SharePoint Server 2010

4) SQL Azure and Cloud Computing

5) Excel Services, VISIO Services and other BI related components and APIs in MOSS 2010

And in terms of "Methodology" and delivery approach, I believe we will hear more about "Agile BI /Data Warehousing" due to the reduced IT budgets in this economy. I'll talk about it in another post.