MDS subscription views: the integration contract nobody wrote down

An MDS subscription view is a plain SQL view in the mdm schema of the MDS database that publishes one entity, hierarchy or collection as a flat result set. You created it once in Integration Management, gave it a name, pointed an SSIS package at it, and never opened that screen again. It is the contract between Master Data Services and everything downstream, and in most estates it is the only part of the implementation that was never written down.

Migrations handle the parts you can see. Entities move, attributes move, hierarchies move, and you can count rows on both sides to prove it. The views are what breaks. Recreating the view object is the easy half; the hard half is the query somebody wrote against it in 2017, inside a job you cannot open, owned by a team that has been reorganised twice since.

What is actually in one

A subscription view definition is four choices: a model, a version or version flag, an entity, and a format. The format decides the shape of the output:

• Leaf members. One row per member, one column per attribute. This is the format almost everyone uses.
• Consolidated members. The parent nodes of an explicit hierarchy, on their own.
• Collection members and collection attributes. For the collections feature, which in practice almost nobody adopted.
• Parent-child and levels. Two ways to flatten a hierarchy: one row per edge, or one row per member with the ancestors spread across columns. Available for both explicit and derived hierarchies.

A leaf view opens with a fixed block of system columns before it gets to anything you modelled: Code, Name, the version name and number, who entered the member and when, who last changed it and when, and the validation status. Then one column per attribute.

Domain-based attributes come through as the referenced member's code rather than an internal row ID, which is the single most useful thing subscription views do. It is also the reason they became the default extraction route for so many teams: a SELECT * gives you something a person can read, which is more than the MDS internal tables ever offered.

The version binding that fails quietly

When you create the view, MDS asks whether to bind it to a version or to a version flag. The difference matters exactly once, and by then it has usually already cost you something.

Bind to a version and the view is nailed to that version number forever. Copy the version, which is what MDS wanted you to do at every cycle close, and the view carries on publishing the old one. Bind to a flag and the view follows whichever version currently carries the flag, so the copy is invisible to consumers.

The failure mode is the expensive part. A view pinned to a stale version does not error and does not go empty. It returns a full result set of last cycle's data, at the right row count, with the right column names. Everything downstream loads cleanly. The numbers are just wrong, and they stay wrong until somebody notices a cost centre that was closed months ago is still receiving postings. Of everything in this post, this is the one I would go and check today rather than at migration time: open Integration Management and look at the binding on every view in the list.

Finding out who actually reads them

MDS holds the view definitions. It holds nothing at all about who consumes them, which is the gap that produces go-live incidents. Start with the list of objects:

-- Every subscription view, with its definition
SELECT  v.name AS ViewName,
        m.definition
FROM    sys.views v
JOIN    sys.schemas s  ON s.schema_id = v.schema_id
JOIN    sys.sql_modules m ON m.object_id = v.object_id
WHERE   s.name = 'mdm'
ORDER BY v.name;

Then find the callers. The plan cache is the fastest first pass, and it costs nothing to run:

-- Recent queries that touched a given view
SELECT TOP 50
        t.text,
        s.execution_count,
        s.last_execution_time
FROM    sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE   t.text LIKE '%vw_Finance_CostCentre%'
ORDER BY s.last_execution_time DESC;

Do not mistake that for an inventory. The plan cache empties on restart and evicts under memory pressure, so it shows you the busy consumers and hides the quiet ones. The quiet ones are the problem: the quarterly regulatory extract, the month-end reconciliation, the annual pricing refresh. Those are exactly the jobs that will not appear in a Tuesday afternoon sample and will fail six weeks after you have declared the migration finished.

For a real list, run an Extended Events session filtered on the view names across a full period close, and leave it running for a month if the calendar allows it. Then take the names it produces to the teams that own those service accounts and ask what the job does. Half of them will not know the view is involved.

They do not respect your model permissions

