Products and Prices synchronization between Dynamics CRM/365 and ERP

When Dynamics 365 is used as a Sales force platform, there is often a need to identify the products that customers are buying in order to determine information such as sales price, support eligibility or other more business and industry specific information. Dynamics 365/CRM offers a set of entities and processes to manage the sales cycles. However, in most cases, the sales have to be recorded in a Finance system, often referred to as ERP (Enterprise Resource Planning) software. This article talks about what Dynamics 365/CRM has to offer on the sales process, product and price management side, what the limitations are then it comes to integrating with an an ERP and discusses some possible solutions.

Managing Sales Process and Pricing in Dynamics 365/CRM

Dynamics 365/CRM comes with a pre-defined sales process that can take you from a lead to a sales order and an invoice. It’s up to each organization to decide what entities they want to use in the process and configure it to meet their needs. The image below shows the sales cycles as defines by Out of the Box Dynamics 365 for Sales.


During the sales process, there is a level of interaction with products and prices at almost every step.

  • In the Opportunity, you enter the list of products and prices that your prospect is interested in.
  • In the quotes, you can copy the products selected in the related opportunity and update them, change the prices, rework the quote until it is accepted by the customer
  • The Order uses the products and prices defined in the accepted quote and the Invoice has the same information as the order

Prior to being made available for selection, the products and prices have to be defined and published in the system. The principle (at a very high level) is illustrated in the image below:



I’m purposely not going over details related to the Price List setup (e.g. volume discounts setup) or Opportunity (write-in products, percentage or amount based manual discount etc.). There is much more to it but I just want to give the big picture here. To sell products in CRM, you must have pre-defined Price List per currency. If you sell in a lot of regions, have a lot of different bundles with lots of product types and variances, then you might end up with a system that is loaded with products and price lists. There is no issue with this model as long as there is an established process to manage those products and price lists in your CRM system.

What are the challenges when ERP comes into play?

There are a few challenges that we see in the field as it relates to the process and structure above.

The out of the box sales process ends with the creation of a sales order and an invoice in Dynamics CRM/365. The reality is that a lot of businesses have these steps in an ERP or have to synchronize what CRM produces to a Finance system. This can be complex because the details about the products selected in the Quote/Order/Invoice have to have matching elements in the ERP system (usually SKU). This means that your product and pricing information has to be synchronized between your CRM and your ERP. This is often a challenge.

ERPs typically have more advanced ways to manage products and prices. They are not as rigid as the price-list based Dynamics 365/CRM structure. As an example, you can have product masters that serve as models for variants (e.g. product is a t-shirt, variants can be size, color, drawing etc.) and prices are automatically calculated based on the selected variables, the region and various other configurable parameters. These mechanisms present in ERP systems are in many cases more adopted for complex products and pricing structures because they allow for more flexibility and result with a model that is easier to maintain. The direct consequence for synchronizing product and price lists with Dynamics CRM/365 is that the data structure of the ERP either has to be converted into a price-list based model, or CRM has to be customized to match the ERP system’s product and pricing structure. Here are a few suggestions and guidelines when you are faced with this type of decision:

  1. If the ERP data model is not too far from the CRM model, you can simply make adjustments as required in CRM and synchronize the data between the 2 systems as needed.
  2. If the ERP data model is complex (e.g. uses product variants), ask yourself the following questions
    • Is it even possible to adapt the CRM data model to match the complex ERP structure?
    • What is the effort (data modeling, data transformation) to keep the CRM and ERP products and prices in sync? (Some ERP systems have ways to automatically generate a price-list based data structure for extraction)
    • Considering that the data model and sync issues are resolved, does the structure and volume of the data (number of products and price lists and data model) allow for a good user experience on the CRM sales side? (i.e. can users still easily find their products and price lists in CRM while working with opportunities or orders etc.). – you might have to create a custom user interface to provide your users with a better experience in order to drive adoption.
  3. Consider using a Configure Price Quote (CPQ) solution as an add-on to Dynamics CRM/365. Some of them are extremely well designed and provide the ability to configure complex product pricing structures while taking care of the user interface to build quotes (add products and automatically calculate prices etc.) and being integrated with Dynamics 365 for Sales/CRM and some ERP products.

