Beispiel #1
0
 public Cube getNativeCube(SaikuCube cube) throws SaikuOlapException {
   try {
     OlapConnection con = connections.getOlapConnection(cube.getConnectionName());
     if (con != null) {
       for (Database db : con.getOlapDatabases()) {
         Catalog cat = db.getCatalogs().get(cube.getCatalogName());
         if (cat != null) {
           for (Schema schema : cat.getSchemas()) {
             if (schema.getName().equals(cube.getSchemaName())) {
               for (Cube cub : schema.getCubes()) {
                 if (cub.getName().equals(cube.getName())
                     || cub.getUniqueName().equals(cube.getUniqueName())) {
                   return cub;
                 }
               }
             }
           }
         }
       }
     }
   } catch (Exception e) {
     throw new SaikuOlapException("Cannot get native cube for ( " + cube + " )", e);
   }
   throw new SaikuOlapException("Cannot get native cube for ( " + cube + " )");
 }
Beispiel #2
0
  public byte[] exportDrillthroughCsv(String queryName, int maxrows) {
    OlapStatement stmt = null;
    try {
      final OlapConnection con =
          olapDiscoverService.getNativeConnection(
              getQuery(queryName).getCube().getConnectionName());
      stmt = con.createStatement();
      String mdx = getMDXQuery(queryName);
      if (maxrows > 0) {
        mdx = "DRILLTHROUGH MAXROWS " + maxrows + " " + mdx;
      } else {
        mdx = "DRILLTHROUGH " + mdx;
      }

      ResultSet rs = stmt.executeQuery(mdx);
      return CsvExporter.exportCsv(rs);
    } catch (SQLException e) {
      throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e);
    } finally {
      try {
        if (stmt != null) stmt.close();
      } catch (Exception e) {
      }
    }
  }
Beispiel #3
0
 public List<SaikuCube> getCubes(String connectionName) {
   OlapConnection olapcon = connections.getOlapConnection(connectionName);
   List<SaikuCube> cubes = new ArrayList<SaikuCube>();
   if (olapcon != null) {
     try {
       for (Catalog cat : olapcon.getOlapCatalogs()) {
         for (Schema schem : cat.getSchemas()) {
           for (Cube cub : schem.getCubes()) {
             cubes.add(
                 new SaikuCube(
                     connectionName,
                     cub.getUniqueName(),
                     cub.getName(),
                     cub.getCaption(),
                     cat.getName(),
                     schem.getName(),
                     cub.isVisible()));
           }
         }
       }
     } catch (OlapException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
     }
   }
   Collections.sort(cubes, new SaikuCubeCaptionComparator());
   return cubes;
 }
Beispiel #4
0
 public ResultSet drillthrough(String queryName, int maxrows, String returns) {
   OlapStatement stmt = null;
   try {
     final OlapConnection con =
         olapDiscoverService.getNativeConnection(
             getQuery(queryName).getCube().getConnectionName());
     stmt = con.createStatement();
     String mdx = getMDXQuery(queryName);
     if (maxrows > 0) {
       mdx = "DRILLTHROUGH MAXROWS " + maxrows + " " + mdx;
     } else {
       mdx = "DRILLTHROUGH " + mdx;
     }
     if (StringUtils.isNotBlank(returns)) {
       mdx += "\r\n RETURN " + returns;
     }
     ResultSet rs = stmt.executeQuery(mdx);
     return rs;
   } catch (SQLException e) {
     throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e);
   } finally {
     try {
       if (stmt != null) stmt.close();
     } catch (Exception e) {
     }
   }
 }
