3 Replies Latest reply: May 13, 2013 2:20 PM by Nasar-Oracle RSS

    How to get OLAP cube objects size info?

    987709
      Hi,

      I am new at OLAP cube.

      I want to know OLAP Cube objects size information like VIEW.

      is any query to get size of Cube objects?

      Please help me out on this.

      Thank you,
      Nitin
        • 1. Re: How to get OLAP cube objects size info?
          Nasar-Oracle
          Nitin,

          This will give you some idea.

          *(1). Size of the whole AW:*

          select dbal.owner||'.'||substr(dbal.table_name,4) awname,
          to_char(round(sum(dbas.bytes)/1024/1024, 1), '999,999.9') as mb, dbas.tablespace_name, logging
          from   dba_lobs dbal, dba_segments dbas
          where  dbal.column_name = 'AWLOB' and dbal.segment_name = dbas.segment_name
          group by dbal.owner, dbal.table_name, dbas.tablespace_name, logging
          order by sum(dbas.bytes) desc

          .

          *(2). Size of internal objects:*
          Each cube has an associated _STORED variable and a 'PXX' partition.
          _PRTCOMP is cube's partition's composite dimension.
          _AGGMAP  is the aggregation map that is used during aggregation of data.

          Replace table AW$BNSOLAP with the name of your AW table.

          select partname,
          round(sum(dbms_lob.getlength(awlob))/1024,0) KB
          from bawolap.aw$bnsolap
          where extnum = 0
          group by partname
          order by kb desc
          +;+
          • 2. Re: How to get OLAP cube objects size info?
            987709
            Hi Nasar,

            Thank you for your update.

            provided queries are returning data by schemas AWNAME and its size. it will give whole AW.

            but i need to find cube object (only one object) size. it may view etc.

            Please do needful

            thank you,
            Nitin
            • 3. Re: How to get OLAP cube objects size info?
              Nasar-Oracle
              You can do a WHERE condition on partname.

              something like
              WHERE PARTNAME LIKE '%CUBE%'
              where CUBE is the name of your cube.

              This will give you the size of objects related to that cube. You can then sum up the sizes.

              Hopefully someone else from OLAP development team can provide a better query.