The Cube Design screen is where you specify the structure of your cube. Now that you have designed a query to use you need to tell CUBE-it Zero how it should determine each of the columns in the query results.
The Cube Design screen is as follows:
Firstly, you need to specify a Cube Name and Database. Once you have processed the cube the database name specified will have been created in SQL Server Analysis Services and the cube name specified will be in that database.
Next, specify for each column whether it is a Dimension, Time Dimension or a Measure.
Dimensions - things you want to analyse by. Dimensions are usually objects in your data such as Product or Person. They can be hierarchical too, e.g. a Product can have a related Category.
Time Dimension - A special dimension relating to a date field in your query. A cube will then create a time hierarchy including year, quarter, month and day based on your data to use as a dimension in your cube.
Measures - the things you want to analyse. This is the data to analyse, such as Order Quantity or Order Value. And you'll need to know how you want to analyse it, e.g. Sum or Average.
CUBE-it Zero allows you one time dimension and helpfully distinguishes these by placing appropriate columns in the dropdown list rather than in the main list. The dropdown list contains all the datetime columns in your query. If your want to include a time dimension select it from the dropdown list, else leave it as "none".
The other columns in your query are listed on the screen. If you need a reminder of what data the column contains click the column name and a screen of sample column data will be shown:
Use the Distinct checkbox to view a list of distinct values.
Each column can be a dimension, a measure, or not used in the cube. Click the appropriate box to select how the column is to be used.
Dimensions
If you wish to include a dimension hierarchy in your cube click the More button alongside the appropriate column. It is important to chose the correct column to build the hierarchy from. Take for example a hierarchy of Product Category - Product Sub Category - Product. There are a number of Product Categories at the top of the hierarchy, each of which has a number of Product Sub Categories, each of which at the lowest level in the hierarchy has a number of Products. To build this hierarchy select Product as a dimension and click the More button.
On the left are a list of available fields, on the right is where the hierarchy is built. Use the >> and << buttons to select and deselect fields. First you would select Product Sub Category, because the hierarchy is such that each Product "rolls up" into a Sub Category. Similarly, each Product Sub Category "rolls up" into a Product Category, so select Product Category so it is next in the list. If the order is incorrect simply use the Up and Dn (down) buttons to amend the order.
Not Used
At first it may seem a bit odd to include columns in your data source query and then not use them in the cube. But consider the dimension hierarchy above. Now that you have used the Product column to build a hierarchy that includes Product Sub Category and Product Category it is unnecessary to use those columns in the cube. So Product Sub Category and Product Category can be marked as Not Used.
Measures
For each measure you will need to specify how you want to analyse the data. A number of different options are available:
Sum (default) - the total of all the values in the column
Count - the number of values in the column
Minimum - the lowest value in the column
Maximum - the highest value in the column
Distinct Count - the number of distinct (i.e. unique) values in the column
None - no aggregation
By Account
Average Of Children - the average of the values in the column
First Child - the value of the first record of the column
Last Child - the value of the last record of the column
First Non-Empty - the value of the first non-null record of the column (n.b. your query may produce null values in some measure columns)
Last Non-Empty - the value of the last non-null record of the column