SugarCRM is opening new headquarters in Germany

Europe has traditionally been a fertile ground for adoption of open source applications. The European union officially encourages using open source software (related PDF here), and many local authorities (mainly in France and Germany) have opted to go with open source alternatives (Linux for the desktop and Openoffice as an alternative to MSOffice). For business open source applications, Europe has a huge potential.

In other SugarCRM news, Sugar showcases several customers who adopted Sugar’s CRM on demand solution. I personally like the cloud alternative over the on-premise one, especially for the SMB. The advantages are clear, but the main concern is integration - you must make sure you can easily integrate the on-demand solution with other business applications (e.g. ERP).

In SugarCRM, logged in as Admin user, go to Admin>Module builder. On the left pane tree menu, click on the package you created (we called it Blog), containing the two modules - writers and posts. Click on writers to expand it, and click on ‘Fields’ icon. You will see a list of fields that are already part of the writers object. We will need to add some new fields (click on the ‘Add Field’ button), that better describe a writer object. You will notice that the writers module (screenshot below) has several fields under the ‘Person’ section, and none under the Writers section. That is because when we created the module, it ‘inherited’ fields from the Person type. Fields we will add now will be added under the Writers section.

Field properties

As you may know from working with other information systems, each object field has properties. Common field properties include name, label, type (string,integer,date etc.), length and required/optional. The most interesting property is type. SugarCRM provides us with a wide range of field types, which become very handy when defining simple as well as exotic objects.

For our writers module, we will add the following fields:

1. No. of posts - a numerator counting the number of posts published by the writer.

2. Subject - the subject the writer writes about. Possible values : technology, finance, environment. A writer can write about more than one subject, so multi-selection should be possible for this field.

3. Post Fees - the amount of money the blog owner pays each writer for a single post.

We selected these fields to demonstrate the flexibility provided by SugarCRM Module Builder in defining types of fields. Obviously, there are many other fields we need to create to properly represent the writer object.

Field Types

1. For the ‘number of posts’ field, we will select the Integer type ( assuming our writer will not write half or quarter post…). In this case, we would need the field to be auto-incremented when a new post is published by this writer. We will accomplish that later by using PHP code. Other properties of an Integer type in SugarCRM are help text that appears next to the filed, a default value and minimum and maximum values. There are also several ‘administrative’ properties that are shared by all field types: whether the field is required or not, should it be audited (e.g. every change in the filed value is logged to a log file) and field behavior during imports.

2. The ’subject’ field is an interesting one, because we need to allow multiple values to be assigned to a single field. That is not as trivial as it sounds, as databases usually allow a single field to have a a single value. To implement multi-value fields in a database, one is required to implement a separate table to hold the 1 to many relationship between the field and its values. In our case, SugarCRM does all that stuff behind the scenes.

Another important feature of the module builder is drop-down lists. Any type of thinkable object would contain fields with a limited list of possible value. The best example - countries. Almost any information system holds lists of countries. This list is well defined and limited, so we need to offer this list to the users to select from, and not allow him to type the country value by himself (interestingly enough, Sugar does not provide an out of the box drop down list of countries..). Our ’subject’ field will contain a drop down list with 3 values - technology, finance and environment. The user will be able to choose 1,2 or all 3 values.

3. You will almost certainly have to maintain information related to money in your CRM system. Be it for quotations, sales forecasts or reporting, monetary fields should be well supported. For our ‘post fees’ field (assuming a blog writer is paid by post), we will select either an integer or more likely, a decimal type, along with a currency field. Combined, these two field can represent any amount of money in any currency. Currencies are also maintainable in SugarCRM, including currency rates (can be maintained from Admin->Currencies).

The next post in this series will explain how to create custom user interfaces using SugarCRM Module Builder.

To make this tutorial more clear, we will create our won custom modules in SugarCRM. As an example, we will create modules that are related to blogs - blog posts and writers. We will then customize our writers object. But first, lets go over some of the customization options SugarCRM provides.

SugarCRM Customization Options

Go to Admin->Module builder. On the left pane of the screen, you should see all your custom packages and related modules, represented in a tree hierarchy. Since I created a Blog package and two modules - writers and posts - this is how my Blog package tree looks like:

You will notice under each module the following customization items:

1.Fields - these are the fields of the object you are building. The default list of fields is determined according to the ‘Type’ of module you selected when you created the module. In our case, for the writer object, we used the ‘Person’ type, so our custom writer object contains fields such as salutation, first and last name and address. We will later create additional custom fields that make up a writer object.

2. Labels - a list of all available labels of each field of our custom module. Although you can change each individual label by selecting the relevant filed, this list is a true productivity booster, especially when you need to change multiple fields.

