Exemplo n.º 1
0
  public SaikuFilter getFilter(
      String queryName,
      String filtername,
      String dimensionName,
      String hierarchyName,
      String levelName) {
    IQuery query = getIQuery(queryName);
    CellSet cs = query.getCellset();
    List<SimpleCubeElement> members = new ArrayList<SimpleCubeElement>();
    SimpleCubeElement dimension = null;
    SimpleCubeElement hierarchy = null;
    Set<MetadataElement> mset = new HashSet<MetadataElement>();

    if (cs != null) {
      List<CellSetAxis> axes = new ArrayList<CellSetAxis>();
      axes.addAll(cs.getAxes());
      axes.add(cs.getFilterAxis());
      for (CellSetAxis axis : axes) {
        int posIndex = 0;
        for (Hierarchy h : axis.getAxisMetaData().getHierarchies()) {
          if (h.getName().equals(hierarchyName)) {
            if (hierarchy == null) {
              hierarchy = new SimpleCubeElement(h.getName(), h.getUniqueName(), h.getCaption());
              Dimension d = h.getDimension();
              dimension = new SimpleCubeElement(d.getName(), d.getUniqueName(), d.getCaption());
            }
            if (h.getLevels().size() == 1) {
              break;
            }

            for (Position pos : axis.getPositions()) {
              Member m = pos.getMembers().get(posIndex);
              if (m.getLevel().getName().equals(levelName)) {
                mset.add(m);
              }
            }
            break;
          }
          posIndex++;
        }
      }
      if (mset.size() == 0) {
        QueryDimension qd = query.getDimension(dimensionName);
        if (qd != null && qd.getAxis().getLocation() != null) {
          for (Selection sel : qd.getInclusions()) {
            if ((sel.getRootElement() instanceof Member)) {
              Member m = ((Member) sel.getRootElement());
              if (m.getLevel().getName().equals(levelName)) {
                mset.add(m);
              }
            }
          }
        }
      }
      members = ObjectUtil.convert2Simple(mset);
      Collections.sort(members, new SaikuUniqueNameComparator());
      log.debug("Create Filters: Found members in the result or query: " + members.size());
    }
    return new SaikuFilter(filtername, null, dimension, hierarchy, members);
  }
Exemplo n.º 2
0
  public static void main1(String[] args) throws ClassNotFoundException, SQLException {
    OlapConnection conn = getConnection(url);
    CellSet cs = getResultSet(mdx, conn);
    // CellSetAxis c;

    int count = 0;
    if (cs.getAxes().size() > 1) {
      for (Position row : cs.getAxes().get(1)) {
        for (Position column : cs.getAxes().get(0)) {
          for (Member member : row.getMembers()) {
            System.out.println("rows:" + member.getUniqueName());
          }
          for (Member member : column.getMembers()) {
            System.out.println("columns:" + member.getUniqueName());
          }
          final Cell cell = cs.getCell(column, row);

          System.out.println("values:" + cell.getValue());

          Position[] positions = new Position[2];
          positions[0] = column;
          positions[1] = row;

          OlapCell oalpCell = new OlapCell(positions, cell.getValue());

          System.out.println("****" + oalpCell.toString());
          System.out.println(count++);
        }
      }
    }
  }
Exemplo n.º 3
0
  @Test
  public void testSaveModelSettings() throws ConfigurationException, IOException {
    PivotModel model = getPivotModel();
    model.setMdx(getTestQuery());
    model.initialize();

    model.setSorting(true);
    model.setTopBottomCount(3);
    model.setSortCriteria(SortCriteria.BOTTOMCOUNT);

    CellSet cellSet = model.getCellSet();
    CellSetAxis axis = cellSet.getAxes().get(Axis.COLUMNS.axisOrdinal());

    model.sort(axis, axis.getPositions().get(0));

    String mdx = model.getCurrentMdx();

    XMLConfiguration configuration = new XMLConfiguration();
    configuration.setDelimiterParsingDisabled(true);

    model.saveSettings(configuration);

    Logger logger = LoggerFactory.getLogger(getClass());
    if (logger.isDebugEnabled()) {
      StringWriter writer = new StringWriter();
      configuration.save(writer);
      writer.flush();
      writer.close();

      logger.debug("Loading report content :" + System.getProperty("line.separator"));
      logger.debug(writer.getBuffer().toString());
    }

    PivotModel newModel = new PivotModelImpl(getDataSource());
    newModel.restoreSettings(configuration);

    newModel.getCellSet();

    String newMdx = newModel.getCurrentMdx();
    if (newMdx != null) {
      // Currently the parser treats every number as double value.
      // It's inevitable now and does not impact the result.
      newMdx = newMdx.replaceAll("3\\.0", "3");
    }

    assertEquals("MDX has been changed after the state restoration", mdx, newMdx);
    assertTrue(
        "Property 'sorting' has been changed after the state restoration", newModel.isSorting());
    assertEquals(
        "Property 'topBottomCount' has been changed after the state restoration",
        3,
        newModel.getTopBottomCount());
    assertEquals(
        "Property 'sortMode' has been changed after the state restoration",
        SortCriteria.BOTTOMCOUNT,
        newModel.getSortCriteria());
  }
