Java Code Examples for org.pentaho.metadata.model.LogicalColumn#setAggregationType()

The following examples show how to use org.pentaho.metadata.model.LogicalColumn#setAggregationType() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: SqlOpenFormulaIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

6 votes

/**
 * In this test we try to see to it :<br>
 * - that the formula engine picks the 2 specified columns from 2 different business tables<br>
 * - that we calculate the sum of the multiplication <br>
 */
@Test
public void testMultiTableColumnFormulasAggregate() throws Exception {
  LogicalColumn quantityOrdered = getOrdersModel().findLogicalColumn( "BC_ORDER_DETAILS_QUANTITYORDERED" );
  Assert.assertNotNull( "Expected to find the business column 'quantity ordered'", quantityOrdered );
  LogicalColumn buyPrice = getOrdersModel().findLogicalColumn( "BC_PRODUCTS_BUYPRICE" );
  Assert.assertNotNull( "Expected to find the business column 'buy price'", buyPrice );

  // let's remove the aggregations of the quantity ordered...
  //
  AggregationType qaBackup = quantityOrdered.getAggregationType();
  AggregationType paBackup = buyPrice.getAggregationType();
  quantityOrdered.setAggregationType( AggregationType.NONE );
  buyPrice.setAggregationType( AggregationType.NONE );

  // This changes the expected result...
  //
  String formula = "SUM( [BT_ORDER_DETAILS.BC_ORDER_DETAILS_QUANTITYORDERED] * [BT_PRODUCTS.BC_PRODUCTS_BUYPRICE] )";
  String sql = "SUM( BT_ORDER_DETAILS.QUANTITYORDERED  *  BT_PRODUCTS.BUYPRICE )";

  handleFormula( getOrdersModel(), "Hypersonic", formula, sql );

  // Set it back to the way it was for further testing.
  quantityOrdered.setAggregationType( qaBackup );
  buyPrice.setAggregationType( paBackup );
}

Example 2

Source File: SqlOpenFormulaIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

6 votes

/**
 * In this test we try to test :<br>
 * - if the formula engine picks the 2 specified columns from 2 different business tables<br>
 * - if we calculate the multiplication of the sums <br>
 */
@Test
public void testMultiTableColumnFormulasAggregate2() throws Exception {
  LogicalColumn quantityOrdered = getOrdersModel().findLogicalColumn( "BC_ORDER_DETAILS_QUANTITYORDERED" );
  Assert.assertNotNull( "Expected to find the business column 'quantity ordered'", quantityOrdered );
  LogicalColumn buyPrice = getOrdersModel().findLogicalColumn( "BC_PRODUCTS_BUYPRICE" );
  Assert.assertNotNull( "Expected to find the business column 'buy price'", buyPrice );

  // let's enable the aggregations of the quantity ordered...
  //
  AggregationType qaBackup = quantityOrdered.getAggregationType();
  AggregationType paBackup = buyPrice.getAggregationType();
  quantityOrdered.setAggregationType( AggregationType.SUM );
  buyPrice.setAggregationType( AggregationType.SUM );

  // This changes the expected result...
  //
  String formula = "[BT_ORDER_DETAILS.BC_ORDER_DETAILS_QUANTITYORDERED] * [BT_PRODUCTS.BC_PRODUCTS_BUYPRICE]";
  String sql = "SUM(BT_ORDER_DETAILS.QUANTITYORDERED)  *  SUM(BT_PRODUCTS.BUYPRICE)";

  handleFormula( getOrdersModel(), "Hypersonic", formula, sql );

  // Set it back to the way it was for further testing.
  quantityOrdered.setAggregationType( qaBackup );
  buyPrice.setAggregationType( paBackup );
}

Example 3

Source File: AggregationScenariosIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

/**
 * Scenario: we have 2 sums and we want to calculate a ratio.<br>
 * The aggregation on the ratio is obviously "none".<br>
 * However, the generator has to keep in mind that it still needs to generate a group by.<br>
 * <br>
 * This is a simple one-table example.<br>
 * 
 */
@Test
public void testRatioOfSumsGroupBy() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$

  // dimension column d1
  //
  final LogicalColumn d1 = new LogicalColumn();
  d1.setId( "d1" ); //$NON-NLS-1$
  d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
  d1.setLogicalTable( bt1 );
  d1.setAggregationType( AggregationType.NONE );
  d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );

  bt1.addLogicalColumn( d1 );
  mainCat.addLogicalColumn( d1 );

  // Sum column bc1
  //
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bc1.setAggregationType( AggregationType.SUM );
  bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  // Sum column bc2
  //
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt1 );
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  // A calculated column: ratio
  //
  final LogicalColumn ratio = new LogicalColumn();
  ratio.setId( "ratio" ); //$NON-NLS-1$
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "[bt1.bc1] / [bt1.bc2]" ); //$NON-NLS-1$
  ratio.setLogicalTable( bt1 );
  ratio.setAggregationType( AggregationType.NONE );
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( ratio );
  mainCat.addLogicalColumn( ratio );

  DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new Selection( null, d1, null ) );
  myTest.getSelections().add( new Selection( null, ratio, null ) );

  MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper.assertEqualsIgnoreWhitespaces(
      "SELECT bt1.d AS COL0 , SUM(bt1.a) / SUM(bt1.b) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
      query.getQuery() );
}

