If you haven't... please read An Introduction To Cubes.
Often when you come across "lite" and "full" versions of a product, the "lite" version is just like the "full" version, but with some menu options grayed-out. This is NOT the case here - CUBE-it and CUBE-it LITE are complete different products! CUBE-it LITE makes it very easy to build basic cubes; but CUBE-it gives you the power you need to create enterprise-ready cubes, with features like Shared Dimensions and Calculated Measures.
If you are going to be building cubes with CUBE-it, you'll need to plan things out first - what are your Measures? where are your Dimensions? what hierarchies do you need? what Time Dimensions are you interested in? Before you even touch CUBE-it, you'll be building some Datasets to support your cubes, and thinking about your indexes and keys.
Planning Dimensions
Each Dimension in your cube needs to be supported by a single Dataset; and because Dimensions are shared across cubes (to support improved performance) you'll want to think carefully about how that Dataset is formed, and especially about any hierarchies involved. The following Dataset has been created to support a "Customer" Dimension, with roll-ups to "State" and "Country". To identify that the Dataset is supporting a cube Dimension, it has been named "dimCustomer".
| customer_id | customer_name | state_id | state | country_id | country |
|---|
1
| Alan
| 1
| TX
| 1
| USA
|
2
| Brian
| 2
| MA
| 1
| USA
|
3
| Charlie
| 3
| BC
| 2
| Canada
|
etc...
|
|
|
|
|
|
Note that we've included the label and the ID (key) for each item. (Unlike CUBE-it LITE, it is not necessary for the labels to be unique.) Multi-part keys are supported too, if you need them; but the label field (Customer Name, State, etc.) needs to be a single field, so if you need to combine multiple fields (eg. First Name + Last Name), you'll have to do that in the Dataset.
Planning Cubes - Measures & Time Dimensions
Each cube consists of a series of Measures, linked to your Dimensions by a Foreign Key; so a cube also needs to be supported by a single Dataset containing all of the relevant measures.
This Dataset also needs to include any Time Dimensions which you want to build into your cube, and the inclusion of multiple date fields (such as "Order Date" and "Ship Date") is fully supported. (However, note that your Time Dimension should not include the time! You will need to "round" your source data to the nearest date, with the time set to midnight.)
These Datasets are often known as "Fact Tables" and the Dataset shown below is named "factSale".
| sale_id | order_date | ship_date | customer_id | product_id | quantity | unit_cost |
|---|
1
| 01/01/07
| 01/02/07
| 1
| 1
| 5
| 10
|
2
| 02/02/07
| 02/03/07
| 2
| 2
| 7
| 12
|
3
| 03/03/07
| 03/04/07
| 3
| 1
| 9
| 14
|
etc...
|
|
|
|
|
|
|
Note that we've included:
- A Primary Key (SALE_ID)
- Time Dimensions (ORDER_DATE, SHIP_DATE)
- Foreign Keys, pointing to our "dim" tables (CUSTOMER_ID, PRODUCT_ID)
- Measures (QUANTITY, UNIT_COST)
Creating A CubeSpace
When you start-up CUBE-it, the first thing you need to do is create a CubeSpace.
The Analysis Server will be running SQL Server 2005 Analysis Services; and the Analysis Database will be created if it doesn't exist. A CubeSpace should almost always map directly onto an Analysis Services Database; and it is inadvisable to point two (or more) CubeSpaces at the same database, since you run the very real risk of naming collisions between dimensions and cubes between CubeSpaces.
Creating Time Dimensions
Once we have created our CubeSpace, we can start adding Dimensions. (We usually add the Dimensions first, as they can be shared across multiple cubes.) To create a Time Dimension, you need to click the "Add" button on bottom right of the Dimensions tab.
Select a Dimension Class of "Time Dimension".
You will need to specify the Start and End dates for the Time Dimension - and this means you need to know your data! If a date in your cube lies outside the scope of the Time Dimension, you will get errors when you process your cube; so it can be safest to specify a very wide range.
Specifying the "First Day of Year" allows you to differentiate between "Calendar" and "Financial" (or "Fiscal") Time Dimensions - setting any date other than 01/01 will create a "Financial" Time Dimension. (Although the "Year" will be displayed when you drill back into this Dimension, it is not used, so any year can be specified.)
Creating Standard Dimensions
As we know, Standard Dimensions are based on a single Dataset, so we select "dimCustomer" to add a Dimension.
In the Dimension Detail, we need to specify the "Name Column" - the value displayed in the final cube. Note that only a single Name Column can be specified, so if you want to combine (say) "First Name" and "Last Name" you will need to do this in the underlying Dataset. The "Unknown Member" is a special "bucket" within the dimension to handle Foreign Keys which are missing in the Primary Key table. Its behavior can be Hidden, Visible or None. (The latter not recommended, as "orphaned" data is then ignored, which can cause your calculations to be incorrect!).
Our next job is to specify the Primary Key column(s). For performance reasons, the Primary Key column(s) should be specified as such in your underlying Dataset.
Adding A Hierarchy To A Dimension
When adding a Hierarchy to a Dimension, you begin by specifying the "Name" column - for example, "State" or "Country". Items should be added to the Hierarchy in the order in which they aggregate - so, "State" first, then "Country".
When you first do this, you will see a message indicating "Incomplete Keys". To fix this, drill into the Hierarchy item and set the key(s) - ie. "State ID" for "State" and "Country ID" for "Country". As with the Dimension itself, only a single "Name" column can be specified, so combined fields will have to be handled in the underlying Dataset.
Creating Cubes
Cube are based on a single Dataset, so too add a new cube to your CubeSpace, select the appropriate "fact" table in the CubeSpace Detail screen, and click "Add".
In the Cube Details screen, our first task it to add the Measures. Note that we have added Sales ID (the Primary Key) as a Measure, with a "Count" aggregation, to give us a basic number of sales. The other items use the "Sum" agregation - this is the one you'll most commonly use, because as the users drill-through the data, they are going to want to see (for example) the total quantity sold. Other options available are "Average", "Min" and "Max". (Some of the agregation options are only available in the "Enterprise" edition of Analysis Services - see Microsoft's documentation for further details!)
To add a Calculated Measure, click on the "Calculation Builder" under the "Calculations" tab. For example, we can derive the "Sale Price" by talking the Quantity and multiplying by the Unit Cost. (Drilling back into the Calculation you can see the generated "MDX" expression. MDX is a query language somewhat analogous to SQL.)
Adding Standard Dimensions To Cubes
Adding Dimensions to cubes is easy, because we've already done most of the work in defining the Dimension. To add a Standard Dimension to a cube, select the Dimension and click "Add". Data Academy will try to match the Keys in your Dimension to Foreign Keys in the cube, but if you see the message "Incomplete Keys" you will need to align them manually. Drill into the Cube Dimension (the yellow "edit" icon) to do this. Every Key in the dimension must be matched with the appropriate Foreign Key in the cube's underlying "fact" table.
Adding Time Dimensions To Cubes
When you add a Time Dimension to a cube, you will again see the "Incomplete Keys" warning. Drill into the Cube Dimension (the yellow "edit" icon) and select an appropriate Date-Time field in the underlying "fact" table. As the underlying Dimension is generic, and probably called something like "Calendar Time", you will also probably wish to rename your Cube Dimension - often this will be to the same name as the underlying data field.
Building A CubeSpace
To build a CubeSpace, return to the main CUBE-it screen, and click the "Run" icon.
When Data Academy runs your CubeSpace, it builds and processes it, but note that these are two different things. The build operation is a one-off, that constructs the meta-data which Analysis Services needs for your cube. The processing is the part which actually puts data into your cube, and as such needs to be re-run every time the data changes. A good way of ensuring this is to use a Data Academy Orchestration Package to process your CubeSpace right after your Data Loaders and Data Spaces have been run.
Once you have run your CubeSpace, you should be able to browse your cubes in Management Studio. (You will need to
Connect to the Analysis Server first.)