The Cube allows you to take your gaming and membership analysis to another level and gives you the flexibility to 'slice and dice' the data however you want. Once the Cube is setup, you can manipulate the data via a drag and drop system to drill into any area of your business that you want to view and produce the exact reports you want.
Creating reports is simple - the elements from the field list in the right hand column can be dragged and dropped into one of the bottom 4 boxes.
In the pivot table field list all Measures are at the top of the list, all Dimensions are below. Dimensions can be Filters, Columns, or Rows while Measures are always Values. You can tell which ones are Values because they have the sum symbol () before each header title as per the image above.
The other difference between Measures and Dimensions is that Dimensions have members and Measures are calculations.
To start, select one or more Dimensions and select one or more Measures. You can play around with where your Dimensions appear, whether they filter the report you're creating or whether they are set out on rows or columns.
For example, if you wanted to show Average Bet by EGM denomination and serial number for a specific club, for October 2013 then your pivot table settings would look like the image below:
There are more detailed explanations on how to use the Cube as well as some templates you can download in the Cube help section located here.
You have two options for updating your data. You can either open your file, click somewhere in the pivot table and then click 'refresh' under the 'Design' tab.
Alternatively you can make sure that every time you open a file that the data is automatically refreshed. To do this open your file, click somewhere in the pivot table and then click on 'options' in the top left:
Once the options box opens, click on 'Data' and then tick the check box to 'Refresh data when opening the file' and click 'OK' to save this preference:
If you want to create a new report using your existing connection:
- Open a new document in Excel
- Click on 'Data' tab
- Then 'Existing Connections'
- Select your connection and click 'Open'
Filtering by dimension values
The below filter demonstrates how to filter the Astute BI Data Cube for 1c EGMs:
a. Select the measure that you would like to filter by & drag it into the FILTERS, COLUMNS or ROWS box in the PivotTable menu
b. Click on the filter icon
c. Select what in the measure you would like included in your report.
Filtering by measures values
There’s a range of flexible Value Filters that can be applied to the Data cube to highlight information such as Top & Bottom entries or entries Greater than a selected threshold.
The below example shows how to select the Top 10 EGMs by Total Daily Turnover:
a. Click the drop down arrow in the header row
b. Select Value Filters
c. Select Top 10
Sorting by Measure Values
The process of Sorting & Filtering by Measure Values is extremely similar. The below example shows how an EGM list can be sorted by Total Daily Turnover:
a. Click the dropdown arrow in the header row
b. Select More Sort Options
c. Select the Measure that you would like to sort by.
Grouping data in a PivotTable is a simple way to make sense of the data that you’re analysing
The below example demonstrates how EGMs can be grouped to shows which gaming area they operate in:
a. Create a PivotTable with the data that you would like to review
b. Select the data that you would like to group (e.g. Serial Number)
c. Click on PIVOTTABLE TOOLS, ANALYZE & Group Selection
d. You can now change the group name to something more meaningful. This is done by selecting the group name, clicking F2 & typing in the required group name.
e. You can ungroup data by selecting the group, going to PIVOTTABLE TOOLS, ANALYZE & Ungroup
Graphs & Charts
You can link Graphs & Charts to a PivotTable & still make changes to the data.
The below 2 examples show different ways to display the same data. Both show Total Daily Turnover for the period of July to September 2013 but in very different ways.
Example 1 shows the Total Daily Turnover Trend by Denomination for the 3rd quarter of 2013. The steps to create this were:
a. Create a Astute BI Data Cube PivotTable with the required Dimensions & Measures.
b. Click on INSERT & select Chart
c. Click on the chart, PIVOTCHART TOOLS, DESIGN & Select Data. Select the area within the PivotTable that you would like displayed in the Chart.
d. Add Chart Elements as required
Example 2 demonstrates which Denominations contributed the most Total Daily Turnover for the 3rd Quarter of 2013 (eg $1.00 EGMs contributed 4.11% of the Total Daily Turnover for the period):
a. Take the PivotTable from the previous Example 1 & move the Calendar Dimension from COLUMNS into FILTERS to group the 3 months together
b. Go to the VALUES section in the PivotTable menu & click the arrow on the Total Daily Turnover Measure. Select Value Field Settings, Show Values As & Select % of Grand Total.
c. Add Chart Elements as required
Conditional Formatting can be applied to a PivotTable to enhance its visual impact. The below PivotTable has had Conditional Formatting applied to it by:
a. Selecting the cells which are to receive Conditional Formatting
b. Select Conditional Formatting from the Home Manu
c. Select Data Bars
d. Select the fill that you would like
How to Sort by Different Headers
If you want to sort the data by different column headings then you will need to copy all the data in your sheet and paste it into a new Excel document or spreadsheet. By copying and pasting the data into another sheet or document, you will lose the pivot table settings and the connection to your data for that particular report so it's best to do this when you've settled on the contents for the report and it's time to manipulate and present it how you want.
An easy way to copy all data in a sheet is by clicking on the triangle at the apex between the columns and rows as per the screenshot below.