Writing a query for a complex OLAP cube
In a complex cube, at least one dimension of the schema contains multiple levels. For example, you have flat data that contains three fields:
data:Object = {
product:"ColdFusion"
year :"2006"
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 year field can have the values: 2006 and 2007.
- The quarter field can have the values: Q1, Q2, Q3, and Q4.
In this example, your schema defines two dimensions, with the TimeDim dimension containing levels for year and quarter, as the following code shows:
<mx:OLAPCube name="FlatSchemaCube"
dataProvider="{flatData}"
id="myMXMLCube"
complete="runQuery(event);">
<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="TimeDim">
<mx:OLAPAttribute name="Year" dataField="year"/>
<mx:OLAPAttribute name="Quarter" dataField="quarter"/>
<mx:OLAPHierarchy name="Time-PeriodHier" hasAll="true">
<mx:OLAPLevel attributeName="Year"/>
<mx:OLAPLevel attributeName="Quarter"/>
</mx:OLAPHierarchy>
</mx:OLAPDimension>
<mx:OLAPMeasure name="Revenue"
dataField="revenue"
aggregator="SUM"/>
</mx:OLAPCube>
The cube has the following structure:
ProductDim // Dimension
Product // Hierarchy
(All) // Member
ColdFusion // Member
Flex // Member
Dreamweaver // Member
Illustrator // Member
ProductHier // Hierarchy
(All) // Level
ColdFusion // Member
Flex // Member
Dreamweaver // Member
Illustrator // Member
Product // Level
ColdFusion // Member
Flex // Member
Dreamweaver // Member
Illustrator // Member
TimeDim // Dimension
Year // Hierarchy
(All) // Member
2006 // Member
2007 // Member
Quarter // Hierarchy
(All) // Member
Q1 // Member
Q2 // Member
Q3 // Member
Q4 // Member
Time-PeriodHier // Hierarchy
(All) // Level
2006
Q1
Q2
Q3
Q4
2007
Q1
Q2
Q3
Q4
Year // Level
2006 // Member
2007 // Member
Quarter // Level
Q1 (having 2006 as a parent) // Member
Q1 (having 2007 as a parent) // Member
Q2 (having 2006 as a parent) // Member
Q2 (having 2007 as a parent) // Member
Q3 (having 2006 as a parent) // Member
Q3 (having 2007 as a parent) // Member
Q4 (having 2006 as a parent) // Member
Q4 (having 2007 as a parent) // Member
Notice in this cube:
- The information for the quarter is added as a hierarchy under the TimeDim dimension, and as a level under the Time-PeriodHier hierarchy.
- The Quarter level under the Time-PeriodHier hierarchy contains multiple entries for each quarter. In this example, there is an entry for each quarter for each year.
- The order of the members, meaning the values along each dimension, is based on the order in which the member values appear in the flat data.
The reason for the multiple entries for a single quarter in the Quarter level under the Time-PeriodHier hierarchy is that a cube has to be able to return results for each quarter for each year. For example, this structure lets you write a query to return data aggregations for all quarters for all years, for all quarters for 2006, or for all quarters for 2007.
Since the ProductDim contains a single level, you can write queries for it in the same way as you did for a simple cube. See Writing a query for a simple OLAP cube for more information.
The following table shows the information returned for different ways to access the TimeDim by calling the findAttribute() method:
|
Reference to method and property |
Returns |
|---|---|
| findDimension("TimeDim").findAttribute("Year").children |
2006, 2007 |
| findDimension("TimeDim").findAttribute("Year").members |
(All), 2006, 2007 |
| findDimension("TimeDim").findMember("2006") |
2006 |
| findDimension("TimeDim").findAttribute("Quarter").children |
Q1, Q2, Q3, and Q4 |
| findDimension("TimeDim").findAttribute("Quarter").members |
(All), Q1, Q2, Q3, and Q4 |
| findDimension("TimeDim").findMember("Q2") |
Q2 having 2006 as a parent. This is the first instance of Q2 in the cube. |
The following table shows the information returned for different ways to use the OLAPDimension.findHierarchy(), OLAPHierarchy.findLevel(), and OLAPLevel.findMember() methods:
|
Reference to method and property |
Returns |
|---|---|
| findDimension("TimeDim").findHierarchy("Time-PeriodHier"). findLevel("Year").children |
2006, 2007 |
| findDimension("TimeDim").findHierarchy("Time-PeriodHier"). findLevel("Year").members |
(All), 2006, 2007 |
| IOLAPElement(cube.findDimension("TimeDim"). findHierarchy("Time-PeriodHier").findLevel("Year"). findMember("2006").getItemAt(0)) |
2006 |
| findDimension("QuarterDim").findHierarchy("QuarterHier"). findLevel("Quarter").children |
Q1, Q2, Q3, and Q4 |
| findDimension("QuarterDim").findHierarchy("QuarterHier"). findLevel("Quarter").members |
(All), Q1, Q2, Q3, and Q4 |
| findDimension("TimeDim").findHierarchy("Time- PeriodHier").findLevel("Quarter").findMember("Q1") |
All Q1 members |
| IOLAPElement(cube.findDimension("QuarterDim"). findHierarchy("QuarterHier").findLevel("Quarter"). findMember("Q2").getItemAt(0)) |
Q2 having 2006 as a parent. Cast the result to an instance of IOLAPElement because getItemAt() returns an Object. |
| IOLAPElement(cube.findDimension("QuarterDim"). findHierarchy("QuarterHier").findLevel("Quarter"). findMember("Q2").getItemAt(1)) |
Q2 having 2007 as a parent. Cast the result to an instance of IOLAPElement because getItemAt() returns an Object. |
You could add a Month level to the TimeDim, as the following definition of TimeDim shows:
<mx:OLAPDimension name="TimeDim">
<mx:OLAPAttribute name="Year" dataField="year"/>
<mx:OLAPAttribute name="Quarter" dataField="quarter"/>
<mx:OLAPAttribute name="Month" dataField="month"/>
<mx:OLAPHierarchy name="Time-PeriodHier" hasAll="true">
<mx:OLAPLevel attributeName="Year"/>
<mx:OLAPLevel attributeName="Quarter"/>
<mx:OLAPLevel attributeName="Month"/>
</mx:OLAPHierarchy>
</mx:OLAPDimension>
For this example, the structure of the TimeDim dimension is:
TimeDim // Dimension
Year // Hierarchy
(All) // Member
2006 // Member
2007 // Member
Quarter // Hierarchy
(All) // Member
Q1 // Member
Q2 // Member
Q3 // Member
Q4 // Member
Month // Hierarchy
(All) // Member
Jan // Member
Feb // Member
... // Adiitional members for each month
Time-PeriodHier // Hierarchy
(All) // Level
2006
Q1
Jan
Feb
Mar
...
2007
Q1
Jan
Feb
Mar
...
Year // Level
2006 // Member
2007 // Member
Quarter // Level
Q1 (having 2006 as a parent) // Member
Q1 (having 2007 as a parent) // Member
Q2 (having 2006 as a parent) // Member
Q2 (having 2007 as a parent) // Member
Q3 (having 2006 as a parent) // Member
Q3 (having 2007 as a parent) // Member
Q4 (having 2006 as a parent) // Member
Q4 (having 2007 as a parent) // Member
Month // Level
Jan (having Q1 having 2006 as a parent) // Member
Feb (having Q1 having 2006 as a parent) // Member
Jan (having Q1 having 2007 as a parent) // Member
Feb (having Q1 having 2007 as a parent) // Member
... // Additional members for each possible
// combination of month, quarter,
// and year
Notice in this cube:
- The Month level contains a value for each month, and for each quarter for each year. Therefore, there should be two entries for January; one for Q1 of 2006 and one for Q1 of 2007.