Sunday, August 14, 2016

Running a Small Business on APEX

LAN Gaming Center
I haven't blogged in a while as the last year of my life has been a roller coaster ride of 60-80 hour work weeks with barely enough time to remember to eat.  In the last year I successfully built and launched a small business - a LAN Gaming Center (pictured above), where people can visit to play the latest video games in a social setting.  We're approaching our 1-year anniversary and I'm happy to say still alive and kicking, working to grow both our in-store and digital offerings.

I'm still employed as a systems engineer during the day, and running a side business while holding down a day job is an incredibly difficult endeavor.  I'm lucky to have a great team of people in place that care for and give attention to the business on a daily basis, but I still can on any given day find myself doing a range of activities including accounting, HR, technical support, management, inventory, or any of a number of other things.  To accomplish this I've relied heavily on caffeine and a variety of software solutions - Gusto for example has made payroll a 5 minute effort, and Slack has allowed my team to communicate from anywhere.  This software is irreplaceable from my perspective, but still there are many customized needs for the business.  I could find cloud solutions for each of these at varying prices but - wait a minute - I'm an Oracle APEX Developer!  I CAN DO ANYTHING!

Fact: spandex increases your ability to code.
With affordable APEX hosting readily available and the ability to create web-based data-driven dashboards with just a few clicks, I've been able to create a variety of customized dashboards for my business to help manage the shop and track our growth.  These dashboards are available to me anywhere I can access the Internet, be it a coffee shop at lunch or at home in the evening, helping me to in effect work from anywhere.  Here are just a few of the custom tailored solutions I've been able to create using Oracle Application Express:

Employee Scheduling

The calendar in APEX 5.0 had a number of improvements including drag & drop rescheduling, daily, weekly & monthly views.  This made for a simple scheduling application for letting employees know what hours they need to work.  A form & report allows employees to add/subtract hours, as well as a summary report for me to quickly pull hours worked for when running payroll.

Bare bones scheduling & payroll totals

Tournaments - Gathering Data

Back in November the local GameStop enlisted us to run a 80+ person Call of Duty tournament to celebrate what is their yearly blockbuster release.  We utilize a website called Challonge to execute complex tournament brackets, but registering people for the tournament offered us a unique opportunity to gather data - game interest, contact information - that could be useful to us for future business.  Using the built in APEX 5 Mobile theme and my personal iPad I was able to quickly get a web form up and running allowing us to register people as they arrived.

Due to poor Internet on site, we set up an ad-hoc LAN at the very last minute to save this tournament.
We also run weekly Smash Brothers for Wii U tournaments, a game our team all enjoys spectating and playing.  I had been entering tournament placings for some weeks into an APEX form as a way to track seasonal points when some customers inquired if we could "seed" the tournament based on a ranking system.  With a bit of work I had a single query to aggregate all available data, apply some weights and come up with a ranking for each individual player.  So now when we register players they are automatically seeded based on their prior results.

Copying & pasting the list of entrants into the Challonge website meets our basic needs, and if I had more time to devote to it I could utilize the Challonge API to automatically pull in tournament results, generate brackets from the push of a button, and even control brackets from within a customized APEX page.

Quests - Unlocking Creative Potential

Our quests system came out of the idea that we should reward some of our more regular and/or skilled customers.  Each quest is an individual challenge for a given game that rewards points, which can earn a customer milestones in the store (for example, we give a free BAWLS energy drink at the 250 point milestone, a free night pass at 750 points, and so on).  APEX allowed me to design the interface for my employees to create & manage quests, and a front-end to let our users view quests and milestones directly on our website in real time.  By giving employees the ability to create and manage quests it unlocked their creative potential, and now we have 325 unique in-store quests available to our customers.
Just a few of our many quests.

Sales - Driving Forward

This page has been a major focus for me and not a day goes by that I don't utilize these reports to track our daily, weekly, monthly & quarterly growth.

