dbnet.home

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.

Brochure  ...  Contact for Details

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.

SupportUpdates

PowerQueryTool, MSAccessTool, SQLServerTool, SQLServerRemote, OpISQLServerSecurity, MySQLTool

2024/11/21 v3.8.2

Bugfixes

  • [OpISQLServerSecurity].[ToolSource.xlsm] only: Corrected field name [OpISQLServerSecurity].[Toolsource.xlsm].[tblOpIRolesGRANT].[grFixedRole] from incorrect [grFIxedRole] (capital letter I). This field is informational-only and is not used in any PowerOpI included Queries or Reports. The updated field name will persist into all subsequent releases.
  • If you have created custom Queries or Reports that include table [OpISQLServerSecurity].[Toolsource.xlsm].[tblOpIRolesGRANT] you should update your Query/Report accordingly.
2024/10/04 v3.8.1

Bugfixes

  • ToolSource.xlsm or ToolManagement.xlsm, all configurations: Corrected SQL syntax using SQL @variable to define Database Name in multiple instances in the that #Directive.
  • ToolSource.xlsm and ToolManagement.xlsm, all configurations: Corrected syntax using global range name in Module1.runDirective() which executes from the DataSources.#Directives CommandButton to create #Directives.
  • TablePush.CommandButton_PushTables_Click(): In Push Script for SQL Server and MySQL, fixed BIGINT value conversion in script, to Excel LONG numerical representation.
  • Fixed several configuration variables and handling thereof, in configurations for SQL Server. Pushed changes across all install configurations (PowerQuery, MySQL, MS Access, multiple SQL Server configurations etc.). In the included PowerOpI template, re-named Excel table source tblPR to avoid a naming syntax conflict in a particular configuration, to tblPurchReq. This table automatically instantiates as rngPR and xlPR within Excel and SQL Server. Changed those to rngPurchReq and xlPurchReq across all configuration instances. Pushed related Excel VBA code changes across all configurations, to maintain code commonality.
2024/07/29 v3.8

Updated PowerOpI Excel PUSH functionality GUI.
2024/07/16 v3.7

Updated PowerOpI Excel PUSH functionality to create SQL scripts compatible with MySQL (and therefore probably with MariaDB), to CREATE TABLEs in MySQL, INSERT VALUES into MySQL tables from PowerOpI Excel source tables, and DROP TABLEs from MySQL.

  • Enables use of MySQL database in PowerOpI configurations.
  • Supports creation and use of VIEWs in MySQL for secure use of MySQL with PowerOpI Excel infrastructure.
  • Updated functionality is propagated through all released configurations of PowerOpI ToolSource.xlsm files.
2024/06/14 v3.6

Use of PUSH functionality to enable remote security and table-data management function for SQL Server used in OpI configurations. Update security definition tables in ToolSource.xlsm, execute the PUSH button function, select database OpISQLServerSecurityDB in SSMS, and from SSMS run the resulting CREATE, INSERT VALUES or DROP script to manage the table values pushed to OpISQLServerSecurityDB.

  • Updated OpISQLServerSecurity\ToolSource.xlsm to push its tables to SQL Server, local or remote, into an OpISQLServerSecurityDB database, having appropriate security to enable security updates from a remote ToolSource (over Local LAN, or WAN).
  • A set of SQL scripts is provided for OpISQLServerSecurity in new directory OpISQLServerSecurityTables, to be used by SSMS to push Tables to database OpISQLServerSecurityDB from OpISQLServerSecurity ToolSource.xlsm.
  • Tables are updated by the security manager in the ToolSource.xlsm and pushed from there to the OpISQLServerSecurityDB database. Then, a project database, remote or local, can be selected in SSMS, and the scripts available in OpISQLServerSecurityTables are executed from SSMS to accomplish directives to manage Logins, Users, Roles, and Schemas for the selected server and project database using the tables pushed to OpISQLServerSecurityDB.
  • Alternatively, the set of SQL Scripts released in v3.4 and available in directory OpISQLServerSecurity\OpISQLServerSecurity, when a remote project database is selected in SSMS, use table data from the OpISQLServer Security\ToolSource.xlsm which must be hosted locally on the server hosting OpISQLServerSecurityDB, to control security settings in the remote database.
  • This new structure adds capability for secure management of security in PowerOpI project databases, from a separate, local or remote, management computer.
  • Propagated updated PUSH functionality to all ToolSource.xlsm configurations, although PUSH structure is currently functional only for SQL Server. This is done to keep the ToolSource.xlsm functional version common across all configurations, to allow for later updates to those platforms.
  • Small bugfixes/enhancements to all ToolSource, Management, Client, and ClientRemote configrations for SQL Server, PowerQuery, Access, and MySQL/MariaDB.
  • SSMS button on ToolSources.xlsm DataSources tab now selects SSMS v20. Directory and therefore version are controlled on Variables tab by Variable named SSMS_Path. If SSMS is installed in its default directory, the version initiated by the button may be controlled by replacing the version number (e.g., 20) in the path string.
