When working with large amounts of data, you can quickly get overwhelmed with the scope and size of the data. For example, you collect sales information for different products, in different regions, and for different customers in a typical two-dimensional spreadsheet. That spreadsheet could easily contain hundreds of rows and tens or even hundreds of columns. Extracting useful information for such a large data collection can be difficult, and trying to identify trends or other patterns in the data can be even harder.

Data visualization is a technique for examining large amounts of data in a compact format. One type of data visualization technique is to use a chart, such as a bar, column, or pie chart. Adobe® Flex® supports many types of charts. For more information, see Introduction to Charts.

Another data visualization technique is to aggregate the data in a compact format, such as in an OLAP (online analytical processing) data grid. An OLAP data grid is similar to a pivot table in Microsoft Excel. An OLAP data grid displays data aggregations in a two-dimensional grid of rows and columns, like a spreadsheet, but the data is condensed based on your aggregation settings.

For example, you collect sales information on a server in a flat data structure of records, where each record contains information for a single customer transaction, for a single product, in a single quarter. The following code shows the format of this flat data:

data:Object = {
    customer:"AAA", 
    product:"ColdFusion",
    year:"2007",
    quarter:"Q1"
    revenue: "100.00" 
}

For a large company with hundreds of customers and tens or hundreds of products, this table could easily contain several thousand rows. Rather than display this information in a standard spreadsheet, you download your data to a Flex application to aggregate sales data by product and quarter, and then display the aggregated data in an OLAP data grid. From this data aggregation, you can determine trends in sales of each product over time.

About the OLAPDataGrid control

You use the Flex OLAPDataGrid control to display an OLAP data grid. The following image shows the OLAPDataGrid control displaying the aggregated sales information for product and quarter:

Like all Flex data grid controls, the OLAPDataGrid control is designed to display data in a two-dimensional representation of rows and columns.

You can modify this example to compare quarterly sales from two different years, as the following example shows:

In the previous figure, the columns of the OLAPDataGrid control show a hierarchy of information for year and quarter. You can add multiple-level hierarchies for both the columns and the rows of the control.

About creating an OLAP data grid

The following figure shows the data flow that you use to aggregate your data for display in the OLAPDataGrid control:

The following steps describe this process in more detail:

1. Start with flat data, which is typically data arranged as a set of records where each record contains the same data fields. For example, you might start with flat data from a spreadsheet, or with data from a table of a relational database.

   The following code shows an example format of flat data:

   data:Object = {
       customer:"AAA", 
       product:"ColdFusion",
       quarter:"Q1"
       revenue: "100.00" 
   }
   
   

2. Define an OLAP schema that describes how your data gets transformed from a flat representation into an OLAP cube. An OLAP schema defines the representation of your flat data in an OLAP cube, and defines how to aggregate your data for an OLAP query.

   An OLAP cube is analogous to a table in a relational database. But whereas a table typically has two dimensions (row and column), an OLAP cube can have any number of dimensions. In this example, the cube has three dimensions: customer, product, and quarter. Every possible set of values for customer, product, and quarter defines a unique point in the cube. The value at each point in the cube is the sales revenue for that set of values of customer, product, and quarter.

3. Create queries to extract aggregated data from the OLAP cube for display in the OLAPDataGrid control.

   After your data is in an OLAP cube, you write queries to extract aggregated data for display in the OLAPDataGrid control. You can write multiple queries to create different types of data aggregations.

4. Use the query results as input to the OLAPDataGrid control to display the results.

About OLAP cubes

An OLAP cube can have any number of dimensions. In its simplest form, the dimensions of an OLAP cube correspond to a field of the flat data set. For example, you have flat data that contains three fields:

data:Object = {
    product:"ColdFusion"
    quarter :"Q1"
    revenue: "100.00", 
}

The data fields of each record can contain the following values:

• The product field can have the values: ColdFusion, Flex, Dreamweaver, and Illustrator.
• The quarter field can have the values: Q1, Q2, Q3, and Q4.
• The revenue field contains the sales, in dollars, of the product for the quarter.

