Data Warehousing
  • 15 Nov 2023
  • 4 minute read
  • Dark
  • PDF

Data Warehousing

  • Dark
  • PDF

Article Summary

Data exports can be built for a variety of data transfer needs, including extracts for data warehouse. Institutions may build data warehouse exports using the robust tools available in Query Builder, such as export values, translation codes, update queues, and formulas.

Slate is a transactional database, but it can provide the necessary information to a data warehouse through data feeds. The process is handled much like any other data integration with Slate, except that the feeds are typically unidirectional (that is, from Slate to the data warehouse only).

What is a Data Warehouse?

A data warehouse is effectively a collection of data from many different sources, from which it is updated on a certain frequency (such as daily or annually). It holds comprehensive information on the different components and segments of an organization.

Data warehouses often store very large amounts of data, not just in terms of the scope of data sources involved, but also in terms of time. When data is moved from the source to the data warehouse, it is considered a "snapshot" of the source at that time. A data warehouse can, and generally does, store snapshots going back to many years. 

Since data from all sources is consolidated in a central place, it is more easily accessed and reported on by users. For example, a user from the institutional research department could easily create reports containing admissions, financial aid, and student life data drawn directly from the warehouse.

Is Slate a Data Warehouse?

While Slate does accept data from many different sources, and while there is no limit to the amount of data or length of time data is stored in Slate, Slate does not function as a data warehouse.

Slate databases are optimized for transactions, for example, submitting an application, or registering for an event. This allows records to be updated extremely quickly, and ensures that institutions are working with the most up-to-date data for their daily operations.

On the other hand, Slate is not optimized for storing and analyzing multidimensional data. That is to say, Slate databases are not designed to store each field value change, and when it was changed, while retaining a copy of the original data as it was in the beginning. 

Consequently, Slate may not be the appropriate tool for certain reporting needs. The Query Builder and Report Builder in Slate are feature-packed and designed to make data querying and retrieval easy for any user, without the need for specialized knowledge such as SQL or database design and development. However, some types of reports are more difficult to create in Slate. For example, year-to-date reports that involve values stored in custom fields may vary in the degree of accuracy, since custom fields store only the most recent value and associated timestamp, not the "snapshot" value at a specified point in time. 

Integrating Slate with a Data Warehouse

Many partner institutions export data from Slate to external data warehouse solutions that typically also house data from other departments or units across campus. These data exports are created and managed with Query Builder, and they can be delivered on a scheduled basis. 

To integrate Slate data with a data warehouse, create a Slate query of the data to be exported.

As with any type of data export, an export to a data warehouse may be set to return cumulative, updated records only, or incremental feeds. To determine what option is appropriate for the data warehouse needs.

SQL for Data Warehouse Exports

Building custom SQL queries or using a direct SQL connection is not necessary to send Slate data to a data warehouse. Using Query Builder within Slate provides a layer of abstraction for the underlying database, and the standard functionality has built-in safeguards, is tested and optimized to perform even under heavy load, and incorporates new updates seamlessly.

Additionally, the different query bases that are easily accessible with Query Builder allow for data extracts pertaining to entities other than persons or applications, such as schools attended, decision history, addresses, test scores, and custom datasets (such as volunteers or alumni). 

If a direct SQL connection must be initiated, the best practice is to set up the data export using materialized views. A materialized view generates a virtual table in Slate according to the specifications in the query, but without the performance considerations of pulling large volumes of data from live database tables. 

Is it necessary to have an ERD of the Slate database to implement a data warehouse project?

Slate is a distinctly different type of system from an institutional ERP or campus-wide CRM, and is tightly modeled around a business process, instead of acting solely as a central repository for data. Slate is composed of many tables that support these business processes. Many of these table columns or attributes are not necessary for reporting or warehousing. As a continuously evolving platform, the Slate database structure may also be updated to accommodate new features and enhancements while retaining performance and availability.

Therefore, we recommend using Query Builder to identify, isolate, and export the data points required for the data warehouse, guaranteeing that the extract always draws from up-to-date table references.

Was this article helpful?

What's Next