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. It’s the fast way to generate rich and interactive analysis tools that look like you’ve spent weeks building. It’s the right way to achieve deeper business insight and shorter decision cycles. If you doubt about the power of Power Pivot for Excel, please read the Top 10 reasons to try Power Pivot for Excel 2010.
In this blogpost I will explain how you can use Power Pivot for Excel 2010 on your Globe database.
To build reports on a database you need to understand the business logic of the application. For instance: How is an outstanding amount of an invoice stored in the database? This can be very complex. At this moment the outstanding item query contains 140 lines of code. To solve the challenge of the business logic we have developed database views which contains the business logic. The view will present the data in an understandable format which can be used in the report. The advantages of using views:
- Business logic is in the database, not in the report. If business logic changes, the view will be updated with the new logic. No changes are needed in the report.
- Data is presented in an understandable way. For instance ‘Supplier code’ instead of a database column name: ‘cicmpy.crdcode’
- Data sets in the reports are easier to read and maintain.
- Performance can be better guaranteed.
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. The Globe Reporting Views starts with ‘GRV_’ in their name. 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 combination 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.
In this Excel sheet you will find tabs for:
- Revenue Overview (Slicers on: Year, Period,Country, Statecode, Sectorcode, Employee)
- Revenue Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
- Cost Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
- Sales Account Analyze (Slicers on: Year, Period)
- Sales Employee Analyze (Slicers on: Year, Period)
- Sales Item Analyze (Slicers on: Year, Period)
- Sales Assortment Analyze (Slicers on: Year, Period)
- Top Suppliers (Slicers on: Year, Period)
- Margin Items (Slicers on: Year, Period, Assortment)
Before starting you need to have:
- MS Office Excel 2010, preferable 64 bits version. Please read the recommendation of Microsoft for which edition of Office 2010 to install.
- Globe database installed on SQL 2005 or higher.
- Power Pivot for Excel.
- Update your Exact Globe to product update 402.
- SQL Read access to the Globe Reporting Views.
- Download and Open the GRV_PowerPivotGlobe excel sheet.
- License to access SQL Server database with Excel.
Now it is time to load your Globe data into the Excel sheet.
- Change the database connection to your Globe database
- Update (Refresh) the the Power Pivot Window.
- Update (Refresh) the Excel sheet.
- Now you are ready to analyze your Globe administration in Power Pivot for Excel. All data is stored locally in the Excel sheet in a compressed format which enable you to analyze off-line.
- Adjust the Charts, Pivots, tables etc to your own preferences.
Enjoy the power of Power Pivot for Excel on your Globe databases.