locked
Best practices for working around limitations created by advanced find (2011) RRS feed

  • Question

  • As a database guy I'm finding it difficult to believe that the recommendations being made by our CRM developers are the "right" way to go about solving certain problems in CRM and I am looking for some independent verification of what I'm being told.

    It seems like they are trying to work around limitations of advanced find, and the way they're doing it in general is by creating redundant elements in the data model.

    So, for example, let's say there's a business requirement to be able to search CRM accounts to show the latest date that the customer service rep account owner had an appointment with them, to make sure we are keeping in contact with our customers. Since there is apparently no way to perform this kind of "outer apply top 1" style query in advanced find, the CRM developers want to add a new data element to the account entity called something like "lastAppointmentDate". Then, whenever an appointment is created in CRM, it will run trigger code to check if the appointment is with the account owner and, if so, it will copy the date of the appointment into the account entity.

    From a data-modelling point of view, this is an extremely bad solution. It means the same information is being stored redundantly in the database, which always, always causes problems. It's a cardinal sin from a logical point of view.

    Speaking of points of "view", the correct solution in the model in most scenarios would be to create just that - a view that does the required query - and then provide users with the ability to query the view. However, this is apparently not possible with CRM.

    Is it really the case that the Microsoft recommendation to provide users with this kind of information is to have CRM developers create redundant storage throughout the CRM database? Because that seems like insanity.


    Monday, August 8, 2016 2:07 AM

Answers

  • CRM Advanced Find is a very useful mechanism to allow easy customisation of views, and to produce simple 'reporting' within the CRM application, but yes it has limitations.

    For the scenario you give, the 2 main options are to use Advanced Find, and accept that you may need redundant data, or to create Reporting Services reports to display the data. As you're using Crm 2011, I assume you have CRM OnPremise, in which case Reporting Services reports can use SQL to query CRM.

    If the requirement is solely to be able to display the last appointment date, then I'd normally go the Reporting Services route, for the same reasons you describe for not wanting to maintain duplicate data.

    However, there may be other factors involved. For example, if the developers wanted to create a workflow that depended on the last appointment date, then you may need to generate this as redundant data


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Proposed as answer by Guido PreiteMVP Monday, August 8, 2016 5:52 PM
    • Marked as answer by allmhuran Wednesday, August 10, 2016 4:19 AM
    Monday, August 8, 2016 1:58 PM
    Moderator

