Date: Thu, 28 Mar 2024 15:37:18 -0500 (CDT) Message-ID: <1156741573.30134.1711658238902@PUBEDFIPRDWEB5.public.local> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_30133_1780313107.1711658238900" ------=_Part_30133_1780313107.1711658238900 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
T= hursday, April 12, 2018
2:15 PM - 3:15 PM= |
Expanding Multi-year Da= ta Capture: 1 year later A year ago, the Alliance launched a set of = development activity to expand the ability to capture multi-year data. Thro= ugh this effort and other effort in the community, we have also benchmarked= a number of =E2=80=9Ctemporal=E2=80=9D data capture technologies. This res= earch will be presented in the context of what kinds of temporal data manag= ement each solves. |
These bro= ad session notes attempt to capture the spirit of the discussion and should= not be interpreted as a transcript. Although Ed-Fi Alliance staff were inv= olved in capturing these observations, the notes below should not be constr= ued as official, complete, or 100% accurate.
Presentation by=
Chris Moffatt
The slide deck = will be posted. These notes will just cover highlights and discussion.
Key question: i= ncorporate T-ODS work in to the Core, or release on the Exchange? Need comm= unity feedback on this. Looking for 6 members to participate in special int= erest group.
Loading tempora= l data
Generally need to be able to export Ed-Fi supportable= (XML?) from source systems and bulk load into the ODS instead of using API=
Could use API on a clean database to load old data in= stages, building up one year after another
Questions on SQ= L Server
Using SQL Snapshots? Part of the import process can work with Snapshots if = you have the license for it, otherwise using backup and restore.
=Thus when speaking of T-ODS snapshots, it is not equi= valent to SQL Server Snapshot.
Temporal API
For now, deferring on building a public API for updat= ing temporal data
But there is a proof of concept of an API
<= /li>Source systems pushing to API probably don=E2=80=99t = support temporal parameters anyway, so bulk loading would be needed<= /p>
Some existing m= odels
Wisconsin
Separate ODS for each year
Collection_ODS - load each year into the Collection= instance
Run validation on it
Publish snapshots for reporting
Arizona
Ed-Fi ODS-like database, with addition of date rang= e columns
Removes SQL keys to allow for duplication on natura= l keys
Data Vault architecture (Certica) - immutable snapsho= ts
Table-valued functions bring back =E2=80=9CEd-Fi-li= ke" data structures)
Load data marts from data vault as needed
Each year clear out your ODS, but the old data are = in the vault (for example)
Could move data more frequently to get more granula= rity, but there is a cost
SQL Server Syst= em Versioned Temporal Tables
Prefer to avoid more vendor lock-in
T-ODS records snapshot of old and current value in a table, but SQL Tempora= l only has old data in the temporal table and current data in the main tabl= e.
You could turn on SQL Temporal without much effort
T-ODS lets you decide what the range is - you can set= an Effective End Date on your own. This is based on whenever you decide th= at you want a snapshot. Effective End Date could thus be null.
= li>Whereas SQL Temporal automatically captures when the = data change.