I would like to use DATAEXPORT command to export data into a relational table, but I want the data to be cleared before loading into it. Is it possible to clear the data in a relational table using a calculation script? IF not what would be the best option?
It's interesting that you ask this question at this time, because I was just in the process of putting together a blog entry on a odd way to do this.
First , the easy way.
You don't mention the relational target. If it is SQL Server , then you could create a stored procedure on the server and run an OSQL statement in your batch script to truncate the table. Truncate is better than delete since it does not log the removals. (can be much quicker and saves space on the server).
Ok to steal the thunder from my blog.
my blog, First you have to still create the stored procedure,
Second, create a load rule Select "Open Sql data" in the load rule and select your sql ODBC driver. In the select claue enter
exec mystoredProcedureName where MyStoredProcedureName is the name of the stored procedure you created
do an ok/retreive and you will get a 1 returned as data to your load rule and the stored procedure will run. You can do something like load that one to an intersection of your Essbase database or Cameron suggested to me, create the load rule to load the one as data then reject rows that have 1 so nothing loads.
Now you have something that will truncate the table, you can put it into a batch script without ever having to go outside of Essbase
How about a CDF, then you do it all in one calc script.
Pretty easy to put together a bit of Java which will clear out a database table, you could pass in all the parameters you need from the calc script.
Could be wrote to run against Oracle, sql server etc