1 Customizing With XML Documents

Creating an XML Document for SQL Reports

1. Develop a SQL query that returns the information to make available in Analytic Workspace Manager.

2. Create a text file named awmtree.xml in the directory with the Analytic Workspace Manager executable, typically Oracle_home/olap/awm.

3. Begin the file with an XML declaration like this one:

   <?xml version="1.0" encoding="utf-8"?>
   

   Specify the appropriate encoding for your site.

4. Enter the XML for the template, as described in "Reference: Elements for SQL Reports".

5. For the sql attribute of the <AWMNode> element, enter the SELECT command that you developed in Step 1. You can replace values in a WHERE clause with bind variables from parent <AWMNode> elements, as described in "Using Bind Variables to Restrict the Report Contents".

6. Refresh the navigation tree. The new reports appear at the end of the tree.

   If the new reports do not appear, then look for syntax errors in the XML.

Example of a Simple Report

Figure 1-1 shows a new report named My User Views in the Analytic Workspace Manager navigation tree. The report displays the results of this query: SELECT view_name FROM user_views.

Figure 1-1 Displaying a Report

Description of "Figure 1-1 Displaying a Report"

The XML document in Example 1-1 defines the My User Views report. The document contains two elements: <AWMTree> and <AWMNode>. <AWMTree> is the root element and identifies this document as containing XML that defines a report for Analytic Workspace Manager. The document can have one or more <AWMNode> elements. In this example, a single <AWMNode> element defines the My User Views report.

Among the attributes that the <AWMNode> element can have are name and sql. Example 1-1 uses name to identify the report as My User Views, and sql to specify a SELECT statement that executes when you select the report in the tree.

<?xml version="1.0" encoding="utf-8" ?>
<AWMTree>
   <AWMNode name="My User Views" sql="SELECT view_name FROM user_views"/>
</AWMTree>

Creating Report Folders

Folders provide an easy way to organize your reports. A folder is simply an <AWMNode> element without a SQL statement, and it is the parent of other <AWMNode> elements. You can nest <AWMNode> elements as deeply as you want.

Figure 1-2 shows a folder named My SQL Reports. It contains two reports, My User Tables and My User Views.

Figure 1-2 Organizing Reports in Folders

Description of "Figure 1-2 Organizing Reports in Folders"

The XML document in Example 1-2 shows that the <AWMNode> elements defining the two reports are the children of the <AWMNode> element that defines the My SQL Reports folder.

<?xml version="1.0" encoding="utf-8" ?>
<AWMTree>
   <AWMNode name="My SQL Reports">
      <AWMNode name="My User Tables" sql="SELECT table_name FROM user_tables"/>
      <AWMNode name="My User Views" sql="SELECT view_name FROM user_views"/>
   </AWMNode>
</AWMTree>

Using Bind Variables to Restrict the Report Contents

Bind variables restrict the contents of a report based on your run-time selection from a list of values in the navigation tree. Figure 1-3 shows the list of views returned by the My User Views report, which is now displayed in the navigation tree instead of in the property inspector. The property inspector displays a report for the particular view selected in the tree.

The most deeply nested <AWMNode> element (the "leaf" element) is displayed in a grid in the property inspector and can return multiple columns. The parent elements are displayed in the navigation tree and either return no columns (that is, a folder) or one column, as shown here.

Figure 1-3 Modifying the Content of a Report

Description of "Figure 1-3 Modifying the Content of a Report"

The type parameter of an <AWMNode> element stores the run-time selection. By referencing the name of the type parameter in a nested <AWMNode> element, you pass the value into that SELECT statement. The XML document in Example 1-2 shows an <AWMNode> element nested in the My User Views <AWMNode>. The nested <AWMNode> uses the value of the type element from the parent <AWMNode> element as a bind variable. You reference a bind variable by putting it in braces {}, as in {view} in the example.

<?xml version="1.0" encoding="utf-8" ?>
<AWMTree>
   <AWMNode name="My SQL Reports">
      <AWMNode 
         name="My User Tables" 
         sql="select table_name from user_tables"/>
      <AWMNode 
         name="My User Views" 
         type="view" 
         sql="select view_name from user_views">
         <AWMNode sql="SELECT * FROM user_tab_columns WHERE table_name = {view}"/>
      </AWMNode>
   </AWMNode>  