Beispiel #5
0
  public ResultSet explain(String queryName) {
    OlapStatement stmt = null;
    try {

      final OlapConnection con =
          olapDiscoverService.getNativeConnection(
              getQuery(queryName).getCube().getConnectionName());
      if (!con.isWrapperFor(RolapConnection.class))
        throw new IllegalArgumentException("Cannot only get explain plan for Mondrian connections");

      stmt = con.createStatement();
      String mdx = getMDXQuery(queryName);
      mdx = "EXPLAIN PLAN FOR \n" + mdx;
      ResultSet rs = stmt.executeQuery(mdx);
      return rs;

    } catch (Exception e) {
      throw new SaikuServiceException("Error EXPLAIN: " + queryName, e);
    } finally {
      try {
        if (stmt != null) stmt.close();
      } catch (Exception e) {
      }
    }
  }
Beispiel #6
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) {
      }
    }
  }
  /**
   * Adds a catalog and its children to the cache. Do not use directly. This must be called with a
   * write lock on the cache.
   *
   * @param catalogName The name of the catalog to load in cache.
   */
  private void addCatalogToCache(IPentahoSession session, String catalogName) {

    final IOlapService.Catalog catalog =
        new Catalog(catalogName, new ArrayList<IOlapService.Schema>());

    OlapConnection connection = null;

    try {

      connection = getConnection(catalogName, session);

      for (org.olap4j.metadata.Schema schema4j : connection.getOlapSchemas()) {

        connection.setSchema(schema4j.getName());

        final IOlapService.Schema schema =
            new Schema(
                schema4j.getName(),
                catalog,
                new ArrayList<IOlapService.Cube>(),
                new ArrayList<String>(connection.getAvailableRoleNames()));

        for (org.olap4j.metadata.Cube cube4j : schema4j.getCubes()) {
          schema.cubes.add(new IOlapService.Cube(cube4j.getName(), cube4j.getCaption(), schema));
        }

        catalog.schemas.add(schema);
      }

      // We're done.
      getCache(session).add(catalog);

    } catch (OlapException e) {

      LOG.warn("Failed to initialize the olap connection cache for catalog " + catalogName, e);

    } finally {
      try {
        if (connection != null) {
          connection.close();
        }
      } catch (SQLException e) {
        LOG.warn("Failed to gracefully close an olap connection to catalog " + catalogName, e);
      }
    }
  }
Beispiel #8
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);
    }
  }
  /** @see org.pivot4j.analytics.datasource.DataSourceManager#getCubes(java.lang.String) */
  @Override
  public List<CubeInfo> getCubes(String catalogName) {
    if (catalogName == null) {
      throw new NullArgumentException("catalogName");
    }

    SimpleDataSourceInfo definition = getDefinition(catalogName);

    if (definition == null) {
      throw new IllegalArgumentException(
          "Data source with the given name does not exist : " + catalogName);
    }

    OlapDataSource dataSource = createDataSource(definition);

    List<CubeInfo> cubes = new LinkedList<CubeInfo>();

    OlapConnection connection = null;

    try {
      connection = dataSource.getConnection();

      for (Cube cube : connection.getOlapSchema().getCubes()) {
        if (cube.isVisible()) {
          cubes.add(new CubeInfo(cube.getName(), cube.getCaption(), cube.getDescription()));
        }
      }
    } catch (SQLException e) {
      throw new PivotException(e);
    } finally {
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          throw new PivotException(e);
        }
      }
    }

    return cubes;
  }
 /**
  * Flushes all catalogs in the catalogNames collection. If hosted=true the method breaks after the
  * first successful schemaCacheFlush, since we know that all schemas will be flushed by the
  * operation. For remote we assume that each needs to be flushed separately, since there are
  * possibly multiple servers.
  */
 private void flushCatalogs(Collection<String> catalogNames, IPentahoSession session)
     throws SQLException {
   for (String name : catalogNames) {
     OlapConnection connection = null;
     try {
       connection = getConnection(name, session);
       XmlaHandler.XmlaExtra xmlaExtra = getXmlaExtra(connection);
       if (xmlaExtra != null) {
         xmlaExtra.flushSchemaCache(connection);
       }
     } catch (Exception e) {
       LOG.warn(
           Messages.getInstance()
               .getErrorString("MondrianCatalogHelper.ERROR_0019_FAILED_TO_FLUSH", name),
           e);
     } finally {
       if (connection != null) {
         connection.close();
       }
     }
   }
 }
