United Kingdom +44 (0) 1424 863 450   hello@simego.com

Team Blog

Keep up to date with everything going on at Simego.


19 April 2016

Output Change Set Data

Outputting the changes between sync’s has been requested by several clients recently.  There’s not an easy way to do it other than enumerate the change set. We are hoping we can fix this so this is a request for some feedback!

 

Currently we have added a Method called GetChangeSets() on the compareResult this returns an object with three DataTableStore objects Added,Updated and Deleted.

 

There is also a Helper Method to combine them into a DataSet with 3 tables, where you can then simply write the DataSet to a File.

 

i.e.

 

public override void AfterCompare(ProjectAutomationCompareResult compareResult)
{
        if(compareResult.HasChanges)
        {
            compareResult.GetChangeSets().GetAllChangesDataSet().WriteXml("AllChanges.xml", XmlWriteMode.WriteSchema);                
        }
}

 

image

 

One problem with the .NET DataSet object is it likes to munch on system memory so if the change set is large it’s going to eat away at a lot of memory.

 

So I guess we’re asking given a change set what do you want to do with it?

 

3xCSV files? 3xXML Files?

 

How would you see this working?


30 March 2016

Converting Integers to Guids

We know that Integers are not Guid’s they are very different data types. This causes a few complications with systems like Dynamics CRM that uses Guid’s for Primary Keys.

 

Most source systems will use an Integer as the Primary Key typically an auto-incrementing ID column on the source table.

 

If we could simply create a Guid from the Integer value we could keep the Integer ID column in our source system and link it to the Guid Primary Key in the Target System. No longer would we need to map the ID to a custom column or manage other lookup/conversion tables.

 

If we take a Guid value and replace the first 4 bytes with the bytes from an Integer we can then reliably convert an Integer to a Guid.

 

OK so we loose the fact that we can guarantee that every Guid value is unique, however are these values really likely to exist in our small applications?

 

Taking an Empty Guid and replacing the first 4 bytes with the values 1-9 produces.

 

00000001-0000-0000-0000-000000000000

00000002-0000-0000-0000-000000000000

00000003-0000-0000-0000-000000000000

00000004-0000-0000-0000-000000000000

00000005-0000-0000-0000-000000000000

00000006-0000-0000-0000-000000000000

00000007-0000-0000-0000-000000000000

00000008-0000-0000-0000-000000000000

00000009-0000-0000-0000-000000000000

 

Or Take an existing Guid and replacing the first 4 bytes with the values 1-9 produces

 

00000001-7df5-4947-ac47-e05ea89d21e4

00000002-7df5-4947-ac47-e05ea89d21e4

00000003-7df5-4947-ac47-e05ea89d21e4

00000004-7df5-4947-ac47-e05ea89d21e4

00000005-7df5-4947-ac47-e05ea89d21e4

00000006-7df5-4947-ac47-e05ea89d21e4

00000007-7df5-4947-ac47-e05ea89d21e4

00000008-7df5-4947-ac47-e05ea89d21e4

00000009-7df5-4947-ac47-e05ea89d21e4

 

So as of Data Sync 3.0.992 release we have added a new Calculated Column function NEWGUID(int) and NEWGUID(guid, int) these functions help us produce the values above.

 

This version will merge your integer value with an Empty Guid.

 

NEWGUID(ID)

 

This version will merge your integer value with a defined Guid value.

 

NEWGUID(GUID("{E03F2193-7DF5-4947-AC47-E05EA89D21E4}"), ID)

 

So now you can convert your source integer ID columns directly to a Guid to use with Dynamics CRM and map directly to the CRM Entity ID.


16 March 2016

Incremental Lookups

Release 3.0.986+

 

When performing a Lookup into a Large data set, this can take a long time since the target data set is loaded before the lookup can be returned. This generally works well when your source is the entire data set or the target lookup is small.

 

When you use Incremental Mode in data sync your source may return a few records and need to lookup into a target data set of millions to get the lookup value. This is not optimal since it requires a full load of the target lookup data set.

 

We have created LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL these functions are exactly the same as LOOKUPA and LOOKUPB except they work incrementally. The result of each lookup operation is cached so that the same value is not looked up multiple times.

 

