## Lesson 1: Creating a business cube

Business cubes are created from tables, views, formulas and summaries in a data source. In this lesson, we will create a business cube based on data in Data Source 1 of the JinfonetGourmetJava.cat catalog.

This lesson contains the following tasks:

- Task 1: Add cube elements
- Task 2: Set up joins
- Task 3: Define hierarchies
- Task 4: Define built-in filters
- Task 5: Add pre-defined filters

### Task 1: Add cube elements

1. Start JReport Designer.

   The JReport Designer window and the Welcome to JReport Designer dialog appear.

2. In the Welcome to JReport Designer dialog, click the Catalog link in the Open category.
3. Browse to select the JinfonetGourmetJava.cat catalog file in `C:\JReport\Demo\Reports\JinfonetGourmetJava`, then click the Open button. The Catalog Browser displays.

   If JReport Designer was not installed in its default location of `C:\JReport\Designer`, adjust the path accordingly.

4. In the Data tab of the Catalog Browser, right-click the Business Cubes node in Data Source 1 and then click Add Business Cube on the shortcut menu.

5. Enter BusinessCubeDemo in the Input Business Cube Name dialog and click OK.

   The Business Cube Editor displays.

   Our business cube will have three categories: one for dimension objects, one for measures objects and another for detail information objects.

6. Click Insert > Category, then in the Category Property dialog, enter Dimensions in the Display Name text field and click OK.

   The Dimensions category is added to BusinessCubeDemo in the Business Cube panel.

7. Expand the Customers table in the Resources Objects panel, drag the fields Customers_City, Customers_State, Customers_Country, Customers_Territory and Customers_Region from the table and drop them on the Dimensions category.

By default, these fields are converted to detail information objects, but here, we want to use them as dimension objects so that later we can define hierarchies on them. To have more control over the type of cube elements, the Edit Cube Element dialog can be used.

1. Right-click the Customers_City object and choose Edit Detail Information from the shortcut menu.
2. In the Edit Cube Element dialog, change the display name of the object to City, choose Dimension from the Type drop-down list, then click OK.

3. Repeat the above two steps to edit the display name of the objects Customers_State, Customers_Country, Customers_Territory and Customers_Region to State, Country, Territory and Region and change their type to Dimension too.
4. Drag and drop the fields Order Date from the Orders table, Product Name, Category and Product Type Name from the Products table, Employee Position from the Account Managers table onto the Dimensions category.
5. Edit the display name of the objects Product Type Name, Employee Position and Order Date to Product Type, Job Position and Sales Date, then change their type to Dimension as explained above.
6. Change the type of the Product Name and Category objects to Dimension as well.

Besides dragging and dropping fields from tables onto category, the other way to add cube elements is by using the Add Cube Element dialog. Next, we will add more elements to the Dimension category via dialog.

1. Right-click the Dimension category and select Add Cube Element from the shortcut menu.

   The Add Cube Element dialog appears. The default cube element type is Dimension chosen from the Type drop-down list.

2. Click the chooser button next to the Mapping Name field, then double-click Name in the Account Managers table in the Cube Element Resources dialog.

3. The display name of the dimension object is then automatically set as Name. Change it to Employee Name, then click OK in the Add Cube Element dialog to add the element.
4. Repeat Step 14 to 16 to add the dimension objects Sales Year, Sales Quarter, Sales Month and Sales Day from the formulas YearOfOrderDate, QuarterOfOrderDate, MonthOfOrderDate, and DayOfOrderDate.

Next, we will create another category to hold the detail information objects we need.

1. Select the BusinessCubeDemo node in the Business Cube panel, click Insert > Category.
2. In the Category Property dialog, enter Detail Information in the Display Name text field and click OK.
3. Drag and drop Customer Name, Annual Sales, Address1, Customers_Phone and Customers_Fax from the Customers table onto the Detail Information category.
4. Right-click the Address1 object, select Rename from the shortcut menu to rename it as Customer Address.
5. Rename the Customers_Phone and Customers_Fax objects to Phone and Fax.
6. Expand the Orders table, drag and drop the fields Order Date, Required Date, Ship Date, Ship Via, Shipped and Payment Received onto the Detail Information category.
7. Drag and drop the field Products_Product ID from the Products table onto the Detail Information category.

The Products_Product ID field is added as dimension object by default, so we need to change its type to detail information.

1. Right-click the Products_Product ID object and select Edit Dimension from the shortcut menu.
2. In the Edit Cube Element dialog, edit the display name of the object to Product ID, change its type to Detail Information and click OK.
3. Expand the Formulas node, drag and drop CustomerCityStateZip and Total to the Detail Information category.
4. Rename the Total object to LineItemTotal.

Now that we have the detail elements exposed in our business cube, we need to add some related measures.

1. Select the BusinessCubeDemo node in the Business Cube panel, click Insert > Category, then in the Category Property dialog, enter Measures in the Display Name text field and click OK.
2. Right-click the Measures category and choose Add Cube Element.
3. In the Add Cube Element dialog, select Measure from the Type drop-down list, select the field Quantity from the Orders Detail table as the mapping field, choose Sum from the Aggregate drop-down list, edit the display name as Total Quantity, then click OK to add the element.

