SQL Queries For Mere Mortals

Databases are capable of storing incredible volumes of data.  However, their true value is found when we learn to mine and manipulate that data.  Structured Query Language or SQL is the language of databases and is common – with some exceptions – to all of the major databases on the market today.

A custom front end is typical of most database solutions where we are immune from having to work with the raw data directly.  Unfortunately, the front end solutions typically provide “prepared” or “canned” reports with limited capabilities to tailor them.

SQL gives you the ability to unlock your data.  Customized queries can provide new insights into your data that would otherwise never be tapped.  Of the many books available to teach you SQL, SQL Queries For Mere Mortals – Third Edition is one of our top recommendations.

SQL Queries For Mere Mortals – Third Edition, John L. Viescas and Michael J. Hernandez.  760 pages.  ISBN-13:  978-0-321-99247-5 / ISBN-10:  0-321-99247-4.  Addison-Wesley

Tap into the power SQL to get the most out of your relational database.  SQL is not a difficult language to learn and you can become dangerously knowledgable in a matter of a few hours or even minutes.  On the other hand, to use it effectively requires more than a basic understanding of a handful of commands.

Learning how to use SQL to it’s greatest advantage begins with a solid foundation in SQL.  This book walks you through the evolution of SQL before commencing with any serious teaching of the language itself.  Numerous examples demonstrate how to use the skills discussed in the text and all of the examples and databases are available for download from the book’s website.  Five unique databases – Sales Orders, Entertainment Agency, School Scheduling, Bowling League, and Recipes – demonstrate the importance of understanding the underlying schema to formulate effective and efficient queries.


At 710 pages of chapter content alone, this book is for those who want an in depth guide to learn how to write effective SQL queries.  While the pace of the book may seem a little slow through the first 5 or 6 chapters, you will appreciate the foundation they set for the materials presented in chapter 7 and beyond where joins, unions, and subqueries are presented.  Of particular importance is the initial overview of database design.  You will quickly discover that a well designed database is essential for writing effective and efficient queries.
Become the wizard! SQL – you’ll wonder how you ever did without it.  You’ll also discover that SQL is more common than you may be aware of.


SQL and Excel

Microsoft’s SQL Server is the database solution of choice for our company.  However, like many of the database solutions available, additional data mining tools are required to provide the top level reports needed to manage the business itself.

Not surprisingly, one of those tools is Excel 2016.  The correlation between a database table and a spreadsheet is intuitively clear.  While it is easy to conceive how they may be related, connecting the two may be a daunting experience the first time around.

