Build a reporting database

Goal

Build the business layer for a repository: tables named and shaped by the modeling technology that governs it, delivered in the way your estate allows, checked against the repository, and kept fresh. This is for an enterprise architect, reporting developer or data steward who answers the same portfolio questions repeatedly, feeds Power BI, or must give a figure that can be defended. You end with a saved reporting profile; the business layer as views in the EA database, as a separate reporting database, or as the rows for Parquet files; a definition file; the result of the alignment and value checks; and an account of what is outside the layer.

Where it runs

This scenario needs the MCP server, and local code execution to create the reporting profile. Computer use covers its UI steps.

What it builds

An EA repository stores everything as generic objects. A stereotype is applied through a separate binding record, and business attributes are name and value rows. There is no "applications" table. The business layer adds one, and every other table your technology implies, over EA's own data:

Names are exactly the technology's: no prefix stripping and no change of case. Every element the inclusion choice admits is in at least one entity table, and every entity table carries the package path, so a report can slice by package. The tables are the same, with the same names, columns, types and rows, whichever way they are delivered, so a report built on one way works on another. On the Westbrook reference model, the layer has 18 entity tables and 23 connector tables, built in under 3 seconds, with 1,456 cells equal to EA. That is an illustration of one small model, not a figure to expect.

Tag values appear as EA shows them. Of two tags with the same name on one element, the first is used. A memo tag is read from its notes, a declared Boolean is 1 or 0, numbers are accepted in plain form only (anything else is empty on a number column, and the value check reports it), and dates read in the machine's locale are stored in ISO form. EA writes an empty value and no value identically, so a blank is a blank.

The three ways the layer is delivered

Where the tables live is a fact about your estate, not a preference. The skill asks three questions, in order:

1. Can you reach the EA repository's database directly, is it SQL Server, and may you create views in it?
2. If not, can you run a SQL Server, or do you have a Microsoft Fabric capacity, for a separate reporting database?
3. If not, Parquet files.

The AI puts all three ways to you at once, each with its consequence, and waits for your answer.

Which ways your repository allows depends on how EA connects to it:

Someone who could use views in the EA database may still choose another way: to keep views out of the production database, or to keep left-out areas away from Power BI. All three ways leave out the packages you exclude.

Before you start

• EA is running with the model open, and the modeling technology is readable in the session. The business layer needs a modeling technology. With none, the AI says so and does not build. Installed, embedded, loaded and enabled are different states; see ea-mdg-assess.
• Local code execution, with Python 3 on the machine, for the census, the inclusion choice and the governance gate (see Supported clients). These use Python's standard library only. The build itself runs inside the AI Power Tools MCP Server for Sparx EA.
• For views in the EA database: SQL Server, reached with Windows authentication as the account that runs the AI Power Tools MCP Server for Sparx EA (there are no SQL logins). Windows' own SQL Server provider is enough; the Microsoft OLE DB Driver for SQL Server is used when it is installed. Use a current, supported SQL Server version; confirm it with your database administrator.
• For a separate reporting database, one of two places (see Where the reporting database runs in ea-reporting-database):
  • A SQL Server you run, reached with Windows authentication exactly as above.
  • A SQL database in Microsoft Fabric, reached with Microsoft Entra sign-in. The workspace that holds it is on a Fabric capacity that is not paused. Create the database in the Fabric portal first, because AI Power Tools cannot create it there, and copy the server name and the database name from its connection strings; the database name is the display name followed by the item id. The Microsoft OLE DB Driver for SQL Server must be installed on the machine where the server runs. A person signs in with a Microsoft work account when the first operation opens the database, once per server process.
• For views in the EA database: AI Power Tools looks for EA's tables in the dbo schema, and the account needs the right to create views, to create the schema (if it does not exist yet) or alter it (if it does), and to read EA's tables. The build checks these first, names each one that is missing, and changes nothing until you have them.
• For a separate reporting database: the account must be able to create the database if it does not exist (on SQL Server), then create schemas, tables, indexes and views, load rows and move tables between schemas, which in practice is likely to mean owner-level rights on that database. These rights are not checked before the build starts, so a missing one shows up as a failed build that leaves the previous data in place. The database name may use letters, digits, _ and - only, and the database must not be one that holds an EA repository.
• A schema of its own. The default schema is logical. A build drops every view already in the schema before recreating the layer, and removing the layer drops them all, so give the layer a schema that holds nothing else. System and reserved names (dbo, sys, guest, information_schema, staging, retired and the database role names) are refused.
• A scope: the root packages to report on, and the packages to leave out. Exclusion is by whole package. There is no masking: notes and tagged values in a kept package all reach the output.
• A baseline of anything you may change. The governance gate's stereotypes and the fix for a blocking connector are the only model changes in this scenario.

