Versions Compared

Old Version 2

changes.mady.by.user Luc Richard

Saved on

compared with

New Version Current

changes.mady.by.user Luc Richard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Database Replicas provide enhanced performance and durability for the learning environment's database. They make it easy to elastically scale out beyond the capacity constraints of a single database instance for read-heavy database workloads. Dual Code can create one or more replicas of your database and serve high-volume application such as Microsoft® Power BI® or other BI (Business Intelligence) applications. 

Insert excerpt
Integration Services
Integration Services
nopaneltrue

Even though the HCE comes with dozens of reports out of the box as well as a basic report building tool, we recognize that some organizations have very specific data collection and aggregation needs that can't be met with the existing reports. We also recognize that the larger hospitals have data warehouses for reporting and data analysis. For that reason, Dual Code's enterprise solution offers the ability for clients to connect their Business Intelligence tools such as Microsoft® Power BI® or Tableau® to their learning environment's private database.

As part of this service, Dual Code creates a second database instance using a snapshot of the source (e.g. "master") database. It then uses the engines' native asynchronous replication to update the read replica whenever there is a change to the source database. The read replica operates as a database instance that allows only read-only connections; applications can connect to a read replica just as they would to any database instance. This architecture provides enhanced performance and durability for the learning environment's database and makes it easy to elastically scale out beyond the capacity constraints of a single database instance for read-heavy database workloads.


...


The replicate replica database is accessible to the client using a an SQL user account that has limited, read-only privileges. For security reasons, each client connecting to the read-only database must have a unique username and password combination. We recommend that the password be rotated on a regular basis. The connection is also locked by our firewall to one or more IP addresses as specified by the client.

In addition to read-only access to select tables, Dual Code has developed a series of database views to make it easier for you to develop custom reports using your preferred BI application.Image Removed The schema of that database is defined from within the learning environment itself. You can view it as an administrator by going to Site Administration > Reports > Database Schema. 

Child pages (Children Display)


Excerpt
hiddentrue

When integrating your preferred Business Intelligence (BI) tool to the database, the following technical details are worth noting:

  • The learning environment is based on Moodle, the world's most popular open-source learning management system. Moodle does not currently generate actual foreign key constraints in the database. As such, Dual Code has defined the relationship using the following convention.

    • The ID column in a table always represents the primary key and usually has the comment of "Primary key of the table."

    • A foreign key usually has the comment of "Key referencing the primary key in the [tablename] table."

  • When dealing with dates or times:

    • As a general rule, most dates/times are stored in the database as a BigInt in Unix time format (also known as Epoch time, POSIX time, seconds since the Epoch, or UNIX Epoch time). Some exceptions apply.

    • The database read-only replica is in the same timezone as the production server.

  • Dual Code has developed views to facilitate your development. 

    • Regular table start with the mdl_ prefix (e.g. mdl_course)

    • Views start with the bi_mdl_ prefix (e.g. bi_mdl_users)