ANOTHER drill down problem :(

[rodriguezum]

hi, when i design my cube in my my clients and personnel dimensions i have their names in given_name, family_name and mother_name and i wish i could join them in jasperanalysis with mondrian. And wen i am using jpivot in JS it works and shows the full name, the problem is when i try to drill down, it give me an error of ambiguosly defined column.

 

Can anyone help please

[swood]

Can you show your schema?

 

Are you using NameExpression or KeyExpression attributes in the schema?

 

Sherman

Jaspersoft

[rodriguezum]

yes that is the problem, whenever i use key name ordinal expression the drill down just doesn't work anymore, cause of the ambiguous comlumn error. Like i said i have given family and mother name, and i wanted to show the client full name for obvious reasons, without having to perform 3 levels in hierarchie, but still be able to drill down.

Heres the schema.

THank you for your reply

[swood]

Hmm, this looks like a bug. It should not matter that you are using a KeyExpression.

 

Can you show the SQL that is failing?

 

Sherman

Jaspersoft

[rodriguezum]

Don't know about the sql but this is the schema definition for that particular class,

  <Dimension type="StandardDimension" name="PERSONNEL">
    <Hierarchy name="PERSO" hasAll="true" allMemberName="TODOS" allMemberCaption="primer nombre" primaryKey="SK" caption="Funcionarios">
      <Table name="DIM_PERSONNEL" schema="TICARES_DW_MIG">
      </Table>
      <Level name="PERONNEL_TYPE" column="PERSONNEL_TYPE" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
      </Level>
      <Level name="PERSONNEL" column="GIVEN_NAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
      </Level>
    </Hierarchy>
    <Hierarchy name="PERSONNEL" hasAll="true" allMemberName="TODOS" allMemberCaption="todosnombres" primaryKey="SK">
      <Table name="DIM_PERSONNEL" schema="TICARES_DW_MIG">
      </Table>
      <Level name="PERONNEL_TYPE" column="PERSONNEL_TYPE" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
      </Level>
      <Level name="name" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
        <KeyExpression>
          <SQL dialect="oracle">
            <![CDATA["GIVEN_NAME" || ' ' || "FAMILY_NAME" || ' ' || "MOTHER_NAME"]]>
          </SQL>
        </KeyExpression>
        <Property name="PHONE" column="PHONE" type="String">
        </Property>
        <Property name="ACTIVE" column="ACTIVE" type="Boolean">
        </Property>
        <Property name="EXTERNAL" column="EXTERNAL" type="Boolean">
        </Property>
      </Level>
    </Hierarchy>
  </Dimension>

the problematic hierarchy is PERSONNEL, but i printed the hould dimension just in case, in attachment i placed a couple of pictures one befor the drill down where we can se all the full names correctly printed, and the other is when i click on a value to drill down. both with the tomcat window on the side, don't know if it helps

 

Don't know how i can show the sql, anything else you need i'll be happy to oblidge, cause this is a problematic issue for me

 

[swood]

I think I see the problem here. It may be Oracle specific.

 

You are doing a "drill through" to the underlying facts, not  a "drill down". Drill down means "go to the next level below the current one".

 

The drill through does a join across the fact table and all the dimension tables and makes the column names in the result be the names of the measures and levels. In your case, you have 2 levels called PERONNEL_TYPE, so the generated SQL has 2 columnns called PERONNEL_TYPE too, which Oracle does not like. Try commenting out the PERSO hierarchy in the schema, or renaming the PERONNEL_TYPE level in one of the hierarchies.

 

This is only an issue with drill through - as you saw, everything else should be working.

 

Sherman

Jaspersoft

[rodriguezum]

I'm sorry for the "drill Down" mistake, i meant drill through, i this the problem has nothing to do with personnel_type, since its merely a column in the database, and i've called the same level many many times in other hierarchies, lets say the time dimension, where the year has been predominantely my first level in many hierarchies. I can drill through the first hierarchie, where in the last level is just shown the first name, the problem is that i need all three names, otherwise all employes with the same name will be merged, and my measures get messed up, so the although i can show the full person names when i drill down in the second hierarchie, when i drill through it throws me the oracle error. 

I tried to follow the foodmart example, but just can't seem to get it right. Could this be Oracle specific?, please advice thanks in advance

Post Edited by rodriguezum at 08/03/2009 07:31

[swood]

I said:

"Try commenting out the PERSO hierarchy in the schema as a test, or renaming the PERONNEL_TYPE level in one of the hierarchies."

 

Sherman

Jaspersoft

[rodriguezum]

:( didn't work, actually i have another dimension that has two hierarchies, one wich reaches down two the full name, and another the sex, none of the levels are the same, and the problem persists. In each dimension i tried a different aproach for displaying the full name, one with a key expression, and another with name and ordinal expression, thw two ways i found in the foodmart example.

Both work in Drill down, but when it comes to drill through, the result is the same  JSException: ORA-00918:  ambiguous column.

Can you tell me if this is Oracle specific??

 

Thanks

[swood]

Hmmm, making the names unique should have fixed it.

 

Can you show me the SQL that fails on the drill through? You may have to turn on this logging in WEB-INF/log4j.properties

 

log4j.logger.jasperanalysis.drillthroughSQL=DEBUG

 

Sherman

Jaspersoft