Skip to main content

Getting started with DirectQuery

note

This tutorial requires access to the DirectQuery modules.

This tutorial will walk you through the basics of DirectQuery by creating an application on a external database.

We’ll see how to:

  • Use the DirectQuery module in a project
  • Connect to the external database
  • Create a data model based on the external database
  • Use this data model in a Cube

This tutorial requires:

  • A external database
  • Familiarity with this database
  • Familiarity with the basic concepts of Atoti.

Building the external content​

Let’s start by generating the content in the external database

Let’s create a new dataset for this tutorial

CREATE SCHEMA TUTORIAL;

Let’s create and feed a PRODUCTS table containing information about the products

if not exists (select * from sys.tables where name = 'PRODUCTS')
CREATE TABLE TUTORIAL.PRODUCTS (
    [PRODUCT_ID] VARCHAR(50) NOT NULL,
    [CATEGORY] VARCHAR(50) NOT NULL,
    [SUB_CATEGORY] VARCHAR(50) NOT NULL,
    [SIZE] VARCHAR(50) NOT NULL,
    [PURCHASE_PRICE] FLOAT NOT NULL,
    [COLOR] VARCHAR(50) NOT NULL,
    [BRAND] VARCHAR(50)  NOT NULL
    );
INSERT INTO
  TUTORIAL.PRODUCTS
VALUES
  ('TAB_0', 'Furniture', 'Table',  '1m80',   190, 'black', 'Basic'),
  ('TAB_1', 'Furniture', 'Table',  '2m40',   280, 'white', 'Mega'),
  ('BED_2', 'Furniture', 'Bed',    'Single', 127, 'red',   'Mega'),
  ('TSH_3', 'Cloth',     'Tshirt', 'M',      19,  'brown', 'Over'),
  ('TSH_4', 'Cloth',     'Tshirt', 'L',      20,  'black', 'Over'),
  ('HOO_5', 'Cloth',     'Hoodie', 'M',      38,  'red',   'Mega');

Then create the SALES table and feed it with 20 facts

CREATE TABLE TUTORIAL.SALES (
    [SALE_ID] VARCHAR(50),
    [DATE] DATE,
    [SHOP] VARCHAR(50),
    [PRODUCT] VARCHAR(50),
    [QUANTITY] BIGINT,
    [UNIT_PRICE] FLOAT
);
INSERT INTO
  TUTORIAL.SALES
VALUES
  ('S0010', '2022-01-31', 'shop_2', 'BED_2', 3, 150),
  ('S0009', '2022-01-31', 'shop_3', 'BED_2', 1, 150),
  ('S0008', '2022-01-31', 'shop_4', 'BED_2', 1, 150),
  ('S0007', '2022-02-01', 'shop_5', 'BED_2', 1, 150),
  ('S0019', '2022-02-02', 'shop_3', 'HOO_5', 1, 48),
  ('S0018', '2022-02-03', 'shop_3', 'HOO_5', 1, 48),
  ('S0017', '2022-02-04', 'shop_3', 'HOO_5', 1, 48),
  ('S0000', '2022-02-04', 'shop_0', 'TAB_0', 1, 210),
  ('S0001', '2022-02-03', 'shop_1', 'TAB_0', 1, 210),
  ('S0002', '2022-02-02', 'shop_2', 'TAB_0', 1, 210),
  ('S0003', '2022-02-01', 'shop_3', 'TAB_0', 1, 210),
  ('S0004', '2022-02-03', 'shop_1', 'TAB_1', 1, 300),
  ('S0005', '2022-02-02', 'shop_2', 'TAB_1', 1, 300),
  ('S0006', '2022-02-01', 'shop_4', 'TAB_1', 2, 300),
  ('S0013', '2022-02-01', 'shop_4', 'TSH_3', 1, 22),
  ('S0012', '2022-02-02', 'shop_5', 'TSH_3', 1, 22),
  ('S0011', '2022-02-03', 'shop_5', 'TSH_3', 1, 22),
  ('S0016', '2022-01-31', 'shop_1', 'TSH_4', 2, 24),
  ('S0015', '2022-02-01', 'shop_2', 'TSH_4', 2, 24),
  ('S0014', '2022-02-02', 'shop_4', 'TSH_4', 1, 24);

