Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

SQL to FetchXML question

  • 1.  SQL to FetchXML question

    SILVER CONTRIBUTOR
    Posted 8 days ago

    We are starting to look into moving our on premise instance from 8.2 to on line.  One of the things that is concerning me is that I have many reports and queries that are SQL based.  My understanding these will all need to be converted to some other format such as REST calls or FetchXML.  From what I have read the current FetchXML has many new constructs but still not as expressive as SQL.

    Here is a simple example I have to check account ownership..
    select
    A.accountnumber Account
    ,A.Name AccountName
    ,U.employeeid EmpId
    ,A.owneridname CurrOwner
    ,U.fullname CorrectOwner
    ,U.isdisabledname CorrectOwnerStatus
    from FilteredAccount A
    inner join FilteredSystemUser U on U.employeeid = A.new_salesperson
    where A.statecode = 0
    and A.ownerid != U.systemuserid

    We have a custom field on the account used to set the owner from our ERP system.  Two issues I see with this example are:
    1. You can't join entities on arbitrary values.
    2. Filters need to use a constant for one of the values.

    In this scenario I would need to retrieve all the data and compare that data to validate the data.

    Am I missing something or does anyone have any suggestions on how to get around this type of issue?

    Thanks,

    Wayne.

    ------------------------------
    Wayne Wittman
    CRM Administrator
    MiTek Industries Inc.
    Clifton NJ
    ------------------------------


  • 2.  RE: SQL to FetchXML question

    TOP CONTRIBUTOR
    Posted 8 days ago
    Wayne,
        You are correct that FetchXML is not as expressive as SQL, and I have suffered through that transition myself. You can join entities on unrelated fields, but not expressions (as far as I know). I found that working with XRMToolbox + FetchXML Builder was the best way to develop my queries before embedding them into an SSRS report.

    The bigger issue for me was the size of the dataset was millions of rows, and SSRS using FetchXML can only aggregate 50k rows, and it can only retrieve 5k result rows. The solution to that problem was to use data replication (https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/replicate-data-microsoft-azure-sql-database) which will permit me to run SQL queries on a near-real-time version of my database.

    MS has come up with an option for customers with a Premium Power BI license to create "paginated reports" using Power BI Report Builder to create the RDL file, which I have not tried yet, so it is not clear to me whether it resolves the data size limitations of FetchXML+SSRS mentioned above.

    ------------------------------
    If this answered your question, please click on the arrow button next to Reply Inline and choose 'Make Best Answer.'
    Thanks.
    Nelson Johnson, Solution Architect
    BroadPoint, Inc., Bethesda MD
    Link with me! https://www.linkedin.com/in/nelsonjohnson/
    ------------------------------



  • 3.  RE: SQL to FetchXML question

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Thanks Nelson.

    I did spend some time looking at the FetchXML Builder in the CRMToolBox and find it pretty helpful.  I will need to spend more time with it though.  Going through the XSD file provided by Microsoft, as least to me, is kind of tough to follow.  I will do some testing with an XML editor supporting schema files.

    There are some aspects we will need to use the data replication but want to be careful what we use this for. We will want to minimize how much we pay for storage multiple times for the same data.  Some we may have to bring back in house from CRM to handle some of the business needs.

    The hope is that we can leverage Bower BI for some of the more complex reports.

    Thanks again.

    Wayne.

    ------------------------------
    Wayne Wittman
    CRM Administrator
    MiTek Industries Inc.
    Clifton NJ
    ------------------------------



  • 4.  RE: SQL to FetchXML question

    TOP CONTRIBUTOR
    Posted 7 days ago
    Here's another tool that could come in handy from the folks at KingswaySoft: http://www.sql2fetchxml.com/

    It wont get you around the limitations of FetchXML, but will help you convert any existing SQL queries to Fetch.

    As Nelson expressed, the best option is to deploy DES (Data Export Service) and have your CRM data synced up to an Azure SQL database. Then you can run SQL queries as much as you like.  Just make sure you size the Azure SQL appropriately, P1 or better.  If you don't, then any queries will be painfully slow.

    Another option is to do a timed sync (every 15 mins?) using SSIS or Scribe to a local on-premise SQL server.  It takes some work to develop and test the packages, make them self-correcting, etc... but you get a local copy to query with SQL.

    Good luck!

    ------------------------------
    Chris Harrington
    Solutions Architect
    PowerObjects, an HCL Company
    ------------------------------



  • 5.  RE: SQL to FetchXML question

    SILVER CONTRIBUTOR
    Posted 7 days ago
    Chris,

    I am a bit embarrassed that I was not aware of that site.  Thanks,

    Yes,  All of these options will have to be on the table here. I am starting to envy the people that started with on-line and didn't go down the path I did.

    Thanks again.

    Wayne.

    ------------------------------
    Wayne Wittman
    CRM Administrator
    MiTek Industries Inc.
    Clifton NJ
    ------------------------------



  • 6.  RE: SQL to FetchXML question

    TOP CONTRIBUTOR
    Posted 8 days ago

    I would suggest creating your own web api that would return the desired result set that cannot be handled by fetchXml.

    This is the same technique that I use to get a custom json that cannot be delivered by the native fetchXml in a single call.

    Cheers!



    ------------------------------
    Rex Kenley Tan, MCSA: Web Applications
    Tallmadge OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------



  • 7.  RE: SQL to FetchXML question

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Rex,

    Can you elaborate on this a bit?  How do you architect this and where do you host your APIs?   The back end of the APIs still need to be FetchXML or query expressions.

    Thanks for the suggestion.

    Wayne.

    ------------------------------
    Wayne Wittman
    CRM Administrator
    MiTek Industries Inc.
    Clifton NJ
    ------------------------------



  • 8.  RE: SQL to FetchXML question

    TOP CONTRIBUTOR
    Posted 6 days ago
    Wayne

    https://www.itaintboring.com/dynamics-crm/custom-actions/
    https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/dn481600(v=crm.8)
    https://www.inogic.com/blog/2016/10/execute-the-global-action-using-web-api-in-dynamics-crm/

    So you basically just call a custom action that takes in a parameter and it would return a result set. Cheers!

    ------------------------------
    Rex Kenley Tan, MCSA: Web Applications
    Tallmadge OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------



  • 9.  RE: SQL to FetchXML question

    SILVER CONTRIBUTOR
    Posted 6 days ago
    Rex,

    I haven't done anything with the custom actions yet but form some brief reading I like what I see.  This keeps everything in the CRM database and in solutions making deployment easy.

    Thanks for the suggestion.

    Wayne.

    ------------------------------
    Wayne Wittman
    CRM Administrator
    MiTek Industries Inc.
    Clifton NJ
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role