A client, for whom I have implemeted a data warehouse solution, did not only use this solution for making high level reports in Excel but also uses it as a source to answer several analytical questions (data mining).
A, for privacy reasons, generalized scenario is: All clients which have responded to our “fill in a random name” campain. The goal being to see if we can spot some charicteristics which may define our target audience for our next campaign. This query would generally result in about 400.000 clients. We would want this result to be in Excel so we could import it in SSPS.
Now, as some of you might know, Microsoft provides a data mining addin for Excel 2007 / 2010. My understanding of data ming is dealing with large data volumes in order to detect realistic patterns. So I thought “data mining addin” plus “Excel” equals Excel can handle huge amounts of rows fetched from a cube. Unfortunately this vision did not translate back into Excel. Trying to get to that level of detail, 400.00 clients, in Excel using a pivottable is near to impossible. Ok, I thought to myself, we will use PowerPivot. Using PowerPivot did enable me to import 400.000 clients (rows in powerpivot) but I was still unable to get this level of detail in Excel pivottable.
So there I was with a client, hungry for more data and more detail but now way of delivering it to him. The workaround? copy paste from powerpivot in SSPS. I know … the horror right?
PS: During my search for alternative solutions I came across this interesting addin for Excel “Predixion“. A data mining adding for Excel in the cloud.