# Analytics Catalog Wizard — Feature & Functionality Guide

**Purpose of this document:** a single, code-grounded source of truth for the Analytics Catalog
Wizard — a few-click discovery-to-report flow spanning the Analytics, SqlWorkbench, and Framework
modules — organized so sections can be lifted directly into audience-specific deliverables:
developer reference, implementation/config guide, admin guide, end-user help, and marketing/sales
collateral. Every claim below reflects the actual backend (`GB5Solution/Analytics`,
`GB5Solution/SqlWorkbench`, `GB5Framework`) and frontend
(`gb4.7mfe/projects/analytics/master/catalogwizard`) code built and committed as of 2026-08-19
(gb5 `dev` @ `6075de33a`, gb4.7mfe `GBDEV4.7` @ `7dfb9a722`). Checkboxes (`[x]`/`[ ]`) mark
built-and-live-verified vs. still-open items at a glance; §3.3 and §7 give the fuller detail
behind every open item. Sections marked **[Internal only — not for marketing]** describe known
gaps and should not appear in customer-facing material.

---

## 1. Executive Summary (marketing/sales-friendly)

Building an ad-hoc analysis in GB5 today means an admin already has to know exact internal table/
field IDs and hand-type every join's SQL predicate. The Analytics Catalog Wizard removes that
barrier: **pick a database → browse its real tables and columns → accept suggested joins (found
automatically from the database's own foreign keys) → name it → get a runnable report** — in a
few clicks, with no SQL knowledge required.

Two things make this genuinely new, not just a nicer form over the same old mechanism:

- **It works against the tenant's own database or an entirely separate client database** —
  registered once in GB5's SqlWorkbench module — so ad-hoc reporting isn't limited to data already
  living inside GB5 itself.
- **The joins aren't guessed by a human.** The wizard reads the target database's real foreign-key
  constraints and proposes the correct join for every pair of selected tables automatically, then
  lets the user confirm or edit before anything is saved.

The result of a completed wizard run is not a preview or a draft — it's a real, saved Analysis
(`MANALYSIS` + its tables, fields, and joins) that immediately shows up in GB5's existing Analysis
Query Designer for further refinement, and — for analyses pointed at an external client database —
one that actually executes and returns live rows from that external database when run, not just
from GB5's own database.

---

## 2. Functional Area Catalog

### 2.1 Schema Discovery

- [x] Browse the **tenant's own database** — tables, columns (name, data type, size/precision,
      nullability, primary-key flag), and foreign keys — with zero connection setup; it's the same
      database the current session is already on. Works against both of GB5's supported engines
      (SQL Server and PostgreSQL), auto-detected per session.
- [x] Browse an **external client database** already registered in SqlWorkbench (`MSWCLIENTDATABASE`)
      — same columns/foreign-key shape returned, resolved through SqlWorkbench's existing
      Vault-backed, role-scoped (read-only) credential system, so no new credential handling was
      introduced.
- [x] One shared result shape (`SchemaTableColumnDTO`/`SchemaForeignKeyDTO`) regardless of which of
      the two sources above was browsed, so every downstream step (join suggestion, registration,
      quick-build) works identically no matter where the data came from.
- [ ] External client databases of a type **other than SQL Server** (PostgreSQL/MySQL/Oracle) —
      not yet supported for browsing. See §3.3/§7.

### 2.2 Table/Column Registration (the semantic layer)

- [x] Selected tables and columns are registered into GB5's existing `DBOBJECT`/`DBOBJECTFIELDS`
      semantic-layer tables automatically — the same registry every other Analysis in GB5 already
      depends on — with **zero manual entry**. Previously this registry could only be populated by
      an offline developer tool or hand-authored inserts; the wizard is the first real, in-product
      way to populate it.
- [x] Re-running the wizard against tables already registered **reuses the existing registration**
      instead of creating a duplicate — matched by table name **and** by which database it came
      from, so a same-named table from two different databases is correctly kept distinct.
- [ ] The registration step (writing `DBOBJECT`/`DBOBJECTFIELDS`) is not part of the same database
      transaction as the rest of the build — see §3.3/§7 for the practical consequence.

### 2.3 Join Suggestion

