Dates to Include Filter

When using DirectQuery you may want to only include a subset of data available on the remote database in a manner that the default directQueryDatesFilter does not support. The directQueryDatesFilter bean is used to tell which dates to include (or exclude) from the DirectQuery data node cube. This creates an ICondition that filters the data cube based on the AsOfDate field.

Default Implementation

The default implementation of the directQueryDatesFilter bean will either include or exclude all the dates provided to the DirectQueryDatesFilterSpringProperties configuration properties. The default bean is defined in DirectQueryDatesFilterConfig as:

@Bean
@ConditionalOnMissingBean
public ICondition directQueryDatesFilter(@Autowired DirectQueryDatesFilterSpringProperties directQueryDatesToIncludeSpringProperties){

	var datesToInclude = directQueryDatesToIncludeSpringProperties.datesToInclude();
	if(datesToInclude != null && !datesToInclude.isEmpty()){
		log.info("The following dates will be included in DirectQuery: " + datesToInclude);
		// Allow DirectQuery node to include any of the following dates:
		return includeAllDates(datesToInclude);
	}

	final var datesToExclude = directQueryDatesToIncludeSpringProperties.datesToExclude();
	if(datesToExclude != null && !datesToExclude.isEmpty()) {
		log.info("The following dates will be excluded in DirectQuery: " + datesToExclude);
		// Restrict DirectQuery node to exclude all of the following dates:
		return excludeAllDates(datesToExclude);
	}

	// No condition to create
	return null;
}

The DirectQueryDatesFilterConfig class also includes two helper methods that can be used to create simple conditions:

/**
* Creates a cube {@link ICondition} that will INCLUDE all the provided dates.
*/
protected ICondition includeAllDates(Collection<LocalDate> datesToInclude){
    return new OrCondition(datesToInclude.stream()
		    .map(date ->
				    // Condition: AsOfDate Cube Field == date
				    new SubCondition(AS_OF_DATE, new EqualCondition(date))
		    ).toList()
    );
}

/**
* Creates a cube {@link ICondition} that will EXCLUDE all the provided dates.
*/
protected ICondition excludeAllDates(Collection<LocalDate> datesToExclude){
    return new AndCondition(datesToExclude.stream()
		    .map(date ->
				    // Condition: AsOfDate Cube Field != date
				    new SubCondition(AS_OF_DATE, new NotCondition(new EqualCondition(date)))
		    ).toList()
    );
}

Custom Implementations

To override this bean you need to create a @Bean that returns an com.quartetfs.fwk.filtering.ICondition. This will replace the default directQueryDatesFilter bean with your custom bean.

SubConditions

When creating a custom condition, it is important to ensure each nested condition is wrapped in a SubCondition where the AsOfDate is used as the matching field. Below are some examples.

Examples

Here are some simple examples of how to customize the directQueryDatesFilter bean.

Include All Available Data in DirectQuery

If we wanted to include all data available on our remote database, we simply return null and there will be no condition on what data can be loaded into the DirectQuery data node:

@Configuration
public class CustomDatesToIncludeBeanConfig {
	@Bean
	public ICondition noRestrictions() {
		// No restrictions on the DirectQuery node
		return null;
	}
}

Only Include Specific Dates

In this example, we are giving the DirectQuery data node access to a specific set of dates. Here we do not extend the InitialDataDirectQueryConfig and instead create our own ICondition from scratch to include only the dates we want.

@Configuration
public class CustomDatesToIncludeBeanConfig {
	@Bean
	public ICondition onlyIncludeTheseDates() {
		// Restrict DirectQuery node to INCLUDE the specific dates:
		List<SubCondition> includeConditions = new ArrayList<>();
		// Add our custom dates
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 1))));
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 2))));
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 3))));
		// Use an OR condition so that any of the dates can be included
		return new OrCondition(includeConditions);
	}
}

Include Year of Dates

In this example, we are extending the InitialDataDirectQueryConfig and using its helper methods to construct our condition. This will include all dates from the last year up to today.

@Configuration
public static class CustomDatesToIncludeBeanConfig extends InitialDataDirectQueryConfig {
	@Bean
	public ICondition includeOneYearOfData(){
		// Include a year worth of dats in DQ data node:
		final LocalDate today = LocalDate.now();
		final LocalDate lastYear = today.minusYears(1);
		final List<LocalDate> yearOfDatesToInclude = lastYear.datesUntil(today).toList();
		// Allow DirectQuery node to include one year worth of dates:
		return super.includeAllDates(yearOfDatesToInclude);
	}
}

Include All Data in Database Except This Week

In this example, we are extending the InitialDataDirectQueryConfig and using its helper methods to construct our condition. This will exclude the last seven days (including today) from the DirectQuery data node.

@Configuration
public static class CustomDatesToIncludeBeanConfig extends InitialDataDirectQueryConfig {
	@Bean
	public ICondition ecludeThisWeek(){
		// Exclude the latest seven days (including today)
		final LocalDate today = LocalDate.now();
		final LocalDate firstDayInMemory = today.minusDays(7);
		final List<LocalDate> daysIncludedIn_InMemoryCube = firstDayInMemory.datesUntil(today.plusDays(1)).toList();
		// Exclude these dates from the DirectQuery node
		return super.excludeAllDates(daysIncludedIn_InMemoryCube);
	}
}

​Dates to Include Filter

​Default Implementation

​Custom Implementations

​SubConditions

​Examples

​Include All Available Data in DirectQuery

​Only Include Specific Dates

​Include Year of Dates

​Include All Data in Database Except This Week