Select Page

SQL Data Load Error

Author: Eric Russo | | October 3, 2014

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, 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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.