Examples with AggStar mondrian.rolap.aggmatcher.AggStar used on opensource projects

Example 6 with AggStar

use of mondrian.rolap.aggmatcher.AggStar in project mondrian by pentaho.

the class SqlConstraintUtils method getColumnExpr.

/**
 * Get the column expression from the AggStar if provided or the regular
 * table if not, and ensure table is in From
 */
public static String getColumnExpr(SqlQuery sqlQuery, AggStar aggStar, RolapStar.Column column) {
    final String expr;
    if (aggStar != null) {
        int bitPos = column.getBitPosition();
        AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
        AggStar.Table table = aggColumn.getTable();
        table.addToFrom(sqlQuery, false, true);
        expr = aggColumn.generateExprString(sqlQuery);
    } else {
        RolapStar.Table table = column.getTable();
        table.addToFrom(sqlQuery, false, true);
        expr = column.generateExprString(sqlQuery);
    }
    return expr;
}

Example 7 with AggStar

use of mondrian.rolap.aggmatcher.AggStar in project mondrian by pentaho.

the class SqlConstraintUtils method constrainMultiLevelMembers.

/**
 * Adds to the where clause of a query expression matching a specified
 * list of members
 *
 * @param sqlQuery query containing the where clause
 * @param baseCube base cube if virtual
 * @param aggStar aggregate star if available
 * @param members list of constraining members
 * @param fromLevel lowest parent level that is unique
 * @param restrictMemberTypes defines the behavior when calculated members
 * are present
 * @param exclude whether to exclude the members. Default is false.
 *
 * @return a non-empty String if SQL is generated for the multi-level
 * member list.
 */
