Dashboard SDK

As I mentioned in a previous post, my company has added a new software development kit to our dashboard application. I was recently asked to prepare a demo of a gauge for a potential customer. The idea is to show information about a set of inventory picks within a warehouse including total travel distance, and the density of products and locations. The customer can use this to measure how efficient their picking process is.

I've been using Ruby more and more recently so I decided to actually create a first cut of the gauge using our Ruby in our SDK. A screen shot is below, followed by the actual code (disclaimer: I'm fairly new to Ruby and I was purposely verbose to illustrate the details; this could have been done in Groovy, Java, or JavaScript as well). The point is, I seriously doubt a report like this could be easily built using a drag and drop BI tool. The code required some specific knowledge of the data structure, but anyone creating something like this will need that domain knowledge anyway. This only took a few hours and it fully integrates with the framework meaning it can be configured per user, output to Excel or PDF, set up as an Alert, etc.

So more evidence for my earlier assertion that business intelligence requires code.



Code (note: this is running inside a Java framework via JRuby so is accessing the database through Hibernate):

include Java
import 'java.util.ArrayList'
import 'WaveSummary'

# Get the list of Ickpts (inventory runs) either from the parameter ($ickpt)
# or the database if they want all

where = 'where 1 = 1 '
if ($ickpt and $ickpt.length > 0) then
 where += 'and ckpt_id in ('
 $ickpt.each do |i|
   where += i.getName() + ','
 end
 where.chop! # lose the last comma
 where += ') '
else
 # No parameter value; get them all; we could filter by date but some span days
 ickpts = $session.createQuery("from Ickpt").list().toArray()
end

# Build map for easier lookup of ickpt record later
ickptMap = Hash.new
ickpts.each do |i|
 ickptMap[i.getIckptKey().getCkptId()] = i
end

if ($dateRange != nil) then
 where += ' and create_dtim between :from and :to '
end

# First query to get the unique slots
query = $session.createSQLQuery("select ckpt_id, count(distinct sel_loc) from aseld " + where + " group by ckpt_id")
if ($dateRange) then
 query.setDate("from", $dateRange.getDateRange($timeZone).getFrom().getTime())
 query.setDate("to", $dateRange.getDateRange($timeZone).getTo().getTime())
end

summaryMap = Hash.new
query.list().each do |result|
 s = WaveSummary.new
 summaryMap[result[0]] = s
 s.uniqueSlots = result[1]
end

# Next, get the unique skus
query = $session.createSQLQuery("select ckpt_id, count(distinct prod_id) from aseld " + where + " group by ckpt_id")
if ($dateRange) then
 query.setDate("from", $dateRange.getDateRange($timeZone).getFrom().getTime())
 query.setDate("to", $dateRange.getDateRange($timeZone).getTo().getTime())
end

query.list().each do |result|
 s = summaryMap[result[0]]
 if ! s then
   s = WaveSummary.new
   summaryMap[result[0]] = s
 end
 s.uniqueSkus = result[1]
end

# Now query to loop through the picks to calculate the distance
query = $session.createSQLQuery("select ckpt_id, sel_x_coord, sel_y_coord, assg_id from aseld " + where + " order by ckpt_id, sort_seq, sel_loc")
if ($dateRange) then
 query.setDate("from", $dateRange.getDateRange($timeZone).getFrom().getTime())
 query.setDate("to", $dateRange.getDateRange($timeZone).getTo().getTime())
end

lastX = lastY = lastCkpt = lastAssg = 0
query.list().each do |result|
 ckpt = result[0]
 s = summaryMap[ckpt]
 if s == nil then
   s = WaveSummary.new
 summaryMap[result[0]] = s
end
assg = result[3]
if (lastCkpt == 0 or lastCkpt != ckpt) then
lastX = lastY = 0
end

x,y = result[1],result[2]
if lastAssg != 0 and lastAssg == assg then
 if lastX != 0 and lastY != 0 then
   s.distance = 0 if ! s.distance
   # Just using straight-line distance between each sel loc
   # This is not exact, but close enough for comparison
   s.distance += Math.sqrt((x-lastX)**2 + (y-lastY)**2)
 end
end
lastCkpt, lastAssg = ckpt, assg
lastX, lastY = x, y
end

# Dump the map into a list to return
data = ArrayList.new
 summaryMap.each do |ckptId, s|
 s.ickpt = ickptMap[ckptId]
 s.distance = 0 if ! s.distance
 s.uniqueSlots = 0 if ! s.uniqueSlots
 s.uniqueSkus = 0 if ! s.uniqueSkus
 s.distance = s.distance/12 if s.distance > 0
 s.slotDensity = s.distance/s.uniqueSlots if s.uniqueSlots > 0
 s.skuDensity = s.distance/s.uniqueSkus if s.uniqueSkus > 0
 data.add(s)
end

data

SaaS Primer

I've been working on building a new Yard/Dock Management/Appointment Scheduling application (hence the lack of blog entries). Our plan is to offer this application as both a traditional in-house hosted app and an externally hosted SaaS model. In that spirit, here are some SaaS articles that I've been reading:

Architecture Strategies for Catching the Long Tail is a great primer on SaaS and the issues you will face when adopting it.

Top Ten Reasons Why On-Demand Services Will Soar in 2008 - the title is pretty self-explanatory.

On Demand Gaining Traction in Supply Chain (PDF) from Aberdeen Research has some good information about the adoption of SaaS in the suppy chain software space.

On-Demand/SaaS Reality discusses SaaS pricing and its adoption in the enterprise.