A common task I encounter is needing to filter a flat file using a list of Essbase members. For example, I have a data file that contains all months for all CostCenters. However, I currently only need to work with a subsection of the file’s data. Say, Qtr1 data for all of CostCenter1’s children.
Anyone who knows me would likely say, “Well, Harry, you’re a linux command line guru. Just grep what you need out of the data file into another file!” And that’s what I’ve been doing for years. I’d write something like: egrep -i ‘jan|feb|mar’ sourceFile.txt | egrep ‘CostCenterA1|CostCenterA2|CostCenterA3|CostCenterB1|CostCenterB2’ sourceFile.txt > filteredFile.txt.
This works well enough, but I’m sure those of you who’ve worked with Essbase for any length of time can see the obvious flaw in this method. It’s pretty apparent: what happens when CostCenterC gets added or CostCenterB has more children added? Or what if CostCenter1 has a total of 100 children? That’s one loooong command. There are ways to handle this using grep. You could add all the member names to be filtered to a separate memberSelection.txt file, then do “grep -f memberSelection.txt sourceFile.txt > filteredFile.txt”. Still, memberSelection.txt has to be manually updated to reflect changes in the Essbase outline.
I’ve had to perform this file filtering so often lately that I finally decided to create a utility to make my life easier. And to keep my life as simple as possible, I added File Filter to an existing tool – cubeSavvy Utilities.
Similar to the Member Filter in ASO Export Parser, File Filter has Member Selections:
Using my example above, I could specify a Member Selection of <CHILDRENOF Qtr1 to get Jan, Feb, and Mar. But where it really gets useful is being able to specify <CHILDRENOF CostCenter1. Sham-Pow! No more cumbersome grep files that I have to remember to update every time a new CostCenter is added. Essbase administrator nirvana:
The result above is running against a flat file with 1.25 million records. 12 seconds is not too shabby, if I do say so myself.
Please give it a test drive, kick the tires, and let me know what you think.
-Harry Gates
Leave a Reply