</AWMTree>

Creating Reports in Object Folders

The awmtree.xml document defines reports that appear in the navigation tree under a database connection, after the Reports folder. You can also define reports that appear in the folders for all schemas, analytic workspaces, cubes, or dimensions. The reports must be in XML documents that have the following names:

• schema.xml, for reports that appear in all schema folders.

• aw.xml, for reports that appear in all analytic workspace folders.

• dimension.xml, for reports that appear in all dimension folders.

• cube.xml, for reports that appear in all cube folders.

These files must be in a JAR file in the plug-in directory.

To create reports in object folders: 

1. Open Analytic Workspace Manager and select Configuration from the Tools menu, as shown in Figure 2-1. The Configuration dialog box appears.

2. Select Enable plugins and enter the path to a plug-in directory, if these parameters are not set already, as shown in Figure 2-2.

3. Click OK, and close Analytic Workspace Manager.

4. Create one or more XML document files.

5. In the plug-in directory, create a JAR file containing the XML files and any icons referenced by them. You can create one JAR file for all of them or create individual JAR files.

6. Open Analytic Workspace Manager and expand the navigation tree to see the reports.

Figure 1-4 shows a report folder named My Tables described in a schema.xml document. It uses a custom icon (red button) in the navigation tree. The My Tables folder appears in the GLOBAL schema folder.

Figure 1-4 Reports in the GLOBAL Schema Folder

Description of "Figure 1-4 Reports in the GLOBAL Schema Folder"

The schema.xml document in Example 1-4 has an <AWMNode> element named My Tables. That element has an icon attribute that specifies a graphics file. In the JAR file that contains the schema.xml file and the button.jpg file, both files are in a directory named plugin11202.

<?xml version="1.0" encoding="US-ASCII" ?>
<AWMTree>
  <AWMNode name="My Tables" 
           icon="plugin11202/button.jpg"
           type="table" 
           sql="select table_name from all_tables where owner = {owner}">
    <AWMNode sql="select column_name, data_type from all_tab_columns where owner = {owner} and table_name = {table}"/>
  </AWMNode>
</AWMTree>

Reference: Elements for SQL Reports

An XML document for SQL reports has the basic format shown in Example 1-5.

<AWMTree>
   <AWMNode>
      <AWMNode>
         <ShowIfQueryTrue>
      <AWMNode>
            .
            .
            .

Creating an XML Document for Calculations

1. Create a custom measure in Analytic Workspace Manager that performs the type of calculation that you want in a template. Use this custom measure to validate the syntax of the expression for the template.

   See Also:

   Analytic Workspace Manager Help for the OLAP expression syntax reference.

2. Create a text file named awmcalcs.xml in the directory with the Analytic Workspace Manager executable, typically ORACLE_HOME/olap/awm.

3. Begin the file with an XML declaration like this one:

   <?xml version="1.0" encoding="utf-8"?>
   

   Specify the appropriate encoding for your site.

4. Enter the XML for the template, as described in "Reference: Elements for Calculations".

5. For the expression attribute of the <Calc> element, cut-and-paste the calculation from the custom measure that you created earlier. Replace the names of the measure, dimension, and so forth with the variables from the ui parameter.

6. Open Analytic Workspace Manager. The new categories and templates appear at the end of the Calculation Type list in the Create Calculated Measure dialog box.

   If the new entries do not appear, then look for syntax errors in the XML. To see changes to the XML document, just reopen the Create Calculated Measure dialog box.

Example of a Simple Calculation Template

Figure 1-5 shows the Calculation Type list in the Create Calculated Measure dialog box. The list contains a new folder named My New Calcs with two additional calculations: Discount and Average.

Figure 1-5 Listing the New Calculations

Description of "Figure 1-5 Listing the New Calculations"

Figure 1-6 shows the template portion of the General tab that appears when a user selects Discount from the tree.

Figure 1-6 Displaying a New Template

Description of "Figure 1-6 Displaying a New Template"

The XML document in Example 1-6 defines the My New Calcs folder and the Discount and the Average calculations. The document contains three elements: <AWMCalcs>, <Category>, and <Calc>. <AWMCalcs> is the root element. It can have one or more <Category> elements. In this example, the <Category> element defines a folder named My New Calcs.