Importing DirectQuery modules​

Additionally to classic Atoti modules you will need to import two modules:

  • One generic module to interact with external databases

<dependency>
    <groupId>com.activeviam.activepivot</groupId>
    <artifactId>activepivot-external-database</artifactId>
    <version>6.0.15-sb3</version>
</dependency>

  • One to connect to your specific database

<dependency>
    <groupId>com.activeviam.directquery</groupId>
    <artifactId>api-bigquery</artifactId>
    <version>6.0.15-sb3</version>
</dependency>

Using the external database in DirectQuery​

Create the database configuration​

Here we define properties of a Databricks connection.
We connect to a database with a user password authentication.
You can read Connect to Databricks page for more details about user authentication. The JDBC connection string also contains parameters to set the user and the database.

final DatabricksConnectionProperties properties =
    DatabricksConnectionProperties.builder()
        .connectionString(connectionString)
        .additionalOption(
            DatabricksConnectionProperties.PASSWORD_PROPERTY_KEY, "your-plain-password")
        .build();

You must also configure some DirectQuery's options or simply use the default settings to begin with:

final DatabricksDirectQuerySettings settings = DatabricksDirectQuerySettings.defaults();

An Atoti session can be set up combining JDBC connection string, these properties and these settings.

final Session session = Session.createSession(properties, settings);

Add external tables into DirectQuery​

The second step is to create the external database schema description.
We start by building the table definition with the help of a external database discoverer.

In this snippet we use Snowflake table discoverer to resolve the table definition from SQL schema metadata.

final Table salesTable =
    session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
final Table productsTable =
    session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "PRODUCTS"));

Create a join between tables​

Then we create a join between the tables of our example.
Sales has a many-to-one relationship with Products on the field product_id.

final SqlJoin join =
    SqlJoin.builder()
        .name("SALES_TO_PRODUCTS")
        .fromTable("SALES")
        .toTable("PRODUCTS")
        .fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
        .build();

Relationship optionality​

When defining a join you can set the Relation Optionality.
The default relationship is Optional, it means that some rows in Sales table could have a product which is missing from the Products table.
You can set this relationship to Mandatory, if you are sure that all products used in Sales table are in Products table.
Mandatory relationship could lead to better performance in some connector features like Incremental refresh.

Create the database​

Combining table and join definitions, we can know build our schema and our database around it.

final Schema schema =
    Schema.builder()
        .withExternalTables(List.of(salesTable, productsTable), List.of(join))
        .build();

Building an app on the external database​

We can now build an ActivePivot on top of this database.

final ISelectionDescription selection =
    StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
final IActivePivotInstanceDescription cubeDescription =
    StartBuilding.cube("MyCube")
        .withMeasures(
            superMeasureBuilder ->
                superMeasureBuilder.withCalculations(
                    c -> {
                      // You can use the database fields to define aggregations
                      Copper.sum("QUANTITY").as("QUANTITY.SUM").publish(c);
                      Copper.max("UNIT_PRICE").as("Max price").publish(c);
                    }))
        .withDimensions(
            builder ->
                builder
                    .withSingleLevelDimensions(
                        List.of("DATE", "SHOP", "PRODUCT", "SIZE", "BRAND"))
                    .withDimension("CATEGORY")
                    .withHierarchyOfSameName()
                    .withLevels("CATEGORY", "SUB_CATEGORY"))
        .build();
final IActivePivotManagerDescription managerDescription =
    StartBuilding.managerDescription()
        .withName("MyManager")
        .withSchema()
        .withSelection(selection)
        .withCube(cubeDescription)
        .build();
final Application app =
    session.applicationBuilder().schema(schema).managerDescription(managerDescription).build();
this.resources.register(app);
app.start();
final IActivePivotManager manager = app.getManager();

That's it, the cube is built and you can run queries on it.

final IActivePivotVersion pivot =
    manager.getActivePivot("MyCube").getHead(IEpoch.MASTER_BRANCH_NAME);
final ICellSet result =
    ActivePivotQueryRunner.create()
        .withWildcardCoordinate("SHOP")
        .forMeasures("QUANTITY.SUM")
        .run(pivot);
GetAggregatesResultCellset.check(pivot, result).printCellSet();