Welcome to the Exact Product Blog

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

Author Archive

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.

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

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

As an administrator of an Exact Globe and or Exact Synergy implementation you want to get the maximum out of your Exact solution. For instance, is my SQL Server configured correctly, can I improve the performance of my Exact solution, etc…. Please have a look at next presentation to see how you can check this and improve if needed:

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

More information about the Self service in one click ( Exact System information tool):

Improve performance of your Exact solution by implementing index suggestions.

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

First of all I would like to thank all customers who have already used the Exact System Information tool . Because of these customers, we have received valuable information to improve the standard index structure of Exact Globe. At a lot of customer sites we have seen that the same index suggestions were reported by the SQL Server database engine. We have used this information to improve the standard index structure of Exact. A good index structure enables the SQL Server database engine to retrieve requested data by the Globe application in an efficient way. For example: Finding something in a book without a good index will take you a lot of time. The better the index the easier it is for you to find the content you are looking for.

In the upcoming releases of Globe we will add these new indexes to the standard index structure of Exact Globe. After you update to these new release, you will automatically make use of this new index structure. However, there can be reasons why you can’t update to this new release immediately. For instance, your custom solution needs to be changed to be compatible with the new Globe release. In this blog I will explain how you can already make use of these new indexes in your current release of Exact Globe.

First of all, Index suggestions will ONLY be available if your Exact database runs on SQL 2005, SQL 2008 or SQL 2008 R2.  The SQL Server database engine stores all index suggestion in dynamic views. A dynamic view with index suggestions can contain content as long as SQL Server is not restarted or indexes are added. The longer SQL Server is running, the more index suggestions can be available in the dynamic views. For the best results your SQL Server should run at least 4 weeks.

 To begin you need to start the Exact System Information tool  and request an improvement report. The user who start the Exact System Information tool should have a SQL System Administrator role (SA). Otherwise you do not have access to the reported index suggestions. During the generation of the improvement report, we will compare the index suggestions reported by your SQL Server Database engine with the indexes we are adding in the new releases of Globe. If one or more matches are found we will add these matches to a new section in the report: Index improvements. See next example. 

 

To add these indexes to your Exact database you can create them in 2 ways:

  • Use the EPTIndex tool 
  • Manually with SQL Server Management Studio
 

  

To create these indexes with the EPTIndex tool: 
  • Download the latest version of the EPTIndex.exe tool
  • Copy EPTIndex.exe to the BIN Folder of your Globe installation folder.
  • Download the latest version of the PerformanceTuningIndices.xml file. Put your mouse on the filename:  PerformanceTuningIndices.xml and right click, Use Save Target As. Save the file in the XML folder of your Globe installation folder. If you double click on the filename, the XML will be opened instead of downloaded.
  • Browse to the BIN folder of your Globe installation and double click on the EPTIndex.exe to start to the EPTIndex tool
  • Select Server and Database
  • Press the Preview button to get a list of possible indexes which can be implemented
  • Press the Generate all button to implement all suggested indexes.
  • More information about the EPTIndex tool can be found here.

To create these indexes manually with SQL Server Management Studio: 

  • Select Start button, Run, SSMS.
    If you do not have installed SQL Server Management Studio, you can install it here.
  • Press the New Query button to open a new query.
  •  Connect to your SQL Server. This is the same server as you have used to run the Exact System Information tool.
  • Paste this script from the improvement report via Copy/Paste in the SQL Server Management Studio (SSMS) New query window.
  • Execute the script by pressing the Execute button.
  • Depending on the index and the size of your database this can take some time to execute.

We strongly advise you to run the Exact System Information on a regular basis. For instance once per 3 months. We are adding on a regular basis new suggestions to the improvement report to improve your Exact solution. If you have any feedback, please let us know. You can comment on this blog post or send an email to Andre@exact.com

Designing considerations for Reporting Services integration (SSRS) in Exact Synergy Enterprise

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

In the blog article: Insight in your daily business, Exact announced the integration of Microsoft SQL Server Reporting services (SSRS) in Exact Synergy Enterprise. Before we started the design of this project, we defined next requirements: 

  • Multi browser support.
  • Support of latest SSRS versions and features.
  • Reports built with the Microsoft report building tools.
  • Easy management of deployed reports.
  • Support for Web parts.
  • Deployment and access to reports via Synergy roles.  

