oslo repository design pattern - why tables + views?

Question

I have looked through the "M" and repository documentation on msdn but didn't find anything covering this particular part. What are the specific reasons/considerations behind the "table-behind-view-in-front" model?

The only two reasons that I can imagine are:

1) To allow lock hints to be included in the view (as they are in the code currently generated when compiling M into tsql). If this is the reason, is there (or will there be) a way to specify which lock hint to use on a per-entity level? (I may want to default to readcommitted on some tables, readuncommitted on others, and rowlocks on yet others).

2) For supporting the security features (i.e. column level permissions and the calls to SessionMayReadFolder) also included in the generated T-SQL DDL. However, the same security checks [/triggers] and column permissions can be applied directly on the table itself without the overhead of views (which will add additional work for the optimizer on complex queries).

---

Kris

Answer 1

You can find information regarding this design pattern in the "Models Help" document that installs with the Microsoft “Oslo” Software Development Kit (SDK).

In this document you will find sections covering "Oslo" repository design patterns in general, and tables and views patterns in particular.

You can download the Microsoft "Oslo" SDK from the Oslo Developer Center

Answer 2

Thanks. I installed the SDK yesterday but didn't notice those documents so I only read the documentation on msdn.microsoft.com.

After reading the document you referred to it is more clear that the design patterns has more to do with "cloud" compatibility than the reasons I first thought of. Although the cloud compatibility part is not spelled out explicitly but I would imagine that the inflexible rules and strict schema and access rules at DB level must be WindowsA..-related.

(Off topic: who came up with the "WindowsA.." name for the cloud services?)

---

Kris

Answer 3

The main reason for having access go through updatable views is to support schema evolution. In principal, as a schema evolves views can be retargeted to the new schema thus minimizing the impact to applications etc that use the repository.

A side benefit is that you can do row level security. This allows access to data in the repository to be controled in such a way that users only see a subset of the rows in a view (e.g. just those related to the Org Chart application but not those for the General Ledger application).

The traditional approach to schema evolution and row level security is to use stored procedures but these do not work well with reporting tools etc. It is a design goal of the repository to work well with tools like Excel and Reporting Services.

Answer 4

Anthony C Bloesch said:

The main reason for having access go through updatable views is to support schema evolution. In principal, as a schema evolves views can be retargeted to the new schema thus minimizing the impact to applications etc that use the repository.

Ok, thanks.

Will the oslo tools support schema evolution or is that something that has to be handled using traditional data modelling tools?

E.g. schema versioning, diff/migration scripts, generation of the backwards compatibility views etc?

---

Kris