package globalExamples; import oracle.olapi.metadata.mdm.MdmCustomObjectFactory; import oracle.olapi.data.source.DataProvider; import oracle.olapi.data.source.FundamentalMetadataObject; import oracle.olapi.data.source.FundamentalMetadataProvider; import oracle.olapi.data.source.NumberSource; import oracle.olapi.data.source.Source; import oracle.olapi.data.source.StringSource; import oracle.olapi.metadata.mdm.MdmAttribute; import oracle.olapi.metadata.mdm.MdmHierarchy; import oracle.olapi.metadata.mdm.MdmLevel; import oracle.olapi.metadata.mdm.MdmLevelHierarchy; import oracle.olapi.metadata.mdm.MdmMeasure; import oracle.olapi.metadata.mdm.MdmPrimaryDimension; import oracle.olapi.metadata.mtm.MtmCustomObjectFactory; import oracle.olapi.metadata.mtm.MtmValueExpression; import java.util.List; /** * Complete code for Example 7-1 through Example 7-6 and Example 7-11 in * Chapter 7, Making Queries Using Source Objects, the Oracle OLAP Developer's * Guide to the OLAP API. * * This program uses the Context10g class, which uses the * CursorPrintWriter class. */ public class MakingQueriesExamples { public MakingQueriesExamples() { } public void run(String[] args) { Context10g context = new Context10g(args, false); DataProvider dp = context.getDataProvider(); // Example 7-1 controllingMatchingWithAlias(context, dp); // Example 7-2 usingTheDistinctMethod(context, dp); // Example 7-3 usingTheExtractMethod(context, dp); // Example 7-4 usingComparisonRuleRemove(context, dp); // Example 7-5 usingComparisonRuleDescending(context, dp); // Example 7-6 selectingFirstAndLastTimes(context, dp); // Example 7-8 selectingSubsetOfSourceElements(context, dp); // Example 7-11 hierarchicalSortingByMeasureValue(context, dp); } private void controllingMatchingWithAlias(Context10g context, DataProvider dp) { context.println("Example 7-1, Controlling Input-to-Source Matching With " + "the alias Method\n"); // Get the primary dimensions and the measure. MdmPrimaryDimension[] mdmPrimDims = context.getMdmPrimaryDimensionsByName(new String[] {"PRODUCT", "CUSTOMER", "CHANNEL", "TIME"}); MdmMeasure mdmUnits = context.getMdmMeasureByName("UNITS"); // Get the Source objects for the measure and for the default hierarchies // of the dimensions. NumberSource units = (NumberSource) mdmUnits.getSource(); StringSource prodHier = (StringSource) mdmPrimDims[0].getDefaultHierarchy().getSource(); StringSource custHier = (StringSource) mdmPrimDims[1].getDefaultHierarchy().getSource(); StringSource chanHier = (StringSource) mdmPrimDims[2].getDefaultHierarchy().getSource(); StringSource timeHier = (StringSource) mdmPrimDims[3].getDefaultHierarchy().getSource(); // Select single values for the hierarchies. Source prodSel = prodHier.selectValue("PRODUCT_ROLLUP::ITEM::15"); Source custSel = custHier.selectValue("SHIPMENTS_ROLLUP::SHIP_TO::52"); Source timeSel = timeHier.selectValue("CALENDAR::MONTH::55"); // Produce a Source that specifies the units values for the selected // dimension values. Source unitsSel = units.join(timeSel).join(custSel).join(prodSel); // Create aliases for the Channel dimension hierarchy Source chanAlias1 = chanHier.alias(); Source chanAlias2 = chanHier.alias(); // Join the aliases to the Source representing the units values specified // by the selected dimension elements, using the value method to make the // alias an input. NumberSource unitsSel1 = (NumberSource) unitsSel.join(chanAlias1.value()); NumberSource unitsSel2 = (NumberSource) unitsSel.join(chanAlias2.value()); // chanAlias2 is the first output of result, so its values are the row // (slower varying) values; chanAlias1 is the second output of result // so its values are the column (faster varying) values. Source result = unitsSel1.gt(unitsSel2) .join(chanAlias1) // Output 2, column .join(chanAlias2); // Output 1, row context.commit(); context.displayResult(result); } private void usingTheDistinctMethod(Context10g context, DataProvider dp) { context.println("\nExample 7-2, Using the distinct Method\n"); // Get the CUSTOMER dimension. MdmPrimaryDimension mdmCustDim = context.getMdmPrimaryDimensionByName("CUSTOMER"); // Get the MARKET_ROLLUP hierarchy of the dimension and the Source for it. MdmHierarchy mdmMarketRollup = context.getHierarchyByName(mdmCustDim, "MARKET_ROLLUP"); StringSource mktRollup = (StringSource)mdmMarketRollup.getSource(); // Get the ancestors attribute of the hierarchy and the Source for it. MdmAttribute mdmMktRollupAncestorsAttr = mdmMarketRollup.getAncestorsAttribute(); Source mktRollupAncestors = mdmMktRollupAncestorsAttr.getSource(); // Reverse the ancestors relation to get the descendants relation. Source mktRollupDescendants = mktRollup.join(mktRollupAncestors, mktRollup.value()); // Select an element of the hierarchy. Source selVal = mktRollup.selectValue("MARKET_ROLLUP::ACCOUNT::23"); // Select the descendants of the specified element. Source selValDescendants = mktRollupDescendants.join(mktRollup, selVal, false); // Append the descedants to the selected element, using distinct // to remove any duplicate elements. Source selValPlusDescendants = selVal.appendValues(selValDescendants).distinct(); // Do the same append operation without using distinct. Source selValPlusDescendantsWithoutDistinct = selVal.appendValues(selValDescendants); context.commit(); context.displayResult(selValPlusDescendants); context.println(" "); context.displayResult(selValPlusDescendantsWithoutDistinct); } private void usingTheExtractMethod(Context10g context, DataProvider dp) { context.println("\nExample 7-3, Using the extract Method\n"); // Get the primary dimensions and the measure. MdmPrimaryDimension[] mdmPrimDims = context.getMdmPrimaryDimensionsByName(new String[] {"PRODUCT", "CUSTOMER", "CHANNEL", "TIME"}); MdmMeasure mdmUnits = context.getMdmMeasureByName("UNITS"); MdmMeasure mdmUnitPrice = context.getMdmMeasureByName("UNIT_PRICE"); // Get the Source objects for the measure and for the default hierarchies // of the dimensions. NumberSource units = (NumberSource) mdmUnits.getSource(); NumberSource unitPrice = (NumberSource) mdmUnitPrice.getSource(); StringSource prodHier = (StringSource) mdmPrimDims[0].getDefaultHierarchy().getSource(); StringSource custHier = (StringSource) mdmPrimDims[1].getDefaultHierarchy().getSource(); StringSource chanHier = (StringSource) mdmPrimDims[2].getDefaultHierarchy().getSource(); StringSource timeHier = (StringSource) mdmPrimDims[3].getDefaultHierarchy().getSource(); // Select values for the hierarchies. Source prodSel = prodHier.selectValues(new String[] {"PRODUCT_ROLLUP::ITEM::13", "PRODUCT_ROLLUP::ITEM::14", "PRODUCT_ROLLUP::ITEM::15"}); Source chanSel = chanHier.selectValue("CHANNEL_ROLLUP::CHANNEL::2"); //Source timeSel = timeHier.selectValue("CALENDAR::YEAR::3"); Source timeSel = timeHier.selectValue("CALENDAR::MONTH::59"); Source custSel = custHier.selectValue("SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1"); /* Source timeSel = timeHier.selectValues(new String[] {"CALENDAR::MONTH::43", "CALENDAR::MONTH::44", "CALENDAR::MONTH::45"}); */ Source sales = units.times(unitPrice); Source measDim = dp.createListSource(new Source[] {units, unitPrice, sales}); Source result = measDim.extract().join(measDim) .join(prodSel) .join(timeSel) //.joinHidden(custSel) //.joinHidden(chanSel); .join(chanSel) .join(custSel); context.commit(); context.displayResult(result); } private void usingComparisonRuleRemove(Context10g context, DataProvider dp) { context.println("\nExample 7-4, Using COMPARISON_RULE_REMOVE\n"); // Get the CUSTOMER dimension. MdmPrimaryDimension mdmCustDim = context.getMdmPrimaryDimensionByName("CUSTOMER"); // Get the MARKET_ROLLUP hierarchy of the dimension and the Source for it. MdmHierarchy mdmMarketRollup = context.getHierarchyByName(mdmCustDim, "MARKET_ROLLUP"); StringSource mktRollup = (StringSource)mdmMarketRollup.getSource(); // Get the ancestors attribute of the hierarchy and the Source for it. MdmAttribute mdmMktRollupAncestorsAttr = mdmMarketRollup.getAncestorsAttribute(); Source mktRollupAncestors = mdmMktRollupAncestorsAttr.getSource(); // Reverse the ancestors relation to get the descendants relation. Source mktRollupDescendants = mktRollup.join(mktRollupAncestors, mktRollup.value()); // Select an element of the hierarchy. Source selVal = mktRollup.selectValue("MARKET_ROLLUP::ACCOUNT::23"); // Create a descendants relation that removes the input value. Source mktRollupDescendantsOnly = mktRollupDescendants.join(mktRollupDescendants.getDataType().value(), mktRollup.value(), Source.COMPARISON_RULE_REMOVE); // Select the descendants of the specified element. Source selValDescendants = mktRollupDescendants.join(mktRollup, selVal); // Select only the descendants of the specified element. Source selValDescendantsOnly = mktRollupDescendantsOnly.join(mktRollup, selVal); context.commit(); context.displayResult(selValDescendants); context.println(" "); context.displayResult(selValDescendantsOnly); } private void usingComparisonRuleDescending(Context10g context, DataProvider dp) { context.println("\nExample 7-5, Using COMPARISON_RULE_DESCENDING\n"); // Get the UNIT_PRICE measure and the Source for it. MdmMeasure mdmUnitPrice = context.getMdmMeasureByName("UNIT_PRICE"); NumberSource unitPrice = (NumberSource) mdmUnitPrice.getSource(); // Get the PRODUCT and TIME dimensions. MdmPrimaryDimension mdmProdDim = context.getMdmPrimaryDimensionByName("PRODUCT"); MdmPrimaryDimension mdmTimeDim = context.getMdmPrimaryDimensionByName("TIME"); // Get the short description attribute for the dimensions. MdmAttribute mdmProdShortDescrAttr = mdmProdDim.getShortValueDescriptionAttribute(); MdmAttribute mdmTimeShortDescrAttr = mdmTimeDim.getShortValueDescriptionAttribute(); // Get the Source objects for the attributes. Source prodShortDescr = mdmProdShortDescrAttr.getSource(); Source timeShortDescr = mdmTimeShortDescrAttr.getSource(); // Get the default hierarchies of the dimensions and their Source objects. MdmLevelHierarchy mdmProdRollup = (MdmLevelHierarchy) mdmProdDim.getDefaultHierarchy(); MdmLevelHierarchy mdmCalendar = (MdmLevelHierarchy) mdmTimeDim.getDefaultHierarchy(); Source prodRollup = mdmProdRollup.getSource(); StringSource calendar = (StringSource) mdmCalendar.getSource(); // Get the FAMILY level of the hierarchy and the Source for it. MdmLevel mdmFamilyLevel = context.getLevelByName(mdmProdRollup, "FAMILY"); Source prodSel = mdmFamilyLevel.getSource(); // Join the family elements to the product short description attribute. Source prodSelWithShortDescr = prodShortDescr.join(prodSel); // Select the May, 2001. Source timeSel = calendar.selectValue("CALENDAR::MONTH::59"); // Join the time selection to the time short description attribute. Source timeSelWithShortDescr = timeShortDescr.join(timeSel); Source result = prodSelWithShortDescr.join(unitPrice, unitPrice.getDataType(), Source.COMPARISON_RULE_DESCENDING, true) .join(timeSelWithShortDescr); context.commit(); context.displayResult(result); } private void selectingFirstAndLastTimes(Context10g context, DataProvider dp) { context.println("\nExample 7-6, Selecting the First and Last Time Elements\n"); MdmPrimaryDimension mdmTimeDim = context.getMdmPrimaryDimensionByName("TIME"); // Get the level attribute of the dimension. MdmAttribute mdmTimeLevelAttr = mdmTimeDim.getLevelAttribute(); // Get the default hierarchy of the dimension. MdmLevelHierarchy mdmTimeHier = (MdmLevelHierarchy) mdmTimeDim.getDefaultHierarchy(); // Get the Source objects for the level attribute and the hierarchy. Source levelRel = mdmTimeLevelAttr.getSource(); StringSource calendar = (StringSource) mdmTimeHier.getSource(); // Create a Source array containing the TIME levels. Source[] levelSources = new Source[3]; List levels = mdmTimeHier.getLevels(); for (int i = 0; i < levelSources.length; i++) { levelSources[i] = ((MdmLevel) levels.get(i)).getSource(); } // Create a list Source that has the levels as element values. Source levelList = dp.createListSource(levelSources); // Create a Source that selects the elements of a level and has // levelList as an input. Source levelMembers = calendar.join(levelRel, levelList.value()); // Create a range Source that specifies all elements after the first and // before the last. Source range = dp.createRangeSource(2, levelMembers.count().minus(1)); // Remove all level elements except the first and the last. Source firstAndLast = levelMembers.join(levelMembers.position(), range, Source.COMPARISON_RULE_REMOVE, true); // Match the levels to the input of firstAndLast. Source result = firstAndLast.join(levelList); context.commit(); context.displayResult(result); } private void selectingSubsetOfSourceElements(Context10g context, DataProvider dp) { context.println("\nExample 7-8, Selecting a Subset of the Elements of " + "a Source"); // Get the CUSTOMER dimension. MdmPrimaryDimension mdmCustDim = context.getMdmPrimaryDimensionByName("CUSTOMER"); // Get the default hierarchy of the dimension and the parent attribute // of the hierarchy. MdmHierarchy mdmShipRollup = mdmCustDim.getDefaultHierarchy(); // Get the Source for the hierarchy. StringSource shipRollup = (StringSource) mdmShipRollup.getSource(); // Select some elements of the hierarchy. StringSource custSel = (StringSource) shipRollup.selectValues(new String[] {"SHIPMENTS_ROLLUP::SHIP_TO::60", "SHIPMENTS_ROLLUP::SHIP_TO::61", "SHIPMENTS_ROLLUP::SHIP_TO::62", "SHIPMENTS_ROLLUP::SHIP_TO::63"}); // Select a subset of the selection. Source custSel2 = custSel.selectValues(new String[] {"SHIPMENTS_ROLLUP::SHIP_TO::60", "SHIPMENTS_ROLLUP::SHIP_TO::62"}); // Join the selection to itself, using the subset as the comparison. Source result1 = custSel.join(custSel, custSel2, true); // Join the selection to the result of the value method, using the subset // as the comparison. Source result2 = custSel.join(custSel.value(), custSel2, true); // Prepare and commit the Transaction. context.commit(); // Display the results. context.println("\nresult1:"); context.displayResult(result1); context.println("\nresult2:"); context.displayResult(result2); } private void hierarchicalSortingByMeasureValue(Context10g context, DataProvider dp) { context.println("\nExample 7-11, Hierarchical Sorting by Measure Value\n"); // Get the primary dimensions and the measure. MdmPrimaryDimension[] mdmPrimDims = context.getMdmPrimaryDimensionsByName(new String[] {"PRODUCT", "CUSTOMER", "CHANNEL", "TIME"}); MdmMeasure mdmUnits = context.getMdmMeasureByName("UNITS"); // Get the MdmPrimaryDimension and the default MdmLevelHiearchy for the // PRODUCT dimension. MdmPrimaryDimension mdmProdDim = (MdmPrimaryDimension) mdmPrimDims[0]; MdmLevelHierarchy mdmProdRollup = (MdmLevelHierarchy) mdmProdDim.getDefaultHierarchy(); // Get the Source for the default product hierarchy . StringSource prodRollup = (StringSource) mdmProdRollup.getSource(); // Get the Source objects for the measure and for the default hierarchies // of the other dimensions. NumberSource units = (NumberSource) mdmUnits.getSource(); StringSource custHier = (StringSource) mdmPrimDims[1].getDefaultHierarchy().getSource(); StringSource chanHier = (StringSource) mdmPrimDims[2].getDefaultHierarchy().getSource(); StringSource timeHier = (StringSource) mdmPrimDims[3].getDefaultHierarchy().getSource(); // Get the parent attribute of the PRODUCT dimension and the Source for it // to use in the recursiveJoin method. MdmAttribute mdmProdParentAttr = mdmProdRollup.getParentAttribute(); Source prodParent = mdmProdParentAttr.getSource(); // Get the short value description attribute and its Source. MdmAttribute mdmProdShortDescrAttr = mdmProdDim.getShortValueDescriptionAttribute(); Source prodShortDescr = mdmProdShortDescrAttr.getSource(); // Select the Direct Sales channel, the year 2000, and all customers. Source chanSel = chanHier.selectValue("CHANNEL_ROLLUP::CHANNEL::2"); Source timeSel = timeHier.selectValue("CALENDAR::YEAR::4"); // 2001 Source custSel = custHier.selectValue("SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1"); // Sort all products hierarchically in ascending order of units sold, // with parents first. Source sortedProduct = prodRollup.recursiveJoin(units, units.getDataType(), prodParent, Source.COMPARISON_RULE_ASCENDING, true, // Parents first true); // Restrict parents to base // Get the short value description of the products. Source sortedProductShortDescr = prodShortDescr.join(sortedProduct); // Produce a Source that has the units sold measure data as its element // values and sortedProductShortDescr as its output. // Since the other dimensions are all single values, use joinHidden so // the are not outputs of the result. Source result = units.join(sortedProductShortDescr) .joinHidden(custSel) .joinHidden(chanSel) .joinHidden(timeSel); // Prepare and commit the current Transaction. context.commit(); // Create a Cursor and display its values. context.displayResult(result); } public static void main(String[] args) { new MakingQueriesExamples().run(args); } }