Implementing LIKE in BODS using lookup_ext

I’ve been doing a bit of ETL using SAP Business Objects Data Services (BODS) for our BI / DW system lately and needed to implement some business rules to return the mapped value based on the input value specified. For example, if the input value was ‘Rent for May 2013’, return the mapped value ‘Rental Payments’. You may be thinking this is pretty straightforward, just use a simple comparison using lookup_ext to check for the text ‘Rent for May 2013’ and then return the mapped value. This of course would work, but then I would need to have every possible variation and I definitely did not want to maintain this as every month I would need a new entry in the mapping table and also to handle every variation depending on how the data is entered – unfortunately there is also no consistency, for some rows it may be ‘Rent for MM yyyy’, others it may be ‘MM yyyy Rent’ and worst yet, there may be extra spaces between the values like ‘Rent  for  MM yyyy’, meaning the format may be different.

Given that I did not want to map every value, I needed a mechanism to do a comparison with wildcards similar to LIKE in SQL. What I was wanted to do was basically do something along the lines of

 SELECT Mapped_Value FROM Mapping_Table WHERE ‘Rent for May 2013’ LIKE ‘%Rent%’ 

You would think it would be pretty straightforward to do the above. I mean this is pretty simple SQL and is a common query that is run. In BODS however, this is a little bit more tricky to do and there isn’t a great deal of information on the Internet. I did plenty of searching and found lots of articles such as:

• lookup_ext and like operator
• lookup_ext() with pattern

All of them had some information regarding how to use patterns and what the ~ operator meant, but didn’t go and show this. The articles did show how to implement an OR condition with lookup_ext in BODS, but not what I was after, a LIKE comparison. Reading all this sounded like it was possible to achieve, I just needed to try and work it out myself based on all the information provided. It was a good learning experience and here I am documenting how this can be achieved.

To implement LIKE in BODS using lookup_ext what you will need to first do is verify that you have BODS 12.1 or above as the ~ operator is not supported in previous versions of BODS. If you don’t have this, you’ll need to use Custom SQL or another approach using LEFT OUTER JOINS. Once you have verified you have the correct version to apply this, we can get started to implement this.

Firstly, what you will need to do is create a table to store your mapping table similar to the table below:

ValueToCompare	MappingValueToReturn
mr(.*Rent.*)	Rental Payments

You may be wondering why the ValueToCompare column is so weird. The way lookup_ext works with the ~ operator is that it expects the comparison function to be embedded in the lookup table, so the comparison column needs to include the comparison function, here we are using mr which is short for match_regex as we want to implement a LIKE comparison (lookup_ext also supports ms (match_simple) and mp (match_pattern) which you can look at if you require different types of comparisons.). Inside the comparison function I have also embedded the regular expression .* which means any number of characters before and after the value as I want a contains, if you want a starts with or ends with, just remove the regular expression from either end. I’m sure there is properly a better regular expression for this, but this works for my scenario and will do.

Once this is defined, all we need to do is bring up BODS and is to implement a lookup_ext using the mapping table as follows:

As you can see, this is pretty much the same as any other lookup_ext configuration you would do. The conditions can be as complex as you need it to be, including other comparisons and also making use of the order by if you have multiple mappings. If you need an example, just leave a comment and I’ll see what I can do.

That’s basically it to implementing LIKE in BODS lookup_ext. Once thing to remember is to make sure you use the ~ operator and not the = operator.

Hope this helps.

Scheduled Web Intelligence reports with Prompts no longer work after upgrade to SAP BI 4.0

I’ve currently been busy working on an upgrade from an SAP BOE 3.0 environment to the new SAP BI 4.0 environment. There are a few lessons that I have learnt while undertaking an upgrade and one that I have come across is in relation to Web Intelligence reports with schedules. What I have found is that the schedule no longer works once upgraded.

Not sure why this is, but digging on the Internet didn’t come up with anything. So I thought I’d try and reset the schedule for the reports one by one and this is when I came up with the next problem, I couldn’t set the prompt, whenever I tried to access the prompt details, I was presented with an awful java.lang.NullPointerException and the properties window would close. Unfortunately the error message is too generic and googling doesn’t help. I thought maybe the Java version was out of date, but that was not the case. I was also running SP5 with Fix Pack 4 which was the latest update so it couldn’t have been a bug that wasn’t fixed. I tried another installation and environment and still no luck.

I was about to give up and call it quits on the upgrade, but not one to give up, I thought I’d try something out of hope, similar to the usual reboot the PC when it doesn’t work – I decided to see if I opened the report and re-saved it over itself, if this would fix the problem. Guess what, this actually fixed the issue with not being able to access the prompts. I was now able to set the prompt and schedule the report. Of course this is a pain to do, but since I only had a small number of reports, I wasn’t too fussed.

So if you are experiencing a similar sort of issue after an upgrade to SAP BI 4.0, give this a try and see how it goes. If you have lots of reports, might be worthwhile looking into using the SDK to do this.