Skip to main content

psaPerformanceBench Installation and User Guide

PSA Support
  • Updated

Table of Contents

Overview

psaPerformanceBench will process automate building BULK processing rules in Transform for the automated mapping actions.  The mapping actions of (COPY, DEFAULT, RULE, XREF, CONSTRUCT) can be combined into a single SQL Update command to decrease runtime. The application does not delete any SQL objects already generated or remove any Transform registrations.  The process sets rules from ACTIVE to BULKUPD so the rules do not process in Transform.  The application also has a DELTA process for collection to download large tables by year to reduce download times and\ora process for perpetual data loads.

 

Key Features:

  • Ability to Bulk Process Source Rules on select st table
  • Ability to Bulk Process Target Rules on the tt table
  • Quick Automation process to change tables from single process to Bulk Process and back
  • Processing History for all Bulk Rule process for up-to-date processing logic
  • Ability to turn Bulk processing off unavailable at the wave-process area level.
  • Collect Delta Table download process for transaction data by year or other categories
  • Collect Delta Table download for ongoing perpetual downloads
  • Monitoring Reports on CPU and Memory usage
  • Monitoring Reports on Disk Read & Write latency and speed
  • Monitoring Reports on Session Data to display SQL that is processing
  • Monitoring Reports on Temp Database to display SQL taking up too much space

Install psaPerformanceBench

The application can be installed on Syniti Solutions DSP versions 7.0.6 and above

Download the Application & License

The psaPerformanceBench application and/or licenseare obtained by opening a support ticket at support.syniti.com.

