- \n
- Download <\/a>Files, and descriptive text. Included ReadMe files contain important installation information.<\/li>\n\n\n\n
- The tool is free<\/strong>, licensed under terms of Free Software Foundation Gnu General Public License (GPL).<\/li>\n\n\n\n
- This version of the tool is called PowerQueryTool<\/strong>, 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. <\/li>\n\n\n\n
- 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 <\/a>at the PowerOpI website.<\/strong><\/li>\n\n\n\n
- PowerOpI website is at www.softtoyssoftware.com<\/a> and provides tons of backup material and links. Capabilities of the PowerOpI Framework are described in detail at vbaConnection <\/a>. Tips and troubleshooting can be found at vbaConnection.htm#troubleshooting<\/a> .<\/li>\n<\/ul>\n\n\n\n
What’s in PowerOpI Framework PowerQueryTool<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
![\"\"](\"https:\/\/svapm.org\/wp-content\/uploads\/2023\/08\/FrameworkBase.jpg\")
<\/figure>\n\n\n\n![\"Key](\"https:\/\/svapm.org\/wp-content\/uploads\/2023\/08\/FrameworkKey.jpg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/svapm.org\/wp-content\/uploads\/2023\/08\/FrameworkDeploy.jpg\")
<\/figure>\n\n\n\nPowerOpIPowerQueryTool_XL.zip Components<\/h2>\n\n\n\n
ToolSource.xlsm<\/strong> (Reference: ToolSource<\/a>) 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<\/span>. Walking through what’s in this file:<\/p>\n\n\n\n
- \n
- Reports <\/strong>(Green <\/span>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 <\/span>(project overview), qPQMBuildDates <\/span>(build dates for Ops), qPQMBuildSpend <\/span>(simple budget for Finance, and for Ops orders), and qPQPurchReq <\/span>(for tracking material purchase). Besides data contained, these illustrate use of JOINs by PowerQuery M. qPQExcelUnion <\/span>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<\/a>)<\/li>\n\n\n\n
- Source Data Tables <\/strong>(Red <\/span>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 <\/span>structure and construction used for JOINing. Some include local “subqueries” done in Excel. (Reference: Source Data Tables<\/a>)\n
- \n
- Allocation <\/strong>equipment to be built and used by organizations shown with quantity, for testing identified, and configured as listed (Reference: Allocation<\/a>);<\/li>\n\n\n\n
- BOM <\/strong>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<\/a>);<\/li>\n\n\n\n
- Dates <\/strong>critical dates with associated keys, for all built and bought elements. These are derived from the table on blue tab qMPP_RollupExcel<\/span>, imported from an MS Project export-file representing the project GANTT (Reference: Dates<\/a>);<\/li>\n\n\n\n
- Elements<\/strong> base detail for each product element such as part numbers, supplier, distributor, configuration details (Reference: Elements<\/a>);<\/li>\n\n\n\n
- CostAdjust <\/strong>and AdjustFactor <\/strong>control calculation of cost progression of prototype elements as prototyping and productization flow through build Phases (Reference: Cost Adjust<\/a>);<\/li>\n\n\n\n
- PR <\/strong>tracking purchase lifecycle, material marshaling and distribution status (Reference: Purchase Request<\/a>);<\/li>\n\n\n\n
- Group1<\/strong>, Group2<\/strong>, and Group3 <\/strong>Tables are used in the demonstration of the UNION QUERY (Reference: Union<\/a>). JOIN <\/span>and UNION <\/span>are key functionality for this type of problem.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Schedule Import <\/strong>(Blue <\/span>tab): qMPP_RollupExcel <\/span>is a Table from importing the file exported from MS Project representing the program GANTT schedule (Reference: Schedule Import<\/a>).<\/li>\n\n\n\n
- DataSource control<\/strong> (Blue <\/span>tab): the DataSources <\/span>tab controls QUERYs and JOINs among local and remote Tables (Reference: Data Source Control<\/a>). The DataSources <\/span>tab contains several elements, ordered as follows in the Excel window:\n
- \n
- tblConnectionConfig<\/strong> (Reference: Connection Config<\/a>) 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<\/span>.<\/li>\n\n\n\n
- Buttons<\/strong> (Reference: Buttons<\/a>) User control of creation and update of Connections.<\/li>\n\n\n\n
- tblSQL<\/strong>(Reference: SQL Editor<\/a>) 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.<\/li>\n\n\n\n
- tblConnection<\/strong> (Reference: M and Connection Strings<\/a>) Contains M Function-Language QUERY definitions, containing JOIN and COMBINE (UNION) QUERYs used with PowerQuery. (Some references at mstrings<\/a> and connectionPQeditor<\/a>).<\/li>\n\n\n\n
- tblLocalizationConfig<\/strong> (Reference: Localization<\/a>) Contains calculated localization variables <\/span>for use in QUERYs.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Variables<\/strong> (Blue<\/span> tab): Table of configuration variables to be used (optionally) when constructing QUERY<\/span>s. (Reference: Data Source Control<\/a>) The variables enumerated on this Tab are informed of file location et al., can be re-used among QUERY<\/span>s to leverage relocation of files, re-naming of directories etc. in distribution and maintenance operation.\n
- \n
- tblLocalizationConfig<\/strong> (Reference: Localization<\/a> Contains calculated localization variables <\/span>for use in QUERYs.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
ToolClient.xlsm <\/strong>(Reference: ToolClient.xlsm<\/a>) This file is just like ToolSource.xlsm<\/span>, but with all Source Data Tables in external ToolSource.xlsm <\/span>file(s), with QUERYs in ToolClient.xlsm <\/span>configured to access them.<\/p>\n\n\n\n
PowerOpIPowerQueryTool_MPP.zip Components<\/h2>\n\n\n\n
P0001_Rollup.mpp <\/strong>(Reference: P0001_Rollup.mpp<\/a>) 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.<\/p>\n\n\n\n
P0001_linked_tables.vsdm<\/strong> (Reference: P0001 VSDM<\/a>) 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. <\/p>\n\n\n\n
Implementing Your Project: Updates and Duplication: (Reference: Implementing Your Project<\/a>)<\/h2>\n\n\n\n
All a mouthful, I know. Install <\/span>the tool files, configure <\/span>them, poke around. Change <\/span>some data, Refresh DATA <\/span>via button and see how Reports update to show your change. Add a product Element <\/span>to the project. Add a Table<\/span>, add it to a JOIN <\/span>or create a new JOIN, and drive a Pivot Report <\/span>with that JOIN. Then try a small project<\/span>, cloning from the objects in the distributed files. Check out schedule updates <\/span>rippling through, and Visio Images <\/span>updating to reflect schedule changes. Increase project size and complexity as you become familiar with the methodology.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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..<\/p>\n\n\n\n
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.<\/p>\n\n\n
\nFurther Reading: Core Program Quantitative Structure for System Programs<\/strong><\/h2>\n
\n\nAdvice for Program Managers: The Blog Series<\/h3>\n<\/div>\n
\n1-Program Management Opportunity<\/strong><\/a>
\nIntroduces 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
\noperational management.<\/p>\n2-Program Management Career Path<\/strong><\/a>
\nDescribes career path thinking for Program Managers including sourcing, progression, advancement, and connection with organizational management.<\/p>\n3-Program Management Career Skills<\/strong><\/a>
\nCareer path thinking for Program Managers including skills and behaviors that develop Program Manager capabilities and leadership and pave the way toward advancement.<\/p>\n4-Program Management Specialization: System Programs Phased Methodology<\/strong><\/a>
\nPM Best Practices and Core Program Structure for Hybrid integrated system programs using Phased HW \u2013 Agile SW, mixed-technologies. Full-Program agility via automated plan tools with continuous plan update.<\/p>\nThe 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.<\/p>\n
5-PMO Role<\/strong><\/a>
\nPMO 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.<\/p>\n6-Quantified Agile for Hardware<\/strong><\/a>
\nProgram Quantification applied to Phased and Agile methodologies to deal with organizational quantitative requirements.<\/p>\n<\/div>\n\n
\nMore Articles by this Author<\/h3>\n<\/div>\n
\nThree Levels of Program Management<\/strong><\/a>
\nGuiding Principles for Program Management Action, Program Quantification, and Leverage Through Tooling.<\/p>\n\nOrganizing Program Communication<\/strong><\/a>
\nProgram Management depends on effective communication. Design Program communication paths for everyone throughout the Program.<\/p>\n\nDatabase Platforms for Program Management Logistics<\/strong><\/a>
\nLogistics Tool extended, using SQL Server and MS Access with MS Excel and PowerQuery.<\/p>\n\nPowerQuery Tool<\/strong><\/a>
\nLogistics Tool using MS Excel Power Query.<\/p>\n\nQuantitative Management<\/strong><\/a>
\nTool methodology for agility with continuous plan update: Program BOM, Tie to Dates, Builds, Element data.<\/p>\n\nComplex Programs: Structure<\/strong><\/a>
\nStructure 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.<\/p>\n\nManaging Complex Projects<\/strong><\/a>
\nProblem Statement. PM responsibility for Program Management drive throughout an organization, also includes schedule, budget, integration, critical path, logistics.<\/p>\n\nProgram Roadmap<\/strong><\/a>
\nPlanning work structure for a Program, and using the plan effectively.<\/p>\n\nLifestyle AI<\/strong><\/a>
\nAI effects on current and future lifestyles. Personal strategies for proactive and defensive use of AI.<\/p>\n\nIterative Thinking<\/strong><\/a>
\nUsing Iterative Thinking to solve big problems and complex logistics.<\/p>\n<\/div>\n\n
\nLink To Free Tools To Manage Schedule, Logistics, And Finance<\/h3>\n<\/div>\n
\nAuthor’s softtoyssoftware.com Website<\/strong><\/a> 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.<\/p>\n
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! \u2013 Julius Caesar<\/em>.<\/p>\n
- \n
- https:\/\/www.softtoyssoftware.com\/dbnet\/programmingprojects\/powerquerytool.htm<\/a><\/li>\n
- Details on design of Structured Tables, JOINs, Reports\/Pivots in Tools.<\/li>\n
- Schedule, Visualization, Reporting.<\/li>\n
- Hybrid program agility with continuous plan update.<\/li>\n
- Microsoft 365 Desktop – based.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n\n\n
PowerOpI Website: www.softtoyssoftware.com<\/a>
PowerOpI YouTube Channel: https:\/\/www.softtoyssoftware.com\/dbnet\/programmingprojects\/YouTube.htm<\/a>
Image(s) used under license from Shutterstock.com<\/a>. Attribution: Elena11<\/a> / Shutterstock.com<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"In previous posts I’ve mentioned that I’ll make available a tool that implements the quantitative methodologies I’ve described. This tool […]<\/p>\n","protected":false},"author":1607,"featured_media":16231,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2,849,850],"tags":[1504],"class_list":["post-16230","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-leadership","category-team-leadership","category-organizational-leadership","tag-1504"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/svapm.org\/wp-content\/uploads\/2020\/11\/SVPM_rmb20201112-HeavyLift.jpg","_links":{"self":[{"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/posts\/16230","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/users\/1607"}],"replies":[{"embeddable":true,"href":"https:\/\/svapm.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16230"}],"version-history":[{"count":111,"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/posts\/16230\/revisions"}],"predecessor-version":[{"id":28609,"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/posts\/16230\/revisions\/28609"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/svapm.org\/index.php?rest_route=\/wp\/v2\/media\/16231"}],"wp:attachment":[{"href":"https:\/\/svapm.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/svapm.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/svapm.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- https:\/\/www.softtoyssoftware.com\/dbnet\/programmingprojects\/powerquerytool.htm<\/a><\/li>\n
- Buttons<\/strong> (Reference: Buttons<\/a>) User control of creation and update of Connections.<\/li>\n\n\n\n
- BOM <\/strong>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<\/a>);<\/li>\n\n\n\n
- Source Data Tables <\/strong>(Red <\/span>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 <\/span>structure and construction used for JOINing. Some include local “subqueries” done in Excel. (Reference: Source Data Tables<\/a>)\n
- The tool is free<\/strong>, licensed under terms of Free Software Foundation Gnu General Public License (GPL).<\/li>\n\n\n\n