A question is often asked: if ERPs are more flexible for pricing, why not have your sales people directly working in ERP? And the answer is simple for Dynamics 365. First, the cost, it is generally much cheaper to buy a Dynamics 365 for Sales app license than to buy an ERP license. Second, Dynamics 365/CRM is much more configurable to adapt to the business processes (creating fields, forms, views, relationships, integrated reports, sales suggestions etc.). I could go further but I won’t. Don’t have your sales people working out of your ERP, it’s generally a bad idea.

Conclusion

    Product management in Dynamics CRM/365 is a relatively rigid process. When you have a complex scenario where the products and pricing info comes from another system, it is important to carefully analyse the gap with the Dynamics 365 structure and to decide how you want to close that gap.

Happy CRM’ing!


Custom User Interface in CRM – HTML5/JavaScript or ASP.NET Web application?

Lots of times on CRM projects, we are required to build and display custom screens inside of Dynamics CRM. There are a lot of factors that come into play in the choice how we build these custom user interfaces. It is a very key decision to make because it can sets the standard technologies and tools that you use for custom screens on project (among other things). I thought I’d share some key points to consider in your decision making process to determine what technologies and tools to use.

The need for custom user interface

First, it is important to know and understand when it is required to build custom UI as part of your CRM solution implementation. As a wrote a few years ago, we sometimes find ourselves in situations where we can come up with a complex data model and we feel like the out of the box UI capabilities won’t be user friendly enough to drive user adoption. Situations like that create the need to build more user friendly interfaces to simply make people’s lives easier when they start using the CRM/XRM application. Some examples are

  • Custom calendar view for timekeeping
  • Tree view to display a complex hierarchy
  • Interactive Bing or Google maps views of CRM data
  • Editable grids
  • Complex grids with aggregates, grouping functionalities
  • Integrated views to other systems

And the list goes on. The needs are very different depending on the nature of the enterprise looking to implement a CRM/XRM solution.

Most common technology choices

From experience, I have seen a lot of custom screens built using the following tools/technologies:

  • Silverlight
  • ASP.NET / ASP.NET MVC
  • HTML/HTML5 + JavaScript + CSS

The screens are usually displayed in web resources or in an IFRAMES inside CRM on forms or as standalone modules (open from a button, display in sitemap area). I will not cover Silverlight since Microsoft announced its end of life and continued support for a few additional years, plus the world has started to move on.

