Sync

Note: This option is generally used for data migration, so limited information is provided here. Contact your Customer Service Manager for more information.You can add the following types of data items:

File

This is a flat delimited file, such as a TXT, CSV, or PSV file. You need to add these files one at a time. Each file appears as a data item.

Directory

This is a folder that contains files. This is a great way to add very well-formatted and consistent files, where additional files might be added at different times.

• Multiple files are added at the same time, but only files prefixed with phocas_ in that directory are synced. Files not following this naming convention are ignored. You should only prefix the files you need for analysis.

• Each file appears as a data item, and the phocas_ part of the file name is replaced with name_.

• All sub-directories are traversed and all filenames starting phocas_ are processed, not just the starting directory specified.

• While you can specify options (such as ignore quotes), all files in the directory to be synced must follow the same format. For example, they must have the same delimiter and must follow the same rules for ignoring quotes and having a header row.

• The column mapping option is not available for this data item, but you can set the data types for each individual file on the Sync Sources page in Phocas.

Spreadsheet

This is a Microsoft Excel workbook file, such as an XLS, XLSX, or XLSB file. You need to add these files one at a time.

• Multiple worksheet tabs are supported and will be synced as individual data items, named filename_sheetname. The file should only contain tabs that need to be synced because you should only sync the data required for analysis.

• Each tab will show as a unique item on the Sync Sources page in Phocas.

• All tabs must follow the same format. There must be no merged cells and no multiple headers. Avoid cell formatting.

• Sync will attempt to identify the data type of each column based on the data type assigned by Excel. Number columns are treated as decimals. Integers and non-numeric fields that contain numbers (such as IDs, phone numbers and so on) should be formatted as text in Excel by preceding with a ‘.

• Sync uses the first line to determine the datatype. You can force Sync to see that as text by prefixing the first data entry of the column with a single quote.

Note: From version 8.0.5 onwards, Sync recognizes number columns in Excel. This may affect database builds involving some older synced files where number columns were previously treated as text and manipulated via a ‘CAST’ expression.

SQL

A SQL script for an SQL table or view that produces a data item. This data item type allows you to extract data from a database using a single query.

Note: Syncs from SQL sources should only be attempted by those with good SQL knowledge and experience. Incorrect and poorly structured SQL queries can have catastrophic effects on the database and ultimately cause loss of service for your business. Contact your Customer Service Manager if you need help with this.

The data must be well structured and you should have a good understanding of the database and how data is related before attempting to extract data. Only extract what is required for analysis and carefully construct your queries to be as efficient as possible. See best practices and examples of SQL scripts in the Additional technical information section below.

Add and test the connection string

1. Click Add.

2. Enter the string used to connect to the database along with the security credentials. First, select one of the pre-defined Database Engine types, then complete the other fields (which change depending upon the selected type).

   • The Custom Database Engine type allows you to enter a bespoke connection string.

   • If you use the Windows authentication method for the SQL server, the user running any automation (scheduled tasks) must have the required SQL access.

3. Click Test to test the connection. If it's OK, click Save to overwrite any previously saved connection or click Save As New to create a new connection.

Complete the configuration

1. See below for information about the Enabled, Incremental Process, and Retain Snapshots options.

2. Enter the SQL Query into the box.

3. Click Preview to preview the results of the script. In most cases, Sync will limit the return to X rows. If using ODBC connections, limit the return before previewing.

4. Click Save and Close or Save. The Save and Close option tests the query before closing. If the preview doesn’t automatically limit the return for that database type, use Save for large queries.

Database

A SQL, MySQL, Oracle, or custom database. This will extract the data for all columns in any tables prefixed with Phocas_ from the database listed in the connection string.

Note: This option is generally used for the migration of data, so limited information is provided here. Contact your Customer Service Manager for more information.

• Data must be well formatted, data typed and follow the other rules of SQL extracts as closely as possible.

• Each table appears as a data item. You need to enter the appropriate connection details and security credentials. Only table names prefixed with phocas_ will be synced.

• bit

• boolean

• tinyint

• smallint

• int

• bigint (from 7.0.15)

• int16

• int32

• int64

• integer

• int identity

• date

• datetime

• datetime2

• smalldatetime

• decimal

• money

• numeric

• char

• varchar

• string

• text

• double

• float

• nchar

• nvarchar

• ntext

• uniqueidentifier

Precision level of decimal(18,6) in SQL is the maximum that can be synced. If decimals with larger precisions are required, convert the decimal to a float.

Considerations:

• Although data can be transformed later, the ideal is always that the source data is in the correct format to upload and use. Ensure the data is properly typed and formatted (date columns). Consider if you can add any concatenated or calculated fields to the source data rather than doing it in Phocas.

