Pro SQL Server Relational Database Design and Implementation

My copy of the book “ProSQL Server Relational Database Design and Implementation , 5th Edition” by Louis Davidson with Jessica Moss arrived this week.  I’m excited to work my way through it.

A well designed database will significantly increase the performance of your database and greatly improve your ability to write efficient and effective SQL queries.  From a lean perspective, an effective database design is equivalent to “quality at the source”.  Your ability to work with your data is inextricably intertwined with the design of the database itself.

Improving a car’s performance begins with the very engine that drives it.  The same is true for your database.  At 14 chapters and 791 pages, the content of the book is thorough and logical, covering the latest features and enhancements to SQL Server 2016.

Although many developers are inclined to control database activities from the client side “front end”, greater performance can be achieved on the server side.  SQL Server 2016 is a powerful relational database management system where the greatest benefits are reaped by implementing a well designed relational database.

For more information or to purchase your copy, just click on the link below!

Pro SQL Server Relational Database Design and Implementation – Fifth Edition – Louis Davidson with Jessica Moss, Apress


OEE in Isolation

OEE or Overall Equipment Effectiveness continues to pervade the world of manufacturing as evidenced by the number of OEE templates that are downloaded each and every day from our downloads page.  In this regard, it is interesting to note the number of OEE solutions that operate in complete isolation.

A medium to large sized company typically deploys some form of ERP (Enterprise Resource Planning) system to manage almost all facets of day to day operations.  Ideally, an integrated OEE solution could take advantage of the existing data streams.

The argument against integration is the lack of “granularity” in the current ERP database system.  Some databases simply do not accommodate the detailed reporting of downtime events such as set up, material changes, process faults, or other lost time events.  Another related factor is whether these data elements can be reported in real time.

In reality, the requirements to support OEE data collection were simply not considered in the original database design and, as a result, two independent systems co-exist.  An opportunity exists for software developers to expand the capabilities of their ERP solutions.

At a minimum, it is possible to bridge two or more independent databases to create a single report using a third party software provider such as Crystal Reports.  Unfortunately, true integration requires more than simply generating a report to better manage and improve your enterprise.

This is a topic that warrants further review.  If we’re writing about it, someone is either working on it or has already done it.

Questions, Comments, Future Topics

Your comments and suggestions are appreciated.  Feel free to leave a comment or send us your feedback by e-mail to or  We respect your privacy and will not distribute, sell, or share your contact information to any third parties.  What you send to us stays with us.

Subscribe to our blog and receive notifications of our latest posts and updates.  Simply complete the e-mail subscription in the sidebar.  Thank you for visiting.

Until Next Time – STAY lean!



The Problem with Solutions

The value of a solution is a problem not worth repeating.

The 5 Why process is one of the many tools that serve to help us to state a given problem, identify a root cause, and ultimately derive a solution to resolve the concern.

One of the caveats that potentially undermines the effectiveness of the 5 Why process is repeatability.  The probability of arriving at a given solution is as varied as the participants in the 5 Why process itself.

The scope and description of the problem become the framework on which the solution is formed.  The questions that evolve through the 5 WHY process depend on the skills and expertise of the participants.  An effective means of deriving an effective solution is explained in our post “How to use the 5-Why approach.”

The problem with the solutions that evolve from the 5-Why process is the limited scope from which they were derived.  In other words, the application of a given solution is constrained by the very problem it is intended to solve.

We can’t solve problems by using the same kind of thinking we used when we created them.

~ Albert Einstein

The ideal solution will resolve the immediate problem and will also yield broader “best practices” at the system and process levels to prevent recurrence in other/different applications.

The knowledge and lessons learned throughout the problem-solving process should also cascade into the systems, processes, and methods that affect future successes and outcomes.  A problem solved is not repeated.

TRIZ is another creative problem-solving technique used to view problems and solutions from different perspectives.  TIPS, the Theory of Inventive Problem Solving,  is another acronym that may be used to describe TRIZ.  The following websites offer more insight into TRIZ:

The above sites offer insights into TRIZ and how it can influence our thinking to solve problems from new and different perspectives.  Learn more about “Resolving Contradictions” at

Until Next Time – STAY lean!






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 (  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 or We look forward to hearing from you and thank you for visiting.

Until Next Time – STAY lean!

Versalytics (64x64)