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 remote database.

We’ll see how to:

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

This tutorial requires:

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

Building the Remote content​

Let’s start by generating the content in the remote 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 remote databases

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

  • One to connect to your specific database

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

Using the remote Database in DirectQuery​

Create the Database Configuration​

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

final RedshiftProperties properties = RedshiftProperties.builder()
        .connectionString(connectionString)
        .additionalOption(RedshiftProperty.PASSWORD, "your-plain-password")
        .build();

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

final Session session = Session.createSession(properties);

Add remote tables into DirectQuery​

The second step is to create the remote database schema description.
We start by building the table definition with the help of a remote 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();

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();