CUBE-it LITE

If you haven't... please read An Introduction To Cubes.

CUBE-it LITE is probably the easiest cube-builder you'll ever use. Having said that, it does have its limitations - that is why we developed the full CUBE-it Data Academy Module. Nevertheless, if you want to get cubing in a matter of minutes, this is the place to start.

We are going to talk-through an example based on the following Dataset.

You need to note, first of all, that this is a flattened Dataset - it has been de-normalized to include descriptive fields like "State" and "Country", rather than "State ID"  and "Country ID" pointing to a Foreign Keys in other tables. This Dataset was flattened-out using Data Academy Data Spaces, and you should always do the same before attempting to build cubes using CUBE-it LITE.

In CUBE-it LITE, select your source table, and click "Add".

Drilling into the Cube Detail screen, you can re-name the cube. You should ensure that the Analysis Server and Database fields are set correctly. The Analysis Server needs to be running Microsoft SQL Server Analysis Services 2005. The Analysis Database will be created if it doesn't exist.

Now we need to add our Measures. You can use the "Multi-Add" functionality to add these.

Now our Dimensions. On the Cube Detail screen, add "Date" as a Time Dimension. Note that only one Time Dimension is available for each cube.

To add a Hierarchical Dimension we need to start at the bottom of our hierarchy. So, for a Dimension that describes Customer/ State/ Country, we need to start by adding "Customer" as a Dimension.

Then drill into the Dimension and add the roll-ups in the order in which they aggregate - so, "State" first, then "Country". NB Each level in the hierarchy must contain unique keys, so if you have Springfield IL and Springfield MA in a City/ State hierarchy, your underlying Dataset will need to create the cities as "Springfield (IL)", "Springfield (MA)" etc.

Finally, all you need to do is return to the top screen and click the "Generate" icon. This may take some time, especially if your source data is very large!

When Data Academy generates your cube, 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 the cube right after your Data Loaders and Data Spaces have been run.

Once you have generated your cube, you should be able to browse it in Management Studio. (You will need to Connect to the Analysis Server first.)