7 Replies Latest reply on Nov 13, 2012 7:17 PM by 951691

    Sparse Member deletion


      I have three members in my version dimension(sparse)

      Data present for all three versions.

      After closure of the budget cycle ,deleted the draft2 version from planning.

      I was hoping that it would reduce the .PAG file size in essbase, but it failed to do so.

      Can anyone help on how to reduce the .PAG size.

        • 1. Re: Sparse Member deletion
          Jake Turrell
          When you deleted the sparse member, Essbase executed a "sparse" restructure, which is basically a re-write of the index file. Essbase doesn't bother with the page file in this situation.

          There are several options available to you, but the easiest is probably write a calc script that includes "CLEARBLOCK EMPTY;". (without the quotes obviously) This will remove all empty blocks.

          Here's a quote from the Tech Ref that you may be relevant:

          •If you use CLEARBLOCK EMPTY, the resulting, smaller database can be processed more efficiently; however, the CLEARBLOCK EMPTY process itself can take some time, depending on the size and density of the database.

          Hope this helps,
          - Jake
          • 2. Re: Sparse Member deletion
            Jake Turrell
            Another option in MaxL: alter database Sample.Basic force restructure;

            You'll need to replace the app name and database name above.
            • 3. Re: Sparse Member deletion
              Hey Jake

              Thanks you very much for your reply.

              I was aware of the forced restructure option, but was not sure of the time and so i have to take a down time.

              Regarding the CLEARBLOCK EMPTY, i was not quire sure. I knew that it removed empty blocks but here the blocks are not present.(i mean physically)
              Can you please throw some light into this. should i FIX other dimensions while executing the CLEARBLOCK EMPTY.

              • 4. Re: Sparse Member deletion
                Jake Turrell
                I think you're correct. CLEARBLOCK EMPTY probably will not solve your issue. While the blocks are physically there, because they aren't referenced in the Index (because the version was deleted), I don't think they're "logically" there . . . if you know what I mean.

                Dense restructure is probably your best bet, although you correctly note that downtime is required.

                Another option is export data, CLEARBLOCK ALL, then reimport the data. Not sure if this would be faster than the dense restructure though.

                - Jake
                • 5. Re: Sparse Member deletion
                  Hi Jake,

                  Thanks for your clarification.

                  I still have a few questions.
                  1.By export did you mean full database export or only level zero.

                  2.can you please specify the downtime i would require.I have nearly 10 Gb data (Though i guess after deleting the version it would be around 7 GB)
                  I know it is not possible to tell the exact time required ,still a fair idea would help me out.

                  • 6. Re: Sparse Member deletion
                    Jake Turrell
                    Do you ever enter data at upper levels? (with a Target Version) If not, then you can probably get away with a level 0 export. But you would have to be CERTAIN that you could run whatever calculations you need to get back to your current state, especially for historical data that may not have been calculated in a while. A full export would be the "safest" option, but will take a long time and will require additional disk space.

                    Do you not have a maintenance window where you can do a dense restructure? What do you do when you need to add an account? (Assuming Account is dense.) Do you have a development environment available?

                    Regarding how long an export or restructure might take, there are just too many variables for me to even hazard a guess.
                    • 7. Re: Sparse Member deletion
                      Hi Jake ,

                      I can surely do a dense restructure. But it would not remove the empty blocks. And my page file size will not be affected.

                      I am not quite aware of the risks of a forced restructure. So would do it in a maintenance server first.

                      Your help is greatly appreciated.

                      Thanks and Regards,