• Ensure you sync only the data required for analysis. For example, remove any columns of data that will not be used and time-limit the data so you don't add years of unnecessary data. Several options (below) can help you achieve this.

Constraints:

• Limit the item sizes to less than 4G. 2G is optimal. Remember, Sync places the data into memory, so free memory should be around 2x the largest sync item. Each data item is different; we've seen some 4GB files that contain 5 million rows / 60 columns, but others that contain 25 million rows / 10 columns.

• Data items that exceed the 4GB limit can be split into multiple smaller data items by year.

• Your network bandwidth will significantly affect sync times, especially if Sync is not located on the same machine as the data. SQL drivers can also significantly affect sync times. Sync times can vary significantly, but 1 million rows with 10 or so columns using MS SQL Server should take less than a minute.

The Edit option is available for files. It allows you to get a preview of the data so you can check everything looks OK.

You can also change the column details of the file before you run the sync process:

• Edit a column, such as rename a heading, change the data type (text, number, date/time, boolean, or Unicode), and set a field length.

• Exclude (disable) a column to temporarily remove it from the sync process. This option is suitable when you have very large data sets where not every column is required, as it makes sense to only sync the columns that you want to see in Phocas.

Regarding the Data Types field, all columns are treated as text unless otherwise defined. You can also set the datatypes when viewing the item in Sync Sources in Phocas.

If you are adding a new file, click the Edit button in the Add File window.

If you have already added the file, double-click the file in the Data Item tab to open the Edit File window, then click the Edit button. Note: If you change the order of columns, add new columns or change the type of data within a field, the column mapping and/or data types must be updated or the sync might fail.

Make your changes, or to disable a column, clear (uncheck) the Enabled checkbox, then click Save.

After you save the file, an asterisk displays in the Edit Columns column in the Data Items tab.

The Autodetect Delimiter option is available for files and directories and it's selected by default. As the name suggests, this option automatically recognizes common delimiters that might be used to separate your data, such as commas and tabs, and separates the data into columns accordingly.

You might prefer Sync to ignore these delimiters and select a specific delimiter to use instead. This option is useful if your data contains other delimiters, such as ^ symbol. If this is the case, clear (uncheck) the Autodetect Delimiter checkbox and enter a delimiter in the box below it. Note that the tab delimiter is only compatible with TXT files.

The Ignore quotes option is available for files and directories. The checkbox isn't selected by default, which means quotes in data are interpreted as the opening quotes of a single string. This can cause a single cell to be populated with rows of data until the next set of quotes is found. The difference is shown in the tables below.

You can select the checkbox to ignore quotes when syncing to handle files containing embedded quotes in the data, such as product descriptions that contain imperial measurements and, therefore, the " character. For example, a 12" ruler. When the checkbox is selected, any embedded quotes are treated as normal text characters and the data is written out correctly rather than defining a single string (escaping the delimiters).

The Enabled option is available for all data item types. It's selected by default, which as the name suggests, means all of the items are enabled to be synced.

You might prefer to disable an item, so Sync temporarily removes it from the sync process. This allows you to control when it's synced; you sync it manually. This is a good idea when the item is not required for analysis. Disabling an item doesn't delete the item; it just means that no new data for that item will be synced as long as it remains disabled. The Last Successful Sync date will not change.

To disable an item, clear (uncheck) the Enabled checkbox. You can do this either when adding or editing the item, or on the Data Items tab.

To re-include that data item in the sync process, select the Enabled box.

This Incremental Process option is available for files and SQL items. It's not selected by default but you can select the checkbox to activate the incremental process. There are two incremental process methods, by date or by key.

The benefit of this incremental sync is the prevention of very large sync items when only the recent data has changed. Your historical data is retained while new data is synced. The incremental process typically syncs all data within an offset period, for example, rolling 30 days.

Incremental process by date

Historical data is stored on the Phocas server, so only a small subset needs to be synced to keep the data up-to-date.

• The data item must contain a DateTime column, which is often the transaction date. If the item does not have such a column, this option is unavailable. Modified dates are ideal but, if the data cannot change after being entered, then entered dates can be used i.e. General Ledger Postings.

• It uses a purge and append method. Phocas works out the earliest date in the item you're syncing and deletes all data from that date forward from the server/cloud. Then the item is synced in the usual way; all the data is added to the existing data on the server/cloud.

• To use this method, select Incremental Process > By Date, then select the applicable column. If you know your item has a Date column but this option is still unavailable, click Edit and set the data type to DateTime, then try again. See the section about column mapping above for more information.

Incremental process by key

• Any data type column can be selected in this case. As such, this option can be used with automatic columns for text files.

• It must have a way to uniquely identify each row and must have a sequential identity or date column. the Primary Key or Unique ID columns are best. Multiple columns can be defined to create a Unique ID for the rows.

