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.

Get the maximum out of your Exact solution: Self service in one click.

In the past I blogged about the Exact System Information tool. With this tool you can optimize your Exact solution in one click. Please have a look to the embedded PowerPoint Web app presentation to see what the Exact System Information can do for you as an Exact user.  



If you are unable to see the presentation you can see it here


Enjoy it!

Support policy for SQL Server 2000 on newer Operating Systems

This blog post applies to customers who uses Microsoft SQL 2000 as database server. In the past Exact Globe started with Microsoft SQL Server 2000 as database server. As of today, still a lot of our customers uses Microsoft SQL Server 2000 as database server. On 14 September Microsoft announced the support policy for the newer operating systems. This means Microsoft SQL Server 2000 is not supported on the latest operatings systems like:

1. Windows Vista (all editions)
2. Windows Server 2008 (all editions)
3. Windows Server 2008 R2 (all editions)
4. Windows 7 (all editions)

Click here to find more information about the Operating Systems Supported by the Editions of SQL Server 2000

In case you want to use Windows Server 2008 or Windows Server 2008 R2, as operating system for your new server, you need to upgrade your SQL Server version. Please contact your Exact reseller for an SQL Server license with a big discount in case you are using the SQL server only for Exact databases. As of release 398 Exact Globe supports Microsoft SQL Server 2008 R2.

Enjoy your upgrade to SQL Server 2008 R2

Overview performance improvements in Exact Globe in the last years.

Photo credit Alancleaver_2000

Over the years a lot new release have become commercial available. A lot of new functionality has come available. With the information we received from customers who have used the Exact System Information tool we have seen that they are not aware of available functionality. The Exact System Information tool is made for customers to help them in improving the performance of their Exact solution. A lot of customers uses Exact Globe already for years. In their administration they have a lot of historical data. Some clean up applications has been made to clean up the database. This will result in a smaller backup and can help to improve the overall performance.

This blog post will give an overview of all performance improvements and cleanup functions.

• Clean up log files. Release 360. Functionality is made to clean up ‘Application log’ or ‘Masterdata log’
• Clean up of historical journal records. Release 360. Historical data in Globe is stored in multiple tables in the database. In the previous release of Globe application, there is no process in place to perform the clearing of old records. As the database size grows from period to period and from year to year, the system performance will be deteriorated
• Database structure optimized. Release 370. Exact Globe 2003 database used data type CHAR instead of VARCHAR to store data with length more than 10 bytes. CHAR always uses fixed length to store data whereas VARCHAR uses dynamic length which depends on the actual size of the data. Therefore from a database structure standpoint, the database was not optimized. As a result, the following drawbacks were introduced. Record was unnecessarily bigger which caused slower queries because less record can be retrieved in one disk I/O. Indexes were bigger which again reduced query performance
• Clean Up XML Import Logs. Product update 395. Whenever you import XML files to Exact Globe, the XML import results which are the log of the relevant events will be recorded. An option to remove the XML logs are added to the functionality for cleaning up logs.
• Cleanup tool to delete obsolete logistic records and MRP planning records. Product update 395. Logistic processes in Exact Globe generate MRP planning records. Examples of logistic processes are sales orders, blanket sales orders, return to merchant authorization (RMA) orders, purchase orders, blanket purchase orders, return to vendor (RTV) orders, interbranch transfers, and quotations. Over the years, as customers’ databases become larger with logistic transactions, the database actions on these records become slower. Historical records such as completed sales or purchase orders may be considered obsolete after the completion of the logistic processes over a certain period of time. In order to enhance the performance of the administration, these obsolete logistics and planning records are best deleted.
• Retrieving Balance Totals. Product update 395. Due to the database structure of Exact Globe, the retrieval of transaction totals requires the system to totalize all transactions. Over time, as your database becomes bigger and filled with transactions, the retrieval of balance totals becomes slower. Faster retrievals are therefore necessary to help you work more efficiently with the system. Enhancements have been made to improve the performance of retrieving balance totals
• Database Performance by Optimizing Index. Product update 397. In every Exact Globe product update, system performance is improved by optimizing the database indices in Exact Globe. In this product update, a tool is implemented to optimize your database instead of the standard indices in Exact Globe. With this method, the indices will be deployed only when it is required by your SQL server. This method also reduces deployment risk because you can easily add or remove the indices if you are not satisfied with the performance result. There will also be performance improvement for functions that use the new index.
• Cleanup tool to delete obsolete logistics records extended to Production Orders and Stock Allocations.Product update 398. The scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool.

