Skip to main content

Application Refresh

With DirectQuery, you can decide to use caches when creating your measures over the data in the remote database. However, those caches can go out of sync with the actual data in the database.
To resolve this issue, you can manually refresh your DirectQuery application to synchronize with the database.

With an Application such as the one built in the Getting started with DirectQuery guide, it is just:

app.refresh();

Implications

Consider the following application and query.

The application sets up an Aggregate Provider over the measure QUANTITY.SUM when created.

final IActivePivotInstanceDescription cubeDescription = StartBuilding.cube("MyCube")
.withMeasures(superMeasureBuilder -> superMeasureBuilder.withCalculations(context -> {
// You can use the database fields to define aggregations
Copper.sum("QUANTITY").as("QUANTITY.SUM").publish(context);
Copper.max("UNIT_PRICE").as("Max price").publish(context);
}))
.withDimensions(
builder -> builder
.withSingleLevelDimensions(List.of("DATE", "SHOP", "PRODUCT", "SIZE", "BRAND"))
.withDimension("CATEGORY")
.withHierarchyOfSameName()
.withLevels("CATEGORY", "SUB_CATEGORY"))
.withAggregateProvider()
.jit()
.withPartialProvider()
.bitmap()
.includingOnlyHierarchies(HierarchyIdentifier.simple("CATEGORY"))
.includingOnlyMeasures("QUANTITY.SUM")
.build();

The query is made on the measure present in the aggregate provider.

private ICellSet runQuery(final Application app) throws QueryException {
final IActivePivotVersion pivot = app.getManager().getActivePivot("MyCube").getHead();
return ActivePivotQueryRunner.create().withDefaultCoordinates().forMeasures("QUANTITY.SUM").run(pivot);
}

The query, before any change to the database, returns 25.

IActivePivotVersion pivot = manager.getActivePivot("MyCube").getHead(IEpoch.MASTER_BRANCH_NAME);
GetAggregatesResultCellset.check(pivot, runQuery(app)).hasOnlyOneCell().containing(25.);

An update to the database is then made, introducing a new sale at a new date.

INSERT INTO TUTORIAL.SALES VALUES ('S0001','2022-04-16','shop_1','TAB_0',5,210);

The application is now out of sync with the database.

SELECT sum(QUANTITY) FROM TUTORIAL.SALES;
-- Returns 30

pivot = manager.getActivePivot("MyCube").getHead(IEpoch.MASTER_BRANCH_NAME);
GetAggregatesResultCellset.check(pivot, runQuery(app)).hasOnlyOneCell().containing(25.);

Because we are relying on the cache to retrieve this total, the new inserted value is off.

This desynchronization is also visible in the hierarchy. Looking at the members of the hierarchy DATE, we do not see the new date 2022-04-16:

final var members = HierarchiesUtil.getHierarchy(pivot, "DATE").retrieveMembers(1);
final var memberNames = members.stream().map(IOlapElement::getName).collect(Collectors.toUnmodifiableList());
System.out.println(memberNames); // Printed [2022-01-31, 2022-02-01, 2022-02-02, 2022-02-03, 2022-02-04]

Thus, a refresh is necessary to resolve all the above errors.

app.refresh();
pivot = manager.getActivePivot("MyCube").getHead(IEpoch.MASTER_BRANCH_NAME);
GetAggregatesResultCellset.check(pivot, runQuery(app)).hasOnlyOneCell().containing(30.);

Cost

caution

Refreshing the application can be expensive: be careful not to refresh your application too often.

An application refresh implies to rebuild all hierarchies and aggregate providers.
Depending on the size of the application, it can take a long time.