Previous Next

Task 6: 

Each number that is displayed in the cross tab represents the sales total of a particular product for a particular state. In this procedure, you add grand totals to display the total sales of all products for each state, the total sales of each product, and the total of all sales across products and states.

1
2
Select the cross tab by clicking on the tab in the lower left corner of the cross tab. Make sure you select the entire cross tab, not just a part of it.
3
4
5
On Grand Total, shown in Figure 17‑11, use the default values, then choose OK.

Figure 17-14 Creating grand totals in the cross-tab row area

Figure 17‑11
A new row is added to the bottom of the cross tab, as shown in Figure 17‑12. In this row is a label that displays Grand Total and a data element that displays [EXTENDED_PRICE].

Figure 17-15 Cross tab including a new row to display grand totals

Figure 17‑12
6
7
8
A new column is added to the cross tab, as shown in Figure 17‑13.

Figure 17-16 Cross tab with a new column to display grand totals

Figure 17‑13
9

How to create data sets for a multi-dataset cube

In this procedure, you create two data sets:

n
n

Note that we are not creating a separate data set for the year dimension, as is typical in a star schema. It is sometimes too complicated to create a pure star schema design when working with data stored in an OLTP system.

1
select CLASSICMODELS.ORDERDETAILS.PRODUCTCODE,
CLASSICMODELS.ORDERS.SHIPPEDDATE,
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH as "EXTENDED_PRICE"
 
from CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
and CLASSICMODELS.ORDERS.STATUS = 'Shipped'
n
n
n
The query creates a calculated column, EXTENDED_PRICE, whose values will be aggregated to calculate the sales totals.
n
The query creates a join between the Orders and OrderDetails tables to get all the necessary data about the orders. Because the data set is retrieving data from an OLTP database, joins are unavoidable.
n
The query contains a filter condition to retrieve order data for orders that have been shipped, and therefore, that have been paid.
2
select CLASSICMODELS.PRODUCTS.PRODUCTLINE,
CLASSICMODELS.PRODUCTS.PRODUCTCODE
from CLASSICMODELS.PRODUCTS
n
n

How to create a multi-dataset cube

1
2
1
2
In Primary dataset, select SalesTotal. In a multi-dataset cube, the fact data set that retrieves the data to calculate measures is the primary data set.

Figure 17‑14
3
Choose Groups and Summaries to define the dimensions and measures for the cube. The Groups and Summaries page, shown in Figure 17‑15, displays the available data sets and fields.

Figure 17‑15
4
1
Under the Sales Totals (Primary) data set, drag SHIPPEDDATE and drop it under Groups (Dimensions) in the drop location that displays (Drop a field here to create a group).
2
Group Level displays the different ways to group the dates. To display the dates as they appear in the data source, select Regular Group. To group the dates by any of the time periods, select Date Group.
3

Figure 17‑16
4
5
1
Under the Productlines data set, drag PRODUCTLINE and drop it under Groups (Dimensions) in the drop location that displays (Drop a field here to create a group). On Add Group, use the default group name.
2
Under the Productlines data set, drag PRODUCTCODE and drop it on the PRODUCTLINE dimension. This action creates a hierarchical relationship between PRODUCTLINE and PRODUCTCODE.
6
Define the cube’s measure. Under the Sales Totals (Primary) data set, drag EXTENDED_PRICE and drop it under Summary Fields (Measures) in the drop location that displays (Drop a field here to create a summary field).
The Groups and Summaries page, shown in Figure 17‑17, displays the dimensions and measure that you define.

Figure 17‑17
7
1
Choose Link Groups. The Link Groups page displays the Productline dimension you created and the primary (fact) data set.
2

Figure 17‑18
8

Figure 17‑19 shows a cross tab that uses the year and PRODUCTLINE dimensions and the EXTENDED_PRICE measure from the cube.

Figure 17‑19

Figure 17‑20 shows the generated cross tab.

Figure 17‑20

(c) Copyright Actuate Corporation 2009