Joomla ServiceBest Web HostingWeb Hosting
Home Services

The Extract, Transform and Load (ETL) process will form a key part of your Data Warehousing solution.

Whilst it is possible to build an Analysis Services cube directly from a relational database using the inbuilt wizard, this process can obscure issues in the source data. It is better to import data in a raw form into a staging database first and then transform it as necessary to reflect the schema that resulted from the Dimensional Modelling process.

SQL Server Integration Services (SSIS) provides a wealth of facilities to manipulate data during the import process, which can be used to restructure data and validate it.

The stages of processing can be summarised as :

  • Import – import data from the source systems, be they databases, flat files or spreadsheets

  • Validate – check that the data imported is valid. Report invalid data and decide whether the process can continue or whether data needs to be fixed first.

  • Transform – restructure the data so that it is in a form that can be reported on easily

  • Migrate – move the restructured data into the relational form of the data warehouse

  • Populate – update any Analysis Services cubes based from the relational database

These stages aren't always necessary for all data, but represent a general approach that will ensure that data can be managed effectively.