Scribe Software Blog

Scribe Workbench Improves Insert Operations over 40%

Scribe Insight’s newest release has added a Bulk Operation mode to improve the performance at which we send data into Dynamics CRM and Salesforce.Com. An overview of the upgrade is here. For data stores that support bulk operations (such as Dynamics CRM and Salesforce), you can now configure DTS files to specify bulk processing with basic insert, update, and delete. Now you can now set bulk operations for individual steps within a DTS file. Prior to Insight Version 7.6, the Bulk Insert feature was set for the entire DTS file, rather than for each step.

In December when this was released, I was so excited to see our focus was on speed.  So over the holidays I upgraded some of my virtual machine images to see what kind of performance improvements one can make by upgrading Scribe along with several other sub systems.  I just want to take it out and let her “stretch out her legs”, lets see how fast we could go.  So below is the first step of the performance improvements.

• First I set up a SQL database with 50,000 contacts and built a simple DTS to insert these contacts into Microsoft Dynamics CRM 2011 as Leads. By the way if you are looking for some test data I found a great resource for generic contact lists.  http://www.briandunning.com/sample-data/ these are random names mixed with random addresses with properly formatted e-mail and web addresses.  He has built many file sizes from 500 to 5 million contact names, perfect for our purposes.  I created a table in SQL and copied the CSV file into the SQL table.
• Next I built a DTS to insert these into Dynamics CRM as Leads.   No formatting of data, no lookups, just moving the data in from the table as fast as possible. I felt that this would help me see the raw changes. Using our previous version I was able to insert 50,000 records in 26.93 minutes, that is 31 rows per second with this DTS, on this image. 

Before we go any further I need to mention that integration performance is determined by many factors.  The use of Lookups and external variables, target application speed, use of database indexes, query syntax, Step Control pre operation conditions, step order of operations, available RAM on the Server, and wind speed all impact the performance of a DTS.   Well maybe not wind speed, but suffice to say there are a lot of factors impacting the performance inserting thousands and millions of records.  So these results are specific to my environment.   So in this instance the only variable was the Scribe upgrade, your “actual mileage will vary”.        

• After upgrading to Scribe Insight 7.6 I opened up the DTS, I got the following indication that the DTS is upgraded.  I click OK and move to the next step of configuration. 

 

 

• Opening up upgraded DTS, I went to the Configure Steps area, selected the insert step and set insert to Bulk Mode, 1000 rows. You can max this out at 1000 rows if you think your data will process error free. If it encounters an error it will revert to non-bulk mode and will step through each row individually until it gets to the end of the batch size.  So as this was purely inserting with no parent or child objects it is good to set it to the maximum value. 

 

• I ran the DTS from the Workbench.  Happily the rows click past.  16 minutes later all 50,000 records have been inserted into CRM.

 

 

When we look at the performance I see that we are slightly over 40 % faster than the previous version at 52 rows per second.

My performance results are as follow:

DTS Version	Source	Rows	Total Time – Insert
7.5.2	SQL 2008 R2	50,000	26.935
7.6	SQL 2008 R2	50,000	16.007
Improvements			41%

Improving the core functional speed is a great way to start 2013.  When I look at the way the DTS needs to work to gain these performance improvements,  some rethinking of the integration patterns and methods may be needed. When bulk operations are called for it will be best used when your steps are of a simple design.  These would be when building migrations where all the data is inserted. When doing SQL based integrations having separate DTS's for Inserting and Update operations may be called for.  Also where extensive parent child operations are being used, like a sales orders with header and details in 1 DTS, this bulk setting will do little to improve performance.  Further, if the source is a single XML file where only one record is being integrated, then bulk will not improve the performance in any noticeable way. 

As these are only preliminary results running on a virtual machine set up on my laptop, not an ideal environment, I am excited to see the impact of some of the other areas where I can improve my integration performance.  I will be tweaking RAM settings, upgrading to SQL 2012, changing Queue Priority settings, and performing several other tweaks to the system, all of which I will share on the Blog.  Next, I am going to upgrade the underlying database to SQL 2012 to see how that impacts performance.  

Scribe MVP Blog: Increase Dynamics CRM Online Scribe Data Migration Performance

