PowerQueryTool

In previous posts I’ve mentioned that I’ll make available a tool that implements the quantitative methodologies I’ve described. This tool makes practical continuous quantitative planning, and execution to meet objectives, enabling agile-mindset management of complex projects.

So here are the links.

You need to download and install two components:

  • Download Files, and descriptive text. Included ReadMe files contain important installation information.
  • The tool is free, licensed under terms of Free Software Foundation Gnu General Public License (GPL).
  • This version of the tool is called PowerQueryTool, and provides a small template Program Management scenario, with focus on use of PowerQuery and its M Function Language, that are included within Microsoft Excel, as the database environment.
  • There is much to say about PowerQueryTool. In the interest of keeping this Blog post short, an abbreviated description follows. For details on the tool, please visit PowerQueryTool at the PowerOpI website.
  • PowerOpI website is at www.softtoyssoftware.com and provides tons of backup material and links. Capabilities of the PowerOpI Framework are described in detail at vbaConnection . Tips and troubleshooting can be found at vbaConnection.htm#troubleshooting .

What’s in PowerOpI Framework PowerQueryTool

The files provided include a small demo project built on the tool elements, illustrating table structures for project material allocation, product structure BOM, project dates, and product element data. The Framework methodology isn’t tied to any particular project; it’s a framework for simplifying and building tables that can be JOINed to represent many expansions and views of an overall effort.

My own efforts have applied this to managing projects for electronic hardware and software, including chip design: semiconductor wafer design, validation, fab, verification and Qual; telecommunications and audio/video compression and streaming hardware and software design, qual and productization; and enterprise class storage hardware and software development, prototyping, system development, and qual. The Framework has evolved in ways to handle these project sizes and complexities; and handled each without encountering limits to size or complexity.

For reference in descriptions below, Framework elements can be used for several configurations. These can be illustrated as Standalone, Data Source, and Client configurations. Standalone contains Data Source, QUERY, and Report elements. These can be split many ways to provide Data Source and Client instances, to support distributed use with centralized maintenance and security; particularly with SQL Server and MS Access.

Key to image showing Objects in PowerOpI Framework

PowerOpIPowerQueryTool_XL.zip Components

