MEET FEATURE REQUIREMENTS, SCHEDULE, and BUDGET! Consulting available to teach your organization to apply this methodology and tools for powerful quantitative management of your business processes.
- Instruction of your staff at your site, using courseware, application example, and a functional Framework.
- Mentoring your key staff on an ongoing or periodic basis, on these tools or on Quantitative Program Management.
- Contracting or Employment in your organization on specific topics.
Check out my YouTube Channel: Power Operational Intelligence
Now Live! Overview, Structure, Task Data, Table Design, SQL Server, and Re-Linking now showing.
Video courses covering material on this website, and more, are presented in the playlists.
Code snippet links at YouTube Code Snippets. Twitter at @poweroperation1, #poweropi, #poweroperationalintelligence.
Subscribe on YouTube, and click the "Notification" Bell icon to be notified as content is published.
SupportUpdatesPowerQueryTool, MSAccessTool, SQLServerTool, SQLServerRemote, OpISQLServerSecurity
2020-08-01 Data Hub?2020-08-01 (For future elaboration and discussion) Given the great connectivity of Power Query, and the connectivity and data transformation capabilities of M, one could make a nice functional data hub, as if SQL Server supported M. It would need facility for M Stored Procedures that could be EXEC'd from an external Client (structure analogous to a SQL SP). And it would need to accept an M Query embedded in a Connection, to be executed by M in such a Data Hub (structure equivalent to QUERY in connnection to SQL Server)... We can embed M in a connection now, but it executes on the Client. I want to centralize sustaining, and (in some cases) hide data structure from Client access; I delegate SQL to the server now, but can we delegate M execution to a server that provides M execution on data passing through it? 2020-08-14 You can come fairly close to such a Data Hub as follows: Let's say Source Data exists in Tables in one or more Source Data Workbooks or other real database environments, and QUERYs are contained in a Control Workbook which implements an eumulated Power Query Data Hub. (And note that for Power Query or MS Access, the Source Data and QUERYs can be in the same workbook whereas for SQL Server the Source Data Tables must be in workbooks SEPARATE from QUERYs using them.) And of course, the Control Workbook/Power Query emulated Data Hub can use the full connectivity of Power Query to connect to dozens of other data source types. Power Query doesn't support Stored Procedures so we emulate that function. In the Control Workbook/Emulated Data Hub, we create Power Query QUERYs to emulate Stored Procedures, exercising the connectivity of Power Query (and it can contain other QUERY structures as well, within their own connectivity constraints). The QUERYs can be M or SQL with Power QUery, each with its own advantages and constraints. Each QUERY to be used to emulate a Stored Procedure in the Data Hub should Load To... a Structured Table. You can name each Table with a convenient name. Each such Structured Table will be directly visible for connection to a Pivot Table/Chart or Report from a Power Query client; or to make it visible to an Excel client using a Data Connection to the Data Hub you can embed the Shadow Range event handler VBA in the code page for the Worksheet Tab that each such Table is on. These QUERYs driving such tables used to emulate a Stored Procedure in this emulated Data Hub should be set up with BackgroundQuery=TRUE manually in the QUERY's Connection Object, or in tblConnectionConfig, with a convenient RefreshPeriod, perhaps 5 minutes but dependent on expected load. The net of these steps provides Tables on the Power Query emulated Data Hub that emulate Stored Procedures; centralizing QUERY design, configuration and sustaining, and hiding data structure and location from distributed clients. It also delegates QUERY execution to the emulated Data Hub, where Power Query can even further delegate execution to its connected data sources per its inherent functionality. Each client Report Workbook then uses Power Query queries, or Excel Data Connections, to connect to these Data Hub Tables as External Data Sources to drive Pivot Tables/Charts or other report elements in the Report Workbooks. The client Report Workbooks cannot actually refresh the emulated Stored Procedure as they could a real Stored Procedure in SQL Server or a stored Query in MS Access, but the Background Query in the emulated Data Hub keeps its Tables reasonably up-to-date. Finally, the client Report Workbooks can also source Power QUERYs directed to use whatever Tables the Data Hub makes visible, using M for Selection, Transformation and JOINs. There is no facility to delegate such a QUERY to the Data Hub, but Power Query will execute M-based QUERYs on the client. We're emulating a Power Query Data Hub but that's not exclusive of the other usual execution configurations. So the client can execute SQL QUERYs on itself if it contains an MS Access engine; and can execute SQL QUERYs on the Data Hub if the Hub server computer hosts SQL Server. Net: this configuration emulates behavior described, for a Power Query Data Hub, utilizing the fantastic connectivity of Power Query and providing reasonable satisfaction of centralized support and security, and function delegation to emulate a Data Hub that would otherwise be based on SQL Server or MS Access each of which has its own constraints. There could well be other ideas or products that perform more polished behavior. There could be unacceptable constraints in the design above. Let me know if you see that somewhere. In the meantime, this description gets a lot of the desired function. Please contact me if you have pointers. 2020-08-01 Data DNS?2020-08-01 (For future elaboration and discussion) Another fantasy, a little farther out: a "Data DNS". Provide a way to provide remote and universal access to databases and tables within databases (with security of course), analogous to network ability to get to/from any node/server/client that makes itself available on the IP network. Generally, create constructs for data access that are analogous to network constructs, in order to provide broader access to data. Provide symbolic access not tied to physical or IP addressing. The Internet virtualizes "location" via IP and #Port addressing, via DNS, IP switches and Routers propagating address translation and ultimately traffic, through the network. An analogous concept for data location could provide symbolic data addressing to data entities across the IP network (within security constraints specified of course). Further example, extend "network analogy" to data partitioning analogous to VLAN ("VDATA"?) to support secure partitioned private data access for corporate/focused-access data, etc. Maybe this exists already but I'm still looking for it - please contact me if you have pointers. |