These functions are supported with the following Data Sync providers.

 

  • Dynamics CRM
  • SQL Server
  • OleDB
  • ODBC

 

For Dynamics CRM this Incremental Lookup will execute a FetchXml query for each new value required from the Lookup. Therefore you want to only do this on a few source source records to reduce the server round trips required to lookup each value.

 

Example

Using LOOKUPBINCREMENTAL to lookup the accountid from the account entity when the account.name equals CompanyName in the source data.

 

LOOKUPBINCREMETAL("accountid", "account", WHEN("name", CompanyName))

 

The function signature of LOOKUPAINCREMENTAL is exactly the same as LOOKUPA so you can easily swap them and see the benefit right away.


For your custom providers that want to support the Incremental Lookup function, you will need to implement the IDataSourceLookupIncremental interface.

 

Then implement the GetLookupValue method and return the value. You do not need to implement the caching this is done for you.

 

public object GetLookupValue(DataLookupSource source, string column, IDictionary<string, object> conditions) 
{
    return null;
}

15 March 2016

SQL Server Insert Performance Boost

We’ve made some significant performance improvements to our SQL Server provider in Data Sync. (Release 3.0.986+)

 

To support the test scenarios below we configured the CommmandBatchSize to 50 and the Transaction to 1000.

 

image

 

CommandBatchSize defines how many rows we send to SQL Server in a single command. There is a limit of 2000 parameters per request so ensure that the sum of your batch and columns is well below this limit.

 

Transaction defines how many rows make up a transaction. This is a new feature and previously we would get an auto-commit transaction on each row. To prevent Data Sync creating Transactions set this to Zero.

 

I have created 2 tests to try and get the fastest possible result one on the LAN and one Local. This is not a real-world example just a demonstration of the limits.

 

Test 1: 1,000,000 Rows to be INSERTED over the local LAN to SQL Server 2014 hosted in Hyper-V.

 

SQL Test 1

 

Results:

Process takes 1 minute 7 seconds to insert all 1M rows that’s just over 14,000 rows per second.

 

Now running the exact same test except to target a SQL Server Express Database on the same machine as where Data Sync is running.

 

Test 2: 1,000,000 Rows to be INSERTED to local SQL Server 2014 Express instance.

 

SQL Test 2

 

Results:

Process takes 14 seconds to insert all 1M rows that’s just over 70,000 rows per second.

 

Considering the previous version of Data Sync would insert at around 500/Sec and the speed would slow down over time this is a major improvement when working with large data sets and SQL Server.

 

The changes we made to the SQL Provider also have a knock on effect to other providers for example in Dynamics CRM we are now seeing linear insert performance over time.

 

Incremental Load for SQL Server also now will load a much larger Data Set in incremental Mode, this 1M row Data Set can be loaded and compared in 8 seconds in Incremental Mode.


19 February 2016

Ouvvi Update for 2016

We have a new Data Sync and Ouvvi update available for download in beta https://www.simego.com/install/data-synchronisation-studio-beta

 

The main changes in this release relate to updates to Ouvvi

 

We have refreshed the UI a little to make it a bit cleaner and removed some unnecessary tabs and options.

 

Ouvvi UI Refresh

 

Short-cut Mini Menu to recently used Step Types so it’s quicker adding a new Step to an existing project.

 

Ouvvi Mini Menu

 

Colour Themes – Set the Visual Colours through CSS https://www.simego.com/help/ouvvi/set-the-colour-theme-of-ouvvi

 

Theme Green

Theme Orange

Theme Black

 

Stop and Start the Ouvvi Service https://www.simego.com/help/ouvvi/set-permissions-to-enable-service-control-in-ouvvi

 

Ouvvi Service Control

 

Version History, now you can view the Data Sync project configuration in History View and download a copy of an old version or restore the version to the current one.

 

Ouvvi Step History

 

Re-use the Connection Library in Ouvvi Step types, for example use a SQL Server Connection in the SQL Batch Step handler.

 

Ouvvi Connection Library

 

New Step Handers, UnZip a File and Change a Trigger Status.

 

Updated Azure SDK version for Azure File upload and download.


23 November 2015

