I'm going to share my one experience building a datacube. I used SQL Server Analysis Services 2005. The company is in retail business and it has stores in several locations. Each store has its own database server but uses the same database schema.
First I pull data from each site into one central database. This is done periodically, in my case monthly. This central database uses the same schema as site database.
Then from this central database, data is massaged to form the 'star schema'. In my case, I wanted to build a sales cube. This sales cube should be able to be sliced by product, date, and location. The sales cube should be able to show sum of item quantity sold, sum of gross sales, and sum of net sales.
In order to create this star schema, I chose to create some views to flatten some table references:
- One view to join sales header and sales detail tables, exposing sales date, product code, location code, quantity sold, unit price, qty * price, and qty * price - discount. This can be called sales fact table.
- One view to join product table with its subtables like product category etc. This is the data source for the product dimension.
- One view to join location table with its subtables. This is the data source for the location dimension.
For date, I created a calendar table containing all dates from 1 Jan 2001 to 31 Dec 2030 that looked like this:
|2001-01-01|2001|1 |0 |
This calendar table is the data source for date dimension.
Next I created a new 'analysis services' project in visual studio. I set the views and tables above as data sources, linked the product code in the sales view to the product dimension, link the sales date to the date dimension, etc, and build the cube.
Analysis services will then set the cube definitions and populate the cube and dimensions. After this process is done, the cube is ready for use.
So the cube is populated when you process it. It will stay the same if you don't reprocess it.