Short, medium & long-term moving averages
I've also started to introduce new non-monetary growth metrics to the dashboard such as blog, twitch & social media traffic as we transition our business model from a brick & mortar shop to a hybrid digital business.  These are all areas where with more time I should be able to integrate with other APIs for an automated growth dashboard.


Oracle APEX is a great option for the technically savvy small business owner if you're looking to build your own customized dashboards or applications for your company.  With just a little SQL & Oracle experience you can create some fast, flexible & robust applications perfectly tailored to your business needs. I'm constantly adding to and improving on mine, and currently working on a maintenance matrix that will allow employees to easily track common maintenance tasks that need doing (patching, cleaning, stocking).  This will help keep us more organized as a team and ensure small tasks don't fall through the cracks or compound into larger problems.

Thursday, July 9, 2015

Heavy Metal - APEX as a Benchmarking Tool

China's 10-story 80,000 ton press, one of the largest known in the world

We had just released two new APEX apps when our director called a meeting to decide what from the long list of application requests we should pick next.  I had written on the whiteboard our high level list of projects - a hodgepodge of napkin ideas and individual wish lists for how we could better manufacture metal products.  Our director asked the group what they thought should come next.  Most heads turned silently towards Phil, our forging specialist who was shifting uncomfortably in his seat.

I had heard a few things.  I knew there was a planned outage upcoming at our forging facility with a 12 million dollar price tag.  I also knew the equipment in question - the press - was critical to our manufacturing process.  It had been running overtime for weeks in order to build up a back inventory for the planned outage.  I knew there was a tight schedule in which the press was expected to be back in operating condition, and any delay in hitting our date would be detrimental to the company.

Phil let out a sigh, and began to speak about the qualification process he was in the midst of.  As part of the upgrade we were required to develop a benchmark to show a before & after of press operation for our customer's approval.  Because our critical process data happened in under a second, this meant sampling data at a high rate (30 points per second).  Due to our forging process being complex and multi-stepped, this meant thousands of samples were required both before & after the upgrade.  This all totaled to around 10 million individual data points which needed to be collected & cross-analyzed.

Our company had done similar benchmarking for small upgrades in the past that had taken months to correlate.  We needed the analysis completed in days this time.  Due to the scope of the upgrade being a major overhaul, a large amount of work would be placed on hold until the data analysis was completed and qualification approved.  An extended qualification process would mean money lost by the company, temporary layoffs across the board, and a lot of upset folks all around.  If we could reduce the time needed to crunch the data, we could accelerate the entire process and save everyone a lot of pain and suffering.

Also different this time - one of our customers was requesting a more advanced statistical analysis of the data set.  Six Sigma has gained popularity in the manufacturing world, and customers are increasingly looking for control charts, histograms, and other visual representations of data in relation to some established limits.  As we had already developed this charting capability internally, the hope was we could leverage Oracle, PL/SQL & APEX to store, analyze & display forging data in an easy to use dashboard.

"We need it ready in three months.  Is it possible?"

Following the unwritten APEX developer's code of never turning down a project, no matter how crazy it may sound, I nodded my agreement.  Later that afternoon I sat down with my co-op, a fresh college graduate who had been hired nine months ago to learn Oracle/APEX development exclusively, and I started to explain the project.  His eyes grew wider with each new detail, and when I told him the timeline his face went white.  Did I really believe we could meet that deadline, he asked?  To be honest I wasn't sure myself, but have found it best to lead through bold confidence and blind determination, so I sat up tall in my chair and channeled my best Obama: "Yes, yes we can!"

Our first chunks of data arrived that week.  As we had no existing data collection system in place, we relied on an analog module to capture raw signals directly off the press and store these in CSV files, which were then manually imported to Oracle tables via SQL Developer.  This was all raw data with only a timestamp, and while our forging records gave us rough start & end timestamps, unfortunately these times were unreliable and/or inconsistent.