Example 4

Source File: AggregationScenariosIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

/**
 * Scenario: we want to make a sum of a ratio<br>
 * The aggregation on the ratio is obviously "SUM".<br>
 * However, the aggregation on the used columns is none.<br>
 * <br>
 * This is a simple one-table example.<br>
 * 
 */
@Test
public void testSumOfRatioGroupBy() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$

  // dimension column d1
  //
  final LogicalColumn d1 = new LogicalColumn();
  d1.setId( "d1" ); //$NON-NLS-1$
  d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
  d1.setLogicalTable( bt1 );
  d1.setAggregationType( AggregationType.NONE );
  d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );

  bt1.addLogicalColumn( d1 );
  mainCat.addLogicalColumn( d1 );

  // Sum column bc1
  //
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bc1.setAggregationType( AggregationType.NONE );
  bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  // Sum column bc2
  //
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt1 );
  bc2.setAggregationType( AggregationType.NONE );
  bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  // A calculated column: ratio
  //
  final LogicalColumn ratio = new LogicalColumn();
  ratio.setId( "ratio" ); //$NON-NLS-1$
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "SUM( [bt1.bc1] / [bt1.bc2] )" ); //$NON-NLS-1$
  ratio.setLogicalTable( bt1 );
  ratio.setAggregationType( AggregationType.SUM );
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( ratio );
  mainCat.addLogicalColumn( ratio );

  DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new Selection( null, d1, null ) );
  myTest.getSelections().add( new Selection( null, ratio, null ) );

  MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper.assertEqualsIgnoreWhitespaces(
      "SELECT bt1.d AS COL0 , SUM( bt1.a / bt1.b ) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
      query.getQuery() );
}

Example 5

Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

public LogicalModel getDefaultModel() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" );
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" );

  LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" );
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" );
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  LogicalColumn bcs1 = new LogicalColumn();
  bcs1.setId( "bcs1" );
  bcs1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" );
  bcs1.setAggregationType( AggregationType.SUM );
  bcs1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bcs1 );
  mainCat.addLogicalColumn( bcs1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" );
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" );

  LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" );
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalColumn bce2 = new LogicalColumn();
  bce2.setId( "bce2" ); //$NON-NLS-1$
  bce2.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  bce2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "[bt2.bc2] * 2" ); //$NON-NLS-1$
  bce2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bce2 );
  mainCat.addLogicalColumn( bce2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" );
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt3" );

  LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" );
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc3" );
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  final LogicalRelationship rl1 = new LogicalRelationship();

  rl1.setFromTable( bt1 );
  rl1.setToTable( bt2 );
  rl1.setFromColumn( bc1 );
  rl1.setToColumn( bc2 );

  final LogicalRelationship rl2 = new LogicalRelationship();

  rl2.setToTable( bt2 );
  rl2.setFromTable( bt3 );
  rl2.setFromColumn( bc3 );
  rl2.setToColumn( bc2 );

  model.getLogicalTables().add( bt1 );
  model.getLogicalTables().add( bt2 );
  model.getLogicalTables().add( bt3 );
  model.getLogicalRelationships().add( rl1 );
  model.getLogicalRelationships().add( rl2 );

  return model;
}

Example 6

Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

/**
 * Scenario 1b: Two Tables are outer joined with an aggregate
 */
@Test
public void testOuterJoinScenario1b() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  mainCat.addLogicalColumn( bc1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$NON-NLS-1$
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._0_N );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1",
          query.getQuery() ); //$NON-NLS-1$
}

Example 7

Source File: AdvancedQueryIT.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

/**
 * Scenario 1c: Two Tables are outer joined with an aggregate constraint
 */
@Test
public void testOuterJoinScenario1c() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  mainCat.addLogicalColumn( bc1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$NON-NLS-1$
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._0_N );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
  myTest.getConstraints().add( new Constraint( CombinationType.AND, "[cat_01.bc2] > 1" ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1 HAVING ( SUM(bt2.pc2) > 1 )",
          query.getQuery() ); //$NON-NLS-1$
}

Example 8

Source File: MondrianModelExporterTest.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

