Team Blog

Just a quick post to show that we now have code highlighting and intellisense type support for our new calculated column feature.

For example below is a screenshot of our CASE function which can be used to transform a value into 1 of several possibilities.

We also have a full list of our supported functions here http://www.simego.com/Help/Functions

We have a great new feature for Data Sync that we just have to tell you about, previously if you wanted to transform some value we had “Dynamic Columns” where you could write your own .NET C#. Now we have a simplified yet very powerful solution for when you just need something simple or if your not very good with .NET. We’ve tried to make it similar to other scripting solutions whilst keeping the ability to be really advanced if you need to.

For example below is a little snippet that adds 30 days to Today.

As with everything we do we’ve made it really easy just hit the (Fx) button on your Data Source to add a new column based on your Function.

We’ve added lots of useful functions so you can virtually do anything you need, plus you can even mix it with the existing Dynamic Columns feature.

Here’s a List of Current Functions with more on the way!

AND(params bool[])
BITWISEAND(params int[])
BITWISECOMPLEMENT(params int[])
BITWISEOR(params int[])
BITWISEXOR(params int[])
CHOOSE(int?, params string[])
CHOOSE(int?, params int?[])
CHOOSE(int?, params double?[])
CHOOSE(int?, params object[])
CONCAT(params string[])
CONCATENATE(params string[])
DATALENGTH(byte[])
DATEVALUE(string)
DAY(System.DateTime?)
FALSE()
FROMBASE64(string)
HOUR(System.DateTime?)
IF(bool, object, object)
IF(bool, string, string)
IF(bool, int, int)
INT(object)
ISINT(string)
ISNULL(object)
ISNULLOREMPTY(string)
ISNUMBER(string)
ISO8601DATE(System.DateTime?)
ISO8601DATETIME(System.DateTime?)
JOIN(string, params string[])
LEFT(string, int)
LEN(string)
LENGTH(string)
LOWER(string)
LTRIM(string)
MID(string, int, int)
MILLISECOND(System.DateTime?)
MINUTE(System.DateTime?)
MOD(int?, int)
MONTH(System.DateTime?)
NEWGUID()
NOT(bool)
NOW()
NULL()
NUMBER(object)
OR(params bool[])
PROPER(string)
REPLACE(string, string, string)
RIGHT(string, int)
RND()
RTRIM(string)
SEARCH(string, string)
SEARCH(string, string, int)
SECOND(System.DateTime?)
SPLIT(string, string)
STR(object)
TOBASE64(byte[])
TODAY()
TRIM(string)
TRUE()
UPPER(string)
UTCNOW()
WEEKDAY(System.DateTime?)
WEEKNUM(System.DateTime?)
YEAR(System.DateTime?)

As more and more value is being derived from enhancing Dynamics CRM with integrated data, there has been an issue with the rate at which you can create and then maintain records with in your Dynamics CRM online environment. It is considered slow, expensive and difficult to achieve, this is more down to the implementations of the third party vendors  rather than some failing of the infrastructure or Dynamics API design.

For this demonstration we are going to use a default Dynamics Online instance with no optimisation, I did not need:

1. Microsoft Server Products
2. Microsoft SQL Server Products including DTS or SSIS
3. Any type of message queuing
4. Any type of xml experience.

I am going to import 5000 simple contact records into the contact entity in a Dynamics CRM Online instance but this solution scales directly to 100,000 records or more (a later blog will introduce the how to run DS3 in parallel for high volume inserts). I did require:

