Also keep in mind that PowerPivot may not rectify most data quality issues, such as deduplication or data lineage issues. Nor does it work easily with advanced modeling issues, such as dealing with nonconforming dimensions, slowly changing dimensions or multiple hierarchies, noted James Dixon, the chief technologist for BI software provider Pentaho.
"You need to make sure you have 100 percent agreement on the keys in the different datasets, and you need to be very careful how the join is done, otherwise the resulting data is meaningless. This is particularly hard with datasets that have differing levels of granularity," he said, adding that these problems are solved using ETL (Extract, Transform and Load) tools.
"These tools are not out of reach for a seasoned Excel user to understand, but factoring in the data quality has to be done -- even the flashiest analysis of bad data is going to lead you to make wrong decisions," Dixon said.