Database Platforms for Program Management Logistics

Richard Bixler

Summary: I've released several new distributions of PowerOpI Tools, each focusing on a different Database platform: PowerQueryTool, SQLServerTool, and MSAccessTool. PowerQueryTool Demo was previously released. ("PowerOpI" is "Power Operational Intelligence".) Each tool provides a Program Management Logistics demo scenario in Excel Tables and Reports, and provides connections to a database platform (PowerQuery, SQL Server, or MS Access) to JOIN and explode Tables to drive the Reports. Each Tool connects to MS Project for program schedule, and to MS Visio for visualization. The combination increases your technical power and organizational management scope to manage project logistics with an Agile mindset. The methodology and tools can handle further scenarios beyond the included demo project. The www.softtoyssoftware.com website pretty deeply covers the technologies and support for these tools, with detail on this page.

Published originally on svprojectmanagement.

Here's a link to the download page with descriptions. These items are free, and licensed under GPL3.

Background
System development programs and hardware/software development programs are hard. These programs inevitably involve development of both hardware and software at several levels, are cross-functional, and involve partners likely to be remote and international. Long material lead times demand long-term planning for build and integration schedule, materials acquisition, and finance. Several major activities are executed in parallel: product development, supply chain, manufacturing capability, and field sales and support provisioning all for the developed product. Prototype hardware iterations are generally not rework-able to add functionality or to fix bugs - complicating material implications. Even if rework is possible, it's expensive in both money and time. Here's an article on SVProjectManagement outlining characteristics of these programs.

Effective Methods
Nevertheless, program management methods have evolved that optimize development of such system- and hardware/software-development efforts. I put up an article on SVProjectManagement describing these cycles. They could be characterized as parallel to the extreme; using critical path designed, provisioned and managed for optimal execution and for guidance on decisions; visualization of program detail-to-totality; focus on integration points and working-path-first to keep the whole organization working; risk-buying for long-lead parallelism; rapid continuous planning; and focus on alignment on program goals and execution in all dimensions.

Planning and Execution
Planning can be done, at least approximated, for both long-term and short-term; schedule, logistics, finance. It helps a lot to have some history available on prior similar tasks. When you have established a plan, of course events happen, so it's a huge advantage to be able to re-plan "easily" to avoid or mitigate issues, to explore and identify alternate paths. We want to have, at every moment, the "Best-Known Path From Here". A great visualization of this is Google Maps' adaptive routing, shown in the figure. We want to know "what's possible from here" as we encounter congested traffic, late passengers, construction or roadblocks, accidents, toll roads, or even a faster route identified. Let's not leave out unplanned honey-do errands, or changes to the whole plan! I mean, you have to drive from waypoint-to-waypoint, but isn't it great to be in the proper lane through a sequence of turns? Work smart! Seems progress on projects, and on Road Trips, have a lot in common.

JOINed Tables
Many, maybe all, Program Managers I've known manage program planning using one or more complicated spreadsheets. Complicated, because they attempt to represent many aspects using two dimensions. Powerful, but the main limitations are limited "views", extensibility and maintenance. So that method also propagates through cross-functional and distributed organizations, whose adaptations can obscure "the plan" or a shared knowledge of current status, likely outcomes, or even short- and long-term objectives. Coherence and completeness can suffer.

I've found that JOINed Tables normalizing the program into sub-problems, accessible across the organization, maintained by and visible to Core Team members and driving locally-relevant timely exploded reports, creates a better result. Here's an article posted on SVProjectManagement. Using JOINs for computer-execution to do the complicated work to conjugate the combined sub-problems - use, sequencing, logistics, and finance - gives capability for multiple views spanning all aspects.

That enables rapid planning, coordinating tangible aspects of implementation and change management in all dimensions: across the organization, up-and-down the management chain, front-to-back on product structure and task sequences, overview-to-detail, and over time.

Multiple Platforms

The database JOIN operation is what brings this all together. We are now releasing PowerOpI Tools that implement this planning-and-execution methodology, on several database platforms: PowerQuery, SQL Server, and MS Access. Why are multiple platforms useful or important?

First, an organization may have resources and methods already in place that facilitate or prefer a particular platform.

Second, each platform has its own consequences, and preferred fit to specifics of product mechanics and organization structure. Differences among the environments shown in this PowerOpI database platform Comparison Table describe consequences to distribution of clients, to maintenance, and in implementation skills.

A quick summary of platform consequences:

• PowerQuery is native to Excel, requiring no setup. It has amazing connectivity, supporting dozens of databases and file formats. You would use M-Language which is very powerful, but on the downside is not broadly known and is not well documented. (PowerOpI Tools do provide necessary templates and implementation though.) PowerQuery doesn't support stored procedures, so each Client distributed contains full QUERYs that disclose database structure and file structure to all Client users. So security and sustaining are more complicated. Given these, it seems best suited to a tight local management group, with decision support and data mining by individuals. It's not all one-sided - you can use SQL with M to get wonderful extraction and transformation capabilities.
• SQL Server is at the other end of the spectrum. This can run on a shared server, accessed by Clients in different environments (Windows, Macintosh etc.). SQL Server supports Stored Procedures and Linked Servers so QUERY details can be "hidden" behind house QUERYs, somewhat simplifying security and sustaining. SQL Server requires considerable setup (much of this is described on the PowerOpI website). SQL Server has many security hooks. Your implementation would use SQL which is broadly understood and documented. You don't need complex SQL to do the PowerOpI applications; but you do have access to the full capabilities of T-SQL. You can use M together with SQL. SQL Server is well-suited to broader client distribution across a larger, cross-functional group, detailing local operation as well as formal summaries. And again, it's not one-sided - SQL Server Express is free, and you can run it on your desktop in a more tightly-controlled configuration. It may also be convenient to use PowerQuery alongside, to support transformations and file import etc.
• MSAccess is between those endpoints. It's included with many distributions of Microsoft 365 and requires no setup. It uses SQL which is well-understood (complex SQL isn't required for PowerOpI, and core function and references are provided). You can use SQL together with M. Linked-file setup is manual which can be a pain especially with distributed Clients. Access supports stored QUERYs, thereby "hiding" QUERY structure and file structure and simplifying sustaining and security. Database files can be centralized and Clients can be distributed, but Access runs on each Client.
• I don't yet have an implementation using Web tools (Azure, Big Query, Sheets, SmartSheet et al.) - but looking at it.

And last, these platforms are not exclusive of each other. PowerOpI Tools use each of the database platforms principally for import and JOIN functionality. So one dataset can be used by multiple platforms: SQLServerTool for cross-functional coordination of planning and execution in Development, Supply Chain and Manufacturing. PowerQueryTool for deep data mining, analysis, and reporting in Product Mangement and Finance. MSAccessTool for organizations without access to SQL Server or familiarity with PowerQuery.

Agile-Mindset Management
System and hardware/software development projects may not lend themselves to broad use of Agile methodologies. Certainly some parts of a development Program may use those methods, but other parts likely have constraints of logistics or partnerships or in-place methodologies - so system programs tend to combine mixed methodologies.

But the rapid planning, multiple views, distributed access, and visualization made available by these PowerOpI tools just released, facilitates Agile-Mindset flexibility across the Program and across organizations. The result can be alignment of planning and execution across the organization, up-and-down the management chain, and front-to-back product structure and task sequences, over duration of the Program.