Exemplo n.º 4
0
 public static void main(String[] args) throws Exception {
   OlapConnection conn = getConnection(url);
   CellSet cs = getResultSet(mdx, conn);
   List<Position> coordinates = new ArrayList<Position>();
   List<OlapCell> results = new ArrayList<OlapCell>();
   explore(cs.getAxes(), coordinates, cs, results);
   Gson gson = new Gson();
   System.out.println(gson.toJson(results));
 }
Exemplo n.º 5
0
  public ResultSet drillthrough(
      String queryName, List<Integer> cellPosition, Integer maxrows, String returns) {
    OlapStatement stmt = null;
    try {
      IQuery query = getIQuery(queryName);
      CellSet cs = query.getCellset();
      SaikuCube cube = getQuery(queryName).getCube();
      final OlapConnection con = olapDiscoverService.getNativeConnection(cube.getConnectionName());
      stmt = con.createStatement();

      String select = null;
      StringBuffer buf = new StringBuffer();
      buf.append("SELECT (");
      for (int i = 0; i < cellPosition.size(); i++) {
        List<Member> members =
            cs.getAxes().get(i).getPositions().get(cellPosition.get(i)).getMembers();
        for (int k = 0; k < members.size(); k++) {
          Member m = members.get(k);
          if (k > 0 || i > 0) {
            buf.append(", ");
          }
          buf.append(m.getUniqueName());
        }
      }
      buf.append(") ON COLUMNS \r\n");
      buf.append("FROM " + cube.getCubeName() + "\r\n");

      SelectNode sn = (new DefaultMdxParserImpl().parseSelect(getMDXQuery(queryName)));
      final Writer writer = new StringWriter();
      sn.getFilterAxis().unparse(new ParseTreeWriter(new PrintWriter(writer)));
      if (StringUtils.isNotBlank(writer.toString())) {
        buf.append("WHERE " + writer.toString());
      }
      select = buf.toString();
      if (maxrows > 0) {
        select = "DRILLTHROUGH MAXROWS " + maxrows + " " + select + "\r\n";
      } else {
        select = "DRILLTHROUGH " + select + "\r\n";
      }
      if (StringUtils.isNotBlank(returns)) {
        select += "\r\n RETURN " + returns;
      }

      log.debug("Drill Through for query (" + queryName + ") : \r\n" + select);
      ResultSet rs = stmt.executeQuery(select);
      return rs;
    } catch (Exception e) {
      throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e);
    } finally {
      try {
        if (stmt != null) stmt.close();
      } catch (Exception e) {
      }
    }
  }
Exemplo n.º 6
0
  public List<SaikuMember> getResultMetadataMembers(
      String queryName,
      boolean preferResult,
      String dimensionName,
      String hierarchyName,
      String levelName) {
    IQuery query = getIQuery(queryName);
    CellSet cs = query.getCellset();
    List<SaikuMember> members = new ArrayList<SaikuMember>();
    Set<Level> levels = new HashSet<Level>();

    if (cs != null && preferResult) {
      for (CellSetAxis axis : cs.getAxes()) {
        int posIndex = 0;
        for (Hierarchy h : axis.getAxisMetaData().getHierarchies()) {
          if (h.getUniqueName().equals(hierarchyName)) {
            log.debug("Found hierarchy in the result: " + hierarchyName);
            if (h.getLevels().size() == 1) {
              break;
            }
            Set<Member> mset = new HashSet<Member>();
            for (Position pos : axis.getPositions()) {
              Member m = pos.getMembers().get(posIndex);
              if (!m.getLevel().getLevelType().equals(Type.ALL)) {
                levels.add(m.getLevel());
              }
              if (m.getLevel().getUniqueName().equals(levelName)) {
                mset.add(m);
              }
            }

            members = ObjectUtil.convertMembers(mset);
            Collections.sort(members, new SaikuUniqueNameComparator());

            break;
          }
          posIndex++;
        }
      }
      log.debug("Found members in the result: " + members.size());
    }
    if (cs == null || !preferResult || members.size() == 0 || levels.size() == 1) {
      members =
          olapDiscoverService.getLevelMembers(
              query.getSaikuCube(), dimensionName, hierarchyName, levelName);
    }

    return members;
  }
