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.
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.
5 On Grand Total, shown in Figure 17‑11, use the default values, then choose OK.
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‑13How to create data sets for a multi-dataset cube
In this procedure, you create two data sets:
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.
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH as "EXTENDED_PRICE"where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
n The query retrieves PRODUCTCODE data because it is the key to later link to the PRODUCTLINE dimension.
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.
n The query retrieves PRODUCTLINE data to use for the PRODUCTLINE dimension.
n The query retrieves PRODUCTCODE data because it is the key that the SalesTotals data set will need to reference.How to create a multi-dataset cube
2 On the Dataset page of Cross Tab Cube Builder, supply the following information, as shown in Figure 17‑14:
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
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).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.
Figure 17‑16
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
1 Choose Link Groups. The Link Groups page displays the Productline dimension you created and the primary (fact) data set.
Figure 17‑18 Link Groups page shows how the dimension and fact data sets are linked
8Figure 17‑19 shows a cross tab that uses the year and PRODUCTLINE dimensions and the EXTENDED_PRICE measure from the cube.
Figure 17‑19Figure 17‑20 shows the generated cross tab.
Figure 17‑20
(c) Copyright Actuate Corporation 2009 |