A <Category> element can have one or more <Calc> elements. This document has two <Calc> elements named Discount and Average.

A <Calc> element has four attributes: name, description, ui, and expression. Each attribute takes a quoted string as a value. The ui element consists of literal text and hypertext links. You create the links by entering one of several available parameters.

Example 1-6 uses the {measure} and {number} parameters. You use the same parameters in the expression attribute as bind variables, which pass the user choices to the calculation. Notice that the Average calculation uses two {measure} parameters. The expression attribute refers to them by their order in the ui attribute: {measure:1} and {measure:2}.

Refer to "Reference: Elements for Calculations" for full descriptions of these elements.

<?xml version ="1.0" encoding="UTF-8" ?>
<AWMCalcs>
   <Category name="NEW_CALCS" description="My New Calcs">
      <Calc 
         name="Discount" 
         description="Discount"
         ui="Discount {measure} by {number} percent"
         expression="{measure}*(1 - ({number}/100))"/>  
      <Calc 
         name="Average" 
         description="Average"
         ui="Average of {measure} and {measure}"
         expression="({measure:1}+{measure:2})/2"/> 
   </Category>
</AWMCalcs>

Adding an Option to a Calculation

You can add an option that changes the basic calculation. The option appears as a check box in the Create Calculated Measure dialog box. Users select the option to create the modified calculation.

Two elements support these options: <CalcOptional> and <CalcOptionalDefinitions>. You can define a <CalcOptional> element locally or globally. Within a <Calc> element, <CalcOptional> applies only to that particular calculation. Within a <CalcOptionalDefinitions> element, <CalcOptional> applies to all calculations that reference it by name.

Figure 1-7 shows the sample calculation with an option of truncating the values of the measure to whole numbers. The user has changed the percentage value to 6.

Figure 1-7 Providing an Option to a Calculation

Description of "Figure 1-7 Providing an Option to a Calculation"

Example 1-7 shows the Truncate option defined locally in a <Calc> element. The option applies only to the Discount calculation.

<Calc 
   name="Discount" 
   description="Discount"
   ui="Discount {measure} by {number} percent"
   expression="{measure}*(1 - ({number}/100))">
   <CalcOptional 
      name="truncate"
      type="boolean"
      text="Truncate the decimal places"
      expression="TRUNC($expression$)"/>
</Calc>

Example 1-8 shows the Truncate option defined globally in the <CalcOptionalDefinitions> element. The option is used by the Discount and the Average calculations, and it is available to any other calculations that might be defined.

<AWMCalcs>
   <CalcOptionalDefinitions>
      <CalcOptional
         name="truncate"
         type="boolean"
         text="Truncate the decimal places"
         expression="TRUNC($expression$)" /> 
   </CalcOptionalDefinitions> 
   <Category name="NEW_CALCS" description="My New Calcs">
      <Calc 
         name="Discount" 
         description="Discount"
         ui="Discount {measure} by {number} percent"
         expression="{measure}*(1 - ({number}/100))">
         <CalcOptional name="truncate"/>
      </Calc>
      <Calc
         name="Average" 
         description="Average"
         ui="Average of {measure} and {measure}"
         expression="({measure:1}+{measure:2})/2">  
         <CalcOptional name="truncate"/>
      </Calc>
   </Category>
</AWMCalcs> 

Creating More Complex Calculation Templates

This example creates five calculations in two folders. The calculations in both folders use the global options defined at the beginning of the XML document. Figure 1-8 shows the calculations as they appear in the Calculation Type list.

Figure 1-8 Listing More New Calculations

Description of "Figure 1-8 Listing More New Calculations"

The My Period To Date calculation has the most complex syntax, including three lists. Using the <Params> element, you can create the lists quickly. This is the definition of the first list, which is displayed in Figure 1-9:

<Params>
   <Param type="list" name="timePeriods">
      <Item expression="GREGORIAN YEAR" text="Gregorian year"/>
      <Item expression="GREGORIAN QUARTER" text="Gregorian quarter"/>
      <Item expression="GREGORIAN MONTH" text="Gregorian month"/>
      <Item expression="GREGORIAN WEEK" text="Gregorian week"/>
      <Item expression="ANCESTOR AT LEVEL {level}" text="Ancestor at level"/>
   </Param>