Enter APEX!  We had anticipated using APEX for just the finished dashboard, but we found by plotting the raw data to line charts we could easily spot start & end points over time.  With a few small modifications we were able to create our own tool for viewing & labelling raw data!

The process was laborious but eventually we were able to group our data points and tag each with forging process & unique material IDs.  Our next challenge was in labeling individual "strokes".  Each time the press applied force we would see a spike in tonnage.  Our customers were interested in the slope and peak of these strokes.  However there were tens of thousands of individual strokes, which made labelling these individually an impossible task.
If we were going to hit our release date we would have to find a way to automatically detect and label each stroke.  Our first attempts were brute forcing through the data set point by point with PL/SQL, with loops and stored values to detect an increasing force value, but we quickly found this to be slow and inefficient.  Our next attempt involved some clever usage of SQL CASE statements and analytical functions, namely AVG and STD_DEV, with PARTITION BY to group our data sets, and using the PRECEDING and FOLLOWING windowing clauses to analyze lagging/leading points.

I've always been amazed at how effortlessly the Oracle database handles analyzing large data sets.  A well-written query can crunch through millions of points in a matter of seconds.  It took a bit of tinkering but eventually we had a 70-line SQL query that was capable of identifying the start & end of each stroke with > 99% accuracy.  We wrapped this in a PL/SQL cursor and with a single function call that was capable of crunching through and updating 10 million+ rows in a matter of minutes.

Eventually we had our APEX labeling tools polished enough where we felt comfortable, and with a whole month to spare!  The color was starting to return to my co-ops face, but this was no time to relax - we had a  dashboard to create!  We could easily draw a multi-line series chart with the standard APEX install, but found to join this with a spline area chart would require the advanced commercial license for AnyChart.  We were able to quickly obtain a license via the Anychart website, however documentation was somewhat limited, and my DBAs were very unhappy about having to replace core APEX installation files with new ones.

Dramatization - no monitors were harmed during Anychart install 

Once the upgrade was complete we realized there was no declarative support for the new chart types.  While they were all now technically available to use in APEX, the only way to get these to display was to pick a "vanilla" AnyChart type that was a close match, switch to XML mode and adjust the syntax as needed.  We were already accustomed to working in XML mode so it wasn't a huge hurdle, but having declarative support for AnyChart upgrades would have made the process much smoother.

After some messing with the XML and a bit of web help we were able to generate a chart like the below using some sample "after" data.  The blue spline area region represents our "before" benchmark, with the lines representing individual strokes post-upgrade.

Pretty neat!  And with dynamic PL/SQL queries we were able to adjust the chart data series on the fly to match whatever filters were selected on the current page.  For our more advanced statistics we plotted the same data on a normalized histogram (column chart) with CpK, CpU and CpL values calculated at chart rendering

We finished just in time for the press to come back online, and spent the better part of the next week barricaded in a conference room using our custom created APEX tools to label raw data as it became available.  Our dashboard was live within 24 hours of the first qualification material forging, and allowed our team insight into the operating condition of the press compared to our benchmark.  The dashboard was eventually handed off to Phil, who was able to turnaround a customer approval that was expected to take months within just two weeks.

This is just one example of how we're applying Oracle and APEX to assist with the manufacturing process.  Next week I'll dive into our melting furnaces and how we've leveraged APEX to keep a real-time dashboard of furnace health.

Tuesday, June 30, 2015

How APEX changed my life

I had the good fortune of being in attendance for Joel Kallman's APEX keynote at the ODTUG Kscope15 conference this year.  Joel's talk was titled "APEX Episode 5: A New Frontier", but could have just as easily been titled "Breaking Bad: The Joel Kallman Story" or "How I Learned to Stop Worrying and Start Smashing Skulls".  In his talk Joel dove into his transformation beginning late last year and explained the formation of the Twitter hashtag #LetsWreckThisTogether.  Joel put the call out for APEX developers everywhere to start tooting their own horns via blogging and Twitter.