• It uses a merge process. New rows (as determined by the selected columns) will be inserted. Existing rows (as determined by the selected columns) will be updated with the new values of the other columns.

• To use this method, select Incremental Process > By Key, then select one or more columns to be used as a uniquely identifying key for your data. You can use the High Water Mark option to get a most recent date from the server to use in the query (see below).

High water mark

You can use High Water Marks to reduce the amount of data fetched by an SQL query. However, they can be tricky to get right, so if you're interested in this feature, please contact your Customer Success Manager for help.

These are best used with sequential data, i.e. when using incremental by date or by sequential ID. They can also be used with a predicate so that if the column used to define the high water mark is grouped, i.e. ID is specific to an entity (such as a company or branch), Sync can still use that column.

Troubleshooting

After you have synced an item using an incremental process, the structure of the item should remain unchanged for subsequent syncs. Changing the columns will cause subsequent syncs to fail. To avoid this happening, select Incremental Process > None, sync the item, and then select the incremental process again.

The Add header rows option is available in directories and spreadsheets. There must only be one header row, as multiple header rows are not supported.

If you're adding a spreadsheet that has multiple tabs, ensure each tab has a header rows.

The Retain snapshots option is available for files and SQL items. It's typically used to convert values as at now (live) type query into over-time analysis.

A popular use case is Stock on Hand, where a snapshot is taken each day. Without enabling this option, the Phocas database would just show the stock from yesterday. By retaining monthly snapshots, you could see what the stock was yesterday, and in any previous months, allowing variance reporting and trend analysis. This historical data is usually not available in an ERP system.

Snapshots are taken for every moment, even when there is no change to source data.

Impact of snapshots on build times Use caution when retaining snapshots, as they can result in very large volumes of data being stored, which can cause databases to take too long to build. This is especially true for daily snapshots. Snapshots should NOT be used for large data sources, such as sales invoices.

Enable snapshots

1. Select Retain Snapshots > Enable Snapshots checkbox.

2. Select whether you want snapshots retained daily, monthly, or a custom interval, such as quarterly. For example, if you select Daily, you can sync as many times as you like and Phocas keeps a copy of the data as at the last sync of each day. Similarly, if you select monthly, a copy of the data as at the end of the month is kept.

3. Enter the number of days you want to retain the snapshots. This is important, as it's not good practice to retain data that is not required for analysis.

4. Click Save.

View snapshots

Snapshots are kept over time in the relevant Sync item table, with a SyncMoment column automatically added.

To view the items in Phocas, go to Administration > Sync Sources and select the relevant source to view the data items in more detail. You will see not only the latest synced data but also data from retained snapshots. For example, in the image below, the most recent sync was on 2018-01-17. You can see this data, and data from the previous sync a day earlier, on 2018-01-16.

You can use snapshots in database designs to map to the date field of the database.

Sync is often the first step of our ETL (Extract, Transform and Load) process where we gather the required data, format it and add calculations and other fields as required and then load it into Phocas ready for you to use in the Designer.

It extracts the data items from your data source, divides it into manageable chunks, encrypts those chunks and send them securely via HTTPS to the Phocas servers.

Sync reads the data specified by the user - SQL, text file, spreadsheet etc.

The data is checked for changes via a MD5 hash match comparison with the previous Sync session:

• If there has been no change, the file is skipped.

• If the file has changed, only the changes are written to a .diff file.

The data is processed and validated where possible (for data types, consistent columns etc.), then written to .psv files.

The .psv and .diff files are zipped and sent to the server.

The server unzips the files:

• .diff files are referenced and changes applied to the server copy of the .psv files

• the .psv files are written to the PhocasSync database

The Parameters tab in the sync file configuration provides an advanced technical option for files, directories, and SQL data items. It allows you to specify parameters to use the Sync file for multiple organizations in Phocas. The parameters define strings of text that can be used in multiple places, including in connection strings, SQL queries and Sync Sources, making it simpler to perform updates and changes. The parameters are passed to Sync and as a result, the information structure is sent to other organizations.

1. Select the Parameters tab and click Add.

2. Enter the parameter name. This must be alphanumeric only; you can't use special characters.

3. Enter a value. Values can not contain parameters. Double quotes cannot be used.

Using parameters as values in supported types

Example of use: Connection string

Example of use: SQL

Parameter substitution in scheduled Syncs

You can substitute different values for defined parameters via scheduled Syncs (SyncCLI).

Individual parameters: To substitute a parameter value, the “-p” switch is used, followed by the parameter name and quoted value. The quoted value is only used for the duration of the session, i.e. the original value defined in Sync remains unchanged.

-p:parameterName “newValue”
-p:server ”localhost“