3. Relationships -a simple, yet very powerful option. Relationships between object is a core attribute of information systems. Defining and creating relationships between objects can be a real task, but Sugar makes it as simple as possible. We will create relationships between the writers and the posts objects.

4.Layouts - layouts in SugarCRM module builder lets build screens that represent your objects with unparalleled ease, using a simple drag and drop interface. There are 3 different views you probably already know, that needs to be defined - edit view, detail view and list view. Additional layouts include Dashlets, which are the the small windows (sometimes called widget, gadgets, portlets..you get the idea) that appear in each users homepage, and search appearance, which let you define on which fields users can perform search.

As you can see, SugarCRM provides powerful customization features that require no coding (although in later posts we will show you some real code power). Just one thing you need to remember - after making any changes to your module, you need to deploy the relevant package for the changes to reflect in your SugarCRM instance. If you made changes but you can’t see them in the user interface, you forgot to deploy your package! ( To deploy, go to Admin->Module Builder, click on the modified package, and then on the deploy button. See screenshot below).

Next post will explain how to add some of our own fields to the writers module, and create relationships between the writers object with the posts object. We always recommend you register to our RSS feed.

Using the term ‘Modules’ might deter novice SugarCRM developers, because Modules sounds like something very complicated (in ERP systems, for example, modules are usually difficult to develop). But all a module does is describe the properties and supporting UI elements required to support a certain type of data object. SugarCRM supports many objects (modules) out of the box - accounts, leads, projects,cases, calls etc. If you want to add support to a new type of object, you need to to create a new module to represent that object.

Creating a new module

First, we need to create a new package. To create a new package in SugarCRM, go to Admin->Module Builder, and click on New Package. A package can contain a single or multiple modules. If you plan to develop several modules which relate to each other, it’s a good idea create them under a single package - it will be easier to deploy them later.

Create new package screenshot

There are 4 fields you need to fill here: Package name, description and author are obvious. As for the ‘key’ field - this will be used as a prefix for any module you will create under this package.

Once a package is created - it is time we create module. After saving the new package (you click the Save button, didn’t you??), you can select the new package by clicking it. Then, just click on New Module. The New Module screen appears:

The important thing to notice in this screen is the Type field - module types are reusable templates of data structures and UI elements. When you select any of the types available, a set of pre-configured module attributes are created. That includes fields, menus and relationships, based on commonly used objects, such as companies, files and people. So if you plan to create a new objects, let’s say a ‘Vendor’, and assuming all your vendors are commercial companies, you would select the ‘Company’ type as a template for your Vendor modules. I often end up using the basic type, but that decision is really up to you. Click Save.

You have now created a new module in SugarCRM. It should appear in the application’s top menu, along all other modules. Although your new module is ready and you can start using it (create new objects, delete or update existing ones), you want to further customize your new module.

The next post will explain how to further customized the data structure and the user interface of your module. Register to our RSS feed to learn more on SugarCRM customization.

Marketing campaign and partner management enhancements

SugarCRM 6.0 is coming up next year, and plans for that release have been detailed by Clint Oram, who founded sugarCRM over 4 years ago, and currently holds the position of of V.P. of product development. SugarCRM 6.0 is going to provide enhancements in two area:

1. Marketing campaign management: as more and more people do business on the web, larger amounts of ad dollars go to online ad campaigns. It becoming an integral part of any marketing strategy. Sugar plans to improve manageability of multi-step campaigns, in which potential customers are engaged in several ways, including phone, email and web advertisement.
2. Partner portal: networking is everything these days, and developing strong relationship with your partner network is crucial to the success of any business. SugarCRM 6.0 will include a dedicated partners portal, allowing your partners to login securely to your SugarCRM system and access relevant information.

According to Oram, the next sugarCRM release, 5.5, will include mainly platform enhancements rather than new functionality.

IBM - SugarCRM Integration

When one of the largest IT companies in the world wants to integrate with your application, you must be doing something right. IBM announced that it’s Web 2.0 platform, sMash, would be able to run SugarCRM and enable developers to easily build composite applications on top of it. Such composite applications, or business mashups, can integrate SugarCRM with an ERP solution, for example. Another important enhancement the sMAsh integration provides is the ability to build and execute workflows on top of SugarCRM. It would be interesting to see Mashups including SugarCRM, especially with ERP systems. A robust composite application, pulling data from multiple backend systems, including Sugar, can make it easier for the IT team to sell SugarCRM inside the enterprise, as users don’t like multiple applications, each with their own UI.

Collapse Unused Sub-Panels

• Greatly decreases processing required to render the page.
• We don’t retrieve information for hidden sub-panels. Users can expand them as they desire.

