What are the capabilities of Schoolzilla Enterprise?
Schoolzilla Enterprise will give you access to a Schoolzilla-hosted and maintained Data Warehouse. You and other data warehouse users will be able to access all data made available in raw from each source system to which we connect, as well as the standardized staged and dw structures. You will have the ability to create tables, views, stored procedures, indexes, etc. in a custom schema which you will own.
We have made a number of improvements to your experience using and accessing the Data Warehouse including:
- Improving the way you securely access the Data Warehouse to eliminate the need for a VPN client.
- Syncing your Schoolzilla account on both the web application and the Data Warehouse. This will mean you have one Schoolzilla password that you can set without Schoolzilla support.
- Enabling management of additional Data Warehouse users on our web application.
Will we (as analysts/developers) have more than read-only access to the warehouse? Will we be able to create custom tables, views, procedures, etc., or will we only have read-only? Does this access have additional cost?
You will be able to create tables, views, and procedures within the custom schema and read every table in the database (including raw source tables not currently available in Tableau Data Server data sources).
What is the custom schema?
The custom schema is the space reserved for you to add your own database objects in the Schoolzilla Data Warehouse. The database objects (tables, views, etc.) are organized using a feature in Microsoft SQL Server known as schemas. (You can think of them almost like a folder structure on your file system, or a package or namespace in programming languages.) Schemas provide a clear separation of what you maintain versus what Schoolzilla maintains in your data warehouse, and allows us to partner without stepping on each other’s toes. If the database objects are stored in the custom schema, you can manage them however you see fit (add, remove, update, etc.). If the database objects are in any other schema, Schoolzilla maintains them.
What can I do in my custom schema?
Create, alter, and delete database objects, including:
- Tables
- Views
- Indexes
- Stored Procedures
- Functions
- Constraints
Manipulate data stored in the custom schema tables:
- Insert
- Update
- Delete
We ask that you please do not store staff or student social security numbers in your custom schema.
How do I add data to my custom schema in my Data Warehouse?
Presently, managing the objects and data in your custom schema requires deep expertise in SQL.
Stored procedures can be used to load data from other tables into the custom schema. These stored procedures can be added to the “custom master” stored procedure that is scheduled to run every night. The custom master will automatically stop after 20 minutes. This means that run serially, your stored procedures should not exceed 20 minutes. If your custom nightly job exceeds 20 minutes, you will be notified by the Schoolzilla support team that your job was killed before completion so that you can optimize code or adjust what is scheduled. (This limit is in place to prevent unexpected rapid growth in your data from having negative impacts to database reliability and performance. Should you reach time limits, you can contact Schoolzilla to request and adjustment after verifying there are no issues with existing custom jobs.)
The import wizard in SSMS and other tools can be used to load data into the custom schema from other sources. You are responsible for the use and management of any tool used to import data.
What happens when a table or column—on which I rely—changes?
Schoolzilla’s team maintains internal release notes that capture changes to field names, field types, table names, and mappings to fields for all tables in the staged and dw schemas. If you have questions about changes made to fields, please reach out to Schoolzilla support.
Raw table names, fields and data types are updated when changes are made by vendors. These are not tracked by the Schoolzilla team.
You are responsible for making required updates after changes to tables, fields, and views in other schemas from which you are pulling data.
How much data can I store in the custom schema of my data warehouse?
Your custom schema starts with 5GB of storage space. Should you reach a storage limit, you can either remove unused objects to free up space, or you can contact Schoolzilla with your expected data volumes to request an adjustment to the limits. (These limits are in place to prevent unexpected rapid growth in your data from having negative impacts to database reliability and performance.)
Are there any known limitations to how I can use the Data Warehouse?
The intention of Schoolzilla Enterprise is to allow organizations that are hungry for data and skilled in managing it to extend the functionality regularly provided by Schoolzilla dashboards currently. There is a lot of power available in this extended functionality, but it is not unlimited power, and it comes with a responsibility to use it judiciously. In some cases, Schoolzilla must restrict access to certain capabilities of underlying tools to ensure the security of student data and provide a reliable experience for all organizations we serve. Currently, Data Champions using Schoolzilla Enterprise will not be able to perform the following directly:
- View execution plans of queries in the database
- Schedule tasks (or adjust any Schoolzilla-managed tasks) within the database scheduler (i.e. SQL Agent in Microsoft SQL Server)
- Use in-database integrations (e.g. CLR Integration or R Services features in Microsoft SQL Server)
- Use SQL Server components besides the query engine (e.g. Integration Services, Reporting Services, etc). Note: you can set up your own SQL Server to leverage Reporting Services, etc. if you choose.
- Use features that rely on the database accessing files on network drives (e.g. bulk insert of data)
This list may change over time, and we also may have to disable any process (even without warning) that may have a significant negative impact to database performance or reliability.
As mentioned above, we also ask that you please do not store staff or student social security numbers in your custom schema.
We welcome feedback about how the functionality available in the Data Warehouse is meeting your needs.
Would this work with other BI tools?
The Data Warehouse will be compatible with any BI tool compatible with AWS-hosted SQL Server databases. We know you will be able to connect using tools like SQL Server Management Studio, Azure Data Studio, Excel, R, DBeaver and more. Please let us know if there are specific tools you use that you’d like to verify are compatible.