private static String constrainMultiLevelMembers(SqlQuery sqlQuery, RolapCube baseCube, AggStar aggStar, List<RolapMember> members, RolapLevel fromLevel, boolean restrictMemberTypes, boolean exclude) {
    // Use LinkedHashMap so that keySet() is deterministic.
    Map<RolapMember, List<RolapMember>> parentChildrenMap = new LinkedHashMap<RolapMember, List<RolapMember>>();
    StringBuilder condition = new StringBuilder();
    StringBuilder condition1 = new StringBuilder();
    if (exclude) {
        condition.append("not (");
    }
    // First try to generate IN list for all members
    if (sqlQuery.getDialect().supportsMultiValueInExpr()) {
        condition1.append(generateMultiValueInExpr(sqlQuery, baseCube, aggStar, members, fromLevel, restrictMemberTypes, parentChildrenMap));
        // 
        if (parentChildrenMap.isEmpty()) {
            condition.append(condition1.toString());
            if (exclude) {
                // If there are no NULL values in the member levels, then
                // we're done except we need to also explicitly include
                // members containing nulls across all levels.
                condition.append(")");
                condition.append(" or ");
                condition.append(generateMultiValueIsNullExprs(sqlQuery, baseCube, members.get(0), fromLevel, aggStar));
            }
            return condition.toString();
        }
    } else {
        // 
        for (RolapMember m : members) {
            if (m.isCalculated()) {
                if (restrictMemberTypes) {
                    throw Util.newInternal("addMemberConstraint: cannot " + "restrict SQL to calculated member :" + m);
                }
                continue;
            }
            RolapMember p = m.getParentMember();
            List<RolapMember> childrenList = parentChildrenMap.get(p);
            if (childrenList == null) {
                childrenList = new ArrayList<RolapMember>();
                parentChildrenMap.put(p, childrenList);
            }
            childrenList.add(m);
        }
    }
    // Now we try to generate predicates for the remaining
    // parent-children group.
    // Note that NULLs are not used to enforce uniqueness
    // so we ignore the fromLevel here.
    boolean firstParent = true;
    StringBuilder condition2 = new StringBuilder();
    if (condition1.length() > 0) {
        // Some members have already been translated into IN list.
        firstParent = false;
        condition.append(condition1.toString());
        condition.append(" or ");
    }
    RolapLevel memberLevel = members.get(0).getLevel();
    // should not contain null.
    for (RolapMember p : parentChildrenMap.keySet()) {
        assert p != null;
        if (condition2.toString().length() > 0) {
            condition2.append(" or ");
        }
        condition2.append("(");
        // First generate ANDs for all members in the parent lineage of
        // this parent-children group
        int levelCount = 0;
        for (RolapMember gp = p; gp != null; gp = gp.getParentMember()) {
            if (gp.isAll()) {
                // Get the next parent
                continue;
            }
            RolapLevel level = gp.getLevel();
            // first parent-children group we're generating sql for
            if (firstParent) {
                RolapHierarchy hierarchy = level.getHierarchy();
                // this method can be called within the context of shared
                // members, outside of the normal rolap star, therefore
                // we need to check the level to see if it is a shared or
                // cube level.
                RolapStar.Column column = null;
                if (level instanceof RolapCubeLevel) {
                    column = ((RolapCubeLevel) level).getBaseStarKeyColumn(baseCube);
                }
                if (column != null) {
                    if (aggStar != null) {
                        int bitPos = column.getBitPosition();
                        AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
                        AggStar.Table table = aggColumn.getTable();
                        table.addToFrom(sqlQuery, false, true);
                    } else {
                        RolapStar.Table targetTable = column.getTable();
                        hierarchy.addToFrom(sqlQuery, targetTable);
                    }
                } else {
                    assert (aggStar == null);
                    hierarchy.addToFrom(sqlQuery, level.getKeyExp());
                }
            }
            if (levelCount > 0) {
                condition2.append(" and ");
            }
            ++levelCount;
            condition2.append(constrainLevel(level, sqlQuery, baseCube, aggStar, getColumnValue(level.nameExp != null ? gp.getName() : gp.getKey(), sqlQuery.getDialect(), level.getDatatype()), false));
            if (gp.getLevel() == fromLevel) {
                // SQL is completely generated for this parent
                break;
            }
        }
        firstParent = false;
        // Next, generate children for this parent-children group
        List<RolapMember> children = parentChildrenMap.get(p);
        // If no children to be generated for this parent then we are done
        if (!children.isEmpty()) {
            Map<RolapMember, List<RolapMember>> tmpParentChildrenMap = new HashMap<RolapMember, List<RolapMember>>();
            if (levelCount > 0) {
                condition2.append(" and ");
            }
            RolapLevel childrenLevel = (RolapLevel) (p.getLevel().getChildLevel());
            if (sqlQuery.getDialect().supportsMultiValueInExpr() && childrenLevel != memberLevel) {
                // Multi-level children and multi-value IN list supported
                condition2.append(generateMultiValueInExpr(sqlQuery, baseCube, aggStar, children, childrenLevel, restrictMemberTypes, tmpParentChildrenMap));
                assert tmpParentChildrenMap.isEmpty();
            } else {
                // needs to be generated for this case.
                assert childrenLevel == memberLevel;
                condition2.append(generateSingleValueInExpr(sqlQuery, baseCube, aggStar, children, childrenLevel, restrictMemberTypes, false, true));
            }
        }
        // SQL is complete for this parent-children group.
        condition2.append(")");
    }
    // In the case where multi-value IN expressions are not generated,
    // condition2 contains the entire filter condition.  In the
    // case of excludes, we also need to explicitly include null values,
    // minus the ones that are referenced in condition2.  Therefore,
    // we OR on a condition that corresponds to an OR'ing of IS NULL
    // filters on each level PLUS an exclusion of condition2.
    // 
    // Note that the expression generated is non-optimal in the case where
    // multi-value IN's cannot be used because we end up excluding
    // non-null values as well as the null ones.  Ideally, we only need to
    // exclude the expressions corresponding to nulls, which is possible
    // in the multi-value IN case, since we have a map of the null values.
    condition.append(condition2.toString());
    if (exclude) {
        condition.append(") or (");
        condition.append(generateMultiValueIsNullExprs(sqlQuery, baseCube, members.get(0), fromLevel, aggStar));
        condition.append(" and not(");
        condition.append(condition2.toString());
        condition.append("))");
    }
    return condition.toString();
}

Example 8 with AggStar

use of mondrian.rolap.aggmatcher.AggStar in project mondrian by pentaho.

the class SqlTupleReader method generateSelectForLevels.

/**
 * Generates the SQL string corresponding to the levels referenced.
 *
 * @param dataSource jdbc connection that they query will execute against
 * @param baseCube this is the cube object for regular cubes, and the
 *   underlying base cube for virtual cubes
 * @param whichSelect Position of this select statement in a union
 * @param targetGroup the set of targets for which to generate a select
 * @return SQL statement string and types
 */
Pair<String, List<SqlStatement.Type>> generateSelectForLevels(DataSource dataSource, RolapCube baseCube, WhichSelect whichSelect, List<TargetBase> targetGroup) {
    String s = "while generating query to retrieve members of level(s) " + targets;
    // Allow query to use optimization hints from the table definition
    SqlQuery sqlQuery = SqlQuery.newQuery(dataSource, s);
    sqlQuery.setAllowHints(allowHints);
    Evaluator evaluator = getEvaluator(constraint);
    AggStar aggStar = chooseAggStar(constraint, evaluator, baseCube);
    // add the selects for all levels to fetch
    for (TargetBase target : targetGroup) {
        // then we don't need to generate sql for it
        if (target.getSrcMembers() == null) {
            addLevelMemberSql(sqlQuery, target.getLevel(), baseCube, whichSelect, aggStar);
        }
    }
    constraint.addConstraint(sqlQuery, baseCube, aggStar);
    return sqlQuery.toSqlAndTypes();
}