I'll provide some background on myself and how I got started with Oracle Application Express over eight years ago in 2007.  Back then I was in my mid twenties and on the rebound from a wild two year ride of semi-professional poker.  I had blown off a very promising career as a DoD cleared software engineer (Java) to spend some time in what you might call self discovery - traveling, writing, and spending hours playing Texas Hold'em online and in brick and mortar casinos.  I was barely getting by grinding small stakes games when I suffered a personal loss that sent me into a bit of a downward spiral.  The events in my personal life had a profound effect on my poker game, and a small profit quickly turned into big losses.  It reached the point where I was not only broke but in debt with friends and family.

When I finally threw in the towel on my poker career and tried to find legitimate work I discovered how shaky references and unexplained gaps in work history can really hinder a guy's career.  In my own mind I was a brilliant, misunderstood software engineer, but in reality I was a completely unproven talent with no real work to show.  After many rejections and at risk of losing my car - the last asset I owned - I swallowed my pride and took a job building point of sale machines on an assembly line.  It was blue collar work, standing for eight hours a day as a foreman breathed down my neck.  I remember feeling the calluses formed on my delicate geek hands after the first week of work and thinking to myself - what a waste!

However it felt great to be working again and earning a legitimate living, and slowly starting to re-pay the good folks who had helped bail me out of my mess.  I was four months into the job when I found myself sitting across from a curious HR representative, who was eyeing my resume and wondering what a guy with my background in computer science was doing working an assembly line.  A slew of contract openings had just come up that needed to be filled fast, and I happened to be a warm body with a security clearance and the word "Oracle" somewhere on my resume.  The few bridges I hadn't burned managed to pull some strings for me, and I found myself shipped across town and introduced to Danielle, an Oracle DBA who was looking for an Oracle developer but instead got me.

She set me to work cutting my teeth on PL/SQL, and within a few weeks I was able to generate a simple report page built on an Oracle database table.  It was then that Danielle first mentioned APEX to me.  The way she described it I groaned - it sounded like an IDE tool similar to NetBeans, which is a decent tool nowadays, but when I had used in 2004 to develop Java applets the tool was a complete train wreck.  Why would I want to use another care bear tool to write garbled code for me when I can just code it all myself?  Danielle insisted I give it a chance, and so I logged into a workspace, created my first application, and generated my first report.

I was hooked.  What had taken me weeks to create with raw PL/SQL was accomplished in a half hour using APEX.  And it was better.  Faster, better looking, with sortable reports accomplished just by clicking the column headers, and easily customizable colors.  Everything just worked.  I dove in head first and explored the various item types, regions, DML processing, authorization schemes, themes.  I went from creating small applications which were laughably bad, to better applications that began to catch on with users, to reviving enterprise-wide databases through the use of APEX.

After five years of government work I made the jump to the private sector where I'm currently employed with a fortune 500 company.  I was hired in a technical position completely outside of the IT infrastructure (a Java shop) and given free rein to create, and we're absolutely crushing it.  Statistical process control, approval management, customer dashboards, reporting, charting, emailing.  We're making the Oracle database sing, and doing so in a fast-paced, agile setting.

If you're reading this you're probably already an APEX developer and I'm preaching to the choir, but if by chance you've never tried it I encourage you to drop what you're doing and go get a free workspace at to take it for a spin!  It's a simple, beautiful, insanely powerful tool that will accelerate the value of any Oracle installation.  For me personally it has sparked a passion that was maybe otherwise missing in my career and led to advancement and recognition within the workplace.

So what can you expect from this blog?  During the open mic night at Kscope15 there were many excellent applications displayed, but with the exception of a couple demos (shoutouts to Jayson Haynes and Ed Jones), applications didn't really stray too far from the core APEX forms & charts.  We have plenty of those types of applications as well, but we've also developed what I feel are some unique applications for APEX as applied to our manufacturing process - melting, forging and inspection of specialty alloys.  My future posts will showcase the more unique ways we're using APEX and what it has helped our company to achieve.