To aggregate your data, you create an OLAP cube with two dimensions: quarter and product. The value along each dimension of the cube is called a member. For example, the product dimension of the cube has the following members: ColdFusion, Flex, Dreamweaver, and Illustrator. For the quarter dimension, the members are Q1, Q2, Q3, and Q4.

The value at any point in the cube defined by the two dimensions is called a measure of the cube. For example, the measure at the point in the cube defined by (Q1, ColdFusion) is 100.00. A schema can define one or more measures for a single point in the OLAP cube.

Flex supports only numeric values for the measure of a cube. The advantage of numeric values is that they can be easily aggregated for display in the OLAPDataGrid control. Some typical aggregation types include sum, average, minimum, and maximum. For example, you specify the aggregation method of the revenue measure as SUM. You then extract sales information from the cube for ColdFusion. The aggregated sales data contains the sum of all ColdFusion sales for each quarter.

About OLAP schemas

To convert flat data into an OLAP cube, you create an OLAP schema that defines the dimensions of the cube, the fields of the flat data that supply the members along each dimension, and the fields of the flat data that supply the measure for any point in the cube.

For example, you have the following flat data that contains sales records:

data:Object = {
    customer:"AAA", 
    product:"ColdFusion",
    quarter:"Q1"
    revenue: "100.00" 
}

The following example shows the definition for an OLAPCube that includes the definition of the OLAP schema used to represent this data in the cube. This schema defines a three-dimensional OLAP cube based on the customer, product, and quarter fields of the data.

<mx:OLAPCube name="FlatSchemaCube" 
    dataProvider="{flatData}" 
    id="myMXMLCube"
    complete="runQuery(event);">
     
    <mx:OLAPDimension name="CustomerDim">
        <mx:OLAPAttribute name="Customer" dataField="customer"/>
        <mx:OLAPHierarchy name="CustomerHier" hasAll="true">
            <mx:OLAPLevel attributeName="Customer"/>
        </mx:OLAPHierarchy>
    </mx:OLAPDimension>
    
    <mx:OLAPDimension name="ProductDim">
        <mx:OLAPAttribute name="Product" dataField="product"/>
        <mx:OLAPHierarchy name="ProductHier" hasAll="true">
            <mx:OLAPLevel attributeName="Product"/>
        </mx:OLAPHierarchy>
    </mx:OLAPDimension>

    <mx:OLAPDimension name="QuarterDim">
        <mx:OLAPAttribute name="Quarter" dataField="quarter"/>
        <mx:OLAPHierarchy name="QuarterHier" hasAll="true">
            <mx:OLAPLevel attributeName="Quarter"/>
        </mx:OLAPHierarchy> 
    </mx:OLAPDimension>
    
    <mx:OLAPMeasure name="Revenue" 
        dataField="revenue" 
        aggregator="SUM"/>
</mx:OLAPCube>

Notice that in this schema:

• All dimensions are defined first, and then all measures.
• The first line of each dimension associates a data field of the flat data with an OLAPAttribute instance. You then use the OLAPLevel.attributeName property to associate the attribute with a level of the dimension to populate the members of the dimension. For example, in this schema you populate the Customer level of the CustomerDim dimension with the data from the customer field of the data.
• A dimension of an OLAP schema always contains a hierarchy of one or more levels. In this schema, the hierarchy of each dimension contains only a single level corresponding to a field of the flat data. This is the simplest form of a dimension. Other schemas could define multiple levels in a hierarchy to create a complex dimension. For more information, see Creating an OLAP schema.
• A measure definition specifies the data field of the flat data that contains the value for each point in the OLAP cube, and how the measure is aggregated by an OLAP query. In this example, you aggregate revenue by summing it. That means all queries of this OLAP cube will return revenue summations. Other types of aggregation methods include maximum, minimum, and average.
• This definition of the OLAPCube specifies an event handler for the complete event. You cannot invoke a query on the cube until it completes initialization, which is signalled by the cube when it dispatches the complete event.