- [x] Given a set of selected tables, every real foreign-key relationship the target database
      already enforces between them is surfaced as a **suggested join**, expressed exactly the way
      GB5's join system already stores joins — no separate "join designer" screen or hand-typed SQL
      required for the common case.
- [x] Multi-column foreign keys are combined into one correct join condition automatically.
- [x] A self-referencing foreign key (a table joined to itself) is flagged for manual attention
      rather than silently guessed, since an automatic alias choice could be wrong.
- [x] Every suggestion can be reviewed, edited, or skipped before anything is saved — the wizard
      proposes, the user decides.

### 2.4 Quick-Build (one click to a real, runnable Analysis)

- [x] From a name plus the selected tables/columns/joins, the wizard creates a complete, real
      Analysis in one save: the Analysis header, one entry per selected table, one entry per
      selected column, and one entry per accepted join — all in a single all-or-nothing database
      transaction (excluding the semantic-layer registration noted in §2.2).
- [x] By default, also creates one ready-to-run report definition from the selected columns (a
      simple, safe default layout) so the wizard's output is an immediately runnable report, not
      just a set of definitions requiring further hand assembly — this can be turned off if a user
      wants to hand-build the report layout instead.
- [x] The result hands back the new Analysis's ID so it can be opened directly in GB5's existing
      Analysis Query Designer for fine-tuning (which fields display where, which are totals, which
      are filters) — the wizard is a fast front door into the existing designer, not a replacement
      for it.

### 2.5 Execution Against an External Database

- [x] An Analysis can be pointed (via GB5's existing per-tenant datasource-override mechanism) at a
      specific SqlWorkbench-registered client database, and — this is the part that makes an
      external-DB analysis actually useful rather than just a definition — **running that report
      now genuinely executes the generated query against that external database and returns real
      rows from it**, using the same read-only, role-scoped connection SqlWorkbench already manages.
- [x] When an Analysis has no such override configured (the default, overwhelming-majority case),
      report execution is byte-for-byte identical to how it already worked before this feature —
      this path was deliberately left completely untouched to protect the existing, heavily-used
      report engine from any regression risk.
- [ ] External execution is supported for **SQL-Server-typed** client databases only in this
      version — see §3.3/§7.

### 2.6 Frontend Wizard

- [x] A four-step guided flow (pick source → browse & pick tables/columns → confirm suggested
      joins → name & build), built on GB5's existing reusable wizard shell component (the same one
      already used for SqlWorkbench's database-provisioning flow), so it looks and behaves
      consistently with an existing, familiar multi-step flow rather than introducing a new UI
      pattern.
- [ ] Not yet wired into a menu/route reachable by an end user — see §3.3/§7.
- [ ] Not yet click-tested in a live browser session — see §3.3/§7.

---

## 3. For Developers

### 3.1 Backend layout
- Schema discovery (own DB): `GB5Framework/FrameworkBLL/SchemaIntrospection/{IOwnDbSchemaBLL,OwnDbSchemaBLL}.cs`,
  `FrameworkDAL/CustomCode/SchemaIntrospection/{ISchemaIntrospectionDAL,SchemaIntrospectionDAL}.cs`,
  `FrameworkDAL/Query/SchemaIntrospection/SchemaIntrospectionQB.cs` — branches on `LoginDTO.DatabaseType`
  for SQL Server vs. PostgreSQL SQL text.
- Schema discovery (external client DB) + internal batch execution: `GB5Solution/SqlWorkbench/SwBLL/Provisioning/{ITargetDbExecutor,TargetDbExecutor}.cs`
  — added `GetServerColumnsAsync`, `GetServerForeignKeysAsync` (SQL Server only, `NotSupportedException`
  otherwise), and `ExecuteQueryBatchAsync` (internal-only, single-connection multi-statement batch
  execution, 100,000-row cap — never exposed via any end-user/ad-hoc-SQL endpoint).
- Shared discovery DTOs: `GB5Shared/DTO/Framework/SchemaIntrospection/{SchemaTableColumnDTO,SchemaForeignKeyDTO}.cs`.
- DBOBJECT source disambiguation: `GB5Shared/DTO/Framework/DBObject/DBObjectDTO.cs`
  (`SourceDbObjectKind`, `SourceClientDbId`), `GB5Framework/FrameworkDAL/Query/DBObject/DBObjectQB.cs`
  (`GET_DBOBJECT_BY_SOURCE_AND_NAME`), `FrameworkDAL/CustomCode/DBObject/DBObjectDAL.cs` /
  `FrameworkBLL/DBObject/DBObjectBLL.cs` (`FindBySourceAndNameAsync`).
- Wizard orchestration: `GB5Solution/Analytics/AnalyticsBLL/Wizard/{IAnalyticsWizardBLL,AnalyticsWizardBLL,JoinSuggestionService}.cs`,
  DTOs under `AnalyticsDAL/DTO/Wizard/*`, endpoints `AnalyticsSL/EndPoints/Wizard/{DiscoverSchema,QuickBuildAnalysis}.cs`
  → `POST /Analytics/Wizard/DiscoverSchema`, `POST /Analytics/Wizard/QuickBuildAnalysis`.
- Transaction-composability groundwork: `AnalyticsDAL/CustomCode/{AnalysisObject/AnalysisObjectDAL,AnalysisField/AnalysisFieldDAL,AnalysisQuery/AnalysisQueryDAL}.cs`
  — each now accepts an optional external `DbTransaction`, so the wizard can save all five entity
  types (Analysis, AnalysisObject, AnalysisField, DBJoin, AnalysisQuery) in one atomic transaction.
- External execution routing: `AnalyticsBLL/ExecutionRouting/{IAnalysisExecutionRouteResolver,AnalysisExecutionRouteResolver}.cs`,
  `AnalyticsDAL/DTO/ExecutionRouting/ExecutionRouteDTO.cs`, and the routing branch inside
  `AnalyticsDAL/CustomCode/Analysis/AnalysisDAL.cs`'s `DynamicOutput`/`DynamicQuery` (additive only —
  the pre-existing local-execution code path was not modified).
