CRM Online 2011 & Scribe Online Replication Services – BackAnyware

Dynamics CRM 2011 Online or Dynamics CRM 2011 Partner Hosted offers the same features and functionality as the on-premise Dynamics CRM 2011 with a few exceptions. Although CRM Online includes the same powerful search, query and reporting features as the on-premise Dynamics CRM 2011 , it limits users from taking full advantage of Microsoft’s powerful Business Intelligence tools such as SQL Server Reporting Services and SQL Server Analysis Services that are required for advanced business intelligence and analytics many businesses need – especially to leverage the valuable data captured by the CRM applications.
But you didn´t have the Data and the Backups in your hand! Thats an issue which needs to be solved in order to keep the operational security.
Lets have an look on Scibe Online RS and how it can help to solve this issue for Dynamics CRM 2011 Online or Dynamics CRM 2011 Partner Hosted.
Scribe Online Replication Services (RS) is a new cloud-based integration platform with which you can create and maintain a copy of your data in Dynamics CRM Online 2011 enabling you to:

• Access and analyze your cloud data with advanced reporting tools including SQL Server Reporting Services (SSRS) and Microsoft Excel
• Generate data cubes using business intelligence tools such as SQL Server Analysis Services (SSAS)
• Merge your CRM data with other information across the enterprise to ensure that you are seeing the entire picture
• Deliver critical business intelligence to all key constituents across your business without requiring access to Dynamics CRM Online 2011
• Automatically create an archive of your customer data to meet compliance and governance

Scribe-Online-RS_dynamics_crm_2011_muenchen

http://www.dynamicsconsulting.de/2012/03/04/crm-online-2011-scribe-online-replication-services-backanyware/

Read more »

Posted in: CRM 2011 and Scribe

Handling Rejected Record Rows from Scribe

Handling Rejected Record Rows from Scribe

So you have a recently installed Scribe Version 7.0.1.15701 on a server. You’ve built some DTS jobs and everything looks to be importing and translating smoothly. THEN – one of your jobs throws up a report like this:

12,555 successful records loaded! 6 record rows failed. “Hmm? I wonder why those records failed?”, you ask yourself. - and so starts your exploration.
Enabling Rejected Rows in Scribe:
Before you can review the rejected rows, you must first tell Scribe that you want it to put the rejected records into a table. This is done by going to Run –> Edit Settings –> Rejected Source Rows (tab) –> Output Rejected Source Rows (checkbox). You can then tell Scribe where you want this “Reject Rows” table. Additionally, you can specify whether Scribe should create a new table for each job that generates “Reject Rows” – or if you want a new table generated. Generally speaking, you don't want a bunch of extra rejected rows tables for the same job, so I would recommend selecting the option to re-use the same table. I’ve defined it this way in the following screen shot from Scribe:

Querying Rejected Rows:
There can be any one of a 1,000 reasons why a particular row in a table fails to import. In my particular situation, the pick-list value that I had on my SELECTCASE statement turned out to be incorrectly numbered, which was throwing out the select 6 rows. But before I made that conclusion definitively, I wanted to see the data. For users of MS SQL Server 2005 or 2008, reviewing these rows is a fairly straight-forward process. Executing a query against a table in MS SQL Server is done through the Query Editor Window. One option is to Right-Click on the table and select Script Table As –>SELECT To –> New Query Editor Window, as depicted in the following:

Once you have the query set up appropriately in the Query Editor Window, simply select “Execute”. The rows of data can then be seen in the results tab below the Query Editor Window.
No Microsoft SQL Server Tools?:
My particular server where Scribe was located did not have MSSQL Server 2005 or 2008 tools installed – so I couldn’t run a simple SQL query against the REJECT_CONTACTMIGRATION table. Frustration set in. I didn’t have a free copy of Microsoft SQL Server 2005/2008 lying around. What can I do? Luckily, there was a solution.
As it turns out, Microsoft has a free product for just such a situation: Microsoft SQL Server Management Studio Express (MSSMSE). From the website (link): (Note, this link is for MSSMSE 2005. The 2008 version can be found here: link)
“Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.”
Download the file appropriate for your install and MSSMSE will connect to the Scribe Internal DB automatically. You should see the following once you have installed the application and launch it:

