Sometimes we need to integrate different data sources to generate reports in SQL Server. We could use SSIS to integrate the data and use SSAS to generate reports. However, is there an easy way to generate some reports of data with millions of rows and integrating different data sources? Check out this tip to learn more.
Yes - The answer is PowerPivot. PowerPivot is an add-in for Microsoft Excel 2010 that allows you to import millions of rows of data from multiple data sources into a single Excel workbook. With PowerPivot you can:
Create relationships between heterogeneous data
Create calculated columns and measures using formulas
By building one or more of these items you can further analyze the data so that you can make appropriate business decisions.
But why Excel? Because everybody loves Excel and everybody knows how to use it. Learning the PowerPivot is then a straightforward task. You can import data from SQL Server, Oracle or other databases. PowerPivot for Excel 2010 is used for data analysis, we can also transform large amounts of data into meaningful information really fast. So let's dive in.
To begin testing the PowerPivot we will need the following items: