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 DATABASE_NAME.SCHEMA_NAME;

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

CREATE TRANSIENT TABLE IF NOT EXISTS PRODUCTS ( PRODUCT_ID STRING NOT NULL,CATEGORY STRING NOT NULL,SUB_CATEGORY STRING NOT NULL,SIZE STRING NOT NULL,PURCHASE_PRICE FLOAT NOT NULL,COLOR STRING NOT NULL,BRAND STRING NOT NULL, PRIMARY KEY (PRODUCT_ID));
INSERT INTO PRODUCTS SELECT 'TAB_0','Furniture','Table','1m80',190,'black','Basic';
INSERT INTO PRODUCTS SELECT 'TAB_1','Furniture','Table','2m40',280,'white','Mega';
INSERT INTO PRODUCTS SELECT 'BED_2','Furniture','Bed','Single',127,'red','Mega';
INSERT INTO PRODUCTS SELECT 'TSH_3','Cloth','Tshirt','M',19,'brown','Over';
INSERT INTO PRODUCTS SELECT 'TSH_4','Cloth','Tshirt','L',20,'black','Over';
INSERT INTO PRODUCTS SELECT 'HOO_5','Cloth','Hoodie','M',38,'red','Mega';

Then create the sales table and feed it with 20 facts

CREATE TRANSIENT TABLE IF NOT EXISTS SALES ( SALE_ID STRING NOT NULL,DATE DATE NOT NULL,SHOP STRING NOT NULL,PRODUCT STRING NOT NULL,QUANTITY FLOAT NOT NULL,UNIT_PRICE FLOAT NOT NULL,PRIMARY KEY (SALE_ID), FOREIGN KEY (PRODUCT) references PRODUCTS(PRODUCT_ID));
INSERT INTO SALES SELECT 'S0010','2022-01-31','shop_2','BED_2',3,150;
INSERT INTO SALES SELECT 'S0009','2022-01-31','shop_3','BED_2',1,150;
INSERT INTO SALES SELECT 'S0008','2022-01-31','shop_4','BED_2',1,150;
INSERT INTO SALES SELECT 'S0007','2022-02-01','shop_5','BED_2',1,150;
INSERT INTO SALES SELECT 'S0019','2022-02-02','shop_3','HOO_5',1,48;
INSERT INTO SALES SELECT 'S0018','2022-02-03','shop_3','HOO_5',1,48;
INSERT INTO SALES SELECT 'S0017','2022-02-04','shop_3','HOO_5',1,48;
INSERT INTO SALES SELECT 'S0000','2022-02-04','shop_0','TAB_0',1,210;
INSERT INTO SALES SELECT 'S0001','2022-02-03','shop_1','TAB_0',1,210;
INSERT INTO SALES SELECT 'S0002','2022-02-02','shop_2','TAB_0',1,210;
INSERT INTO SALES SELECT 'S0003','2022-02-01','shop_3','TAB_0',1,210;
INSERT INTO SALES SELECT 'S0004','2022-02-03','shop_1','TAB_1',1,300;
INSERT INTO SALES SELECT 'S0005','2022-02-02','shop_2','TAB_1',1,300;
INSERT INTO SALES SELECT 'S0006','2022-02-01','shop_4','TAB_1',2,300;
INSERT INTO SALES SELECT 'S0013','2022-02-01','shop_4','TSH_3',1,22;
INSERT INTO SALES SELECT 'S0012','2022-02-02','shop_5','TSH_3',1,22;
INSERT INTO SALES SELECT 'S0011','2022-02-03','shop_5','TSH_3',1,22;
INSERT INTO SALES SELECT 'S0016','2022-01-31','shop_1','TSH_4',2,24;
INSERT INTO SALES SELECT 'S0015','2022-02-01','shop_2','TSH_4',2,24;
INSERT INTO SALES SELECT '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.1</version>
</dependency>

  • One to connect to your specific database

<dependency>
  <groupId>com.activeviam.directquery</groupId>
  <artifactId>api-snowflake</artifactId>
  <version>6.0.1</version>
</dependency>

Using the remote Database in DirectQuery​

Create the Database Configuration​

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

final SnowflakeProperties properties = SnowflakeProperties.builder()
        .connectionString(connectionString)
        .warehouse("your-warehouse")
        .additionalOption(SFSessionProperty.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 remote 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();