tag:blogger.com,1999:blog-7650953985627040991.post5708869194280176969..comments2024-02-14T05:30:55.538-05:00Comments on Cameron's Blog For Essbase Hackers: The fastest way to export targeted data from BSO Essbase with NONEMPTYBLOCKCameron Lackpourhttp://www.blogger.com/profile/07701786303677521318noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-7650953985627040991.post-48581524236210632792017-07-28T12:39:53.068-04:002017-07-28T12:39:53.068-04:00Hi,
We are trying Replicated partition from BSO c...Hi,<br /><br />We are trying Replicated partition from BSO cube to ASO. We want to replicate all the members form a dimension except one parent and its descendants. using except command below gives error.<br />Command in partition: Except({Descendants([EC],[ECDIM].Levels(0))},{Descendants([EC.1],[ECDIM].Levels(0))}),"AEntity"' sourcearea1<br /><br />Error : ERROR - 1023040 - msg from remote site [[Thu Jul 27 15:49:45 2017]Servername/ASOapp/ASOcube/Custadmin/Error(1023040) msg from remote site [[Thu Jul 27 15:49:45 2017]Servername/BSOApp/BSOcube/admin/Error(1023007) Parse error in partition definition line [0]]].<br /><br />Export data from BSO and import to ASO is taking long time, I want to go with partition option.vhttps://www.blogger.com/profile/13578952786987143149noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-87996525263898669612017-03-28T10:51:38.324-04:002017-03-28T10:51:38.324-04:00Hi - any update on mumo805's question? I know...Hi - any update on mumo805's question? I know Essbase normally requires a dimension on both rows and columns but when loading into a relational source it would be nice to have the period on the row and the data in a single column.<br /><br />Thanks.<br />Rose GlazewskiRosehttps://www.blogger.com/profile/09274255044598270102noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-21263600438291436832016-11-25T22:40:27.835-05:002016-11-25T22:40:27.835-05:00Great article on our site. Keep up the good writin...Great article on our site. Keep up the good writing.<br />Obat Herbal Gula Darah Tinggi Terbaikhttp://apotek45.com/obat-tradisional-penyakit-gula-paling-ampuh/noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-4729693779379385632016-07-15T15:17:35.807-04:002016-07-15T15:17:35.807-04:00Hi,
I have a problem. When I export the data, for...Hi,<br /><br />I have a problem. When I export the data, for some reason Essbase exports each time period member as its own column. I want the time period value to be part of the row data, not its own column. How do I do that? All of the other dimensions don't have that problem.mumo805https://www.blogger.com/profile/03000235359602342289noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-90380773659961934772016-01-22T01:31:13.244-05:002016-01-22T01:31:13.244-05:00Hi Moses,
You can spool the contents of the outpu...Hi Moses,<br /><br />You can spool the contents of the output query into Text file via Maxl command.<br /><br />Eg.<br /><br />**************<br />spool on to 'TEST_MDX_OUTPUT.txt';<br /><br />logon userid password on host;<br /><br />/* Add your MDX query here */<br /><br />logout;<br /><br />spool off;<br />**************<br /><br />Regards,<br />TharikTharikhttps://www.blogger.com/profile/07972596933414820119noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-36785243575319786122016-01-22T01:29:36.652-05:002016-01-22T01:29:36.652-05:00Hi Moses,
You can spool the contents of the outpu...Hi Moses,<br /><br />You can spool the contents of the output query into Text file via Maxl command.<br /><br />Eg.<br /><br />**************<br />spool on to 'TEST_MDX_OUTPUT.txt';<br /><br />logon userid password on host;<br /><br />/* Add your MDX query here */<br /><br />logout;<br /><br />spool off;<br />**************<br /><br />Regards,<br />TharikTharikhttps://www.blogger.com/profile/07972596933414820119noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-25593462229639543582015-03-05T18:24:52.467-05:002015-03-05T18:24:52.467-05:00Cameron,
Using this script I am able to do in 3.1...Cameron,<br /><br />Using this script I am able to do in 3.1 seconds what and export script does in 34 minutes. Wow. However, do you have a method to get it to export as a text file? If I could execute this with a batch script and have the output as a text file (to pick up with a load rule) it would be a game changer for me.<br /><br />Sincerely,<br />M MosesAnonymoushttps://www.blogger.com/profile/11489727605182857308noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-33786673413425372212014-08-12T15:46:09.679-04:002014-08-12T15:46:09.679-04:00Though a little late to the post, I thought I woul...Though a little late to the post, I thought I would add there's an 8 bit Racer X here: http://www.stafaband.info/download/mp3/lagu_technical_difficulties_racer_x_8_bit_remix/ For those seeking that retro 80's video game sound.Robb Salzmannhttps://www.blogger.com/profile/08971173000793211795noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-47709555937655365772014-07-09T22:35:41.650-04:002014-07-09T22:35:41.650-04:00Hah.
So there's an earlier comment about the...Hah. <br /><br />So there's an earlier comment about the 'biggest' intersection that can be brought back. <br /><br />In 11.1.2.3 they've set a limit. <br /><br />4294967296 (2 power of 32) intersections is the biggest retrieve possible.<br /> <br />So there you go. Don't go bigger than that. <br /><br />Works wonders bringing data straight into Smartview using an essbase connection. Even formats it as a smartview retrieve! <br /><br />Cheers<br />PetePetehttps://www.blogger.com/profile/06439105832806165572noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-90182683551520824502014-03-25T06:54:54.421-04:002014-03-25T06:54:54.421-04:00Eric,
I wrote the above using 11.1.2.2 so in theo...Eric,<br /><br />I wrote the above using 11.1.2.2 so in theory it should work.<br /><br />If you use the query exactly as I wrote it against Sample.Basic, does it work?<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-8922342011064859342014-03-24T11:38:36.763-04:002014-03-24T11:38:36.763-04:00Hi Cameron,
I'm using version 11.1.2.2 and I&...Hi Cameron,<br /><br />I'm using version 11.1.2.2 and I've tried using NON EMPTY, NONEMPTYBLOCK and NONEMPTYSUBSET in my MDX query and Essbase puts out this error for any of them:<br /><br /> ERROR - 1260052 - Syntax error in input MDX query<br />YBLOCK' .<br /> ERROR - 1241101 - Unexpected Essbase error 1260052<br /><br />Also, it doesn't seem to like the comma "," after COLUMNS as it will error out on that as well if I leave the comma there as per your example.<br /><br />Any thoughts as to why my Essbase environment doesn't seem to recognize any of the NON EMPTY, NONEMPTYBLOCK, NONEMPTYSUBSET functions?<br /><br />Thanks!<br /><br />EricEricnoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-90309331244928907332014-01-29T20:13:18.732-05:002014-01-29T20:13:18.732-05:00NONEMPTYBLOCK doesn't seem to work when transp...NONEMPTYBLOCK doesn't seem to work when transparent partitions are in place. Apologies if you said this and I missed it.Simply Joehttps://www.blogger.com/profile/12953899700146055015noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-37195440997438886642013-11-30T13:18:26.493-05:002013-11-30T13:18:26.493-05:00Ranga,
Re the Dynamic Time Series members, no, I ...Ranga,<br /><br />Re the Dynamic Time Series members, no, I know of no way to get at that via a calc script. <br /><br />Sorry.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-52723882294041695012013-11-25T11:47:12.881-05:002013-11-25T11:47:12.881-05:00Is there any way to export the YTD values using Da...Is there any way to export the YTD values using Data export in Calculations script.Anonymoushttps://www.blogger.com/profile/12219133499484656489noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-85696455160011075182013-04-28T02:37:29.214-04:002013-04-28T02:37:29.214-04:00Hi Cameron,
It would be interesting to compare t...Hi Cameron, <br /><br />It would be interesting to compare the time a binary export needs with the methods you described in your post. Some times it can be very fast too.<br /><br />ManolisManolisVhttps://www.blogger.com/profile/03672388136527629151noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-59701717590759511232013-04-12T09:09:05.217-04:002013-04-12T09:09:05.217-04:00LuigiO,
I do just that in the code with the follo...LuigiO,<br /><br />I do just that in the code with the following MaxL statement:<br /><br />alter session set dml_output alias off ;<br /><br />Take a look at the MaxL -- it's all there.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-18264610189325935412013-04-10T21:23:41.691-04:002013-04-10T21:23:41.691-04:00Cameron,
Is there a way to output Member names an...Cameron,<br /><br />Is there a way to output Member names and not aliases using MDX?<br /><br />ThanksLuigiOhttps://www.blogger.com/profile/02707354122094325570noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-49106742801687277402013-03-18T12:23:12.370-04:002013-03-18T12:23:12.370-04:00Hey Cameron,
I have found a lot of useful stuff y...Hey Cameron,<br /><br />I have found a lot of useful stuff you've posted both here and elsewhere. <br /><br />Hoping to use this method as a selective export, I was probably going after a few million of the 90 million data blocks in our workforce application. I am not sure, but I believe the Crossjoins are preprocessed in memory and there must be a practical limit to how many cells can be held at one time. At least that's my guess. <br /><br />The server did come back on its own but while the query was running and for a while after killing it, I could not even remote into it.Todd Binenstocknoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-20553931954200324822013-03-04T18:13:21.538-05:002013-03-04T18:13:21.538-05:00Camaren,
I have been using NONEMPTYBLOCK keyword ...Camaren,<br /><br />I have been using NONEMPTYBLOCK keyword for a long time. This undocumented keyword is very buggy. It would "maddeningly" exclude data blocks from many of my queries.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-13941433715627337012013-02-06T10:32:00.659-05:002013-02-06T10:32:00.659-05:00Surprisingly the server came back a few hours late...Surprisingly the server came back a few hours later after the process terminated with an error, probably due to running out of memory to support the large Crossjoin. For what it is worth, here are the error messages:<br /><br /> ERROR - 1200467 - Error executing formula for [REGION DEFINITION]: status code [1130203] in function [].<br /> ERROR - 1241101 - Unexpected Essbase error 1200467.Todd Binenstocknoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-35522145949823767732013-02-06T07:19:43.123-05:002013-02-06T07:19:43.123-05:00Todd,
Great to hear from you. I am amazed you re...Todd,<br /><br />Great to hear from you. I am amazed you read this blog. :)<br /><br />Wow, pretty cool, MDX hosed the OS. Are you *sure* that's the case? I can imagine bringing an esssvr.exe down, or essbase.exe even (although that seems sort of unlikely) but the entire server to the point of require a hard boot? Anything is possible but that sounds like a bad server to me.<br /><br />I would be very curious to see what, if anything, was written to the Essbase, app, and MaxL logs.<br /><br />Okay, to your question, I do not know of any way to calculate what is too big for NONEMPTYBLOCK. I can tell you that I went after a 200 gb (really) app, and pulled out about 30,000 blocks with no problem. How many blocks do you reckon you were going against? I can fake it in AWS (not going to worry about that going down) and see if I can hit the same behavior.<br /><br />Regards,<br /><br />Cameron LackpourAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-62154582496183695582013-02-05T17:00:53.484-05:002013-02-05T17:00:53.484-05:00Well, this method can apparently crash a server pr...Well, this method can apparently crash a server pretty hard if your member selections are a large enough cross join. After retrieving a few blocks of data very rapidly, I went on to see how long it would take for this method to export all the level 0 data in one scenario in my workforce database. The answer is apparently infinity. The server will not respond to terminal services so it may need a physical button push to recover. Cameron, are you aware of any ways of calculating resource utilization for these queries so that we can determine what is too big for a server to handle?Todd Binenstocknoreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-75132651046501332082013-01-15T11:22:02.978-05:002013-01-15T11:22:02.978-05:00P. Derek,
I used that command to keep the log fil...P. Derek,<br /><br />I used that command to keep the log file clean. You aren't seeing all of the script but it did a report script, MDX with NON EMPTY, and then MDX with NONEMPTYBLOCK and it all got confusing. I just left in in there for the blog post because I like a simple to read log file.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-79362264855910754072013-01-15T11:20:21.673-05:002013-01-15T11:20:21.673-05:00Tim,
FWIW, I am trying it now with <SPARSE an...Tim, <br /><br />FWIW, I am trying it now with <SPARSE and it is *slower*.<br /><br />I may have to wait till the box is 100% free, but there is no magic bullet in the performance like MDX's NONEMPTYBLOCK.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.comtag:blogger.com,1999:blog-7650953985627040991.post-72448600240324059922013-01-15T10:52:19.438-05:002013-01-15T10:52:19.438-05:00Just out of interest why do you include this line?...Just out of interest why do you include this line?<br /><br />alter application ep clear logfile ;<br /><br />I know what it does, just wondering why it is there.P. Dereknoreply@blogger.com