Scribe MVP (Most Valuabe Professional) Jayson Goldinger, of Slalom Consulting, posted this useful blog today on Dynamics CRM Online integration with Scribe and we thought our readers would find it useful. Thanks Jayson!

"For those of you who have done data migration or integration with Dynamics CRM Online, you know that the performance may be a challenge. I recently faced that challenge during a recent project.
 
I had approximately 52,000 account records with associated contacts to import. I also needed to do a check (Seek Step) against CRM to see if the record was already in CRM. I was using Scribe Insight with my source being Excel and my target was Dynamics CRM Online using the Dynamics CRM Adapter. On my first attempt my job ran for 9 hours and only imported approx. 15,000 records. At that rate it would have taken over 30 hours to complete....."

Read Jayson's entire blog here: http://blog.slalom.com/2012/08/20/increase-dynamics-crm-online-scribe-data-migration-performance/ 

 

Happy New Year 2012!

As we close out 2011, we want to thank our partners and our customers for doing business with Scribe. We've had a big year with the launch of new products, Replication and Synchronization Services, on our Scribe Online cloud integration platform. We're equally excited about our upcoming release of Insight 7.5 this week. This version will allow our partners and customers to integrate wherever they are world-wide, and we're looking forward to working with new partners and customers in Asia and Central and Eastern Europe.

However, expanding around the globe is a bit more than software enhancements! We're making investments throughout Scribe to support your integration needs such as:

• The EMEA office will be expanding to a team of 5 and adding a dedicated pre-sales engineer. This includes relocating Linda Johnson, who many of you have worked with for the past five years. So, you will be hearing from Linda as well as seeing her more!
• Starting in January, Enterprise customers and Elite partners will have access to 24x7 Support for critical support issues.
• With the addition of Roger Hodskins, Vice President of Strategic Alliances, and more of my focus, we continue to deepen relationships with the global system integrators with whom we partner, such as Ciber, Columbus IT, Hitachi Consulting and Logica.
• You'll be hearing more about how multi-national Scribe customers and partners solve interesting challenges and reap the benefits of providing superior service to their customers, decrease costs, and increase operational efficiencies.

We wish you a wonderful holiday season and a sincere "Thank You!" for your support and business throughout 2011. Stay tuned for more in 2012!

Scribe’s Agent Technology for Scribe Online

In building the Scribe Online solution, a key goal was to create a system that could operate seamlessly in the hybrid world, reflecting our belief that increasingly customers and partners will face the challenge of integrating applications that sit within a customer's firewall with cloud-delivered applications combined with other web service-based sources for data improvement and data enrichment. Scribe's agent-based technology is a key facet of delivering solutions in the hybrid world. In this blog post I wanted to talk about the Scribe agent, explain several aspects of its architecture and discuss why we architected the solution the way we did.

The Scribe Agent is a lightweight component that runs locally within your IT environment. The primary responsibility of the agent is to establish secure communications between premise-based applications, cloud-based applications and with Scribe Online. The agent architecture provides a number of key advantages, including: 