Perform the following steps to retrieve the necessary information for a license request:

  1. On the DSP application server, locate the Hardware Identifier program (called “HardwareIdentifier.exe") included in a zip file along with the DSP installation software and documentation previously downloaded from Syniti.
  2. Open the program.
  3. Click Generate.
  4. Copy the automatically generated ID and collect the following additional information. All information below pertains to the application server running DSP; no information is needed regarding the database server:
    1. Hardware ID (as mentioned above)
    2. Windows computer name
    3. Number of processor cores (as shown in the Task Manager CPU tab)
    4. Usage of the DSP instance, as in, DEV, TEST (or QA) or PROD
  5. Syniti Licensing will deliver the license file via the support ticket.

Install the License

Perform the following steps to install the license:

  1. Log in to the DSP site as an Administrator.
  2. Select Admin > Configuration > Product Licenses in the Navigation pane.
  3. Click the Upload a file icon in the FILE NAME column next to the Upload a New Product License link.
  4. Locate the license file that was provided by Syniti Licensing.
  5. Click Open.
  6. Verify the license is uploaded.

    NOTE: If the Navigation pane does not display all the licensed components as expected, use the browser refresh button or the F5 key to refresh the screen. At this point the full vertical menu will appear.

Install the Application

Perform the following steps to install the application:

  1. Right click on psaPerformanceBenchs.zip and go to Properties. Ensure to unblock the file if it is blocked.
  2. Unzip the file.
  3. Navigate to the DSP Installation folder (Ex: D:\BOA\DSP or C:\Program Files (x86)\BOA\DSP)
  4. Back up the DSP Install\BOA\DSP folder to a compressed zip file.
  5. Back up all Syniti-supplied SQL Server databases or verify that a complete recent backup already exists.

    NOTE: Supplied Databases are - AutoGen, cMap, cMap_Data, cMass, cMass_Data, Console, CranPort, CranSoft, DataConstructionServer, DataDialysis, DataGarage, DBMoto_Client, DGE, DGE_Data, dgReports, dgSAP, dspAddOn, DSPCommon, dspMonitor_AccPak, dspMonitorConfig, DSW, IGC, Integrate, IntegrateStaging, InterfaceServer, MC, & RADToolkit

  6. Stop IIS

    This process disconnects all active DSP users, so it is highly recommended to perform the install when no users are on the system. This process stops IIS on the web server.

    1. Open Windows Start Menu.
    2. Open the Command Prompt (run as an administrator).
    3. Type: IISReset –stop
    4. Press the Enter key.
    5. Leave the Command Prompt window open for later use.
  7. Stop all services that start with “Cransoft Service …”

    This process stops all DSP background jobs, so it is highly recommended to perform the install when no scheduled operations are running on the system.

    1. Open Windows Start Menu.
    2. Select Administrative Tools
    3. Run Services
    4. Right-click the DSP service.
    5. Select Stop
    6. Repeat the previous two steps for any additional DSP services.
  8. Copy the Web folder from the zip file to your existing DSP install\Web folder.  If prompted, replace the files in the destination.
  9. Copy the Databases folder from the zip file to your existing DSP install\Databases folder.  If prompted, replace the files in the destination.
  10. Navigate to DSP install\Databases and execute file psaPerformanceBench_Install.bat(run as an administrator)
  11. Start all services that start with “Cransoft Service …”
    1. Open Windows Start Menu.
    2. Select Administrative Tools.
    3. Run Services.
    4. Locate the DSP service(s).
    5. Right-click the DSP service.
    6. Select Start.
    7. Repeat the previous two steps for any additional DSP services.
  12. Start IIS.
    1. Open Windows Start.
    2. Open the Command Prompt (run as an administrator).
    3. Type: IISReset –start
    4. Press the Enter key.

Configure psaPerformanceBench

psaPerformanceBench comes pre-configured with a testing wave for reviewing the application.

Run this stored procedure after the installation.

EXECUTE [psaPerformanceBench] .[dbo].[webLoadDefaultIns]

If the Navigation pane in DSP doesn’t show psaPerformanceBench, then try these steps:

  1. Log in to the DSP site as an Administrator.
  2. Select Admin > Configuration > Product License in the Navigation pane.

    Ensure that psaPerformanceBench appears here.

  3. Select Admin > Configuration > Site Menu in the Navigation pane.

    Ensure that psaPerformanceBench appears here.  If not, then:

    1. Click Add.
    2. Enter a priority in the PRIORITY field.
    3. Enter psaPerformanceBench as the label for the site menu option in the LABEL field.
    4. Select the psaPerformanceBench : psaPerformanceBench page from the LINK TO PAGE ID list box.
  4. Select Admin > Configuration > Parameters in the Navigation pane.
    1. Click Clear Cache.
    2. Reload the browser tab.

If you still can’t reach the psaPerformanceBench application, then review the “Define Security Roles” article in the DSP Online Help to ensure that your DSP user has access to psaPerformanceBench.  The DSP Online Help is accessible from the question mark icon in the top-right corner of all DSP pages.  You can also open a ticket at support.syniti.com for assistance.

User Guide

Quick Checklist to Bulk Process

  1. Open Web Page Object Targets in psaPerformanceBench.
  2. Find Target on the child Page Bulk Target and Click the Activate Toolbar.
  3. Click Source on the Bulk Target Page.
  4. Click Activate on the Target Source (only 1 Source can have Truncate Target checked).
  5. Click Build Rules to build update on selected at source or target in Transform.
  6. Click the Transform Toolbar to go to the Target in Transform.
  7. Click on the Source Rules or Target Rules to see the new registrations.
  8. Click Process Target in Transform to see Bulk Rules process.
  9. Click on Processing History in psaPerformanceBench to see the SQL being process.
  10. Click Remove Rules to delete Bulk Rules on selected at source or target.

 

Quick Checklist to Collect Delta

  1. In SQL SERVER, Create Delta Database in SQL SERVER.
  2. In DSPCOMMON, Register Target Data Source for Delta.
  3. In Collect, Add Target registration pointing to SQL SERVER database.
  4. In Collect, Add Source registration pointing to Collect Target Delta.
  5. In Collect, Add Table for the using where clause series.
  6. In psaPerformanceBench Collect Delta Table, Register the Delta Target, Source, Table and the Merge Target for the data.
  7. In psaPerformanceBench Collect Delta Table Where, Add the Where clauses for each table.
  8. In psaPerformanceBench Collect Delta Table Where, Activate the Where clauses and set the processing order.
  9. In psaPerformanceBench Collect Delta Table, Click Start to update where clause and create the post load stored procedure.
  10. In psaPerformanceBench Collect Delta Table, Click Refresh or In Collect click Refresh to start the perpetual download.

Object Launch\Bulk Targets Page

Object Launch will have all Waves, Process Areas and Object listed on this first page.  The Wave Exclude page will limit the results on the launch page. Click Activate on the Bulk Targets to start the Build Process and click Sources.  The History will display all SQL processed against this target.

The Data Block size will be defaulted from the parameter table to the vertical view of this page.

The best performance for data chunking is between 250,000 to 500,000 records in a single Update/Commit.  Tables with a lot of columns should have a smaller data chunk size. This process will keep the logs from growing to be too large.

psaPerfBench_1.png

mceclip0.png

The Vertical view will store the Data chunk size for block updates.   The value is defaulted from the parameter setting.  The comments field is for a developer to leave any custom notes for other developers to read.

psaPerfBench_2.png

Target Sources - Mappings

Target Source is the only page for creating and removing the Bulk Rules.  The Target source must be activated first.  This will load the mapping with default Bulk Ready flags turned on for Default, Copy, Construct, Rule and XREF actions.  Click the Build Rules toolbar to build the rules in Transform and click the Remove Rules to remove them from Transform.

If mappings have changed since the original build, click load mappings and then click Remove Rules and Build Rules again to get all the registrations correct in Transform.

Assembly Where Clause override will be used in the filtering of the source table load.

Bulk Rule Where clause is the active field applied to the source bulk processing rules and the target load rule.  This requires a MANUAL rule to be created to set as relevancy data. It should be set as the second rule after the insert rules has processed.

Example:

psaPerfBench_3.png

No coding is required to enable the process.  

This process does not DELETE any SQL objects or Transform registrations.  

The Target Source registered with the source table Cranport package is updated to manual load so the bulk loading insert process runs.

 

Bulk rule builds will auto generate all of the SQL Procedures.

Bulk rule remove resets the Transform target back to the initial state.

It will only set the status of the rules to BulkUpd or Inactive.

Example of rules being set to BulkUpd.

psaPerfBench_4.png

Target Delete and Insert rule being set to Inactive.

psaPerfBench_5.png

Example of cranport being remove from the Target Source.

psaPerfBench_6.png

To Enable:

  1. Click Activate and it will load mappings.
  2. Build Rules.

To Disable:

  1. Click Remove Rules.
  2. Click Inactivate to stop procesing for this source or table.

To Load New Mappings:

  1. Click Load Mappings.
  2. Click Remove Rules.
  3. Click Build Rules.

To Add Filter on Source Rules:

  1. Click the AddFilter toolbar that will read the Active Field from Map.
  2. Or Manual enter a where clause in BULK RULE WHERE field that will be applied to the source bulk processing rules.

psaPerfBench_7.png

mceclip2.png

Field Mappings – Bulk Build

Field can be set to be included or excluded from the Bulk Processing.  Rules actions that call functions which take large amounts of memory, may be excluded from the Bulk processing cycles.

Click the Inactive button to remove the rule from processing. Manual Rules and Manual Construction can not be activated on this page.

Any Rule Activate or Inactivate will require the Remove Rules followed by Build Rules.

This will ensure the Registrations in Tranform will match these settings.

To Update Mappings:

  1. Click Activate\Inactivate on the Mappings.
  2. Click Remove Rules.
  3. Click Build Rules.

psaPerfBench_8.png

mceclip3.png

The vertical view of the mapping displays information from Map any automation setting the Bulk Process will use. No updates can be made to map from this application.

psaPerfBench_9.png

psaPerfBench_10.png

psaPerfBench_11.png

Processing History SQL Log

Processing Log will displayall Bulk Processing SQL history.  The one on the top is the last one processing.  Click vertical view to see the SQL running.

psaPerfBench_12.png

psaPerfBench_13.png

All Active Targets

All Bulk Targets across waves configured for Bulk Rule Processing.  Process will display who built the rules and when they were last executed.  The Source image will take a user to the Target Source Page for setting bulk changes.  The history will go to the Processing History SQL Log.

psaPerfBench_14.png

There are no updates on this page.

mceclip4.png 

Wave Exclude

Wave exclude will make the Wave not display on the Object Launch page so a Target cannot be made active.   The Plus toolbar will add the wave process area and the Subtract toolbar will remove it from the select web page. Active targets will not be impacted with this change.

psaPerfBench_15.png

mceclip5.png

Collect Delta Table

Collect downloads can use where clauses in the packages to reduce timeouts and session limits from the source system.  General ledger transaction tables often must be downloaded by year or a sequence to successfully get all the data.  There are two types of downloads on the Delta Process.

The Where Clause series will run a bunch of downloads. As one completes, it will mark the entry as inactive, and then start processing the next active where clause.  Once all where clauses have been processed, the download will be marked as inactive.  The Perpetual downloads are designed to run daily and will download the latest records,and then merge them into the Merge Target Database.

Downloads must have the START process clicked so the post load stored procedures will be created and registered into the Delta Target Database.  The END process will remove the registered stored procedure and stop the ongoing process.  The REFRESH image will update where clause in collect for that table and start the cycle of downloading.

The Merge Target Database is where the data will be loaded once the delta process has been completed.

psaPerfBench_16.png

mceclip6.png

Collect Delta Table Where

Databases used in dspMigrate waves.  Details of the objects can be accessed by clicking on the images.

psaPerfBench_17.png

mceclip7.png

Collect Delta Table Log

Delete Table Log provides the timestamp and order in which the where clauses were processed.

The count shows how many records were downloaded and merged into the Target table.

psaPerfBench_18.png

Collect Delta Table Column

Merge Target database process will read columns in the source and target tables. Then create the insert SQL commands to load the records.  There is no coding required for any developers.  This table displays the columns being used for this process.

psaPerfBench_19.png

Collect Target Source Table

The last image will take a user to the Collect Target Source Table. This will enable them to see which table where clause is being updated and view the post load rule is in place.

The user must have access to Collect to get to this page.

psaPerfBench_20.png

Delta Target tables must have a stored procedure registered to perform the merge.

psaPerfBench_21.png

CREATE PROCEDURE [dbo].[irdgSAPDelta_Recycle_BSEGUpd] AS  

BEGIN

EXECUTE [psaPerformanceBench].dbo.webTargetSourceTableDeltaDownloadRecycleUpd 'dgSAPDelta','SAP_S4H','BSEG'  

END

Delta Target tables then use the regular collect table download options.

This process should work for any package type.  SSIS, CranPort, DataServices and RFC.

psaPerfBench_22.png

Collect Delta Log

Delete Table Log shows the timestamp and order in which the where clauses were processed.

This will display the log for all tables and will be retained for 180 days.

psaPerfBench_23.png

Collect Copy Tables

The Collect Copy Tables assist in reducing time to set up the delta process.  It copies the table list and scheduling if checked to the delta target.  The Only Active checkbox copies active tables from source to the delta source.  The copied tables count will show what was copied.

psaPerfBench_24.png

mceclip8.png

CPU & Memory Log

CPU & Memory log displays the amount if CPU SQL SERVER is using on the server.  The memory is the amount of allocated memory it is using.  If SQL SERVER has a maximum of 28GB out of 32 GB, 100% means it is using all the 28GB.  SQL SERVER historically does not release memory back to zero unless the SQL SERVER Database is restarted.  It will grow back to 100% over a period.

Service Page TempDBSnap Shot will get the current values and is scheduled to run once per hour.

Click on the Process Toolbar image to the latest values immediately.

psaPerfBench_25.png

mceclip9.png

Disk Read & Write Log

Disk read and write log displays latency time values and disk throughput available.

The Log displays Database File name being read or updated. If there is a file being accessed with high latency and throughput, it may be a good idea to split into multiple data files and or disks (controllers).

Service Page TempDB Snapshot provides the current values and is scheduled to run once per hour.

Click on the Process Toolbar image to the latest values immediately.

psaPerfBench_26.png

mceclip10.png

Session Log

Session data capture once per hour displays who is active at that given time.  Long running processes will display the entry over time and should be investigated for performance reasons.

The vertical view will show extended SQL information about the process.

Service Page TempDB Snapshot will get the current values and is scheduled to run once per hour.

Click on the Process Toolbar image to the latest values immediately.

psaPerfBench_27.png

mceclip0.png

psaPerfBench_28.png

psaPerfBench_29.png psaPerfBench_30.png

Temp Database Log

Temp database log shows active running SQL command using Temp database space.  SQL that runs for over 1 hour should be reviewed for performance reasons.  The vertical view provides more details about the SQL processing.

Service Page TempDB Snapshot will get the current values and is scheduled to run once per hour.

Click on the Process Toolbar image to the latest values immediately.

psaPerfBench_31.png

psaPerfBench_32.png

mceclip1.png

Database Details

Database details will show the file size for each database and the amount of free space available.

Users cannot shrink or alter database settings from this page.  It is just a helpful tool to monitor which databases are growing the fastest and if there is space to be released.

The process toolbar will get the latest information, but the service page will update daily.

psaPerfBench_33.png

mceclip2.png

Drive Space

Drive Space is a monitor tool for the user to see if a drive is almost out of space.

When a drive goes to 100% full, DSP processing will start failing for database on the drive trying to be updated.  This page is a READ ONLY page so it cannot move database files or add space to any drive.  You must contract the DBA or Basis to handle server issues.

The process toolbar will get the latest information but the service page will update daily.

psaPerfBench_34.png

mceclip3.png

Parameters

Parameters pages store a couple of basic settings.  The Default Block size will be set for each new Target in the Bulk processing.  The Drop Template could be turned off at client sites since no development should be done for Bulk processing.  The New Data Test Size is the amount of data to run via the Educate-Performance target for comparisons of standard Autogen rules versus bulk processing Autogen rules.

psaPerfBench_35.png

mceclip4.png

Merge Type

Merge Type will only support one type of method for the Collect Delta Recycling.  This will download from Source system using a where clause.  The second step will delete using the where clause from the Target Database Table and then insert all the downloaded records into the target table.

The next two options are in development. Please contact SMT if there is a need to get the latest option for the Collect Delta process.

psaPerfBench_36.png

Build Option

Source Rules have two options.  FATINSERT that will combine the Copy and Default rules into the INSERT command used to load the ST Table.  The INSERT option will make Copy and Default rule run as a separate SQL command.  FATINSERT will save one read & write for the entire table.  Rule rules, XREF rules and Construct rules will run in their own register rule for fastest processing and tracking value purposes.

Target rules only have one option to do processing.  The Default Rules, Copy Rules and Rule Rules will all run as its own SQL command.

psaPerfBench_37.png

mceclip8.png

Debug Log

Debug log for dynamic SQL building of objects for psaPerformanceBench.

psaPerfBench_38.png

Security Role

psaPerformanceBench will be delivered with one security role.

The application is designed for developers to optimize target execution times.

If there is a need for additional security roles needed, they will have to be built at the client sites.

psaPerfBench_39.png

Test Wave Install for Application Evaluation and Server Performance Testing

psaPerformanceBench will be one wave (Educate-Performance) with six Targets loaded for processing bulk rules versus standard generation rules.  A User can adjust the test data size in the parameter page for testing larger amounts of data.  The concept is the same regardless if there is 100K or 5 million records in the test cycle. There are 3 dsw databases installed with the application so all the views and rules are ready to be processed.

Wave is a process for a developer to understand the Bulk feature and measure the performance of the server when processing 5 million records. All these actions are mappings that Bulk performance processing can merge. (Copy, Default, Construct, Rule, XREF and RuleXREF). 

psaPerfBench_40.png

mceclip7.png

The image below displays psaPerformanceBench running a test cycle of 5,000,000 records.

There are no manual rules in this test example.

psaPerfBench_41.png

Document Control

Issue

Date

Maintainer / Owner

Description

1.0

8/31/2020

Dan Downey

Version Release to all project

 

 

 

 

Contact Information

Contact Name

Title

Phone Number

E-Mail

Dan Downey

Solution Architect

678-361-7573

Daniel.Downey@syniti.com

NOTE: Syniti’s Professional Service Accelerators (psa) have been developed and are supported by a team of senior consultants. The psas are designed to supplement the Stewardship Tier delivered features with content and automation that accelerate the progress of the project. If you have any questions or encounter any issues while using a psa, please submit a support ticket and be sure to select the psa on the form.

 

The attached document outlines installation, configuration, and usage information for the psaPerformanceBench application, an accelerator for the Syniti Stewardship Tier (SST, formerly DSP).

 

Professional Service Accelerators (PSAs) are licensed separately from the SST. For more information, please email NASMT@syniti.com. To download or install a PSA, submit a request to Syniti support.

 

Updated on July 12th, 2021

 

Related articles