Direct Data Access is included in the SaaS Advantage deployment tier and may be available as an add-on for certain customers in the SaaS Plus deployment tier. To learn more about deployment tiers, see Learn SaaS Plus and Advantage Options.
As part of the Open Database initiative, launched in 2010, Blackboard offers Data Direct Access for Learn SaaS. With Data Direct Access, you can query a near real-time replica of your Learn database whenever you need to. Support staff can troubleshoot issues; academic staff can collect data about adoption, use, and the efficacy of certain initiatives; and leadership can use real data to evaluate return on investment and make data-supported decisions.
Data Direct Access has two components - the SaaS read replica database and detailed schema documentation.
- Near real-time - Data are updated from Production databases in near real-time.
- Secure - Data and data transfer are encrypted to IP-restricted addresses.
- High performance - Databases run on production-equivalent hardware.
- Flexible access - Use your favorite DB client application or technology.*
*must support connections to PostgresSQL over SSL.
Documentation is delivered in HTML packages in Javadoc format and provides information about the Learn SaaS database structure, including layout and table inter-dependencies. The packages below include a description of the data characteristics of the tables and columns, including datatypes, sizes, nullability, index, sequence, key, and constraint information. Additional commentary is also provided for key tables and columns.
This information is intended for intermediate to expert administrators and developers experienced working with relational databases.
You can view the Schema or Changes packages directly in HTML using the following links. You can also download the packages through Behind the Blackboard on the Download Files page for each release.
|Release||Schema and Changes|
|9.1 Q4 2018||View Schema | View Changes|
|9.1 Q2 2018||View Schema | View Changes|
|9.1 Q4 2017||View Schema | View Changes|
|9.1 Q2 2017||View Schema ||
|9.1 Q4 2016||View Schema | View Changes|
|9.1 Q2 2016||View Schema | View Changes|
|9.1 Q4 2015||View Schema | View Changes|
|9.1 October 2014||View Schema | View Changes|
|9.1 April 2014||View Schema | View Changes|
While the read replica is a full and complete copy of the production database, the published schema in the documentation does not include a complete description of every database object in Blackboard Learn. The following information is not included:
- Detailed commentary for some tables and columns.
- No table or column information for the Xythos engine file storage schema.
- No information about stored procedures, views, triggers, or database jobs.
When you purchase Direct Data Access or become a Learn SaaS Advantage customer, Blackboard will provide you with these details required to connect to your read replica database.
- Hostname of the database
- Port Number will be 5432
- SSL Public Key: https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
You can request up to 5 username/password sets.
As part of the security apparatus, connections will only be allowed from a limited number of IP addresses. Blackboard will ask you to identify up to five unique source IP addresses from which you intend to interact with the read replica. If you have trouble identifying your IP addresses, you can find it by going to http://checkip.amazonaws.com/ on the machines from which you plan to connect.
Now that you have this access, how do you make it useful? The following questions may help:
- What is actually here in the data?
- What sort of things do I want to know?
- What else should I be looking at/for?
- How exactly do I get that from these data?
- What does this query really do?
Get answers and help with Direct Data Access:
- Join the Community: https://community.blackboard.com
- Professional Services: Blackboard Consulting offers services to get you started. Contact us for help with Direct Data Access.
To access the data, you'll need a database explorer, reporting tool, or some software that can connect to the PostgresSQL database over SSL. For our example, and to help you test your connection, we are using the free, open source tool pgAdmin, which is available for both Mac and Windows.
Note that Blackboard can't provide technical support for pgAdmin or any third party software you might use to connect to the read replica. Information provided here is done so as an example. The pgAdmin interface may vary based on version and platform. We have attempted to provide the most up to date and accurate information possible.
Before you begin:
- Download and install the latest version of pgAdmin for your operating system.
- Download and save the SSL Public Key, making note of where you saved it.
- Open the pgAdmin application and select the plug icon to add a connection to a server.
- Complete the fields on the Properties tab
- Name: choose any name you would like
- Host: value provided by Blackboard
- Port: 5432
- Service: leave empty
- Maintenance DB: postgres
- Username: value provided by Blackboard
- Password: value provided by Blackboard
- Store Password?: leave unchecked for higher security
- Color: choose a display color (optional)
- Group: leave empty or add to a group if you have any
- Next you will have to provide the SSL certificate to pgAdmin. On the SSL tab:
- SSL: Choose "Require" from the drop down
- Server Root Certificate File: Browse to your saved SSL Public Key (rds-combined-ca-bundle.pem)*
- Select OK to save
Note that pgAdmin is looking for files with the .crt extension. Don't worry, pgAdmin will convert the .pem file to .crt when you select OK.
- In the pgAdmin tool, select the database you want to query from the Object browser window.
- Select the SQL icon in the toolbar. The icon appears as a magnifying glass.
- The SQL Editor window appears. Type your query in this window.
- Try a simple query and view all the user records:
select * from USERS
- Select the green arrow icon when you're finished.
Get data from the data microservice
This section pertains to clients who have adopted the new data microservice available starting with 9.1 Q2 2018. Blackboard is rolling out this improvement gradually to provide a smooth transition. If you are a Blackboard Learn administrator and have questions, open a case on Behind the Blackboard.
Blackboard is upgrading the data management process for clients with Direct Data Access (DDA). If your institution has DDA, the following applies to you. The upgrade involves migrating reporting data to a new state of the art Snowflake database. Once the migration is complete, these clients will have access to a new data microservice for Blackboard Learn. With this microservice, you can get the same data that supports Learn's built-in reporting.
Blackboard will contact you prior to migration to help you setup credentials for your institution so that you can access your data from the Snowflake DB cloud instance after the migration.
Get data using the Snowflake user interface
You can use Snowflake's user interface by logging into Snowflake with the URL and credentials that Blackboard provides your team. For information about using Snowflake's user interface, see Snowflake's tutorial Logging into Snowflake.
Get data programmatically
The Snowflake DB documentation provides resources to programmatically access your data using your credentials. Programmatic access requires a JDBC URL. The Snowflake documentation provides guidance on this topic.
Snowflake supports SQL with some modifications. For more information, see Snowflake's documentation.
Upgrade BIRT reports
BIRT reports that ship with Blackboard do not require updating.
Custom BIRT reports should use the standard dataSource values, bb_common_data_source for the current stats database or the bblearn_data_source for the current learn database. If this is the case, you do not need to change anything. The BIRT reports will run against Snowflake once your organization enables the data microservice. If a custom BIRT report does not use one of these datasources, it will have to be changed to do so.
The relevant change between the two systems is that the table formerly called activity_accumulator is identified as activity_accumulator_archive in Snowflake.
Upgrade Building Blocks for the new microservice
If your custom building block runs queries against the stats tables you must change how the building block gets its database connection. The following method automatically returns a Snowflake connection if your organization has enabled the data microservice.
Data and tables available
Reports heavily use the following tables, all of which are migrated into Snowflake when your organization enables the data microservice. From the time of upgrade, Learn maintains the versions in Snowflake and allows those found in the original database to go stale. The following materialized views are available in the Snowflake database. For more information about particular tables or groups of tables, see ODS Schema.
activity_accumulator_archive (replaces activity_accumulator)