Introduction
Imagine you want to start a business in US, but know nothing about the region where it is more appropriate to start such business, neither you have an idea about
the competitors and the demographic information in the region. Where should you go? - Business Area Explorer is the right place to start!
An application is available at http://businessareaexplorerv2.cloudapp.net/.
Business case
Case 1
A Venture Capital Firm looking to invest into “Green” companies or start-up a “Green” company in a specific region based on demographics information such as minority, veteran, etc.
ownership of related industries. VC needs to be able to filter data based on criteria and find high/low density populated areas based on zip codes,
counties, etc. “Heat map” to depict high/low population density areas could be overlaid on top of the filtered data to pinpoint priority areas for such investments
Case 2
A Healthcare Insurance Company is organizing a marketing campaign to determine high/low population areas to target woman, veteran, minority owned
companies with a specific net worth and company size. Campaign will be used to attract employees of those companies to enroll into private Healthcare
Insurance Exchanges (HIX) available to those employees in 2014 based on Government’s Healthcare Reform Act.
Implementation Story
To cut a long story short, we wanted to create a mashup of company data with the demographic data, to be able to do cross-filtering like: show me the companies with more than 10 employees, more than 1M$ net worth and located in the area where annual capital income growth is larger than 5%.
What is the most important thing for mashup? Technology? No, it's DATA.
Getting DATA
Windows Azure Marketplace provides an easy way to subscribe to different data sets and manage those subscription.
We will need a company data, that can be extracted from the D&B Developer Sandbox feed https://datamarket.azure.com/dataset/dnb/developersandbox .
Also we will use a demographics data, that can be obtained from the ESRI Data feed https://datamarket.azure.com/dataset/esri/keyusdemographicstrial
Using the data market feeds is not much different from a clients point of view. You generate the proxy by data pointing to the URI of the feed like https://api.datamarket.azure.com/DNB/DeveloperSandbox/v1.
To make it work, you need to set credentials for the generated proxy to your LiveID for user and the Primary account key for the password. In .Net it will look like
proxy.Credentials = new NetworkCredential("live id","primary account key");Doing Mashup
Since we want to do cross-filtering, we need to somehow marry those data sets.
D&B data can be joined by the DUNSNumber - an identifier for the company that is generated and maintained by D&B.
To marry D&B company data with ESRI data we should take ZipCode data from the LocationLatLong D&B dataset and match it to the GeographyId field in the demog1 dataset by ESRI.
You fetch all ESRI data where GeographyName is equal to Zip Code, then go one by one and search LocationLatLong companies with ZipCode starting with the value of the GeographyId
Once you've married the data sets, you can filter the data by both demographics and companyh attributes.
Building Application
We wanted our application to be easily accessible, thus we did not go with Win8/Windows Store application but started with "good old" HTML5. Once you do HTML5, then you can't help using jquery and jqgrid.
Want to show things on the map? Google Maps are the mainstream technology here.
ASP.NET MVC4 will also do nicely here.
The last thing - where will we host it? Cloud comes to rescue - we can publish our app into Windows Azure by
just clicking "Publish" inside Visual Studio.
Not more than 2 days passed and our mashup is already live. Nothing pleases more that the work that is just done.
Could you imagine the codeproject article without a line of code?
Let's flesh the article with some code. I would like to show you how we are moving the company data through the application from the D&B backend to HTML5 UI.
Let's magine we have successfully generated a proxy for the D&B feed. Now we will wrap it into our own data access layer.
First we will define a Company class with all the attributes that we are going to use.
public sealed class Company
{
public string DUNSNumber { get; set; }
public string CompanyName { get; set; }
public int NumberOfEmployees { get; set; }
public decimal NetWorth { get; set; }
public string Industry { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public string Country { get; set; }
public string Green { get; set; }
public string WomanOwned { get; set; }
public string VeteranOwned { get; set; }
public string MinorityOwned { get; set; }
public decimal Latitude { get; set; }
public decimal Longtitude { get; set; }
} We will also need to apply filtering, so we need to define a request class with filtering criteria.
public sealed class CompanyRequest
{
public CompanyRequest()
{
MaxEmployees = int.MaxValue;
MaxNetWorth = decimal.MaxValue;
}
public LatLong NE { get; set; }
public LatLong SW { get; set; }
public int MinEmployees { get; set; }
public int MaxEmployees { get; set; }
public decimal MinNetWorth { get; set; }
public decimal MaxNetWorth { get; set; }
public string IndustryCode { get; set; }
public bool Green { get; set; }
public bool WomanOwned { get; set; }
public bool VeteranOwned { get; set; }
public bool MinorityOwned { get; set; }
public List<string> GeoIds { get; set; }
} Also we will need a small LatLong class to represent coordinates.
public sealed class LatLong
{
public decimal Latitude { get; set; }
public decimal Longitude { get; set; }
}Now let's move to the data source implementation. I have removed most of the data query code for brevity.
public class CompanyDataSource
{
public List<Company> GetCompanies(CompanyRequest request)
{
if(request == null)
throw new ArgumentNullException("request");
var companies = FetchCompanies();
companies = ApplyFilters(request, companies);
return companies;
}
protected virtual List<Company> FetchCompanies()
{
DNBDeveloperSandboxContainer proxy = CreateProxy();
DataServiceQuery<LocationLatLong> locations = GetLocationInformation(proxy);
DataServiceQuery<PublicRecords> publicRecords = GetPublicRecords(proxy);
DataServiceQuery<Firmographics> firmographics = GetFirmographics(proxy);
DataServiceQuery<Green> greenCompanies = GetGreenCompanies(proxy);
DataServiceQuery<Veteran> veteranOwnedCompanies = GetVeteranOwnedCompanies(proxy);
DataServiceQuery<Women> womenOwnedCompanies = GetWomenOwnedCompanies(proxy);
DataServiceQuery<Minority> minorityOwnedIndicator = GetMinorityOwnedIndicator(proxy);
var locationOperation =
Task<IEnumerable<LocationLatLong>>.Factory.FromAsync(locations.BeginExecute(null, null),
locations.EndExecute);
var publicRecordOperation =
Task<IEnumerable<PublicRecords>>.Factory.FromAsync(publicRecords.BeginExecute(null, null),
publicRecords.EndExecute);
var firmographicsOperation =
Task<IEnumerable<Firmographics>>.Factory.FromAsync(firmographics.BeginExecute(null, null),
firmographics.EndExecute);
firmographicsOperation.Wait();
var greenCompaniesOperation = Task<IEnumerable<Green>>.Factory.FromAsync(greenCompanies.BeginExecute(null, null),
greenCompanies.EndExecute);
var veteranOwnedOperation =
Task<IEnumerable<Veteran>>.Factory.FromAsync(veteranOwnedCompanies.BeginExecute(null, null),
veteranOwnedCompanies.EndExecute);
var womenOwnedOperation = Task<IEnumerable<Women>>.Factory.FromAsync(womenOwnedCompanies.BeginExecute(null, null),
womenOwnedCompanies.EndExecute);
var minorityOwnedOperation =
Task<IEnumerable<Minority>>.Factory.FromAsync(minorityOwnedIndicator.BeginExecute(null, null),
minorityOwnedIndicator.EndExecute);
Task.WaitAll(new Task[]
{
locationOperation, publicRecordOperation, firmographicsOperation, greenCompaniesOperation,
veteranOwnedOperation, womenOwnedOperation, minorityOwnedOperation
});
List<LocationLatLong> locationsResult = locationOperation.Result.ToList();
List<Company> companies = new List<Company>(locationsResult.Count);
AddLocations(locationsResult, companies);
AddPublicRecords(companies, publicRecordOperation.Result.ToList());
AddFirmographics(companies, firmographicsOperation.Result.ToList());
AddGreen(companies, greenCompaniesOperation.Result.ToList());
AddVeteranOwned(companies, veteranOwnedOperation.Result.ToList());
AddWomenOwned(companies, womenOwnedOperation.Result.ToList());
AddMinorityOwned(companies, minorityOwnedOperation.Result.ToList());
return companies;
}
private void AddPublicRecords(IEnumerable<Company> companies, IEnumerable<PublicRecords> publicRecords)
{
var lookup = publicRecords.ToLookup(m => m.DUNSNumber);
foreach (var company in companies)
{
var publicRecord = lookup[company.DUNSNumber].FirstOrDefault();
if(publicRecord != null)
{
if(String.IsNullOrEmpty(company.CompanyName))
company.CompanyName = publicRecord.Company;
company.Address = publicRecord.Address;
company.City = publicRecord.City;
company.Country = publicRecord.Country;
company.State = publicRecord.StateAbbrv;
company.ZipCode = publicRecord.ZipCode;
company.NetWorth = ParseDecimal(publicRecord.NetWorth);
}
}
}
private void AddLocations(IEnumerable<LocationLatLong> locationsResult, List<Company> companies)
{
foreach (var location in locationsResult)
{
var company = new Company();
company.CompanyName = location.Company;
company.DUNSNumber = location.DUNSNumber;
company.Latitude = ParseDecimal(location.Latitude);
company.Longtitude = ParseDecimal(location.Longitude);
companies.Add(company);
}
}
private DataServiceQuery<PublicRecords> GetPublicRecords(DNBDeveloperSandboxContainer proxy)
{
return (DataServiceQuery<PublicRecords>) proxy.PublicRecords.Select(p => new PublicRecords()
{
Address = p.Address,
City = p.City,
Company = p.Company,
Country = p.Country,
StateAbbrv = p.StateAbbrv,
DUNSNumber = p.DUNSNumber,
ZipCode = p.ZipCode,
NetWorth = p.NetWorth
}).Take(DataSetSize);
}
private static DataServiceQuery<LocationLatLong> GetLocationInformation(DNBDeveloperSandboxContainer proxy)
{
return (DataServiceQuery<LocationLatLong>)proxy.LocationLatLong.Take(10000)
.Select(
m =>
new LocationLatLong() {Company = m.Company,DUNSNumber = m.DUNSNumber, Latitude = m.Latitude, Longitude = m.Longitude }).Take(DataSetSize);
}
private DNBDeveloperSandboxContainer CreateProxy()
{
DNBDeveloperSandboxContainer proxy =
new DNBDeveloperSandboxContainer(
new Uri("<a href="https:
ProxySetup.Setup(proxy);
return proxy;
}
}
}
Here we are asynchronously fetching the data from several D&B feeds, then compose the information into one dataset and then filter it by the criteria we need.
We have a special ProxySetup helper class to push authentication data and compression support to the proxy:
public static class ProxySetup
{
public static void Setup(System.Data.Services.Client.DataServiceContext proxy)
{
if(proxy == null)
throw new ArgumentNullException();
string clientKey = ConfigurationManager.AppSettings["ClientKey"];
if (String.IsNullOrEmpty(clientKey))
throw new InvalidOperationException("ClientKey is empty");
string clientSecret = ConfigurationManager.AppSettings["ClientSecret"];
if (String.IsNullOrEmpty(clientSecret))
throw new InvalidOperationException("ClientSecret is empty");
proxy.Credentials = new NetworkCredential(clientKey,clientSecret);
proxy.MergeOption = MergeOption.NoTracking;
proxy.SendingRequest += SendingRequest;
}
static void SendingRequest(object sender, SendingRequestEventArgs e)
{
e.RequestHeaders.Add("Accept-Encoding", "gzip,deflate");
((HttpWebRequest)e.Request).AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
}
}We would like to cache the data. To achieve this we will create a decorator around the data service. No black magic - an old good ASP.NET cache is used.
public sealed class CachingCompanyDataSource : CompanyDataSource
{
protected override List<Company> FetchCompanies()
{
string key = "COMPANIES";
object companies = GetFromCache(key);
if (companies != null)
return (List<Company>)companies;
var results = base.FetchCompanies();
AddToCache(key,results);
return results;
}
private void AddToCache(string key, object value)
{
HttpContext.Current.Cache.Insert(key, value, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromDays(1));
}
private object GetFromCache(string value)
{
return HttpContext.Current.Cache[value];
}
}Once we have the data service in place, we can work with it in the ASP.NET MVC controller and return the data to client upon the request. Please pay attention that a custom action filter "CompressFilter" is used to compress the json sent to the client.
public class HomeController : Controller
{
private readonly CachingCompanyDataSource _companiesDataSource = new CachingCompanyDataSource();
private readonly ESRIDataService _esriDataService = new ESRIDataService();
[HttpPost]
[CompressFilter]
public ActionResult GetCompanies([ModelBinder(typeof(RequestModelBinder))] CompanyRequest companyRequest, [ModelBinder(typeof(RequestModelBinder))] DemographicsRequest demographicsRequest)
{
var items = _esriDataService.GetStats(demographicsRequest);
List<string> geoIds = items.Select(i => i.GeographyId).Distinct().ToList();
companyRequest.GeoIds = geoIds;
var companies = _companiesDataSource.GetCompanies(companyRequest);
var result = Json(new {Companies=companies, Items=items});
result.MaxJsonLength = int.MaxValue;
return result;
}
}
} Now we should write some javascript to query our data source (some of the functions were removed for brevity):
(function ($, window) {
$(function () {
var urlPublicRecordsTemplate = $('#mapSection').data('public-records-url');
function reloadData(urlTemplate, startLat, endLat, startLon, endLon, onSuccess, onComplete) {
var industry = $('#industry').val();
var minMaxEmployees = getMinMaxEmployees();
var minMaxNetWorth = getMinMaxNetWorth();
var postData = {
startLatitude: startLat,
startLongitude: startLon,
endLatitude: endLat,
endLongitude: endLon,
recordType: getSelectedRecords(),
industryCode: industry,
minEmployees: minMaxEmployees.min,
maxEmployees: minMaxEmployees.max,
minNetWorth: minMaxNetWorth.min,
maxNetWorth: minMaxNetWorth.max,
minPercentOfUnemployment: getNumber('#minPercentOfUnemployment'),
maxPercentOfUnemployment: getNumber('#maxPercentOfUnemployment'),
minPerCapitalIncome: getNumber('#minPerCapitalIncome'),
maxPerCapitalIncome: getNumber('#maxPerCapitalIncome'),
minTotalHouseholds: getNumber('#minTotalHouseholds'),
maxTotalHouseholds: getNumber('#maxTotalHouseholds'),
minAverageHouseholdSize: getNumber('#minAverageHouseholdSize'),
maxAverageHouseholdSize: getNumber('#maxAverageHouseholdSize'),
minMedianHomeValue: getNumber('#minMedianHomeValue'),
maxMedianHomeValue: getNumber('#maxMedianHomeValue'),
minHouseholdGrowth: getNumber('#minHouseholdGrowth'),
maxHouseholdGrowth: getNumber('#maxHouseholdGrowth'),
minPerCapitaIncomeGrowth: getNumber('#minPerCapitaIncomeGrowth'),
maxPerCapitaIncomeGrowth: getNumber('#maxPerCapitaIncomeGrowth')
};
$.post(urlTemplate, postData).
done(onSuccess).
fail(function(e1, e2, e3) {
alert('An error occured during the data loading.');
}).
always(onComplete);
}
function reload(startLat, endLat, startLon, endLon, mapShouldBeReloaded) {
$.screenBlocker.blockScreen();
reloadData(urlPublicRecordsTemplate, startLat, endLat, startLon, endLon,
function (result) {
if (mapShouldBeReloaded) {
loadMap(result.Companies);
}
loadCompaniesGrid(result.Companies);
loadDemographicGrid(result.Items);
},
function() {
$.screenBlocker.unblockScreen();
});
}
});
}(jQuery, window));
I hope you could get something useful from this article. Take care and write code.
Sincerely yours,
Aliaksandr Pedzko.