Example 9 with AggStar

use of mondrian.rolap.aggmatcher.AggStar in project mondrian by pentaho.

the class AggregationManager method generateSql.

/**
 * Generates the query to retrieve the cells for a list of segments.
 * Called by Segment.load.
 *
 * @return A pair consisting of a SQL statement and a list of suggested
 *     types of columns
 */
public static Pair<String, List<SqlStatement.Type>> generateSql(GroupingSetsList groupingSetsList, List<StarPredicate> compoundPredicateList) {
    final RolapStar star = groupingSetsList.getStar();
    BitKey levelBitKey = groupingSetsList.getDefaultLevelBitKey();
    BitKey measureBitKey = groupingSetsList.getDefaultMeasureBitKey();
    // Check if using aggregates is enabled.
    boolean hasCompoundPredicates = false;
    if (compoundPredicateList != null && compoundPredicateList.size() > 0) {
        // Do not use Aggregate tables if compound predicates are present.
        hasCompoundPredicates = true;
    }
    if (MondrianProperties.instance().UseAggregates.get() && !hasCompoundPredicates) {
        final boolean[] rollup = { false };
        AggStar aggStar = findAgg(star, levelBitKey, measureBitKey, rollup);
        if (aggStar != null) {
            if (LOGGER.isDebugEnabled()) {
                StringBuilder buf = new StringBuilder(256);
                buf.append("MATCH: ");
                buf.append(star.getFactTable().getAlias());
                buf.append(Util.nl);
                buf.append("   foreign=");
                buf.append(levelBitKey);
                buf.append(Util.nl);
                buf.append("   measure=");
                buf.append(measureBitKey);
                buf.append(Util.nl);
                buf.append("   aggstar=");
                buf.append(aggStar.getBitKey());
                buf.append(Util.nl);
                buf.append("AggStar=");
                buf.append(aggStar.getFactTable().getName());
                buf.append(Util.nl);
                for (AggStar.Table.Column column : aggStar.getFactTable().getColumns()) {
                    buf.append("   ");
                    buf.append(column);
                    buf.append(Util.nl);
                }
                LOGGER.debug(buf.toString());
            }
            AggQuerySpec aggQuerySpec = new AggQuerySpec(aggStar, rollup[0], groupingSetsList);
            Pair<String, List<Type>> sql = aggQuerySpec.generateSqlQuery();
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("generateSqlQuery: sql=" + sql.left);
            }
            return sql;
        }
    // No match, fall through and use fact table.
    }
    if (LOGGER.isDebugEnabled()) {
        StringBuilder sb = new StringBuilder();
        sb.append("NO MATCH : ");
        sb.append(star.getFactTable().getAlias());
        sb.append(Util.nl);
        sb.append("Foreign columns bit key=");
        sb.append(levelBitKey);
        sb.append(Util.nl);
        sb.append("Measure bit key=        ");
        sb.append(measureBitKey);
        sb.append(Util.nl);
        sb.append("Agg Stars=[");
        sb.append(Util.nl);
        for (AggStar aggStar : star.getAggStars()) {
            sb.append(aggStar.toString());
        }
        sb.append(Util.nl);
        sb.append("]");
        LOGGER.debug(sb.toString());
    }
    // Fact table query
    SegmentArrayQuerySpec spec = new SegmentArrayQuerySpec(groupingSetsList, compoundPredicateList);
    Pair<String, List<SqlStatement.Type>> pair = spec.generateSqlQuery();
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("generateSqlQuery: sql=" + pair.left);
    }
    return pair;
}

Example 10 with AggStar

use of mondrian.rolap.aggmatcher.AggStar in project mondrian by pentaho.

the class AggregationManager method findAgg.

/**
 * Finds an aggregate table in the given star which has the desired levels
 * and measures. Returns null if no aggregate table is suitable.
 *
 * <p>If there no aggregate is an exact match, returns a more
 * granular aggregate which can be rolled up, and sets rollup to true.
 * If one or more of the measures are distinct-count measures
 * rollup is possible only in limited circumstances.
 *
 * @param star Star
 * @param levelBitKey Set of levels
 * @param measureBitKey Set of measures
 * @param rollup Out parameter, is set to true if the aggregate is not
 *   an exact match
 * @return An aggregate, or null if none is suitable.
 */