1. The 5000 contact file can be found here (http://www.briandunning.com/sample-data/).
2. Data Synchronisation Studio v3 found here (http://www.simego.com/Products/Data-Synchronisation-Studio 8mb download runs on the desktop or server)

I connected the Source to the CSV file and selected the 5000 record contact file.

I then connected the destination to our dynamics CRM Online instance.

I mapped contact and address details to make sure the sync reflected a realistic insert.

The compare only takes about 2 seconds and DS3 predicts 5000 inserts and previews the records:

Clicking the synchronise button synchronises the 5000 records from the Source to the Destination in 4:06 seconds (20 records per sec)

This in itself is faster than most competing solutions but what is more important is the ability to alter the source system data and re-synchronise. This is where much migration pain is felt. For example we could decide that the web address is completely incorrect we are better to calculate it by using the last portion of the email address: ie. david.evans@simego.com –> http://www.simego.com.

We can achieve this by using a dynamic column which is just a calculated field:

I can then just map this Dynamic Field onto the websiturl in the contact entity.

Comparing the 5000 locally and the 5000 on Dynamics CRM Online takes a maximum of a couple of seconds. DS3 then shows you what it will be updated within Dynamics online. It will ONLY update columns that have been changed, meaning it will update other fields with may trigger workflows or plug-ins inadvertently.

We can now synchronise the updated column in less than 2 minutes (1:59) which is 42 rows per second. I could have stopped and restarted the sync at any time and it would have just set off where it stopped.

Whilst it is possible for other products to use parallel servers with message queues, it soon becomes very expensive and time consuming.My next post will demonstrate using DS3 in parallel which is only limited by the bandwidth and speed that Dynamics Online can process the incoming requests.

If you would like a quick demonstration of DS3's Dynamics CRM integration (or any other Sharepoint, Active Directory integration) please feel free to drop an email to support@simego.com. We really are always happy to show of our solutions and share some of our clients successes.

Here’s an example where were using Podio to allow users to enter details on forms upload pictures and then use Data Sync to integrate this data with SQL Server.

Data Sync will Synchronise the Form Data with a SQL Database and download the attached files into a Folder on the file system.

Here’s a typical form in Podio and we want to export this Meta Data to SQL and download the picture files.

First we connect Data Sync to this App in Podio and include the columns were interested in the schema map.

Using the Tools Create SQL Table Menu we automatically create a SQL Table from this Schema to import the Data.

Now everything is Mapped as we Expect.

The Next Step is to use the Project automation feature of Data Sync to add your own code to download the files from Podio and write them to the Local Disk.

The Podio Provider exposes a special “Podio” object that allows us to interact directly with the Podio API, we added a special GetFile Method to this object to make it easy to download files from Podio.

At the End of the Synchronisation process we iterate through the New items collection and get each file and write them to the disk with the code below.

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;
using Simego.DataSync;
#endregion

using System.IO;

/// <summary>
/// </summary>
class ProjectAutomationOverride : Simego.DataSync.Automation.ProjectAutomationShim //Do Not Change This Line
{    
    
    public override void Start()
    {
        
    }

    public override void BeforeCompare(ProjectAutomationLoadResult loadResult)
    {

    }

    public override void BeforeSynchronisation(ProjectAutomationCompareResult compareResult)
    {
            
    }

    public override void End(ProjectAutomationResult result)
    {
        if ( result.HasChanges ) 
        {
            foreach(var item in result.CompareResult.New) 
            {
                string path = Path.Combine(
                    @"C:\Temp\PodioExport", 
                    item.SourceRow[5].BeforeColumnValue.ToString());
                
                File.WriteAllBytes(path, 
                
                    DataSourceA.Podio.GetFile(item.SourceRow[6].BeforeColumnValue.ToString()));    
            }
        }
    }

    public override void Error(ProjectAutomationException e)
    {
        
    }

}

[Note: The Index values in the SourceRow collection above relate to the row position in the Schema Map] 

Now after the Synchronisation the SQL Table is populated from Podio and the Local Disk contains copies of the Files that were attached to the Form Files.

This is just a simple example of how Data Sync can integrate with Podio to provide business value quickly and easily.

We currently have two providers for Podio one for Space Contact Items and one for Space Apps.

To connect to Podio you will need to locate the Podio providers in the list of Providers and the easiest way is to use the search function.

[Note: Leaving the OAuth settings blank will allow Data Sync to use our default application settings with Podio, you can override these with your own Podio API keys if you like.]

Click on the Credentials property and this will then bring up a web page where you need to authenticate with Podio and grant Data Sync permission to connect to your account.

Once connected you can then pick the App in Podio to connect to from the App Drop-down list.

The Silent property prevents updates to the app INSERT/UPDATE from being reported to your Activity Stream.

Clicking OK will then popup the Connection Library option which is a new feature that allows you to save credentials for use again later. The OAuth credentials will expire after 28 days so it’s a good idea to save these in the connection library so you can easily update them again later.

That’s it now your connected to Podio and you can start designing your integration or migration.

Here at Simego we love Podio so it was natural that we developed a connector for Podio that would allow us to integrate Data from our LOB systems into Podio.

Out of the Box Podio lets you import Excel sheets and a few other options. But we need something that we can run on a Schedule which can take information captured through our Website and ensure it ends up in Podio.

We’ve made this all a bit too easy, simply connect Data Sync to your Source data and use the new Podio data provider. If your working with Contacts then you should use the Contacts provider. Set the Credentials which goes through the OAuth song and dance and then choose the Space in Podio to connect.

Then Map your data just as you would any other system, you need a Unique Key to relate records and Podio exposes a nice external_id column you can use to store the ID from your source system.

Then run through the normal Compare and Sync process and your new data will then be synchronised with data in Podio.

We have recently been working with some of our partners to allow them to integrate their client’s ‘Documentum’ environments using Data Sync Studio. Currently, this is still in development but early signs are that we will offer the integration as a product.

We have developed the product to be purely api based and it needs no connections to the underlying Database or content servers.

I am going to run through moving files between the desktop and a Documentum environment. We could just as easily move files from a Sharepoint Library to Documentum as well.

As usual we use the following simple integration steps in DS3:

1. Connect to a source which in this case is a folder on my desktop
2. Connect to the destination which is a Documentum folder within a Repository
3. Map any fields we require. This mapping is a feature built into DS3 and allows us to lookup information to update Documentum metadata about the document entity.
4. Press the ‘Synchronise’ button and the documents are sent up to Documentum with the meta data correctly set.

For this demonstration we are going to use some simple text files as examples but we can target more complex types in Documentum.

Step 1: Connecting to the file system source:

Select the folder containing the files you would like in Documentum.

Step 2 : Connecting to a Documentum Repository and Selecting in Folder

We have several options:

Credentials: Username, password and optionally Domain

DocumentType: The connector displays a dropdown listing all types within the repository.

DocumentumServerURL: The http address of the Documentum DFC url.

Timeout: A timeout in of any request.

ContentUpdateBehaviour: Under some circumstances we are not interested in integrating the content of a document just the attributes (contentless documents). We can choose to synchronise the content or not.

Folder: The connector allows you to browse the folders within the Repository to find a folder to sync.

RepositoryName: The Repository Name.

Step 3: Map the columns between the 2 Data Sources

There are several things to note about the connector:

1. It will automatically bring back the attributes for the selected ‘type’ In this case we are just using the vanilla ‘dm_document’ type in Documentum.
2. It will use the ‘dm_formats’ table to map files to their correct ‘Format’ in Documentum.
3. It supports repeating attributes with a string[], int[] attribute type.
4. Dynamic Columns are fully supported if we need to lookup values to write to attributes in the target types.

I added a new Dynamic Field called ‘Subject’ to map to the dm_document attribute Subject:

Step 3: Run the Synchronisation:

Comparing A->B (File Systems –> Documentum) and then synchronising moves the documents and updates the metadata accordingly:

When I delete some files from the source file system and change the Subject in the Dynamic Column to be ‘Food and Beverage’ we can update the new files.

Comparing Comparing A->B (File Systems –> Documentum) again DS3 shows us the updates it will make to Documentum:

You might notice the performance times, there is no caching and the Documentum Server (Data Source B) is 3000 miles away over the internet.

We can then see the update to the Subject in the Document properties:

As we progress through the development of this connector I will post some more blogs.

If you would like more information about this Documentum connector please just drop an email to sales@simego.com and we can organise a chat over tea.

System Requirements

• Microsoft Windows Server 2008/R2/2012
• IIS 7.0 or Higher
• Windows Authentication for IIS
• .NET Framework 4.0 Full Profile
• SQL Server 2008 or Higher configured for SQL Mixed Authentication Mode.

Step 1 – Data Synchronisation Studio

Install Data Synchronisation Studio as normal selecting either 64 bit or 32 bit as required for your system. Then activate Data Sync with your Server Edition License Key.

Step 2 – Data Sync Team Server

Install Data Sync Team Server as normal by running the install program this will install the IIS WebSite for Ouvvi and install the Ouvvi Processing Service.

Step 3 – DB Setup

The next step is to create an Ouvvi Database that this instance of Ouvvi will connect to. When you start the Ouvvi Website if there’s no configuration file it will start the setup process.  Here you need to enter your Network Credentials these are your Windows Username & Password that has Admin rights for your SQL Server. The SQL Server Name and Instance Name such as LOCALHOST\SQLEXPRESS the name of the Database you wish to create and a SQL User account name and password (Setup will create this user).

Step 4 – License

Once the DB is configured which takes a few seconds you will be redirected to the home page with further setup instructions. The first thing is to enter your Server Edition license key.

Selecting “Projects” from the Menu will re-direct you to the License Page. Enter your Key and Click Activate.

Step 5 – Windows Service

Next you need to open the Windows Service Control Manager (services.msc) to configure and Start the Ouvvi Processing Service.

Locate the “Ouvvi Processing Service” set the user to a domain service account you want to use to run the Data Sync integration Tasks and press Start.

Now if you switch to the Services –> System Services Tab you will see all the background Tasks have started and are running.

NOTE: If you need to re-start the setup process you should delete the Ouvvi.config file from the Directory C:\ProgramData\Simego\Ouvvi and restart the OuvviWeb AppPool in IIS.

Yesterday we blogged about the CRM Trigger that enables real-time synchronisation with Ouvvi / Data Sync Team Server. Today we’re going to cover off the other side where you can trigger a project to start from a change in your SQL Database (SQL Server, Oracle, DB2, MySQL, ODBC, OleDB etc).

This works in a similar way to the CRM Trigger except this time we query your SQL Database every 30 seconds and ask for the latest DateTime value of any changes.

To do this you need to write a query that would return the latest change timestamp from your database for the items your monitoring.

For example

SELECT MAX(modifiedon) FROM dbo.AccountBase

To set this up you Add a new Trigger to your Data Sync Team Server environment.

Choose the SQL Trigger Option

Then configure the Trigger, choosing the Network Library and connection string to your database and the Query to execute. (Yes you can use other database servers i.e. Oracle, IBM DB2, etc).

That’s it Ouvvi will start to Query your SQL DB every 30 seconds and when there’s a change projects linked to this trigger will be queued to start.

Now that the project is using a Trigger you can see under Reporting how the project is started through distribution of actual changes rather than a schedule.

If your Table does not have a valid DateTime column that gets updated when the record is changed then you can implement a solution based on SQL Triggers to maintain an updated timestamp value like this.

Create a new SQL Table like this in your Database

   1: CREATE TABLE [dbo].[OuvviTriggers] (

   2:     [ID] int IDENTITY(1, 1) NOT NULL,

   3:     [Name] nvarchar(255) NOT NULL,

   4:     [Updated] datetime DEFAULT(getdate()) NOT NULL

   5: )

   6: GO

Then you can create a SQL Trigger on any Table that you want to monitor like this where you update the ‘TableA’ to match the name of the Table your monitoring.

   1: CREATE TRIGGER TableA_OuvviTrigger

   2: ON [TableA]

   3: FOR INSERT, UPDATE, DELETE

   4: AS

   5:     UPDATE [dbo].[OuvviTriggers]

   6:         SET [Updated] = GETDATE()

   7:     WHERE [Name] = 'TableA'

   8: GO

Then your Ouvvi Trigger just becomes SELECT [Updated] FROM [OuvviTriggers] WHERE [Name] = ‘TableA’