In the latest version of WIT we have switched from using a traditional relational database to using SQL Server Analysis Services to calculate the scores displayed on the charts.
When employee surveys are completed by clients the answers are saved in the traditional relational database. From here they go on a journey until they finally fulfil their potential as a chart in WIT.
That journey goes something like this:
1. They start out in our relational database as a set of rows in a series of tables.
Our primary database structure has several legacy complexities which are not really relevant to WIT, such as employee surveys contributing to List and Accreditation entries. Only one of these entries will be available in WIT.
2. Next stop on the journey is the Data Warehouse. The Data Warehouse is a half way house between the strict relational model used by the primary database and the OLAP structure needed by Analysis Services. When moving data from the primary database to the data warehouse we remove duplication (only the primary entry is taken over), remove irrelevant information (rows and columns) and assign surrogate keys to identify the records inside Analysis Services
There is a script in the Data Warehouse project which contains the logic for taking data from the primary database and moving it into the Data Warehouse.
3. The cube is defined and populated based on the tables and views in the data warehouse. Tables in the data warehouse relate to either Fact or Dimension tables in the cube. A Fact table is some data that we are going to want to aggregate while a Dimension is some data that we are going to want to filter or display based on. When the Cube is processed it pulls the required information from the data warehouse and does any necessary pre-calculations.
On a nightly basis an Integration Services job runs which checks if a cube rebuild is required and if it is will automate running through the steps described above.