SQL and PL SQL Loading Via EAS

We have been doing quite a bit of SQL and PL SQL loading using EAS as of late. I have found the user interface can be confusing and the user guide to not be of much assistance. Below is what we have found to work for our needs.

First step is to get the ODBC set up, which the user guide can walk you through if you aren’t aware how to.

Once that is set up, within EAS you create a new load rule or modify and existing load rule if you prefer. Then navigate to File->Open SQL. You will first be prompted to select your Essbase Server, Application, and Database.

At first the next screen looks straight forward, but really there are a lot of extra fields that you may or may not need to use.

 
For instance to load via PL SQL, from a source such as PeopleSoft, you will want to select the Oracle Wire Protocol option from the SQL data sources section. You will enter your SQL statement by either using the boxes provided or by entering the entire statement into the Select section. Either of these methods will work. The next step is to select OK/Retrieve at the bottom of the screen. Note: You do not need to use the Connect section at all. Trying to fill in the Server, Application, Database, or Dictionary path in the Connect section will result in an error stating “Failed to Establish Connection With SQL Database Server. See log for more information.”
 
To load via SQL, you will want to select the SQLServer Wire Protocol option from the SQL data source section and enter your SQL statement into the provided boxes or into the Select section. Depending on how you have identified the tables in the from section of your query, you may have to provide the database name in the Connect section. If the database is included in the table description (ex: <>.dbo.<<>>) you will likely be able to move straight to selecting the OK/Retrieve button without issues. If you have not identified the database name in the table description, place the database name in the Connect section to move forward.  
Once you have selected OK/Retrieve, you will be prompted with the SQL Connect box where you are prompted for a user name and password, which will be your PL SQL or SQL user name and password. As a note, we found we had to have SQL authentication for this to work. Using Windows authentication produced errors and we were unable to connect. The Essbase Server, Application, and Database should default to your previous selection.

After selecting OK, your data will populate into your Data Prep Editor screen. From here you can create a load rule as you normally would. You can create both load and build load rules to run off of these data sources.

If you still receive a Failure to Establish Connection error message and you are sure of the passwords you are using, then you will want to look at the SQL next. Here are things to look for:

• Make sure you have not placed the word “Select” into the SQL box – It utilize the Select from the header on the box
• Remove table and column aliases – If you have multiple tables that share fields, you will need to put the entire table name in front of the field throughout your query
• Check your more complex SQL functions to make sure they are compatible with the SQL loader within EAS – Try removing the complex statements and see if you can connect
• Remove anything you have commented out – Leave the SQL statement as purely the SQL statement to be executed

When I have had an issue with my query I have stripped the SQL down to the most simple statement first. Sometimes, I have even gone so far as to have it be Select * from <>. Then adding each piece back in until I get the entire statement in place. It helps in identifying which part of the code EAS is having problems with.