Multi browser support.
In March 2010 Exact announced the start of the project: Multi-browser support of Exact Synergy Enterprise. This requirement made the answer to the next question relative easy to made: Will we retrieve and render the data on the web server or on a Reporting Services report server? Retrieve and rendering of reports on the web server can be done with the Report Viewer control. This has some advantages however it conflicts with our goal to support multiple browsers.  This means that Exact Synergy Enterprise will render all SSRS reports on a Reporting Services report server. You have a license to use reporting services as long as the reporting server is installed on your database server. If you install a seperate reporting server you require an additional license. More information about SQL 2008 licensing can be found here.  

Support of latest SSRS versions and features.
In every new version of SQL server, new features will be added to Reporting server. Especially in SQL 2008 R2 a lot of nice reporting features are added like 

Maps, Sparklines, Databars and Indicators.  

     

Reports build with the Microsoft report building tools.
Microsoft made excellent tools to build reports and make use of the new features. For instance:  

Easy management of deployed reports.
After building a SSRS report, you should deploy the report to the reporting server. The deployment of SSRS reports can be done via the Synergy user interface. This includes deletion of deployed reports and the re-deploy of reports to a new or additional reporting server. 

Support of web parts.
Web parts are a nice way to build your own dashboard. As of Exact Synergy Enterprise we support web parts on the home page of a user. 

  

Deployment and access to reports via Synergy roles.
To deploy reports via Synergy you should have the role Report manager. To access a report you should have the role which are linked to the reports. This can be existing roles but also new created roles.

I hope you will enjoy the integration of Reporting services in Exact Synergy Enterprise.

Photo credits: SOCIALisBETTER

Exact System Information improvement report extended with a SQL upgrade advice.

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

As of today, The Exact System Information Improvement report is extended with a SQL upgrade advice to SQL Server 2008 R2. The Exact System Information tool will make a SQL upgrade advice based on your current SQL Server license. This is especially interesting for customers who are currently using SQL 2000. In the past Exact was not a reseller of SQL Server, so you need to buy a full license of SQL Server somewhere else. As of SQL 2005,  Exact is selling so-called Runtime licenses of SQL Server. These licenses only allow the use of SQL Server in combination with Exact Software’s products. Runtime licenses are much cheaper in comparison with a full license of SQL Server. A Runtime license embed the complete SQL Server code.
Reasons to upgrade if you are running on SQL Server 2000:

  • Reduce the size of your backup up to 60% with SQL backup compression.
  • Improve the overall performance and reduce index storage and maintenance costs by implementing filtered indexes.
  • 64 bits support.
  • Windows update integration: SQL Service packs are shipped with Windows update.
  • Free license to use SQL Server Reporting Service on the same server as the SQL Server.
  • Make use of Tablix, Charts, Gauge data regions, Maps, Spark Lines, Indicators and Databars in SQL Reporting Services.
  • After upgrading your SQL Server, we are able to analyze and optimize your index structure to improve the overall performance. The analyze will be done everytime you start the Exact System Information tool (ESI).
  • Use the SQL Server value calculator to make an estimate on your savings based on your use of SQL Server.

In the improvement report you will find some links to additional imformation about these upgrade suggestions. To receive a quotation for a SQL license you only need to press on the link in the report. You can also contact your local Exact office or your reseller.

To receive the SQL upgrade advice you need to

  1. Download the ExactSysInfo.zip and start the Exact SysInfo.exe 
  2. Request an improvement report
  3. Specify your email adress in the first screen of the Exact System Information tool.

Exact System Information improvement report extended with filtered index suggestions.

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

As of today the Exact System Information improvement report is extended with the possibility to implement filtered indexes for Globe databases on a server with SQL Server 2008 or SQL 2008 R2. See next screen shot from an improvement report.

As of SQL Server 2008 filtered indexes are introduced. A filtered index allows us to create an index with a filter on a subset of rows within a table. A filtered index will:

  1. Improve query performance. Statistics are more accurate which can result in better query plans.
  2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
  3. Reduce index storage costs.

Filtered indexes can be very useful on columns which contains mostly NULL values and where the queries retreive only the rows where the data is NOT NULL. In Exact Globe we have a lot of indexes on columns which contains mostly NULL values. See Microsoft website link for more information about filtered indexes.