In this case, it is connecting directly to the Scribe Internal database. Once connected, you can browse directly to the table you want to review:

Once you find the table you want to query, simply right-click on the table and select “New Query Editor Window”. This will populate the SQL Query Editor window with the appropriate SQL SELECT statement for the table you want. Execute the query and then you can view the rows in the table.
Once I identified the problem, I simply modified the DTS to include a filter for records with my reject record condition; re-ran the DTS; and my 6 lost records were loaded successfully! Victory!
Other Scribe topics can be viewed on Customer Effective’s Scribe Blog, located here.
Lastly, another topic on our forums enables you to create a workflow to surface Scribe errors and monitor them directly into CRM, which can be very helpful. This is located here.
Good luck with your CRM projects!

Read more »

Posted in: CRM 2011 and Scribe

Scibe -How To Migrate Millions of Records to CRM in Hours (Rather Than Days) Using Scribe

How To Migrate Millions of Records to CRM in Hours (Rather Than Days) Using Scribe

One of the criticisms that Scribe Insight sometimes gets is that it’s slow. While it is true that Scribe is not the speediest ETL tool out there, much of the blame for sluggishness can be attributed to DTS design.
One rule of thumb is that every trip to the source or target slows down the process. That means that dblookups and seek steps, while useful tools, cause the process to go slower. Also, updates and update/inserts hit the target 2-3 times, so updates are slower than inserts.
Here’s an example of what I’m talking about: I integrated a prospect database with Microsoft Dynamics CRM. This is going to be a couple of hundred records inserted or updated per day, but to get started, we first had to load the existing prospect data, roughly 2.5 million records.
I wrote the DTS for the on-going integration, in which I used an update/insert step, as they will be getting updated information for existing prospects, and we want the process to update if it exists, and insert if it doesn’t. This DTS also contained a seek step and two dblookups to populate lookup fields in CRM from other CRM entities.
When I tested the DTS, it imported 10 records every 3 seconds. While this performance would be satisfactory for the on-going integration volume, it would be too slow for the initial migration of legacy data. At that rate, it would take 8 days to load the legacy data.
Here’s what I did to cut the migration of the legacy data down to a reasonable amount of time:
1. I copied the legacy data to a table in the ScribeInternal database, and made ScribeInternal the source for my migration DTS. This made it a shorter trip to get the data.
2. I moved the lookups and seeks to the source query. Instead of doing a lookup to the account table in the DTS, I joined the filteredaccount view from CRM in to my source query, and removed the dblookup and seek steps from my DTS. Now instead of having to seek for the id of a related record, I have the GUID in my source data, and write that directly to the accountid field.
3. Given that the legacy data contains unique records that do not yet exist in CRM, I made my migration DTS an insert step.
The result is that the DTS now only hits the source and the target one time. The difference in performance is pretty astounding. The migration DTS now moves 50 records per second, taking what would have otherwise taken days to hours.
The final thing you can do is to thread your job. As you may know, jobs run through XML message queues are threaded, meaning multiple operations happen simultaneously, but jobs run through the workbench are not threaded. If your on-going integration is going to be a high data volume, using message queues is a good way to go.
However, setting up xml based DTS for a one time migration can add a level of complexity. Another option to thread your jobs is to break your migration DTS into multiple DTS’s and run multiple instances of the workbench simultaneously. In this case, I made a copy of my migration DTS. In the first DTS I filtered the source query to just records created prior to 1/1/2008. In the second DTS, I filtered the source query to just records created on or after 1/1/2008. I then ran them at the same time.
You are still constrained by the resource limitations on your server and the CRM web services, but by following these steps you can optimize your migration processes to run in a reasonable amount of time, without having to resort to unsupported methods, like direct table updates.

Read more »

Posted in: CRM 2011 and Scribe