locked
CRM Database to Data warehouse (Extraction) RRS feed

  • Question

  • Hello everyone.

    I'm starting a data warehouse project where I have as input the CRM database. I want to know which is  Microsoft supported form to extract data at ETL procedures.

    I found that there are base tables and extension base for CRM 2011 and base for CRM2015; for both 2011 and 2015 CRM there are views and filtered views.

    Because filtered tables contain all data names, corrections UTC and integrate the CRM security, the extraction is slow when the tables are very large and worsens when is required to do joins with other filtered views.

    I appreciate your suggestions.

    Thursday, September 17, 2015 7:05 PM

All replies

  • Hi,

    did you try running against normal view rather then filteredview?

    so if you are currently queries against view called "FilteredAcount" then change query to run against view called "Account"

    when query against FilteredAcount system checks for permission and runs security checks hence it might be slow, try with normal view and see if this helps..


    MayankP
    My Blog
    Follow Me on Twitter

    Friday, September 18, 2015 2:49 PM
    Answerer
  • Thanks for your answer MayankP.

    I tried normal views, but I face a migration of crm 2011 to 2015 and later to 2016. I understood that use the normal views is not supported and these can change in future versions.

    How can I optimize the queries in supported form ?

    Friday, September 18, 2015 3:38 PM
  • Hello,

    Yes, you are correct normal views are not supported way to do this.

    But this views are present in all CRM versions (CRM 3, CRM 4, CRM 2011 and CRM 2013 and CRM 2015) till date so it is safe to use in my personal opinion BUT you are correct there always possibility they will not be available in next version.

    other option you have is improve security model such way that these queries with FilteredViews runs faster and also work with DBA to ensure relevant index are present on relevant table and that will also improve your performance of your queries.


    MayankP
    My Blog
    Follow Me on Twitter

    Monday, September 21, 2015 9:27 AM
    Answerer
  • Hi,

    Base and extension tables were merged into a single table from 2013 onwards. You can work directly with the tables as long as you control your own filtering and are not writing to them.

    Regards

    Chris

    Monday, September 21, 2015 3:04 PM
  • Hello,

    Yes, you are correct normal views are not supported way to do this.

    But this views are present in all CRM versions (CRM 3, CRM 4, CRM 2011 and CRM 2013 and CRM 2015) till date so it is safe to use in my personal opinion BUT you are correct there always possibility they will not be available in next version.

    other option you have is improve security model such way that these queries with FilteredViews runs faster and also work with DBA to ensure relevant index are present on relevant table and that will also improve your performance of your queries.


    MayankP
    My Blog
    Follow Me on Twitter

    I know that nothing but filtered views are supported for reports, but are we sure there are any read restrictions on the database?  I thought all reading was supported, certainly where users/security is not a consideration.
    Monday, September 21, 2015 3:55 PM