I find PowerPivot to be an incredibly powerful tool for building ad-hoc analytical models from heterogeneous data sources. Now, there are a LOT of tools on the market today that can do that. But PowerPivot has the unique position of being fully integrated into Excel, which means that the power to build in-memory analytical models is now very easy and very much now the domain of business and data subject matter experts. It can be quite a challenge to retain the knowledge transfer from data domain experts how are close to the business, to the IT folks who are responsible for the BI solution, including the data schemas, analytics, dimensional models, etc.
Related: What's New in Microsoft SQL Server 2012 PowerPivot
Great, so PowerPivot will enable your business users to gain insights into data faster and with more accuracy. But now you want to share that wisdom with the rest of the business by using the SharePoint PowerPivot services in a collaborative environment. But what if your corporate reporting tool is not SharePoint (or Performance Point) or Excel? This can be quite common since Excel is the reporting tool that you use when developing PowerPivot models and (prior to Office 2013) the Power View animated data visualizations are not available in that version of Excel.
Data discovery through a tool like Power View creates a complete self-service scenario to optimize data delivery velocity and data discovery in your organization. Another very popular non-Microsoft tool is Tableau. If your business is using Tableau to provide BI dashboards and self-service data discovery, don’t fear. You can still use your PowerPivot in-memory cube model as a data source for Tableau reports. Here’s how:
1. Visit http://www.tableausoftware.com/drivers to make sure that you have the PowerPivot drivers that you’ll need to connect to a PowerPivot model from Tableau.
2. In your Tableau workbook, make a data connection to PowerPivot:
3. PowerPivot is a native data source in Tableau and you can use a local PowerPivot or a PowerPivot stored in SharePoint:
4. You have 3 options in the PowerPivot connection dialog from Tableau: SharePoint URL, UNC and Local Excel File. Which one you choose will depend on a few factors. If you are making a report from your local Excel PowerPivot file, then are essentially using a data source that is local to your computer. But if you store the PowerPivot on SharePoint, you can then point to the model with a local domain URL or a UNC folder that will allow you share the report with PowerPivot as a dynamic data source that uses the SQL Server in-memory analytical engine in SSAS. This allows you to scale out a dashboard and report based on a great PowerPivot model that you’ve created to the rest of your organization.
Read more blog posts from Mark Kromer at "SQL Server BI Blog."
