SQL Server View Based Pages

 
For a further look into Read Only Page, don't forget to go back and watch the two webinars we aired: 

A "Read Only Page" is a page based on a SQL Server Database View. This is in contrast to most pages in MP that are based on SQL Server Tables. Because they are based on SQL Server views, you cannot edit the data that you see on these pages. Security roles can only be granted "read" rights to these pages.

Want to create a Read Only Page? Sometimes that's a great idea! And sometimes, not so much. Keep reading to learn how to discern the difference. As always, the first step is to identify the problem you are trying to solve before determining the solution method. We strongly recommend working with our Professional Services team to determine the right approach for your unique goals.
Advantages & Use Cases

Why would you use a read only page instead of a typical page based on a SQL Table, which would allow authorized users to edit the data on that page?

  • Read Only Pages can have "summary" fields right on the form. So, a value like "total giving last year" can be calculated in real time and put on the form.
  • Read Only Pages can "pull" data into the summary fields from across the entire database. We can have a value like "salvation date" from a milestone right below a field titled "first attended" and followed by a field called "first joined a group." These three fields might create a clear picture of someone's progression and that is often desirable in a summary dataset.
  • Summary fields on read-only pages can be used in charts on that page. Charts cannot be based upon "correlated sub queries" which are often used in views on other pages to summarize data.
  • Summary fields on read-only pages can be used in mail merge emails (if contact_ID_Field is populated)
  • If created correctly, they can leverage existing tools and reports. Contact_ID_Field is needed.
  • If created correctly, they can leverage security roles and global filters (Congregation Filters). Global_Filter_ID_Field is needed.
  • Read Only Pages can create "virtual records." So, for example, if you wanted a table that has a row per month and a bunch of facts about that month, you don't necessarily need a database table containing months, you can summarize facts in any table that has a date by month. The selections on such a Read Only Page may be unreliable.
  • Data from Read Only Pages can be consumed as read-only data from the REST API.
  • Read Only Pages that contain the proper foreign keys can have editable sub pages. While you can't edit the page itself, you could edit lots of things in sub pages.
  • Read Only Pages can have foreign keys from a variety of tables which means sub pages not normally found together can be put under the same open record from a read only page.
  • Merging two datasets with use of a Common Table Expression (CTE) is another major case. For example, really analyzing someone's past history as a servant/leader. We need to summarize and often join a very particular dataset with "n" rows per person, but the actual read only page should only display one row per person. So, answering questions like "have they served, how long since, where, how often, etc" becomes possible in advanced searches that are simple to for end users to use. Correlated sub queries and custom SQL in view clauses can be "re-used" as they are moved one layer deeper in the stack.
  • Data on the form can be organized precisely according to the needs of the target audience of that read only page. Making someone use a filtered page means they still currently see all fields in the same order as the page it is based upon.

When do these Read Only Pages make sense?

  • The singles minister wants to really understand how singles are involved at the church and be able to communicate with them strategically.  He has 10 questions about their involvement (serving, giving, in groups, has kids, etc). We can deliver a view of just active single adults with just the information he wants and serve up just the charts he cares about.
  • The membership department needs a lot of summary data about the church population by age groups. While we could deliver this as a report, a Read Only Page will allow us to leverage the resulting dataset for charts. It also ensures the dataset we use for any reports can be based upon the same underlying view.
  • A third party writing an application who will use the API needs to be restricted to a subset of donor data. The subset must be guaranteed not to include donor identifying information or donations outside their scope. A Read Only Page can limit the data access at the source rather than doing this via a stored procedure (the other reasonable option today). This gives the church visibility to the data from within MP that is actually being extracted. That increases confidence and surfaces issues.
  • A consultant is encouraging a church to write a very complex nightly routine to "snapshot" data into a redundant table structure to merge facts about people into one page.  A Read Only Page based on a SQL View can eliminate this very "heavy" operation nightly and make data real time as opposed to 24 hour delay.

What are some drawbacks to read-only pages?

  • The advanced search will not have folders you can expand to get to other fields on other tables because views do not have Foreign Keys defined even if the value of the Foreign key is present. So, all fields you want the user to see must be on the form.
  • The Xfer button will not work (but the Transfer Selections tool might).
  • It is possible to make a read only page where selections are reliable from one session to another, but it is also possible that selections might not be reliable. They will be reliable if the person who wrote the SQL Server View ensured there is a unique primary key per record and that records which qualify to be in the SQL View always have the same primary key.

Choosing the Right Approach

The Platform offers a lot of solutions for working with data:

  • The Page
  • The Page View (saved search in the Platform)
  • The Filtered Page
  • The Read Only Page (based on SQL Server views)

One or more of these can be paired with Routines, Snapshots, and/or Charts.

Pro tip: Knowing which solution to us and when to us it is a technical consideration. We strongly recommend working with Professional Services to determine the right approach for your unique goals.

Read Only Page Creation

So you've very carefully considered all of your options, the pros and cons, and have decided that a Read Only Page is the way to go! Here's what you need to know for this adventure:

Required in your view:

  • Your Read Only Page must have a Primary Key.
  • The view must begin with the letters "vw"
  • First field must be INT and, if possible, unique. This is going to serve as the Primary Key Field. If this if not reliably the same from one session to another, the end user should not rely upon any selections made/saved in the view. 
  • Domain_ID must be present in your view or the Platform won't allow you to setup security roles to read this page.

If the rows are about people:

  • Make sure Contact_ID is a field returned by the view.
  • Make sure Congregation_ID is a field returned by the view
  • Make sure that if the results are "one row per person" you use Contact_ID as the Primary Key (first field).