Exemplo n.º 7
0
 private Cell getCellAt(int axisCoord, int perpAxisCoord) {
   final Position[] positions =
       new Position[] {
         dataAxisInfo.fullPositions.get(axisCoord), totalsAxisInfo.fullPositions.get(perpAxisCoord)
       };
   Cell cell = cellSet.getCell(positions[col], positions[row]);
   return cell;
 }
Exemplo n.º 8
0
  @Test
  public void testBookmarkModelState() {
    PivotModel model = getPivotModel();
    model.setMdx(getTestQuery());
    model.initialize();

    model.setSorting(true);
    model.setTopBottomCount(3);
    model.setSortCriteria(SortCriteria.BOTTOMCOUNT);

    CellSet cellSet = model.getCellSet();
    CellSetAxis axis = cellSet.getAxes().get(Axis.COLUMNS.axisOrdinal());

    model.sort(axis, axis.getPositions().get(0));

    String mdx = model.getCurrentMdx();

    Serializable bookmark = model.saveState();

    assertNotNull("Bookmarked state should not be null", bookmark);

    PivotModel newModel = new PivotModelImpl(getDataSource());
    newModel.restoreState(bookmark);

    newModel.getCellSet();

    String newMdx = newModel.getCurrentMdx();
    if (newMdx != null) {
      // Currently the parser treats every number as double value.
      // It's inevitable now and does not impact the result.
      newMdx = newMdx.replaceAll("3\\.0", "3");
    }

    assertEquals("MDX has been changed after the state restoration", mdx, newMdx);
    assertTrue(
        "Property 'sorting' has been changed after the state restoration", newModel.isSorting());
    assertEquals(
        "Property 'topBottomCount' has been changed after the state restoration",
        3,
        newModel.getTopBottomCount());
    assertEquals(
        "Property 'sortMode' has been changed after the state restoration",
        SortCriteria.BOTTOMCOUNT,
        newModel.getSortCriteria());
  }
Exemplo n.º 9
0
  public void setCellValue(
      String queryName, List<Integer> position, String value, String allocationPolicy) {
    try {

      IQuery query = getIQuery(queryName);
      OlapConnection con =
          olapDiscoverService.getNativeConnection(query.getSaikuCube().getConnectionName());

      Scenario s;
      if (query.getScenario() == null) {
        s = con.createScenario();
        query.setScenario(s);
        con.setScenario(s);
        System.out.println("Created scenario:" + s + " : cell:" + position + " value" + value);
      } else {
        s = query.getScenario();
        con.setScenario(s);
        System.out.println("Using scenario:" + s + " : cell:" + position + " value" + value);
      }

      CellSet cs1 = query.execute();
      query.storeCellset(cs1);

      Object v = null;
      try {
        v = Integer.parseInt(value);
      } catch (Exception e) {
        v = Double.parseDouble(value);
      }
      if (v == null) {
        throw new SaikuServiceException("Error setting value of query " + queryName + " to:" + v);
      }

      allocationPolicy = AllocationPolicy.EQUAL_ALLOCATION.toString();

      AllocationPolicy ap = AllocationPolicy.valueOf(allocationPolicy);
      CellSet cs = query.getCellset();
      cs.getCell(position).setValue(v, ap);
      con.setScenario(null);
    } catch (Exception e) {
      throw new SaikuServiceException("Error setting value: " + queryName, e);
    }
  }
 /**
  * Prints a cell set.
  *
  * @param cellSet Cell set
  * @param pw Writer
  */
 private static void print(CellSet cellSet, PrintWriter pw) {
   pw.println("Axis #0:");
   printAxis(pw, cellSet.getFilterAxis());
   final List<CellSetAxis> axes = cellSet.getAxes();
   final int axisCount = axes.size();
   for (int i = 0; i < axisCount; i++) {
     CellSetAxis axis = axes.get(i);
     pw.println("Axis #" + (i + 1) + ":");
     printAxis(pw, axis);
   }
   // Usually there are 3 axes: {filter, columns, rows}. Position is a
   // {column, row} pair. We call printRows with axis=2. When it
   // recurses to axis=-1, it prints.
   List<Integer> pos = new ArrayList<Integer>(axisCount);
   for (int i = 0; i < axisCount; i++) {
     pos.add(-1);
   }
   if (axisCount == 0) {
     printCell(cellSet, pw, pos);
   } else {
     printRows(cellSet, pw, axisCount - 1, pos);
   }
 }
