locked
ETL tool for onlice (advanced) reporting when fetchxml is too limited RRS feed

  • Question

  • We have build very advanced analytical reports for our Microsoft Dynamics CRM solution (OneFactor portfolio and fundmanagement solution). We have used SSRS and SQL queries, which work fine for on-premises versions.

    Now I wonder how should I resolve this for Online version - are there any ETL tools so that I could extract data from CRM Online into separate SQL Server database ? FetchXML is too limited (and slow) for our purposes and we have very large database w. hundreds of thousends of database rows.

    Cheers,

    PEKKA


    Pekka

    Tuesday, October 22, 2013 9:34 AM

Answers

  • Hi Pekka,

    You could download the entities to a local database using:

    SSIS - Will take some time to set up but is reliable if you have the SSIS skills. (KingswaySoft is good)

    Scribe - Could achieve the download successfully but performance is low and may put you back at square 1.

    or Simego's DS3 - Finance Industry use it because of performance, reliability and ease of use and it supports deletes.

    You can set the page Size to 5000 rows and should be getting about 5000 records/second download.

    You could also look to create an Azure Database close to the Dynamics Online Environment to increase speed?

    You could use some formula engines like North52 to roll up the data first reducing the need for aggregating within the report?

    ->Disclaimer I work for Simego!

    Please update this on how you get on.

    David

    David@simego.com


    • Marked as answer by _pekka_ Wednesday, October 23, 2013 10:54 AM
    Wednesday, October 23, 2013 10:35 AM

All replies

  • Hi Pekka,

    You could download the entities to a local database using:

    SSIS - Will take some time to set up but is reliable if you have the SSIS skills. (KingswaySoft is good)

    Scribe - Could achieve the download successfully but performance is low and may put you back at square 1.

    or Simego's DS3 - Finance Industry use it because of performance, reliability and ease of use and it supports deletes.

    You can set the page Size to 5000 rows and should be getting about 5000 records/second download.

    You could also look to create an Azure Database close to the Dynamics Online Environment to increase speed?

    You could use some formula engines like North52 to roll up the data first reducing the need for aggregating within the report?

    ->Disclaimer I work for Simego!

    Please update this on how you get on.

    David

    David@simego.com


    • Marked as answer by _pekka_ Wednesday, October 23, 2013 10:54 AM
    Wednesday, October 23, 2013 10:35 AM
  • For SSIS you may also check COZYROC SSIS+ library. It supports all Dynamics CRM deployment types and the licensing is very affordable and liberal.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Wednesday, June 25, 2014 2:25 AM