1 2 Previous Next 24 Replies Latest reply: Oct 9, 2013 1:45 AM by Eswar'S RSS

    ASO Load Multiple files at same time - 11.1.1.3

    18_4_Ever
      Hi,

      I know in 11.1.2.2 you can use wildcards to load files at same time. I'm trying to load some files into ASO cube at same time. So I broke them out into 5 different MaxL scripts. Each MaxL starts with:

      alter database "'RTL_BLD'"."'Retail'" initialize load_buffer with buffer_id 1;

      I just increment the 1 to a 2, 3, 4, 5.

      So couldn't I execute these all at once as each load is going to a different buffer?
        • 1. Re: ASO Load Multiple files at same time - 11.1.1.3
          TimG
          Short answer:

          Yep.

          Long answer:

          If your source is SQL then you can already load in parallel with multiple rules file (which you probably know) even in 11.1.1.3. If you are using flat files what you're proposing will work, and you can then commit the buffers in a single step - which I highly recommend if you're not creating incremental slices. I think, but don't have time to test right now, that you will have to specify the resource usage of each buffer to avoid the total exceeding one - the Technical Reference has the details.

          You may also need to consider the other buffer properties (aggregate_sum, aggregate_use_last) and the buffer commit specification (add values, override) etc, because obviously you can see different end results depending on whether you use multiple buffers and how you commit them.
          • 2. Re: ASO Load Multiple files at same time - 11.1.1.3
            sunil k
            You will be able to load to multiple buffers concurrently but you will not be able to commit buffers to database concurrently.

            Your data load buffer commit statements have to be run one after another i.e. the statement below

            import database 'app'.'db' data from load_buffer with buffer_id 1;

            Regards,
            Sunil
            • 3. Re: ASO Load Multiple files at same time - 11.1.1.3
              TimG
              Sunil, the Tech Ref says that this is valid syntax:
              import database AsoSamp.Sample data from load_buffer with buffer_id 1, 2;
              http://docs.oracle.com/cd/E12825_01/epm.111/esb_techref/maxl_imp_data_as.htm

              Does that not work in 11.1.1.3?
              • 4. Re: ASO Load Multiple files at same time - 11.1.1.3
                18_4_Ever
                Yes. I'm looking at tech ref and I think I was committing the buffers on each and then when the first one released it hosed up the buffer count/availability. Do you know if there is a max of 3 buffers? Once I try to goto 4 I get:

                Error 1270027 - Cannot proceed while the cube is being loaded.
                • 5. Re: ASO Load Multiple files at same time - 11.1.1.3
                  TimG
                  Do you mean when you create the buffers? I hadn't noticed / didn't know of a limit. Can you create all the buffers first in a single script to create more than three and then run the loads against them?

                  Out of interest, how much data are you loading (i.e. do you actually have a load performance problem relating to getting the data into the buffer, rather than at the commit stage)?
                  • 6. Re: ASO Load Multiple files at same time - 11.1.1.3
                    sunil k
                    Hi Tim,

                    That should work

                    I was actually trying to explain it from the point of operation exclusivity and concurrency (different sessions ) and this is what i went by

                    -----
                    Data load (committing the contents of a load buffer to the database)
                    Creating an aggregate storage load buffer and loading data into the load buffer are not exclusive operations. These operations can run concurrently with any other operations. However, committing the data in the load buffer to the database is an exclusive operation.
                    -----

                    Nevertheless what you suggested will work. However, it would interesting to see how we achieve concurrent load to various buffers. Single mxl script will execute statements one after another so there will be no concurrency during load to buffers (not sure if something is introduced for parallel loads in latest version). If we decide to achieve it through multiple mxl scripts, i am not sure if buffers will be available across sessions so a concluding script can load by a single statement.

                    Interesting that we were discussing something similar yesterday as well on a different post :)

                    Regards,
                    Sunil
                    • 7. Re: ASO Load Multiple files at same time - 11.1.1.3
                      TimG
                      Yes, how you run this is problematic. I am 99.999% certain that the buffers will be available across sessions. There is a MaxL command (display database?) that lets you see currently existing buffers, which suggests to me that they are shared. Frustratingly, I'm not in front of a system I can test on right now! :)
                      • 8. Re: ASO Load Multiple files at same time - 11.1.1.3
                        sunil k
                        Are you explicitly destroying the buffers after you are done with it? Something like

                        alter database 'app'.'db' destroy load_buffer with buffer_id 1;

                        Also resource_usage is very important parameter to manage while trying to use multiple buffers

                        However you error seem to suggest that you already have a load operations (import database) in progress on your cube - you may want to check active sessions while this error is being thrown. Import database being a exclusive operation may not allow other operation like initializing a buffer.

                        Regards,
                        Sunil
                        • 9. Re: ASO Load Multiple files at same time - 11.1.1.3
                          18_4_Ever
                          Yes. We have 24 2GB files to load. Trying to cut down on the time it takes to load them.
                          • 10. Re: ASO Load Multiple files at same time - 11.1.1.3
                            GlennS_3
                            In the past, I have had issues trying to run two different load rules that used the same ODBC connection, even to different applications. I got around it by creating multiple ODBC connections to the same driver and it using a differnt connection name in each load rule. You might try that.
                            • 11. Re: ASO Load Multiple files at same time - 11.1.1.3
                              DanPressman
                              The code for loading multiple flat files at the same time is in the techref (see bottom).

                              But you also should consider four other methods to speed your data loads:

                              1. MAKE CERTAIN that your default and tmp tablespaces are on different hard drives, SANs or Channels. This alone will reduce load times 40%

                              2. Increase DLSINGLETHREADPERSTAGE, DLTHREADSPREPARE, DLTHREADSWRITE to allow parallel processing within each of your load files

                              3. Sort your data - if you either:

                              a. Have multiple input records hitting a single cell
                              b. Have multiple input records hitting a cells on the same compression dimension

                              This will minimize the times that ASO has to find the and combine cells or (or ensure that last loaded works depending on settings) or combine cells onto the same compression record. If sorted to be near each other they will still be in the aso cache or memormy-mapped io area.

                              4. REDUCE your file sizes two ways:

                              a. create short aliases for all or at least some of your most used members - you can do it in a second or third alias member that the users do not see and then write your data using these member names

                              b. After sorting your files create them in the Essbase Output Format (actually this is suggested in the documentation) where you do not repeat member names that have not changed since the previous record. This is now trivial to do in SQL Server 2012 with the LAG function.



                              Now you will need to balance wheter multiple parallel buffer loads or increasing DLPrepareThreads is best based on your hardware. It will depend on the number of cpu cycles available. On machines without a large number of processors you will get as much improvement by simply increasing DLPrepare as adding the parrallel loads.


                              Finally if you still have CPU cycles left over or maybe instead of parallel or major increases in DLPREPARETHREADS you can redue the apparant disk size of your files by using Windows compression on them. Additionally you can set the Default and Tmp tablespace directories to COMPRESSED. Essbase will not know the difference because the op system will have re-expanded them. I have tried it and it works !






                              Example: Perform Multiple Data Loads in Parallel

                              1.
                              In one MaxL Shell session, load data into a buffer with an ID of 1:
                              alter database AsoSamp.Sample
                              initialize load_buffer with buffer_id 1 resource_usage 0.5;
                              import database AsoSamp.Sample data
                              from data_file "dataload1.txt"
                              to load_buffer with buffer_id 1
                              on error abort;
                              2.
                              Simultaneously, in another MaxL Shell session, load data into a buffer with an ID of 2:
                              alter database AsoSamp.Sample
                              initialize load_buffer with buffer_id 2 resource_usage 0.5;
                              import database AsoSamp.Sample data
                              from data_file "dataload2.txt"
                              to load_buffer with buffer_id 2
                              on error abort;
                              3.
                              When the data is fully loaded into the data load buffers, use one MaxL statement to commit the contents of both buffers into the database by using a comma separated list of buffer IDs:
                              import database AsoSamp.Sample data
                              from load_buffer with buffer_id 1, 2;
                              • 12. Re: ASO Load Multiple files at same time - 11.1.1.3
                                GlennS_3
                                Dan,
                                Are you telling me the documentation is wrong? According to the tech reference, DLThreadsprepare is valid for ASO cubes but DLWritethreads is not.
                                • 13. Re: ASO Load Multiple files at same time - 11.1.1.3
                                  DanPressman
                                  No - I did not know that WRITE did not apply to ASO until recently and simply forgot it. You are as always correct!!!
                                  • 14. Re: ASO Load Multiple files at same time - 11.1.1.3
                                    18_4_Ever
                                    Hi,

                                    Where would the default and tmp tablespace settings reside and how do you change them? I'm browsing EAS and can't find anything.
                                    1 2 Previous Next