Exemplo n.º 11
0
  public SaikuTag createTag(String queryName, String tagName, List<List<Integer>> cellPositions) {
    try {
      IQuery query = getIQuery(queryName);
      CellSet cs = query.getCellset();
      List<SaikuTuple> tuples = new ArrayList<SaikuTuple>();
      List<SimpleCubeElement> dimensions = new ArrayList<SimpleCubeElement>();
      for (List<Integer> cellPosition : cellPositions) {
        List<Member> members = new ArrayList<Member>();
        for (int i = 0; i < cellPosition.size(); i++) {
          members.addAll(cs.getAxes().get(i).getPositions().get(cellPosition.get(i)).getMembers());
        }
        List<SaikuMember> sm = ObjectUtil.convertMembers(members);
        SaikuTuple tuple = new SaikuTuple(sm);
        tuples.add(tuple);

        if (dimensions.size() == 0) {
          for (Member m : members) {
            SimpleCubeElement sd =
                new SimpleCubeElement(
                    m.getDimension().getName(),
                    m.getDimension().getUniqueName(),
                    m.getDimension().getCaption());
            if (!dimensions.contains(sd)) {
              dimensions.add(sd);
            }
          }
        }
      }
      List<SaikuDimensionSelection> filterSelections = getAxisSelection(queryName, "FILTER");
      SaikuTag t = new SaikuTag(tagName, dimensions, tuples, filterSelections);
      return t;

    } catch (Exception e) {
      throw new SaikuServiceException("Error addTag:" + tagName + " for query: " + queryName, e);
    }
  }
 /**
  * Prints the rows of cell set.
  *
  * @param cellSet Cell set
  * @param pw Writer
  * @param axis Axis ordinal
  * @param pos Partial coordinate
  */
 private static void printRows(CellSet cellSet, PrintWriter pw, int axis, List<Integer> pos) {
   final CellSetAxis _axis = cellSet.getAxes().get(axis);
   final List<Position> positions = _axis.getPositions();
   final int positionCount = positions.size();
   for (int i = 0; i < positionCount; i++) {
     pos.set(axis, i);
     if (axis == 0) {
       int row = axis + 1 < pos.size() ? pos.get(axis + 1) : 0;
       pw.print("Row #" + row + ": ");
       printCell(cellSet, pw, pos);
       pw.println();
     } else {
       printRows(cellSet, pw, axis - 1, pos);
     }
   }
 }
Exemplo n.º 13
0
  public Matrix format(final CellSet cellSet) {
    // Compute how many rows are required to display the columns axis.
    final CellSetAxis columnsAxis;
    if (cellSet.getAxes().size() > 0) {
      columnsAxis = cellSet.getAxes().get(0);
    } else {
      columnsAxis = null;
    }
    final AxisInfo columnsAxisInfo = computeAxisInfo(columnsAxis);

    // Compute how many columns are required to display the rows axis.
    final CellSetAxis rowsAxis;
    if (cellSet.getAxes().size() > 1) {
      rowsAxis = cellSet.getAxes().get(1);
    } else {
      rowsAxis = null;
    }
    final AxisInfo rowsAxisInfo = computeAxisInfo(rowsAxis);

    if (cellSet.getAxes().size() > 2) {
      final int[] dimensions = new int[cellSet.getAxes().size() - 2];
      for (int i = 2; i < cellSet.getAxes().size(); i++) {
        final CellSetAxis cellSetAxis = cellSet.getAxes().get(i);
        dimensions[i - 2] = cellSetAxis.getPositions().size();
      }
      for (final int[] pageCoords : CoordinateIterator.iterate(dimensions)) {
        matrix =
            formatPage(cellSet, pageCoords, columnsAxis, columnsAxisInfo, rowsAxis, rowsAxisInfo);
      }
    } else {
      matrix =
          formatPage(cellSet, new int[] {}, columnsAxis, columnsAxisInfo, rowsAxis, rowsAxisInfo);
    }

    return matrix;
  }