</Params>

Figure 1-9 Choice Lists In a Calculation Template

Description of "Figure 1-9 Choice Lists In a Calculation Template"

Example 1-9 shows the complete XML document that defines the five calculation templates shown in Figure 1-8.

<?xml version ="1.0" encoding="UTF-8" ?>
 
<AWMCalcs>
  <Params>
    <Param type="list" name="timePeriods">
      <Item expression="GREGORIAN YEAR" text="Gregorian year"/>
      <Item expression="GREGORIAN QUARTER" text="Gregorian quarter"/>
      <Item expression="GREGORIAN MONTH" text="Gregorian month"/>
      <Item expression="GREGORIAN WEEK" text="Gregorian week"/>
      <Item expression="ANCESTOR AT LEVEL {level}" text="Ancestor at level"/>
    </Param>
    <Param type="list" name="aggOps">
      <Item expression="SUM" text="sum"/>
      <Item expression="MAX" text="maximum"/>
      <Item expression="MIN" text="minimum"/>
      <Item expression="AVG" text="average"/>
    </Param>
  
  </Params>
  <CalcOptionalDefinitions>
    <CalcOptional 
        name="percentages"
        type="boolean"
        text="Multiply by 100"
        expression="($expression$)*100"/>
    <CalcOptional 
        name="truncate"
        type="boolean"
        text="Truncate the decimal places"
        expression="TRUNC($expression$)" /> 
  </CalcOptionalDefinitions> 
  <Category name="DEMO_CALCS" description="Demo Calcs">
    <Calc
        name="PctDif"
        description="My Percent Difference"
        ui="Percent difference between {measure} and {measure}."
        expression="({measure:1} - {measure:2}) / abs({measure:2})">
      <CalcOptional name="percentages" />
    </Calc>
    <Calc
        name="PriorPeriod"
        description="My Prior Period"
        ui="Prior period for measure {time_measure} in the {time_dimension}
            dimension and {hierarchy} hierarchy {number} period(s) ago."
        expression="LAG({time_measure},{number}) over hierarchy ({hierarchy})" />
    <Calc
        name="Periodtodate"
        description="My Period to Date"
        ui="{timePeriods} to date for {time_measure} in the {time_dimension}
           dimension and {hierarchy} hierarchy. Aggregate over {timePeriods}
           using {aggOps} from the {calcRange} of the period."
        expression="{aggOps}({time_measure}) OVER HIERARCHY ({hierarchy} 
           BETWEEN {calcRange} WITHIN {timePeriods})">
        <Param type="list" name="calcRange">
           <Item expression="UNBOUNDED PRECEDING AND CURRENT MEMBER"
              text="beginning"/>
           <Item expression="CURRENT MEMBER AND UNBOUNDED FOLLOWING"
              text="end"/>
      </Param>
      <CalcOptional name="truncate"/>
    </Calc>
  </Category>
  <Category name="NEW_CALCS" description="My New Calcs">
    <Calc 
        name="Discount" 
        description="Discount"
        ui="Discount {measure} by {number} percent."
        expression="{measure}*(1 - ({number}/100))">
         <CalcOptional name="truncate"/>
    </Calc>
    <Calc 
        name="Average" 
        description="Average"
        ui="Average of {measure} and {measure}"
        expression="({measure:1}+{measure:2})/2">  
      <CalcOptional name="truncate"/>    
    </Calc>
  </Category>
</AWMCalcs> 

Reference: Elements for Calculations

An XML document for calculations has the basic format shown in Example 1-10.

<AWMCalcs>
   <Category>
      <Calc>

Example 1-11 expands on this basic structure to include the definition of options in the calculations.

<AWMCalcs>
   <CalcOptionalDefinitions>
      <CalcOptional>
   <Category>
      <Calc>
         <CalcOptional>

Example 1-12 expands the basic structure to include choice lists in the user interface.

<AWMCalcs>
   <Params>
      <Param>
         <Item>
   <Category>
      <Calc>
         <Param>
            <Item>

Following are the descriptions of the elements.