Installing DBFit for SQL Server
I’ve been using DBFit on several projects recently, and I’m really pleased with the results. If you’re not familiar with DBFit, it’s a set of Fit fixtures that allows Fitnesse tests to execute directly against a database, without you having to build a separate connector. This post shows you how to get started with DbFit with the least amount of pain.
We practice Test Driven Development at ext.IT and while TDD is a pretty mature discipline in most modern programming languages, it’s still not widely practiced in the database world. We’ve had some success in the past using TSQLUnit, but I prefer DbFit because you can use it to write Unit Tests as well as Acceptance Tests. In addition, Fitnesse allows you to add additional colour in the form of documentation to truly turn your acceptance tests into an executable specification.
One thing that doesn’t (at least to me) appear to be clear from the documentation is that to install DbFit to test a Microsoft SQL Server database, you can simply install Fitnesse, and then install FitSharp. The advantage of doing this, compared to installing DbFit from sourceforge is that you will be working with the latest and greatest versions of Fitnesse and FitSharp. The current instance of DbFit on sourceforge uses a Fitnesse build from 2008.
Here is a high level overview of the installation steps (more detailed information including troubleshooting steps is available at each of the links below):
- If necessary, install Java 6 from here: http://www.java.com/en/download/index.jsp
- Install Fitnesse from here: http://fitnesse.org/FrontPage.FitNesseDevelopment.DownLoad. As it mentions on the downloads page, just download fitnesse.jar, into the folder in which you’d like to install it and type java -jar fitnesse.jar -p 8080 (where 8080 is the port number you’d like to use to run fitnesse from – usually port 80 is taken by other stuff on your machine) at the command line. Fitnesse will unpack and install itself.
- If necessary, install .NET framework 3.5 or 4.0 from here: http://msdn.microsoft.com/en-us/netframework/aa569263
- Install FitSharp from here (choose the correct version for your version of the .NET framework): https://github.com/jediwhale/fitsharp/downloads. To install FitSharp, create a folder under the folder into which you installed Fitnesse (I name mine FitSharp) and unpack the installation files into this folder.
- Once you’re installed and up and running, follow the examples in the DbFit Reference to see how the whole thing works.
- One additional tip is that if you’re following the Hello World example in the DbFit reference, use the following text instead of the one they document in Step 2: Setting Up the Environment. This will correctly point you to the location of your FitSharp installation files and use the Microsoft SQL Server flavour of DbFit:
!define COMMAND_PATTERN {%m -r fitnesse.fitserver.FitServer,"FitSharp\fit.dll" %p}
!define TEST_RUNNER {FitSharp\Runner.exe}
!define PATH_SEPARATOR {;}
!path FitSharp\dbfit.sqlserver.dll
I’ll be posting some further information on using DbFit as an executable specification that includes acceptance tests, for Unit Testing and performance testing, as well as showing you some of the ways we’ve organized our test suites to make them more maintainable and easier to understand. Happy Database Testing!
Data Smells
There has been much discussion in recent years about code smells and using them to drive refactoring. I have heard and read very little, however, about data smells. Data smells are different from code smells and they drive a different process, but I believe that they are equally as important.
Data is like fresh food from the grocery store; much of what you buy has a “use or freeze by” date. Data too has this expiration date and like the fresh food, if this date is ignored, there will be spoilage that results in unpleasant smells. In data’s case, the “use by” date can be quite obvious as in the case of accounting data or more subtle like the useful lifespan of customer purchasing data. The “freeze by” date gives an indication of when the data is no longer useful in daily operations but generally has value as historical information to be used in reporting and analysis. The most insidious data is comprised of the fields, tables and sometimes entire data stores that have been rarely or never used. The bad smells don’t reach us because this data or structure is not being actively used.
Where does this data originate? Sometimes, it comes from the pet projects of long-departed managers or executives that ultimately didn’t serve a useful business function. Others may come from projects that were dropped due to budget or other business constraints and left in a partially-complete state. In my experience, the greatest contributors to stale data are the large, enterprise applications like Onyx CRM / Consona CRM or Oracle Financials that have been implemented in a traditional waterfall fashion. SaaS applications such as Salesforce.com that allow well-intentioned but technically-challenged users to create entire data structures are a close second and gaining rapidly.
In waterfall implementations of large, enterprise-level applications, user groups often pile on requirements in an effort not to miss any possible use case, even those which should be considered to be marginal. Because requirements are gathered upfront and then cast in stone, it’s easy to see why so much marginally-useful or even useless data points are created to satisfy the perceived business need. How often do we review this data once it has been implemented? Monthly, yearly, once a decade? Unfortunately, the likely answer is: never. We have servers to manage, upgrades, updates and patches to test, database code to write and refactor, documentation to maintain, meetings to attend, newer, shinier applications to prepare and the list goes on and on. Meanwhile, columns that contain minimal or no data and tables with stale, useless data take up space and use server resources unnecessarily.
It is incumbent on data professionals, and I include DBAs, data developers and business analysts in this category, to find these issues and to offer solutions.
- Consider re-purposing custom and user-defined data columns to support newer business needs.
- Offer to archive (just in case) those old tables and to eliminate them from the production server.
- Refactor code to eliminate dead-ends and processes that are creating the offending rows. I have seen several cases where there hundreds of thousands of rows of data that is still being created to support a process that no longer exists or is assigned to a user who is long-gone.
We offer CRM “tune-ups” for Onyx CRM, Consona CRM, Oracle Financials, Microsoft Dynamics CRM and Salesforce.com which would include data analysis of this kind. Look for a blog post on this in the future.
In addition, I’ll address the “how-to” side of this issue in a future post. In the meantime, take a look at your biggest data stores. Do you have any columns that have never been populated, have not been populated in more than a year or have a population of less than 5% of the rows? If so, these are candidates for the big cleanup ahead.
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.