Forum Stats

  • 3,874,160 Users
  • 2,266,675 Discussions
  • 7,911,745 Comments

Discussions

generated SQL querys are different between DEV and PROD environment

Hi,

I have a problem with the results of querys in Answer, I get different result with the same repository, same query in Answers(catalog), same OS... between DEV environment and PROD environment. The generated SQL querys are also different.

The BI vesion is 10.1.3.4

Any idea about this question?

Regards

Guillermo
Tagged:

Best Answer

  • gerardnico
    gerardnico Member Posts: 1,465
    Answer ✓
    You have two query in dev because you use for one logical table two source table :
    * VEIS_HECHOS_ACUMULADOS
    * and VEIS_HECHOS_GLOBAL

    How OBIEE select this tables ? This tables are selected if :
    * all columns in the answer belongs to the source table
    * the logical level of each source table is set with the same parameters for the two source table

    It's the case for you ?
«1

Answers

  • William Lesmana
    William Lesmana Member Posts: 135
    edited Oct 16, 2009 3:23AM
    Hi-

    Are you using Essbase as data source? Check the version of ur Dev and Prod.
    If different, probably this would help: [http://hekatonkheires.blogspot.com/2009/07/obiee-essbase-mdx-generation-issues.html]

    -Will
  • 649804
    649804 Member Posts: 196
    Check the NQSConfig.ini and instanceconfig.xml file if there is any difference or not.
  • No, the data source is a database transactional.
  • The configuration files are equal in both environments.
  • gerardnico
    gerardnico Member Posts: 1,465
    What is the difference ?
    Can you give us a SQL statement in prod and an other in dev.

    Cheers
    Nico
  • I attach the generated querys here.

    The result obtained in the production environment is wrong, not getting some columns(empty columns) that contain functions than TODATE AND AGO, but the querys are the same in Answer.

    thanks a lot.

    Guillermo


    DEV:

    +++ucargodes:2a520000:2a520002:----2009/09/17 12:27:58

    -------------------- Sending query to database named CGO_EIS_Gestion (id: <<7905614>>):
    select D1.c3 as c1,
    D1.c4 as c2,
    D1.c5 as c3,
    D1.c6 as c4,
    D1.c7 as c5,
    D1.c2 as c6,
    D1.c1 as c7
    from
    (select sum(T29909.DIF_NETO_NETO_REC_EUROS_A1) as c1,
    sum(T29909.NETO_NETO_REC_EUROS) as c2,
    T30214.YEAR as c3,
    T30214.MES as c4,
    T30452.CITY_DSC as c5,
    T30452.AGENTE_DSC as c6,
    T30452.AGNT_ID as c7
    from
    VEIS_AGENTE_VIEW T30452,
    VEIS_TIEMPO T30214,
    VEIS_HECHOS_GLOBAL T29909
    where ( T29909.AGNT_ID = T30452.AGNT_ID and T29909.FECHA_EMI = T30214.BT_DATE and T30214.YEAR = 2009 and T30452.CITY_DSC = 'Lisbon' and T30214.YEAR <> '0001' )
    group by T30214.MES, T30214.YEAR, T30452.AGENTE_DSC, T30452.CITY_DSC, T30452.AGNT_ID
    ) D1


    +++ucargodes:2a520000:2a520002:----2009/09/17 12:27:58

    -------------------- Sending query to database named CGO_EIS_Gestion (id: <<7905670>>):
    select D2.c3 as c1,
    D2.c4 as c2,
    D2.c5 as c3,
    D2.c6 as c4,
    D2.c7 as c5,
    D2.c2 as c6,
    D2.c1 as c7
    from
    (select sum(T30204.DIF_NETO_NETO_REC_EUROS_A1) as c1,
    sum(T30204.ACU_NETO_NETO_REC_EUROS) as c2,
    T30214.YEAR as c3,
    T30214.MES as c4,
    T30452.CITY_DSC as c5,
    T30452.AGENTE_DSC as c6,
    T30452.AGNT_ID as c7
    from
    VEIS_AGENTE_VIEW T30452,
    VEIS_TIEMPO T30214,
    VEIS_HECHOS_ACUMULADOS T30204
    where ( T30204.AGNT_ID = T30452.AGNT_ID and T30204.ANNO_EMI = T30214.YEAR and T30204.ANNO_EMI = 2009 and T30214.YEAR = 2009 and T30452.CITY_DSC = 'Lisbon' and TO_NUMBER(TO_CHAR(T30214.BT_DATE, 'dd'), '99') = 1 and T30214.YEAR <> '0001' )
    group by T30214.MES, T30214.YEAR, T30452.AGENTE_DSC, T30452.CITY_DSC, T30452.AGNT_ID
    ) D2


    PROD:

    +++MATCHM.gropa:5880000:5880001:----2009/09/17 12:35:53

    -------------------- Sending query to database named CGO_EIS_Gestion (id: <<17455093>>):
    select distinct D1.c3 as c1,
    D1.c4 as c2,
    D1.c5 as c3,
    D1.c6 as c4,
    D1.c7 as c5,
    D1.c2 as c6,
    case when D1.c2 - D1.c1 <> 0 then D1.c1 / decode(D1.c2 - D1.c1 , 0 , to_number(null), D1.c2 - D1.c1) * 100 else NULL end as c7
    from
    (select sum(T73036.DIF_NETO_NETO_REC_EUROS_A1) as c1,
    sum(T73036.NETO_NETO_REC_EUROS) as c2,
    T76571.YEAR as c3,
    T76571.MES as c4,
    T76419.CITY_DSC as c5,
    T76419.AGENTE_DSC as c6,
    T76419.AGNT_ID as c7
    from
    VEIS_TIEMPO T76571,
    VEIS_AGENTE_VIEW T76419,
    VEIS_HECHOS_GLOBAL T73036
    where ( T73036.AGNT_ID = T76419.AGNT_ID and T73036.FECHA_EMI = T76571.BT_DATE and T76419.CITY_DSC = 'Lisbon' and T76571.YEAR = 2009 and T76571.YEAR <> '0001' )
    group by T76419.AGENTE_DSC, T76419.CITY_DSC, T76419.AGNT_ID, T76571.MES, T76571.YEAR
    ) D1
  • gerardnico
    gerardnico Member Posts: 1,465
    edited Oct 16, 2009 8:12AM
    Why do you send two query for the dev and one for the prod.
    I take the first one of the test to compare it with the production.

    I bet that your repository are not the same because you don't have the same id for the object.

    In the production environment, you have a formula in the AGENT_ID column
    case when D1.c2 - D1.c1 0 then D1.c1 / decode(D1.c2 - D1.c1 , 0 , to_number(null), D1.c2 - D1.c1) * 100 else NULL end as c7
    Check in your BMM if you don't have a formula.

    Otherwise, the query are the same, same conditions, same tables.
  • That is the problem in the production environment only generates a query, i don´t understand why, if the query are the same, same conditions, same tables, same data source....

    The two repositories in this Subject Areas are the same

    It is a rather rare case.

    Guillermo
  • gerardnico
    gerardnico Member Posts: 1,465
    Answer ✓
    You have two query in dev because you use for one logical table two source table :
    * VEIS_HECHOS_ACUMULADOS
    * and VEIS_HECHOS_GLOBAL

    How OBIEE select this tables ? This tables are selected if :
    * all columns in the answer belongs to the source table
    * the logical level of each source table is set with the same parameters for the two source table

    It's the case for you ?
  • thanks a lot!

    I have created the joins again, and it has been solved succesfully.

    regards

    Guillermo
This discussion has been closed.