How to use Power Pivot for Excel on your Exact Globe database.

As described in my previous blog, easier to build reports on a Exact Globe database with your preferred reporting tools by making use of the Globe reporting views, I described the introduction of the Globe reporting views. In this blog I will describe how you can use these reporting views with Power Pivot for Excel (one of my favorite 10 features of SQL 2008 R2).  PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers.

To make it a little bit easier for you I have created an Power Pivot for Excel sheet with some pre configured charts and tables on it. Of course in combinatie with the slicers. Last but not least, some relations between the different views. These relations are very important to analyze and combine different types of data.

This Power Pivot for Excel sheet make use of the Globe Reporting Views (GRV_*). As of Exact Globe product update 402 the first version of the reporting views are shipped. These views will only be installed as of SQL 2005.

In this Excel sheet you will find tabs for:

1. Revenue Overview (Slicers on: Year, Period,Country, Statecode, Sectorcode, Employee)
2. Revenue Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
3. Cost Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
4. Sales Account Analyze (Slicers on: Year, Period)
5. Sales Employee Analyze (Slicers on: Year, Period)
6. Sales Item Analyze (Slicers on: Year, Period)
7. Sales Assortment Analyze (Slicers on: Year, Period)
8. Top Suppliers (Slicers on: Year, Period)
9. Margin Items (Slicers on: Year, Period, Assortment)

Before starting you need to have:

• MS Office Excel 2010, preferable 64 bits version.
• Globe database installed on SQL 2005 or higher.
• Power Pivot for Excel.
• Globe Reporting Views installed on your Globe database. In case you can't wait for Globe release 402. Download the script to install the views.
• Read access to the Globe Reporting Views
• Download and Open the GRV_PowerPivotGlobe excel sheet.

Now it is time to load your Globe data in your sheet. Next steps are described in my blogpost: How to change the database for your Power Pivot sheet.

1. Change the database connection to your Globe database
2. Update (Refresh) the the Power Pivot Window.
3. Update (Refresh) the Excel sheet.
4. Now you are ready to analyze your Globe administration in Power Pivot for Excel.
5. Adjust the Charts, Pivots, tables etc to your own preferences.

Enjoy the power of Power Pivot for Excel on your Globe databases.