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:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Shortcut to Fixing a Corrupted Oracle Inventory

Oracle Central Inventory files are essential to installing and patching software. The process for fixing a corrupted file can be found here.

Chad Cleveland | February 23, 2016

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

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.