1 Reply Latest reply on Aug 21, 2012 7:50 AM by Shankar S.-Oracle

    OLAP summation Problem


      If a OLAP cube is build on 4 dimension and in a report we only put filter on 2 or 3 dimension then the report not showing the correct value what it should be.

      Ex :

      Dimension :

      If i choose filter on 2 or 3 dimension then the report is not showing the correct value. If i choose all the filter (4 filter) then it is showing the correct data. Means CUBE wrongly derive the data when any of the filter codition is missing.

      Please let me know if you require anything more regarding this issue then i can provide the data sample in details.

      Pallab Mandal
        • 1. Re: OLAP summation Problem
          Shankar S.-Oracle
          If you miss out dimensions in the report then it's the responsibility of the reporting tool (via tool model/metadata) to (transparently) insert filters for the missing dimension so that the sql being sent to the database is a fully qualified olap sql query.

          For example, using OBIEE as the reporting tool, the metadata built or setup in the obiee repository helps bring in the missing dimensions automatically in the report query sent to the database. More details at: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/olap/biee/index.html (repository setup is part of first lesson).

          If you're writing some kind of sql or using a reporting tool which does not contain an useful/intelligent middle layer (akin to the rpd in obiee) then i guess you are required to NOT miss dimensions in your query and to write or setup a more usable report which does include all dimensions. Missing out a dimension will force the sql results to come for all the members of the missing dimension and would still be correct but the flaw is more to do with "improper report specification" than "failure to return correct results".

          NOTE: There are options like using Microsoft XL add-in for Oracle OLAP provided by Simba Technologies and OBIEE or OBIEE version which support native access to OLAP. Using either of these modes of access to Oracle OLAP Analytical Workspace, you are not constrained to refer to all dimensions.