Salesforce Selective Queries

A basic understanding of the selective query concept is fundamental to scalability on the Salesforce platform. Non-selective queries have a significant performance impact on List Views, Reports and SOQL and can often result in Apex Trigger runtime exceptions as below – as the data volume increases over time.

System.QueryException Cause: null Message: Non-selective query against large object type (more than 200000 rows)

SOQL queries executed from Apex Triggers have to be selective where the record count in the queried object is 200,000 or more. The determination of a selective query is a function of volume plus field-level selectivity. It is therefore the case that the selectivity state of a given query is volatile meaning in practical terms that the initiating Apex Trigger may work one day and not the next.

Selective Query Definition

Selectivity is determined by the index state of the query filter conditions and the number of records the filter returns (selectivity) versus the object total records. The thresholds below show the difference between the selectivity calculation for a standard index versus a custom index.

Selectivity Thresholds:
Standard Index – 30% (first 1M) then 15%
Custom Index – 10% (first 1M) then 5%

Unary filter:

e.g. select Name from Account where IndexedField__c=’ABC’

With a custom index on IndexedField__c the filter must return <10% of the total records in the object to be considered selective – up to the first 1 million records from that point the threshold drops to 5%.

Multiple filters AND (exclusively):

e.g. select Name from Account where IndexedField__c=’ABC’ and SecondIndexedField__c=’123′

The Query Optimiser will set the leading operation on the basis of lowest cost. If no filters are selective a table scan is performed.

If all filters have an index then a Composite Index Join optimisation can be applied.

In this case each filter must be less than 2x (two-times) the selectivity threshold.
All filters combined must be less than selectivity threshold.

If all filter fields are standard then use the standard index selectivity threshold – otherwise use custom index selectivity threshold.

Multiple filters OR (at least one):

e.g. select Name from Account where IndexedField__c=’ABC’ or SecondIndexedField__c=’123′

Selective AND filter indexes could be set as the Leading Operation – if none exist, then a table scan occurs unless all filters have an index then a Composite Index Union optimisation becomes possible.

In this case each filter must be less than selectivity threshold.
All filters combined must be less than selectivity threshold.

If all fields are standard then use the standard index selectivity threshold – otherwise use custom index selectivity threshold.

Parent Field Filter:

e.g. select Name from Contact where IndexedField__c=’ABC’ and Account.IndexedField__c=’ABC’

Where parent object fields are referenced in a filter, each filter index is individually and the lowest cost option selected as the leading operation.

Note, the parent field is not indexed on the queried object, so Account.Id can incur a table scan on Opportunity whereas AccountId may allow the standard index to become the leading operation.

The notes above provide a basic outline of the concepts but should be sufficient to convey the key concepts.

 

Implementation Approach

As data volumes grow query behaviour can change dramatically, to mitigate this database queries originating in Apex code, list view and report definitions must consider the future peak data volume and field-level data characteristics (primarily selectivity). This considered approach can help identify an appropriate indexing strategy that maintains query performance by ensuring query selectivity. So, forward planning is absolutely key; queries should be designed to be selective up to the projected peak data volumes. Thinking ahead to this extent is very seldom applied in my experience, particularly where the Salesforce implementation evolves project-by-project and the longer term picture is not a priority.

In order to evaluate the selectivity of a given query – the following 2 approaches can be applied.

REST API Query Resource Feedback Parameter

The Force.com REST API exposes a Query resource that accepts an explain parameter which can set with a SOQL query, List View Id or Report Id. The results show the options considered by the Query Optimiser and the lowest cost option (leading operation) taken. A relative cost value of less than 1 indicates a selective query, anything higher indicates non-selective. The example below shows the output for a report where a report filter hits an indexed field.

selective-queries-api

Developer Console Query Plan

From Summer ’14 on the Developer Console can be enabled [Help>Preferences>Enable Query Plan] to display a Query Plan option on the Query Editor tab. The construct of the output is the same as the API approach. Note, this appproach is limited to SOQL queries.

selective-queries-developer-console

The references section below provides link to the technical detail of the 2 approaches introduced above.

 

References

Query & Search Optimisation Cheat Sheet

Developing Selective Force.com Queries through the Query Resource Feedback Parameter Beta

Developer Console Query Editor

Improve performance with Custom indexes using Selective SOQL Queries

Custom Index Request Checklist

Platform Cache – Salesforce Winter ’16

This video post provides a technical overview of the Platform Cache feature added in the Winter ’16 release.


Salesforce Implementation Audit