Beispiel #11
0
  public static void main(String[] args) throws Exception {

    // Load the driver
    Class.forName("mondrian.olap4j.MondrianOlap4jDriver");

    // Connect
    final Connection connection =
        DriverManager.getConnection(
            "jdbc:mondrian:" // Driver ident
                + "Jdbc=jdbc:hsqldb:file:foodmart/foodmart;" // Relational DB
                + "Catalog=file:foodmart/FoodMart.xml;"); // Catalog

    // We are dealing with an olap connection. we must unwrap it.
    final OlapConnection olapConnection = connection.unwrap(OlapConnection.class);

    // Get a cube object.
    Cube salesCube = olapConnection.getOlapSchema().getCubes().get("Sales");

    // Build a query object.
    Query myQuery = new Query("myQuery", salesCube);

    // Lookup some dimensions
    QueryDimension productDim = myQuery.getDimension("Product");
    QueryDimension storeDim = myQuery.getDimension("Store");
    QueryDimension timeDim = myQuery.getDimension("Time");

    // Place dimensions on some axis
    myQuery.getAxis(Axis.COLUMNS).addDimension(productDim);
    myQuery.getAxis(Axis.ROWS).addDimension(storeDim);
    myQuery.getAxis(Axis.FILTER).addDimension(timeDim);

    // Including a member by metadata
    Member year1997 =
        salesCube.lookupMember(IdentifierNode.ofNames("Time", "1997").getSegmentList());
    timeDim.include(year1997);

    // Including a member by name parts
    productDim.include(
        Selection.Operator.CHILDREN,
        IdentifierNode.ofNames("Product", "Drink", "Beverages").getSegmentList());

    // We can also exclude members
    productDim.exclude(
        IdentifierNode.ofNames("Product", "Drink", "Beverages", "Carbonated Beverages")
            .getSegmentList());

    // Validate this query
    myQuery.validate();

    // Print!
    System.out.println("/********************* QUERY ***********************/");

    System.out.println(myQuery.getSelect().toString());

    System.out.println("/********************* QUERY ***********************/");
    System.out.println(" ");
    System.out.println(" ");
    System.out.println(" ");
    System.out.println(" ");

    System.out.println("/********************* RESULTS ***********************/");

    RectangularCellSetFormatter formatter = new RectangularCellSetFormatter(false);

    PrintWriter writer = new PrintWriter(System.out);

    formatter.format(myQuery.execute(), writer);

    writer.flush();
    System.out.println("/********************* RESULTS ***********************/");
  }