The permission model you configured in MDS (functional areas, entity and attribute permissions, hierarchy member permissions) governs the web application and the Excel add-in. It has no bearing on a subscription view, because a subscription view is an ordinary database object and access to it is a SQL Server permission.

Anyone in db_datareader on the MDS database reads every attribute of every member, including the ones you carefully hid from them in the UI. Most teams find this during an audit rather than during design. Carry it into the migration as a requirement instead of repeating it: restrict the sensitive attribute on the object you publish, not only on the screen people log into. The permission model does not come across in a deployment package either, so both halves of this need rebuilding by hand.

What replaces them

Split the consumers into two groups, because they need different answers.

Systems that can reach the SQL Server keep querying SQL views. In Primentra these are called integration views. You create one per entity from Settings, choose flat or hierarchy, exclude any columns the consumer should not see, and it is deployed as a real view into the mdm schema. Same schema name MDS used, and the view name is yours to choose, which matters more than it sounds like it should.

The difference worth knowing before you plan the cutover is what each type does with domain-based attributes. A flat view covers one entity and leaves a domain attribute as the referenced row's ID. A hierarchy view resolves it, joining in the parent's code, name and attributes as extra columns. If your consumer expects the MDS behaviour of reading a code rather than an ID, the hierarchy view is the one you want.

There is also a difference MDS did not have. When someone renames an attribute or changes its type, the deployed view no longer matches the entity behind it. Primentra detects that drift, marks the view in the list, and offers a sync that redeploys it. In MDS, a changed entity meant regenerating the view by hand, and a view that stopped carrying a new attribute was found by whoever noticed the column was always null.

Systems that cannot reach the database use the REST API. GET /api/v1/entities/:id/records returns the same records as paged JSON against an API key, with domain attributes carried as both the raw reference and a resolved display value. That covers the cloud consumers a subscription view could never serve, and unlike the views it also has a write path.

Keeping the old contract alive at cutover

You do not have to renegotiate every downstream query in the same weekend you move the data. Because the schema is mdm in both systems and you choose the view names, the object a consumer selects from can stay exactly where it was.

Column names are the part that does move. Primentra prefixes each column with the entity's table name, so Code arrives as CostCentre_Code. A thin alias view puts the old contract back:

-- Primentra deploys the hierarchy view with prefixed columns.
-- This preserves the column names the old consumers expect.
CREATE OR ALTER VIEW mdm.vw_Finance_CostCentre AS
SELECT  CostCentre_Code  AS Code,
        CostCentre_Name  AS Name,
        CostCentre_Owner AS Owner,
        Company_Code     AS Company
FROM    mdm.vw_CostCentre_Hierarchy;

Alias views are scaffolding. They buy you the ability to move the data on one weekend and update the consumers on a schedule that suits the teams who own them, one at a time, with a rollback that is a single CREATE OR ALTER. Write down which alias views exist and which consumer each one is waiting on, or you will have reinvented the undocumented contract you just spent a month untangling.

One thing the alias cannot fake is the system columns. If a consumer reads VersionNumber or ValidationStatus, there is nothing to alias them to, because Primentra has no model versions and validates on the way in rather than stamping a status on the row. Those consumers need a real change, and finding them is a good reason to read the view definitions rather than assume every job just selects the columns it was told about.

The order to do this in

1. Script out every view in the mdm schema, definitions included, and put the file in source control. This is the contract, and it is currently stored in a database you are about to decommission.
2. Record each view's version binding while MDS is still running. A view on a stale version is publishing wrong data today, migration or not.
3. Build the consumer list. Plan cache for the quick pass, Extended Events across a period close for the real one, then confirm with the owning teams.
4. Snapshot every view to CSV. A CSV opens in five years; a view in a decommissioned instance does not.
5. Create the replacement views, choosing hierarchy where the consumer relies on codes rather than IDs.
6. Add alias views for anything you are not ready to change, and track them as open items with an owner.
7. Cut over, then compare row counts and a sample of values view by view before anyone runs a load against them.

Common questions