mapping based on whether or not a member already exists in the dimension

[OSAdmin]

Originally posted by Patrick Victory

Let's say we have ten thousand vendors in the source system. Most (~6k) of the vendors are minor and we don't need to maintain detail about them but we still need to load all of the data.

We've built out the dimension to have our "important" vendors in the appropriate hierarchies but I'd like to map all other vendors to an "Other Vendors" member.

The problem is that the only way I can think to do with transformation rules is create a 1:1 map for every vendor in the dimension (~4k rules) and then a final *:* to map the rest to Other. This seems like a really terrible solution; management would be a hassle and it just looks bad.

I've tried to brainstorm around some kind of connector BR logic that uses a descendants function to return a list of all vendors in the dimension and then write logic based on that but I'm not exactly sure how to implement it and it would destroy the vendor IDs themselves so drilling back would be problematic.

The other solution I've thought about is somehow leveraging the internal Member table and pull member names based on the DimID but I can't seem to reference both an internal and external table at the same time. There was some discussion of trying to do it between Stage and the Member table but then no one I've talked to has been able to come up with a way to do that as part of an import in a workflow.

Ultimately, the logic is "if it exists in the dimension, use it, if not 'other'"

[OSAdmin]

Originally posted by Jason Evans

A potential solution could be to use a parser business rule upon import.  In the rule, evaluate the vendor number against your dimension, if there's a match, then return the vendor number, if not, then either return "Other" or even "Other_<vendorID>.  If you did the Other<vendorID> approach, you would just use a mask rule to map Other* to Other which would provide the details needed for drill back.  Everything else would be * to *.