/*
// This software is subject to the terms of the Eclipse Public License v1.0
// Agreement, available at the following URL:
// http://www.eclipse.org/legal/epl-v10.html.
// Copyright (C) 2007-2011 Julian Hyde
// All Rights Reserved.
// You must accept the terms of that agreement to use this software.
*/
package org.olap4j;
import org.olap4j.impl.Bug;
import org.olap4j.mdx.SelectNode;
import org.olap4j.metadata.*;
import org.olap4j.query.*;
import org.olap4j.query.QueryDimension.HierarchizeMode;
import org.olap4j.query.Selection.Operator;
import org.olap4j.test.TestContext;
import java.sql.Connection;
import java.sql.DriverManager;
import junit.framework.TestCase;
import static org.olap4j.test.TestContext.nameList;
/**
* Unit test illustrating sequence of calls to olap4j API from a graphical
* client.
*
* @since May 22, 2007
* @author James Dixon
* @version $Id: OlapTest.java 404 2011-03-18 21:54:56Z lucboudreau $
*/
public class OlapTest extends TestCase {
final TestContext.Tester tester = TestContext.instance().getTester();
private Connection connection;
public OlapTest() {
super();
}
protected void tearDown() throws Exception {
// Simple strategy to prevent connection leaks
if (connection != null
&& !connection.isClosed())
{
connection.close();
connection = null;
}
}
public Cube getFoodmartCube(String cubeName) {
try {
connection = tester.createConnection();
OlapConnection olapConnection =
tester.getWrapper().unwrap(connection, OlapConnection.class);
Catalog catalog = olapConnection.getOlapCatalogs().get("FoodMart");
NamedList<Schema> schemas = catalog.getSchemas();
if (schemas.size() == 0) {
return null;
}
// Use the first schema
Schema schema = schemas.get(0);
// Get a list of cube objects and dump their names
NamedList<Cube> cubes = schema.getCubes();
if (cubes.size() == 0) {
// no cubes where present
return null;
}
// take the first cube
return cubes.get(cubeName);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public void testModel() {
try {
if (false) {
// define the connection information
String schemaUri = "file:/open/mondrian/demo/FoodMart.xml";
String schemaName = "FoodMart";
String userName = "foodmartuser";
String password = "foodmartpassword";
String jdbc =
"jdbc:mysql://localhost/foodmart?user=foodmartuser"
+ "&password=foodmartpassword";
// Create a connection object to the specific implementation of an
// olap4j source. This is the only provider-specific code.
Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
connection = DriverManager.getConnection(
"jdbc:mondrian:Jdbc=" + jdbc
+ ";User=" + userName
+ ";Password=" + password
+ ";Catalog=" + schemaUri);
} else {
connection = tester.createConnection();
}
OlapConnection olapConnection =
tester.getWrapper().unwrap(connection, OlapConnection.class);
// REVIEW: jhyde: Why do you want to name connections? We could add
// a connect string property 'description', if that helps
// connection.setName("First Connection");
// The code from here on is generic olap4j stuff
// Get a list of the schemas available from this connection and dump
// their names.
Catalog catalog = olapConnection.getOlapCatalogs().get("FoodMart");
NamedList<Schema> schemas = catalog.getSchemas();
if (schemas.size() == 0) {
// No schemas were present
return;
}
// Use the first schema
Schema schema = schemas.get(0);
// Get a list of cube objects and dump their names
NamedList<Cube> cubes = schema.getCubes();
if (cubes.size() == 0) {
// no cubes where present
return;
}
// take the "Sales" cube
Cube cube = cubes.get("Sales");
// The code from this point on is for the Foodmart schema
// Create a new query
Query query = new Query("my query", cube);
QueryDimension productQuery = query.getDimension("Product");
QueryDimension storeQuery = query.getDimension("Store");
QueryDimension timeQuery = query.getDimension("Time");
Member productMember =
cube.lookupMember(nameList("Product", "Drink"));
// create some selections for Store
storeQuery.include(
Selection.Operator.CHILDREN, nameList("Store", "USA"));
// create some selections for Product
productQuery.clearInclusions();
productQuery.include(
Selection.Operator.CHILDREN, productMember);
productQuery.include(
Selection.Operator.CHILDREN, nameList("Product", "Food"));
// create some selections for Time
timeQuery.include(
Selection.Operator.CHILDREN, nameList("Time", "1997"));
// place our dimensions on the axes
query.getAxis(Axis.COLUMNS).addDimension(productQuery);
assert productQuery.getAxis() == query.getAxis(Axis.COLUMNS);
query.getAxis(Axis.ROWS).addDimension(storeQuery);
query.getAxis(Axis.ROWS).addDimension(timeQuery);
try {
query.getAxis(Axis.ROWS).addDimension(storeQuery);
fail("expected exception");
} catch (Exception e) {
assertTrue(
e.getMessage().contains("dimension already on this axis"));
}
query.validate();
query.execute();
// for shits and giggles we'll swap the axes over
query.swapAxes();
query.validate();
query.execute();
} catch (Throwable t) {
t.printStackTrace();
fail();
}
}
public void testSelectionModes() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// TEST CHILDREN SELECTION
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{[Product].[Drink].Children} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// TEST ANCESTORS SELECTION
productDimension.clearInclusions();
productDimension.include(
Selection.Operator.ANCESTORS, nameList("Product", "Drink"));
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{Ascendants([Product].[Drink])} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// TEST DESCENDANTS SELECTION
productDimension.clearInclusions();
productDimension.include(
Selection.Operator.DESCENDANTS, nameList("Product", "Drink"));
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{Descendants([Product].[Drink])} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// TEST INCLUDE_CHILDREN SELECTION
productDimension.clearInclusions();
productDimension.include(
Selection.Operator.INCLUDE_CHILDREN,
nameList("Product", "Drink"));
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{{[Product].[Drink], [Product].[Drink].Children}} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// TEST SIBLINGS SELECTION
productDimension.clearInclusions();
productDimension.include(
Selection.Operator.SIBLINGS, nameList("Product", "Drink"));
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{[Product].[Drink].Siblings} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// TEST LEVEL MEMBERS SELECTION
NamedList<Level> productLevels =
productDimension.getDimension().getDefaultHierarchy().getLevels();
Level productDepartments = productLevels.get("Product Department");
productDimension.include(productDepartments);
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{[Product].[Drink].Siblings, [Product].[Product Department].Members} ON ROWS\n"
+ "FROM [Sales]", mdxString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testMultipleDimensionSelections() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.INCLUDE_CHILDREN, nameList("Store", "USA"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(
Selection.Operator.CHILDREN, nameList("Time", "1997"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin([Product].[Drink].Children, "
+ "CrossJoin({[Store].[USA], "
+ "[Store].[USA].Children}, "
+ "[Time].[1997].Children)) ON ROWS\n"
+ "FROM [Sales]", mdxString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testSwapAxes() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{[Product].[Drink].Children} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
query.swapAxes();
assertEquals(
Axis.COLUMNS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.ROWS,
measuresDimension.getAxis().getLocation());
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Product].[Drink].Children} ON COLUMNS,\n"
+ "{[Measures].[Store Sales]} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
query.swapAxes();
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testSortDimension() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.INCLUDE_CHILDREN,
nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.getAxis(Axis.FILTER).addDimension(timeDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{{[Product].[Drink], [Product].[Drink].Children}} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Sort the products in ascending order.
query.getDimension("Product").sort(SortOrder.DESC);
SelectNode sortedMdx = query.getSelect();
String sortedMdxString = sortedMdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{Order({{[Product].[Drink], [Product].[Drink].Children}}, [Product].CurrentMember.Name, DESC)} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
sortedMdxString);
CellSet results = query.execute();
String s = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[Drink]}\n"
+ "{[Product].[Drink].[Dairy]}\n"
+ "{[Product].[Drink].[Beverages]}\n"
+ "{[Product].[Drink].[Alcoholic Beverages]}\n"
+ "Row #0: 4,409.58\n"
+ "Row #1: 629.69\n"
+ "Row #2: 2,477.02\n"
+ "Row #3: 1,302.87\n",
s);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testSortMultipleDimension() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
assertEquals(
Axis.ROWS,
timeDimension.getAxis().getLocation());
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin({[Time].[1997].[Q3].[7]}, [Product].[Drink].Children) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// Sort the products in ascending order.
query.getDimension("Product").sort(SortOrder.DESC);
SelectNode sortedMdx = query.getSelect();
String sortedMdxString = sortedMdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin({[Time].[1997].[Q3].[7]}, Order({[Product].[Drink].Children}, [Product].CurrentMember.Name, DESC)) ON ROWS\n"
+ "FROM [Sales]",
sortedMdxString);
CellSet results = query.execute();
String s = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Drink].[Dairy]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Drink].[Beverages]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Drink].[Alcoholic Beverages]}\n"
+ "Row #0: 629.69\n"
+ "Row #1: 2,477.02\n"
+ "Row #2: 1,302.87\n",
s);
// Just to be sure we execute the sort on a NON EMPTY axis again
query.getAxis(Axis.ROWS).setNonEmpty(true);
productDimension.clearInclusions();
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Food"));
SelectNode sortedMdxNonEmpty = query.getSelect();
String sortedMdxNonEmptyString = sortedMdxNonEmpty.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "NON EMPTY CrossJoin({[Time].[1997].[Q3].[7]}, Order({[Product].[Food].Children}, [Product].CurrentMember.Name, DESC)) ON ROWS\n"
+ "FROM [Sales]",
sortedMdxNonEmptyString);
CellSet results2 = query.execute();
String s2 = TestContext.toString(results2);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Starchy Foods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Snacks]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Snack Foods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Seafood]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Produce]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Meat]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Frozen Foods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Eggs]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Deli]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Dairy]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Canned Products]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Canned Foods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Breakfast Foods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Baking Goods]}\n"
+ "{[Time].[1997].[Q3].[7], [Product].[Food].[Baked Goods]}\n"
+ "Row #0: 1,059.06\n"
+ "Row #1: 1,248.92\n"
+ "Row #2: 6,342.01\n"
+ "Row #3: 383.20\n"
+ "Row #4: 7,084.85\n"
+ "Row #5: 304.61\n"
+ "Row #6: 5,027.30\n"
+ "Row #7: 930.70\n"
+ "Row #8: 2,222.69\n"
+ "Row #9: 2,896.81\n"
+ "Row #10: 250.84\n"
+ "Row #11: 3,301.38\n"
+ "Row #12: 551.95\n"
+ "Row #13: 3,232.70\n"
+ "Row #14: 1,487.74\n",
s2);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testSelectionContext() throws Exception {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.INCLUDE_CHILDREN,
nameList("Product", "All Products"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(
Selection.Operator.MEMBER, nameList("Time", "Year", "1997"));
Selection selection =
timeDimension.include(
Selection.Operator.CHILDREN, nameList("Time", "Year", "1997"));
selection.addContext(
productDimension.createSelection(
nameList("Product", "All Products", "Drink")));
// [Store].[All Stores]
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.MEMBER, nameList("Store", "All Stores"));
Selection children =
storeDimension.include(
Selection.Operator.CHILDREN, nameList("Store", "All Stores"));
children.addContext(
productDimension.createSelection(
nameList("Product", "All Products", "Drink")));
children.addContext(
timeDimension.createSelection(nameList("Time", "1997", "Q3")));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "Hierarchize(Union(CrossJoin({[Product].[All Products], [Product].[All Products].Children}, CrossJoin({[Time].[1997]}, {[Store].[All Stores]})), Union(CrossJoin({[Product].[Drink]}, CrossJoin({[Time].[1997].[Q3]}, [Store].[All Stores].Children)), CrossJoin({[Product].[Drink]}, CrossJoin([Time].[1997].Children, {[Store].[All Stores]}))))) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// Sort the rows in ascending order.
query.getAxis(Axis.ROWS).sort(
SortOrder.ASC,
nameList("Measures", "Store Sales"));
SelectNode sortedMdx = query.getSelect();
String sortedMdxString = sortedMdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "Order(Hierarchize(Union(CrossJoin({[Product].[All Products], [Product].[All Products].Children}, CrossJoin({[Time].[1997]}, {[Store].[All Stores]})), Union(CrossJoin({[Product].[Drink]}, CrossJoin({[Time].[1997].[Q3]}, [Store].[All Stores].Children)), CrossJoin({[Product].[Drink]}, CrossJoin([Time].[1997].Children, {[Store].[All Stores]}))))), [Measures].[Store Sales], ASC) ON ROWS\n"
+ "FROM [Sales]",
sortedMdxString);
CellSet results = query.execute();
String s = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[All Products], [Time].[1997], [Store].[All Stores]}\n"
+ "{[Product].[Drink], [Time].[1997], [Store].[All Stores]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q1], [Store].[All Stores]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q2], [Store].[All Stores]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q3], [Store].[All Stores]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q3], [Store].[Canada]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q3], [Store].[Mexico]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q3], [Store].[USA]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q4], [Store].[All Stores]}\n"
+ "{[Product].[Non-Consumable], [Time].[1997], [Store].[All Stores]}\n"
+ "{[Product].[Food], [Time].[1997], [Store].[All Stores]}\n"
+ "Row #0: 565,238.13\n"
+ "Row #1: 48,836.21\n"
+ "Row #2: 11,585.80\n"
+ "Row #3: 11,914.58\n"
+ "Row #4: 11,994.00\n"
+ "Row #5: \n"
+ "Row #6: \n"
+ "Row #7: 11,994.00\n"
+ "Row #8: 13,341.83\n"
+ "Row #9: 107,366.33\n"
+ "Row #10: 409,035.59\n",
s);
}
public void testComplexSelectionContext() throws Exception {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.MEMBER, nameList("Product", "All Products"));
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "All Products"));
QueryDimension timeDimension = query.getDimension("Time");
Selection selection = timeDimension.include(
Selection.Operator.CHILDREN, nameList("Time", "Year", "1997"));
selection.addContext(
productDimension.createSelection(
nameList("Product", "All Products")));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.ROWS,
timeDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
if (!Bug.BugOlap4j3106220Fixed) {
return;
}
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "Hierarchize(Union(CrossJoin({[Product].[All Products]}, [Time].[1997].Children), CrossJoin([Product].[All Products].Children, [Time].[1997].Children))) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
CellSet results = query.execute();
String s = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[All Products], [Time].[1997].[Q1]}\n"
+ "{[Product].[All Products], [Time].[1997].[Q2]}\n"
+ "{[Product].[All Products], [Time].[1997].[Q3]}\n"
+ "{[Product].[All Products], [Time].[1997].[Q4]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q1]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q2]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q3]}\n"
+ "{[Product].[Drink], [Time].[1997].[Q4]}\n"
+ "{[Product].[Food], [Time].[1997].[Q1]}\n"
+ "{[Product].[Food], [Time].[1997].[Q2]}\n"
+ "{[Product].[Food], [Time].[1997].[Q3]}\n"
+ "{[Product].[Food], [Time].[1997].[Q4]}\n"
+ "{[Product].[Non-Consumable], [Time].[1997].[Q1]}\n"
+ "{[Product].[Non-Consumable], [Time].[1997].[Q2]}\n"
+ "{[Product].[Non-Consumable], [Time].[1997].[Q3]}\n"
+ "{[Product].[Non-Consumable], [Time].[1997].[Q4]}\n"
+ "Row #0: 139,628.35\n"
+ "Row #1: 132,666.27\n"
+ "Row #2: 140,271.89\n"
+ "Row #3: 152,671.62\n"
+ "Row #4: 11,585.80\n"
+ "Row #5: 11,914.58\n"
+ "Row #6: 11,994.00\n"
+ "Row #7: 13,341.83\n"
+ "Row #8: 101,261.32\n"
+ "Row #9: 95,436.00\n"
+ "Row #10: 101,807.60\n"
+ "Row #11: 110,530.67\n"
+ "Row #12: 26,781.23\n"
+ "Row #13: 25,315.69\n"
+ "Row #14: 26,470.29\n"
+ "Row #15: 28,799.12\n",
s);
}
public void testSortAxis() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.INCLUDE_CHILDREN,
nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.getAxis(Axis.FILTER).addDimension(timeDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{{[Product].[Drink], [Product].[Drink].Children}} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Sort the rows in ascending order.
query.getAxis(Axis.ROWS).sort(
SortOrder.BASC,
nameList("Measures", "Store Sales"));
SelectNode sortedMdx = query.getSelect();
String sortedMdxString = sortedMdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "Order({{[Product].[Drink], [Product].[Drink].Children}}, [Measures].[Store Sales], BASC) ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
sortedMdxString);
CellSet results = query.execute();
String s = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[Drink].[Dairy]}\n"
+ "{[Product].[Drink].[Alcoholic Beverages]}\n"
+ "{[Product].[Drink].[Beverages]}\n"
+ "{[Product].[Drink]}\n"
+ "Row #0: 629.69\n"
+ "Row #1: 1,302.87\n"
+ "Row #2: 2,477.02\n"
+ "Row #3: 4,409.58\n",
s);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testDimensionsOrder() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.INCLUDE_CHILDREN, nameList("Store", "USA"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(
Selection.Operator.CHILDREN, nameList("Time", "1997"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin([Product].[Drink].Children, "
+ "CrossJoin({[Store].[USA], "
+ "[Store].[USA].Children}, "
+ "[Time].[1997].Children)) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// Push down the Products dimension.
query.getAxis(Axis.ROWS).pushDown(0);
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin({[Store].[USA], "
+ "[Store].[USA].Children}, "
+ "CrossJoin([Product].[Drink].Children, "
+ "[Time].[1997].Children)) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
// Pull Up the Time dimension.
query.getAxis(Axis.ROWS).pullUp(2);
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "CrossJoin({[Store].[USA], "
+ "[Store].[USA].Children}, "
+ "CrossJoin([Time].[1997].Children, "
+ "[Product].[Drink].Children)) ON ROWS\n"
+ "FROM [Sales]",
mdxString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
/**
* Note: hierarchize mode only works when a single dimension is selected.
*/
public void testDimensionsHierarchize() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.INCLUDE_CHILDREN, nameList("Store", "USA"));
storeDimension.setHierarchizeMode(HierarchizeMode.POST);
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{Hierarchize({{[Store].[USA], "
+ "[Store].[USA].Children}}, POST)} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
storeDimension.setHierarchizeMode(HierarchizeMode.PRE);
query.validate();
mdx = query.getSelect();
mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{Hierarchize({{[Store].[USA], "
+ "[Store].[USA].Children}})} ON ROWS\n"
+ "FROM [Sales]",
mdxString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
/**
* This test makes sure that the generated MDX model is not affected
* by subsequent operations performed on the source query model.
*/
public void testQueryVersusParseTreeIndependence() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
// Setup a base query.
Query query = new Query("my query", cube);
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.INCLUDE_CHILDREN,
nameList("Product", "Drink"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
assertEquals(
Axis.ROWS,
productDimension.getAxis().getLocation());
assertEquals(
Axis.COLUMNS,
measuresDimension.getAxis().getLocation());
// These variables are important. We will evaluate those
// to decide if there are links between the MDX and the Query
SelectNode originalMdx = query.getSelect();
String originalMdxString = originalMdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS,\n"
+ "{{[Product].[Drink], [Product].[Drink].Children}} ON ROWS\n"
+ "FROM [Sales]",
originalMdxString);
// change selections
measuresDimension.include(
Selection.Operator.SIBLINGS,
nameList("Measures", "Customer Count"));
productDimension.include(
Selection.Operator.SIBLINGS,
nameList(
"Product", "All Products", "Drink", "Alcoholic Beverages"));
// Add something to crossjoin with
query.getAxis(Axis.ROWS).addDimension(
query.getDimension("Gender"));
query.getDimension("Gender").include(
Operator.CHILDREN, nameList("Gender", "All Gender"));
query.getAxis(null).addDimension(
query.getDimension("Product"));
query.validate();
// Check if the MDX object tree is still the same
assertEquals(originalMdxString, originalMdx.toString());
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testExclusionModes() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
// Setup a base query.
Query query = new Query("my query", cube);
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN,
nameList("Product", "Drink", "Beverages"));
productDimension.include(
Selection.Operator.CHILDREN,
nameList("Product", "Food", "Frozen Foods"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Sales Count"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.getAxis(Axis.FILTER).addDimension(timeDimension);
query.validate();
// Validate the generated MDX
String mdxString = query.getSelect().toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Sales Count]} ON COLUMNS,\n"
+ "{[Product].[Drink].[Beverages].Children, [Product].[Food].[Frozen Foods].Children} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Validate the returned results
CellSet results = query.execute();
String resultsString = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sales Count]}\n"
+ "Axis #2:\n"
+ "{[Product].[Drink].[Beverages].[Carbonated Beverages]}\n"
+ "{[Product].[Drink].[Beverages].[Drinks]}\n"
+ "{[Product].[Drink].[Beverages].[Hot Beverages]}\n"
+ "{[Product].[Drink].[Beverages].[Pure Juice Beverages]}\n"
+ "{[Product].[Food].[Frozen Foods].[Breakfast Foods]}\n"
+ "{[Product].[Food].[Frozen Foods].[Frozen Desserts]}\n"
+ "{[Product].[Food].[Frozen Foods].[Frozen Entrees]}\n"
+ "{[Product].[Food].[Frozen Foods].[Meat]}\n"
+ "{[Product].[Food].[Frozen Foods].[Pizza]}\n"
+ "{[Product].[Food].[Frozen Foods].[Vegetables]}\n"
+ "Row #0: 103\n"
+ "Row #1: 65\n"
+ "Row #2: 125\n"
+ "Row #3: 100\n"
+ "Row #4: 143\n"
+ "Row #5: 185\n"
+ "Row #6: 68\n"
+ "Row #7: 81\n"
+ "Row #8: 105\n"
+ "Row #9: 212\n",
resultsString);
// Exclude the Carbonated Beverages because they are not good
// for your health.
query.getDimension("Product").exclude(
nameList(
"Product", "Drink", "Beverages", "Carbonated Beverages"));
// Validate the generated MDX
query.validate();
mdxString = query.getSelect().toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Sales Count]} ON COLUMNS,\n"
+ "{Except({[Product].[Drink].[Beverages].Children, [Product].[Food].[Frozen Foods].Children}, {[Product].[Drink].[Beverages].[Carbonated Beverages]})} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Validate the returned results
results = query.execute();
resultsString = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sales Count]}\n"
+ "Axis #2:\n"
+ "{[Product].[Drink].[Beverages].[Drinks]}\n"
+ "{[Product].[Drink].[Beverages].[Hot Beverages]}\n"
+ "{[Product].[Drink].[Beverages].[Pure Juice Beverages]}\n"
+ "{[Product].[Food].[Frozen Foods].[Breakfast Foods]}\n"
+ "{[Product].[Food].[Frozen Foods].[Frozen Desserts]}\n"
+ "{[Product].[Food].[Frozen Foods].[Frozen Entrees]}\n"
+ "{[Product].[Food].[Frozen Foods].[Meat]}\n"
+ "{[Product].[Food].[Frozen Foods].[Pizza]}\n"
+ "{[Product].[Food].[Frozen Foods].[Vegetables]}\n"
+ "Row #0: 65\n"
+ "Row #1: 125\n"
+ "Row #2: 100\n"
+ "Row #3: 143\n"
+ "Row #4: 185\n"
+ "Row #5: 68\n"
+ "Row #6: 81\n"
+ "Row #7: 105\n"
+ "Row #8: 212\n",
resultsString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testExclusionMultipleDimensionModes() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
// Setup a base query.
Query query = new Query("my query", cube);
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN,
nameList("Product", "Drink", "Beverages"));
productDimension.include(
Selection.Operator.CHILDREN,
nameList("Product", "Food", "Frozen Foods"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Sales Count"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.MEMBER, nameList("Store", "USA"));
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.ROWS).addDimension(productDimension);
query.getAxis(Axis.FILTER).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
// Validate the generated MDX
String mdxString = query.getSelect().toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Sales Count]} ON COLUMNS,\n"
+ "Hierarchize(Union(CrossJoin({[Store].[USA]}, [Product].[Drink].[Beverages].Children), CrossJoin({[Store].[USA]}, [Product].[Food].[Frozen Foods].Children))) ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Validate the returned results
CellSet results = query.execute();
String resultsString = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sales Count]}\n"
+ "Axis #2:\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Carbonated Beverages]}\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Drinks]}\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Hot Beverages]}\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Pure Juice Beverages]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Breakfast Foods]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Frozen Desserts]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Frozen Entrees]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Meat]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Pizza]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Vegetables]}\n"
+ "Row #0: 103\n"
+ "Row #1: 65\n"
+ "Row #2: 125\n"
+ "Row #3: 100\n"
+ "Row #4: 143\n"
+ "Row #5: 185\n"
+ "Row #6: 68\n"
+ "Row #7: 81\n"
+ "Row #8: 105\n"
+ "Row #9: 212\n",
resultsString);
// Exclude the Carbonated Beverages because they are not good
// for your health.
query.getDimension("Product").exclude(
nameList(
"Product", "Drink", "Beverages", "Carbonated Beverages"));
// Validate the generated MDX
query.validate();
mdxString = query.getSelect().toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Sales Count]} ON COLUMNS,\n"
+ "Hierarchize(Union(CrossJoin({[Store].[USA]}, Except({[Product].[Drink].[Beverages].Children}, {[Product].[Drink].[Beverages].[Carbonated Beverages]})), CrossJoin({[Store].[USA]}, Except({[Product].[Food].[Frozen Foods].Children}, {[Product].[Drink].[Beverages].[Carbonated Beverages]})))) ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE {[Time].[1997].[Q3].[7]}",
mdxString);
// Validate the returned results
results = query.execute();
resultsString = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sales Count]}\n"
+ "Axis #2:\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Drinks]}\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Hot Beverages]}\n"
+ "{[Store].[USA], [Product].[Drink].[Beverages].[Pure Juice Beverages]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Breakfast Foods]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Frozen Desserts]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Frozen Entrees]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Meat]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Pizza]}\n"
+ "{[Store].[USA], [Product].[Food].[Frozen Foods].[Vegetables]}\n"
+ "Row #0: 65\n"
+ "Row #1: 125\n"
+ "Row #2: 100\n"
+ "Row #3: 143\n"
+ "Row #4: 185\n"
+ "Row #5: 68\n"
+ "Row #6: 81\n"
+ "Row #7: 105\n"
+ "Row #8: 212\n",
resultsString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testCompoundFilter() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
// Setup a base query.
Query query = new Query("my query", cube);
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.MEMBER,
nameList("Product", "Drink", "Beverages"));
productDimension.include(
Selection.Operator.MEMBER,
nameList("Product", "Food", "Frozen Foods"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Sales Count"));
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(nameList("Time", "Year", "1997", "Q3", "7"));
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.MEMBER, nameList("Store", "USA"));
query.getAxis(Axis.ROWS).addDimension(storeDimension);
query.getAxis(Axis.FILTER).addDimension(productDimension);
query.getAxis(Axis.FILTER).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
query.validate();
// Validate the generated MDX
String mdxString = query.getSelect().toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Sales Count]} ON COLUMNS,\n"
+ "{[Store].[USA]} ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE Hierarchize(Union(CrossJoin({[Product].[Drink].[Beverages]}, {[Time].[1997].[Q3].[7]}), CrossJoin({[Product].[Food].[Frozen Foods]}, {[Time].[1997].[Q3].[7]})))",
mdxString);
// Validate the returned results
CellSet results = query.execute();
String resultsString = TestContext.toString(results);
TestContext.assertEqualsVerbose(
"Axis #0:\n"
+ "{[Product].[Drink].[Beverages], [Time].[1997].[Q3].[7]}\n"
+ "{[Product].[Food].[Frozen Foods], [Time].[1997].[Q3].[7]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sales Count]}\n"
+ "Axis #2:\n"
+ "{[Store].[USA]}\n"
+ "Row #0: 1,187\n",
resultsString);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testNonMandatoryQueryAxis() {
try {
Cube cube = getFoodmartCube("Sales");
if (cube == null) {
fail("Could not find Sales cube");
}
Query query = new Query("my query", cube);
// create selections
QueryDimension productDimension = query.getDimension("Product");
productDimension.include(
Selection.Operator.CHILDREN, nameList("Product", "Drink"));
QueryDimension storeDimension = query.getDimension("Store");
storeDimension.include(
Selection.Operator.INCLUDE_CHILDREN, nameList("Store", "USA"));
storeDimension.setHierarchizeMode(HierarchizeMode.POST);
QueryDimension timeDimension = query.getDimension("Time");
timeDimension.include(
Selection.Operator.CHILDREN, nameList("Time", "1997"));
QueryDimension measuresDimension = query.getDimension("Measures");
measuresDimension.include(nameList("Measures", "Store Sales"));
//query.getAxis(Axis.ROWS).addDimension(productDimension);
//query.getAxis(Axis.ROWS).addDimension(storeDimension);
//query.getAxis(Axis.ROWS).addDimension(timeDimension);
query.getAxis(Axis.COLUMNS).addDimension(measuresDimension);
//query.validate();
SelectNode mdx = query.getSelect();
String mdxString = mdx.toString();
TestContext.assertEqualsVerbose(
"SELECT\n"
+ "{[Measures].[Store Sales]} ON COLUMNS\n"
+ "FROM [Sales]",
mdxString);
try {
query.validate();
fail();
} catch (OlapException e) {
assertEquals(0, e.getCause().getMessage().indexOf(
"A valid Query requires at least one "
+ "dimension on the rows axis."));
}
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public static void main(String args[]) {
OlapTest olapTest = new OlapTest();
olapTest.testModel();
}
}
// End OlapTest.java