dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20513
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9388: Analytics, impl filtering
------------------------------------------------------------
revno: 9388
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2012-12-21 17:50:36 +0100
message:
Analytics, impl filtering
modified:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2012-12-21 14:12:04 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2012-12-21 16:50:36 +0000
@@ -49,7 +49,7 @@
public static final String ORGUNIT_DIM_ID = "ou";
public static final String VALUE_ID = "value";
- private static final String LEVEL_PREFIX = "uidlevel";
+ public static final String LEVEL_PREFIX = "uidlevel";
private Map<String, List<String>> dimensions = new HashMap<String, List<String>>();
@@ -120,6 +120,11 @@
return list;
}
+
+ public List<String> getFilterNames()
+ {
+ return new ArrayList<String>( filters.keySet() );
+ }
public Map<String, List<String>> getDimensionMap()
{
@@ -140,11 +145,6 @@
return map;
}
- public void setDimension( String dimension, List<String> values )
- {
- dimensions.put( dimension, values );
- }
-
/**
* Returns the dimensions which are part of dimensions and filters. If any
* such dimensions exist this object is in an illegal state.
@@ -229,7 +229,7 @@
@Override
public String toString()
{
- return dimensions != null ? dimensions.toString() : "";
+ return "[Dimensions: " + dimensions + ", Filters: " + filters + "]";
}
// -------------------------------------------------------------------------
@@ -302,6 +302,27 @@
{
this.organisationUnitLevel = organisationUnitLevel;
}
+
+ // -------------------------------------------------------------------------
+ // Get and set helpers for dimensions or filter
+ // -------------------------------------------------------------------------
+
+ public List<String> getDimensionOrFilter( String key )
+ {
+ return dimensions.containsKey( key ) ? dimensions.get( key ) : filters.get( key );
+ }
+
+ public void resetDimensionOrFilter( String key, List<String> values )
+ {
+ if ( dimensions.containsKey( key ) )
+ {
+ dimensions.put( key, values );
+ }
+ else if ( filters.containsKey( key ) )
+ {
+ filters.put( key, values );
+ }
+ }
// -------------------------------------------------------------------------
// Get and set helpers for dimensions
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-19 14:35:23 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-21 16:50:36 +0000
@@ -56,9 +56,6 @@
//TODO create data mart for average, less-than yearly data elements
// aggregate in time dimension only
// insert into standard analytics table?
- //TODO filter, exclude from select
-
- //TODO investigate whether quarterly partitions are faster
@Autowired
private AnalyticsManager analyticsManager;
@@ -93,7 +90,7 @@
{
Timer t = new Timer().start();
- int optimalQueries = MathUtils.getWithin( SystemUtils.getCpuCores(), 1, 4 );
+ int optimalQueries = MathUtils.getWithin( SystemUtils.getCpuCores(), 1, 6 );
List<DataQueryParams> queries = queryPlanner.planQuery( params, optimalQueries );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2012-12-21 14:12:04 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2012-12-21 16:50:36 +0000
@@ -45,6 +45,8 @@
public class DefaultQueryPlanner
implements QueryPlanner
{
+ //TODO call getLevelOrgUnitMap once
+
@Autowired
private OrganisationUnitService organisationUnitService;
@@ -86,6 +88,13 @@
}
}
+ // ---------------------------------------------------------------------
+ // Set filters for each period type and organisation unit level
+ // ---------------------------------------------------------------------
+
+ queries = setFilterByPeriodType( queries );
+ queries = setFilterByOrgUnitLevel( queries );
+
if ( queries.size() >= optimalQueries )
{
return queries;
@@ -95,7 +104,7 @@
// Group by organisation unit
// ---------------------------------------------------------------------
- queries = splitByDimension( queries, DataQueryParams.ORGUNIT_DIM_ID, optimalQueries );
+ queries = splitByDimensionOrFilter( queries, DataQueryParams.ORGUNIT_DIM_ID, optimalQueries );
if ( queries.size() >= optimalQueries )
{
@@ -106,7 +115,7 @@
// Group by data element
// ---------------------------------------------------------------------
- return splitByDimension( queries, DataQueryParams.DATAELEMENT_DIM_ID, optimalQueries );
+ return splitByDimensionOrFilter( queries, DataQueryParams.DATAELEMENT_DIM_ID, optimalQueries );
}
public boolean canQueryFromDataMart( DataQueryParams params )
@@ -121,7 +130,7 @@
/**
* Splits the given list of queries in sub queries on the given dimension.
*/
- private List<DataQueryParams> splitByDimension( List<DataQueryParams> queries, String dimension, int optimalQueries )
+ private List<DataQueryParams> splitByDimensionOrFilter( List<DataQueryParams> queries, String dimension, int optimalQueries )
{
int optimalForSubQuery = MathUtils.divideToCeil( optimalQueries, queries.size() );
@@ -129,7 +138,7 @@
for ( DataQueryParams query : queries )
{
- List<String> values = query.getDimensions().get( dimension );
+ List<String> values = query.getDimensionOrFilter( dimension );
if ( values == null || values.isEmpty() )
{
@@ -142,7 +151,7 @@
for ( List<String> valuePage : valuePages )
{
DataQueryParams subQuery = new DataQueryParams( query );
- subQuery.setDimension( dimension, valuePage );
+ subQuery.resetDimensionOrFilter( dimension, valuePage );
subQueries.add( subQuery );
}
}
@@ -242,6 +251,41 @@
}
/**
+ * Replaces the period filter with individual filters for each period type.
+ */
+ private List<DataQueryParams> setFilterByPeriodType( List<DataQueryParams> queries )
+ {
+ for ( DataQueryParams params : queries )
+ {
+ if ( params.getFilterPeriods() != null && !params.getFilterPeriods().isEmpty() )
+ {
+ params.getFilters().putAll( getPeriodTypePeriodMap( params.getFilterPeriods() ) );
+ params.getFilters().remove( DataQueryParams.PERIOD_DIM_ID );
+ }
+ }
+
+ return queries;
+ }
+
+ /**
+ * Replaces the organisation unit filter with individual filters for each
+ * organisation unit level.
+ */
+ private List<DataQueryParams> setFilterByOrgUnitLevel( List<DataQueryParams> queries )
+ {
+ for ( DataQueryParams params : queries )
+ {
+ if ( params.getFilterOrganisationUnits() != null && !params.getFilterOrganisationUnits().isEmpty() )
+ {
+ params.getFilters().putAll( getLevelColumnOrgUnitMap( params.getFilterOrganisationUnits() ) );
+ params.getFilters().remove( DataQueryParams.ORGUNIT_DIM_ID );
+ }
+ }
+
+ return queries;
+ }
+
+ /**
* Creates a mapping between period type name and period for the given periods.
*/
private ListMap<String, String> getPeriodTypePeriodMap( Collection<String> isoPeriods )
@@ -275,4 +319,22 @@
return map;
}
+
+ /**
+ * Creates a mapping between the level column and organisation unit for the
+ * given organisation units.
+ */
+ private ListMap<String, String> getLevelColumnOrgUnitMap( Collection<String> orgUnits )
+ {
+ ListMap<String, String> map = new ListMap<String, String>();
+
+ for ( String orgUnit : orgUnits )
+ {
+ int level = organisationUnitService.getLevelOfOrganisationUnit( orgUnit );
+
+ map.putValue( DataQueryParams.LEVEL_PREFIX + level, orgUnit );
+ }
+
+ return map;
+ }
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-21 12:59:39 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-21 16:50:36 +0000
@@ -84,6 +84,11 @@
{
sql += sqlHelper.whereAnd() + " " + dim + " in (" + getQuotedCommaDelimitedString( dimensionMap.get( dim ) ) + " ) ";
}
+
+ for ( String filter : params.getFilterNames() )
+ {
+ sql += sqlHelper.whereAnd() + " " + filter + " in (" + getQuotedCommaDelimitedString( params.getFilters().get( filter ) ) + " ) ";
+ }
sql += "group by " + getCommaDelimitedString( dimensions );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2012-12-21 14:12:04 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2012-12-21 16:50:36 +0000
@@ -246,6 +246,42 @@
queryPlanner.planQuery( params, 4 );
}
+
+ /**
+ * Query filters span 2 partitions. Splits in 2 queries for each partition,
+ * then splits in 2 queries on organisation units to satisfy optimal for a
+ * total of 4 queries.
+ */
+ @Test
+ public void planQueryH()
+ {
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
+ params.setFilterPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3", "2000Q4", "2001Q1", "2001Q2" ) );
+
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+
+ assertEquals( 4, queries.size() );
+ }
+
+ /**
+ * Query spans 3 period types. Splits in 3 queries for each period type, then
+ * splits in 2 queries on data elements units to satisfy optimal for a total
+ * of 6 queries.
+ */
+ @Test
+ public void planQueryI()
+ {
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setFilterOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
+ params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000", "200002", "200003", "200004" ) );
+
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+
+ assertEquals( 6, queries.size() );
+ }
// -------------------------------------------------------------------------
// Supportive methods