Steps

1. AI with computer use; otherwise you If EA is not running or the model is not open, start EA and open it. AI Power Tools ea-start-here runs the session preflight.
2. AI Power Tools ea-reporting-database reads the technology from the running session (get_mdg_from_runtime, Discovering technologies). AI It confirms that EA answered from the loaded technology, and stops if there is none.
3. AI ea-reporting-database puts the three delivery ways to you, with their consequences. You answer from the facts of your estate.
4. AI Power Tools ea-reporting-database reads the package tree with direct queries (Direct queries). You choose the root packages and the packages to leave out.
5. AI Power Tools ea-reporting-database reads the in-scope elements, connectors and their stereotype bindings with direct queries. Engine The census counts elements and connectors by the stereotype they are bound to, and the analysis compares them with the technology.
6. AI with computer use; otherwise you If any connector carries a qualified stereotype name with no binding to the technology, stop and fix it in EA, after a baseline. Engine The census runs again. No profile is written while any remain.
7. You Elements that carry two of the technology's stereotypes are flagged. Decide whether to continue (each is then in both tables) or fix them in EA.
8. AI ea-reporting-database puts the inclusion choice to you, with the counts for each answer. You answer, and, if you choose gap by gap, decide each gap.
9. AI ea-reporting-database writes the reporting profile, with your answer and a record of it. The profile is a local file: where the assistant can write local files it does so, otherwise it gives you the content to save.
10. Engine The governance gate lists the in-scope elements whose stereotype is empty (Find ungoverned elements). You decide per element. AI Power Tools To apply a stereotype, ea-change-management takes a baseline and ea-reporting-database applies it; Engine the census confirms the element is placed.
11. AI Power Tools For a separate reporting database or Parquet files, ea-reporting-database runs the reporting preflight (build_reporting_database with the preflight only), which writes nothing. AI with computer use; otherwise you fix anything that blocks the build, in EA. You decide on each flag.
12. AI Power Tools ea-reporting-database builds: build_business_layer for views in the EA database, build_reporting_database for the other two ways (Reporting business layer). You For a SQL database in Microsoft Fabric, sign in when Microsoft's sign-in window comes to the front; later operations in the same server process reuse the sign-in. AI It reads the timings aloud: they are the only sizing evidence for your repository.
13. AI Power Tools For either database way, ea-reporting-database checks the layer (check_business_layer). AI It reads the result.
14. AI ea-reporting-database reports four things, in order (see below).

Steps 5 to 8, the inclusion choice. The layer always holds what the technology declares. What it holds beyond that is your decision, made once, shown with counts, and saved in the profile. There are three answers:

• Only what the technology defines.
• Everything in the repository: a table for every observed stereotype, or for every element type where an element has no stereotype.
• Gap by gap: each stereotype or connector kind the technology does not declare is shown with its count and an example name, and you include it, exclude it, or send it to the modeling-language skills to be declared. Sending it there is a hand-off to ea-mdg-model-build followed by a fresh census, not an answer; the profile is not written while any gap is undecided.

A stereotype counts as the technology's only when it is bound to the technology. The same name carried ad hoc, or bound to another language, is a gap. The AI asks every time a profile is created. Two things are data defects rather than gaps, and are fixed in the repository: a connector whose stereotype is qualified text with no binding (for example a connector stereotype stored as WBA::Uses with no binding), and an element carrying two of the technology's stereotypes.

Step 9, the reporting profile. One file you keep, which every build, check and refresh reuses. It holds the technology the layer follows; the scope, as root packages to include and packages to leave out, both by identifier, so a renamed or moved package keeps its setting; the inclusion choice and its record; and where the output goes. List every root package you do not want reported among the packages to leave out. A root package named in neither list is flagged on every run, because the flag exists to catch packages created after the profile was saved.

Step 11, the reporting preflight. It reads the copy of EA's tables, applies the scope, and reports the rows that would be kept, the links that cross into a left-out area, and the "(excluded)" stand-ins those links need: a row for the far end of a kept link, showing the element type and the name "(excluded)", never the real name. Then two lists:

