-> Register product. If this is the first time that you use this application, follow the instructions in  Application registration .

Now you will choose the cube to work with, you are presented with four options:

• Server cube : If you will connect to a MS Olap Services or MS Analysis Services cube.
• Local cube: If you will open an offline multidimensional cube in a .cub file.
• View: If you want to open a previously saved view.
• Web Address : If you want to open an offline cube located in a web address.

=> Choose Local cube option and open the file 'gs3.cub' located in the installation directory.

This cube is about a sport distribution company that sales products around the world. It has the following dimensions:

• Measures : This dimension holds the numeric values to be analyzed. These values are:
  • Qty : Number of articles sold 
  • Amount : Sum of the values of these documents 
  • Orders : Number of documents or invoices made 
  • VAT : 15% tax of amount 
  • Profit: = Price - Cost
  • Cost : Product cost
• Class: Products by class (shirts, pants, etc)
• Customers: Customers by geographic region
• Date: Date of invoice by year, year-month, date.
• Months : Year month, handy when crossing year against month.
• Product : Products by supplier.
• Sport: Products by sport (golf, tennis, etc).

2 Application screen

The screen is divided in four sections:

2.1 Title

Title of the cube you work with, normally is the cube name, but you can change it by double clicking on it.

=> Double click on the cube title and change it to: 'Tutorial'.

2.2 Headings

Because in the computer screen you can only look at a 2D data matrix (if you wanted to see a three dimensional view, the numbers will become one on top of another), the cube data will be put in two sections. In this section will appear all the dimensions that are not in the data area.

From these dimensions we can only choose one element. The value of this element appears to the right of the dimension name. These values will be used as a search criteria. If we choose a certain year, we will only see data for this year.

=> Double click in the Measures dimension. It will appear the Dimension options dialog with the Measures dimension on top.

=> Double click on Amount in order to change it for the current value. Note how this value appears on the right side of the dialog.

=>  Press OK to leave this dialog.

Take note how the cube values have changed. Instead of having 6,671 items sold, we have a sales for 2,258,201 in the Class->Total and  Customers->Total cell. You can also see that the element of dimension Measures is Amount instead of Qty .

2.3 Data

In this section, appears sales amount crossed against the dimensions appearing here. In this case the Class and Customers dimensions. To exchange a dimension from the heading to one of the data dimensions, we just click once in the heading dimension value we want to exchange and then another click in a field of a dimension in the data area. We will exchange the field Date->Total for the Customers->Europe field.

=>  Click on the Date dimension in the heading area. See how the mouse cursor changes to a diagonal double pointed arrow.

=>  Click on Customers->Europe field in the data area.

Immediately the Customer and Date dimension get exchanged and appears in different areas. Look how in the heading area appears the Customers->Europe field.

=> Double click in the Customers dimensions in the heading area, double click on Total field to choose it and press OK. See how Customers->Total is shown in the heading area.

Another operations you can do in the data area are:

2.3.1 Pivot

The Date dimension is in the vertical axis of this data area and the Class dimension is in the horizontal one. With the pivot instruction, you can exchange their position.

=> Click on the upper left cell of the data area. Take note how the two dimensions in the data area are exchanged.

Other ways to perform this operation are with menu option Actions -> Swap XY  or the corresponding button in the toolbar.

-> To continue with this tutorial, make yourself sure to position the Customers dimension in the vertical axis of the data area.

2.3.2 Drilldown

The cube dimensions are naturally hierarchical. Not always, but normally all dimensions have a great total field, the first level subtotals, the second level subtotals and so on, until you get to last level of detail that is defined in the cube. The drilldown operation will let you open more level of detail of a dimension.

=> The Customers dimension should be in the vertical axis of the data area, if not exchange Customers->Total with Date->Total and pivot the cube clicking on the left top cell of the data grid. We are looking at the great total of sales (according to the heading area values) and its next level. In this case the world continents.

=> Click on Europe to drilldown on it and see its detail. Automatically, European countries appear.

=>  Click on the country United Kingdom to see its companies.

2.3.3 Adding dimensions

The same way that you exchanged the dimensions, you can add dimensions by pressing the Control key when clicking on the data area.