Note if a parameter name contains spaces, it must also be quoted:

“-p:My Server” “localhost”
“-p:Test Database” “Testing”

Example format for substituted parameters:

-p:server: “DatabaseServer“
-p:database "Sales2016"
-"p:db table" "Sales Customers"
-p:columns "ID, Address"

Multiple parameters: You can substitute several parameters for a range of data items.

These multiple parameters can be entered in the Arguments field in a scheduled task:

-r "c:\temp\new sync\multipleparameters.sync" -user "John User" -password "myPassword" -service "http://company.example.com" -source "Sales" -p:server "DatabaseServer" -p:database "Sales2016" "-p:db table" "Sales Customers" -p:columns "ID, Address" "-p:sales directory" "C:\temp\Sales" -p:filePath "C:\temp\Customers.txt"

Iterations

Although basic information is provided here, the Iterations option is an advanced feature that we recommend you use in consultation with a Phocas consultant.

Iterations use the power of parameters to allow the sync process to be repeated with a new set of values in the parameters. Each time Sync is triggered, all items are processed for each iteration, in order, with the default parameters being overridden by the iteration parameters.

Iterations are useful in the following situations:

• Where there are multiple data sources, particularly multiple ERPs, linked to one Phocas site. Typically, these data sources are identical, except for having unique credentials and one or more different parameter values. As the setup is almost the same for each source, you can add iterations to the Sync process. This iterative approach removes the need for separate configuration files and makes the Sync setup quicker and less complex.

• Where the same queries are used for multiple databases or companies within a database.

To use Iterations:

1. Select the Iterations tab and click Add.

2. Enter a name for the iteration.

3. Add the parameters for the iteration.

   • You can click the Populate DataSource Parameters button to populate the iteration with the default parameters configured on the Parameters tab.

   • If a parameter isn’t defined for an iteration, the default parameter will be used.

   • If the same Sync Source name is used, each sync item will be added to for each iteration. Parameters can be used in the Sync Source name if separate Sources are required, and in Sync Item names if separate items are required.

• Keep code simple and easy to read. If it’s complex, it’s likely to be wrong or there’s a better way to do it. Be consistent with styles. Add notes.

• Include only the columns required.

• Include any columns used in calculations for clarity and validation.

• Ensure data type columns are consistent, i.e. ‘FLOAT’ for number columns, ‘DATE’ for date columns etc.

• Limit column widths. For notes type columns is the whole width required? Can it be truncated with LEFT()?

• Limit historical data. Use a WHERE clause filter to include only relevant data that is not fixed.

• Use a consistent sort order: oldest to newest. Sync can skip uploading unmodified parts.

• Avoid instances, subdomains and schemas where possible when listing tables to future-proof against changes and migrations.

• Follow the hierarchy of data transformation: Source data, Extract, Phocas Views/Post Sync Actions, Transform Columns.

Simple SELECT

select top 200 id from entity_1 order by id

CTE

with customers (customer, city, state, status) as
(
 select cat_1, cat_2, cat_3, cat_7 from entity_1 
 where id < 1000
),
active_customers as
(
 select customer, city, state from customers
 where status = 'active'
),
active_VA_customers as
(
 select customer, city from active_customers
 where state = 'VA'
)
select * from active_VA_customers
where city like 'N%'

Execution scripts

declare 
 @query varchar(max),
 @insertQuery varchar(max),
 @database varchar(100),
 @table1 varchar(100),
 @table2 varchar(100),
 @period varchar(100)
set @database = 'phocas_testing_sales'
set @table1 = 'Transaction_0_1'
set @table2 = 'QuantaB'
set @period = 'PeriodIndex_0'
select @query = '';
select @insertQuery = 'select StreamsID, PeriodIndex, Branch_1, Scalar_1, Scalar_2, Scalar_3, RecordCount from {database}.dbo.{table1}
union all
select StreamsID, pm.PeriodIndex AS PeriodIndex, Branch_1, sum(Scalar_1) as Scalar_1, sum(Scalar_2) as Scalar_2, sum(Scalar_3) as Scalar_3, count(*) as RecordCount
from {database}.dbo.{table2} 
inner join {database}.dbo.{period} pm on (pm.Moment = QuantaB.Moment) group by StreamsID, pm.PeriodIndex, Branch_1'
select @insertQuery = REPLACE(@insertQuery, '{database}', @database)
select @insertQuery = REPLACE(@insertQuery, '{table1}', @table1)
select @insertQuery = REPLACE(@insertQuery, '{table2}', @table2)
select @insertQuery = REPLACE(@insertQuery, '{period}', @period)
exec (@insertQuery)

0: success.

1: Sync file doesn't exist.

2: incorrect service URL, username, or password.

3: all other exceptions.