It has amazed me for years that people don’t utilize the function within Essbase to connect it directly to relational databases. Sure, people for years talked about Essbase Integration Studio and now Essbase Studio is supposedly the best thing since sliced bread. Don’t get me wrong, Essbase Studio is a very good tool and it does enable the functionality of drill through which is very powerful. The problem is that Essbase Studio can be overkill for some situations and to be honest it can’t really do everything it claims. One example of this is updating text lists. Oracle claims that it can be done via Essbase Studio but even Oracle Support admits there is a bug that precludes this functionality from working.
So, if you just have some relational data and want it in Essbase easily with functionality you already know how to use and drill through isn’t a requirement than maybe the Load Rules SQL connectivity is for you. The big problem with the SQL Connectivity for Load Rules is that the documentation is less than optimal. Essentially there are three ways to connect to a relational database via Load Rules and they are all pretty easy to use. They are via and ODBC connection, a Substitution Variable or an Oracle Call Interface (OCI).
In order to utilize any of these you’ll need to create a new rules file, and then click File and “Open SQL”.
You’ll then be prompted for a database, which is your Essbase database. I’m not sure why this is, but if you are creating a rules file from a database it should already be populated for you.
You will then be presented with the Open SQL Data Sources window. This is where you will enter everything you need to retrieve relational data for Essbase.
In the upper left you’ll see the three connection options. The ODBC connection is easy enough if you have access to the Essbase server and can define the ODBC connection on it. Even if you are running the Essbase Administration Console client on your machine it will still read the ODBC connections from the server only. I almost always define the ODBC connection as a System DSN and have never had any problems with it.
To be honest I haven’t tested the Substitution Variable method enough, however, I can tell you that putting an OCI call in a Sub Var and utilizing that doesn’t seem to work.
The OCI option is the trickiest to use if you haven’t used it before because finding the correct syntax is difficult. Which is the real reason I decided to write this particular blog entry. The Oracle documentation for an OCI call states that the syntax is: “jdbc:oracle:oci:@<servername>:<port number>:<SID>”. (The use of <> is to illustrate an example, you don’t need them in your syntax anywhere I’ve used them.) However, that doesn’t work in Essbase. This makes sense since by clicking the radio button in Essbase that you are going to use an OCI call you’ve already told Essbase the beginning of that syntax. Incidentally, I found that syntax by looking up Oracle Database documentation, as I could not find a single example or reference to syntax anywhere in the Essbase documentation.
What I’ve found works within the Essbase Load Rules is: “<servername>:<port number>/<SID>”. One very important thing to realize there is that all Oracle documentation seems to say that a “:” should be used to separate the Port number from the SID. However, I have not had any luck with that syntax and have always had to resort to using a “/”. I haven’t tested this on an Oracle RAC and it may be different in that scenario.
For those with SQL experience the rest of the window should be pretty self-explanatory. You can put anything in the “Select” window that you usually would and anything in the “From” window that you would write into a sql statement, including PL-SQL commands. In the “Where” box you enter any additional statements, including things like, “ORDER BY” or even “UNION” if you’d like.
The entry area at the top right can be ignored for the most part but if you need to fill it out it’s essentially all of your Essbase info.
Once you have everything, hit the “OK/Retrieve” button and you should see data in your new Rule file just as if you’d opened a text file. One difference is that when you open a text file, Essbase doesn’t automatically assume the first row has column heading whereas the SQL interface will automatically take the columns from your “Select” window and use them as the column headers.
When you save the rul file you will automatically save the SQL you’ve entered.
One note about this is that while you are developing you’ll need access to the app logs for the application you are building the rul for. The reason is that any problem at all with the connection string or the query will result in the same error message from EAS. Essentially it’ll tell you to check the log. It means the Application log and there you will find pretty good messages to help you debug any issues.
No comments:
Post a Comment