5 Replies Latest reply on Apr 7, 2016 3:17 PM by JustTheFacts

    Dimension does not aggregate

    kzane

      Hello everyone,

       

      when I add a certain dimension to my report, it is not aggregated. This means that the records with same dimension value are repeated. Example (without the column in question):

       

      CompanyCountryEmployee Count
      Company1England3
      Company1Norway2
      Company1Sweden5
      Company1France7

       

      Now when I add this specific column, for example in my case 'Department', this happens (taking only England as an example):

      CompanyCountryDepartmentEmployee Count
      Company1EnglandFinance1
      Company1EnglandFinance1
      Company1EnglandSales1

       

      Why does the Finance department not aggregate Employee Count to 2, like this:

      CompanyCountryDepartmentEmployee Count
      Company1EnglandFinance2
      Company1EnglandSales1

       

       

      I'm a bit lost as to where I should begin looking at what might be causing this problem. Any help or tip is appreciated.

       

      Thank you

        • 1. Re: Dimension does not aggregate
          SriniVEERAVALLI

          You need to look into join and content level for fact with department.

          1 person found this helpful
          • 2. Re: Dimension does not aggregate
            JustTheFacts

            kzane,

             

            Srini is correct - examining your fact-dimension joins and the levels (on the "Content" tab) of your fact source(s) is a good start. Whenever you have aggregation weirdness, those are places to look. In addition, I would look at the dimension hierarchy for your Department dimension and specifically I would look at dimension keys. If something like "Department ID" is specified as the dimension level key, but "Department Name" (what you are showing in the report) is specified as column at that same level of the hierarchy (possibly also marked as a "use for display" key at that level), then you could see this behavior if there were multiple (sub?) departments with different IDs but which all carried the name "Finance".

             

            That's a lot of words, so here's a specific example. I'm going to assume that your Department hierarchy has three levels:

             

            Total (no key)

            Company (Key = Company Name)

            Department (Key = Department ID, Display Key = Department Name)

             

            Furthermore, in order to "break" the report the way you are seeing I'm going to assume that "Department ID" is not 1:1 with Department Name. That is, different Department IDs might carry the same Name. Like so:

             

            Company     Dept ID     Dept Name
            Company1     123          Finance

            Company1     456          Finance

            Company1     789          Sales

             

            In this case, if you ask OBIEE to aggregate numbers at the Department level of the hierarchy, it must include Department ID in the GROUP BY part of the query, because you told it that Dept ID is the key of that level of the hierarchy. Even though Dept Name is what you want to show on the report (and it is the Display Key of the hierarchy level), OBIEE thinks that only Dept ID can uniquely identify individual Departments, so it breaks it out by that column.

             

            The fix in this case is either a) to add a level to the hierarchy for Department Name, between Company and Department (maybe rename to Sub-Department?) or b) remove Dept ID from the hierarchy and just make Dept Name the primary key at that level.

             

            Hope that helps.

            1 person found this helpful
            • 3. Re: Dimension does not aggregate
              kzane

              Thanks justthefacts,

               

              this was exactly the reason:

              justthefacts wrote:

               

              I would look at the dimension hierarchy for your Department dimension and specifically I would look at dimension keys. If something like "Department ID" is specified as the dimension level key, but "Department Name" (what you are showing in the report) is specified as column at that same level of the hierarchy (possibly also marked as a "use for display" key at that level), then you could see this behavior if there were multiple (sub?) departments with different IDs but which all carried the name "Finance".

               

              Dept Name to Dept ID was definitely not 1:1. In addition to that, the primary key for the dimension level was the Dept ID while Dept Name was set to 'Use for Display'. So it makes sense why there were multiple entries for a single Dept Name. If I set the Primary Key for the Dimension Level to Dept Name, then it works as expected. Thanks for that!

               

              My following question would be, what is the best way I can make this work without changing the existing dimension? Our system has a lot of reports and there might be a specific reason why someone set that Dimension Level like it was (Dept ID as primary key). So I would prefer to keep this dimension as it is. Do I just make a whole new dimension and connect it to a copy of the KPI used for that report?

              • 4. Re: Dimension does not aggregate
                JustTheFacts

                You should be able to get the results you want by using the AGGREGATE() function. Try using this formula for your measure:

                 

                AGGREGATE(EmployeeCount BY Company, Country, Dept Name)

                 

                I think that will force it to use Dept Name as the 'group by' column, instead of the defined dimension key. If that doesn't work, you might have to get more devious with your report design.

                1 person found this helpful
                • 5. Re: Dimension does not aggregate
                  JustTheFacts

                  Also, for what it's worth, it would be surprising to me if your dimension hierarchy is supporting any useful behavior in the front end. As you have seen, unless Dept ID is actully on the report, the Dept Name gives almost no useful information to the end user. You see several "Finance" but which is which? You'd have to add Dept ID to the report to know, and when you do that you're showing the One True Key at the department level anyway.

                   

                  Depending on who you talk to, the way your Department keys are set up now range from "probably not a good idea" to "WRONG! WRONG! WRONG!" :-) I lean towards the first interpretation. I mean, who knows? Maybe someone in your user community really needs to see reports broken out with three identical "Finance" rows with no way to tell them apart? I might question that person's sanity, but if it's a user requirement and you can't convince them otherwise, you have to support it. I have users like that. :-)

                   

                  I'd be interested to know how many reports have Dept Name on them without also having Dept ID, and how the users interpret those reports?

                   

                  One final note about "Use For Display" keys. These are mostly used to support natural-drill interactions. So you build a report with Company on it, and OBIEE automatically makes each Company a link which drills down to Department. OBIEE will use the "Use For Display" key, if it exists, when it drills to the Department level (but behind the scenes, the actual primary key will be in the GROUP BY of the query). When you are hand-building reports you can obviously choose whichever columns you want to repressent Department, and it looks to me like Dept ID & Dept Name are both required for the results to be meaningful.

                  1 person found this helpful