Dynamics CRM Entity Trigger

We have updated the CRM Entity trigger in Ouvvi release 2.5.350 (Data Sync v3.0.972) to support CRM 2015 and also CRM Online.

 

Simply enter the base URL to your Dynamics CRM Server (https://crm.dynamics.com for CRM Online) and the Organisation Name along with the Credentials. This is the same as when you connect to CRM in Data Sync as Ouvvi is actually calling the Data Sync CRM Provider for this.

 

The trigger runs a FetchXml query against CRM to get the MAX of modifiedon from the Entity you specify. You can optionally add a FetchXml Filter if you want to only trigger based on a subset of values in a given Entity.

 

image

 

Multiple Triggers for the same CRM Server will re-use the same underlying connection and therefore running multiple CRM Triggers to the same CRM Server is pretty fast.

 

Typically any projects linked to a CRM Trigger will be run within 30 seconds of a CRM data change with the default configuration.


21 October 2015

Automatic Schema Mapping

We have a new Schema Mapping feature in the latest Data Sync Beta release that is designed to assist in the mapping of a Source system to a Target System.

 

This new auto mapping feature will look at the schema column names, data types and also a snapshot of the Data to automatically generate the Schema Mapping.

 

Lets take an Example, Here we have the Northwind SQL Demo Database Products table as our source.

 

image

 

I have created a CSV file with some of the data, 10 rows and modified the names of some of the columns to see whether Data Sync an Automatically map this for us.

 

image

 

The default mapping gets this far, not really very good, the column names do not match enough to get any sensible default mapping.

 

image

 

However if we select the AutoMapper from the Schema Map Toolbar.

 

image

 

Select the default options.

 

image

 

We now get a much better result, in fact the Auto Mapper has successfully managed to map all the Columns.

 

Auto Mapper Results

 

You will notice that CategoryID and SupplierID columns are mapped correctly even though the data is very similar and we even managed to map Discontinued to Enabled based entirely on the data values.

 

The Options of the Auto Mapper allow you to remove columns that have no data i.e. are all NULL on the source and columns that just have a default value i.e. are all the same. This is handy when you have many columns in your data sources but you want to find the columns that contain data.


9 October 2015

Data Sync and Ouvvi Update

There have been many updates and features added to Data Sync and Ouvvi recently with the release of Data Sync 3.0.966 and Ouvvi 2.5.346.

 

It was time that we listed the changes so that you could see what we have been up to.

 

The two major features are the Ouvvi Multiple Instance support, great for DEV/PROD configuration on a single server and Data Sync Item Events allowing you programmatic control during the target write phases.

 

Amazon

 

New provider that supports Amazon S3 Blobs, Read and Write Files to Amazon Blob Storage. Easily upload and download files with Amazon S3 Storage and use MD5 hash to verify the change.

 

Azure

Support for Azure Storage Blobs with the File System to enable simple upload and download of files into Blob Storage.

 

Support for Azure Storage Tables, simply synchronise your Data with Azure Table Storage. Add additional attributes and support for batch operations.

 

Both Azure Providers now support the connection registry and Server Explorer.

 

image

 

Active Directory

Active Directory provider now supports Automation Item events allowing you to intercept operations against AD and to extend the functionality. Such as Setting Passwords on new accounts etc.

 

    public override void AfterAddItem(object sender, DataCompareItemInvariant item, object identity)
    {        
        Trace.WriteLine("AfterAddItem->{0}", identity); 
        
        using(var entry = DataSourceB.GetDirectoryEntry(identity)) 
        {                        
            var uac = (int)entry.Properties["userAccountControl"].Value;            
            uac = uac | 0x10000; //Password never Expires
            uac = uac & ~0x2; //Unlocked
            
            entry.Properties["userAccountControl"].Value = uac;
            entry.Invoke("SetPassword", "!password123");
            entry.Properties["pwdLastSet"].Value = -1;            
            entry.CommitChanges();            
        }        
    }

 

Dynamics CRM

Dynamics CRM Connector, supports Dynamics CRM 2015 and also implements Automation Item Events when Batch and Thread Count equal 1.

 

Dynamics CRM Fetch Xml Provider

A new provider for Dynamics CRM that takes a FetchXml Query as the source, this can either be a Saved Query in CRM or a hard coded Xml.

 

File System Provider

The file system provider now provides real-time feedback during the data load to allow for stop operation and also supports the Automation Item Events. File System can be configured to only return files that have been modified since a certain date.

 

Grouping Analysis Provider

New provider that can be used to group and summarise data, supports the usual set of aggregate functions.

 

SUM, MIN, MAX,FIRST, LAST, AVERAGE, MODE, MEDIAN, VARIANCE, RANGE, STDEV, STRINGARRAY, INTARRAY

 

OData

The OData provider now supports Automation Item Events.

 

OpenXML Provider

New Provider for Excel using the OpenXML format eliminates the old 32/64bit issue with the previous excel provider that targeted the Microsoft Office OleDb drivers.

 

Podio

The Podio Items provider now supports Automation Item Events.

 

SharePoint

SharePoint providers ClientAPI and Online have been updated with new client libraries and support for the new Automation Item Events. This allows you to intercept operations against SharePoint to write your own implementation or other required functionality.

 

Simego Web API

The Simego Web API provider now can connect to Ouvvi to enable Read-Write operations against the Ouvvi Database to sync up Projects, Steps etc.

 

SQL, OleDB, ODBC

The SQL, OleDB and ODBC providers now support Automation Item Events. Also we have added Helper Functions ExecuteNonQuery, ExecuteScalar and UpdateSourceRow to enable simple write back scenarios.

 

Support for SQL Server LocalDB instances.

 

Values Service Re-Direction

When your project is running under Ouvvi you can now re-direct the Values Service to obtain values from the Ouvvi User Settings Store. It is possible to both Read and Write against User Settings hosted in Ouvvi via the Values Service.

 

Ouvvi User Settings

Creating Project Properties with the same name as Ouvvi user Settings causes the values of the properties to be updated at runtime from the Ouvvi User Settings.

 

Ouvvi Multiple Instances

You can now install multiple instances of Ouvvi on a single server, each instance is isolated from the others. This allows different versions of Ouvvi and Data Sync to be running on a single server to aid upgrade scenarios. Each instance of Ouvvi has a SQL DB, Windows Service, Data Sync install and private Connection Library. You can mix this configuration to suit your requirements.

 

image

 

Ouvvi Connection Library Re-Direction

Opening a Data Sync project hosted in Ouvvi will cause the Connection Library of Data Sync to be re-directed to the Ouvvi Server hosted version.

 

Ouvvi Data Sync Project View

You can now view the details and mapping of a project directly in Ouvvi without the need to open the project.

 

image

 

Ouvvi Data Sync Connection Mapping

You can now re-map the connections of a Project hosted in Ouvvi without opening the project in Data Sync.

 

image


17 July 2015

Data Sync and Ouvvi update

In the Latest Data Sync beta build (3.0.936) for Ouvvi we have enabled the ability to view the Data Sync project configuration right in the browser.

 

This allows for a quick overview of the project and the mappings. You can even switch the connection library connections right in Ouvvi.

 

image


14 July 2015

Multiple Isolated Ouvvi Instances

As of release 3.0.930 of Data Synchronisation Studio it is now possible to install multiple instances of Ouvvi on a single Server. This allows you to create a instance for say DEV, UAT and PROD on a single server. Or you could use this to break down a large Ouvvi instance into smaller Line of Business processes or similar.

 

Each instance has it’s own SQL Database, Connection Library, User Permissions, Ouvvi Service and version of Data Sync. This allows you to test new releases of Data Sync and Ouvvi without impacting a production instance.

 

Because of this change, Ouvvi is now distributed as part of the Data Sync install rather than a separate install. The install of Ouvvi is a manual process that involves pointing IIS to a directory that holds a copy of Ouvvi and running the in-browser setup and configuring the Windows Service.

 

We will be documenting the installation process shortly however if any clients would like to try this out please get in touch via our support desk.

 

We have a quick guide for installing a Developer Instance with IISEXPRESS and LOCALDB here https://www.simego.com/Help/Ouvvi/Install-Developer-Instance-of-Ouvvi-Automation-Server