The important part of the suggestion is “we don’t retrieve information for hidden sub panels”. We did not try to implement this specific tweak, but it made us head to a new direction in our search for parameters influencing the performance of SugarCRM search. After all, scanning 100,000 contact records and fetching the names of each contact which matches the search query should take very little time. After all, we do use indices (the MySQL contact table has indices on both first name and last name, which are the fields we used for the search). There must be another problem. A problem with some additional information displayed in the result page.

Contacts search result page screenshot - multiple fields returned

By default, for the contact object, the search result page displays not only first and last names, but also the related account, email address and more. These additional fields are not part of the contact table. That means that sugar has to join together several tables to retrieve these values. And joins are expansive. It is likely that more time is spent  on retrieving these additional pieces of information than on the actual search in the contacts table for records matching the search query. I will check these execution times later and provide you with exact figures.

Trying An Easy Fix

My next obvious step was to go to the SugarCRM studio and remove the extra fields from the search result page. SugarCRM provides amazing simplicity as far as user interface configuration is concerned. Removing those fields (and later restring them) took me no more than a minute. amazing!.

I thought I was on my way to another ‘improve SugarCRM performance by 300%’ post, but then something strange happened. I checked the search query performance with a result page displaying only a single field from the contacts table (Name), but the performance was identical to that of displaying multiple fields from multiple tables. I rechecked, rebooted the SugarCRM server, the client, everything. Nada. Same search performance. Something’s wrong here.

SugarCRM - MySQL Queries Examined

I configured the MySQL server log to output sql queries, then compared the sql queries used for both scenarios - a single field returned vs. multiple fields from multiple tables returned. And the queries were identical. Both had tons of join statements to the email_addresses and the contact tables.
I plan to report this as a bug to SugarCRM. I’m not even sure if this is a bug, but it’s the wrong behavior. I believe that if they fix this, the performance boost for searches will be incredible. I also believe the same behaviour (same sql query regardless of the fields that are actually displayed in the result page) is common to searches of all type of objects (accounts, leads etc.) in SugarCRM.

I will keep you posted on the status of the bug fix I open. Register to our RSS feed - like all good things in life, it’s fun and it’s free!

SugarCRM

SugarCRM, the most popular open source CRM solution, offers a wide range of online demo systems. Separate demos are available for community, professional and enterprise editions.
SugarCRM Demo Systems

OpenCRX

OpenCRX offers two demo systems: one with a complete OpenCRX instance, offering all available functionality, and another one for its store frontend application.
OpenCRX Demo Systems

Vtiger

Vtiger offers a wide choice of their CRM application test systems. You can choose amongst systems in several languages, and more interestingly, between several roles. Roles are part of any modern open source business application, and Vtiger allows you to login to its demo systems as a sales manager, marketing manager, support manager and stores manager. Testing a CRM system using different roles provides a different perspective, both on in terms of the look and feel of the application, as well as its breadth of functionality.
Vtiger Demo System

But since applications built on top of PaaS solutions are only limited by the developers imagination, software developers began developing fully functional, stand alone applications, which had nothing to do with the ‘original’ application build on top of that particular PaaS, CRM in the case of Force.com. 

SugarCRM, the leading open source CRM solution, provides their own development platform, called SugarForge. Initially, trivial extensions to SugarCRM core functionlaity were built, such as integration with external mail software and browsers. During a recent visit to SugarForge’s application directory, I was surprised to find attempts at building fully functional ERP solutions on to of the platform.
Should SugarCRM endorse these solutions, or even develop their own ERP system on the based on SugarForge? I say No. There are two dimension to that question which require further discussion - a technical dimension and a commercial dimension.

ERP Vs. CRM - Technical Dimension

ERP and CRM systems are different. Obviously, functionality provided by ERP applications far exceeds that of CRM. But adding new functionality is relatively easy when the platform you run your application on is robust and flexible, and SugarForge sure is. 

But there is one major difference, which will require major changes in the platform to become ERP ready - transaction processing. ERP systems are designed to support on business transactions. Think of a ’simple’ transaction occurring tens, hundreds or even thousands of times a day in a manufacturing company - a new production order is opened, B.O.M for that order is exploded, purchase requisites are automatically generated, stock is allocated for required materials, machine and personnel time are scheduled, and on and on. That type of transactional processing requires dedicated mechanisms in the underlying platform. CRM systems are much more static and atomic. Relationship between objects (Account and Contact, Lead and account, for example) is more simple.

ERP Vs. CRM - Commercial Dimension

From a commercial prospective, there is a huge difference in terms of sales cycle and channel partners between ERP and CRM. Sales cycles for ERP solutions are notoriously long, as company wide requirements should be met, and budget for the project is usually big. CRM solutions serve the sand possibly support staff, making the decision making process much more simple.

