10 Replies Latest reply: Jan 21, 2013 12:08 PM by 985501 RSS

    ASO cube - Problem in data aggregation

    985501
      Hi

      I have a ASO cube, which has 15 dimensions and has about 4 millions data in it. In order for aggergation of the cube we have a .msh script which is run by a batch script once the data load completes. The cube has a table space of 20GB allocated.

      Now, when i check the logs i can see that the data aggregation is done but when i check in the cube the number of aggregated cell is 0.

      The Script used for aggregating is as given

      execute aggregate selection on database xyz.xyz stopping when total_size exceeds 400;
      execute aggregate process on database xyz.xyz stopping when total_size exceeds 400;


      I am not able to understand what is causing the cube not to aggregate the cells.
      Please reply soon what is the problem and the solution as it is a production issue.
      Do i need to increase the table space or do i have to so a change in the script?
      Thanks in advance.

      Thanks
      Jun
        • 1. Re: ASO cube - Problem in data aggregation
          name_and_address_withheld
          Did the log show how many aggregated views were created?

          Is it possible to change 400 to something like 4 and see what happens?
          • 2. Re: ASO cube - Problem in data aggregation
            TimG
            It would be very useful to see the output of these commands; I assume you have a log file you could extract this from and post here?

            However, a couple of observations...

            First, the first statement in your script seems redundant in that the 'process' command both selects and builds views.

            Secondly, the 'stopping value' is specified as a factor of the input data. '400' means you are trying to select views such that the size of the database will increase to 400 times the size of the level-zero input data which is an exceptionally high value - it wouldn't surprise me if this is creating a problem. Unless your input data is very small (<50MB, which is probably impossible given 4m input cells and the key length plus data values) a 400x increase is going to exceed your available space.
            • 3. Re: ASO cube - Problem in data aggregation
              985501
              Hi

              Please check the logs.

              -------------------------------------------------------------------
              Start time for batch file logging 1:00:00.50 Fri 01/18/2013
              -------------------------------------------------------------------
              Metadata Load Process started... 1:00:03.02 Fri 01/18/2013
              Disabling the primary connect 1:00:03.03 Fri 01/18/2013
              Started xyz Metadata load: 1:00:04.28 Fri 01/18/2013
              xyz Metadata load completed: 1:07:14.54 Fri 01/18/2013
              Calling xyz data load: 1:07:14.60 Fri 01/18/2013
              Started xyz data load: 1:07:14.63 Fri 01/18/2013
              xyz data load done : 1:32:46.40 Fri 01/18/2013
              Started xyz data aggregation: 1:32:46.45 Fri 01/18/2013
              xyz data aggregation Done: 1:32:46.45 Fri 01/18/2013
              --------------------xyz Script Executed--------------- : 2:11:21.61 Fri 01/18/2013



              What i can find out is that since the table space is allocated as 20GB, i guess the aggregation is happening till 20GB and then it is getting stopped. The logs are not showing any error in aggregation. I guess partial aggregation is happening. Please check the logs once and let me know if you are seeing any anomally.

              Thanks
              Jun
              • 4. Re: ASO cube - Problem in data aggregation
                name_and_address_withheld
                That looks like a custom log, unless things are different in the land beyond v9. Have you checked the actual application log? Also, as TimgG suggested it probably is too much aggregation for the disk space available, so try testing a smaller number (e.g. 4 instead of 400).
                • 5. Re: ASO cube - Problem in data aggregation
                  TimG
                  Yeah, what NAAW said.

                  That's not an Essbase or MaxL log in any version. All I can tell from that is that your aggregation is suspiciously fast (<1ms). Unless your custom logging is incomplete or faulty.

                  EDIT -- Scratch my comment on load times, I misread the start / end times. Loading to a slice is probably not the problem (it would prevent aggregation selection / processing without a merge).

                  Post the entire MaxL script or scripts - not just the two aggregation lines - and post the actual MaxL output. If you can't do that (though if you can't, how can you be supporting this application?) post the relevant chunk of the Essbase application log.
                  • 6. Re: ASO cube - Problem in data aggregation
                    985501
                    Hi TimG


                    The below are the logs for the application

                    *[Mon Jan 21 01:33:32 2013]Local/XYZ/XYZ/admin/Info(1013091)*
                    *Received Command [AsoAggregateClear] from user [admin]*

                    *[Mon Jan 21 01:33:32 2013]Local/XYZ/XYZ/admin/Info(1013273)*
                    Database XYZ.XYZ altered

                    *[Mon Jan 21 01:33:32 2013]Local/XYZ///Info(1013210)*
                    *User [admin] set active on database [XYZ]*

                    *[Mon Jan 21 01:33:32 2013]Local/XYZ/XYZ/admin/Info(1013091)*
                    *Received Command [AsoAggregateSelect] from user [admin]*

                    *[Mon Jan 21 01:40:02 2013]Local/XYZ/XYZ/admin/Info(1270046)*
                    Selected [2048] new aggregate views. Elapsed time [390.189] sec

                    *[Mon Jan 21 01:40:03 2013]Local/XYZ///Info(1013210)*
                    *User [admin] set active on database [XYZ]*

                    *[Mon Jan 21 01:40:03 2013]Local/XYZ/XYZ/admin/Info(1013091)*
                    *Received Command [AsoAggregateProcess] from user [admin]*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ/XYZ/admin/Error(1270006)*
                    *Failed to extend tablespace [default]. Please see application log for details*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ/XYZ/admin/Info(1270077)*
                    *Failed to extend file [E:\Cubes_data_temp\XYZ\default\ess00001.dat]: user-specified file size limit reached*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ/XYZ/admin/Info(1270074)*
                    *Failed to create file in file location [E:\Cubes_data_temp]: user-specified disk size limit or file count limit reached*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ///Info(1013205)*
                    *Received Command [SetApplicationState]*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ///Info(1019010)*
                    *Writing Application Definition For [XYZ]*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ///Info(1019011)*
                    *Writing Database Definition For [XYZ]*

                    *[Mon Jan 21 02:11:31 2013]Local/XYZ///Info(1019022)*
                    *Writing Database Mapping For [XYZ]*

                    *[Mon Jan 21 02:11:32 2013]Local/XYZ///Info(1013214)*
                    *Clear Active on User [admin] Instance [1]*

                    As i said its a issue with the table space size. But i want to know is there any alternative to handle this issue without increasing the size of the table space.


                    This is the msh script which is been use to do the aggregation.*


                    ++Alter database XYZ.XYZ clear aggregates;++


                    ++execute aggregate selection on database XYZ.XYZ stopping when total_size exceeds 400;++
                    ++execute aggregate process on database XYZ.XYZ stopping when total_size exceeds 400;++

                    ++alter application XYZ enable connects;++
                    ++logout;++


                    Let me know your ideas.

                    Thanks
                    Jun

                    Edited by: 982498 on Jan 29, 2013 2:15 AM
                    • 7. Re: ASO cube - Problem in data aggregation
                      TimG
                      Thanks Jun, that's much more useful.

                      So you are clearing the aggregations, (redundantly) selecting aggregations and then performing an aggregation 'process' (which both selects and builds).

                      If you really can't increase the tablespace, you must reduce the 'stopping' value to allow the data to fit into the available tablespace. If your temp and default tablespaces are of different sizes, I would think the data must fit into the smaller of the two, since the temp tablespace is used during aggregation processing.

                      As I said above, the 'stopping' value in the aggregate process statement is expressed as a factor of the input data size. You need to figure out how large your input data is (look at database properties in EAS) and then work out what multiple will fit. It will likely be much smaller than 400! Remember that you can also use decimals if necessary, e.g. 1.5 will design aggregations up to half as large as the input data. I would also allow a healthy margin to account for data growth and also because I think the size estimates generated during the aggregate selections are just that - estimates.

                      For example, if your input data is 5GB and you only have 20GB available, try a stopping value of 3, so that the total database size should not exceed (approximately) 15GB. Also remember that you should be designing aggregations in conjunction with query performance testing, not in isolation.

                      As an aside, I'm surprised to see the selection defining 2048 aggregations, since the DBAG says that no aggregation can contain more than 1023 views. Without wishing to be rude, I don't think whoever wrote this script was 100% sure of what they were doing!
                      • 8. Re: ASO cube - Problem in data aggregation
                        TimG
                        Incidentally, you might want to take your password out of your post.
                        • 9. Re: ASO cube - Problem in data aggregation
                          DanPressman
                          As ting says the size limit is not exact it starts the script and then aggs the fees in script using the estimated view sizes. View size eat are based on a saple of data so are not perfect.

                          To increase the size of the aggregated cube if in windows stop application and in windows explored go to app/appname/dynamo and select advanced then turn on compression repeat for app/temp directory
                          • 10. Re: ASO cube - Problem in data aggregation
                            985501
                            Thanks TimG for your comments.....it was very much helpfull for me.....:)