• What blocks the build: a connector whose stereotype is stored as qualified text with no binding to the technology. Fix it in EA by re-applying the stereotype from the technology's toolbox, or through the stereotype property, after a baseline. Then run the reporting preflight again.
• What you decide before continuing: elements carrying two of the technology's stereotypes; a root package created since the profile was saved, which is left out until you add it; a profile entry that matches no package; and elements with two tags of the same name.

For views in the EA database, the rights are checked first, and the census in step 5 finds unbound connectors.

Step 12, the build. For views in the EA database, the build checks the rights, then recreates every business view and the scoped views of EA's tables in the profile's schema, inside one transaction. It never writes to an EA table. For a separate reporting database, it reads EA's tables through EA in ranges, cuts the left-out packages in memory, loads a staging copy, and swaps it in inside one transaction, so a reader never sees a half-built database; then it builds the views. For Parquet files, it computes the business tables' rows; the files themselves are written in Build a Power BI model. Every build:

• writes the definition file beside the profile: every table with its columns, declared types and row counts, plus the Table Directory and EA's own tables, so the Power BI generator needs no database connection;
• for a separate reporting database or Parquet files, keeps the extract that was loaded, beside the profile, replacing the last one (Rebuild a report without Enterprise Architect); left-out content is never written to disk;
• is a full rebuild with no history; anything deleted or moved in EA follows. A failed or stopped build leaves the previous data live.

Step 13, the two checks. The alignment check measures the layer against the technology's metamodel, in counts: every allowed combination has a table; every bound connector whose two ends are placed lands in a combination; each connector table's row count equals the definition's own placement; elements in two tables are listed. If only the count of connectors "outside every allowed combination" is above zero, the build is correct: those connectors use a declared connector stereotype between two element stereotypes the technology does not allow together. The AI names each such combination and its count, and you either add the combination to the technology or fix the model. A missing combination table, or a connector-count mismatch, is a defect in the build: stop and report it. The value check, on by default, compares every name, package path, tag column, connector end, base type and connector tag with an independent read through EA, and reports the cells compared and the differences. EA must have the same repository open. A check that compared no cells is a failure, never a pass. The value check applies to the two database ways; a Parquet build returns the same alignment result with the build.

Step 14, what the assistant says, in order: (1) which delivery way, and whether the data is live or a snapshot, with its as-of time; (2) whether the layer was checked, and the result; (3) what is outside the layer: left-out packages, elements the inclusion choice left out, the "(excluded)" stand-ins, and every flag you continued past, so a reader comparing a table count with EA's own count hears the difference first; (4) what was built: the tables, row counts, the definition file and the timings. It never reports a figure from a table without saying how populated the columns behind it are.

Reading the tables. An element carrying two stereotypes is in two tables and is counted in both, and so is a connector that ends on it. A text tag holding several values separated by commas is one value in its column, so test it with "contains", not "equals". Only a declared list-of-references tag is split into rows; a checklist tag stays a column.

Prompts to use

Opening:

Follow-ups:

• Inclusion: "Run the census over the package and put the three inclusion choices to me with the counts, then show me the gaps one by one."
• Gate: "Run the governance gate and show me what will be missing before you write the profile."
• Preflight: "Run the reporting preflight on my saved profile. Write nothing. Tell me whether anything blocks the build and what I have to decide."
• Check: "Check the business layer: is it aligned with the WBA metamodel, and how many cells differ from EA?"
• Report: "Tell me what is outside the layer and why, then how many business applications are in scope for GLBA, with how complete that tag is."
• Cloud: "Build the reporting database into our SQL database in Microsoft Fabric with Microsoft Entra sign-in. Tell me what I have to set up in the Fabric portal first, and when I will be asked to sign in."

Recommended models

Ratings are guidance based on the kind of reasoning the scenario needs, not measured benchmarks.

Checking the result

• Correct. The alignment check is aligned, or the only non-zero count is connectors outside every allowed combination, each explained. The value check compared more than zero cells and found zero differences. A spot-check of one entity table against EA (counting WBABusinessApplication elements in the scope) agrees, with the as-of time in mind for a snapshot. Red flag: a result reported as checked with zero cells compared.
• Complete. Row counts per table agree with EA's own counts, and the AI explains every gap before you find it: left-out packages, elements the inclusion choice left out, elements declined at the gate, elements counted in two tables, and the stand-ins. The Table Directory lists a connector table for every combination the technology allows. Every figure comes with its populated count.
• Nothing unintended changed. No EA table was altered. For views in the EA database, only the layer's own schema changed. If you applied stereotypes or fixed connectors, a baseline comparison shows only those changes.

