The Data-cube: powerful data analysis

Orixa includes a visual component called a "data cube." These are sometimes know as pivot-tables or decision-cubes in other software programmes. Scripts can be written to return data from the database and display it in a data cube, housed within a "Dashboard" page, which can include multiple elements. Once displayed, the data cube allows users to make complex analysis of the data without needing to do any further programming.

Data-cubes have built-in functionality to show their data in chart-format, which can be extremely powerful, they can also be exported directly to tools such as Excel.

What does a data-cube do?

Data-cubes take a table of data returned from the database and allow it to be grouped in multiple "dimensions, so that totals are visible. A table showing a list of customers and the value of their sales could be grouped to show the total sales by type of customer shown vertically, with sales per month shown horizontally for example. In this way comparison of sales for each month by customer type is immediately easy and intuative.

Data-cubes are complex and powerful. Users who have used Excel may be familiar with their features.

Data Cube

Features of the data-cube window

The toolbar includes buttons

Refresh Data: This just fetches fresh data from the database, incase any records have been updated or changed since it was last opened.
Toggle Cube / Chart: This is explained below.
Clear cube: This resets the data-cube so that no data is organised. The raw data is still present, so it can be used.
Show Grid: This shows a grid-view of the data that is being presented in the data-cube.
Export cube: This allows the data-cube to be exported in Excel or Webpage format so other programs can view the data.
Format Cells: This shows an Options dialog box which allows complex customization of the cells in the data-cube, including bold, colored and other specialized displays.

Displaying chart data in a Data-cube

Data Cube Chart: Click "Toggle Cube / Chart" (1) to switch between the two data-views

Basic Use of data-cubes: Selecting Rows, columns, grouping and filtering

The best way to learn how to use data-cubes is simply to start playing with them. Remember that until you press the "Save" button everything you have done is temporary, so it is easy to undo.

Data Cube Set up rows and columns

Starting from an empty data-cube (which has data) Click on the "Field List" and click-drag items from it to the Columns (1.) Rows (2.) and Body (3.) of the data-cube.

Data will then be grouped. You will see the distinct entries in the data in each row and column.

It is best to select data with only a few distinct values to use for columns otherwise very little of the data will be visible unless the user scrolls across or down.

The field(s) which have been dragged into the Body (3.) will be Summed by default. These should be number fields so that it is possible for sums to be computed.

Important: Once a field has been dragged into the Body (3.) the "Measures" area will indicate "1". If you want to actually see data in the data-cube, drag the "Measures" area so that it is in either a Row or a Column area.

Data Cube Filters

Live Filtering of data (1)

Any field can be dragged from the field-list into the "filter area". Once this is done the data from these columns can be filtered to add and remove data from visibility.

Data Cube: Using Filters

Using Live Filtering

All fields include live-filter drop-downs, these are present for Rows, Columns and fields dropped in the Filter region of the data-cube.

These features closely mimick features of data-cubes in Excel, making them easier for users who are familiar with Excel to learn.

Click on the "filter" down arrow at the edge of the field.
Tick or untick individual entries in the list to hide / show them.
Buttons at the bottom of the window allow you to show all / hide all and swap the records shown / hiden.

Customizing a data-cube

Data Cube Format Cells

The "Format Cells" dialog box

When the user clicks the "Format Cells" button in the toolbar, the window shown will open.

The features of this window are quite complex. Full details of all aspects of its operation are covered in the Fast Report FastCube help document.

Tabs are present to control all aspects of the display of data in the cube, including the types of summary (sum,average, count), specialized marking for data (ie bold font for certain ranges etc.)
In each tab there is a set of options which the user can edit.
Once they have completed editing, click "Apply" to save

Data Cube Chart Options

Customization options for charts

When a "cube-chart" is visible the toolbar will switch so that different buttons are visible.

Chart type button: This changes the chart-format being shown between the choices listed.
Chart Options: This allows selection of mior options such as visibility of different elements in the chart, but also gives access to "Axis Options" shown below.

Data Cube Chart Axis Options

The "Axis Options" Window

This window allows customization of the cube-chart, switching the columns and rows used for charting, and setting the dimensions and depth of charting.

Fuller details of how to use it can be found in the Fast Report Help Documentation.

Cube, double-click to "drill down" into items.

If an item in a Cube sums records double click to view linked records

Cubes summarize data. Individual cells in a cube may contain multiple records.

When a user double-clicks on any cell, a grid-form will open showing all the records summed in that cell. This grid will contain all the fields present in dataset returned by the SQL for the cube.

If users regularly want to "drill down" in this way it can be useful to add extra fields to the dataset of the cube to allow them to view this data here.