All replies

  • CRM Advanced Find is a very useful mechanism to allow easy customisation of views, and to produce simple 'reporting' within the CRM application, but yes it has limitations.

    For the scenario you give, the 2 main options are to use Advanced Find, and accept that you may need redundant data, or to create Reporting Services reports to display the data. As you're using Crm 2011, I assume you have CRM OnPremise, in which case Reporting Services reports can use SQL to query CRM.

    If the requirement is solely to be able to display the last appointment date, then I'd normally go the Reporting Services route, for the same reasons you describe for not wanting to maintain duplicate data.

    However, there may be other factors involved. For example, if the developers wanted to create a workflow that depended on the last appointment date, then you may need to generate this as redundant data


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Proposed as answer by Guido PreiteMVP Monday, August 8, 2016 5:52 PM
    • Marked as answer by allmhuran Wednesday, August 10, 2016 4:19 AM
    Monday, August 8, 2016 1:58 PM
    Moderator
  • David Jennaway's answer covered all, I just add some personal considerations.

    Your developers are not actually wrong, and showing that kind of data (a top row field from a related 1:N relationship) is a common requirement. You are working with CRM 2011, a bit consolation is that in the newer versions of CRM some new functionalities try to help (like rollup fields or quick view forms).

    I understand that you are a database guy and thinking in terms of database normalization, but in practice it's not always the best solution and some duplication of the data can be intentionally designed.

    CRM uses SQL Server as backend for storing the data, but from CRM perspective how the DB is modeled/structured doesn't matter, if adding a field is correct from a functionality point of view (for example required by a worflow as David example) the duplication inside DB should not matter (of course the implementation of this duplication should be done correctly, for example using a plugin and not by some js scripts)


    My blog: www.crmanswers.net - CRM Theme Generator

    Monday, August 8, 2016 5:52 PM
  • CRM Advanced Find is a very useful mechanism to allow easy customisation of views, and to produce simple 'reporting' within the CRM application, but yes it has limitations.

    For the scenario you give, the 2 main options are to use Advanced Find, and accept that you may need redundant data, or to create Reporting Services reports to display the data. As you're using Crm 2011, I assume you have CRM OnPremise, in which case Reporting Services reports can use SQL to query CRM.

    If the requirement is solely to be able to display the last appointment date, then I'd normally go the Reporting Services route, for the same reasons you describe for not wanting to maintain duplicate data.

    However, there may be other factors involved. For example, if the developers wanted to create a workflow that depended on the last appointment date, then you may need to generate this as redundant data


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thanks for your response. I find this to be a very disappointing. aspect of what is otherwise a pretty good application architecture. Perhaps we need to look at more recent versions of CRM.

    We already do use SSRS reports from within CRM, but this "copy data around" approach is being used for cases where users want to be able to perform searches on these various fields and then click through to the details of each returned entity in CRM - ie, the normal functionality of the CRM grid views. So having to write a separate report for each such query, and not using the normal grid functionality to show those results, would be giving up a lot of the reasons why we paid all that money for the "bells and whistles" cleverness of the CRM interface in the first place.
    • Edited by allmhuran Wednesday, August 10, 2016 4:20 AM
    Wednesday, August 10, 2016 4:12 AM
  • David Jennaway's answer covered all, I just add some personal considerations.

    Your developers are not actually wrong, and showing that kind of data (a top row field from a related 1:N relationship) is a common requirement. You are working with CRM 2011, a bit consolation is that in the newer versions of CRM some new functionalities try to help (like rollup fields or quick view forms).

    I understand that you are a database guy and thinking in terms of database normalization, but in practice it's not always the best solution and some duplication of the data can be intentionally designed.

    CRM uses SQL Server as backend for storing the data, but from CRM perspective how the DB is modeled/structured doesn't matter, if adding a field is correct from a functionality point of view (for example required by a worflow as David example) the duplication inside DB should not matter (of course the implementation of this duplication should be done correctly, for example using a plugin and not by some js scripts)


    My blog: www.crmanswers.net - CRM Theme Generator


    Thanks, I appreciate your reply.

    I have studied logical data models, set theory, predicate logic and relational theory for 20 years, and on the back of that expertise I feel compelled to assert that you are categorically incorrect about redundant data storage being "a correct" solution, or that it "does not matter". 

    There are, of course, ways to solve problems like this correctly. The most obvious solution under normal circumstances would be to create a view, function or stored procedure which returns the data in the shape required by the end user without having to store it like that in the database. More generally this is just an example of "computer science 101 - separation of interface from implementation".

    The problem with the CRM architecture is that there does not appear to be a way to create a view in the CRM database and then expose that to the advanced find interface. This is an architectural shortcoming on the part of the application. The result seems to be that creating redundant elements is the only way to provide this kind of interface functionality. But - and this should be repeatedly shouted from the rooftops - it means we are sacrificing the integrity of the data model in order to meet a GUI requirement. That's not good.

    Wednesday, August 10, 2016 4:18 AM

  • Thanks, I appreciate your reply.

    I have studied logical data models, set theory, predicate logic and relational theory for 20 years, and on the back of that expertise I feel compelled to assert that you are categorically incorrect about redundant data storage being "a correct" solution, or that it "does not matter". 

    There are, of course, ways to solve problems like this correctly. The most obvious solution under normal circumstances would be to create a view, function or stored procedure which returns the data in the shape required by the end user without having to store it like that in the database. More generally this is just an example of "computer science 101 - separation of interface from implementation".

    The problem with the CRM architecture is that there does not appear to be a way to create a view in the CRM database and then expose that to the advanced find interface. This is an architectural shortcoming on the part of the application. The result seems to be that creating redundant elements is the only way to provide this kind of interface functionality. But - and this should be repeatedly shouted from the rooftops - it means we are sacrificing the integrity of the data model in order to meet a GUI requirement. That's not good.

    I'm not questioning your expertise on data modeling here, but CRM is not a DBMS and you can't apply all the data modeling concepts of a DBMS when you model CRM data, and in this case Dynamics CRM.

    As I wrote before the fact that Dynamics CRM uses SQL Server is not relevant because it's must be considered as Read Only (meaning that you can't create stored procedures to alter the data and create a computed column for example).

    You are not sacrificing the integrity of the data model for a GUI requirement, you are doing it for a BUSINESS requirement, and these two things are totally different.

    Your company requires the last appointment date information inside the account entity in order to see the value from Advanced Find or inside a subgrid? Then with Dynamics CRM you must duplicate the data to accomplish this requirement. Is the perfect solution? of course it's not, but take it or leave it.

    In my opinion the problem is not duplicating the data, the problem is design/write the code in order to make this duplication robust, because if it's not robust that will ruin up your DB. You want to make it robust in the SQL side (like creating a function/stored procedure or altering the views to avoid the duplication in the first place), because it's not possible on SQL side when Dynamics CRM is involved, the alternative is on the application side (create a new field and write a plugin, just an example of "Dynamics CRM 101 - Customization & Configuration")


    My blog: www.crmanswers.net - CRM Theme Generator

    Wednesday, August 10, 2016 7:23 AM
  • I agree that CRM as a whole is not a DBMS. But, like just about every business application suite, it sits on top of some kind of logical data model, and such a data model is almost universally implemented in a relational database.

    So, my issue is not with having the CRM application do cool and useful things, that's great! My problem is with the idea that the data model is somehow subservient to the interface. Of course that's backwards - the data model is the thing that represents the reality of the world in which the business is operating. The GUI is just a way to interact with that model. To put it another way, one could create and populate a database that completely describes the CRM aspect of a business without ever creating a GUI. But one cannot create a GUI to do the same without ever creating a database. As such, the dependency clearly goes from the GUI to the model, not from the model to the GUI. Desired GUI functionality should not be reflected anywhere in the model. Doing this gets the dependency backwards. 

    This is the logical architecture of every application that works with persisted information. Dynamics CRM is not somehow "special and different" in this regard. And I'm not just seeing it this way because of some myopic adherence to relational theory based on my current position title as an information architect - this is all just basic computer science principles.

    I also can't agree with the idea that you're sacrificing the model for a business requirement (not a GUI requirement). The business requirement to be able to search based on some information, which happens to be a calculated data element. I already described how one can, in a correctly designed system, satisfy that kind of requirement without violating information principles. It's only the limitations of the CRM interface that force us to push the interface dependency down into the model. Then it's the CRM GUI that is forcing us to violate information principles, not the business requirement.
    Wednesday, August 10, 2016 7:44 AM