=> Click on dimension Date on the heading area. Click on the Customers->Total field pressing the Control key at the same time. You will see the Date and Customers dimension in the vertical axis of the data area.

=> Pressing the Control key, click again on field Date->Total to remove it from the data area.

2.4 Chart

In the bottom part of the screen there is a chart showing the data area values.

The format chart can be easily modified. Right click on the chart or particular sections of it. If you want specific help choose it's own menu option Properties.

3. Grid options

In the prior section we gave format to the chart. Now we will see how to format the numeric data.

=>  Choose menu option Edit -> Display options  or click the corresponding icon in the tool bar to go to the display format dialog.

3.1 Format

This dialog will let you modify the cube data format.

= > Click on Data back color and choose a light color. Click OK and see the color change in the dialog's matrix.

You can change the color of several of the grids areas. The data are the values in the matrix, the titles are the cube's dimensions element names. Also, the subtotal areas are applied when the data area in the vertical axis is in hierarchical mode. It can be applied to all dimension levels, except the last one.

-> As an exercise, show the data in blue color with yellow background and the titles in yellow color with blue background.

=> Check the Apply subtotal format box and write the number two on the Level text. This option will apply a different format to the first two subtotal dimension levels in the data area.

= > Apply the same format that was applied to data back color to the subtotal back color. In the font section we will distinguish them.

=>  Choose the Font tab to modify the data fonts. You can modify the data and title font, either in normal or subtotal data.

=> Choose Subtotal font and apply a bold font.

=>  Do the same change to the Subtotal title font.

=> Choose the 'Format' tab and choose '$#,##0' format to apply a currency format. In the Custom text below you can write your own format.

-> The check box Save as default makes the format changes permanent each time you work with this application. Check the box if you want to save your changes as permanent.

=>  Click OK to apply the changes in the cube and come back to this Dimension options dialog again.

3.2 Values

This section is used to format data depending on the cube values. You can apply this conditional formatting to the cube, either from absolute values or by a percentage above or below a formula applied to the data shown. These formulas can be average, median or standard deviation.

=>  Click on the Values tab of the dialog. Choose the average function and a  minimum and maximum percentage of 15.

The back and fore buttons are used to change the display background and foreground colors of values above, below or between the values given. We will paint with a red background all values that are 15% below the average of the values shown.

=> Press the <= minimum Back button and choose a light red button and press OK.

See how the button font turns to bold, this means that this option is selected. You can press it again to disable the conditional formatting.

=> Choose Vertical direction and press OK to exit this dialog.

When you choose a formula for conditional format is necessary to choose its direction. Formulas can be calculated along the horizontal or vertical axis. In our example, horizontal will get and average of (524964.4 + 1091739.4 + 641497.2) / 3 = 752733.67 for the classes at the Customers->Total values. If we choose vertical, the average will be calculated for each member shown of the Class dimension. If you rotate the cube, this option will stay the same unless you choose the Rotate with cube option, in which case this option will be changing with the cube.

4 Dimension options

Once that you know how to format the display, you will choose how to see its values. Most of these options apply only to dimensions that are located in the data area, either vertical or horizontal (although we will only work with the vertical axis).

-> Make sure the Customers dimension is in the vertical axis of the data area.

It's possible to change a dimension view by right clicking on a field in the dimension and choosing menu option Dimension view .

=> Right click on the Customers->Total  field to show the pop menu, then show the Dimension view option to display a list of different views. Choose Hierarchy 2 levels to change the view..

Note how the field Customers->Total is shown with two subtotal levels.

You can choose other ways to look at a the dimension members, like the full hierarchy, or final nodes of a dimension or only show the field you are clicking on (Only me). Also there is Explorer view that is the default view and only my sons to show only its direct members.

When browsing a dimension in hierarchical levels mode, the navigation is a bit different than was shown previously.

=> Click on Customers->Europe to drill down to more detail. See how Customers->Europe becomes the top node and the country companies are shown.

=> Click again on Customers->Europe. Notice how Customers->Total become the top node in the dimension. This operation is known as drill-up.

The Dimension options dialog will let you set more options. This dialog is available by clicking on a dimension name in the data area or via the menu option Edit -> Dimension option  or the corresponding button in the toolbar.