2024/04/09 v3.5

Incremental updates, bugfixes, and documentation to all PowerOpI tools, in areas of
2023/08/11 v3.4

As appropriate to target Database system:

  • Release of OpISQLServerSecurity Tool and support in Framework files (e.g. ToolSource.xlsm) supporting management of Server Roles, Server Logins, Database Roles, Schema, and Users in appropriate target Database systems. Notes here.
  • Source data push through ToolSource.xlsm files to SQL Server Tables in remote database. Notes here.
  • Support for ODBC access to remote SQL Server and other foreign web databases such as MySQL. Notes here.
2022/11/30 v3.3
  • All Listed Products: Documentation only. Updated Copyright Notice on all products to reflect current dates, and added accommodation for OpISQLServerSecurity Tool supporting management of Server Roles, Server Logins, Database Roles, and Database Users -- Upcoming(!!!).
  • SQLServerTool and SQLServerRemote: Minor updates to prepare for use with OpISQLServerSecurity Tool.
  • SQLServerTool and SQLServerRemote: Dropped (unnecessary) inline call within QUERYs repeating set configuration of OLEDB Provider.
2022/09/11 v3.1, v3.2
  • 3.1: Documentation only. In InstallSetup.rtf, indicated "Quit MS Access" before building connections in MSAccessTool Excel files. Added steps to enable legacy Data Connection in Excel.
  • 3.2: Added support for un-named SQL Instances. On Variables tab and in usage of Common Variables, if SQL Instance name is left blank, SQL Server instance is referred to as <ServerComputerName> rather than <ServerComputerName>\<SQL Instance Name>.
2022/08/09 v3.0
  • Consolidated available configurations to MSAccessTool, PowerQueryTool, SQLServerTool, and SQLServer Remote.
  • SQLServerTool: Added structure, implemented in PowerOpI SQLServerRemote tool, for use of SQL Server from remote PowerOpI clients.
    • Tool elements share a common base, but each is configured for use as client, datasource, management etc., by settings of Common Variables, and by contained SQL specific to the intended role for each element. PowerOpI tools each include a demo project, intended as templates to be used as illustration of how-to, or modified, or replaced, as appropriate to support your organizational projects. The SQLServerRemote tool includes these elements:
      • ToolManagement: SQL Server environment setup and maintenance including Database, Users, Linked Servers.
      • ToolSource: Management of Project data tables, creation of Stored Procedures, Table upload and cube/view upload.
      • ToolClient: Query creation and checkout from server hosting SQL Server.
      • ToolClientRemote: Client configured for login and query from separate/remote client computers. Remote clients are on the same network; web configuration is not currently supported.
  • MSAccessTool, PowerQueryTool, SQLServerTool: Updated to maintain tool base common with updated SQLServerTool elements.
  • Integrated installation procedure including installation, security setup, and configuration for database platforms, PowerOpI applications including SQL Directive support, and project-data configuration.
  • Added configuration and use of Microsoft PowerBI Desktop as a report client, to InstallSetup_3.0.rtf file, and added that file to all download .zip files. Added example .pbix PowerBI Desktop files into _XL.zip files for PowerOpI MSAccessTool, PowerQueryTool, and SQLServerRemote. Examples illustrate QUERYs from PowerBI Desktop using direct Query, use of Linked Servers to access source files, and use of Stored Procedures in SQL Server and MS Access, all from PowerBI. PowerQuery M direct Queries performing JOIN etc in M require type casting in M, illustrated in the examples. Updated file MTemplatesMStrings.txt to also illustrate the resulting M QUERYs.
2021/02/20 v2.0
  • Products derived from PowerOpI Framework released: DemoPQ, Framework, MSAccessTool, PowerOpIFrameworkMPP, PowerQueryTool, SQLServerTool.
2021/02/17 v1.3
  • Updated Common Configuration Variables to support Domains, and Projects.
  • If specified, a domain name (eg "dbo.") can be appended at front of Stored Procedure in SQL Server.
  • If specified, SQL exported to SSMS can include a domain name in in the exported procedure name, eg "EXEC_dbo.ProcName"
  • If a ProjectName is specified, SQL exported to SSMS can be placed into a created directory named as the ProjectName, within the directory named as Excel file exporting the SQL.
2021/01/03 v1.2
  • Updated Common Configuration Variables logic to cover more default cases.
  • Moved tblLocalizationConfig to its own tab to eliminate conflict when adding rows to tables on tab DataSources.
  • tblLocalizationConfig updates now controlled by button with "dirty" indication, instead of auto-update. Fixes copy-paste in table, as well.
  • Re-Named file DemoPQClient.xlsm to PQToolClient.xlsm, DemoPQDataSource.xlsm to PQToolDataSource.xlsm
2020/12/07 v1.1
  • Strengthened relocation logic in Common Configuration Variables, more comments on each setting, more example configurations.
2020/11/26 v1.0
  • Initial Release.

Blog Introduction

Covering topics as they come up.

Here's a contact link

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.