For someone looking to make a quick connection, we recommend visiting the datapig technologies blog (http://datapigtechnologies.com/blog/).  In a matter of minutes, you can establish a connection to your database and dynamically refresh the data on your spreadsheet.

We have also added a link to the datapig technologies blog from our Excel Websites Page.  We trust that you too will find the information presented to be of value to you and your organization.

Until Next Time, STAY lean!




Lean UX – Intriguing

The application of lean  evolved well beyond it’s application in the world of manufacturing.  Certainly, the medical field and hospitals have reaped significant rewards not only to the benefit of their own efficiency and effectiveness but also to the patients who are under their care.

It is a testament to lean practitioners who apply their knowledge of lean to create new paradigms for business and industry around the world.  The book, Lean UX: Applying Lean Principles to Improve User Experience, published by O’REILLY is another example of this.

I have discussed the application of Lean Principles in the context of software development over the past few years and is perhaps one of the reasons that this book caught my attention.  We’ve mentioned many times before that lean principles and practices can be extended to other facets of business beyond the manufacturing floor.  Enhancing or improving the user experience – especially for web applications – is surprisingly specific, yet well developed.

With regard to the design process itself, I can appreciate an iterative design and release process whereby a preliminary release is followed by frequent updates based on user feedback and / or additional features that were reserved for later release cycles.

The release and development of Windows 10 serves as an excellent example of this process.  As a participant in the Windows “Insider Preview Build” Program, I continue to receive the latest Windows 10 builds and provide frequent feedback to Microsoft accordingly.

Lean UX: Applying Lean Principles to Improve User Experience provides an interesting perspective and may just change the way you view web applications in the future.

The KINDLE advantage

Download the free Kindle app to save money and the environment when you purchase the Kindle version of Lean UX: Applying Lean Principles to Improve User Experience.

I have the KINDLE app installed on all of my key devices including my Blackberry, iPhone, iPad, MacBook, and Windows PC’s and a lot of books in my Kindle library. Books are available to you anywhere at anytime as long you have your device with the Kindle app with you.

It’s also easy to move from one device to another without having to remember the last page you read because KINDLE knows the last page read on each device.

Aside from not having try your complete library of books around with you, consider the shelf space you’ll be saving too!

Also consider how easy it is to follow along with an open book on the screen while learning to do something new on your computer.

I highly recommend the Kindle app though I still have a certain appreciation for hardcopy books too.

Until Next Time – STAY lean!

Versalytics (64x64)




Chaos in Paradise …

Have you ever wondered why or how so much time can be wasted performing what should have been a simple task?  I recently encountered a situation that could easily have been avoided if the information I received provided a little more detail including tips and traps to avoid.

I’m currently writing a database application in Python that will solicit data from an SQL database .  The unexpected work “stoppage” occurred as I was testing a database module that I was planning to use.

I continued to be greeted by errors every time I attempted to run a small script written specifically to test the module and it’s various functions.  I followed the step by step instructions as provided in the core documentation and checked, double checked, and triple checked my code.

I determined that I can’t be the only person to ever experience this problem and turned to Google in search of answers.  After a lot of research and reading, I finally found an explanation related to the error I was experiencing and it was one that also made sense:

Files cannot be written to a drive’s root directory.

When I reviewed the instructions again, I realized that writing to the root directory could be a possible source of error.  The instruction and examples I was using to model my code wrote the file to a subdirectory several levels down from the root. I initially decided to write a file to the root directory where I could easily find it as I didn’t want to have to traverse the hierarchy of the drive’s directory structure for a “simple” API test.

Although I thought I was following the directions to the letter – step by step – I actually introduced the potential for error by attempting to write the file to the root directory.

To be clear, the information accompanying the module did not explicitly state that files could not be written to the root directory.  Could it be that the author of the module never experienced the error because they wrote the file to a subdirectory several levels down from the root?  Or, could it be that the author just assumed that we would “know better”

Thankfully, the code executed successfully when I created a subdirectory at least one level down from the root and attempted to write the test file to it accordingly.

Lesson Learned

I personally believe that you can’t be too thorough. I would rather have someone complain that they “get it”, as opposed to not giving enough information and assuming they did.  In this case I lost a lot of time if the presumption was that “I should’ve known”.

No matter how hard we try, it seems that you can’t provide enough information or instruction to prevent “users” or programmers alike from doing the right things in ways we least expected them to.  Assuring that your code will execute without flaw is challenging enough.   Assuring that a programmer follows best practices when implementing the code makes the process even more complicated.

As an aside, one of my pet peeves is the daily barrage of “App Updates” where the primary reason for updating is “Bug fixes and improvements”.

The authors of the module may not have considered all of the “possibilities” that others may attempt for their specific implementation.  If they did discover this anomaly, then it should have been documented and duly noted.

… For Dummies

More often we tend to present the “solution that works” without due attention to the potential sources of error, side effects, and / or consequences of failing to follow the instructions as written.

One of the reasons I recommend and give preference to “… For Dummies” books is the inclusion of the “Warning”, “Tip”, and “Remember” icons used to call attention to details or specific information for the topic at hand.  Even the “Foolish Assumptions” explicitly clarify the pre-requisite knowledge required before reading the book.

Chaos in Paradise – The Garden of Eden

The temptation to defy instructions, regardless of the consequences, prevails throughout history and dates back to the Garden of Eden.  Even then there was chaos in paradise when the notice to evict was given.

In a perfect world, everything works as intended.  Unfortunately, reality gives us cause to consider all of the possible side effects, nuances, traps, and consequences.  For this reason, everything is in a continual state of improvement.

There’s always a better way and more than one solution!

Why this matters

If I knew that writing a test file to the root directory would cause the module to fail, I would’ve changed my code to write it to a subdirectory.  I could have saved myself a few hours of time and my confidence in the solution provided by the module would have remained strong.  The continual errors gave me cause to challenge the choice I made and to consider whether I should look for an alternative solution.

How much time do we lose in our day to day operations because people are deviating from the “working solution” with every intention of saving time or attempting to be more efficient?  The answer is as varied as the number of people involved.

Your feedback matters

If you have any comments, questions, or topics you would like us to address, please feel free to leave your comment in the space below or email us at redge@leanexecution.ca or redge@versalytics.com. We look forward to hearing from you and thank you for visiting.

Until Next Time – STAY lean!

Versalytics (64x64)

Windows Error Code 0X80072F8F

Upgrading to Windows 10

I decided it was time to upgrade my Samsung Premium Ultrabook from Windows 8 to Windows 10.  From what I understand, the Windows 10 upgrade may not be free forever and upgrading to the new operating system was something I could do while I working on other things.

The dead battery suggested that a lot of time had passed since I last used this machine.  As it turns out, this was also warning sign that maybe things wouldn’t go as smoothly as I had originally planned.

When I loaded Internet Explorer, I was immediately greeted with a banner to Upgrade to Windows 10 through Microsoft’s website.  After following the instructions to download the update, I attempted to run the installer.

A message appeared on the screen stating the software “tool” could not be installed due to error code 0x80072F8F.  Error codes don’t bother me as much as they once did because they can easily be searched on the internet.

Internet to the Rescue

I used Google to search for “Error Code:  0X80072F8F” and discovered I wasn’t the only person who had this problem.  The solution simply required me to ensure the date and time were current on my computer.

As my computer’s battery was completely drained, the date and time were set to a date in May of 2009.  Changing the date and time to the current settings was the cure to resolving the error.

The answer and source for this solution appeared in a forum on Microsoft’s website at http://answers.microsoft.com/en-us/windows/forum/windows_7-update/error-code-0x80072f8f/d5006dbe-5946-4d68-8f08-8620eeb65efd?auth=1.

Two methods to resolve the error code are presented.  Method one (1) simply requires you to update the clock to reflect the current date and time.  This method works on Windows 8.1 though it is not specifically mentioned in the context of the solution.

  1. Click Start, and then click Run.
  2. In the Open box, type Timedate.cpl, and then click OK.
  3. On the Date and Time tab, make sure that the computer’s date and time are correct. If the settings are incorrect, adjust them to the correct date and time.
  4. Click OK to close the Date and Time Properties dialog box.
  5. Try to install updates again.

Full credit goes to the author of the solution as it appeared on the website cited above.

How is this Lean?

Sometimes the solution for the problem you are trying to solve already exists.  In this specific case, I would’ve had a very difficult time attempting to resolve the error code without having access to the internet.

Not all problems require exotic “in-house” solutions nor do they require the expertise of an IT staff member to resolve.  The scope of the problem and it’s inherent solution should be understood to ensure the concern is addressed in the most efficient and effective manner possible.

Your feedback matters

If you have any comments, questions, or topics you would like us to address, please feel free to leave your comment in the space below or email us at redge@versalytics.com.  We look forward to hearing from you and thank you for visiting.

Until Next Time – STAY lean!

Versalytics (64x64)


Implementing an ERP (Enterprise Resource Planning) system is a lot of work and happens to be where we’ve been spending most of our time and resources over the past few months.  The OEE reporting spreadsheet we planned to release some time ago is complete and we’ve been using it on some of our core production processes.  

Some have emailed, asking when the spreadsheet will be published.  We plan to make this spreadsheet available once we are satisfied that it serves the purpose we originally intended.  The tools we provide here are offered free of charge and the urgency of releasing them is offset by revenue driven opportunities that in turn support our site and make these “free” resources possible.

Since we are implementing a new ERP system, we also have the opportunity to create and generate customized OEE reports from data already being collected as part of the normal production reporting process.  As the lead for implementation, I am in the unique position of tailoring the data collection and reporting requirements in kind.  This of course is one of the few tasks of many currently under way.

As you may expect, the ERP system runs from a highly structured and more sophisticated database than that of a simple spreadsheet.  As mentioned in many past posts, a commitment to OEE will eventually require a database for more efficient and effective data processing.  Having the opportunity to incorporate OEE from current organic data is less disruptive and represents a significant step forward to real time OEE reporting.

Crystal Reports

We could generate database queries from Excel to further evolve the development of our spreadsheet, however, Crystal Reports integrates nicely with our database front end and the reports are available to everyone on the network.  In other words, the necessity for a spreadsheet is superseded by the need for customized reporting using Crystal Reports.  We can also use Crystal Reports with Excel and many other data sources.

Crystal Reports is a de facto standard in industry and a license is relatively inexpensive considering the powerful capabilities it brings to your data.  A mere mention here in this post does little to expose the merits of using Crystal Reports and we recommend that a little research of your own is warranted.

Your feedback matters

If you have any comments, questions, or topics you would like us to address, please feel free to leave your comment in the space below or email us at feedback@leanexecution.ca or feedback@versalytics.com.  We look forward to hearing from you and thank you for visiting.

Until Next Time – STAY lean

Versalytics >> Analytics

Happy Anniversary

Happy Anniversary

It’s hard to believe that today marks our 7th anniversary.  I still remember writing that first post and wondering who would be interested in what we had to offer.

After more than 293,000 views, thousands of free downloads, and visitors from more than 120 countries, we can say that we’ve successfully helped more than a few people and companies get started with their OEE training and implementation.

We would like to thank all of our subscribers and visitors for your feedback, support, and many “thank you” notes over the years.

Your feedback matters

If you have any comments, questions, or topics you would like us to address, please feel free to leave your comment in the space below or email us at feedback@leanexecution.ca or feedback@versalytics.com.  We look forward to hearing from you and thank you for visiting.

Until Next Time – STAY lean

Versalytics >> Analytics