tag:blogger.com,1999:blog-7650953985627040991.post3868166752982816601..comments2024-02-14T05:30:55.538-05:00Comments on Cameron's Blog For Essbase Hackers: Stupid Programming Tricks No. 19 -- TRUNCATEing DATAEXPORTCameron Lackpourhttp://www.blogger.com/profile/07701786303677521318noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-7650953985627040991.post-88049130208178094912015-10-14T17:44:24.244-04:002015-10-14T17:44:24.244-04:00It's not the most elegant solution, but we hav...It's not the most elegant solution, but we have a custom web interface where users can log in and launch SQL database packages or Essbase MSH scripts so users can refresh the data in their cubes on an ad hoc basis during the day if needed. The SQL packages generally pull data from the source and put it in staging tables that the Essbase dim builds / load rules pull from.<br /><br />So I set up a package that deletes the records from my table, and an MSH script that executes the export calc script. We don't want users having the database schema credentials, so I set up Essbase substitution variables at the all apps / all databases level for those. With 2 clicks they can execute the process and they get an e-mail when it finishes.Jen Guziknoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-50159918068056888492015-10-05T15:29:23.168-04:002015-10-05T15:29:23.168-04:00I was all jazzed about finally getting a dataexpor...I was all jazzed about finally getting a dataexport to a DSN source to work properly until I hit the pesky issue of it simply adding the results to the table with multiple runs. So off to Google I ran and then I was jazzed again about finding a blog post from Cameron about it.<br /><br />It's disheartening that a way to do this automatically wasn't included from the outset...exporting to a table is all but useless without it, especially given the nature of Essbase data and no real way to guarantee only incremental data without a lot of tricks and business process assumptions.Jen Guziknoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-82360296369031518542015-04-08T11:01:41.367-04:002015-04-08T11:01:41.367-04:00I figured it out...
CREATE OR REPLACE TRIGGER ESS...I figured it out...<br /><br />CREATE OR REPLACE TRIGGER ESSBASEDATALOAD<br /><br />BEFORE INSERT ON WMRI_DATAEXPORT<br />BEGIN<br /> EXECUTE IMMEDIATE 'TRUNCATE TABLE WMRI_DATAEXPORT';<br />ENDChris Rhttps://www.blogger.com/profile/09498398131572778081noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-21981791169621826632015-04-07T18:13:56.316-04:002015-04-07T18:13:56.316-04:00I'm back on this awesome trick. I'm tryin...I'm back on this awesome trick. I'm trying to make it work in Oracle but I'm getting an error with this command:<br /><br />"create or replace TRIGGER ESSBASEDATAEXPORT BEFORE INSERT ON WMRI_DATAEXPORT <br />BEGIN<br /> truncate table WMRI_DATAEXPORT;<br />END;"<br /><br />Error: PLS-0010: Encountered the symbol "TABLE" when expecting one of the following: :=.(@%; The symbol ":= was inserted before "TABLE" to continue.<br /><br />I'm excited to use this. We're not using ODI and I think this solution is very elegant.<br /><br />Chris Rhttps://www.blogger.com/profile/09498398131572778081noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-54066074141459376272014-08-20T12:51:31.881-04:002014-08-20T12:51:31.881-04:00Peter,
Thank you for your kind words. I know thi...Peter,<br /><br />Thank you for your kind words. I know this sounds maudlin or sentimental in a saccharine way, but knowing that you and others get some value out of what I write means an awful lot to me.<br /><br />Re this as a solution: all of the comments to this post have essentially said, “Interesting, but I’d never do it that way”. And I accept the argument that from an ETL or batch perspective, this is kind of dumb.<br /><br />I wrote this post for two reasons:<br />1) I wanted to know how to write a SQL trigger. Yes, It’s All About Me, but this is the only way I learn as I don’t exactly have an employer providing training. Although when I was (briefly) a consulting company employee they had me writing training so there wasn’t any real gain, at least that time. :)<br />2) I couldn’t think how else to do this interactively, i.e., a Planner or an Admin kicks off a calc script to SQL. There just isn’t a slick way to do this from a calc script. Yes, there are other possibilities (jobs in Workspace, FDM launches, etc.) but they aren’t any less kludgy. <br /><br />Re encrypting the SQL password, no, I know of no way of doing that in a calc script. It’s a curious omission, given how Oracle Planning command line utilities require an ecryped password and of course MaxL’s public/private key encryption.<br /><br />Lastly (almost), it’s tough to say what a real-world dba would say when told, “I need five usernames with TRUNCATE access to five separate tables”. If the security is atomic enough, does it matter? Or do they just go crazy when they hear the request. Sometimes I have success with, “Yes, I know it’s crazy, but it’s just the way the product works.” Other times the dbas tell me to take a long walk off a short pier.<br /><br />Lastly (for real), if you’re on LinkedIn, would you mind linking in to me? I’d like to email you on a few subjects. I searched for you in vain – maybe the Aus version limits US LI? Or if you can figure out some other way of trading email address so that the whole world doesn’t spam us, I’m open to that as well.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-87611136467510062622014-08-19T22:03:18.734-04:002014-08-19T22:03:18.734-04:00G'day All,
I'm probably in Chris's c...G'day All, <br /><br />I'm probably in Chris's camp - once one starts requiring 'proper' ETL (whatever that means), rather than just simple data imports/exports I'd likely want to move to ODI and use the functionality there (well, I'd find somebody who knew ODI better than me and get them to do it).<br /><br />I have a 'feeling' that is where oracle would likely want to push it, given the way the licensing works now in getting a limited use ODI license with a planning license.<br /><br />One slightly ontopic question...but is it possible (at all) to encrypt the outbound SQL password? I don't think so, be interested if anyone managed it. <br /><br />I'm not sure what that means with security with the particular example in the blog. Is it better in a control sense having multiple users accounts with only access to one table if there is a potential risk that the user/password is available in cleartext? The issue with 'system accounts' is that sometimes they end up having more access than required. <br /><br />Anyways, always interesting to see new things - and notwithstanding my comments above, I may end up using exactly this function to get around a problem I'm having! <br /><br />Cheers<br />PetePetehttps://www.blogger.com/profile/06439105832806165572noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-17815779686662966332014-08-19T10:19:26.216-04:002014-08-19T10:19:26.216-04:00Chris,
Thank you for your kind words.
There are ...Chris,<br /><br />Thank you for your kind words.<br /><br />There are lots of ways to make BSO DATAEXPORT to SQL work. All of them are kind of painful. Glenn made the same comment about multiple usernames/ODBC connections and you are both right. I only ask how many of these would really and truly exist at a typical client. My guess would be one or two.<br /><br />I was coming at it from the perspective of an interactive calc script but Glenn's batch approach and your ODI approach make sense as well given the relative contexts.<br /><br />I agree that the command could stand some ehnacements -- I fear that the approaches we describe are at the far end of what people do with the command. Personally, I would *really* like to be able to tag the *Essbase* username to the export as that would allow all sorts of interesting things to happen. You could kind of, sort of, do that in an AFTER LOGON trigger but it's all a bit complicated.<br /><br />And as for the triggers -- what you saw in the post was my very first one (or ones). I really wanted to know the basics of how they work and they are actually pretty awesome. <br /><br />As always, thank you for reading.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-89443068858770637862014-08-19T01:00:43.386-04:002014-08-19T01:00:43.386-04:00Hi Cameron,
Thanks for teaching me about triggers...Hi Cameron,<br /><br />Thanks for teaching me about triggers. I usually would solve this problem using ODI to delete the members of the SQL. And for the logging of the number of deletions, etc I'd do the same.<br /><br />What troubles me is having multiple IDs as that can be problematic at clients. Let's say you want to use this solution on multiple cubes, well it looks like some more users are needed. Otherwise very cool stuff.<br /><br />Also I agree that Essbase needs more SQL help for ETL. Transformations in load rules is bunk.<br /><br />Finally I think the Essbase calcscript function for relational databases is missing two key components:<br /><br />1. Create the dang table (or re-create/truncate it)<br /><br />2. Control the order of the columns (dimensions) better. It ain't cool developing an ETL and that have someone performance tune the app re-arrange the outline order or even dense/sparse settings.<br /><br />Appreciate your blog. See you at #KScope15! Hooray ODTUG<br /><br />-Chris RothermelUnknownhttps://www.blogger.com/profile/04448370919953385596noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-3104060481683679682014-08-18T14:35:50.957-04:002014-08-18T14:35:50.957-04:00Glenn,
As always it is gratifying to know that yo...Glenn,<br /><br />As always it is gratifying to know that you read my blog.<br /><br />You are quite right in noting that each table that gets exported to must have a separate username and consequently ODBC connection. I noted that in the text and yes, if there were many of these it would be a drag. Usually there are not hundreds of these, more like a handful, but I can definitely see a dba giving me the stink eye if I asked for five usernames with TRUNCATE access. I can see getting an ugly look if I asked for one when it comes down to it.<br /><br />Having said that, runsql.jar file – that is completely unsupported, right? And over five years old? That would make me worry in a production context although I take your point about it being used for real.<br /><br />Lastly, I like your approach of running a stored procedure in a load rule – one could definitely set it up as a dummy load rule (I even set the single column to ignore for data load so there is no error on load). However, that only makes sense within the context of a load process or at least an Essbase-centric batch process. If I were a Planner, and I was writing data out (or even an administrator forcing a partial export) that needed to be TRUNCATEd beforehand, it wouldn’t work from Planning or Excel.<br /><br />Fwiw, this works in SQL Server as a super simple stored procedure:<br /><br />ALTER PROCEDURE [dbo].[TruncateQueryTestSampleBasicExport]<br />AS<br />BEGIN<br />TRUNCATE TABLE QueryTest.dbo.SampleBasicExport<br />END<br /><br />The sql (T-SQL) in the load rule looks like this:<br />SYSDATETIME()<br />Exec TruncateQueryTestSampleBasicExport<br /><br />Not that your last suggestion doesn’t work, but it is coming at it from a batch Essbase build/load perspective. Within that context, they’re great. For everything else, I can’t see it, particularly if the DATAEXPORT is the first step in an ETL process outside of Essbase.<br /><br />What’s really, really, really needed is that TRUNCATE option on write – that would solve everything.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-27974048231050397962014-08-18T10:19:59.554-04:002014-08-18T10:19:59.554-04:00Cameron, A always I love your posts, but I have a ...Cameron, A always I love your posts, but I have a concern with your approach. Suppose I have 10 different jobs. I would need a separate login id for each. now what about 50 jobs? Gets messy. The two things I came up with and blogged about are 1. issuing the truncate in a load rule (yes it can be done but would require lots of extra load rules) 2. Use the runSQL CDF to run the truncate or a stored procedure that does the truncate. I use the runsql all the time. <br />As a side note, the truncate in dataexport has been requested as an enhancement, but I have no idea if or when it might get includedGlennShttps://www.blogger.com/profile/08532634180859769798noreply@blogger.com