4.1 Elements

Here we will chose which dimension elements we will see and how. By default we will always be in Explorer mode. This will open to show more detail of every member in the dimension. If we click on it again, its detail won't be shown.

There are three ways to look at a dimension: as a hierarchy as a list or as a single value.

A hierarchy can show dimension members from different levels, like continent, country and company from the Customers dimension. This mode is selected with view options explorer, hierarchy N levels and full hierarchy.

A field list is shown when there all dimension members are at the same level, regardless of their place in their hierarchy. This means that you can show Customers->Total, Customers->Americas, Customers->Europe and Customers->any client at the same level. Select field's list, final nodes, only my sons and level's nodes view type.

The third type is when you want to see a single dimension value in the cube. Use one field field's list choosing only one field or only me dimension type view.

We will choose to analyze our data by country.

=> Click on the Customers dimension name on the data area. The dimension options dialog will appear.

=> Choose option Level's nodes of the list called Type. The dimension levels will appear instead than the dimension members. Double click on Country and press OK. See how only countries are shown and which countries are more than 15% below average by Class members, and then return to this dialog.

4.2 Properties

With MS Analysis cubes, you can store dimension properties together with dimension members. For example, if you are looking at a client, you could see it's address, telephone etc. This tab lets you choose which properties to see at each level in the dimension. The level property OlapX%Name is a special property that means the dimension member name.

4.3 Values

When fields are listed in list or single value mode, it's possible to display it together with some formulas or calculated fields. You can display list fields with their total, average, minimum, maximum, number of records, median or standard deviation .

=> Click again on the Customers dimension name on the data area and from the Dimension options dialog go to tab Values and choose the total and average functions. Press OK.

In the data grid you can see the total and average above the countries list. You can see that both are above 15% below average as is expected. Also you can see the average that is used for each class.

These formulas can be shown before or after the list. Also they can be hidden from the graph.

=> Go to menu option Edit -> Cube options or click the appropriate button on the toolbar and choose tab Options.

=> Clear option Display list formulas in chart and press OK.

See how the total and average aren't shown in the legend chart. You can pivot the chart without pivoting the data. Choose menu option Action -> Rotate chart or press the corresponding button in the toolbar.

Now we will see what can be done with a single field.

=> Click on the Customers cell in the data grid.

=> Choose Only me type and double click on  Total to choose it.

=> Choose the values tab and choose all the formulas on the right side and press OK.

=> Go to the grid options dialog, format tab and choose the --- format and press OK. This will choose the default cube format for each field.

=> Press on Class->Shoes to open this field.

=> Pivot the view by pressing the left top cell of the grid view.

This field is shown together with the formulas chosen, this formulas are:

• % Total: Is the % of total for each Class member with respect Customers->Total.
• % Parent: Is the % of total for each Class member with respect to its parent. Since Class->Total have no parent, a #INF is returned. If you look at Class->Molded Cleats, you will see that this fields contributes 11.39% to Class->Total as % Total, while it contributes with the 40.10% to Class->Shoes on % Parent.
• Ranking: This function enumerates the Class dimension by their order according it's value in the cube. So the best Class is Equipment, the second best is Shoes and then Clothing.
• Best: Shows the best final node for the member in Class dimension. For example, SS001: Europa Cup Premium is the best shoe sold according to the heading criteria.
• The rest of the functions are best suited when comparing against dates:
• Increment: Shows the actual value minus the last one.
• Decrement: Shows the actual value minus the next one.
• Difference: Absolute value of the increment.
• Year to date: Shows the value of this field plus all their previous ones.
• Rolling sum: Rolling sum of last n values. The default rolling value is twelve.
• Rolling average: Rolling average of last n values. The default rolling value is twelve.

=> Pivot the cube again by pressing on the top left cell of the data grid.

4.4 Calculated fields

Up to now we have been working with the original cube members. Often we will need to create new values and analyze the cube from their value perspective. For example, we would like to analyze growth from two time periods instead of yearly values. The product offers MDX functionality for creating new fields.

MDX is Microsoft's standard language expression for accessing multidimensional cubes. It's a robust language that will let you create from simple expressions to complex formulas. MDX is outside the scope of this tutorial, but nevertheless we will create a simple formula.

