AI-ready STAR data warehouse

Licensing
---------
Copyright (c) 2026 Encapsulated Inc.

This package is provided under the Encapsulated Community Source-Available
License 1.0 in LICENSE.txt. It is source-available, not OSI open-source software.
Read the full license before using or sharing the scripts.

The license permits free internal commercial use, including production, private
modifications, paid professional services, and free sharing under its terms.
You may hire Encapsulated or another consultant. Continuing to use the covered
software does not require a support subscription or software-license renewal.

Selling or commercially bundling the covered warehouse, or offering it as a paid
hosted warehouse product, requires a separate written commercial agreement with
Encapsulated. Genuine consulting and administration of a customer's permitted
deployment remain allowed. Independent applications are addressed in the license.

This is a summary; LICENSE.txt contains the governing terms. Third-party software
and services remain subject to their own agreements and charges.


This package includes two databases:
- STAR virtual: a local copy of the STAR tables and columns used by the warehouse.
- STAR data warehouse: the reporting model, with documented relationships and
  descriptions that people and AI tools can read.

Included files:
- encapsulated_star_virtual.sql
- encapsulated_star_datawarehouse.sql
- LICENSE.txt
- readme.txt

Before you begin
----------------
Use SQL Server 2025 for the instance hosting these two databases. The warehouse
uses the native json data type and REGEXP_LIKE; set the warehouse database's
compatibility level to 170.

Create both databases on the same SQL Server instance. Use new, empty databases;
these are installation scripts, not upgrade scripts. Run each script in the
correct database, not in your source STAR database.

You will need permission to create the databases and their objects, and a linked
server connection with read access to the source STAR database. Have your SQL
Server administrator configure the linked server and its login mappings.

The database names below are examples. You may choose your own names.


1. Create and install the STAR virtual database
----------------------------------------------
Create a new database, for example encapsulated_star_virtual.

In that database, run the included encapsulated_star_virtual.sql script.


2. Configure the connection to STAR
----------------------------------
If you already have a suitable linked server to your STAR database, reuse it.
Check its settings against the template described below, including Data Access
and RPC Out.

For a new linked server, open the definition of
app.administrator__configure_linked_server in the virtual database. It contains
an administrator's template to review, customize, and run manually. Executing
the procedure itself intentionally raises an error; it does not configure the
linked server for you.

The template drops and recreates a linked server with the chosen name, including
its login mappings. Skip that template if you are reusing an existing connection.
You can also configure the linked server through SQL Server Management Studio.

In the virtual database, edit the existing row with id = 0 in app.config:
- star_server_name: the linked server name on this SQL Server instance.
- star_db_name: the actual STAR database name on the source server.

Replace the sample values with the values for your environment. The linked server
name may differ from the actual server name.


3. Load the STAR virtual database
--------------------------------
In the virtual database, run app.ping without parameters:

	execute app.ping;

Wait for it to complete successfully before continuing. The initial load copies
the selected STAR data. Subsequent runs use each table's configured sync mode;
by default, the supplied tables use incremental synchronization.


4. Create the warehouse and its source synonyms
----------------------------------------------
Create a second database on the same SQL Server instance, for example
encapsulated_star_datawarehouse. Set its compatibility level to 170.

In the VIRTUAL database, run:

	select t.synonym
		from app.interface as t;

Copy all values from the synonym column, without the column heading. These are
CREATE SYNONYM statements generated for the virtual database's actual name.

Switch to the new WAREHOUSE database and run those statements. Create the
synonyms before running the warehouse installation script: its source functions
depend on them.


5. Install, document, and load the warehouse
------------------------------------------
In the WAREHOUSE database:

5.1. Run the included encapsulated_star_datawarehouse.sql script.

5.2. Apply the maintained metadata as SQL Server extended properties:

	execute appmetadata.sync;

     This publishes the descriptions from the appmetadata tables. Run it again
     whenever you change those descriptions. Review its results for added,
     updated, or removed descriptions and any items still missing documentation.

5.3. Load the warehouse:

	execute app.ping;

     The warehouse must be enabled in app.config (id = 0, enabled = 1). The
     installation script supplies this setting. app.ping validates the source
     before refreshing the warehouse tables. If validation fails, investigate
     the reported rule before proceeding.


6. Schedule refreshes
--------------------
Create a SQL Server Agent job with two Transact-SQL steps, in this order:

Step 1 - Database: your STAR virtual database
	Command: execute app.ping;
	On success: go to Step 2.
	On failure: quit the job reporting failure.

Step 2 - Database: your STAR data warehouse
	Command: execute app.ping;
	On success: quit the job reporting success.
	On failure: quit the job reporting failure.

Choose the schedule that suits your firm. Verify that the job's execution
account can access both databases and use the linked server connection.

Both databases record refresh activity in app.logs. The warehouse refresh commits
one table at a time, so reports running during a refresh can see a mixture of
previous and newly refreshed data. Plan report timing accordingly.


More documentation is coming to sqlsig.com. Feedback and questions are welcome.

Enjoy!

-Amine Fayad
encapsulated.com | sqlsig.com
