4.7.4 Example: OLAP Overlay
Walkthrough
In this example, you will create a data cube (OLAP overlay) with two pairs of nested dimensions and two measures. This overlay will be based on the existing Order Model, so first make a working copy of that logical model:
1. Open the Asset panel, and expand the 'Data Source' node.
2. Expand the 'Orders' node, and expand the 'Data Model' node.
3. Right-click the 'Order Model' node in, and select 'Copy' from the menu.

4. Right-click on the 'Data Model' node, and select 'Paste' from the context menu. This prompts you to enter a name.
5. Name the copy 'New Order Model' and click 'OK'.
6. Double-click the 'New Order Model' node to open the copied model.
7. Click the Hierarchy tab at the bottom of the edit panel.
8. Remove the existing hierarchical overlay, so that you can start fresh. To do this, follow the steps below:
a. Select a dimension (cube icon) in the 'Dimensions' panel. Click the 'X' button to delete the entity from the overlay.

b. Repeat the above step to delete all additional dimensions in the 'Dimensions' panel.
c. Repeat the above steps to delete all measures in the 'Measures' panel.
You will now specify dimensions and measures for the new hierarchical overlay. The four dimensions are as follows:
• 'Customer State'
• 'Customer City'
• 'Product Category'
• 'Product SKU'
The first pair forms a hierarchical group, i.e., 'City' represent a finer granularity than 'State'. The second pair also forms a hierarchical group, i.e., 'SKU' represents a finer granularity than 'Category'.
First you will create the dimensional hierarchy representing customer location. This hierarchy will be composed of 'Customer State' and 'Customer City':
9. Click and drag the 'Customer.State' attribute from the model panel to the 'Dimensions' panel. This adds a new dimension called 'State' to the 'Dimensions' panel, automatically placed under a hierarchy labeled 'State'.

10. Click on the 'State' hierarchy node (cube icon) to select it. In the 'Name' field of the 'Properties' panel, enter the name “Customer Location.” (You can also enter a description of this hierarchy, if desired.)
11. Click and drag the 'Customer.City' icon from the model panel to the 'Dimensions' panel. Drop it on top of the 'Customer Location' node.


The 'City' attribute should now appear as a dimension in the 'Customer Location' hierarchy. Make sure the dimension order is correct: Finer-granularity dimensions should appear below the corresponding courser-granularity dimensions; thus, 'City' should appear below 'State'. If the ordering is wrong, move the dimension by using the arrow buttons above the panel.
12. To add the next hierarchy, drag the 'Product.Category' attribute from the model panel to an empty location in the 'Dimensions' panel. This adds a new dimension called 'Category' to the 'Dimensions' panel, automatically placed under a hierarchy labeled 'Category'.
13. Click on the 'Category' hierarchy root node (cube icon) to select it. In the 'Name' field of the 'Properties' panel, enter the name “Product Info.” (You can also enter a description of this hierarchy.)
14. Click and drag the 'Product.SKU' icon from the model panel to the 'Dimensions' panel. Drop it on top of the 'Product Info' node.

This adds the 'SKU' attribute as a dimension in the 'Product Info' hierarchy. Again, make sure the dimension ordering is correct, 'SKU' below 'Category'.
Now you will add two measures. The first measure will be the quantity of product purchased, and the second measure will be the dollar amount of discounts applied. Because this second measure is not available as an existing attribute, you will derive it from other attributes by using an expression.
15. Click and drag the 'Product.Quantity Purchased' attribute from the model panel to the 'Measures' panel. This adds a new measure called 'Quantity Purchased' to the 'Measures' panel.
16. Click to select the 'Quantity Purchased' measure in the to the 'Measures' panel. This opens the 'Properties' panel at bottom.
17. From the 'Aggregate' menu, select the 'MIN' option. This will compute the minimum quantity purchased for each level of the dimension.


18. To define the derived measure, the dollar amount of discounts, click the 'Create Derived Measure' button above the 'Dimensions' tree. This opens the 'Add Expression' dialog box.
19. Enter “Total Discounts” in the 'Expression Name' field. This will be the name of the new measure.

20. Click the 'OK' button in the 'Add Expression' dialog box to open the Script Editor.
You can double-click an attribute names in the 'Fields' tree to add it to the expression.
21. In the Script Editor, enter the following expression to define the new measure representing the total dollar amount of discounts:
field['Product.Total'] * field['Order.Discount']

22. Click the 'Save and Close' button. This adds the new derived measure 'Total Discounts' alongside the other measure.
23. From the 'Aggregate' menu in the 'Properties' panel, select the 'SUM' option. This will compute the summed discount in dollars for each level of dimension granularity.
The hierarchical overlay is now fully specified, and you can use this OLAP model when you design Viewsheets.
See Also
Creating a Crosstab, in Dashboard Design.
| << 4.7.3 Creating Derived Measures | © 1996-2013 InetSoft Technology Corporation (v11.4) | 4.7.5 Design Considerations >> |