If you require assistance installing or configuring Sync, or need any other help, contact our Support team.
Phocas Sync, also known as Sync Client or simply Sync, is one way to get your data into Phocas.
Sync is a small Windows-based piece of software (middleware) that you install on your computer. It connects to your raw data items, collects them, and securely sends them to your Phocas site over the Internet.
Sync can connect to multiple data sources, including SQL and other ODBC (Open Database Connectivity) compatible databases, as well as flat files in multiple formats (Excel worksheets and CSV and TXT files). The data items from these sources become available to your administrator in Designer as sources for database design.
As the name suggests, Sync synchronizes updates to the synced data at scheduled times, such as overnight or weekly. This regular synchronization is important for most Phocas users, as they want to view the latest data whenever they open a database.
The following diagram summarizes the Sync setup process (purple boxes) and shows the output (green box). The same steps are required when you need to reinstall new versions of Sync.
Step 1. Download the Sync Installer
Before you download and install any files, please read the System requirements page and take note of the following points:
Although Sync can connect to data sources on network drives, it should never be installed on a network drive.
Sync doesn't have to be installed on the same physical machine as the data source as long as it can access that data source from its location, such as via a network drive.
(Applicable if you are reinstalling Sync) Ensure you don’t have Sync open or currently running.
Click the applicable Sync Installer link to download the Sync Installer to your computer:
If you are reinstalling Sync, ensure you install the file in the Sync application folder, not the Sync files. Otherwise, it will overwrite your existing projects.
Locate the downloaded SyncInstaller zip file on your computer.
Unzip the file (right-click the file name and click Extract All), noting where the file will be extracted.
Locate the extracted SyncInstaller file, then right-click > Run as Administrator.
Follow the steps in the Sync Installer wizard, noting where the Sync application folder will be installed on your computer. By default, it's installed on the root of the C: drive, in a folder called Sync inside a folder called Phocas.
The Sync folder contains the Sync app and later, the Sync file, which is essentially a configuration file that tells it where to get the data, what to do with it, and where to send it.
Step 3. Configure Sync
The configuration of Sync involves connecting to your Phocas site and creating a source. The source is the directory in Phocas to which you will send the data items.
On your computer, locate and open the Sync app (PhocasSync.exe).
If you've already installed Sync and configured one or more sync files, the last used file will open. Don't edit this existing sync file unless you are confident in what you are doing. Mistakes can have huge consequences that can be time-consuming and costly to fix. If you have any doubts, please contact your Customer Success Manager.
In this case, it's best to create a new file: Click File > New.
Click the Configuration tab.
Enter a name for your Sync data source.
This is the name of the container for the data you're going to send to Phocas. You'll see this name on the Sync sources page in Phocas. This isn't the name of the Sync file itself; you add that later (see Save the Sync file below).
Enter a name that will allow end users to easily identify the data. The typical naming convention is [DataSource]-[RawData]. Don't use an underscore in the name, as this is a reserved character.
Enter your Phocas service credentials:
Enter the base URL (web address) of your Phocas implementation, including the prefix (https://). For example: https://yoursubdomain.phocassoftware.com.
Enter your username and password.
Ignore the Only sync data items used in databases option, as it is not applicable at this stage.
Change the default [30]Days To Retain Logs, if required.
Each time the sync process runs, a log file is saved. You can set how many days you want to keep those logs. After the specified number of days is met, files older than that number of days are automatically removed. If you schedule Sync to run every hour, you will quickly accumulate a lot of log files, so you might want to reduce the number of days. You can change this number later.
Proceed to add data items.
Step 4. Add the data items
After you install and configure Sync, the next step is to add (connect to) the data items that you want to sync to your Phocas site. The Sync setup process is incomplete until you add at least one data item and run the sync process. You can add more data items later.
Regardless of the data type, the data must be well structured and consistent and only contain what is required for analysis in Phocas.
In Sync, click the Data Items tab.
Click Add and select the required data item type. Typically, you will either add files or an SQL table or view, but several other data item types are available. Expand the section below to learn about the available types.
Data items (file, directory, spreadsheet, SQL, and database)
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.
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
Click Add.
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.
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
See below for information about the Enabled, Incremental Process, and Retain Snapshots options.
Enter the SQL Query into the box.
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.
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.
Supported data types
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 and constraints
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.
Click Browse and select the path for the item. This is where it's located on your computer. The name is then populated for you, but you can edit it if required.
Select the applicable options for the item and click Save. The available options depend on the data item:
Review column mapping, and edit or exclude columns
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.
Select your own delimiter when syncing
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.
Ignore quotes
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).
Disable a data item from syncing
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.
Set data items to sync incrementally
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.
Include header rows
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.
Retain snapshots
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.
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.
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.
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.
Step 5: Save the Sync file
The Sync file is like a package that contains the sync configuration, source, and data items. You must save your Sync file to preserve your sync setup. The Sync file name does not appear anywhere in Phocas; it is for reference only on your computer.
In Sync, click File > Save, locate and select the Phocas > Sync folder and click Save.
Step 6: Sync the data (manually)
The last step in the process is to sync your data to your Phocas site. Even if you intend to schedule the sync process, you still need to run the sync process manually at this point for the source and data items to appear in Phocas.
Select one of these methods:
To sync all the data items, click Sync Now.
To sync one or more specific data items, select their rows(s) and click Sync Selected Now.
It might take a few minutes for the sync process to complete. The progress is displayed.
Next steps
After you complete the sync setup process, you can take the following actions:
View sync sources and items in Phocas: The data items will appear within the source directory in the data sources panel in Designer, ready to be used in database designs. You can also view the source (and access its data items) on the Administration > Sync Sources page.
Only sync data items used in a database: This option only applies after the first sync process. That is, on your first use of Sync, all the data items will be synced, regardless of whether they are used in any Phocas database designs. Afterward, you can choose to only sync the data items used in database designs.
Update Sync: The next time you open the Sync app, if there's a newer version available, you'll get an alert message to that effect. Click the link in the message to download the newest version. See step 2 above, as the process for reinstalling Sync is the same.
Versions of Sync before version 24 will not get the alert, so you need to manually update Sync using the installer link in Step 1 above. To check which version of Sync you have, open the Sync app and click Help > About.
It's best to use the latest version of Sync as it has all the latest features, and behind the scenes, it's the most efficient, so the resource use will be lower, and sync times are likely to decrease. If you do want to update an older version, please back up your Sync files first because if the upgrade causes any issues, the Sync file cannot be downgraded.
Additional technical information
How Phocas Sync works
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
Note about number columns
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.
Parameters and iterations
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.
Select the Parameters tab and click Add.
Enter the parameter name. This must be alphanumeric only; you can't use special characters.
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:
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:
Select the Iterations tab and click Add.
Enter a name for the iteration.
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.
SQL query best practices
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.
SQL script examples
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)
SyncCLI error codes
0: success.
1: Sync file doesn't exist.
2: incorrect service URL, username, or password.
3: all other exceptions.
Troubleshooting
If a sync fails, you can check the log files, which are stored in C:\Phocas\SyncFiles\Files\Logs.
The latest log is also available on the Phocas Sync Sources page.