- Cross-module wiring: `AnalyticsBLL.csproj`/`AnalyticsDAL.csproj` gained `ProjectReference`s to
  `FrameworkBLL` and `SqlWorkbench/SwBLL` (an already-established pattern elsewhere in this repo,
  e.g. `MMBLL`/`TMSBLL` → `FrameworkBLL`).
- `MDATASOURCE` gained `SwClientDatabaseId` (`AnalyticsDAL/DTO/DataSource/DataSourceDTO.cs`,
  `DataSourceQB.cs`, validated as SQL-Server-only in `DataSourceBLL.Save`) — the link that lets an
  Analysis's datasource override point at a SqlWorkbench client database.

### 3.2 Frontend layout
- `gb4.7mfe/projects/analytics/master/catalogwizard/` — `catalogwizard.component.{ts,html,scss}`,
  `dbservice/catalogwizard.db.service.ts`, `model/catalogwizard.model.ts`,
  `service/catalogwizard.service.ts` — selector `gb-analytics-catalog-wizard`, built on the shared
  `gb-wizard` shell (`features/gbwizard/gbwizard.component.ts`), modeled on
  `projects/sqlworkbench/provisioning/provisioning/provisioning.component.ts`'s target-DB-picker
  flow for visual/interaction consistency.
- API registry: `projects/gbhost/public/api/gb5api/analytics.ts` —
  `Analytics.Wizard.DiscoverSchema` → `/ans/Analytics/Wizard/DiscoverSchema`,
  `Analytics.Wizard.QuickBuild` → `/ans/Analytics/Wizard/QuickBuildAnalysis`.
- Existing screens the wizard hands off to for further refinement: `gb-analysis-query-designer`,
  `gb-analysisfields`, `gb-dbjoin` (all under `projects/analytics/master/`) — unchanged by this work.

### 3.3 Remaining completion work (for the feature developer)

Ordered roughly by leverage/impact — this is the authoritative to-do list; §7's gap table is the
status view of the same items.

1. **Apply the two schema migrations to every live database this feature must run against.**
   `DB/Migrations/20260817_Analytics_DBObject_SourceRef_{SqlServer,Postgres}.sql` (widens
   `DBOBJECT`'s uniqueness from name-alone to name+source, confirmed necessary against GB5DEMO's
   live `UKDBOBJECT_DBOBJECTNAME` constraint) and
   `20260817_Analytics_DataSource_SwClientDbLink_{SqlServer,Postgres}.sql` (adds
   `MDATASOURCE.SwClientDatabaseId`) are written and schema-verified but **not yet executed against
   GB5DEMO or any other environment**. Nothing in §2.2/§2.5 can run end-to-end until these are
   applied. (A third migration, seeding four missing `MAUTONUMBER` rows the wizard's ID-generation
   depends on, **has** already been applied live on GB5DEMO — see §7 for why those rows were
   missing.)