=> Double click on the Date dimension in the heading area to show its dimension option dialog and choose Calculated fields tab.

A list of all the calculated fields for this dimension are shown. With the Add, Edit and Remove buttons you can manage them. It's important to know that this calculated fields are not saved in the cube itself, you must define them either when creating the cube or with MS Analysis Manager. If you want to keep your calculated fields, you must save your work in an view and open it later.

=> Press the Add button to show the calculated fields dialog.

=> In the name textbox, write 98-97 dif.

=> Open the fields tree view and choose Date->1998, then write a - in the text textbox and choose again field Date->1997 . The text box should show: '[Date].[Total].[1998] - [Date].[Total].[1997]'.  Press OK to create this field.

Once the field was added to the list, we will choose it for using it as a heading field.

=> Choose the elements tab and double click in this newly created field. Press OK to exit this dialog.

Now the cube is showing the difference between both years. We will change the cube view, so the changes are more apparent.

=> Pivot the cube clicking on the left top cell of the data grid so the dimension Customers is in the vertical axis of the data grid.

=> Right click on Customers->Total and choose Full hierarchy from the Dimension view menu.

=> Go to the grid options dialog, choose the Values tab, then choose the Value range formula and use values 0 and 0 as minimum and maximum. Press OK button to see your data,

The data is highlighted if its below 0, meaning that its value in 1998 was less than in 1997, according to the criteria shown in the heading area.

5 Cube options

In this part of the tutorial we will look at options that affect the whole cube and what data is displayed. Basically you can display data in different ways like % of total or year to date, etc., ordering and pareto options.

If you right click on a dimension cell in the data area a popup menu will appear, from this menu you can apply several functions against the values shown in the data grid plus some formatting functions. Later on we will use the cube options dialog to create more complex behaviors.

The popup menu options are:

• Properties: Show a cell properties.
• No order: Order values according to the dimension members in the list or hierarchy.
• Ascending order: Orders cube values in ascending order. When showing a hierarchy, the hierarchy levels are respected.
• Descending order: See ascending order explanation above.
• Copy: Copies cube data to the clipboard.
• Freeze: Freezes the rows or columns from the data grid, so they're no longer scrolled. Click in any value to override this option.
• Swap X/Y axis: Pivots the cube.
• Rotate chart: Pivots the chart, without rotating the data grid.
• Dimension view: Let you choose another dimension view as seen before.
• Cube view: Let you apply a formula to the data and see it differently.
• Pareto options: Let you apply a pareto function to the cube data.

For all the options that we will work with it's important to click in the right cell. It's different to sort by Class->Total than by Class->Shoes. In our example, if we click in Class->Total the ordering will be made on it's values and the Customers members will be positioned according their value.

=> Right click on Class->Total and choose Descending order from the popup menu.

See how the Customers members are sorted according their increment in 1998 minus 1997.

-> If you want, change the display format in the Grid options dialog you can do it.

Now we will limit the report data. We would like to look only at customers that brings 80% of sales for the Class->Shoes member. Remember two things: we are sorting by Class->Total member, so the value might not be sorted as you expect.

There are 36 clients on the list with a total sales of 346087.1 difference of sales between 1998 and 1997.

=> Right click on Class->Shoes member and choose menu option Pareto options -> Top %. Answer 80 to specify the top 80%.

In the view you can see only 289248.7 difference value (83%) and 7 customers only.

Now we would like to see this in percentages instead than values.

=> Again right click on Class->Total and choose menu option Cube View -> % Total. If you don't see percentages, change the number format in the grid options dialog.

Now we will look at more detail at the Cube options dialog.

=> Choose menu option Edit -> Cube options or the corresponding button in the toolbar to enter the Cube options dialog.

5.1 Pareto

We apply pareto settings to our cube, bringing only the customers that brought 80% increment in sales between 1998 and 1997.

When using pareto options, we have to following options to get the greatest or the smallest values fields:

• All elements: No pareto options are set.
• Number of elements: Gets a specific number of records.
• Sum of values: Brings all records until their values add to a given number.
• Percentage of values: Bring all records until their values add to a specific percentage of the total values.  

