4 Replies Latest reply: Jul 3, 2009 12:08 PM by GlennS_3 Branched to a new discussion. RSS

    DATAEXPORT command

      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?

        • 1. Re: DATAEXPORT command
          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
          • 2. Re: DATAEXPORT command
            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


            • 3. Re: DATAEXPORT command
              Thanks for your reply.

              My relational database is Oracle. Can I use the same procedure for Oracle?
              • 4. Re: DATAEXPORT command
                Oracle databases make use of stored procedures just like Sql server does but I don't know if the syntax is exactly the same. I've not tried this against an Oracle DB, but am guessing it would work