Select Page

SQL Data Load Error

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

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

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

CONTACT US

Work for Us

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

EXPLORE JOBS