After implementing filtered indexes on some customer databases, we have seen reduction of the index size between 25% to 30%. This will results in an overall database size reduction of around 10 %. The actual reduction of the index size in your database depends on the number of NULL values in your database. Please DO NOT shrink your database after implementing the filtered indexes. Shrinking databases and explanding database can result in defragmentation of the database files on NTFS level. SQL Server will first use the free space in the database before it will growth.

Performance tests indicated no noticeable performance increase or decrease with read actions, however write performance is gained due to the fact less index information needs to be written. After implementing filtered indexes, the fragmentation of your indexes will be lower. This is good for the overall performance of your Exact solution.

 To begin you need to start the Exact System Information tool  and request an improvement report. The user who start the Exact System Information tool should have a SQL System Administrator role (SA). If you can implement filtered indexes, it will be mentioned in the improvement report. In the improvement report you will find a link to the script to implement filtered indexes.

We strongly advise you to run the Exact System Information on a regular basis. For instance once per 3 months. We are adding on a regular basis new suggestions to the improvement report to improve your Exact solution. If you have any feedback, please let us know. You can comment on this blog post or send an email to Andre@exact.com

Did you already request an improvement report for your Exact solution?

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

As mentioned in a previous blog post, Exact started developing the Exact System Information (ESI) tool. With this tool you can receive an improvement report via e-mail. What can you expect in the report?

1. Server improvements.
The server improvement section is related to your SQL server configurations and answers questions like: Have you installed the latest Service packs? Can you make changes in your SQL Server configuration to make use of all available memory? Etcetera.
Screenshot of Server Improvements:

2. Database improvements.
The database improvements section is related to the database itself and gives advice about new available product updates, clean up of historical journal records, clean up of voided entry record, background jobs, clean up of temporary tables etcetera.
Screenshot of database improvements:

The number of requested improvements reports are growing every month. The Exact System Information tool can be downloaded and used for free by all organizations that uses Exact Globe and/or Exact Synergy (Enterprise). Did you already request an improvement report? Do not forget to specify your email address when running the tool.

If you have feedback please comment on this blog or send an email to Andre.van.de.Graaf@exact.com The ESI tool can be downloaded from here.

Receive free advice on optimizing your Exact solution

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

In 2009 we started developing the Exact System Information (ESI) Tool. With this free tool we can serve you, as one of our valued Exact Globe and Exact Synergy customers, with targeted and personal advice on how to make better use of our software solutions. By advizing you and thousands of other customers, we can also gain better insight in how our software is used; valueable information that enables us to keep improving our solutions. The Exact System Information Tool can be downloaded for free and the file ‘ExactSysInfo.exe’ can be installed by all organizations that use Exact Globe and/or Exact Synergy (Enterprise).  

It is important for you to know what kind of information is exchanged by the ESI Tool. When you use the ESI Tool no company sensitive information will be transferred. Only technical information like usage statistics, logged errors, numbers of records per table, database size and settings will be exchanged. When using the ESI Tool you can see exactly what information is send to us in the CSV file that is placed on your desktop or via menupath ‘Logs, View CSV file’.

Personally, I am very happy to see that a lot of our customers have already used the ESI tool. As of today, the Exact System Information Tool is even extended with the option to receive an automated improvement report per e-mail.

If you specify your e-mail address before pressing the ‘Start Analysis button’, the Exact System Information will analyze your SQL Server and Exact databases. When the analysis is completed you will receive the improvement report within 10 minutes. The report is divided in 2 sections:

1. Server improvements.
The server improvements section is related to your SQL server configurations and answers questions like: Have you installed the latest Service packs? Can you make changes in your SQL Server configuration to make use of all available memory? Etcetera.
 
2. Database improvements.
The database improvements section is related to the database itself and gives advice about new available product updates, clean up of historical journal records, clean up of voided entry record, background jobs, clean up of temporary tables etcetera.

If you have already used the Exact System Information tool in the past, the tool will update itself when you start it up again.

Enjoy using the Exact System Information tool. If you have suggestions for improvements please let me know, as we do not only want to improve our software solutions but also the Exact System Information Tool itself.

Photo credit: cliff1066tm