ETL for Datawarehouse

Owned by Elizabeth Storrs
Dec 01, 2023
2 min read

When TUSMGP spun out into their own instance from GradHealthSci, one concern was how Datawarehouse would connect to them. It turns out, the answer was that there was no need to add or change anything:

Dmitri says:

Although we do have a new Slate instance hosted by Technolutions, we bring all data into our Tufts-hosted database on server WSSSQLPRD01, and it’s from there that the SLATE ETL to DW happens, and you will continue to ETL just from that single database server.

The only changes arising as a result of this are not connection-related but data-related: 

The ETL to DW draws from 2 databases on WSSSQLPRD01:

1.“SLATE”, which is the database of tables in the same format as the actual Technolutions tables, with all instances combined into each table.

2.”GAMS_SLATE”, which is a database of transformed data from “SLATE”, used for creating the interface to SIS.

On the Slate database in WSSSQLPRD01, the field TuftsSchoolCD in all of the tables is used to designate the Slate instance from which the data was drawn.  Formerly, the code “MD” corresponded to the GRADHLTHSCI Slate instance.

Going forward, “NU” will be the code corresponding to the GRADHLTHSCI instance (and will therefore include data from both NUTR and GSBS), while “MD” will correspond to the new PHPD Slate instance, on the Slate database on WSSSQLPRD01.

On the GAMS_SLATE database on WSSSQLPRD01, the TuftsSchoolCD for data drawn from the GRADHLTHSCI instance will be broken out into “NU” and “SK”, and that may be all that Mike used in his queries—I don’t know.

Chun-Pi’s questions, with Dmitri’s answers.

  1. Server:

What’s the new server’s name? still on SQL server or another database?

(Current SLATE ETL pointed to wsssqlprd01)

                This is unchanged.

  1. Login user:

Please setup a new DW(dwbi) user account to access this new clone database.

                Unchanged.

  1. Firewall:

Please make sure the following 2 servers have access to this new server:

                             10.246.172.55 WBODITST01

                             10.246.172.54 WBODIPRD01

Since the server is still wsssqlprd01 I assume these servers still have access to it.