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 Direct Data Access for Learn SaaS. With Direct Data 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.
Direct Data Access has two components - the SaaS read replica database and detailed schema documentation.
SaaS read replica databases
- 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 PostgreSQL over SSL.
Open database documentation
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 and documentation on Blackboard's repository or using the following links. You can also download the packages from Behind the Blackboard on the Download Files page for each release.
|Release||Schema and Changes|
|9.1 Q4 2019 and above||View Schema | View Changes|
|9.1 Q2 2019||View Schema | View 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|
What isn't included?
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 information about stored procedures, views, triggers, or database jobs.
Access the data
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://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
Learn more on how to use it with RDS.SSL from Amazon AWS
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.
Turn data into information
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.
Using the pgAdmin tool
To access the data, you'll need a database explorer, reporting tool, or some software that can connect to the PostgreSQL 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.
Create and configure a new database connection
- 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.
Run a SQL query
- 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.
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)