Heilmeier’s Catechism – a checklist for software projects
Until recently, I am ashamed to say that I had never heard of George Harry Heilmeier. A recent retweet by Roy Osherove on Twitter soon had me digging for more information.
It turns out that not only was Mr. Heilmeier a pioneering contributor to liquid crystal displays, he was a Vice President (and later CTO) of Texas Instruments during the time they produced the mighty Speak and Spell.
Mr Heilmeier’s Wikipedia page lists an amazing amount of awards, including the National Medal of Science and the IEEE Medal of Honor, but that’s not what sparked my curiousity.
What was interesting to me about Mr. Heilmeier was a series of questions anyone should be able to answer when proposing a research project or product development effort. These questions are known as Heilmeier’s Catechism.
Here is Heilmeier’s original list of questions:
Heilmeier’s Catechism
- What are you trying to do? Articulate your objectives using absolutely no jargon.
- How is it done today, and what are the limits of current practice?
- What’s new in your approach and why do you think it will be successful?
- Who cares?
- If you’re successful, what difference will it make?
- What are the risks and the payoffs?
- How much will it cost?
- How long will it take?
- What are the midterm and final “exams” to check for success?
When I read this list, it struck me that these questions could easily be adapted as a software project checklist.
With some small tweaks in language, this list becomes a standard project checklist that any consulting organization should work on with their customers to answer when deciding whether or not to go ahead with a project:
Project Checklist
- What is the underlying business problem we are trying to solve with this project?
- What happens today? Is this problem worked around with manual processes?
- What’s new in this approach and why do we think it will be successful?
- Who are the project stakeholders?
- If we’re successful, what difference will it make?
- What are the risks and the payoffs? How can the risks be mitigated?
- How much will it cost?
- How long will it take?
- How will we measure progress on the project? How do we know we’ve been successful?
What about your organization’s project approval process? Does your company use Heilmeier’s Catechism to decide whether to give a project a green light? What other questions should be asked before starting a project?
SOQL Group By – Grouping records in Salesforce.com
Overview
One of the most sought after features in SOQL (Salesforce.com Object Query Language) has been the inclusion of record grouping. While Aggregate functions such as Min, Max, Count, and Sum have been available, they have not been available to retrieve grouped Aggregate results. For example, if you wanted to retrieve the Count and Sum(Amount) of Opportunity Records grouped by an Account, you have to retrieve all of the Opportunities and create a loop to increment Counts and Sum based on the Account associated with the Opportunity. Ugly!
With the introduction of the Salesforce Platform Winter 2010 release and version 18 of the API, grouping and a few other functions are available. In this post I will concentrate on the “GROUP BY” clause. In future posts, I will examine other clauses such as:
- GROUP BY ROLLUP ()
- GROUPING ()
- GROUP BY CUBE ()
- HAVING ()
EXAMPLE
In this example, I want to showcase the different types of Grouping and Aggregate Functions that can be combined to come up with some interesting results. I want to create a Visual Force Page bound to a Custom Controller, where I can display the results of the SOQL statement.
First, let’s start with the Controller code. I wanted to bind the results of the query directly to a apex:dataTable, so I created an object to hold the Grouping values and Aggregated results. Below is the object:
public class GroupedQuery
{
public string GroupBy1 {get;set;}
public string GroupBy2 {get;set;}
public string GroupBy3 {get;set;}
public integer Count {get;set;}
public Decimal Sum {get;set;}
public Decimal Average {get;set;}
public Decimal Min {get;set;}
public Decimal Max {get;set;}
}
In addition, in order to bind an object to the dataTable within the Visual Force page, you need to expose a public member from your Controller code. For brevity sake, I just added a public List<> member like this:
public List<GroupedQuery> queryResults {get;set;}
Next in the Controller Constructor (keep in mind this is for demo purposes!) we use a SOQL query to retrieve a List of generic sObjects:
List<sObject> queries = [select Account.Name AccountName, Count(Name) CountResult
from Opportunity group by Account.Name];
Once we have the Results we iterate through a loop to create our collection of GroupedQuery objects:
queryResults = new List<GroupedQuery>();
for (sObject query: queries)
{
GroupedQuery myObject = new GroupedQuery();
myObject.Count = Integer.valueOf(query.get('CountResult'));
myObject.GroupBy1 = String.valueOf(query.get('AccountName'));
queryResults.Add(myObject);
}
The variable queryResults is exposed as a public variable from the controller and thus we can tie this List directly to a dataTable and access the public properties of the GroupedQuery object contained in the List. Below is the Visual Force page code:
<apex:dataTable id="queryResults" value="{!queryResults}"
var="queryResultsRow" border="1" width="100%">
<apex:column >
<apex:facet name="header">GroupBy1</apex:facet>
{!queryResultsRow.GroupBy1}
</apex:column>
<apex:column >
<apex:facet name="header">Count</apex:facet>
{!queryResultsRow.Count}
</apex:column>
</apex:dataTable>
The code above will output the following results (the Account data is data from the Developer Edition of Salesforce.com)
A more involved example is included below. This includes two levels of grouping, as well as Min, Max, Sum and Average functions:
Summary
As demonstrated, the new functionality released with the Salesforce.com API Version 18 with regards to Grouping is extremely powerful and addresses a pain point that many developers have had to overcome to achieve Rollup type queries. This information is contained within the What’s New notes for Salesforce.com Version 18. Keep in mind this is included with the Winter 2010 release of the Salesforce.com platform, so you will need to ensure that your organization has been upgraded in order to avail of this functionality.
Are you tired of working with consulting companies who give you what you ask for, but not what you need? If you want to partner with us to extend your IT capabilities and produce real results, contact us
{
public string GroupBy1 {get;set;}
public string GroupBy2 {get;set;}
public string GroupBy3 {get;set;}
public integer Count {get;set;}
public Decimal Sum {get;set;}
public Decimal Average {get;set;}
public Decimal Min {get;set;}
public Decimal Max {get;set;}
}
Matching Salesforce Data to Back Office Systems
Overview
Often when working with Salesforce Account and Contact records, you want to see if there’s a match with existing SQL Server data in back office systems. While there are several commercial solutions that perform data matching on the Force.com platform (just search the App Exchange for deduplication or lead management to see what’s out there), as well as SSIS fuzzy lookups on the Microsoft SQL Server platform, often you want a quick and free solution that compares data between Salesforce.com and SQL Server data.
To do this, you simply need to add a custom formula field in Salesforce.com and a similar query in your SQL Server data. You can tweak this formula to match different fields depending upon the demographic information contained in the back office system. I will demonstrate this with the Account object in Salesforce.com. In this example, I am assuming that the company name, street, city and postcode will be stored in the back office system.
Duplicate Check field
The field simply concatenates parts of the customer’s name and address together, converts it to upper case, and pads any empty spaces with a character not likely to appear in the name or address fields. I chose the asterisk “*” for this example.
| Name |
Street | City | Post Code | Duplicate Check Code |
| *************************** | ||||
| FELLS WARGO HOME EQUITY | FELLS W******************** | |||
| CATAMARAN EXPRESS COMPANY | SAN BERNADINO | 91911 | CATAMA E**********SAN B91911 | |
| GUMFORT CARE DENTURE CENTER | 900 HOCKSVILLE RD | MASSAPEQUA | 91758 | GUMFOR C900 HOCKSVMASSA91758 |
| ORLANDO/GREEN COUNTY CAB | 1234 LAKE ROAD | ORLANDO | 12319-5273 | ORLAND C1234 LAKE ORLAN12319 |
You’ll notice that this works best when the fields you choose to populate the check code are required, or at least generally populated in both systems. If you choose fields that are often left blank, you can turn up more false positives for matches, which defeats the purpose of the duplicate check.
Salesforce.com Custom Formula Field
To implement this in Salesforce, you need to add a custom formula field to the Account object in Salesforce.com:
- Login to Salesforce.com as an account with administrator privileges.
- Click the ‘Setup’ link at the top of the screen:
- On the left hand App Setup menu, click Customize, Accounts, Fields:
- In the Account Custom Fields and Relationships section, click New.
- Choose Formula as the field type:
- Enter a label and a name for the field. Choose Text as the output type of the formula field:
- Enter the matchcode formula into the Advanced Formula text area:
- Setup the field level security and add the field to the appropriate page layouts, and Save the field.
- That’s it! The field will now be available via the API, Apex code and can be exposed through data exports with the Apex Data Loader.
I’ve reproduced the code from the screenshot in a copyable format below to save you from RSI
UPPER (
MID(TRIM( NULLVALUE(Name, '') )& '******' , 1, 6)
& MID(TRIM(NULLVALUE(Name, '')) & '*' , FIND(' ', TRIM(NULLVALUE(Name, ''))
& '*'),1)
& MID(TRIM(NULLVALUE(Name, '')) & '***' , FIND(' ', MID(TRIM(NULLVALUE(Name,
'')) & ' ',
FIND(' ',TRIM(NULLVALUE(Name,'')) & ' ') + 1,59)) +
FIND(' ', TRIM(NULLVALUE(Name,'')) & ' ') + 1,1)
& MID(TRIM( NULLVALUE(BillingStreet, '')) &'**********' , 1, 10)
& MID(TRIM( NULLVALUE(BillingCity, '')) & '*****' , 1, 5)
& MID(TRIM( NULLVALUE(BillingPostalCode, '')) & '*****' , 1, 5)
)
SQL Custom Query
Although adding the custom field to Salesforce alone has some value (for example, you could use it in custom code to detect duplicates), the real value is when you can match up the values to your back office system. I’m assuming your back office server is running SQL Server 2005 or higher for this T-SQL example, but most forms of SQL have equivalent functions that can manipulate strings, so check your documentation if you are using a different flavour of SQL, and please post your custom formula in the comments section to help out others.
This query could be added to a view, stored procedure or custom field in SQL.
For this example I’m assuming that the fields are called ‘Name’, ‘StreetName’, ‘City’, and ‘ZipCode’, and are stored in a table called CompanyInfo.
SELECT UPPER (
SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '******' , 1, 6) +
SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '*' , CHARINDEX(' ', LTRIM(RTRIM(ISNULL
(Name, ''))) + '*'),1)
+ SUBSTRING(LTRIM(RTRIM(ISNULL(Name, ''))) + '***' , CHARINDEX(' ', CHARINDEX(LTRIM
(RTRIM(ISNULL(Name,''))) + ' ',
CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,59)) +
CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,1)
+ SUBSTRING(LTRIM(RTRIM(ISNULL(StreetName, ''))) + '**********' , 1, 10)
+ SUBSTRING(LTRIM(RTRIM(ISNULL(City, ''))) + '*****' , 1, 5)
+ SUBSTRING(LTRIM(RTRIM(ISNULL(ZipCode, ''))) + '*****' , 1, 5)
) AS 'DuplicateCheck'
FROM CompanyInfo
Using your duplicate check code
Once the check is in place in both systems, you can use it in several ways. You can match the fields directly, you could create a matching “confidence” score by using character by character comparisons or you could use more complex fuzzy lookup functions to further match the data.
Summary
Now that you have a single field to compare Salesforce and SQL data, you can tweak the values to find better matching algorithms, and use these fields as part of any duplicate checking process for data integration.
New look and feel
We’ve just given the website a facelift and are now using WordPress to generate our content.
We’re pleased with the results – please take a look around and let us know what you think by adding comments below.
Bridging the Gap
As with almost everyone who’s pursing a professional degree in academia, the anxiety resulting from the anticipation of entering the professional workforce is usually only dwarfed by–probably the fear of death. You always hear the stories of the companies that are a total nightmare to work for. You always see the gross depictions of the professional working environment on the television. It skews your expectations and intensifies your anxiety towards the inevitable.
My personal situation, which is probably not THAT unique, is that I didn’t have any experience in a professional setting throughout the course of my academic career. Sure, there were odd jobs here and there that didn’t really amount to much but provide a minimum wage now and then, but that was pretty much the extent of my work history. Don’t get me wrong, those experiences serve its own purpose for personal growth, but it doesn’t really contribute towards gaining experience in a true professional environment. There is nothing to really prepare someone for this transition besides actually experiencing it. There are, however, a few discernible aspects of the work environment that I’ve noticed, and that I feel as though directly contributes to my productivity and quality of my work produced:
- A collaborative team environment vs a management-employee relationship.
- This allows me, personally, to feel as though I’m contributing and working on a project (the perspective of essentially “owning” my own portions), and makes it not feel as though it is just another assigned task which I must complete as per rigid criteria after rigid criteria (the perspective of just doing work for someone else).
- Flexible work schedule.
- I chose to work from about 9a.m. to about 4p.m., and an hour or so anytime after that at home (as long as it constitutes an 8 hour day of work). There are also some days when I am able to work remotely, which provides even more flexibility. It’s a proven fact that some of the best ideas and solutions to problems were derived while in a state of relaxation, or “non-thinking.” When you don’t have to worry about clocking in and clocking out, it allows for more time to concentrate on what you’re doing, when you need to.
- Incremental exposure to new platforms and applications.
- It allows for deeper understanding and reduced stress from having to cram so much in a short period of time. Understanding of the technology and concepts fuel creative solutions rather than code regurgitation as a result of memorization.
So how much of a difference is there between academia and the work environment? Based on the three bullet points above, absolutely nothing. Any respectable college will emphasize collaboration with your peers, and the course schedule that you choose (to a certain degree) is structured as a few hours of instruction that incrementally exposes you to new concepts, and the rest is up to you to finish the work. Now, that’s not to say that some companies may function differently for their own purposes, but the similarities that I’ve found have made it helpful for me to transition.