Query optimization techniques for business intelligence systems

A computer program product comprising a tangible computer-readable memory device having computer logic recorded thereon for enabling a processing unit to optimize a query for execution against one or more relational databases, the computer program product comprising:

• first computer program logic for enabling the processing unit to receive a query submitted by a user, the query including a measure expression and a dimensional context within which the measure expression is to be evaluated;
• second computer program logic for enabling the processing unit generate a first representation of the query, wherein the first representation of the query comprises a table sub-tree associated with the dimensional context and a scalar sub-tree associated with the measure expression;
• third computer program logic for enabling the processing unit to generate a second representation of the query based on the first representation of the query by transforming the scalar sub-tree into at least a sparse logical operator and creating an inner join logical operator that performs an inner join between at least a table returned by the sparse logical operator and a table returned by or derived from a table returned by a logical operator built from the table sub-tree;
• and
• fourth computer program logic for enabling the processing unit to provide the second representation of the query for application against the one or more relational databases.

A method for executing a join between a first table and a second table, comprising:

• generating a list of join columns that includes columns common to the first table and the second table;
• identifying one or more relevant columns by at least analyzing a filter table expression with which the second table is associated;
• for each join column in the list of join columns:
• determining whether every V-path between the join column and a relevant column contains another join column;
• and
• in response to determining that every V-path between the join column and a relevant column contains another join column, removing the join column from the list of join columns;
• and
• defining a join operation between the first and the second table that utilizes join columns that have not been removed from the list of join columns,
• at least one of the generating, identifying, determining, removing and defining steps being performed by one or more processors.