SQL Data Load Error

What You’re Seeing

So you’ve decided to use a relational data source to load data into an Essbase cube using the EAS SQL Interface and a load rule. The problem is that when you preview the data in your rule, there seem to be records that aren’t in the relational table no matter how hard you query.

You might have realized by now that the new records in your rule are either parts or the rest of the preceding entry.

What’s Happening

I haven’t looked into precisely which characters in which encodings produce this result, but it appears that a newline or carriage return entered into a data entry field will end up being passed into some relational databases. So when the Essbase rule reads the relational table, it also reads the newline, and translates this as an entirely new record. Long story short: RDB one record, Essbase shows two or more, and it’s because special characters are translated differently by each.

Don’t Believe Me?

Go ahead and load your relational table in Excel using MSQuery or another data connection option. Then go to a record that is being split by Essbase. Click on the cell that has the broken value. You should see a new line in the formula bar.

Not the Solution

Hopefully the problem column you’re using isn’t one required for your data load. If it is, you should write a query to remove any offending special characters. Unfortunately, as late as 11.1.2.1, even if you don’t have to use the column, just ignoring the column in the rule still results in the same behavior.

The Solution

Modify your query in the SQL Interface to eliminate the column (if that’s an option). You might want to go crazy and eliminate all the columns you don’t use. I typically do this and sum across the used columns to create an aggregate table data load (one value loaded per intersection in an Essbase cube).

Solution to be Tested

I’m also thinking it might be even easier to just replace the special characters in the Essbase SQL Interface query. However, doing this depends on the relational system you’re using and the functions available. I’ll add an update once I give it a go.