ToolSource.xlsm (Reference: ToolSource) This is core functionality of PowerQueryTool. This file is set up as a Standalone configuration, and can also be used as a Data Source for DemoPQClient.xlsm. Walking through what’s in this file:

  • Reports (Green tabs): These are reports driven by QUERYs that JOIN Source Data Tables in the Red tabs. The Reports have been named for the QUERYs that drive them: qPQMProto (project overview), qPQMBuildDates (build dates for Ops), qPQMBuildSpend (simple budget for Finance, and for Ops orders), and qPQPurchReq (for tracking material purchase). Besides data contained, these illustrate use of JOINs by PowerQuery M. qPQExcelUnion illustrates UNION aggregation of several Source Data Tables using PowerQuery M. Most of the work needed in projects I worked on was handled by a handful of Tables, QUERYs and Reports just like these. (Reference: Reports)
  • Source Data Tables (Red tabs): The demo project is represented in several tables, each of which handles a separate aspect of the effort, simplifying representation of the project as a whole. Each illustrates data for its problem representation, and Key structure and construction used for JOINing. Some include local “subqueries” done in Excel. (Reference: Source Data Tables)
    • Allocation equipment to be built and used by organizations shown with quantity, for testing identified, and configured as listed (Reference: Allocation);
    • BOM planning-bill of materials – product build tree structure representing product elements to be built and components needing special management for cost or effort, and configuration of each element, assembly, and revision (Reference: BOM);
    • Dates critical dates with associated keys, for all built and bought elements. These are derived from the table on blue tab qMPP_RollupExcel, imported from an MS Project export-file representing the project GANTT (Reference: Dates);
    • Elements base detail for each product element such as part numbers, supplier, distributor, configuration details (Reference: Elements);
    • CostAdjust and AdjustFactor control calculation of cost progression of prototype elements as prototyping and productization flow through build Phases (Reference: Cost Adjust);
    • PR tracking purchase lifecycle, material marshaling and distribution status (Reference: Purchase Request);
    • Group1, Group2, and Group3 Tables are used in the demonstration of the UNION QUERY (Reference: Union). JOIN and UNION are key functionality for this type of problem.
  • Schedule Import (Blue tab): qMPP_RollupExcel is a Table from importing the file exported from MS Project representing the program GANTT schedule (Reference: Schedule Import).
  • DataSource control (Blue tab): the DataSources tab controls QUERYs and JOINs among local and remote Tables (Reference: Data Source Control). The DataSources tab contains several elements, ordered as follows in the Excel window:
    • tblConnectionConfig (Reference: Connection Config) Controls creation and configuration of connections to Tables in local and external files and databases. In the PowerQueryTool distribution, this controls configuration of Connections used by PowerQuery M QUERYs, defined in tblConnection.
    • Buttons (Reference: Buttons) User control of creation and update of Connections.
    • tblSQL(Reference: SQL Editor) Contains SQL-language QUERYs. PowerQueryTool focuses on M with PowerQuery, so this table is unlikely to be used in PowerQueryTool, although in other configurations SQL can be embedded within an M Query.
    • tblConnection (Reference: M and Connection Strings) Contains M Function-Language QUERY definitions, containing JOIN and COMBINE (UNION) QUERYs used with PowerQuery. (Some references at mstrings and connectionPQeditor).
    • tblLocalizationConfig (Reference: Localization) Contains calculated localization variables for use in QUERYs.
  • Variables (Blue tab): Table of configuration variables to be used (optionally) when constructing QUERYs. (Reference: Data Source Control) The variables enumerated on this Tab are informed of file location et al., can be re-used among QUERYs to leverage relocation of files, re-naming of directories etc. in distribution and maintenance operation.
    • tblLocalizationConfig (Reference: Localization Contains calculated localization variables for use in QUERYs.

ToolClient.xlsm (Reference: ToolClient.xlsm) This file is just like ToolSource.xlsm, but with all Source Data Tables in external ToolSource.xlsm file(s), with QUERYs in ToolClient.xlsm configured to access them.

PowerOpIPowerQueryTool_MPP.zip Components

P0001_Rollup.mpp (Reference: P0001_Rollup.mpp) This is a normal MS Project plan for the Demo project, with added VBA to provide Export capability to an xls file named after it, and an associated external Resource file. Several sub-projects (M0004.mpp, M0005.mpp) are rolled up into P0001_Rollup.mpp, to interconnect dependencies and deliverables among the sub-projects. The exported file P0001_Rollup.xlsx includes the connected sub-projects.

P0001_linked_tables.vsdm (Reference: P0001 VSDM) This is a normal MS Visio image, with added VBA to size and position graphical objects to a Timeline object on the Image illustrating flow of the Demo project.

Implementing Your Project: Updates and Duplication: (Reference: Implementing Your Project)

All a mouthful, I know. Install the tool files, configure them, poke around. Change some data, Refresh DATA via button and see how Reports update to show your change. Add a product Element to the project. Add a Table, add it to a JOIN or create a new JOIN, and drive a Pivot Report with that JOIN. Then try a small project, cloning from the objects in the distributed files. Check out schedule updates rippling through, and Visio Images updating to reflect schedule changes. Increase project size and complexity as you become familiar with the methodology.

Once the tool is set up and your project and product structure are represented in Source Data Tables and JOINed, concentration shifts to managing the project using and updating Table data; and the tool fades into the background: Project and Product data updates frequently, and structure updates occasionally.

Setting up a second project is much more straightforward: your first project is the working path using PowerQueryTool in your file structure, organization structure, and product structure, that you can modify and extend; and also provides a record of Actuals available for estimates going forward. As you develop this infrastructure, and especially as you distribute the expertise, each project gets better than the last..

It’s much more complicated to read and write about these actions, than it is to do them. Remember the PowerOpI website and support links above.


Further Reading: Core Program Quantitative Structure for System Programs

Advice for Program Managers: The Blog Series

1-Program Management Opportunity
Introduces a vision and framework for development of Program Managers and PMO, for Program Management specialization to its environment, and for improved effectiveness and integration of PM with organization
operational management.

2-Program Management Career Path
Describes career path thinking for Program Managers including sourcing, progression, advancement, and connection with organizational management.

3-Program Management Career Skills
Career path thinking for Program Managers including skills and behaviors that develop Program Manager capabilities and leadership and pave the way toward advancement.

4-Program Management Specialization: System Programs Phased Methodology
PM Best Practices and Core Program Structure for Hybrid integrated system programs using Phased HW – Agile SW, mixed-technologies. Full-Program agility via automated plan tools with continuous plan update.

The Series also solicits contributions to this Blog site, to extend coverage of PM Best Practices and Core Program Structure to a broadening set of Specializations.

5-PMO Role
PMO behavior to achieve Program Management effectiveness specialized to its environment managing PM practices in the organization, including PM development and advancement and connection with organizational management.

6-Quantified Agile for Hardware
Program Quantification applied to Phased and Agile methodologies to deal with organizational quantitative requirements.


More Articles by this Author

Three Levels of Program Management
Guiding Principles for Program Management Action, Program Quantification, and Leverage Through Tooling.

Organizing Program Communication
Program Management depends on effective communication. Design Program communication paths for everyone throughout the Program.

Database Platforms for Program Management Logistics
Logistics Tool extended, using SQL Server and MS Access with MS Excel and PowerQuery.

PowerQuery Tool
Logistics Tool using MS Excel Power Query.

Quantitative Management
Tool methodology for agility with continuous plan update: Program BOM, Tie to Dates, Builds, Element data.

Complex Programs: Structure
Structure Program with Parallel Phasing. Describes coordination of EE/ME, FW, Test, Supply/CM, Driver/Kernel, Transport, Management. Scheduling, Integration points, scaffolding, and starting work. Hybrid Program Cross-Domain Coordination of dev frameworks, including Phased and Agile/Scrum where appropriate, via integration points and scaffolding. Software Integration Sequence and Dependency Planning.

Managing Complex Projects
Problem Statement. PM responsibility for Program Management drive throughout an organization, also includes schedule, budget, integration, critical path, logistics.


Link To Free Tools To Manage Schedule, Logistics, And Finance

Author’s softtoyssoftware.com Website with articles on Program Management and Program quantification tooling using Excel, MS Project, and MS Visio with SQL databases PowerQuery, SQL Server, and MS Access. Articles describe how you can use tools available from the website, or develop these capabilities yourself using commonly available tools.

Tools available from this website are free. They can handle small to large programs, limited only by your imagination. Using the included Program Template to envision, organize, plan, and run a large program puts me in mind of unleashing a Roman Legion to a sure outcome. Veni, Vidi, Vici! – Julius Caesar.

PowerOpI Website: www.softtoyssoftware.com
PowerOpI YouTube Channel: https://www.softtoyssoftware.com/dbnet/programmingprojects/YouTube.htm
Heavy Lift Photo licensed from www.shutterstock.com

Share

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top