Wednesday, April 22, 2009

Data Manipulation

Load rules can be used to load data, modify outlines or both. I've always been a big believer on making any "data manipulations" in your source system. For instance, you are performing a dimension build that needs to combine 2 or more columns to create a new field. This can be done with a load rule, but when in doubt, perform this step in SQL or your source system to limit the amount of data manipulations that need to occur in a load rule. Believe it or not, over time, a load rule can become corrupt and if you don't have a backup (and you should always have a backup, but just in case you don't, it will be hard to recreate all the data manipulations that you made in a load rule.

With that being said, I've come across numerous clients that don't have the ability to change the source dimension files (for example, the files come directly from SAP) so you will need to make data modifications in a load rule at some point in time, here's some examples of things you can do:

--------------------------------

Add Prefix or Suffix
As I noted on the Field Properties page, you can add a Prefix and Suffix to any field. This is really straight forward, let's say your data comes in as 2009, 2010, 2011, but your members in the Year dimension are YR2009, YR2010, YR2011. Instead of changing your data files, simply add a prefix of YR on the Global Properties tab of the Field Properties option. After you make the change, you will see the preview of your data change to YR2009, YR2010, YR2011 instead of 2009, 2010, 2011.

No comments: