How does it work inside ?

For all those who want to have some more technical instrospection in the way the datawarehouse is constructed, this article will tell you more about SQL principles involved in the structure of the cube. This will give some eclearings on how far queries can go in reports.

The technical expression of an indicator

An indicator is a computable measurement (usually numerical). It can be used in arithmetic or statistic calculations. Thus it essentially has a form of a scalar value to which functions such as SUM, MEAN or value selectors such as MAX or MIN can be applied. An indicator occupies a single numerical field in the base records table (which is called the "fact table"). This table will probably use a simple primary key (integer sequence), followed by as many numerical fields s needed for storing each collected indicator.

The technical expression of a single dimension

The technical expression of a linear axis (star)

Let consider now two distinct dimensions assembled in a single axis, e.g., city and country dimensions, each dimension needs a table to store all the values that were collected in the explored data. The fact record only needs a single foreign key to bind the single event, so called "fact" to the base dimension (the city of the user generating the event). The data cube feeding process will also have to bind the city to the relevant country in the upper dimension. Thus a foreign key is added to the "city" dimension table to "arrange" collected cities in the "countries" comming from real data.

The TOA model

This first release of TOA will only allow constructing start-shaped analysis cubes. One dimension can only accept one additional foreign key for keying the upper dimension. Although simpler than the "snowflake" model that assumes a dimensions might be qualified by several upper concurrent dimensions, the star model in TOA yet answers a lot of interrogations of the analyst.

Last modified: Wednesday, 24 February 2010, 09:59 PM