ASP.NET / ASP.NET MVC

  • Pros
    • Flexible server side business logic
    • Easily integration with external systems (web services, databases etc.)
    • Can be very quick to develop depending on the requirements, expertise and accelerators (advanced controls provided by Telerik, Infragistics or others can speed up development time significantly)
    • Develop with a familiar programming language (C# + Visual Studio) – this of course depends on your development background
  • Cons
    • Requires the deployment and maintenance of a hosted web application
    • You have to deal with the weight of ASP.NET (ViewState, Postbacks etc.)
    • Not a great fit with CRM Online and cloud offerings in general (cloud deployment, cross origin web sites, integrations etc)
    • Required additional configuration in CRM + JavaScript to set the IFRAME’s sources (URL)
    • Effort for developing custom web applications is often under-evaluated

HMTL(5)/JavaScript

  • Pros
    • Usually, a much lighter solution and if done properly, custom UI components can be very responsive and provide great user experience
    • All components are in CRM Web Resources (html pages, JavaScript files, images…)
    • Custom screens are CRM Solution aware and there is no need for deploying and maintaining an additional web site on a web server
    • A definite plus for cloud based Dynamics CRM implementations
    • Can be very quick to develop depending on the requirements, expertise and accelerators (Kendo UI controls or others can speed up development time significantly; other frameworks like Knockout JS, Bootstrap can also be useful)
    • Leverage trending technologies and tools
    • Use any IDE you want (no obligation to use Visual Studio, even if you should J)
  • Cons
    • Possibly additional complexity if you need to display information from an external system
    • If not structured properly, JavaScript code can become massive and hard to navigate/maintain
    • For non-web developers, might be a steep learning curve
    • Need to establish good project structure and standards (e.g. use TypeScript or Script Sharp)

What else to consider?

There are other factors to take into account, some being specific to each organization. A few examples:

  • What is the expertise or your resource pool?
  • Do we have people to build and maintain what we deploy?
  • Are we in CRM Online or planning to move to CRM online?
  • Are we, as a company, trying to get into HTML/JavaScript more?

This is good food for thoughts for starters. If you want more guidance, feel free to reach out to us.

Leverage the power of Power BI with Dynamics CRM data

Yesterday I attended a presentation from felow MVP Sophie Marchand about Power BI. I had previous exposure to Power BI when it became generally available for free as part of the Office 365 suite. The content of the presentation was around multiple areas in Power BI, so I thought I’d revisit some of them and put some thought into how we could leverage the power of the Business Intelligence platform to manipulate and play with Dynamics CRM data.

Connecting Dynamics CRM Online to Power BI (web)

This area is well documented. When you create or log on to you Power BI account, you can get Dynamics CRM Online data by going to the “Get Data” section, selecting “Services” and finding the Microsoft Dynamics CRM content pack.

image

Once you click on Connect, you will need to provide your Dynamics CRM Online OData Service URL (make sure you don’t leave a trailing slash in the end).

image

Once this process is completed, Power BI will connect to your Dynamics CRM Online Organization, get a set of data and build a Sales Manager Dashboard. That dashboard is pre-configured as part of the native integration between CRM Online and Power BI. Keep in mind that you can also connect to CRM Online from Power BI for Desktop. The way the data is structured is a bit different and you don’t get the Sales Manager Dashboard by default. That being said, it bring the data in using the OData Service and gives you the ability to create your own visualizations and reports. Let’s take it a step further and see what options you have if you need to manipulate data from custom entities and create visuals, dashboards that are useful for your business.

Custom Dynamics CRM data sources for Power BI

If you want to leverage Power BI, which you should in many ways, there are other ways to connect it to your CRM data. Here are some of the options that you have.

Extract Dynamics CRM in Excel Spreadsheets and Import the files to Power BI

Power BI (web)

image

Power BI for Desktop

image

For CRM On-Premise organizations, using Power BI for Desktop, you can simply connect to your CRM Database start creating for dimensions, cubes and reports from there. If you need more information on how to get started with Power BI for Desktop, make sure you check out Microsoft’s article here.

image

Worth noting, you can export your Power BI for Desktop content and upload it to the Power BI web platform. This gives you the ability to view the dashboards online, question it differently and also share it with people in your organization. In the Dynamics CRM and Microsoft ecosystem, it has never been easier to build reports and intelligence around your operational data. Enjoy!

Synchronize CRM Online Data with Skyvia

I just discovered an interesting tool, thought I’d share a quick review.

Skyvia is a cloud data integration solution. It has connectors to a few data sources and is able to move data from one cloud based source to another based on configuration and schedule. Oh I forgot to mention, it’s FREE J

As a test, I configured it to connect to my Dynamics CRM Online sandbox organization and my Zoho CRM organization:

Once my connections are created and validated, I create and design a package to transfer contacts from Dynamics CRM Online to Zoho CRM:

As you can see on the screenshot above, Skyvia gives you the ability to create synchronization tasks (1) and also to schedule the execution your packages (2). The package can also be ran manually.

The tool can be very handy especially if you have simple synchronization needs between CRM and other systems. The supported data sources are Dynamics CRM Online, MySQL, PostgreSQL, SQL Server, Salesforce.com, Zoho CRM, Sugar CRM. Obviously such a tool does not replace the need for more complete and mature data integration products such as Scribe and SSIS connectors, but it provides an easy way to achieve simple data integration between multiple cloud based systems. Works perfectly for me demos! And it’s free (at least for now).

CRM 2013 – Leveraging Actions to get around JavaScript cross-domain challenges

Last year, I wrote about the challenge of cross-domain calls from JavaScript with CRM 2011. The issue was related to fact that from a security perspective, you could not have JavaScript functions executing on a CRM form event or when a ribbon button is clicked calling web services outside of the CRM domain. I proposed a few workarounds here but the bottom line is that in all cases, there was some sort of a negative impact in each solution. With CRM 2013, actions processes are a great way to get around the browsers’ cross domain restriction.

In this article, I am providing an example of how actions can be used to make a request to a web service outside of the CRM domain from a record’s form event.

Scenario

When users call an incident management center, the agents need to capture the temperature at the time of the incident in the city where it occurred. In order to do so, we decide to create a button on the command bar that agents can click on and that will perform the following tasks:

  • Read the City and Country information from the form
  • Call an Action that
    • Takes the City and Country as parameters
    • Use an external web service to get the temperature in the city
    • Returns the temperature as output parameters
  • Sets the temperature field value on the form

Note: In order to achieve this directly with JavaScript, performing Step 2 of the action would require to make a cross-domain call from JavaScript.

Process Configuration

To start things off, let’s start be creating an Action of type process. This action will have 2 input values (City and Country) and 2 output values (Temperature in Fahrenheit and in Celsius).

The steps are really simple. The action needs to a custom workflow activity that will connect to a weather web service and return the temperature in two output values of its own.

Custom Workflow Activity

Here you can see what the custom workflow activity code looks like. It’s very straight forward in that it only has 3 steps:

  1. Read the city and country inputs
  2. Initialize the web service client object and make the web service call
  3. Set the values back in the output fields to be available in the process

Calling Action from JavaScript

This is the last piece of the puzzle. At this point, we simply need to call the action created from a JavaScript event (ribbon button clicked, on change of a value etc.). In order to achieve this, create a function that is called when the client even occurs. That function should execute the Action, read the output values and set the field values on the form. This has to be done with a SOAP call. There are two ways to do this. You can:

  • use Deepak’s example as show in his blog post here or
  • use the CRM 2013 Sdk.Soap.js action message generator published by the Microsoft CRM SDK team to generate a request and response class you can use in your call to call the action using JavaScript.

Wrap up

This is another great example of how actions can be leveraged to work around challenges that we’ve dealt with in the pre-CRM 2013 era. Keep in mind that in order to call an external web service from the custom workflow activity, there are server security elements to take into consideration (e.g. can my server talk to the web service? etc.)

Hope this helps!

Dynamics CRM and multi-tenancy

It’s a topic that is often discussed and after Microsoft released its whitepaper on it late last year, I thought it would be interesting to write a short summary of some of the enterprise challenges that it addresses.

Definition

Let’s take a look at a few definitions of the term “Multitenancy

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple client-organizations (tenants).” – Wikipedia

Multi-tenancy is an architecture in which a single instance of a software application serves multiple customers. Each customer is called a tenant. Tenants may be given the ability to customize some parts of the application, such as color of the user interface (UI) or business rules, but they cannot customize the application’s code.” – WhatIs.com

Multitenancy is a reference to the mode of operation of software where multiple independent instances of one or multiple applications operate in a shared environment. The instances (tenants) are logically isolated, but physically integrated” – Gartner IT Glossary

Relating these definitions to Microsoft Dynamics CRM, we can view the CRM Server installation as the single instance of the application and the CRM Organizations created on the CRM server as tenants. The tenants are technical groupings within the application, such as separate databases within SQL Server.

Common Enterprise Challenges and Solutions based on Multi-tenancy

Function Localization

  • Description: Provide a common core of functionality, but with local functional variations – with “proper” delegation
  • Example: Multi-national company with business models that vary based on market size, legal compliance or other factors
  • Solution: Using multi-tenancy or multiple instances allows each business area or local region to have an independent implementation with its own local variations.

Master data management

  • Description: Provide a consistent, managed core of information, perhaps distributed only Selectively and enable smooth transitions without big bang replacements
  • Example: You may have an organization that operates in multiple areas and the terms/rules differ from one place to another. These different areas may need to manage their master data independently from one another. In these types of scenarios, it is important to maintain the data that is common across the different components and particularly critical is managing changes to that data. While there are different approaches for accomplishing this goal, many scenarios benefit by having a “master” for certain data sets because it provides for change management through that central master data source (master data management, or MDM)
  • Solution: This approach requires that the central master data be synchronized to all instances so that each instance has access to the latest version of the core information

Physical distribution

  • Description: Mitigate network latency
  • Example: For business solutions that support users that are physically distributed over large distances (global deployments), using a single instance may not be suitable because of the implications (such as WAN latency) associated with the infrastructure over which the users connect.
  • Solution: Distributing instances to provide users with more local access can reduce or overcome WAN-related issues, as the access occurs over shorter network connections

Security/privacy

  • Description: Accommodate legislative/national differences (e.g. patient confidentiality, Swiss banking, third-party use)
  • Example: This is usually a resulting of some sort of legal compliance. For example in Canada, healthcare patient information cannot be transferred from one province to another. If there was a national platform, there would have to be a way to prevent people from access data from patients living in other provinces.
  • Solution: In these types of scenarios, some or all of the data is stored locally, and potentially some of the data is stored centrally

Scalability

  • Description: Accommodate extreme volumes and/or extensive use of Service Scheduling; Provide for isolation of workloads (e.g. web site, customer kiosks)
  • Example: While a single instance of Microsoft Dynamics CRM can scale up and out to support the growth of a customer’s business, with very high data volumes or levels of complexity, there are additional considerations.
  • Solution: for scenarios in which groups of users work independently of each other in operational terms, it may be possible to host the groups on separate Microsoft Dynamics CRM instances and to use reporting to combine results across business areas for management oversight

Multi-tenancy Challenges and Solutions Patterns

The whitepapers has a full detailed section on possible solutions to the challenges introduced by multi-tenancy. These challenges come from the fact that the separation has to be managed. You may want to synchronize your metadata, or expose data from on CRM organization within a different organization.

SSIS Tools for Dynamics CRM

ETL is an everyday subject in medium to large enterprise ERP or CRM project implementations. When it comes to Dynamics CRM, there are many different ways to insert, update and delete data. Using Microsoft’s SQL Server Integration Services (SSIS) is a good solution because of its cost (included in the SQL Server suite), development environment familiarity (Visual Studio, SQL Server Management Studio).

The problem with Dynamics CRM in this context is that its database is not supposed to be accessed directly to read, create, modify or delete data (read operations are OK using Filtered Views). These operations must be done via the application’s API. Fortunately, there are third party tools that provide a “bridge” between SSIS and CRM through custom SSIS components. These tools transform their SSIS components into MS CRM API calls in the background, allowing to perform ETL by using Microsoft’s recommended and supported approach.

Here are some tools that I’ve seen or heard of that will help do your MSCRM data processing with SSIS:

  • Kingsway Soft’s SSIS Integration Toolkit for Microsoft Dynamics CRM
    • Notes
      • Supports CRM 3.0, 4.0, 2011, 2013 and Online
      • Comes with 4 major components: Connection Manger, Source Component, Destination Component, Option Set Mapping Component
    • Pricing Information
      • Free Developer License
      • Single Server Perpetual License @ USD $1295
      • Single Server One Year License @ USD $695
      • Single Server Annual Maintenance and Upgrade @ USD $295
      • Enterprise License – Contact for details
  • RSSBus‘s Dynamics CRM SSIS Components
    • Notes
      • Supports CRM 4.0, 2011 and Online (presumably 2013 as well but no official word on that on their website)
      • Free 30 day trial available
    • Pricing Information
      • Full SSIS Component Subscription (multiple workstation, Royalty-Free distrubiton) @ USD $999
      • Single Machine License @ USD $359
  • Team4 SSIS Connector for Microsoft CRM
    • Notes
      • Supports CRM 4.0, 2011
      • It looks like the product hasn’t been updated in a while given the lack of update on the website
    • Pricing Information
      • Connector is Licenses per server @ EUR 4600
  • CozyRoc Dynamics CRM Source and Destination Components
    • Notes
      • Mature product
      • The web site doesn’t mention support for CRM 2013 (it is supported though)
    • Pricing Information
      • Varies based on the Components you buy
      • Between $400 and $2500
  • Devart’s SSIS Data FlowDevart’s SSIS Data Flow Components
    • Notes
      • Devart has a large portfolio of data integration products
    • Pricing Information
      • Single License @ USD $249.99

 

Cheers!