public static AggStar findAgg(RolapStar star, final BitKey levelBitKey, final BitKey measureBitKey, boolean[] rollup) {
    // can NOT have any foreign keys.
    assert rollup != null;
    BitKey fullBitKey = levelBitKey.or(measureBitKey);
    // a levelBitKey with all parent bits set.
    final BitKey expandedLevelBitKey = expandLevelBitKey(star, levelBitKey.copy());
    // we need only find the first one and return it.
    for (AggStar aggStar : star.getAggStars()) {
        // superset match
        if (!aggStar.superSetMatch(fullBitKey)) {
            continue;
        }
        boolean isDistinct = measureBitKey.intersects(aggStar.getDistinctMeasureBitKey());
        // we can use it without looking any further.
        if (!isDistinct) {
            // Need to use SUM if the query levels don't match
            // the agg stars levels, or if the agg star is not
            // fully collapsed.
            rollup[0] = !aggStar.isFullyCollapsed() || aggStar.hasIgnoredColumns() || (levelBitKey.isEmpty() || !aggStar.getLevelBitKey().equals(levelBitKey));
            return aggStar;
        } else if (aggStar.hasIgnoredColumns()) {
            // we cannot safely pull a distinct count from an agg
            // table if ignored columns are present since granularity
            // may not be at the level of the dc measure
            LOGGER.info(aggStar.getFactTable().getName() + " cannot be used for distinct-count measures since it has" + " unused or ignored columns.");
            continue;
        }
        // If there are distinct measures, we can only rollup in limited
        // circumstances.
        // No foreign keys (except when its used as a distinct count
        // measure).
        // Level key exact match.
        // Measure superset match.
        // Compute the core levels -- those which can be safely
        // rolled up to. For example,
        // if the measure is 'distinct customer count',
        // and the agg table has levels customer_id,
        // then gender is a core level.
        final BitKey distinctMeasuresBitKey = measureBitKey.and(aggStar.getDistinctMeasureBitKey());
        final BitSet distinctMeasures = distinctMeasuresBitKey.toBitSet();
        BitKey combinedLevelBitKey = null;
        for (int k = distinctMeasures.nextSetBit(0); k >= 0; k = distinctMeasures.nextSetBit(k + 1)) {
            final AggStar.FactTable.Measure distinctMeasure = aggStar.lookupMeasure(k);
            BitKey rollableLevelBitKey = distinctMeasure.getRollableLevelBitKey();
            if (combinedLevelBitKey == null) {
                combinedLevelBitKey = rollableLevelBitKey;
            } else {
                // TODO use '&=' to remove unnecessary copy
                combinedLevelBitKey = combinedLevelBitKey.and(rollableLevelBitKey);
            }
        }
        if (aggStar.hasForeignKeys()) {
            /*
                    StringBuilder buf = new StringBuilder(256);
                    buf.append("");
                    buf.append(star.getFactTable().getAlias());
                    buf.append(Util.nl);
                    buf.append("foreign =");
                    buf.append(levelBitKey);
                    buf.append(Util.nl);
                    buf.append("measure =");
                    buf.append(measureBitKey);
                    buf.append(Util.nl);
                    buf.append("aggstar =");
                    buf.append(aggStar.getBitKey());
                    buf.append(Util.nl);
                    buf.append("distinct=");
                    buf.append(aggStar.getDistinctMeasureBitKey());
                    buf.append(Util.nl);
                    buf.append("AggStar=");
                    buf.append(aggStar.getFactTable().getName());
                    buf.append(Util.nl);
                    for (Iterator columnIter =
                            aggStar.getFactTable().getColumns().iterator();
                         columnIter.hasNext();) {
                        AggStar.Table.Column column =
                                (AggStar.Table.Column) columnIter.next();
                        buf.append("   ");
                        buf.append(column);
                        buf.append(Util.nl);
                    }
System.out.println(buf.toString());
*/
            // This is a little pessimistic. If the measure is
            // 'count(distinct customer_id)' and one of the foreign keys is
            // 'customer_id' then it is OK to roll up.
            // Some of the measures in this query are distinct count.
            // Get all of the foreign key columns.
            // For each such measure, is it based upon a foreign key.
            // Are there any foreign keys left over. No, can use AggStar.
            BitKey fkBitKey = aggStar.getForeignKeyBitKey().copy();
            for (AggStar.FactTable.Measure measure : aggStar.getFactTable().getMeasures()) {
                if (measure.isDistinct()) {
                    if (measureBitKey.get(measure.getBitPosition())) {
                        fkBitKey.clear(measure.getBitPosition());
                    }
                }
            }
            if (!fkBitKey.isEmpty()) {
                // AggStar.
                continue;
            }
        }
        // so will still be an allowable agg match
        if (!aggStar.select(expandedLevelBitKey, combinedLevelBitKey, measureBitKey)) {
            continue;
        }
        if (expandedLevelBitKey.isEmpty()) {
            // only the first (non-rolled-up) to be returned.
            continue;
        }
        rollup[0] = !aggStar.getLevelBitKey().equals(expandedLevelBitKey);
        return aggStar;
    }
    return null;
}