• The agent enables secure communications without the need for you to poke holes in your firewall.
• The agent allows you to fully control of your data. Unlike hub and spoke architectures, integrating a premise-based application with a cloud-based application with Scribe Online does not require that data flow through Scribe Online. You are in complete control of which data leave your firewall and where it goes.
• The agent operates semi-autonomously. The agent periodically communicates to the Scribe Online cloud to receive new commands (start, pause) and to communicate work performed – and the agent continues to work even if that communication is temporarily disrupted. For example, when Scribe released the Scribe Online April update, we scheduled a 60 minute maintenance window. During that period, even though Scribe Online was down for maintenance, agents continued to run and continued to process data; customers' mission critical integration services were entirely unaffected.
• The agent is a headless program which runs as a service – it has no user interface. All configuration of and interaction with the agent is performed via the Scribe Online user interface – which means that you and your partners can monitor, manage and administer your integration services regardless of whether you are working on premise or remotely, without the need for VPN access to the agent machine.
• The agent is lightweight – easy to install and easy to configure with minimal technical prerequisites. A design goal of Scribe Online is that a new user should be able to sign up for Scribe Online, install an agent, and configure the Scribe Online RS service in less than ten minutes. The download is less than 15MB and typically runs in less than 100MB of RAM.
• The agent is high performance. When you create a solution instance via the UI, Scribe Online compiles that solution and downloads the compiled executable to the agent behind the scenes. When your solution is running, its running compiled code – which means it is fast.
• Agents are disposable. All settings and process definitions reside in the cloud and are synchronized down to the agent as needed. For example, if you lose your agent machine due to a hard disk crash, simply download and install the agent on a new machine. The first thing the agent does upon establishing connectivity to the cloud is bring down process definitions and instructions – and in less than 10 minutes your integration is up and running again!
• The agent is low maintenance – effective with the upcoming May update of Scribe Online, agents update themselves so that you automatically take advantage of new features and bug fixes.
• Agents are architected for easy active-active clustering. You can install multiple agents today – and in the near future, you will be able to exercise fine-grained control over which solution instances run on which agents, including solution instances which run on multiple agents simultaneously. Thus, with a fraction of the effort required for existing clustered solutions, you are able to install and configure multiple agents working collaboratively for easy load balancing, high throughput, and easy high reliability.

Introducing the 5 Capabilities of a Collaborative Integration Platform

In my previous blog post, I spoke of the changing role of the partner brought on by the advent of cloud computing. In this new world, partners are moving away from a reliance on 'systems integration' and moving towards creating value-added industry focused solutions. A critical component to these solutions is the need to help companies make sense of the integration requirements to other business applications and data sources. When developing an integration approach, it is important to recognize the different and evolving roles between the partner and customer throughout the phases of an application deployment. Even if there isn't a partner involved, in many cases IT organizations act in a similar capacity as partner to the business to deliver a successful CRM project. A collaborative approach to integration between "partner" and "customer" best serves the integration needs of companies deploying these business applications.

It is rare that one person has all of the skills and knowledge necessary to fully implement an integration solution. In a CRM integration project for example, the development of the integration requires a solid understanding of the design of the CRM system and the key customer processes that drive the integration need. It also requires detailed knowledge of the other applications, many of them complex back office applications. The ability to allow multiple people to participate in the development and design of the integration solution in a coordinated manner is essential. Each of the parties should be able to design their discrete piece and simply fold it into the broader solution. The other members of the project team should be able to easily understand the work others have done and should be able add their input and comments. Team members that have moved onto other projects should be able to easily move back into an active role.

So beyond these team development capabilities what does a collaborative approach to integration really mean? Over the new few weeks I will be exploring the 5 capabilities of a collaborative integration platform. This exploration should shed some light on the direction we are headed with our new integration platform, Scribe Online, as well as some of the continued investments we will be making in Scribe Insight. So watch for my next post as we drill down on the first capability; Reusability.

Salesforce.com to Microsoft Dynamics CRM Migration Template

We now have a template that will help you migrate data from Salesforce.com to Microsoft Dynamics CRM version 4 or version 2011. The template takes care of the basics of account, lead, and activity data.

Here is a list of the data the template migrates.

• Account records
• Contact records
• Activity records
• Un-converted Leads
• Attachments

When the template runs, it creates key cross reference relationships for the primary IDs for accounts, contacts, and leads. With the graphical environment in the Scribe Workbench it should be relatively easy to extend the template to handle other types of data related to these three objects, or to other objects.

You can learn more about the template and how to implement it by watching this tutorial video or by reading the template guide. The guide has more details about the template including the field mappings. You can download the template from the Samples section of our template download page.

Migration Performance

Usually, when we talk about performance optimization, it is in the context of ongoing data integrations. There are some shared concepts involved in optimizing one-time migrations, though there are a few things that are different about migrations. This post is dedicated to performance optimization in a one-time data migration scenario.

Migration performance is important since there is generally a requirement of a specific cut over date, and missing that date could have risks for a project. If an ongoing data integration simply takes a little longer than anticipated to process transactions it usually does not carry the same type of risk to your project. This blog post covers the major areas that can impact migration performance and gives you some tips and tricks to speed up a migration.

Hardware

