You may need to transform data, for example, to change date formats.
Assume you want to create an OLAP model, combining data from sales and financial databases. If the sales database specifies New York as New_York and the financial database specifies New York as NY, you can transform NY to New_York in the staging area (see Deciding to Create Staging Areas) without changing source data.
You can do some data transformations in OLAP models and metatoutlines. (For a list of available transformations, see Integration Services online help.) If you must do significant transformations on the data, consider using a data transformation tool before you use the data in Integration Server.
Transformations you must perform in source databases, and not in Integration Services, include these:
Most frequently accessed transformations
For example, you want the Essbase database outline to include members for Year, Qtr, and Month, and the data source table contains a column called TRANSDATE. TRANSDATE holds the transaction date for each row.
Transform the data ahead of time, creating a source table that contains the columns YEAR, QTR, and MONTH. The columns contain data transformed from the TRANSDATE column. You can index the YEAR, QTR, and MONTH columns to improve performance.
Transformations to take the intelligence out of key columns
Some key columns may include categories of information you want to split out before using the data in Integration Server. For example, if the source table has a PRODID column containing product identification information, transform the data in the data source so that it maps to the data categories you want to display in the Essbase outline. In Figure 5, Transforming a Product Code Stored in a PRODID Column, for example, the categories are CUSTID, STATE, and PRODNUM.
