PowerOpI 
INSTALLATION AND CONFIGURATION
v3.2
from https://www.softtoyssoftware.com/dbnet/programmingprojects/powerquerytool.php#downloadandinstall
https://www.softtoyssoftware.com/dbnet/programmingprojects/downloads/InstallSetup_v3.0.rtf

Install Apps
    Microsoft 365 Apps for enterprise (or Excel 2019 or later)
    Microsoft Project (Project Plan3 or Project Plan 5 - need desktop installation)
    Microsoft Visio (Plan 2, Visio Professional - need desktop installation, with connection to external data)

Install Database Environment
    SQL Server Express (Choose download https://www.microsoft.com/en-us/sql-server/sql-server-downloads; or Direct Download Express 2019) https://go.microsoft.com/fwlink/?linkid=866658
        Microsoft.ACE.OLEDB.XX (Download). https://www.microsoft.com/en-us/download/details.aspx?id=54920
        MSOLEDBSQL (Download). https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
        SSMS (Download). https://aka.ms/ssmsfullsetup
        References
            Installation Wizard Help https://docs.microsoft.com/en-us/sql/sql-server/install/instance-configuration?view=sql-server-ver16(and scroll down to section "Database Engine Configuration - Server Configuration Page")
            Install SQL Server Database Engine https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-database-engine?view=sql-server-ver16
    MS Access and PowerQuery are included in Microsoft 365 Tools for Enterprise

Set up Excel App-Level Security
    Within Excel File > Options > Trust Center > Trust Center Settings... >
        > Trusted Locations (PowerOpI directory at least) > Add new location... > add path, and checkmark "Subfolders of this location are also trusted" and checkmark "Allow Trusted Locations on my network for remote access"
        > MacroSettings select "Enable VBA macros", and checkmark "Trust access to the VBA project object model"
        > External Content Select "Enable all Data Connections" (others may also be enabled by other processes)
    In Excel, allow Native Queries
        Data menu > Get Data > Query Options >
            GLOBAL >
                > Data Load > select "Detect column types..."
                > Security > UN-checkmark "Require user approval..." under Native Database Queries
            CURRENT WORKBOOK >
                >Data Load > checkmark "Detect column types..." under Type Detection

    If using PowerQuery connections, re-verify or update permissions in Excel for existing Data Source Settings (if any)
        In Excel, Data menu > Get Data > Data Source settings...
            Select Data sources in current workbook and review, re-verify or update Data Sources as needed if they are used by PowerQuery in this workbook.
                Data source may be listed as <servername>\<sqlserverinstancename> or <servername>\<sqlserverinstancename>.<databasename> depending on level at which access security is granted.
                For Data sources on current computer (i.e. servername = current computer) typical values may be: Edit Permissions... > Credentials Edit... > Windows > Use my current credentials, Un-check "Encrypt connections", select Privacy Level "None". You may wish to set more restrictive permissions.
                For Data sources on a remote computer (i.e. servername <> current computer) typical values may be: Edit Permissions... > Credentials Edit... > Database > Username and password to server (initial defaults for PowerOpI tools are "ProgramManagement" and "abcd%12" not including the quote characters), Un-check "Encrypt connections", select Privacy Level "None". You may wish to set more restrictive permissions.
            Select Data sources in Global permissions and review, re-verify or update Data Sources as needed if they are used by by all PowerQuery clients on this computer.
                Settings choices are same as listed just above for Data sources in current workbook

    Setup Legacy Data Import Wizards
        This is a convenience to allow manual set up data connections from the Excel User Interface.
        Excel File Menu > Options > Select Data Pane > Show legacy data import wizards > Select as needed. Typically Access, Web, Text, SQL Server, Data Connection Wizard.

Set up SQL Server Firewall Security
References
   SQL Server Firewall Ports: https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16
   MS Social Enable Remote Connection to SQL Server: https://social.technet.microsoft.com/wiki/contents/articles/1533.how-to-enable-remote-connections-on-sql-server.aspx (Setup for server system firewalls such as Windows Defender, Norton Security, et al. The link shows procedure for Defender setuup. Description below is based on Norton.)
   Firewall for Database Engine Access: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access?view=sql-server-ver16
   ApexSQL Tools The early parts of this article are directly relevant; the later parts are specific to ApexSQL Tools which are outside the scope of this website: https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/
https://docs.microsoft.com/en-us/sql/t-sql/functions/security-functions-transact-sql?view=sql-server-ver16 Transact-SQL security Functions</a> Reference, some of which are used in SQL in PowerOpI Tools.

    Configure SQL Server for Remote Access, on the computer hosting the SQL Server Instance.
        In SSMS, connect locally to SQL Server instance.
        Right-click <Server Instance Name> (e.g. "COBALT\SQLEXPRESS") in SSMS Object Explorer > properties > to Server Properties pane.
        Connections page Set check-mark "Allow Remote Connections to this server".
        Security page Select "SQL Server and Windows Authentication Mode".
        Permissions page will be set up by SQL Directives later when setting up Users and Permissions.
    Configure SQL Server Network Access
        SQLEXPRESS default installation uses Dynamic Ports, out-of-scope for this website. You must re-configure SQL Server to use the default port 1433.
        On computer hosting SQL Server Instance
        In Computer Management Console "%windir%\system32\compmgmt.msc /s"
            Select Services and Applications/Services/SQL Server(<Instance such as "SQLEXPRESS">) and set Log On Property to Built-in account LocalSystem
            Then re-start the SQL Server service. For reference, the LocalSystem login is the NT Authority\System login within SQL Server Security.
            Similarly, set the LogOn property for SQL Server Browser to .\<computer sysadmin login name> with its password.
            Set the LogOn property for SQL Full-text Filter Daemon Launcher (<instance name as above>) to NT Service\MSSQLFDLauncher$SQLEXPRESS
            Set the LogOn property for SQL Server Agent (<instance name as above>) to built-in account Network Service.
    To use PowerOpI SQLServerRemote ToolClientRemote, from a client computer:
        On the computer hosting SQL Server Instance, either:
            Windows Start > Programs > Microsoft SQLServerManager15 or SQL Server 2019 Configuration Manager (as installed) or
            Windows Start > Programs > Windows Administrative Tools > Computer Management > Services and Applications > SQL Server Configuration Manager
        Open SQL Server Network Configuration > Protocols for <server instance> such as (SQLEXPRESS)
            Enable TCP/IP
            Right-Click TCP/IP and select Properties
            On Tab "Protocol" set Listen All to Yes.
            On Tab "IP Addresses" scroll down to "IPAll"
                Set TCP Dynamic Ports to blank (delete the "0")
                Set TCP Port to 1433
                Click OK
                Connection as, for example, "XANADU\SQLEXPRESS" connects via default Port 1433. To use a different port for security reasons, specify it and connect as "XANADU\SQLEXPRESS, 1527" to direct use of port 1527 configured in IPAll.
        Re-Start SQL Server Services
            Select SQL Server Services > right-click SQL Server <Server instance name such as SQLEXPRESS>;
            Select Re-Start. May take a couple of tries if it times out.
    Configure Firewall Access
        On computer hosting SQL Server Instance, create the following settings in the Firewall in use:
        Program Control
            SSMS
            C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\common7\IDE\Ssms.exe
            Allow, In/Out (to/from), Any Computer, All Protocols
            SQL Server
            C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlserver.exe
            Allow, In/Out (to/from), Any Computer, All Protocols
            SQL Server Telemetry Client
            C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlsqlceip.exe
            Allow, In/Out (to/from), Any Computer, All Protocols
            SQL Browser Services
            C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe
            Allow, In/Out (to/from), Any Computer, All Protocols
        Traffic Rules
            SQL Server TCP, UDP Ports 1433, 1434, 135
                Allow, In/Out (to/from), Any Computer, TCP and UDP, Local and Remote
                local ms-sql-s (port 1433)
                local ms-sql-m (port 1434)
                local epmap (port 135)
                remote ms-sql-s (port 1433)
                remote ms-sql-m (port 1434)
                remote epmap (port 135)
            SQL Server UDP Ports 500, (4500 if used)
                Allow, In/Out (to/from), Any Computer, UDP, Local and Remote
                local isakmp (port 500)
                local port 4500
                remote isakmp (port 500)
                remote port 4500

Install PowerOpI Tools
    PowerOpI Directory (create) includes a Project Directory for each organizational Project to be handled. Reference: Where to Install https://www.softtoyssoftware.com/dbnet/programmingprojects/powerquerytool.php#wheretoinstall 
        You create the PowerOpI Directory, and create each organizational Project directory within it
        Choose database configugation, and download from powerquerytool. https://www.softtoyssoftware.com/dbnet/programmingprojects/powerquerytool.php
        Into EACH organizational Project directory, you install the PowerOpI tool intended for use by un-zipping the two zip files downloaded into the Project file
            xxx_XL.zip
            xxx_MPP.zip
                Second zip file un-zipped into a Project Directory will overwrite one file duplicated between these two files
                Each organizational Project direcroty can include any other Project directories useful to the Program Manager - Docs, Reports, Tools, Configs, Suppliers
        Installation of ToolClientRemote on each remote client computer
            ToolClientRemote must be installed within a directory named the same as its related Project directory that is on the server computer containing ToolManagement, so that ToolClientRemote can connect properly to database entities including Linked Servers that have been set up by ToolManagement for that Project.
            The directory containing ToolClientRemote can be located anywhere in the file structure of the client computer.
    Fileshare/SMB WIndows and Network Directories as necessary to users of the PowerOpI Project files
        For Data Edit access to ToolSource to Source parties.
        For Remote Client Access to viewers.
        For RDT access to PowerOpI to Source parties and to Admin.
        SQLServerRemote file ToolManagment.xlsx is administrative and will probably only be made accessable on its installation directory to yourself and a backup admin.
        For SQL Server Remote, set up RDT access to the server computer hosting SQL Server, if needed from the manager's client computer.
    Check PowerOpI Common Variables
        When opening a PowerOpI tool file (ToolManagement, ToolSource, ToolClient, or ToolClientRemote), first close any other Excel file open on that computer, that is sourced from another Directory Root (e.g. "F:\...") or another computer (e.g. \\OTHERCOMPUTER\...). When files are open from sources with a different Root, Excel can't tell the directory path reliably to your file. That doesn't matter except when Excel is working out addresses and paths, which is just what it's doing when setting up connections to databases...
        Once PowerOpI Tool files are installed into each Program directory, with security and access set up, open each file and verify values on the Variables tab.
        Check the Variables in each file: ToolSource, ToolClient, ToolManagement, ToolClientRemote. Each tool attempts to locate itself and to set variable values consistent with the installation. But you may need to (for example) values defining a Linked Server if it needs to reference a file in a different directory.
        If any values are updated, the red flag at the top of the tab will light up. If that is so, press the Update button next to it to update the Variable values, and to turn the flag green.
        See the note belowfor more detailed instructions. https://www.softtoyssoftware.com/dbnet/programmingprojects/powerquerytool.php#variables

Set Up MS Visio Clients,  and MS Project Data Source Files
    Link MS Visio files
        Images can be linked to Tables exported from MS Project showing the Program sequencing, dependencies and dates, and will be dynamically updated as the MS Project plan is updated. (You could further develop different images relevant to your Program linked to different Program data.) There is a separate .vsdm file for each Visio figure in the project. Figures are usually linked to a table in ToolSource.xlsm that holds the current imported state of the MS Project plan exported to file P0001_Rollup.xlsx; or to any ToolClient for local image tracking. Each .vsdm file must be linked using the following procedure.
            In the distributed PowerOpI tools MSAccessTool, PowerQueryTool, SQLServerTool and SQLServerRemote, there are three .vsdm files included. One connects to the .xlsx file exported from MS Project as shown in the procedure following. One links to a Table in ToolSource and the third links to a Table in ToolCLient. As you follow the procedure below, note the file address and update it appropriately; and the Table name. The .xlsx linkage is to an Excel Tab. The others are to a Shadow Range for a Table in each source file.
            Open a Visio .vsdm image file.
            Visio Ribbom Data > Show/Hide section > Checkmark External Data Window.
            In the opened External Data window, right-click > Configure Refresh...
            Click the Change Data Source... button > Microsoft Excel Workbook > <ProjectFileName>.xlsx (browse to the Network path to the exported file) > "Task_Table1$ (worksheet)". (For Data Source files supporting data sources as Structured Tables, ToolSource.xlsm creates a Shadow Range for each Table that can be selected in Visio instead of the Tab delection required for Task_Table1$.)
            Checkmark "first row contains column headings", Rows to include (All Data) > Uniquely Identified checkmark "UniqueID".
            AFter Dialogue Buxes are Closed, Right-Click External Data Window and Refresh Data...
            Repeat for each Visio Image file.
Each .vsdm file contains a macro to export the image to .gif, .pdf, and .png.
    MS Project files
        These files should remain linked without need for modification. Note that in the download PowerOpI Tool distributions, several individual projects (M0004.mpp, M0005.mpp etc.) are linked into a Rollup file P0001_Rollup.mpp, which also links in a Resource file P0001_Resource.mpp. This is a good structure for some distribution of schedule/plan maintenance by responsible parties. The projects included into the Rollup are linked together using the method of structuring the project files discussed on this the softtoyssoftware website, and the result is exported as P0001_Rollup.xlsx for linking and JOINing into other PowerOpI tools.
Use files P0001_Rollup.mpp, P0001_Resource.mpp, M004.mpp, and M0005.mpp as templates for MS Project, in order to inherit embedded VBA, project structure, and calendar structure. The files may be re-formatted with care. The files should remain in a common Program tools directory, which also contains the Visio and Excel PowerOpI files for the Program.
Files M004.mpp, and M0005.mpp  are inserted into file P0001_Rollup.mpp as subprojects. Your Program be planned as a single MS Project using file P0001_Rollup.mpp as its basis (you can re-name the file as needed, but the string "Rollup" should be retained in the filename to enable export). Alternatively, additional or modified Subprojects based on files M004.mpp, or M0005.mpp  can be inserted via MS Project menu "Project" > Insert > Subproject.
If Subprojects are inserted into P0001_Rollup.mpp, they should follow the structure of M004.mpp, with milestone sections Links In and Links Out at the top of the file for linking among Subprojects. Subprojects must  be linked FROM WITHIN P0001_Rollup.mpp using these milestone sections, in order to preserve linkages when changes are made. Changes to task numbers below these sections will then be "hidden" from linked Subprojects; and any changes to Links In and Links Out made within P0001_Rollup.mpp will remain linked properly.
File P0001_Rollup.mpp contains an inserted column TaskMilestone, in which you create Keys to be used to link tasks to other PowerOpI files. The Keys in the templates are formatted as Elementname:ElementRev:Milestone including embedded colon characters to delimit sub-fields, because that is the format of Keys used in the Excel and Database Tables in the other PowerOpI files. Keys may be any format you design, but must match among files in order to match in database JOIN operations, and Excel INDEX/MATCH or equivalent lookup operations.
File P0001_Rollup.xlsx is exported from the Rollup file P0001_Rollup.mpp, using Macro MS Project Menu "Developer" > View Macros > P0001_Rollup.mpp!ReportDatesExport. P0001_Rollup.xlsx can be imported and JOINed with PowerOpI Excel and Database Tables  via Key values. Take care NOT to execute MS Project Macro deleteTask by mistake. (In all these cases, if the file P0001_Rollup.mpp is re-named, then its new name is used instead of all instances of the name P0001_Rollup.mpp just described. The new filename should retain the string "Rollup" to enable export.)
Project Resources are defined in P0001_Resource.mpp and assigned geo-related holiday calendars (CM-Asia, JDM-Taiwan, Company-US etc, reflecting extended holidays observed. These Resources are assigned in the rollup mpp, P0001_Resource.mpp.
YouTube playlist https://www.youtube.com/playlist?list=PLvgWuP_S8eIGAP7JCKa2-POVh9-PyTyif contains detailed discussion of these points.

Set up Database Connections for SQL Server
First, in the steps outlined below, set up Directive database management tools from ToolManagement and ToolSource, and then use the Directives from SSMS to set up the SQL Server database environment for the Project on the server computer.
Next, build and check connections in ToolSource and ToolClient on the server computer..
Then,set up instances of ToolClientRemote on client computers.

Directive names shown are from the PowerOpI distributions of SQLServerTool and SQLServerRemote. For names of Linked Servers and Stored Procedures, your Project is likely to have different names. Your Project will likely be in a Directory with a different name. Remember that names and addresses are set on the Variables tab, and QUERY names, logic and use in connections are controlled on the DataSources tab of each PowerOpI Excel tool.
Example SQL is shown on page Remote (https://www.softtoyssoftware.com/dbnet/programmingprojects/Remote.php) on this website, and is visible within each tool ToolManagement.xlsm, ToolSource.xlsm, ToolClient.xlsm, and ToolClientRemote.xlsm on tab DataSources which also show Connection Strings, and source for QUERYs for PowerQuery using M language.

Close all involved Source Data files before proceeding. Access from SQL Server to open files by the following steps will fail for any linked entities such as Linked Servers or OPENROWSET SPs.
ToolManagement is provided for SQLServerRemote to set up and manage the SQL Server environment. For SQLServerTool, management functionality is more limited, and is provided in ToolSource. Management is accomplished using SSMS with Directives configured and set up for that purpose by ToolManagement and ToolSource. ToolClient and ToolClientRemote may also provide Directives, but this is discouraged since Clients typically do not have management access to the database, for better security and distributed maintenance..
Set up Directives
    On Server computer hosting SQL Server Instance: ToolManagement.xlsm, ToolSource.xlsm, ToolClient.xlsm
    On each Client computer hosting ToolClientRemote.xlsm
    From each PowerOpI Tool: ToolManagement.xlsm, ToolSource.xlsm, ToolClient.xlsm, ToolClientRemote.xlsm
        Create SSMS Directives via Tab DataSources, Pushbutton Execute tblSQL Directives
        Location of Directives exported is configured on Tab "Variables", variable name "ExportTo". Its value is typically like "C:\Users\Owner\Documents\SQL Server Management Studio\SQLServerRemote\ToolManagement\ProfileLocal", including the name of the Database ("SQLServerRemote"), the name of the PowerOpI tool ("SQLServerRemote"), and the configuration profile in use in the PowerOpI tool ("ProfileLocal").
        These Directives can be loaded by the SSMS File > Open > Folder > ... sequence. It's convenient to create a shortcut to the root of the path containing directives into the SSMS Menu Bar.
        Directives may be provided, or created for your Project, for any of the PowerOpI tools. Use the "Execute tblSQL Directives" pushbutton in each PowerOpI tool on its DataSources tab to create Directives for each tool.
        Directives are defined by including the string #VBTEXTJOIN: EXPORT, or #VBTEXTJOIN EXPORTWITH <schema-name>, enclosed within SQL comment delimiters. As with all SQL expressions in tblSQL, you can see how such statements are created using tool Variables for configuration.

Create the Project Database (one or more)
    From SSMS on server computer, open Export Directory for ToolManagement (for SQLServerRemote) or ToolSource (for SQLServerTool) for the Project and run Directive
        CREATE_Server1DatabaseName1.sql
    This procedure will only create the database if it does not already exist, to protect existing SPs in its Programmability directory. The following steps will re-create SPs for this database that may have been changed due to Variable value changes (such as filepaths).

Create Users, Logins, and Permissions
    From SSMS on server computer, open Export Directory for ToolManagement (for SQLServerRemote) or ToolSource (for SQLServerTool) for the Project and run Directives:
        qSetPermissions.sql to set up Windows security logins (in ToolManagement or ToolSource).
        CREATE_LoginAndUser_SSMS.sql to set up SQL Server security logins for remote access (in ToolManagement, for SQLServerRemote only).
        CREATE_LoginAndUserPermissions_SSMS.sql to set database access and permissions for SQL Server security logins(in ToolManagement for SQLServerRemoteonly).

Configure the SQL Server ACE Provider
    From SSMS on server computer, open Export Directory for ToolManagement (for SQLServerRemote) or ToolSource (for SQLServerTool) for the Project and run Directives:
        CREATE_dbo.spConfigACE.sql to set up an SP which is called from other SPs

Create Linked Servers (one or more, to update filenames and paths)
    From SSMS on server computer, open Export Directory for ToolManagement (for SQLServerRemote) or ToolSource (for SQLServerTool) for the Project and run Directives:
        CREATE_LS_ToolSource.sql and
        CREATE_LS_P0001_Rollup.sql
        (and/or all properly-named LS_ProjectName_LSNames) linked servers for your project, to build or re-build the Linked Servers. Check the Linked Servers by following down the path in SSMS Server Objects > Linked Servers > (expand each Linked Server) > Catalogs > default > Tables which will expand to list all Tables and Named Ranges accessible through that LS if it is working properly.
        Linked Server names actually created include the Project name doing the creation and filename referenced, e.g. LS_SQLServerRemote_P0001_Rollup.
        Finally, remember that you must create a Linked Server for each Data Source file to be accessed. That Linked Server will be able to access all Named Ranges and Worksheets within its linked file. You can use Shadow Ranges https://www.softtoyssoftware.com/dbnet/programmingprojects/powerquerytool.php#exceldata described just below, to make Excel Structured Tables visible to SQL Server Linked Servers or OPENROWSET, and as MS Access Linked Tables.

Create Stored Procedures (one or more, that use Linked Servers, or OPENROWSET; to update filepaths)
    From SSMS on server computer, open Export Directory for ToolSource (for both SQLServerTool and SQLServerRemote) for the Project and run Directives:
        CREATE_spUnionGroupToolSource.sql
        CREATE_spUnionQueryLS_ToolSource.sql
        CREATE_spBuildPlanLS_ToolSource.sql
        CREATE_spPushTablesLS.sql
        CREATE_spPushTablesOPENROWSET.sql
        CREATE_spBuildPlanTable.sql
        CREATE_spUnionGroupTableSSMS.sql
    Stored Procedures created by these Directives will include the name of the Schema name, Linked Server name which includes the Project name, and PowerOpI tool filename (e.g., ToolSource) within the name of the SP actually created, e.g.: dbo.spBuildPlanLS_SQLServerRemote_ToolSource; calls to the SP must use the created name.
    ToolSource allows data owners to update data, to set up new QUERYs, to set update or set up new QUERYs using the Tables, and to push the Tables to the SQL Server instance.
    You should name your SPs with a preceding "sp". Don't use "sp_" though, which is reserved for system procedures. Check that the SPs were correctly created and that configuration is correct for each, by following down the path in SSMS Databases > (databaseName) > Programmability > Stored Procedures which will list SPs for that database. (right-click) > Modify shows you the source SQL for each.

Next, setup Date and Data connections in the PowerOpI Tool files ToolManagement.xlsm, ToolSource.xlsm, ToolClient.xlsm,and ToolClientRemote.xlsm. In each case you can see the connection setup being processed in the lower left corner of the Excel pane. Wait for each operation to complete before clicking a button for the next operation.
    On the Server computer
        On ToolManagement, tab DataSources, click Build+Update DATE Links and then click Build+Update DATA Links.
        On ToolSource, tab DataSources, click Build+Update DATE Links and then click Build+Update DATA Links.
        On ToolClient, tab DataSources, click Build+Update DATE Links and then click Build+Update DATA Links.
    On each computer hosting TooClientRemote.xlsm
        On ToolClientRemote, tab DataSources, click Build+Update DATE Links and then click Build+Update DATA Links.
        If the link re-builds fail, remember that ToolClientRemote must be within a directory on the client computer where that directory is named the same as its Project on the server computer.

User Login
    On ToolManagement.xlsm, ToolSource.xlsm, ToolClient.xls on the Server computer default login is Windows security and can be left there.
    Connections from ToolClientRemote will ask for SQL Server login credentials. Logins for remote use without Windows security, created above, are ProgramManagement and ProgramManagement2, both with password "abcd%12" for the downloaded template Project. On initial Template setup, use these names and passwords to get the tools working. When customizing for your Project, please change the user names and passwords away from these defaults.
    Connections involving PowerQuery will ask on set-up for credentials to login to SQL Server. Most common answer is Windows Current User login credentials (radio button); otherwise enter server-specific credentials for that user. This is repeated for both PowerOpI Data Source setup, and Client setup (stated in next paragraph).

Normal Use
    When Access and Visio linkages, and Excel Variables, SPs, LS, DATES and DATA have been updated and refreshed, the Project should be functional, and you can add/update/delete Excel tables, and click Refresh DATES and Refresh DATA as needed to incorporate updates into Reports. Once structure and paths are set up, it remains quite stable and absorbs subsequent changes well.
    The environment is now set up and should refresh and import Dates and update Pivot reports, using the ToolClient Refresh DATE and Refresh DATA pushbuttons. Use the pushbuttons rather than Refresh All from the Excel toolbar, to avoid refreshing dead links resulting an error report.
    Glitches do occur, so check the troubleshooting table when needed. https://www.softtoyssoftware.com/dbnet/programmingprojects/vbaConnection.php#troubleshooting

Set up Database Connections for MS Access
MS Access Data Source connections Each database environment behaves differently in connecting data sources. MS Access must be manually set up, on each install including each local, shared, and client installation.
    Linking MS Access Data Source Tables
        Re-Link Existing Tables
            MS Access Menu External Data > Linked Table Manager > checkmark Data Source(s) listed > Edit >
            Update path and filename as needed to locate current location of Data Source file. If updating a downloaded MSAccessTool instance installed in C:\Users\<username>\Documents\PowerOpI\MSAccessTool, you probably only need to update the <username> part of the path, click "Save" and "Close". In other cases, you may need to update more of the path and/or filename.
            If several different paths are listed, you will have to update each. If several are the same, you can checkmark them, Edit and update them together. If several are different, checkmark them one at a time, Edit, and then move on to next.
        Link in New Tables
            Each Data Source table must be linked individually, even if they exist within one file such as a ToolSource.xlsm file. MS Access Menu External Data > New Data Source > From File > Excel >enter File name: and path > and select "Link to the data source by creating a linked table" > OK.
            Select Show Named Ranges and select "rng<tablename>" (which is a "shadow range" name created by ToolSource) and "OK". (A Shadow Range is created by ToolSource.xlsm for each Structured Table used; MSAccess can't see Structured Tables directly so PowerOpI ToolSource creates these Shadow Ranges to make them visible.)
        Using Network Addresses vs. Local Addresses
            If you are using MSAccessTool only on a single computer with no remote Client computers, you can use local addresses to link Tables (e.g., C:\Users\<username>\Documents\PowerOpI).
            If a client on a remote computer connects to MSAccessTool, it does so by opening the .accdb file that resides on the Server/data source computer (location set by MSAccessTool\ToolClient.xlsm Variables definition); and that file opens in MSAccess on the Client computer, not the Server computer. (The directory containing the MSAccessTool .accdb file must be shared in Windows to Client computers.)
            The Client computer must access the Data source tables that reside on the Server computer, so the .accdb file link to each Table must use the Network Address of the Server computer. The Network address looks like \\SERVERNAME\Documents\PowerOpI\MSAccessTool\ToolSource.xlsm (In this example the Documents directory is shared to Client computers.) The Network address can be determined from Windows File Explorer > left pane > Network >\\SERVERNAME > Share Name; Sub-path to MSAccessTool directory > filename.
            And, of course when re-linking Data Source files if the .accdb file is moved, the Network Address including \\SERVERNAME and Shared Directory name of the Data Source file(s) must be updated in that process.
            If you use the Network address of the local computer to access Data Source files on the same local machine, and if the network is not operating (such as on a Laptop in a location without network connectivity), the local connection may not function. This is a property of Windows networking... If it's local to the Data Source files, a Client should use the Local address (C:\...).
            Once Linked, the Tables will appear in the MS Access All Access Objects left pane as Tables, each by the name created as it was linked (e.g., "rng<tablename>" showing the Shadow Range names). Double-clicking a Table name in that pane displays the Table content. The linked Tables are available for QUERY creation in the MS Access Design View editor, or SQL View editor, or in direct Query connections from a Client such as PowerOpI MSAccessTool ToolClient.xlsm.
        Re-build Connections to reflect the current directory structure
            Close MS Access before proceeding with Excel Connection Setup.
            Build Connections in Excel, as follows:
            In ToolSource, click Build+Update DATE Links and then click Build+Update DATA Links to build the connections in the current directory structure.
            In ToolClient, click Build+Update DATE Links and then click Build+Update DATA Links to build the connections in the current directory structure.
    The environment is now set up and should refresh and import Dates and update Pivot reports, using the ToolClient Refresh DATE and Refresh DATA pushbuttons. Use the pushbuttons rather than Refresh All from the Excel toolbar, to avoid refreshing dead links resulting an error report.
    Any ToolClient.xlsm for MSAccessTool installed on a remote client computer must be installed within a Windows directory named the same as the same Project set up in the common server computer. Variables must be checked, and DATE and DATA connections must be re-built.
    Before using a remote client, connect the remote client computer via filesharing (use the Network connection in File Explorer ) to the data source computer containing data source files and the .accdb file. After connecting, you can optionally close the File Explorer pane. The connection will need to be re-established after any subsequent re-boot of the client or data source computers.
    Note also that MS Access and also PowerQuery can Query an open file. That means that a single file can host Data Source Tables, QUERYs, and Reports, all in the same file. Viewing and refreshing a report in that case means that the Data Source file is open too since it's the same file, but that's okay with MS Access and with PowerQuery. However, note that Data Source file(s) must NOT be open in MS Access at the same time the source file is QUERYed.
    SQL Server, on the other hand, will NOT QUERY an open Data Source file, so a separate Client file must contain QUERYs and Reports, and the Data Source file for those QUERYs must NOT be open when a QUERY using them is executed.

Set up Database Connections for PowerQuery
PowerQuery Data Source connections PowerQueryTool requires much less setup than MSAccessTool and especially much less than SQLServerTool. It can provide broad data functionality within Excel, and provides connectivity to dozens of database environments and file types.
Some PowerQuery functionality is provided in MSAccessTool, SQLServerTool and SQLServerRemote that uses those respective environments, and you didn't even notice the setup! PowerQuery can "see" Structured Tables within an Excel file, so doesn't need Shadow Ranges to be set up as ToolSource files can do.
However, functionality outside of Stored Procedures that reside within the database environments, is carried within each PowerQuery client file, exposing file structure and related product structure, complicating security and maintenance.
PowerQuery in Excel can QUERY an open Excel file, so a single PowerQueryTool file ToolSource or ToolClient can host Data Source Tables, QUERYs, and Reports all in a single file. Clients can access Tables from a common Data Source. And PowerBI Desktop can QUERY the same Data Source Tables with the same QUERY structures (in M Language, not SQL) to drive numerous analysis tools and graphics.
    First
        Validate Variables on the ToolSource Variables Tab.
        In ToolSource, click Build+Update DATE Links and then click Build+Update DATA Links to build the connections in the current directory structure.
    Then
        Validate Variables on the ToolClient Variables Tab.
        In ToolClient, click Build+Update DATE Links and then click Build+Update DATA Links to build the connections in the current directory structure.
    Finally
        The environment is now set up and should refresh and import Dates and update Pivot reports, using the ToolClient Refresh DATE and Refresh DATA pushbuttons. Use the pushbuttons rather than Refresh All from the Excel toolbar, to avoid refreshing dead links resulting an error report.
        You can update/add/delete Tables, and modify/delete/add new QUERYs. These are in M Language not SQL. There are examples throughout the softtoyssoftware https://www.softtoyssoftware.com/default.htm website, and a page of references for common QUERYs https://www.softtoyssoftware.com/dbnet/programmingprojects/MTemplatesMStrings.txt

    Any ToolClient.xlsm for PowerQueryTool installed on a remote client computer must be installed within a Windows directory named the same as the same Project set up in the common server computer. Variables must be checked, and DATE and DATA connections must be re-built.
Before using a remote client, connect the remote client computer via filesharing (use the Network connection in File Explorer ) to the data source computer containing data source files. After connecting, you can optionally close the File Explorer pane. The connection will need to be re-established after any subsequent re-boot of the client or data source computers.
    Final note - You can do M QUERYs directly in Excel. But of course, before you can use PowerQuery with a database such as MS Access and SQL Server, those database systems must be set up and linked as described in prior sections of this accordion section. The work is in setup of the database environments, not in setup of PowerQueryTool.

Set up Connections From PowerBI Desktop
Microsoft PowerBI Desktop can be used as a client for PowerOpI data source files, to import files, or to drive Visuals in PowerBI from
    Direct QUERYs in M within PowerBI, which may include embedded SQL.
    Database QUERYs using Linked Servers in SQL Server or linked files in MS Access.
    Database QUERYs using Stored Procedures in SQL Server, or MSAccess, or other database environments.
The sample project template in the distributed PowerOpI files is more suited to tables than to visuals (describing the difference between Power Operational Intelligence ("how are we going to execute") versus Power Business Intelligence ("trend analysis, prediction of results")). However, that's more a consequence of the project template provided - nothing about PowerOpI Tools limits them from PowerBI applications.
There are visuals in Excel as a report client, and PowerBI supports numerous native visuals and many more through its AppSource (PowerBI menu Home > Insert section > More Visuals > From AppSource). PowerBI doesn't directly support Pivot Tables, but you can use PowerQuery from Excel via PowerOpI PowerQueryTool. So there's overlap through interoperability.
If a PowerBI report is installed or moved to a different client computer, if its source address references files (including MS Access .accdb files), that address must be updated at the client. That would involve QUERYs to MS Access or to PowerOpI source files.
    For each PowerBI Desktop report file
    PowerBI menu Home > menu section Queries > Transform Data > leftmost pane Queries > Select each QUERY > Menu section Query > Advanced Editor > displays M QUERY
    Update every source statement in each QUERY, e.g.
    source = Excel.Workbook(File.Contents("C:\Users\Owner\Documents\PowerOpI\MSAccessTool\ToolSource.accdb), null, true),
    More elements of the path than highlighted may need to be updated depending on your specific configuration and fileshare paths.

SQL Server connections from PowerBI still address SQL Server as a Service addressed as <servercomputername>\<sqlserverinstancename> and so do not need source address update in PowerBI Desktop QUERY source statements.
To use PowerBI Desktop from a remote client computer for QUERYs of files on a source computer, a filesharing connection needs to be established from the client computer via the network address in the File Explorer to the source computer e.g. \\COMPUTERNAME\SHARENAME\path\sourcefilename.
When a QUERY is executed from PowerBI Desktop, it may ask for Credentials. For SQL Server, the defaults set up for a remote connection are SQL Server Security, User = ProgramManagement2, Password = abcd%12. (You likely changed from these defaults when your SQL Server instance was installed, and you should use the security, user, and password set up at that time.)

References
PowerOpI Website: https://www.softtoyssoftware.com/
Troubleshooting: https://www.softtoyssoftware.com/dbnet/programmingprojects/vbaConnection.php#troubleshooting
M Query Language Function Reference: https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference
M Query Language Examples: https://www.softtoyssoftware.com/dbnet/programmingprojects/MTemplatesMStrings.txt
SQL Server Permissions with Descriptive Figure: https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

Copyright 2022 Richard M Bixler. All Rights Reserved.