The first place to start is with hardware. Scribe Insight can be installed almost anywhere; the requirements to get it running are minimal. However, you should still take care when planning where you install your Scribe Insight server and workstations. Keep in mind where Scribe will run the migration from. Ideally, it is best to have Scribe Insight on its own dedicated server with at least 2 GB of RAM and dual CPUs. This can scale upwards for larger migrations, but it's a good starting point. You can use a local instance of Scribe Insight on your laptop to design DTS files, but we do not recommend using your laptop to run your migration.

Another piece of information to think about with hardware is resource contention. Again, Scribe has a small enough footprint that it can be installed and running on the same machine as other apps, but there are risks associated with that type of setup. For example, let's imagine you installed Scribe on the same server as SQL Server, Dynamics CRM, and Exchange. When the migration kicks off, Scribe will start trying to use local resources to run the DTS file. Now, if Scribe is reading/writing to Dynamics CRM, the Microsoft API will start being utilized, consuming additional resources. Next, since Scribe is reading/writing to something that uses SQL Server, we will see the SQL Server server start to consume memory. You can see how this sets up a situation where multiple processes are trying to use the same server resources. If this server is underpowered, this situation can very quickly begin to negatively affect the performance of the migration, and the corresponding applications.

Minimize Target Operations

Now let's take a closer look at what Scribe is doing while it is processing data. The biggest goal for performance with Scribe is to minimize target database operations. When Scribe makes a call to do an action against a target (insert/update/delete/seek) it makes a request and waits for a response from that system to find out if it was a success or a failure. So by making sure you are not needlessly asking for actions to occur, you can help minimize this waiting period. This is the type of change that may not give you perceptible changes to the run time of a few records, but when you multiply small increases in performance across many rows of data, these small performance improvements can make a big improvement in the time it takes to run a migration.

With this concept in mind of reducing the amount of DB hits, we have some Scribe features built in to assist you. Some of these are fairly common, while others are a little less known, but can still provide a lot of help.

The first of these is the Cross Reference Keys feature. When importing data, you will generally need to do some foreign key association, which can cost you a lot of DB hits looking to relate on those foreign keys. Using Scribe's built in Cross Reference Keys feature allows you to store those keys locally in the SCRIBEINTERNAL database for fast lookups. Keeping lookups local is a great way to reduce the number of target operations, while not losing any functionality of the migration.

Another great feature is the use of Calculated Variables. These give you a way to generate a value once, and use it over again in the same DTS file. A great example of this would be if you use the same DBLOOKUP to pull back an AccountID multiple times in one DTS file. Every time that DBLOOKUP happens, it's an additional database operation. Instead of wasting several database operations, you can do that lookup once, store the return value in a Calculated Variable, and then use that variable over and over again to get the ID value. It's a simple way to get the data you need, but not waste time with extra database hits.

There are some other features that can save you some overhead such as Automatic Foreign Key assignment and message processor caching. These are built in features of Scribe that require no additional setup by you in order to use, other than knowing they exist. By knowing what Scribe can do, you can better design your jobs to take advantage of these built in features.

Migration Design

When people set up migrations, at first the only approach that seems feasible is simply a point to point migration (all accounts à accounts, contacts à contacts, etc). But, there are actually several different approaches you can take using some of the features and functions of Scribe to your advantage. These design tactics should help you reduce the amount of time it takes to run your migration.

The first design tactic is to run the migration in stages using a net change pattern. Let's say, for example, you have a lot of lead time to design and test this migration, but the actual amount of time you have to cut over to production once you begin the migration is very short. You can run the migration and process the majority of the data several days before the cutover date. Then on the cutover date, you can run the migration again using the :LastRunDateTime system variables. That way, you have a much smaller subset of data to process to complete the migration on the cutover date.

Another method is to make use of the multi-threading capability in Scribe. The ideal way of doing this is by using queue based integration processes. Don't let the word integration fool you; you can use these features for your migration. Queue based integrations immediately give you up to 63 threads, depending on your license, to run data in parallel, boosting your throughput. When working with queue based integrations, the standard method of loading the queue with data is using some kind of publisher. But, when running migrations people tend to like having a little more control over what data is being run at what time, so instead of using a publisher to load your queue, you can make use of a DTS that has a target connection of the XML adapter using the Queue output option. This way, you can load the queue whenever you want, control how many records are loaded, have a running status about how many records are in there, and have the ability to easily change the source query in your DTS file to meet your needs.