@Test
public void testLevelAnnotations() throws Exception {

  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );
  level.setFormatter( "com.pentaho.formatter" );

  level.getAnnotations().add( new OlapAnnotation( "GeoRole", "city" ) );
  level.getAnnotations().add( new OlapAnnotation( "RequiredParents", "country,state" ) );

  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  level.setLogicalColumns( new ArrayList<LogicalColumn>() );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, "select * from customer" );
  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE,
      TargetTableType.INLINE_SQL );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  MondrianModelExporter exporter = new MondrianModelExporter( businessModel, "en_US" );
  String data = exporter.createMondrianModelXML();

  TestHelper.assertEqualsIgnoreWhitespaces(
    "<Schema name=\"model\">\n"
      + "  <Dimension name=\"Dim1\">\n"
      + "    <Hierarchy name=\"Hier1\" hasAll=\"false\">\n"
      + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n"
      + "         <![CDATA[select * from customer]]>\n"
      + "        </SQL>\n"
      + "    </View>\n"
      + "      <Level name=\"Lvl1\" uniqueMembers=\"false\" column=\"pc1\" type=\"Numeric\" formatter=\"com.pentaho.formatter\">\n"
      + "        <Annotations>\n"
      + "          <Annotation name=\"GeoRole\">city</Annotation>\n"
      + "          <Annotation name=\"RequiredParents\">country,state</Annotation>\n"
      + "        </Annotations>\n"
      + "      </Level>\n"
      + "    </Hierarchy>\n"
      + "  </Dimension>\n"
      + "  <Cube name=\"Cube1\">\n"
      + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n"
      + "         <![CDATA[select * from customer]]>\n"
      + "        </SQL>\n" + "    </View>\n"
      + "    <DimensionUsage name=\"Dim1\" source=\"Dim1\" foreignKey=\"pc2\"/>\n"
      + "    <Measure name=\"bc1\" column=\"pc1\" aggregator=\"sum\"/>\n"
      + "  </Cube>\n"
      + "</Schema>", data );
}

Example 9

Source File: MondrianModelExporterTest.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

@Test
public void testLevelProperties() throws Exception {

  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );

  level.getAnnotations().add( new OlapAnnotation( "GeoRole", "city" ) );

  List<LogicalColumn> cols = new ArrayList<LogicalColumn>();
  LogicalColumn lcLat = new LogicalColumn();
  lcLat.setName( new LocalizedString( "en_US", "latitude" ) );
  lcLat.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Latitude" );
  lcLat.setDataType( DataType.NUMERIC );
  LogicalColumn lcLon = new LogicalColumn();
  lcLon.setName( new LocalizedString( "en_US", "longitude" ) );
  lcLon.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Longitude" );
  lcLon.setDataType( DataType.NUMERIC );

  cols.add( lcLat );
  cols.add( lcLon );

  level.setLogicalColumns( cols );

  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  // level.setLogicalColumns(new ArrayList<LogicalColumn>());
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, "select * from customer" );
  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE,
      TargetTableType.INLINE_SQL );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  MondrianModelExporter exporter = new MondrianModelExporter( businessModel, "en_US" );
  String data = exporter.createMondrianModelXML();

  TestHelper.assertEqualsIgnoreWhitespaces( "<Schema name=\"model\">\n" + "  <Dimension name=\"Dim1\">\n"
      + "    <Hierarchy name=\"Hier1\" hasAll=\"false\">\n" + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n" + "         <![CDATA[select * from customer]]>\n" + "        </SQL>\n"
      + "    </View>\n" + "      <Level name=\"Lvl1\" uniqueMembers=\"false\" column=\"pc1\" type=\"Numeric\">\n"
      + "        <Annotations>\n" + "          <Annotation name=\"GeoRole\">city</Annotation>\n"
      + "        </Annotations>\n" + "        <Property name=\"latitude\" column=\"Latitude\" type=\"Numeric\"/>\n"
      + "        <Property name=\"longitude\" column=\"Longitude\" type=\"Numeric\"/>\n" + "      </Level>\n"
      + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Cube name=\"Cube1\">\n" + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n" + "         <![CDATA[select * from customer]]>\n" + "        </SQL>\n"
      + "    </View>\n" + "    <DimensionUsage name=\"Dim1\" source=\"Dim1\" foreignKey=\"pc2\"/>\n"
      + "    <Measure name=\"bc1\" column=\"pc1\" aggregator=\"sum\"/>\n" + "  </Cube>\n"
      + "</Schema>", data );
}

Example 10

Source File: MondrianModelExporterTest.java From pentaho-metadata with GNU Lesser General Public License v2.1

4 votes

private LogicalModel getTestModel( TargetTableType tableType, String targetTable, String targetSchema,
    boolean hiddenMembers ) {
  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );
  level.setHidden( hiddenMembers );
  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  measure.setHidden( hiddenMembers );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  level.setLogicalColumns( new ArrayList<LogicalColumn>() );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, targetTable );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_SCHEMA, targetSchema );

  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE, tableType );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  return businessModel;
}