Beispiel #12
0
  public SaikuConnection getConnection(String connectionName) throws SaikuOlapException {
    OlapConnection olapcon = connections.getOlapConnection(connectionName);
    SaikuConnection connection = null;
    if (olapcon != null) {
      List<SaikuCatalog> catalogs = new ArrayList<SaikuCatalog>();
      try {
        for (Catalog cat : olapcon.getOlapCatalogs()) {
          List<SaikuSchema> schemas = new ArrayList<SaikuSchema>();
          for (Schema schem : cat.getSchemas()) {
            List<SaikuCube> cubes = new ArrayList<SaikuCube>();
            for (Cube cub : schem.getCubes()) {
              cubes.add(
                  new SaikuCube(
                      connectionName,
                      cub.getUniqueName(),
                      cub.getName(),
                      cub.getCaption(),
                      cat.getName(),
                      schem.getName(),
                      cub.isVisible()));
            }
            Collections.sort(cubes, new SaikuCubeCaptionComparator());
            schemas.add(new SaikuSchema(schem.getName(), cubes));
          }
          if (schemas.size() == 0) {
            OlapDatabaseMetaData olapDbMeta = olapcon.getMetaData();
            ResultSet cubesResult = olapDbMeta.getCubes(cat.getName(), null, null);

            try {
              List<SaikuCube> cubes = new ArrayList<SaikuCube>();
              while (cubesResult.next()) {

                cubes.add(
                    new SaikuCube(
                        connectionName,
                        cubesResult.getString("CUBE_NAME"),
                        cubesResult.getString("CUBE_NAME"),
                        cubesResult.getString("CUBE_NAME"),
                        cubesResult.getString("CATALOG_NAME"),
                        cubesResult.getString("SCHEMA_NAME")));
              }
              Collections.sort(cubes, new SaikuCubeCaptionComparator());
              schemas.add(new SaikuSchema("", cubes));
            } catch (SQLException e) {
              throw new OlapException(e.getMessage(), e);
            } finally {
              try {
                cubesResult.close();
              } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
              }
            }
          }
          Collections.sort(schemas);
          catalogs.add(new SaikuCatalog(cat.getName(), schemas));
        }
      } catch (OlapException e) {
        throw new SaikuOlapException(
            "Error getting objects of connection (" + connectionName + ")", e);
      }
      Collections.sort(catalogs);
      connection = new SaikuConnection(connectionName, catalogs);
      return connection;
    }
    throw new SaikuOlapException("Cannot find connection: (" + connectionName + ")");
  }
Beispiel #13
0
 public static CellSet getResultSet(String mdx, OlapConnection conn) throws OlapException {
   OlapStatement statement = conn.createStatement();
   CellSet cellSet = statement.executeOlapQuery(mdx);
   return cellSet;
 }
Beispiel #14
0
  public CellDataSet execute(String queryName, ICellSetFormatter formatter) {
    String runId = "runId:" + ID_GENERATOR.getAndIncrement();
    try {
      //			System.out.println("Execute: ID " + Thread.currentThread().getId() + " Name: " +
      // Thread.currentThread().getName());
      IQuery query = getIQuery(queryName);
      OlapConnection con =
          olapDiscoverService.getNativeConnection(query.getSaikuCube().getConnectionName());

      Long start = (new Date()).getTime();
      if (query.getScenario() != null) {
        log.info(
            runId
                + "\tQuery: "
                + query.getName()
                + " Setting scenario:"
                + query.getScenario().getId());
        con.setScenario(query.getScenario());
      }

      if (query.getTag() != null) {
        query = applyTag(query, con, query.getTag());
      }

      String mdx = query.getMdx();
      log.info(runId + "\tType:" + query.getType() + ":\n" + mdx);

      CellSet cellSet = query.execute();
      Long exec = (new Date()).getTime();

      if (query.getScenario() != null) {
        log.info("Query (" + queryName + ") removing scenario:" + query.getScenario().getId());
        con.setScenario(null);
      }

      CellDataSet result = OlapResultSetUtil.cellSet2Matrix(cellSet, formatter);
      Long format = (new Date()).getTime();
      log.info(
          runId
              + "\tSize: "
              + result.getWidth()
              + "/"
              + result.getHeight()
              + "\tExecute:\t"
              + (exec - start)
              + "ms\tFormat:\t"
              + (format - exec)
              + "ms\t Total: "
              + (format - start)
              + "ms");
      result.setRuntime(new Double(format - start).intValue());
      getIQuery(queryName).storeCellset(cellSet);
      return result;
    } catch (Exception e) {
      if (log.isInfoEnabled()) {
        String error = ExceptionUtils.getRootCauseMessage(e);
        log.info(runId + "\tException: " + error);
      }
      throw new SaikuServiceException(runId + "\tCan't execute query: " + queryName, e);
    } catch (Error e) {
      if (log.isInfoEnabled()) {
        String error = ExceptionUtils.getRootCauseMessage(e);
        log.info(runId + "\tError: " + error);
      }
      throw new SaikuServiceException(runId + "\tCan't execute query: " + queryName, e);
    }
  }