If you have already designed a migration solution and converting to queue based integration seems a little much to do at this point, there is another way to get multi-threading out of Scribe. You can run multiple Workbenches at the same time. As long as your data can be segmented in a way that you can avoid running the same records twice, this can be an easy method to get jobs to run in parallel. The biggest concern for this is that you have sufficient hardware to support the multiple Workbenches running at the same time. If you do not, you run the risk of actually hurting performance by overworking the computer. The Workbench does not have any thread awareness, unlike the message processors, which know when there are not enough resources to start up another message processor.

If the computer where your Scribe server in installed cannot support this type of multi-threaded load, you could also install multiple Scribe workstations on other computers, giving each a portion of the migration to do. The most important thing to know is that you must segment your data, or run the risk of creating duplicates in your target system. You can build it in using features like Cross Reference Keys Locking to allow Scribe to know when it's processing a duplicate records in another process, and to create logic to avoid just inserting it again.

So, that's a glimpse into some of the tips, tricks, and design considerations that can help you design your migration for the fastest performance. There are many ways to optimize Scribe, so do not be shy of trying out some of these ideas. If you happen to find some particularly great design features or a few tricks you use, please share it with us.

If you would like to see videos where we explain the concepts presented in this blog post in more detail, check out the Scribe Inside Track service, which gives you access to a vBook called "Performance." A subscription to the Inside Track gets you access to premium content including deep dive educational videos, monthly interactive expert panel discussions, and a quarterly Inside Track newsletter. Learn more here - http://www.scribesoft.com/InsideTrack/.

Make your data work for you!

In my role here at Scribe Software, I have the opportunity to present the Scribe ‘Insight’ application solution to many companies ranging from the mom-n-pop shops up to global enterprise organizations.  And what I have found most intriguing across this business landscape is not only how similar their data integration requirements are, but also their need for application extensibility to support business process agility.  This need is a direct result of current business demands and competitiveness within their ever changing marketplaces.  It is my hope within this post to show you how the Scribe Insight ‘platform’ supports this broad business spectrum; enabling a true high, wide, and deep approach in helping companies deliver to their many business needs. 

I'm sure that you would agree in today’s competitive business environment “doing more with less” is considered the norm.  I am not singling out employee productivity here, but rather application productivity.   How can we squeeze more out of our existing applications and how can we better consolidate our newer applications so as to provide a 360° view of our data? 

This is the vision and direction of our prospects and customers.  Companies have recognized that a ‘blinder’ approach to data sharing (point-to-point solution) is just not cutting it.  As company’s disparate business systems become many, the need to integrate becomes more obvious and vital.  Their data is the lifeblood of the organization and the efficient and effective sharing of this data, whether internal and/or external, is one of many critical components that come together in driving businesses forward.

Whether the need is for replication, synchronization, or the process integration of data, Scribe Insight delivers to that and more.  A true ‘platform’ architected solution that removes those ‘blinders’ and is strategic in bridging the gap between cloud based and on-premise disparate islands of information; now and into the future.  In today’s competitive business climate, sharing data from one business system to another is absolutely mandatory across the enterprise; however making that data work for you outside of their respective applications (i.e. business monitors, sales data views) is where an extensible architected solution adds tremendous business value. 

For example, the ability to automatically monitor sales orders entered into your CRM system to provide real-time custom based notifications (i.e. emails, text messages) proactively informing the District Sales Manager of the current order volume vs. expected results?  

 Or graphically generating a 3D visual illustrating product cross-selling type, quantity, and customer? 

These are just a few samples of many possibilities that Scribe Insight can bring to the table allowing your otherwise static data to take on a new life, regardless of its location, allowing team analysis and validation of strategic business decisions and direction.  

Scribe Insight has been architected, since its beginning, as a ‘platform’ solution that is not only configurable, adaptable, and scalable, but also extensible.   The functionality to non-programmatically create either Scribe monitors and/or data views are inherent features within the product delivering to the needs of our prospects and customer today. 

