Support Spotlight: The secrets of cluster analysis for data archiving in SAP Business One

In this Blog we will go through a simple scenario to check if Data Archiving is suitable for your company. The data archive wizard reduces the size of the company database by removing data that is no longer required for the regular course of work (older than two years). The Data Archive functionality is based on clusters of transactions and/or documents. A cluster is archivable if all the transactions connected through the cluster are defined as removable.

Please note that this feature is available when using Microsoft SQL server.

A common question we get in Support is why a certain transaction cannot be archived. Here we will look at using queries in Microsoft SQL Studio to find out why a document is not archivable.

Cluster Analysis using Microsoft SQL Studio

Lets’ look at the steps below in SAP Note 2299043 – Data Archive – How to Perform Cluster Analysis.

1. Download and extract the attached file ‘20140429_DA_QueriesForAnalysis_v1.0.0_KT.zip’ from SAP Note 2299043.
2. In the SQL Server Management Studio connected to the test database, run query ‘20140429_DA_1_Initial_Info_v1.0.0_KT.sql’.
3. Log on to the SAP Business One application and run the Data Archive Simulation process until Step 6 – Data Archive Recommendations.
4. Find the SAP Business One process ID. (via Windows Task Manager).
5. Replace <PID> in the attached query ‘20140429_DA_2_Create_BACKUP_TEMP_ARC_v1.0.0_KT.sql’ with your SAP Business One process ID and run the query on the database.

Now it is possible to query against database table BACKUP_TEMP_ARC to verify the transactions in the required clusters.

For example, when running data archive to 2009-12-31, you want to find out why AR invoice 1 is in closed status but not archived.

First run query to find its cluster, there two ways:

a. Find by document type and document entry:

select * from BACKUP_TEMP_ARC where docabs=1 and doctype=13

The query output:

b. Find by journal entry transid:

select * from BACKUP_TEMP_ARC where docabs=278 and doctype=30

(278 is the AR invoice Journal Entry Trans. No.)

The query output:

From the result, we can see this transaction belongs to Cluster 1 (ClusterId=1), the transaction itself is removable (CanArcObj=Y), but the cluster is not removable (CanArcClus=N).

Next is to find out the nonremovable transaction in Cluster 1, plus why it is not removable and how it is connected to AR invoice 1.

To find the non-removable transaction in Cluster 1 there are two ways:

a. Run the following query:

select * from BACKUP_TEMP_ARC where Clusterid=1 and canarcobj <>’Y’

The query output:

b. Go to Data Archive Wizard Step 6 – Data Archive Recommendations screen, double click the row for Cluster 1:

Image Disclaimer: Image/data in this SAP Note is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Then on the new opened screen “Data Removal Recommendations-Transaction View (Cluster 1)”, choose Display “Nonremovable Transactions”:

Image Disclaimer: Image/data in this SAP Note is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Here the non-removable transaction is Journal Entry 344, Reason for error is “Connected to nonremovable document” which is not accurate. The real reason is the transaction was externally reconciled in year 2010 which is outside the archiving date range, here is the query to check it:

select T0.MatchNum as ExternalReconNumber, T0.MatchDate as ExternalReconDate,t0.MthAcctCod as AcctCode from OMTH T0

inner join JDT1 T1 on T0.MthAcctCod = T1.Account and T0.MatchNum = T1.ExtrMatch

where T1.TransId = 344

The query output:

For more information refer to section “What Data Is Archived?” in Data Archiving how-to guide here.

To find out how Journal Entry 344 is connected to AR invoice 1 (Journal Entry 278), use the query ‘20140429_DA_3_Detect_connections_v1.0.0_KT.sql’ from SAP Note 2299043 and replace accordingly the ‘DocType’ and ‘DocAbsEntry’ in the last sentence of this query as below:

The query output:

The result means Journal Entry 278 is connected to Incoming Payment 1, Incoming Payment is connected to Internal Reconciliation 2, Internal Reconciliation 2 is connected to Journal Entry 344.

For more information on the meaning of different doctype refer to the SDK help file REFDB.chm (check Journal Entry table OJDT.Transtype).

Support Spotlight Series 

We hope this read has been informative and beneficial. For more blog posts and tips from the SAP Business One Support team, please visit our space on the communities: https://blogs.sap.com/tag/b1-support-spotlight/

For questions about SAP Business One, please submit your question here.