4. Repeat Step 31 to 32 to add the following measure objects:
   - Display name: Total Discount
     Mapping field: Discount in the Orders Detail table
     Aggregate function: Sum
   - Display name: Total Sales
     Mapping field: the Total formula
     Aggregate function: Sum
   - Display name: Total Shipping Cost
     Mapping field: Shipping Cost in the Orders table
     Aggregate function: Sum
   - Display name: Average Price
     Mapping field: Unit Price in the Orders Detail table
     Aggregate function: Average

By now, the business cube is created and it consists of objects from tables and formulas, while the fields in the tables are independent of each other, so our next task is to set up some relationships between different tables.

### Task 2: Set up joins

A business cube is based on tables (including views). By default, there is no relationship among the tables. In order to make them correlated, you need to define joins between them. In this task, we will build several joins between the tables we have added to the business cube.

1. In the Business Cube Editor, choose Tools > Join Editor to open the Business Cube - Join Editor window.

All the tables that are added to the business cube are listed in the join panel. First, we will create a join between the Account Managers and Orders tables based on manager IDs.

1. Resize the two tables to make all column names in them fully displayed.
2. Scroll down the Orders table to make the AccountManagerID_FK1 column shown.
3. Position the mouse pointer over the column Account Manager ID in the Account Managers table, click and hold the left mouse button, move the mouse pointer to the AccountManagerID_FK1 column in the Orders table, then release the mouse button.

   The join relationship is then established between the two tables, and a join named Join1 is automatically added to the All Joins panel in the Join Editor.

4. Repeat the above steps to set up the following joins:
   - Join2, which joins the Customers and Orders tables based on customer IDs
   - Join3, which joins the Orders and Orders Detail tables based on order IDs
   - Join4, which joins the Products and Orders Detail tables based on product IDs

   After done, we will have four joins between tables in the business cube as follows:

### Task 3: Define hierarchies

In this task, we define some hierarchies in the business cube. Hierarchies support drilling in the ad hoc report, that is, allowing measures to be calculated based on different levels.

1. In the Business Cube Editor window, choose Tools > Hierarchy Management.
2. In the Hierarchy Editor, click <Add Hierarchy...> in the Hierarchy list, then click the Rename button to name the added hierarchy as Geography.

3. Select the Geography hierarchy and add the dimensions Region, Territory, State and City to it one by one.
4. Add a new hierarchy named Products, then add the dimensions Product Type, Category and Product Name one by one to the hierarchy.
5. Create another hierarchy named Time, and add the dimensions Sales Year, Sales Quarter, Sales Month and Sales Date one by one to it.
6. Add one more hierarchy named Employee, then add the dimensions Job Position and Employee Name one by one to the hierarchy.

   Now, four hierarchies have been defined in the business cube.

7. Click OK in the dialog to save the hierarchies and close the editor.

### Task 4: Define built-in filters

In this task, we add a built-in filter to the business cube so as to discard some unwanted data.

1. Click the Filter button on the toolbar of the Business Cube Editor, and the Filter dialog displays.

We only want the data of employees who are sales representatives or marketing staff.

1. In the Filter dialog, click the Add Condition button to add a filter line.
2. Click next to the field text box, then in the Cube Element Resources dialog, expand BusinessCubeDemo and then Account Managers, select Employee Position and click OK to return to the Filter dialog.

3. Select = as the operator.
4. Click next to the value text box.
5. In the Values dialog, select Employee Position in the Fields tab, then switch to the Value tab and double-click Sales Representative.

6. Close the Values dialog to return to the Filter dialog again.
7. Click the Add Condition button to add another filter line and specify the relationship between the two lines as OR from the logic drop-down list.
8. Set the condition of the newly added filter to be Employee Position = Marketing as explained above.
9. Click OK in the Filter dialog to save the built-in filter settings.

### Task 5: Add predefined filters

Built-in filter takes effect anytime when the business cube is adopted. However, predefined filter can be applied or not at runtime.

1. Click the Predefined Filter button on the toolbar of the Business Cube Editor, and the Predefined Filter dialog displays.

We can add one or more predefined filters to the business cube. Here we will add two filters; one focuses on the sales information on the country USA in the year 2006 and the other discards data of products which is in the Seasonal category.

1. In the Predefined Filter dialog, click the New button to add a filter.
2. Double-click the filter name in the Name cell to rename it to USA2006, and provide a short description to the filter: Sales of 2006 in USA.
3. In the Condition panel, set the condition of the filter as Country = USA AND Sales Year = 2006.

   The Condition panel is similar to the Filter dialog. We can set conditions to predefined filters as we do to built-in filters.

4. Click the New button again to add a new filter.
5. Name the filter NotMild and give a short description to the filter: Product category is not Mild.
6. Set the condition of the filter to be Category != Mild.

7. Click OK in the Predefined Filter dialog to save the predefined filter settings.
8. Click File > Save in the Business Cube Editor window to save the business cube.

### Lesson 1 summary

In this lesson, we created a business cube and added some cube elements in it, including categories, dimension objects, measure objects and detail information objects. Then, we set some hierarchies on the measure objects so as to enable drilling in ad hoc report. At last, we defined some filters on the business cube to narrow down the data scope.