Based on the requirements of your application, you might create multiple OLAP cubes from the same flat data set, where each cube uses a different schema to create its own arrangement of dimensions and measures. For more information and examples of OLAP schemas, see Creating an OLAP schema.

About OLAP queries

OLAP queries extract aggregated data from an OLAP cube for display in an OLAPDataGrid control. The query specifies the dimensions that define the characteristics of the query, and the measure or measures aggregated to create the query results.

In the previous section, you defined an OLAP schema for sales information where the schema defines CustomerDim, ProductDim, and QuarterDim dimensions, and a single measure for revenue aggregated by the SUM aggregation method. Therefore, you can create a query to sum revenue by the following criteria:

• Product for each quarter
• Customer for each product
• Customer for each quarter
• Any other combination of members from each dimension

You construct a query in ActionScript as an instance of the OLAPQuery class, and then execute the query by calling the OLAPCube.execute() method, which returns an instance of the AsyncToken class.

A query is required to have two axes, a row axis and a column axis, of type IOLAPQueryAxis. The row axis defines the data aggregation information for each row of the OLAPDataGrid control, and the column axis defines the data aggregation information for each column of the control.

You use the OLAPSet class to specify the data aggregation information for each axis, as the following example shows:

// Create an instance of OLAPQuery to represent the query. 
var query:OLAPQuery = new OLAPQuery;
            
// Get the row axis from the query instance.
var rowQueryAxis:IOLAPQueryAxis = query.getAxis(OLAPQuery.ROW_AXIS);
// Create an OLAPSet instance to configure the axis.
var productSet:OLAPSet = new OLAPSet;
// Add the Product to the row to aggregate data 
// by the Product dimension.
productSet.addElements(
    cube.findDimension("ProductDim").findAttribute("Product").children);
// Add the OLAPSet instance to the axis.
rowQueryAxis.addSet(productSet);
            
// Get the column axis from the query instance, and configure it
// to aggregate the columns by the Quarter dimension. 
var colQueryAxis:IOLAPQueryAxis = query.getAxis(OLAPQuery.COLUMN_AXIS);         
var quarterSet:OLAPSet= new OLAPSet;
quarterSet.addElements(
    cube.findDimension("QuarterDim").findAttribute("Quarter").children);
colQueryAxis.addSet(quarterSet);

// Execute the query.
var token:AsyncToken = cube.execute(query);
// Set up handlers for the query results.
token.addResponder(new AsyncResponder(showResult, showFault));

Notice that in this query:

• You initialize each axis by calling the OLAPQuery.getAxis() method.
• You configure each OLAPSet instance by calling the OLAPSet.addElements() method to specify the information used to populate the axis.
• You set up two functions to handle the query result defined by the AsyncToken class. In this example, the function showResult() handles the query results when the query succeeds, and the function showFault() handles any errors detected during query execution. For more information on using the AsyncToken class, see Executing a query and returning the results to an OLAPDataGrid control.

For more information and examples of OLAP queries, see Creating OLAP queries.

The differences between the OLAPDataGrid and the AdvancedDataGrid control

You use the OLAPDataGrid control to display the results of an OLAP query. The OLAPDataGrid control is a subclass of the AdvancedDataGrid control and inherits much of its functionality. However, because of the way you pass data to the OLAPDataGrid control, it has several differences from the AdvancedDataGrid control:

• Column dragging is not allowed in the OLAPDataGrid control.
• You cannot edit cells in the OLAPDataGrid control because cell data is a result of a query and does not correspond to a single data value in the OLAP cube.
• You cannot sort columns by clicking on headers in the OLAPDataGrid control. Sorting is supported at the dimension level so that you can change the order of members of that dimension.

You populate an OLAPDataGrid control with data by setting its data provider to an instance of the OLAPResult class, which contains the results of an OLAP query. For a complete example that uses this control, see Example using the OLAPDataGrid control.