“A clear sight of the end goal should not cloud one’s peripheral vision.”

Scribe 100% used in all our projects

Today I'd like to introduce the company I work for, Travi@ta, and explain our successful partnership with Scribe.

Travi@ta is located in Brussels (Diegem), Belgium and is focused only on Microsoft Dynamics Crm. With more than 50% of market share, we are by far the leader of the Belgian market. Founded in 2003, Travi@ta employs nearly 40 people, all of them being dedicated to and having strong experience with Microsoft Dynamics Crm.

So far we had a nice and steady growth, and in the process had the opportunity to successfully implement Crm in a lot of companies in a variety of markets. And Scribe has been involved in many of these projects. To this day, we have implemented more than 50 projects with Scribe, and we do around one new project per month.

This year, Travi@ta has been named finalist for the Microsoft Dynamics Financial Services Partner of the Year 2010 for our contribution in the insurance sector. In Financial Services, all the Belgian insurance companies are Travi@ta's customers... and they all use Scribe! There is no exception. This simple fact enlights the power of Scribe Insight and the trust that our customers have in this product. As said above, we have extensive experience with Microsoft Crm. And we have not found a tool that even comes close to the usability, simplicity and power of Scribe Insight. This is the reason why we suggest to all our customers to use Scribe.

We use Scribe mostly to setup batch interfaces between the back-end systems (master data) and MS CRM, where the data get enriched. These integration scenarios involve data synchronization, automated data feeds, etc. For such scenarios, the Total Cost of Ownership of Scribe is low given its rich inherent functionalities. The kind of entities that we sync to MS CRM are: brokers (accounts) along with their different sales reps by Insurance product, points of sales, end customers, policies, offers, territories, user hierarchy, data warehouse data (used for marketing segmentation), contact persons, software tool infos (broker tools), incentives (data such as travels, gifts, registrations), commissions, etc... Scribe Insight has proven to be the most appropriate tool to perform those data driven integrations easily and smoothly.

Thanks to our strong relationship with Scribe, we have reached the highest partner level at Scribe: Scribe Elite Partner. Ludo Frisch, Microsoft Dynamics CRM and Scribe Insight Certified Consultant at Travi@ta, has earned his Scribe MVP (Most Valuable Professional) title. There are only 33 Scribe MVP's worldwide. They represent the most experienced and skilled Scribe users from among their over 800 partners, 10.000 customers and related software vendors.

This year also, we are happy to announce that Trav@ta has been named for the 2010 President's club for Microsoft Dynamics for the third time! Our exclusive devotion to Microsoft Dynamics CRM, together with our strong business experience in CRM, and our ability to work and partner with the Best, like Scribe, allow us to continually grow and participate to the incredible success of Microsoft Dynamics Crm.

Scribe Project Snapshots for Dynamics NAV

As a way to further expand visibility into Scribe usage, we have a significant compilation of Project Snapshots on our web site. Snapshots are brief overviews of Scribe projects, describing the business needs, Scribe deployment, and the results realized by the customer.

Recently we've compiled a number of Snapshots from our Dynamics NAV customers in order to provide Dynamics NAV partners and their prospects with references and information on successful integrations with this popular Dynamics ERP product.

Here is a sample of the business processes that Dynamics NAV customers have automated and streamlined through integration:

• B2B & Partner Integration
• Billing and Invoices Integration
• Customer Master Integration
• Customer Support Integration
• Internal Systems Integration
• Lead Qualification Integration
• Ongoing Data Migration
• Pricing & Quotation Integration
• Product Master Integration
• Sales Order Integration

In these industries:

• High Tech
• Government
• Distribution
• Retail
• Manufacturing
• Professional Services

We have more snapshots in the queue to post for all of our products (so much to do, so little time!), so we wanted our readers to know this Snapshots section is updated almost daily. It's not the same old information here month after month, so if you haven't dropped by this section for a while, please do.

It's a terrific place to see if your particular business problem has already been solved using Scribe. Go ahead and check 'em out! Simply click on one of the headings; Endpoint, Business Problem or Industry, and you're off!