Welcome to the Exact Product Blog

On this blog we will cover stories from all around the Exact ecosystem.

Simple and extremely fast analysis of your business with Power Pivot for Excel.

Written by André van de Graaf on . Posted in Exact Globe

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:

  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:

Now it is time to load your Globe data into the Excel 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. All data is stored locally in the Excel sheet in a compressed format which enable you to analyze off-line.
  5. Adjust the Charts, Pivots, tables etc to your own preferences.

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

Comments (5)

  • Scott Leete

    |

    Andre: Nice to see some ideas that we discussed in the Spring coming to fruition. This capability will be greatly appreciated by our customers.

    Reply

  • Jorgen

    |

    Als ik nu een rapport wil maken met het volgende.
    Artikelcode – omschrijving – actuele voorraad – te kort – te leveren aan klant – te ontvangen uit fabriek. Kan ik deze waardes uit 1 database model halen en zo ja uit welke. Ik wil ook de mogelijkheid hebben om deze gegevens te verversen zodat ze actueel zijn

    Als u mij hierbij kunt helpen dan waardeer ik dit zeer.

    Reply

  • Wojciech

    |

    Nice.

    Reply

  • Geert

    |

    I’m not able to download : GRV_PowerPivotGlobe excel sheet.

    SSL protocol error?

    Reply

  • Freddy

    |

    How do I know if I have “SQL Read access to the Globe Reporting Views”?

    Reply

Leave a comment