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