The partners channel is different also - most ERP implementations require a local partner to implement and customize the selected solution to fit to customer requirements. CRM applications, on demand in particular, have a very small initial overhead, and once basic parties (contacts, leads, contacts etc.) have been imported, the solution is ready to service the first users.

For sugar to move to such long sales cycles and to become dependent on a partners ecosystem, would mean putting too much energy away from of its main target audience - the salesforce.

The solution? Integrate with ERP

What SugarCRM do is work with an open source ERP vendor on tight integration. Combine a fully functional open source ERP with a CRM solution, add open source BI to the mix, offer everything both on demand, as well as on site, and you have a win-win-win-win situation (the forth winner is the customer). A source tells me that work is underway.

We begin our optimization efforts with a small configuration change that will have a huge positive impact on performance. When you are looking to optimize an application which is built on top of on several other components, the first place to look for optimization ideas is on the application vendor website and forums. You are likely to get your best advice there.

SuagrCRM Performance Guidelines

Open source CRM Vendor SugarCRM provides there own basic performance guidelines. Since we are trying to solve search related performance issues, we will focus own efforts around a suspicious looking guideline: “Drop Absolute Totals From List Views”. When you search for a contact in SugarCRM, results are paged with 20 results per page. You can also see the total number of results your search query returned. Look at the screenshot below:

As we saw in our baseline SugarCRM test results, when a search is performed in SugarCRM, the factor influencing search performance most is the number of results returned, and not the total number records that were searched. That was a bit strange, because SugarCRM only displays 20 results per page. But reading about the configuration option “Drop Absolute Totals From List Views” everything falls into place - the extra time required for a query with a larger result set to complete, is the time used to calculate the total count of the results set!!.

Test Results

We used OpenSTA to run a benchmark, ramping up 5 users who perform search for Contacts on 100K records in our SugarCRM instance. The blue bar represents search duration, in seconds, when the totals are hidden (disabled), while the orange bar represent total enabled results. The results are crystal clear - an improvement of 300% on average for search performance when totals are disabled.

Discussion

Now, I honestly can’t understand why would SugarCRM show totals by default, when they are fully aware of the detrimental effect enabling this option has on search performance. The only drawback to disabling it is that you do not know how many results have been returned. Do we care? No. When you search for something on Google, do you ever take a look at how many results were returned?  What you do is browse the first, second, maybe third page for relevant results.

SucralosCRM Configuration Option 1:

Set Drop Absolute Totals From List Views to True, as explained in SugarCRM’s performance guide.

For more SugarCRM performance guidelines and other open source CRM news, register to our RSS Feed.

LAMP Stack

Like any other business application, SugarCR relies on services from 3 different software components: an application server (Apache & PHP), a database engine (Mysql or MSSQL) and an operating system (Linux,Unix or Windows). When it comes to performance tuning for SugarCRM, we will have to optimize each of these components separately. There are endless configuration options for each of them, but Pareto’s principal is definitely applicable here - 20% of the available configuration options will provide 80% of the performance boost we are looking for.

Project SucraloCRM will focus on the LAMP stack: the operating system we will use is Linux (Ubuntu), the database server is MySQL and the application server includes Apache and PHP. 

Mysql

As far as overall application performance is concerned, the most important component for any CRM application is the database engine. CRM applications often manage very large amounts of data - hundreds of thousands and even millions of contact details, accounts or calls. All these records are stored in database tables.
Database optimization is an area of vast knowledge and experience. Mysql specific performance optimization techniques have been studies for years. We will be putting a lot of time and energy into bringing Mysql to optimal performance for SugarCRM.

PHP

PHP is the most popular language used to build dynamic web applications. It is a very simple yet powerful language, allowing developers to add new functionally with relative ease. However, PHP is also an interpreted language. That means that the compiled PHP code cannot be executed directly by the underlying operating system, but rather an intermediate piece of software needs to translate the PHP compiled code to a code to be executed by the operating system. Interpreted languages (such as PHP and JAVA) performance is poorer than native languages, such as C or C++.

Fortunately, due to PHP’s huge popularity, there are many tools and guides relating to improving PHP performance.

Apache Web Server

The Apache web server hosts 60% of websites on the Internet, serving millions of pages every second.It is considered to be one of best software applications ever written, performance wise.  

Apache acts as an intermediate between the operating system and the PHP interpreter. Optimal Apache performance is a crucial ingrediente to the success of project SucraloCRM.

Operating system

The software component which is the furthest away from the SugarCRM application is the operating system. The operating system is an area with the largest number of configuration permutations, but with the lowest impact on the overall SugarCRM performance.

To stay up to date with the progress of project SucralosCRM, and other SugarCRM news and updates, register to our RSS Feed.