Variations

• One package. Make it the only root package in the profile. The scope includes everything under it, however deep.

• Keep it fresh in the background. A refresh is started with one call (start_reporting_refresh), which returns a run id at once; a second call (get_reporting_refresh) reports it as running, succeeded or failed, and when it has finished gives the row counts per table, every flag, the alignment result and the timings, or the reason it stopped. A scheduled AI agent, for example a Claude Cowork scheduled task connected to the machine where EA runs, can start it, follow it and report on it. What a refresh does follows the profile's delivery way:

  • Views in the EA database: the data is already live, so a refresh only regenerates the views, which is how a changed technology or inclusion choice takes effect.
  • A separate reporting database: the reporting preflight, the extract, the cut, the load, the swap-in and the views, as in a build.
  • Parquet files: the rows and the definition file are rewritten, but not the Parquet files or the Power BI project, which wait for the next run of the step in Build a Power BI model.

  EA must be running with the repository open on that machine. A run that fails, or that its reporting preflight stops, leaves the previous data live and says why. A second refresh of the same profile while one is running is refused, and names the run already in progress. Run ids are kept only until the AI Power Tools MCP Server for Sparx EA restarts, so keep the server running until the run ends. A refresh into a SQL database in Microsoft Fabric needs a person at the machine to complete the Microsoft Entra sign-in when the server process first opens the database. A refresh reuses the saved inclusion choice and does not ask again, but it flags drift: stereotypes and connector kinds that are not in the technology and that your gap-by-gap answer neither included nor excluded, and a changed technology version. When drift is flagged, run the inclusion choice again. Never schedule the refresh with Windows Task Scheduler.

• Remove the layer. remove_business_layer drops every view in the profile's schema, then the schema, and reports how many views it dropped. For views in the EA database, that removes AI Power Tools' entire footprint there. It never touches EA's tables or a reporting database's copied tables. A Power BI project built on the layer stops refreshing until the layer is rebuilt from the saved profile, so keep the profile; the AI says so before removing.

• A reporting database in the cloud. Choose a SQL database in Microsoft Fabric as the reporting database, with Microsoft Entra sign-in. The tables, checks, refresh and replay are the same as on a SQL Server you run. A model published to the Power BI service refreshes from it with no gateway (Build a Power BI model).

• Settle a disputed figure later. Replay the last build from its kept extract, with no EA connection: Rebuild a report without Enterprise Architect.

• Power BI. Continue with Build a Power BI model, which generates the Power BI project from the definition file.

• A large shared repository. Rely on the timings each build reports.

Limitations

• A modeling technology is required. Without one there is no metamodel to build the layer from.
• SQL Server for views in the EA database, with Windows authentication only. A SQL Server you run, or a SQL database in Microsoft Fabric, for a separate reporting database: Windows authentication on SQL Server, Microsoft Entra sign-in in Fabric. Another database engine cannot hold either; the connection fails.
• The Microsoft Entra sign-in needs a person. It happens once per server process, in Microsoft's sign-in window, and an unanswered sign-in stops the operation after about ten minutes without writing anything. There is no unattended sign-in.
• Exclusion is by whole package, with no masking. Everything in a kept package reaches the output.
• Snapshots for a separate reporting database and Parquet files: rebuilt in full, with no history. Only the last build's extract is kept, and it holds the content you chose to include, so treat it like the reporting data.
• A dedicated schema. A build and a removal drop every view in the profile's schema.
• Read-only mode. With the AI Power Tools MCP Server for Sparx EA in read-only mode, building or removing the views is refused, for either database. Building or replaying a separate reporting database is not refused, and a refresh is refused only for views in the EA database.
• A comma-joined text tag is one value, and a checklist tag stays a column.
• A call that appears to hang is usually a dialog box in EA. With computer use, the assistant sees the dialog in EA's window, reads it, uses the message to correct the call, dismisses the dialog and keeps going. Without computer use, you take a screenshot of the dialog and paste it into the conversation so the assistant can analyze it, then you dismiss the dialog so processing can continue.