Performance improvement Globe (398): Logistic MRP Cleanup Tool Extended to Production Orders and stock allocations.

Photo Credit: Donger

As of Exact Globe product update 398 is is possible to delete the obsolete logistics and planning records for production orders and allocation entries. This is a good extension to the MRP cleanup tool which is released as of product update 395.

In Exact Globe product update 395, the logistics MRP cleanup tool for cleaning up obsolete logistic records and budget entries without affecting the related processes was introduced. The records included completed sales orders, blanket sales orders, RMA orders, purchase orders, blanket purchase orders, RTV orders, interbranch transfer requests, and quotations. For more information, see Product Update 395: Improved Performance with Obsolete Logistic Records and MRP Planning Records Cleanup Tool .


However as of product update 398, in order to enhance the performance of the administration, system administrators also need to delete the obsolete logistics and planning records for production orders and allocation entries. As such, in this implementation, the scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool. This enhancement will definitely assist administrators in boosting the performance of the administration with ease. For more information about the updated prerequisites, how to use the cleanup tool, and the latest list of functionalities that are affected by the cleaning up process, see How-to: Cleaning Up Obsolete Logistic Records and Budget Entries.

To get an estimate how much records can be cleaned up, please run the Exact System Information tool and request an improvement report. More information about the improvement reports of the Exact System Information tool can be found here.

Enjoy the update to Globe product update 398.

Exact Globe supports SQL Server 2008 R2 as of product update 398

 In July 2010 the Exact Globe product update 398 is released. As of this product update 398, Microsoft SQL Server 2008 R2 is supported. Beside this Office 2010 (32 bits) is supported. More information about this product update can be found here.

Be aware that a database which is upgraded to SQL 2008 R2 can't be used on a previous version of SQL Server.

Enjoy your upgrade to SQL 2008 R2.

Exact Synergy Enterprise certified for Windows Server 2008 R2

As of product update 240, Exact Synergy Enterprise is certified for the latest  Microsoft server platform Windows Server 2008 R2. This is another great step of Exact in supporting the latest technology of Microsoft. Exact Synergy Enterprise can be used in 32 bits or 64 bits mode.
Exact Synergy Enterprise and Exact Globe now supports the latest available Microsoft operatings systems like Windows 7, SQL2008 and Windows Server 2008 R2.

Exact Globe compatible with Windows 7

On 22 october 2009 Windows 7 will become commercial available. Exact Software has become one of the first European software vendors to receive this certification. By the time the Microsoft Worldwide Partner Conference took place in July, Exact Globe was certified and ready to run on Windows 7. More information about Exact Globe running on Windows 7 can be found on the Exact Product Blog and on the Ready.Set.7 site of Microsoft.
On the Ready.Set.7 website you’ll find the Exact logo and behind it, a short piece describing the advantages.
A short film featuring Aad ‘t Hart can also be viewed, in which he gives his views on what Windows 7 can do for our customers. “Windows 7 increases the productivity of the Exact Globe end user,” Aad says.

With Exact Globe you are now able to run on the most recent available technology with Windows 7 and SQL Server 2008. Don't forget that you can buy SQL 2008 licenses for the use with Exact products with a big discount via Exact.

Exact Globe and Exact Synergy supports SQL Server 2008 as of product update 393.

As of 21 april 2009 product update 393 for Exact Globe and Exact Synergy is generally available for all customers worldwide with an active maintenance agreement, except for customers with custom solutions .

As of product update 393 both Exact Globe and Exact Synergy supports the use of SQL Server 2008. This means that Exact starts selling this new version of SQL Server. Please contact your local Exact office or reseller. If you want to have a look at the unique selling points of SQL Server 2008, please have a look at the overview on the Microsoft website. For an overview of technical documentation for SQL Server 2008, please refer to the Books Online.

SQL Server 2008 licenses grant customers the right to downgrade, which means that customers wanting to use SQL Server 2005 can still do so when purchasing SQL Server 2008 licenses. The upgrade to SQL Server 2008 can then take place at their own convenience. This is also important to know for customers purchasing or using Exact Synergy Enterprise: they are adviced to purchase SQL Server 2008 licenses and use SQL Server 2005 until product update 241 of Exact Synergy Enterprise, which suports SQL Server 2008,  is available.