Skip to main content

Database Overview

The ActivePivot cube allows to have an aggregated view on data, along different dimensions. The initial data from which the aggregated view is constructed comes from a database. Historically, ActivePivot only used its own database, called the Datastore. Since versions 6.0, the concept has been extended so that external databases can also be used as the data provider on which the cube relies.

The interface used to represent any component capable of providing versioned data for the cube is com.activeviam.database.schema.IDatabase.

The Datastore component evolved to become an IDatabase, just one alternative amongst others.

Database Schema​

The database schema consists of a set of tables, each containing fields, and that are interlinked with joins.
For those used to the vocabulary historically used in the Datastore, the tables correspond to what used to be called stores, and the joins to what used to be called references.
Naming joins is not something that exist in common databases. Yet, in the context of ActivePivot where one pre-define a flattened schema on which they work, it is convenient to assign names to those paths. This allows to easily discuss about fields integrated to the schema.

schema with tables and join

Exploring the Database Schema​

Since users don't usually focus on one table but a set of connected tables and all the fields they contain, they access the fields from their paths: a starting table, followed by a series of joins, to a target field. This can be tedious for a basic component listing only tables.

The IDatabaseSchemaEntityResolver is a utility tool to explore that schema. Its goals are to find the referenced tables and fields from their names and paths.

Versions​

Atoti+ Databases have the same concept of versions and branches that were previously exposed by the Datastore and ActivePivot cubes. As before, they represent single points in time of the Database and alternative scenarii on the dataset.

Query Runner​

info

This component is accessed from a Version though the method IDatabaseVersion#getQueryRunner.

The Query Runner is the entry-point to run queries on a Database. It offers to run different types of queries:

  • List: These queries are made to retrieve a list of records that match some specified conditions.
  • Aggregate: These queries are made to retrieve aggregated results from the database. They specify the conditions for selecting data, the fields to group - or aggregate - data by, and the aggregation functions to apply.
  • Get-By-Key: These queries are made to retrieve one or several records (lines of data in a table) for which we know the unique identifier (e.g. the key).
  • Statistics: These queries are made to retrieve information about the Database. Details like the size of a table, or the cardinality of a given field.

Behind the scene, ActivePivot cubes also make use of those queries. MDX SELECT queries will run Aggregate queries to retrieve the underlying values powering Copper computations. List queries are used by Drillthrough queries, to access the underlying data behind one member.

Query Manager​

info

This component is accessed from a Version though the method IDatabaseVersion#getQueryManager.

Completing the Query Runner, the Query Manager allows to prepare queries beforehand. Using an analogy with JDBC, the Query Runner creates SQL Statement while the Query Manager creates Prepared Statements. In the language of the QueryManager, it produces instances of IPreparedQuery
There are several benefits of Prepared Queries, from building once for all a given query, then parameterizing it before running it, to saving creation cost for a query using in a compute-intensive code path.

Once created, those queries must be passed to a given Query Runner to be executed in a given version.