This post provides an outline approach to consider when performing an internal audit of an existing (or emerging) Salesforce implementation. As an individual who specialises in the provision of such quality assurance services from an external perspective, I’m convinced that most projects would benefit from a periodic internal review, perhaps augmented by some occasional external perspective and insight (Salesforce services can help here). However this is approached, in the majority case the internal project team will have the requisite experience and competency to deliver such an introspective review, the challenge is often one of finding the right time, or indeed any time, to conduct it. This is why a retrospective build review should be planned every 3 or 4 sprints (or thereabouts – projects differ) with a full implementation audit scheduled every release. The principal being that whilst the build is in flight, periodic sense checks are made on key quality aspects, technical integrity, platform limits etc. with a comprehensive audit applied pre-release (ideally). The latter may need to consider a combined future deployment state where multiple parallel development streams converge into a single production org.

Note, an implementation audit is build-focused (or solution oriented) and should not assess the fit-for-purpose nature of the functionality in respect to business requirements (i.e. the problem-to-solution mapping). The only exception to this arises where an obvious mapping to a standard feature is missed resulting in a “gap” that is unnecessarily filled by a technical solution option.

Note, in order to cut down on the time required to conduct the audit access to individuals who can describe the functional intent is imperative. In the internal case the programme/project architect should be leading the audit and should be aware of the functional design context.

Note, before diving into the detail of the implementation, it can be highly valuable to re-define the high-level solution architecture (HLSA) in current state terms. The key point being that the macro-level view is often distorted by micro-level design decisions made during the project course. A periodic check is useful to ensure that this organic change is understood and that the integrity of the original architectural vision is maintained.

Indicative review areas are listed below (this is not exhaustive)

Declarative build environment
1. Identify platform limits that are reaching a high percentage of utilisation that may present risk to scalability and future phases of development.
2. Identify any future maintainability risk presented by the conventions applied in the definition of configuration elements (e.g. naming conventions, opportunities for best practice improvements.).
3. Identify functional areas where a mapping to standard features could be achieved.
4. Identify security vulnerabilities (org-access, sharing model etc.).

Technical customisations
1. Identify risks to data integrity and application responsiveness.
2. Document risks to scalability and extensibility imposed by platform execution limits.
3. Document deviations from best practice technical patterns, conventions and coding standards.
4. Identify security vulnerabilities introduced by technical componentry.
5. Document deviations from best practice development practices and process.

Integration architecture
1. Identify risk associated with deviations from best practice integration patterns and practices.
2. Identify opportunities to reduce limits consumption.
3. Identify data integrity and scalability vulnerabilities related to the current state integration architecture.

Identity management
1. Identify risk associated with implemented single sign-on processes and related services/infrastructure.
2. Document deviations from best practices related to identity management.

Salesforce Platform Limits – Designing for Scale

A Salesforce instance is a constrained environment where limits exist in respect to capacity and execution. Examples of capacity limits being data storage, number of active users, number of custom objects/custom fields/picklists etc. examples of execution limits being API calls per 24-hour period, SOQL queries executed within an Apex transaction, Viewstate size in Visualforce pages etc.. In both the capacity limit and execution limit case it is imperative that the existence and implications of the constraints are factored into the solution design from the outset. Each and every constrained resource must be treated as a precious asset and consumed in an optimised manner even on seemingly trivial implementation projects. From experience it is often the case that a Salesforce implementation grows (in terms of both use and breadth of functionality) at a rapid rate once it gains traction in an enterprise. If you’ve carelessly exhausted all the constrained resources in the first release, what happens next? Note, some soft limits can be increased by Salesforce on a discretional or paid-for basis, however this doesn’t negate the need to make responsible design decisions and at the very least the highlight the possible additional cost associated with a particular approach. Hard limits do exist in key areas, the Spanning Relationships Limit or cross-object reference limit as it also referred is a strong example of this.

Designing for scale simply requires an intelligent consumption of such resources and appropriate solution design decisions in a limited number of areas. The proliferation of Apex and Visualforce related execution limits don’t necessarily impact the scalability of the implementation, the impact is isolated to the micro level. The selected limits listed below however apply at the org level (Salesforce instance) and can constrain the scalability of an implementation (in functional terms). This list is not exhaustive, for a complete picture refer to the Salesforce Limits Quick Reference Guide.

Limits Primarily Influenced by User License Model