Pareto options can be set either at the horizontal axis or at the vertical axis. In our example we choose vertical axis, since we get the 80% records from the grid vertical axis.

We also can choose against which field compare the values. As we have seen, we can get a different pareto set of values depending which field we compare to. For example, Class->Total and Class->Shoes. Here we can choose compare against another field in the grid or in the cube.

Use in Show fields to compare: Cube Values to use a grid value to obtain the pareto function and All cube fields to choose any value in the cube. If you will compare against a grid value, choose Axis to select which data grids you want to compare to.

For example, choose Date->1997 to obtain the pareto function for the Customers with best 1997 sales (top 80%) and show their difference between 98 and 97.

=> Show All cube fields on Show fields to compare, choose field Date->Total_>1997,

5.2 Order

As with Pareto function, you too can select exactly how to order your information. The Cube options dialog in tab Order is the same as the Pareto tab, except that you can show to order by more than one field.

One thing you have to keep in mind, if you rotate your cube, this cube options are applied to the cube view and not to cube data, this means that your vertical pareto options will be kept as vertical pareto options. If you want this pareto and order options to rotate with the cube, you have to choose the Rotate with cube checkbox in the Cube options dialog.

=> Pivot two times the cube pressing on the left top cell of the data grid.

=> Go to the Cube options dialog and choose the Rotate with cube checkbox and press OK.

=> Pivot again the cube twice.

Notice the difference between the two pivots. If you rotate the cube, the order or pareto options stay in the same dimension.

5.3 Options

If you go to the Cube options dialog and tab Options, you will get a list of distinct options to set. Among them showing or hiding empty fields.

-> Take pareto options off and choose to display empty cells.

5.4 Values

We've been looking at values almost this entire tutorial, you can choose to look at it differently from a set of formulas. This formulas were explained above are % Total, % Parent, etc. You have to set against which field of cube you want set to calculate the formula.

If you choose formula Best, you also have to set the level from the dimension you want to bring the best values. For example, if you are looking at field Customers->Total, you can choose to look at Regions, Countries or Companies.

It's also possible to set the formula pointing to a field that is not showing with this dialog.

6 Printing and exporting of cube

You can always copy the cube values to the clipboard and paste them into other applications.

=>  Open a Windows application that will let you paste data from the clipboard like Notepad or Excel.

=>  Press the 'Control-C' keyboard keys or the menu option 'Edit -> Copy' or the corresponding button from the tool bar. Go to the other application and paste from the clipboard. In this way you can copy your cube values to other applications. Close this application and return to the program.

=>  To print or export the cube, choose the option 'File -> Print (Export) from the menu or the corresponding button from the tool bar and choose the options that you want.

=> Choose the Export function, there will appear a dialog in which you will choose the output file desired. Choose 'Adobe PDF' as file type (or another one) and use 'CubePDF' as file name. Press OK to export it.

-> Open the file just created clicking on it from the Windows start menu: Start -> Documents -> CubePDF or name.

If you sent it to print, the print preview dialog will appear.

=> Open an application where you can paste graphic objects in it like MS Powerpoint.

=> Press the 'Copy' button to copy the actual page to the clipboard and paste it on the previously open application. This way you can create presentations of your work. Close this application and return to the cube browser.

=> Click the button with the magnifying glass to zoom in the print preview page.

=> Click the button with the printer to print your work.

7 Save your work

If you choose the check box 'Save as default' from the format dialogs, all the format that you created is saved inside your PC and will continue to be used. If you wish to save you cube view, (heading dimension and their values, data area dimensions and chart format), you can save your work in an view, which is a local file with .oxv extension.

=> Save your work choosing the menu option 'File -> Save' or the corresponding button from the tool bar and choose 'Tutorial' as a file name. Press OK to save the view.

=> Close your work window clicking on the 'File -> Close' menu option or the close button in the cube window.

=> Open a new cube from the 'File -> Open' menu option or the corresponding tool bar button and choose the 'View' option.

=>  Open the file 'Tutorial.ocv' that you just created and continue working as usual.

8 Conclusion

Here is the end of the this tutorial. We sincerely hope that this tutorial have help you and now you have a more clear idea of its functionality. If you are evaluating this product, we hope this product meet your expectations.