Group By Analysis

The Analysis Group By provider in Data Sync takes a number of Data Sync project files as the source Data and applies grouping and aggregate functions to the results. You can also use a single project or file to allow you to perform functions or groupings on the data found in the source.

You can either use the connector within the connection window or you can use the quick function from the Tools menu. The quick function will link the current project to the grouping project.

Aggregate Datasources

If you need to use two sources as a single source, essentially perform a query union join, then you can do this with a grouping project.

This will enable you to pass the paths to two or more project files and then perform aggregations from this as needed.

An example of where this might be useful is if you have multiple Active Directory OUs and you want to join them into a single table.

Below we will cover how you could configure the project for the above example. This assumes you already have two projects that connect to the OUs you want to join and the columns you want to use are added to the schema maps.

Start by opening a new Data Sync project and then open the connection window. Browse to Other and then Group By Analysis.

In the sources field open the collection editor and add the paths to each of the projects you want to join the sources of. Clicking onto the ellipsis will allow you to browse the file explorer to find the project files.

Click OK to add the sources and then click Connect to create the connection.

Now you can add a column to group the data by, in this example we are using the DS-SAMAccountName for the users.

Click OK to add the group column to the project and then click refresh in the datasource toolbar to show the column in the column window.

The next step is to add the columns you want to use to the ValueColumns property by clicking on the ellipsis to open the collection editor.

Here we have added five columns, one of which is the OU column where the data type is a string, the function used is first (to return the first value), the name to be displayed is OU, and the column name from the source projects is DS-OU.

To add these to the project click OK and then refresh the datasource window using the refresh button.

You can then add these to the schema map and connect to your target datasource to synchronise the results as you would in a normal Data Sync project.

You can preview the data from the schema map to ensure it is returning the data as expected.

Grouping Using Quick Start

To perform grouping functions on the dataset within your Data Sync project start by creating your source data project and create any extra columns that you may need for the grouping. Then add the columns you would like to use within the grouping project to the schema map and map the target to a Null/ Empty data source by going to Tools > Create Null/Empty Datasource.

For example in the screen capture below we have added a lookup to the supplier table to return the supplier details alongside the product information. Added the columns to the schema map and created a Null datasource to ensure there is a valid schema map table.

The data preview shows duplicate key values as the key column (SupplierID) appears multiple times. As we will be using this project as a datasource we do not need to worry about this as it will be resolved once the data is grouped.

Remember that if your dataset doesn't contain the data you need you can add calculated columns or other lookup columns to the schema map as needed.

Then expand the Tools menu and select Use Project with Analysis Grouping.

This will open the Analysis Grouping Options window where you need to specify the columns to group by and the function that should be applied and to which columns.

The Group By column is the column or multiple columns that define the group. In the example below we group by the lookup column Company Name, but you can use multiple columns to create the group, e.g. an ID and name, if your data needs that.

In this example we group by Company Name and then return a string array of the Product Names for all products with that company name.

You can add additional functions later on if it is needed.

Clicking OK creates a new project with the current project as the source of the data, with the group and value columns as the columns.

Below you can see the company name and the products supplied that by company appear in a string array separated by a semi-colon.

You can now use this as a Data source as normal and map the results to your target or simply export the results.