Asynchronous Apex Method Executions :
This limit includes @futures, Batch Apex (start, execute and finish method invocations and Scheduled Apex (execute method invocations). Future method calls made from Apex Triggers can be a risk in relation to this limit. For example, Apex Triggers which fire on record updates which make callouts via @futures can cause scalability issues as data volumes grow. In this example it may become necessary to bulk process the modifications via Batch Apex, assuming a batch style of integration is acceptable. What if near real-time (NRT) is necessary?

The calculated limit is the higher number of 250K or (200 * user license count), where the applicable licenses to this calculation are full Salesforce and Force.com App Subscription only.

Total API Request Limit :
Enterprise Edition = 1,000 per Salesforce and Salesforce platform license, 200 for Force.com App subscription license
Unlimited/Performance Edition = 5,000 per Salesforce and Salesforce platform license, 200 per Force.com app subscription license

Note, sandboxes have a flat limit of 5M which can give a false impression of the limits applied in production.

All inbound API traffic counts towards this limit, including Outlook plug-ins, Data Loader etc. For implementations with limited Standard users this limit can be restrictive, and it is reasonably common for extension packs to be purchased to mitigate this. In all cases consumption must optimised by batching updates and use of the Bulk API where possible.

Limits Primarily Influenced by Salesforce Edition

Workflow Time Triggers Per Hour :
Enterprise Edition = 500
Unlimited/Performance Edition = 1000

This limit can be an issue for implementations with high volume transaction processing throughputs, where time-based workflow is employed to send reminder emails etc. If the hourly limit is exceeded triggers are processed in the next hour and so on. This may cause issue if the actions are time critical.

Workflow Emails Per Day :
1,000 per standard Salesforce license, capped at 2 million.

Apex Emails Per Day:
1,000 in total. The maximum message count per send is limited per edition.
Enterprise Edition = 500
Unlimited/Production Edition = 1000

An unlimited number of emails can be sent per day to Users by using the SingleEmailmessage.setTargetObjectId() and MassEmailmessage.setTargetobjsctIds() methods. This includes customer and partner portal users and even high volume portal users.

This limit is critical to understand and to mitigate in a scalable solution design. In short don’t use Apex to send email unless the recipient is a User. In portal cases use the User Id and not the Contact Id. Prefer Workflow based email sending, as the limits Are considerably higher, and perhaps use Apex script to set criteria picked up by a Workflow rule.

Additional Limits to Consider

Batch Apex (5 pending or active)
Scheduled Jobs (100 scheduled)
Apex Script Characters (3M)
Dynamic Apex Describes
Cross Object References

From a best practice perspective a Platform Limits Reference document should be maintained for all Salesforce implementations that lists the applicable limits and related consumption. This approach surfaces the existence of the limits and should provide design principles such as using Workflow to send customer emails in preference to Apex script. Without an ordered approach where limit consumption is proactively tracked it is highly likely that expensive refactoring exercises or multi-org strategies become necessary over time that could have been minimised,deferred or entirely avoided.

Any-org Design Considerations

The concept of any-org development is an interesting one. The strict definition, to my mind, being the development of a set of components (perhaps packaged) that are designed and coded specifically to install and function in any Salesforce org. This is typically an ISV concern, where testing and maintaining a single-code base can be highly desirable over managing a base package plus multiple extension packages, or in the worse case multiple independent packages. Either way an ISV needs to maximise the addressable market for a product whilst minimising the ongoing effort to do so. The same drivers do not apply in the single-org case, where a consultancy and/or in-house team are delivering technical components to be installed into a known Salesforce org (or multi-org estate). In the single-org case it is common practice to see technical components designed and coded for the current state of the target org(s), with no consideration to how the org(s) may evolve over time. This can often result in situations where costly technical work is required simply to activate an optional product feature, or to provide user access in another locale. In such cases the situation can often be compounded by the fact that the original development team are no longer available.

In short, in my view some degree of future-proofing should be considered in designing for the single-org model, using the techniques applied by ISVs in the any-org model.

    Any-org Design Considerations

  1. Optional Features
  2. Examples; Person Accounts, Quotes

    There are a multitude of optional product features which can be enabled directly in the Salesforce web application UI or via Salesforce support. In the majority of cases such feature activations irreversibly add new objects and fields to the Salesforce instance. From the perspective of keeping simple orgs uncluttered by objects related to unused features this makes perfect sense. From the perspective of designing for the any-org model, this approach poses a few challenges. The main challenge being that Apex code won’t compile where a static reference exists to an object (or field) that doesn’t exist in the org. There is no simple answer to this, instead a selective approach should be taken where optional features that may already be active (or could in the future be activated), that have some impact on your code are accommodated. The approach to achieving this for any-org Apex code basically involves replacing static references with Dynamic SOQL and Dynamic Apex (see coding techniques below).

  3. Multi-currency
  4. The default currency mode of a Salesforce org is single currency, the majority stay this way. It is however common to have multi-currency and perhaps advanced currency management (ACM) activated in orgs where business operations are international. Activation of multi-currency often occurs once the Salesforce org has become established, perhaps in a single region. This can be problematic where technical customisations have been added that aren’t currency aware.

    In the any-org case, all Apex code should be multi-currency aware and use Dynamic SOQL to add the CurrencyIsoCode field to all object queries involving currency fields. Additionally, currency aware logic should include checks to ensure that related transactions are the same currency, and that custom analytics are presenting data in the corporate currency (default and therefore expected behaviour for the native reporting functions). Note, the behaviour of aggregate functions involving currency fields must also be handled.

  5. Editions Support
  6. A key design decision for ISVs is the Salesforce editions to be supported by their managed package. This one has less relevance to the single-org model, unless the multi-org estate includes different editions.

    It is possible to group editions into two distinct groups;
    1. Group (or Team) Edition and Professional Edition
    2. Enterprise Edition and Unlimited Edition

    In the case of group 1 assume that standard objects such as Product, Pricebook2, PricebookEntry, RecordType do not exist and ensure no static references exist in the code. The OrganizationType field on the Organization object tells us which edition the code is executing within.

    public static Boolean isTeamOrProEdition(){
    		
    	if (isTeamOrProEdition==null){		
    		List<Organization> orgs = [select OrganizationType from Organization where Id=:UserInfo.getOrganizationId() limit 1];
    		if (orgs.size()>0)				
    			isTeamOrProEdition=(orgs[0].OrganizationType=='Team Edition' || orgs[0].OrganizationType=='Professional Edition');
    	}
    	return isTeamOrProEdition;
    }
    
  7. Internationalisation
  8. Whether an international user base is anticipated or not it is general software development best practice to externalise string literals into resource files. In the Salesforce context this means Custom Labels. A best practice here is to apply strict categorisation and a meaningful naming convention. Also ensure all literals are externalised not just labels in the UI, for example trigger error messages.

    Another consideration for i18n is the use of currency and date formatting helpers. Where UI components do not apply default formatting for an SObject field you need to handle this in code. An i18nHelper class which translates ISO locale and currency codes to date format strings and currency format strings plus symbols respectively can be very helpful.

    Useful abbreviations:
    i18n – internationalisation; development practice enabling support for localisation.
    l11n – localisation; act of localising an internationalised software product for a specific locale.

  9. Profile Permissions
  10. Visualforce pages are preprocessed for components directly bound to SObject fields where the user profile does not have CRUD or FLS permissions. In such cases the fields are not displayed or are made read-only, depending on visibility state. This comes as a surprise for many developers who assume that User Profile permissions are entirely ignored on Visualforce pages.

    reference: Enforcing_CRUD_and_FLS

    In the any-org model, where direct SObject field binding is being used in a Visualforce page, this may require a manual check during initialisation to protect the functional integrity of the page. For example, a custom page with no fields displayed and no explanation is not a great user experience, instead the page should simply inform the user they don’t have sufficient permissions, they can then take this up with their Administrators.

    private Boolean hasRequiredFLS(){    	
        // rule 1: all custom fields must be accessible.
        // rule 2: check isUpdateable on all fields where inline editing offered.
        	
        Schema.DescribeFieldResult d;
        	
        Map<String, Schema.SObjectField> siFieldNameToToken=Schema.SObjectType.SalesInvoice__c.fields.getMap();
        	
        for (Schema.SObjectField f : siFieldNameToToken.values()){    		
        	d = f.getDescribe();
        		
        	if (!d.isCustom()) continue;
        	if (!d.isAccessible()) return false;
        }
    
        d = siFieldNameToToken.get('InvoiceDate__c').getDescribe();
        if (!d.isUpdateable()) 
        	this.isInlineEditable=false;
        else {
        	d = siFieldNameToToken.get('DueDate__c').getDescribe();
        	if (!d.isUpdateable()) 
        	    	this.isInlineEditable=false;
        	else this.isInlineEditable=true;
        }	
        return true;
    }
    
    Coding Techniques

  1. Dynamic SOQL
  2. Do not statically reference objects or fields that may not exist in the org. Instead compose Dynamic SOQL queries and execute via Database.query(). With this approach, you can build the required query using flags which indicate the presence of optional feature fields such as RecordTypeId, CurrencyIsoCode etc. The Apex Language Reference provides good coverage of Dynamic SOQL. Be very careful to ensure that your composed string does not include user supplied text input – this would open up a vulnerability to SOQL injection security vectors.

    public static Id getStandardPricebookId(){
    	if (standardPricebookId==null){			
    		String q='select Id, isActive from Pricebook2 where IsStandard=true';
    		SObject p = Database.query(q);
    	
    		if (!(Boolean)p.get('IsActive')){
    			p.put('IsActive',true);
    			update p;
    		}
    		standardPricebookId=(String)p.get('Id');
    	}
    	return standardPricebookId;
    }
    
    public SalesInvoice__c retrieveSalesInvoice(String siId){
        try{
            //& Using dynamic Apex to retrieve fields from the fieldset to create a soql query that returns all fields required by the view.
            String q='select Id,Name,OwnerId';
            q+=',TotalGross__c';
    
            for(Schema.FieldSetMember f : SObjectType.SalesInvoice__c.FieldSets.invoices__Additional_Information.getFields()){
                if (!q.contains(f.getFieldPath())) q+=','+f.getFieldPath();
            }   
                
            if (UserInfo.isMultiCurrencyOrganization()) q+=',CurrencyIsoCode';			
            if (AppHelper.isPersonAccountsEnabled()) q+=',PersonEmail,PersonContactId';  
                
        	q+=',(select Id,Description__c,Quantity__c from SalesInvoiceLineItems__r order by CreatedDate asc)';     		
            q+=' from SalesInvoice__c';
            q+=' where Id=\''+siId+'\'';
                
            return Database.query(q);
        } catch (Exception e){
            throw e;
        }
    }
    
  3. Dynamic Apex
  4. Do not statically reference objects or fields that may not exist in the org. Instead use Dynamic Apex techniques such as global describes and field describes. Where a new SObject is required, use the newSObject() method as shown below, this is particularly useful for unit test data creation. The Apex Language Reference provides good coverage of Dynamic Apex, every developer should be familiar with this topic.

    public static List<SObject> createPBE(Id pricebookId, List<SObject> products){
    	SObject pbe;
    	List<SObject> entries = new List<SObject>();		
    		
    	Schema.SObjectType targetType = Schema.getGlobalDescribe().get('PricebookEntry');
    	if (targetType==null) return null;
    				
    	for (SObject p : products){
    		pbe = targetType.newSObject();
    			
    		pbe.put('Pricebook2Id',pricebookId);
    		pbe.put('Product2Id',p.Id);
    		pbe.put('UseStandardPrice',false);
    		pbe.put('UnitPrice',100);	
    		pbe.put('IsActive',true);
    		entries.add(pbe);
    	}	
    	if (entries.size()>0) insert entries;	
    	return entries;
    }
    
  5. UserInfo Methods
  6. The UserInfo standard class provides some highly useful methods for any-org coding such as;
    isMultiCurrencyOrganization(), getDefaultCurrency(), getLocale() and getTimezone(). The isMultiCurrencyOrganization() method will be frequently used to branch code specific to multi-currency orgs.

    public static String getCorporateCurrency(){			
    	if (corporateCurrencyIsoCode==null){			
    		corporateCurrencyIsoCode=UserInfo.getDefaultCurrency();
    		
    		if (UserInfo.isMultiCurrencyOrganization()){
    			String q='select IsoCode, ConversionRate from CurrencyType where IsActive=true and IsCorporate=true';
    			List<SObject> currencies = Database.query(q);
    			if (currencies.size()>0)
    				corporateCurrencyIsoCode=(String)currencies[0].get('ISOCode');				
    		}
    		return corporateCurrencyIsoCode;
    	}
    }
    
    Challenges

  1. Unit Test Data
  2. In the any-org model the creation of unit test data can be a challenge due to the potential existence of mandatory custom fields and/or validation rules. To mitigate the former, Dynamic Apex can be used to identify mandatory fields and their data type such that test data can be added (via a factory pattern of some sort). In the latter case there is no way to reliably detect a validation rule condition and as such for ISVs it is a blessing that unit tests do not actual have to pass in a subscriber org (wrong as this may be in principle). In the single-org case we can improve on this (and we have to), by adding a global Validation Rule switch-off flag in a Org Behaviour Custom Setting (see previous post) – this approach is helpful in many areas but for unit test data creation it can isolate test code from Validation Rules added post-deployment. There’s a tradeoff here between protecting unit tests versus the risk of using test data that may not adhere to the current set of Validation Rules.

  3. Unit Test Code Coverage
  4. The addition of multiple conditional code paths, i.e. branching, for the any-org case makes it challenging to achieve a high code coverage percentage in orgs which do not have the accommodated features activated. For example, unit tests executing in a single currency org, will not be run code specific to multi-currency, and therefore the code coverage drops accordingly. To mitigate this, consider adding OR conditions to IF branches which include unit test flags and perhaps Test.isRunningTest() to cover as much code as possible before leaving the branch. During coding always strive to absolutely minimise the feature-specific code – this approach will help greatly in respect to unit test coverage.

  5. QA
  6. In the any-org model, it is imperative to test your code in an org with the accommodated features activated. This will require multiple QA orgs and can increase the overall testing overhead considerably. Also, factor in the lead time required to have features activated by Salesforce support, such as multi-currency and Person Accounts.

  7. Security
  8. Dynamic SOQL queries open up the possibility of SOQL-injection attacks where user-supplied text values are concatentated into an executed SOQL query string. Always sanitise and escape data values where such code behaviour is necessary.

  9. Governor Limits
  10. The any-org model is highly contingent upon the use of limited resources such as Apex Describes. As a best practice employ a helper class pattern with cached values.

    One Approach – Future Proofing Single-org Developments

    Optional Features – selective
    Multi-currency – yes
    Editions Support – no
    i18n – yes
    Unit Test Data – yes
    Profile Permissions – yes

    The list above is my default position on the approach to take on single-org developments, this can change significantly depending on the current state of the org in terms of configuration and customisation, plus the client perspective on the evolution of their Salesforce org and attitude toward investing in future-proofing/extensibility. In the single-org, consultancy project case it’s advisable to be completely open and let the client decide if the additional X% cost is worth the value. I think the real point here is that the conversation takes place and the client has the opportunity to make an informed decision.

Salesforce Query Optimisation

Understanding how the Salesforce Query Optimiser (SQO) works is fundamental to delivering performance at the data layer. Good practice in this regard, as part of an optimised physical data model implementation, is key to delivering efficient data access at the application layer.

In short, certain fields are indexed by default, custom indexes can be added via External Id and unique field attributes or by Salesforce support. For composite indexes the latter is required. The SQO utilises an index statistics table which records the distribution of data in each index. When processing a query, a pre-query is made to the statistics table to determine whether a candidate index is useful or not, in terms of selectivity thresholds etc.

The Query & Search Optimization Cheat Sheet provides a very useful reference in terms of the logic applied by the SQO, plus the fields indexed by default. Bookmark as a guide next time you’re writing a complex SOQL query or designing a data model. In the latter case performance of data retrieval is an often overlooked design consideration.

Spanning Relationships Limit

Experienced Salesforce technical architects will always look to declarative solution options before considering technical alternatives. This type of thinking is best practice and indicative of an architect considerate of TCO (total cost of ownership), future maintenance etc.. In my case I’ll go as far as to challenge requirements such that I can deliver a fit for purpose solution using declarative features. In the main this is exactly the right approach – but in some cases there’s more to consider. Let me explain.

Large Data Volumes (LDV) is currently a hot topic within the Salesforce technical community, there’s some great resources and prescriptive guidance available. But aside from the data aspect, what about orgs with large and complex declarative and technical customisations? In some cases such LCOs (Large Customisation Org) have grown organically, in others the org is being used as a platform to deliver non-CRM functionality such as complex portal solutions, ERP etc.. In either case what we’re talking about here is an org with high levels of custom objects, workflow rules, formula fields, Apex script and so on. In such a scenario it is highly likely that the LCO will be constrained by capacity limits (maximum number or users, custom objects, data size etc.) or execution limits (governor limits applied to Apex scripts etc.). In the organic growth case, where an org may have started life in one business division and then expanded across the enterprise, there will certainly come a point where a multi-org strategy becomes the only option, continual refactoring and streamlining to provide additional headroom will eventually cease to be viable. In light of this multiple-org architectures are becoming more commonplace with enterprises partitioning over organisation structure or business process boundaries, enabling localised innovation and growth with some data sharing and consolidation. That said, the transition from the single-org to the multiple-org model is potentially costly and disruptive, as such the design factors to consider, to optimise the longevity of a single-org implementation in the face of organic growth, are key for an architect to understand and implement from the outset. A firm understanding of the applicable limits for the org type and user licensing model is the best starting point for this, combined with the practical experience of where limits are soft, and can be increased by salesforce.com support, and where limits are hard platform constraints. This latter type of limit being most relevant to the goal of optimising org longevity. An example being the Spanning Relationships limit.

Spanning Relationships Limit
This limit constrains the total number of unique object relationships which can be referenced in declarative build elements (workflow rules, validation rules etc.) associated with a single object. This is a significant constraint on larger data models, and typically surfaces first for the central standard objects (Account, Contact, Case etc.). The soft limit here is 10, the hard limit being 15, however there are also performance degradation considerations at anything over the 10 level. When this capacity limit is reached, the only options are to refactor the declarative implementation or revert to Apex script solution options. It is therefore critical to understand that this hard limit exists when designing a data model and also when adding declarative elements which introduce a new relationship traversal. There may be an argument for some level of denormalisation in the physical data model, it’s generally unlikely that a Salesforce data model would be in 3NF anyway, unlike a traditional RDBMS a data model optimised for storage is not always the right approach.

Returning back to my original point, in considering declarative solution options versus technical alternatives, the complexity of the data model, plus capacity limits applied to the declarative build model are also factors. There’s no silver bullet answer.

Salesforce Exception Reports

I think it’s fair to say that consideration of reporting and analytics is not traditionally a technical architect’s direct concern. In the Salesforce context I always challenge this presumption and promote the idea that a project architect should absolutely be looking at the high-level analytical requirements during the foundation stage of the project. Why you may well ask? The answer to this is simple, in order to design a fit-for-purpose data model you must consider data coming out (via the native reporting tools) as well as data going in efficiently. I make this point in a prior post that outlines my view on the Salesforce development process. The number of projects I see where this thinking hasn’t been applied and 3rd party tools are wheeled in to address the gap continues to surprise me. Key to success here is understanding what can be achieved with the Salesforce reporting functionality. Which brings me on to the topic for this post – exception reports, a type of reporting not obviously catered for by the standard reporting tools. For instance – show me a list of Accounts without an Opportunity in the last year. Such reports are invaluable in identifying omissions, focusing sales activities, generating call lists, tracking inactivity etc. etc.

I’ll use 2 examples below to clarify how to approach such a scenario.

Simple Example.
Accounts without a closed Opportunity in the last 30 days.

For this report we use a “without” Cross Filter as per the screenshots. Cross Filters were added in Spring ’12 and enable records to be filtered based on the existence (or non-existence) of related records which meet defined criteria. Within a Cross filter up to 5 sub-filters can be defined. Note, the report’s top level filters do not apply at the sub-filter level. Cross filters can be added for any related object, custom or standard – the limit is 3 per report (each with up to 5 sub-filters). Powerful functionality indeed and a great convenience that avoids a Custom Report Type for the outer join, note the sub-filter is limited to fields on the related object, no-cross object sub-filter logic is possible.

simple exception report

Complex Example.
Accounts without a Won Opportunity involving a certain Product Family in the last 60 days.

For this report we start with a Cross Filter report to satisfy the “without Won Opportunity” requirement. From there we need a indicator (of the existence of an Opportunity Product linked to a product of the specified family) at the Opportunity level which can be used in a sub-filter. This is actually straightforward to do and relies on the old school technique for exception reports, namely the use of a rollup summary fields and simple workflow.

Solution components :
Opportunity Product – Custom Field – ProductFamilyHardwareFlag
Number (18,0 default 0)

Opportunity Product – Workflow Rule – “Opp Product – New or Changed Product”
Evaluate – Created and Edited – Formula (OR(ISNEW(), ISCHANGED( Product2Id )))

Opportunity Product – Workflow Action – “Opp Product – Set ProdFamilyHardwareFlag”
Field to update [ProductFamilyHardwareFlag] with Formula = IF( TEXT(Product2.Family)=’Hardware’, 1,0)

Opportunity – Custom Field – ProductFamilyHardwareCount
Rollup Summary Field (Opportunity Product – SUM – Field to aggregate = ProductFamilyHardwareFlag). All records included.

How does this all work? In short, a workflow rule on Opportunity Product sets a numeric field equal to 1/0 based on the product family meeting the defined criteria. The numeric field is then rolled up to the Opportunity level via RSF, which is then applied in a sub-filter as below.

complex exception report

Note, this is just an example to illustrate the concept. An actual implementation would typically require a more generic approach.

Org Behaviour Custom Setting

As a best practice I implement a hierarchy type Custom Setting (OrgBehaviourSettings__c) which enables a highly useful, org-level selective switching-off of dynamic behaviour such as Triggers. The setting usually has independent flags for workflow rules and validation rules also, with the setting fields being referenced in rule entry criteria and formula expressions respectively. Having such a setting in place (and fully respected across the declarative build and Trigger scripts) from the outset can be really useful for diagnostics and data loading. It may be advantageous in some cases to provide a different set of values for an integration user perhaps – do this with caution..

Example object definition

<?xml version="1.0" encoding="UTF-8"?>
<CustomObject xmlns="http://soap.sforce.com/2006/04/metadata">
    <customSettingsType>Hierarchy</customSettingsType>
    <customSettingsVisibility>Protected</customSettingsVisibility>
    <description>Org-level behaviour settings - enable switching-off of Apex Triggers etc.</description>
    <enableFeeds>false</enableFeeds>
    <fields>
        <fullName>TriggersEnabled__c</fullName>
        <defaultValue>true</defaultValue>
        <description>When set to True, Apex Triggers (coded to respect the setting) will execute - when set to false Apex Triggers exit immediately.</description>
        <externalId>false</externalId>
        <inlineHelpText>When set to True, Apex Triggers (coded to respect the setting) will execute - when set to false Apex Triggers exit immediately.</inlineHelpText>
        <label>TriggersEnabled</label>
        <type>Checkbox</type>
    </fields>
    <label>OrgBehaviourSettings</label>
</CustomObject>

Example trigger code

trigger OnAccount on Account (after update
//				after delete, 
//				after insert, 
//				after undelete, 
//				before delete, 
//				before insert, 
//				before update
				) {							
  OrgBehaviourSettings__c obs = OrgBehaviourSettings__c.getInstance();
  System.debug(LoggingLevel.ERROR, obs);
  if (!obs.TriggersEnabled__c) return;	
									  
  AccountTriggerHandler handler = new AccountTriggerHandler(Trigger.isExecuting, Trigger.size);

//    if (Trigger.isInsert && Trigger.isBefore){
//        handler.onBeforeInsert(Trigger.new);
//    } else if (Trigger.isInsert && Trigger.isAfter){
//        handler.onAfterInsert(Trigger.new, Trigger.newMap);
//    } else if (Trigger.isUpdate && Trigger.isBefore){        
//        handler.onBeforeUpdate(Trigger.new, Trigger.newMap, Trigger.oldMap);
//    } else if (Trigger.isUpdate && Trigger.isAfter){
    	handler.onAfterUpdate(Trigger.new, Trigger.newMap, Trigger.oldMap);
//    } else if (Trigger.isDelete && Trigger.isBefore){
//        handler.onBeforeDelete(Trigger.old, Trigger.oldMap);
//    } else if (Trigger.isDelete && Trigger.isAfter){        
//        handler.onAfterDelete(Trigger.old, Trigger.oldMap);
//    } else if (Trigger.isUnDelete){
//    	handler.onAfterUndelete(Trigger.new, Trigger.newMap);
//    }
}

Don’t forget to populate the setting in test code (example below creates default org-level values).

OrgBehaviourSettings__c obs = OrgBehaviourSettings__c.getInstance( UserInfo.getOrganizationId() );
obs.TriggersEnabled__c = true;	
insert obs;

Salesforce Data Storage

In reading this interesting title (via MEAP) on the trendy topic of Big Data, I was reminded of the many times I’ve had to explain the general principles of the Salesforce data storage model and specifically how many records can be stored per user.

In short each user license gets an allowance, the edition of Salesforce dictates how big the allowance is. A minimum threshold of 1GB applies regardless of users and edition. For illustration, Enterprise Edition (EE) customer with 100 standard user licenses gets 100*20MB=2GB (20MB per-user for EE, UE is 120MB). If the same customer had only 10 users, they would have 1GB, as 10*20MB=200MB is less than the minimum value.

So – how does this equate to physical record storage? The answer to this is straightforward, in general terms each record held consumes 2KB regardless of the number of custom fields, therefore simply divide the allocation by 2KB as below.

2GB = 1,073,741,824*2 = 2,147,483,648 / 2,048 = 1,048,576 = approximately 1 million records

Note – exceptions to the 2KB per-record rule are Article (4KB) Campaign (8KB), CampaignMember (1KB), PersonAccount (4KB) and EmailMessage (variable). Article Types and Tags all consume 2KB for each record. Certain records such as Products, PriceLists, Chatter Posts and Comments etc. do not count. *Update* – Contact Roles and Partner Roles also do not count toward data storage.

This post is not exhaustive, but the maths and noted exceptions should be sufficient to gain an approximation of required versus actual storage. In a world where cloud storage is perceived to be almost a free commodity, the Salesforce model looks restrictive when considered simply in GB terms, particularly in comparison to other cloud services such as iCloud, where 5GB is free.

It always makes sense to implement a fit-for-purpose archiving strategy (off-platform perhaps), balancing business need for data versus storage cost – a further consideration being the performance implications of larger data sets in areas such as reporting and search.