2. **Live-verify against a real external SQL Server client database.** No SqlWorkbench-registered
   client database exists on GB5DEMO today, so the entire external-DB path — discovery, quick-build,
   and especially the new execution-routing code in `AnalysisDAL.DynamicOutput` — has been built and
   compiles cleanly but has **not been exercised end-to-end against a real external target**.
   Register one real SQL Server client database in SqlWorkbench and run the full flow before
   considering this feature production-ready.
3. **Regression-check MM/TMS after the DBOBJECT migration lands.** Both modules reference
   `FrameworkBLL`/`DBOBJECT`; a review of their own DAL files found no query keying on
   `DBOBJECTNAME` alone, but confirm their screens still list/save `DBOBJECT` rows correctly once
   the constraint change (item 1) is actually applied to a shared environment.
4. **Wire the frontend wizard into a menu/route.** `gb-analytics-catalog-wizard` exists as a
   component but isn't reachable by an end user yet — this app's routing is menu-driven, so it
   needs a Menu/route entry (and likely a launch point from the Analytics module's existing screens)
   before any user can open it.
5. **Browser-verify the frontend wizard.** Code-complete and reviewed, but not yet click-tested in
   a live browser session against real discovered schema and a real quick-build call.
6. **v2: Postgres/MySQL/Oracle client-database support.** Both discovery (`GetServerColumnsAsync`/
   `GetServerForeignKeysAsync`) and execution (`AnalysisDAL`'s query engine, which is SQL-Server-
   idiomatic throughout — temp tables, `OFFSET/FETCH`, bracket-quoted aliases) are SQL-Server-only
   today by deliberate v1 scoping, not oversight. Needs an Npgsql-based `ITargetDbExecutor` sibling
   plus a dialect-aware rewrite of `AnalysisDAL`'s query builder — a substantial follow-on project
   in its own right, not a quick addition.
7. **Close the DBOBJECT-registration transaction gap.** Registering a newly-discovered table
   (`IDBObjectBLL.SaveDBObject`) is not part of the same transaction as the rest of the wizard's
   build. If a later step in the same build fails and rolls back, a newly-created `DBOBJECT`/
   `DBOBJECTFIELDS` row is not automatically cleaned up (today this is logged loudly as a warning,
   not silently swallowed, but it's still a real gap). Closing it fully would require making
   `SaveDBObject` transaction-composable, mirroring the same pattern already applied to
   `AnalysisObjectDAL`/`AnalysisFieldDAL`/`AnalysisQueryDAL` in this same round of work.
8. **Smarter default-query field layout.** The auto-created report (§2.4) currently places every
   selected column as a plain display column with no measure/dimension inference — deliberately
   simple for v1 so a user always gets *something* runnable immediately. A worthwhile follow-on:
   infer likely measures (numeric, non-key columns) vs. dimensions and default their zones/
   aggregation accordingly, reducing how much a user needs to fix afterward in the Query Designer.
9. **Observability.** Confirm `GB5Trace.Step`/`EventLogPublish`/cache-invalidation discipline is
   fully exercised once item 2 (live external-DB verification) actually runs the code paths that
   exist today only as reviewed-but-unexercised logic.

### 3.4 How other developers & metadata admins put these to use today

**Discovering a database's schema**
- Call `POST /Analytics/Wizard/DiscoverSchema` with `SourceKind=0` (the tenant's own DB) or
  `SourceKind=1` plus a `SourceClientDbId` (a SqlWorkbench-registered client database) to get back
  every table's columns and every foreign key in that database — no SQL, no manual `DBOBJECT` entry
  required first.
- For an external client database, it must already be registered in SqlWorkbench
  (`MSWCLIENTDATABASE`/`MSWDBSERVER`) with a resolvable read-only credential — this feature reuses
  that existing registration/credential system rather than introducing a new one.

**Building an Analysis from discovered schema**
- Call `POST /Analytics/Wizard/QuickBuildAnalysis` with the tables/columns the user picked and
  whichever suggested joins were accepted (the join list itself is computed client-side today from
  `DiscoverSchema`'s raw foreign-key list — see the frontend's `catalogwizard.service.ts` — matching
  the backend's own table-name-based resolution).
- The response's `AnalysisId` can be opened directly in the existing `gb-analysis-query-designer`,
  `gb-analysisfields`, and `gb-dbjoin` screens for any further hand-tuning — the wizard's output is
  a normal Analysis in every respect, not a special/separate kind of object.
- Re-running the wizard against tables already registered is safe — it reuses the existing
  `DBOBJECT` registration rather than duplicating it (see §2.2).

**Pointing an existing Analysis at an external client database**
- Set `MDATASOURCE.SwClientDatabaseId` on a datasource row (via `DataSourceBLL.Save`) to the
  SqlWorkbench client database's ID — this is validated server-side to be SQL-Server-typed
  (rejected otherwise with a clear error).
- Bind that datasource as the `OverrideDataSourceId` on the relevant `MANALYSISWORKSPACE` row for
  the Analysis + workspace in question (GB5's existing per-tenant datasource-override mechanism —
  not new to this feature, just newly *consumed* by report execution).
- From that point on, running the Analysis's report transparently executes against the external
  database — no special "external mode" flag or different endpoint to call.

---

## 4. For Implementers / Admins — What's Configurable

**Configurable today through the wizard itself, no SQL/direct-insert needed (once the frontend is
routed — see §3.3 item 4):**
- Which tables/columns of a database become part of an Analysis.
- Which joins are used between those tables (suggested automatically, editable before save).
- The Analysis's name/code and whether a default runnable report is created alongside it.

**Requires the backend migrations (§3.3 item 1) to be applied to an environment first:**
- Discovering/registering tables from more than one distinct database (own DB vs. any external
  client DB) without name collisions.
- Linking a datasource to a SqlWorkbench client database at all (`SwClientDatabaseId`).

**Requires engineering involvement (not yet self-service):**
- Registering the external client database itself in SqlWorkbench in the first place (a separate,
  pre-existing SqlWorkbench admin task, not new to this feature).
- Reaching the wizard UI at all until it's wired into a menu/route (§3.3 item 4).
- Anything involving a non-SQL-Server external client database (§3.3 item 6) — not supported yet
  regardless of admin effort.

**Rollout checklist for enabling this for a client/tenant:**
1. Confirm both schema migrations (§3.3 item 1) have been applied to the target environment.
2. If the client wants to report against their own external database: register it in SqlWorkbench
   first (server + client-database entries, role-scoped credentials), and confirm it's a SQL Server
   database (v1 scope).
3. Confirm the wizard's frontend route/menu entry exists for the users who'll use it (§3.3 item 4).
4. Walk a pilot user through: pick source → browse tables → confirm joins → build — verify the
   resulting Analysis opens correctly in the existing Query Designer.
5. For an external-DB analysis specifically: confirm the report actually returns rows from that
   external database (not silently falling back to the tenant's own DB) before treating the
   external-DB capability as live for that client.
6. Set expectations: this is a v1 scoped to SQL Server external databases only, and to the wizard's
   own simple default report layout — further layout tuning happens in the existing Query Designer,
   not the wizard itself.

---

## 5. For End Users — Roles & Journeys

- **Pick a source**: choose "our own data" or a specific external database your organization has
  already connected, from a simple list — no connection strings or technical setup involved.
- **Browse and pick**: see the real tables and columns in that database and select exactly the ones
  you need for your report.
- **Confirm joins**: review a suggested way to connect your selected tables (found automatically
  from how the database itself defines its relationships) — accept, tweak, or skip each one.
- **Name and build**: give your analysis a name, click build, and get a ready-to-run report
  immediately — with the option to keep refining exactly which columns show where using the
  existing Analysis designer, without starting over.
- **Power users**: once a wizard-built Analysis exists, use the existing Analysis Query Designer for
  anything the wizard's simple default layout doesn't already cover (totals, filters, custom
  display arrangement) — the wizard is a fast starting point, not a ceiling.

---

## 6. For Marketing / Sales

**Lead with what's real, working, and differentiated:**
- **From raw database to runnable report in a few clicks**, with no SQL knowledge required —
  removes the traditional dependency on a technical admin or consultant for ad-hoc reporting needs.
- **Joins are discovered, not guessed.** The wizard reads the target database's own real foreign-key
  relationships and proposes the correct join automatically — a level of automation most competing
  "report builder" tools leave entirely to the end user.
- **Not limited to data already inside the product.** A client's own external database, once
  connected through GB5's existing database-management tooling, becomes just as reportable as GB5's
  native data — and reports against it return real, live data from that external system, not a
  static copy or a definition that only "looks" connected.
- **Built on, not around, GB5's existing reporting engine.** A wizard-built analysis is a completely
  normal Analysis the moment it's created — it opens in the same Query Designer every other analysis
  uses, so there's no separate, parallel "wizard-only" system for a client to learn or get stuck in.

**Do not yet promise** (see §7 for exact current status of each):
- Reporting against external databases of any type other than SQL Server (PostgreSQL/MySQL/Oracle
  client databases aren't supported for this feature yet).
- A fully self-service path with zero engineering involvement — the two schema migrations and the
  frontend's menu/route wiring are still outstanding (§3.3 items 1 and 4).
- Smart, automatic measure-vs-dimension detection in the wizard's default report layout — today's
  default is a simple, safe starting point, not an intelligent one.

---

## 7. Known Gaps / Roadmap Items — [Internal only, not for marketing]

| Area | Status |
|---|---|
| `DBOBJECT` source-disambiguation migration | Written and schema-verified against GB5DEMO's live `UKDBOBJECT_DBOBJECTNAME` constraint (confirmed it would otherwise block registering a same-named table from two different databases) — **not yet applied** to GB5DEMO or any other environment. |
| `MDATASOURCE.SwClientDatabaseId` migration | Written and schema-verified — **not yet applied** to any environment. |
| `MAUTONUMBER` seed rows for `MANALYSISOBJECT`/`MANALYSISFIELDS`/`MANALYSISQUERY`/`MANALYSISQUERYFIELDS` | **Applied live on GB5DEMO.** These four tables already held real historical data (400/12,457/60 rows respectively) despite having no ID-generation counter registered at all — a pre-existing gap unrelated to this feature, discovered and fixed as a prerequisite for the wizard's own ID allocation to work. |
| External-DB end-to-end live verification | Not yet done — no SqlWorkbench-registered SQL Server client database exists on GB5DEMO today to test against. Both the discovery and execution-routing code paths compile and were reviewed, but have not been exercised against a real external target. |
| Non-SQL-Server client databases (PostgreSQL/MySQL/Oracle) | Explicitly out of v1 scope for both discovery and execution — needs a new `ITargetDbExecutor` implementation and a dialect-aware rewrite of `AnalysisDAL`'s query builder, a substantial follow-on project. |
| `DBOBJECT` registration transaction gap | Registering a newly-discovered table is not part of the wizard's main save transaction; a later failure/rollback in the same build does not undo a just-created `DBOBJECT`/`DBOBJECTFIELDS` row (logged as a warning, not auto-cleaned). |
| Frontend menu/route wiring | `gb-analytics-catalog-wizard` component exists but is not yet reachable via any menu or route. |
| Frontend browser verification | Code-complete and reviewed; not yet click-tested live against real discovered schema and a real quick-build call. |
| Default report layout intelligence | Every selected column becomes a plain display column by default — no automatic measure/dimension inference yet. |
| MM/TMS regression check post-migration | No query keying on `DBOBJECTNAME` alone was found in either module's own code, but this should be re-confirmed once the `DBOBJECT` migration (row 1 above) is actually applied to a shared environment. |

---

*Compiled 2026-08-19 from the actual implementation work in this session — code inspection, live
schema verification against GB5DEMO (via direct `sqlcmd` queries against
`INFORMATION_SCHEMA`/`sys.*` catalog views), and the final committed/pushed state of both
repositories (`gbBE/gb5` `dev` @ `6075de33a`, `gb/gb4.7mfe` `GBDEV4.7` @ `7dfb9a722`). Re-verify
against current code before reuse if significant time has passed — several items in §7 are
mid-remediation and may have changed, especially once the pending migrations (rows 1–2) are
applied.*