Exemplo n.º 14
0
  // coordinates.size should be 0 at very first
  public static void explore(
      List<CellSetAxis> axes, List<Position> coordinates, CellSet cs, List<OlapCell> cellList) {
    int level = coordinates.size();
    // System.out.println(level + "  " + axes.size());
    if (level < axes.size()) {
      for (Position p : axes.get(level).getPositions()) {
        coordinates.add(p);
        explore(axes, coordinates, cs, cellList);
      }

      if (level > 0) {
        coordinates.remove(level - 1);
      }

    } else {
      Position[] positions = new Position[coordinates.size()];
      positions = coordinates.toArray(positions);
      Cell cell = cs.getCell(positions);
      OlapCell olapCell = new OlapCell(positions, cell.getValue());
      cellList.add(olapCell);
      // System.out.println((++count) + " " + olapCell.toString());
      coordinates.remove(level - 1);
    }
  }
Exemplo n.º 15
0
  public TotalsListsBuilder(
      Measure[] selectedMeasures,
      TotalAggregator[] aggrTempl,
      CellSet cellSet,
      AxisInfo totalsAxisInfo,
      AxisInfo dataAxisInfo)
      throws Exception {
    Cube cube;
    try {
      cube = cellSet.getMetaData().getCube();
    } catch (OlapException e) {
      throw new RuntimeException(e);
    }
    uniqueToSelected = new HashMap<String, Integer>();
    if (selectedMeasures.length > 0) {
      valueFormats = new Format[selectedMeasures.length];
      measures = selectedMeasures;
      for (int i = 0; i < valueFormats.length; i++) {
        valueFormats[i] = getMeasureFormat(selectedMeasures[i]);
        uniqueToSelected.put(selectedMeasures[i].getUniqueName(), i);
      }
    } else {
      Measure defaultMeasure = cube.getMeasures().get(0);
      if (cube.getDimensions().get("Measures") != null) {
        Member ms = cube.getDimensions().get("Measures").getDefaultHierarchy().getDefaultMember();
        if (ms instanceof Measure) {
          defaultMeasure = (Measure) ms;
        }
      }
      measures = new Measure[] {defaultMeasure};
      valueFormats = new Format[] {getMeasureFormat(defaultMeasure)};
    }
    this.cellSet = cellSet;
    this.dataAxisInfo = dataAxisInfo;
    this.totalsAxisInfo = totalsAxisInfo;
    final int maxDepth = dataAxisInfo.maxDepth + 1;
    boolean hasMeasuresOnDataAxis = false;
    int measuresAt = 0;
    int measuresMember = 0;
    final List<Member> members =
        dataAxisInfo.axis.getPositionCount() > 0
            ? dataAxisInfo.axis.getPositions().get(0).getMembers()
            : Collections.<Member>emptyList();
    for (; measuresMember < members.size(); measuresMember++) {
      Member m = members.get(measuresMember);
      if ("Measures".equals(m.getDimension().getName())) {
        hasMeasuresOnDataAxis = true;
        break;
      }
      measuresAt += dataAxisInfo.levels[measuresMember].size();
    }
    if (hasMeasuresOnDataAxis) {
      this.measuresAt = measuresAt;
      measuresCaptions = new String[selectedMeasures.length];
      for (int i = 0; i < measuresCaptions.length; i++) {
        measuresCaptions[i] = selectedMeasures[i].getCaption();
      }
    } else {
      this.measuresAt = Integer.MIN_VALUE;
      measuresCaptions = null;
    }

    totalBranch = new TotalNode[maxDepth];
    TotalNode rootNode =
        new TotalNode(
            measuresCaptions, measures, aggrTempl[0], this, totalsAxisInfo.fullPositions.size());
    col = Axis.ROWS.equals(dataAxisInfo.axis.getAxisOrdinal()) ? 1 : 0;
    row = (col + 1) & 1;
    this.aggrTempl = aggrTempl;

    totalBranch[0] = rootNode;
    totalsLists = new List[maxDepth];
    for (int i = 0; i < totalsLists.length; i++) {
      totalsLists[i] = new ArrayList<TotalNode>();
    }
    totalsLists[0].add(rootNode);
    memberBranch = new Member[dataAxisInfo.maxDepth + 1];
  }
 /**
  * Prints the formatted value of a Cell at a given position.
  *
  * @param cellSet Cell set
  * @param pw Print writer
  * @param pos Cell coordinates
  */
 private static void printCell(CellSet cellSet, PrintWriter pw, List<Integer> pos) {
   Cell cell = cellSet.getCell(pos);
   pw.print(cell.getFormattedValue());
 }