dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #09887
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2679: Simplification in datamart
------------------------------------------------------------
revno: 2679
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2011-01-20 22:08:24 +0100
message:
Simplification in datamart
modified:
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.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-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-01-10 15:53:32 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-01-20 21:08:24 +0000
@@ -41,6 +41,15 @@
String ID = CrossTabService.class.getName();
/**
+ * Filters and returns the DataElementOperands with data from the given
+ * collection of DataElementOperands.
+ *
+ * @param operands the DataElementOperands.
+ * @return the DataElementOperands with data.
+ */
+ Collection<DataElementOperand> getOperandsWithData( Collection<DataElementOperand> operands );
+
+ /**
* Creates and populates the crosstab table. Operands without data will be
* removed from the operands argument collection.
*
@@ -58,13 +67,6 @@
void dropCrossTabTable( String key );
/**
- * Trims the crosstab table.
- *
- * @param operands the DataElementOperands with data.
- */
- void trimCrossTabTable( Collection<DataElementOperand> operands, String key );
-
- /**
* Gets all CrossTabDataValues for the given collection of period ids and source ids.
*
* @param dataElementIds the dataelement identifiers.
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-01-20 19:55:28 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-01-20 21:08:24 +0000
@@ -29,10 +29,8 @@
import java.util.ArrayList;
import java.util.Collection;
-import java.util.HashSet;
import java.util.List;
import java.util.Map;
-import java.util.Set;
import org.amplecode.quick.BatchHandler;
import org.amplecode.quick.BatchHandlerFactory;
@@ -86,13 +84,16 @@
// CrossTabService implementation
// -------------------------------------------------------------------------
+ public Collection<DataElementOperand> getOperandsWithData( Collection<DataElementOperand> operands )
+ {
+ return crossTabStore.getOperandsWithData( operands );
+ }
+
public List<String> populateCrossTabTable( final Collection<DataElementOperand> operands,
final Collection<Integer> periodIds, final Collection<Integer> organisationUnitIds )
{
if ( validate( operands, periodIds, organisationUnitIds ) )
{
- final Set<DataElementOperand> operandsWithData = new HashSet<DataElementOperand>();
-
final PaginatedList<DataElementOperand> operandList = new PaginatedList<DataElementOperand>( operands, MAX_COLUMNS );
final List<String> crossTabTableKeys = new ArrayList<String>();
@@ -105,17 +106,13 @@
crossTabTableKeys.add( key );
- final Set<DataElementOperand> operandsWithDataInPage = new HashSet<DataElementOperand>();
-
crossTabStore.dropCrossTabTable( key );
crossTabStore.createCrossTabTable( operandPage, key );
-
- log.debug( "Created crosstab table for key: " + key );
-
+
final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class );
batchHandler.setTableName( CrossTabStore.TABLE_NAME + key );
batchHandler.init();
-
+
for ( final Integer periodId : periodIds )
{
for ( final Integer sourceId : organisationUnitIds )
@@ -142,8 +139,6 @@
if ( value != null )
{
hasValues = true;
- operandsWithData.add( operand );
- operandsWithDataInPage.add( operand );
}
valueList.add( value );
@@ -158,14 +153,9 @@
batchHandler.flush();
- trimCrossTabTable( operandsWithDataInPage, key );
-
- log.info( "Populated crosstab table for key: " + key );
-
+ log.info( "Populated crosstab table for key: " + key );
}
- operands.retainAll( operandsWithData ); // Remove operands without data
-
return crossTabTableKeys;
}
@@ -177,21 +167,6 @@
crossTabStore.dropCrossTabTable( key );
}
- public void trimCrossTabTable( Collection<DataElementOperand> operands, String key )
- {
- // TODO use H2 in-memory table for datavaluecrosstab table ?
- // TODO optimize retrieval by dividing up based on agg operator / type?
-
- if ( operands != null && key != null )
- {
- crossTabStore.createTrimmedCrossTabTable( operands, key );
-
- crossTabStore.dropCrossTabTable( key );
-
- crossTabStore.renameTrimmedCrossTabTable( key );
- }
- }
-
public Collection<CrossTabDataValue> getCrossTabDataValues( Collection<DataElementOperand> operands,
Collection<Integer> periodIds, Collection<Integer> sourceIds, List<String> keys )
{
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-01-10 15:53:32 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-01-20 21:08:24 +0000
@@ -44,6 +44,15 @@
final String TABLE_NAME_TRIMMED = "datavaluecrosstabtrimmed_";
/**
+ * Filters and returns the DataElementOperands with data from the given
+ * collection of DataElementOperands.
+ *
+ * @param operands the DataElementOperands.
+ * @return the DataElementOperands with data.
+ */
+ Collection<DataElementOperand> getOperandsWithData( Collection<DataElementOperand> operands );
+
+ /**
* Creates a crosstab table where the first column is the period identifier,
* the second column is the source identifer, and each subsequent column
* corresponds to an operand.
@@ -58,19 +67,6 @@
void dropCrossTabTable( String key );
/**
- * Renames the trimmed crosstab table to the regular crosstab table.
- */
- void renameTrimmedCrossTabTable( String key );
-
- /**
- * Creates a trimmed crosstab table based on the regular crosstab table.
- * Trimming implies removing columns without data.
- *
- * @param operands the DataElementOperands with data.
- */
- void createTrimmedCrossTabTable( Collection<DataElementOperand> operands, String key );
-
- /**
* Gets all CrossTabDataValues for the given collection of period ids and source ids.
*
* @param dataElementIds the dataelement identifiers.
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-01-10 15:53:32 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-01-20 21:08:24 +0000
@@ -64,6 +64,30 @@
// CrossTabStore implementation
// -------------------------------------------------------------------------
+ public Collection<DataElementOperand> getOperandsWithData( Collection<DataElementOperand> operands )
+ {
+ final Collection<DataElementOperand> operandsWithData = new ArrayList<DataElementOperand>();
+
+ final StatementHolder holder = statementManager.getHolder();
+
+ for ( DataElementOperand operand : operands )
+ {
+ final String sql =
+ "SELECT COUNT(*) FROM datavalue " +
+ "WHERE dataelementid=" + operand.getDataElementId() + " " +
+ "AND categoryoptioncomboid=" + operand.getOptionComboId();
+
+ Integer count = holder.queryForInteger( sql );
+
+ if ( count != null && count > 0 )
+ {
+ operandsWithData.add( operand );
+ }
+ }
+
+ return operandsWithData;
+ }
+
public void createCrossTabTable( final List<DataElementOperand> operands, String key )
{
final StatementHolder holder = statementManager.getHolder();
@@ -113,59 +137,6 @@
holder.close();
}
}
-
- public void renameTrimmedCrossTabTable( String key )
- {
- final StatementHolder holder = statementManager.getHolder();
-
- try
- {
- final String sql = "ALTER TABLE " + TABLE_NAME_TRIMMED + key + " RENAME TO " + TABLE_NAME + key;
-
- holder.getStatement().executeUpdate( sql );
- }
- catch ( SQLException ex )
- {
- throw new RuntimeException( "Failed to rename trimmed crosstab table", ex );
- }
- finally
- {
- holder.close();
- }
- }
-
- public void createTrimmedCrossTabTable( Collection<DataElementOperand> operands, String key )
- {
- final StatementHolder holder = statementManager.getHolder();
-
- try
- {
- final StringBuffer buffer = new StringBuffer( "CREATE TABLE " + TABLE_NAME_TRIMMED + key + " AS SELECT periodid, sourceid, " );
-
- for ( final DataElementOperand operand : operands )
- {
- buffer.append( operand.getColumnName() ).append( ", " );
- }
-
- if ( buffer.length() > 1 )
- {
- buffer.deleteCharAt( buffer.length() - 1 );
- buffer.deleteCharAt( buffer.length() - 1 );
- }
-
- buffer.append( " FROM " + TABLE_NAME + key );
-
- holder.getStatement().executeUpdate( buffer.toString() );
- }
- catch ( SQLException ex )
- {
- throw new RuntimeException( "Failed to get crosstab table columns", ex );
- }
- finally
- {
- holder.close();
- }
- }
// -------------------------------------------------------------------------
// CrossTabDataValue
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-20 08:46:49 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-20 21:08:24 +0000
@@ -239,6 +239,18 @@
log.info( "Filtered data elements, number of operands: " + allOperands.size() + ", " + TimeUtils.getHMS() );
// ---------------------------------------------------------------------
+ // Remove operands without data
+ // ---------------------------------------------------------------------
+
+ allOperands = new HashSet<DataElementOperand>( crossTabService.getOperandsWithData( allOperands ) );
+
+ nonCalculatedOperands.retainAll( allOperands );
+ indicatorOperands.retainAll( allOperands );
+ calculatedOperands.retainAll( allOperands );
+
+ log.info( "Number of operands with data: " + allOperands.size() + ", "+ TimeUtils.getHMS() );
+
+ // ---------------------------------------------------------------------
// Create and trim crosstabtable
// ---------------------------------------------------------------------
@@ -254,15 +266,7 @@
return 0;
}
- log.info( "Number of crosstab tables: " + keys.size() + ", number of operands with data: " + allOperands.size() + ", " + TimeUtils.getHMS() );
-
- // ---------------------------------------------------------------------
- // Remove operands without data
- // ---------------------------------------------------------------------
-
- nonCalculatedOperands.retainAll( allOperands );
- indicatorOperands.retainAll( allOperands );
- calculatedOperands.retainAll( allOperands );
+ log.info( "Number of crosstab tables: " + keys.size() + ", " + TimeUtils.getHMS() );
// ---------------------------------------------------------------------
// Delete existing aggregated data
