In Certification Corner we try and bring different type of examples to help educate about OneStream functions. In this series of articles, we will identify the order of operations of the Transformation Rules.
In this series of articles where we highlight mapping, we will try and use different types of examples. Some lend themselves to numeric values more than others, but the purpose of this series will be to show many different ways to map information.
What are Transformation Rules?
Transformation Rules are rules that map a source Member to a specific target value. There are 5 major rule types to map your data. Typically, when providing examples, we use numbers. For this article we will use text movie titles to help map and show you the difference and order of mapping rules.
Before starting the mapping examples, let's review the different type of mapping rules:
|
Transformation
Rule Type
|
Definition
|
Example
|
|
One-to-One
|
Explicitly mapped
Members (Scenario, Time and View can only use these rules)
|
Actual -> Actual
05/31/2021 -> 2021M5
Monthly -> MTD
23099 - > 23000
|
|
Composite
|
Supports mapping ‘slices’ of Members such as a product
|
A#51000: UD2#H* to the UD1 Member of Sales
|
|
Range
|
Map a range of
Accounts from A to Z to = target.
|
Map Accounts 21230~21239 to 20300
|
|
List
|
Map a delimited list of Accounts to one Account
|
Map Accounts 41137;42642;42688 to Account 60100
|
|
Mask
|
Wildcard mapping
that takes the place of one character (?) or multiple characters
(*)
|
Map Accounts that start with 86 (as 86*) to 41000 or *84*7* to 41000
Account starts with any one character, then 86 (as ?86*) to 42800
Any Account to any Account if there is a match: * to *
Mask Rules run less efficiently due to use of? or embedded asterisks (e.g., *84*7*)
|
For this series we will use Star Wars and Star Trek movies to illustrate the different mapping concepts:
|
Star Wars: Episode I: The Phantom Menace
|
Star Trek: The Motion Picture
|
|
Star Wars: Episode II: Attack of the Clones
|
Star Trek II: The Wrath of Khan
|
|
Star Wars: Episode III: Revenge of the Sith
|
Star Trek III: The Search for Spock
|
|
Solo: A Star Wars Story
|
Star Trek IV: The Voyage Home
|
|
Rogue One: A Star Wars Story
|
Star Trek V: The Final Frontier
|
|
Star Wars: Episode IV: A New Hope
|
Star Trek VI: The Undiscovered Country
|
|
Star Wars: Episode V: The Empire Strikes Back
|
Star Trek: Generations
|
|
Star Wars: Episode VI: Return of the Jedi
|
Star Trek: First Contact
|
|
Star Wars: Episode VII: The Force Awakens
|
Star Trek: Insurrection
|
|
Star Wars: Episode VIII: The Last Jedi
|
Star Trek: Nemesis
|
|
Star Wars: Episode IX: The Rise of Skywalker
|
Star Trek
|
| |
Star Trek Into Darkness
|
| |
Star Trek Beyond
|
The first part of this is to map the movie series to a dimension that has movie series names. We need to map all the movies to a target of either Star Wars or Star Trek. There are multiple ways to do this.
We can use one-to-one mapping and map each movie to Star Wars or Star Trek as is shown in this example:
| Source |
Target |
| Star Wars: Episode I: The Phantom Menace |
Star Wars |
| Star Wars: Episode II: Attack of the Clones |
Star Wars |
| Star Trek: The Motion Picture |
Star Trek |
| Star Trek II: The Wrath of Khan |
Star Trek |
Another method is to use Wild Cards to map Exceptions to rules. If we use wildcards to map the movies, there are two movies that get missed with these mapping rules: Star Wars* and Star Trek*
| Source |
Target |
| Star Wars* |
Star Wars |
| Star Trek* |
Star Trek |
| Solo: A Star Wars Story |
Star Wars |
| Rogue One: A Star Wars Story |
Star Wars |
Which one of these rules runs first? If you were just to put the last two lines in, those lines will process first. They are captured below:
| Source |
Target |
| Star Wars* |
Star Wars |
| Star Trek* |
Star Trek |
| Solo: A Star Wars Story |
Star Wars |
| Rogue One: A Star Wars Story |
Star Wars |
| Star Wars: Episode I: The Phantom Menace |
Star Wars |
As an example, if you pull out the Episode 1 and map it one-to-one, it will use the one-to-one rule and not the Star Wars* rule.
Here is another way that can help mapping by not typing the whole text and relying on the wild card:
| Source |
Target |
| Star Wars* |
Star Wars |
| Star Trek* |
Star Trek |
| Solo* |
Star Wars |
| Rouge One* |
Star Wars |
In our next article we will use wildcard mapping to map using a second dimension.
Let us know in the comments which mapping technique you would have used in this situation.
