12 Replies Latest reply: Mar 14, 2013 2:32 PM by Sasi Nagireddy RSS

    Duplicate Data is missing in the report which is in database

    980613
      I am getting only distinct data in report. We need duplicated data in report if it is available in database.

      Below is sql from log file.

      WITH
      SAWITH0 AS (select T29602.JCLD_HRS_NBR as c1,
      T29602.JCLD_CALC_RT as c2,
      T29389.CPC_CUST_NM as c3,
      T30264.LA_NM as c4
      from
      V_FACT_JCLD_CHK_DSTRB_LA T29602 /* Fact_JCLD_CHK_DSTRB_LA_Check_Distribution_Labor */ ,
      V_DIM_LABR_ASGN T30264 /* Dim_LABR_ASGN_D_Labor_Assignment */ ,
      V_DIM_CLT_PAY_CPNT T29389 /* Dim_CLT_PAY_CPNT_D_Jcdp_Cpc_Earning */
      where ( T29389.SUR_CLT_PAY_CPNT_ID = T29602.SUR_CLT_PAY_CPNT_ID_EARN and T29389.CPC_CUST_NM = 'Regular' and T29602.SUR_LABR_ASGN_ID = T30264.SUR_LABR_ASGN_ID and T30264.LA_NM = '111 Commercial Const EE' and TO_DATE(T29602.SUR_REF_TIME_PERD_ID_END,'YYYY-MM-DD') >= TO_DATE('2013-03-13' , 'YYYY-MM-DD') and TO_DATE(T29602.SUR_REF_TIME_PERD_ID_EFF,'YYYY-MM-DD') <= TO_DATE('2013-03-13' , 'YYYY-MM-DD') ) ),
      SAWITH1 AS (select D1.c1 as c1,
      D1.c2 as c2,
      D1.c3 as c3,
      D1.c4 as c4,
      D1.c5 as c5
      from
      (select 0 as c1,
      D1.c2 as c2,
      D1.c3 as c3,
      D1.c1 as c4,
      D1.c4 as c5,
      ROW_NUMBER() OVER (PARTITION BY D1.c1, D1.c2, D1.c3, D1.c4 ORDER BY D1.c1 ASC, D1.c2 ASC, D1.c3 ASC, D1.c4 ASC) as c6
      from
      SAWITH0 D1
      ) D1
      where ( D1.c6 = 1 ) )
      select D1.c1 as c1,
      D1.c2 as c2,
      D1.c3 as c3,
      D1.c4 as c4,
      D1.c5 as c5
      from
      SAWITH1 D1

      I found some blogs they said to avoid this issue uncheck (with_clase_supported and Rownum_supported) db features but it didn’t work. After I uncheck above features sql looks like,


      select 0 as c1,
      D1.c2 as c2,
      D1.c3 as c3,
      D1.c1 as c4,
      D1.c4 as c5
      from
      (select T29602.JCLD_HRS_NBR as c1,
      T29602.JCLD_CALC_RT as c2,
      T29389.CPC_CUST_NM as c3,
      T30264.LA_NM as c4
      from
      V_FACT_JCLD_CHK_DSTRB_LA T29602 /* Fact_JCLD_CHK_DSTRB_LA_Check_Distribution_Labor */ ,
      V_DIM_LABR_ASGN T30264 /* Dim_LABR_ASGN_D_Labor_Assignment */ ,
      V_DIM_CLT_PAY_CPNT T29389 /* Dim_CLT_PAY_CPNT_D_Jcdp_Cpc_Earning */
      where ( T29389.SUR_CLT_PAY_CPNT_ID = T29602.SUR_CLT_PAY_CPNT_ID_EARN and T29389.CPC_CUST_NM = 'Regular' and T29602.SUR_LABR_ASGN_ID = T30264.SUR_LABR_ASGN_ID and TO_DATE(T29602.SUR_REF_TIME_PERD_ID_END,'YYYY-MM-DD') >= TO_DATE('2013-03-13' , 'YYYY-MM-DD') and (T30264.LA_NM in ('11 N MAIN', '111 Commercial Const EE')) and TO_DATE(T29602.SUR_REF_TIME_PERD_ID_EFF,'YYYY-MM-DD') <= TO_DATE('2013-03-13' , 'YYYY-MM-DD') )
      ) D1
      order by c2, c3, c4, c5

      ]]
      [2013-03-13T16:05:35.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-20] [] [ecid: 4f39abcd85d0b609:-490f4d43:13d5fcf2feb:-8000-0000000000003be4] [tid: 997c] [requestid: 27960005] [sessionid: 27960000] [username: weblogic] -------------------- Execution Node: <<909047>>, Close Row Count = 27, Row Width = 168 bytes [[

      ]]
      [2013-03-13T16:05:35.000+00:00] [OracleBIServerComponent] [TRACE:7] [USER-20] [] [ecid: 4f39abcd85d0b609:-490f4d43:13d5fcf2feb:-8000-0000000000003be6] [tid: 720c] [requestid: 27960005] [sessionid: 27960000] [username: weblogic] -------------------- Execution Node: <<909234>> GroupByNoSort, Close Row Count = 20, Row Width = 168 bytes [[



      When I was looking into log file (In my example) I saw that BI server is getting 27 rows back from db but when it sees duplicated data/rows it reduces row count to 20.

      What changes I have to make to get duplicated rows/data in my report?

      Let me know If you need more information to make you clear.
        • 1. Re: Duplicate Data is missing in the report which is in database
          Yogi1729
          hi,

          try to un check the option Distinct_supported feature in the DB features and check ur data once.

          and let me know.
          • 2. Re: Duplicate Data is missing in the report which is in database
            980613
            Distinct_supported feature is already unchecked in my environment.
            • 3. Re: Duplicate Data is missing in the report which is in database
              JasonHudson
              Would you mind posting the XML from the Advanced tab please?
              • 4. Re: Duplicate Data is missing in the report which is in database
                Srini VEERAVALLI
                Make sure you didn't select check box for distinct at logical fact source->Content tab.

                Make sure aggregation is correct and matches each other BI and DB results, if matches just ignore you might not have a specific column in criteria to split rows. Try to pull lowest granular column which is not aggregated, that would guide you.

                Keep update
                • 5. Re: Duplicate Data is missing in the report which is in database
                  MuRam
                  Hi,

                  Use the key or id column used to join in the report to get the detailed report.

                  Hope this helped/ answered.

                  Regards
                  MuRam
                  • 6. Re: Duplicate Data is missing in the report which is in database
                    980613
                    Hi Jason,

                    Here is the XML.


                    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201008230" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
                    <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Employee Labor Distribution Transaction History&quot;">
                    <saw:columns>
                    <saw:column xsi:type="saw:regularColumn" columnID="c40de1ebbb479fed9">
                    <saw:columnFormula>
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Labor Assignment Name"</sawx:expr></saw:columnFormula></saw:column>
                    <saw:column xsi:type="saw:regularColumn" columnID="cbcfb4eb7e495e585">
                    <saw:columnFormula>
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Earning Name"</sawx:expr></saw:columnFormula></saw:column>
                    <saw:column xsi:type="saw:regularColumn" columnID="c360232c0f7b4d0cc">
                    <saw:columnFormula>
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Calculated Rate"</sawx:expr></saw:columnFormula></saw:column>
                    <saw:column xsi:type="saw:regularColumn" columnID="c67b7293faf7d4c91">
                    <saw:columnFormula>
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Hours"</sawx:expr></saw:columnFormula></saw:column></saw:columns>
                    <saw:filter>
                    <sawx:expr xsi:type="sawx:logical" op="and">
                    <sawx:expr op="equal" xsi:type="sawx:comparison">
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Labor Assignment Name"</sawx:expr>
                    <sawx:expr xsi:type="xsd:string">111 Commercial Const EE</sawx:expr></sawx:expr>
                    <sawx:expr op="equal" xsi:type="sawx:comparison">
                    <sawx:expr xsi:type="sawx:sqlExpression">"Employee Labor Distribution Transactions"."Earning Name"</sawx:expr>
                    <sawx:expr xsi:type="xsd:string">Regular</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
                    <saw:views currentView="0">
                    <saw:view xsi:type="saw:compoundView" name="compoundView!1">
                    <saw:cvTable>
                    <saw:cvRow>
                    <saw:cvCell viewName="titleView!1">
                    <saw:displayFormat>
                    <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow>
                    <saw:cvRow>
                    <saw:cvCell viewName="tableView!1">
                    <saw:displayFormat>
                    <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
                    <saw:view xsi:type="saw:titleView" name="titleView!1"/>
                    <saw:view xsi:type="saw:tableView" name="tableView!1">
                    <saw:edges>
                    <saw:edge axis="page" showColumnHeader="true"/>
                    <saw:edge axis="section"/>
                    <saw:edge axis="row" showColumnHeader="true">
                    <saw:edgeLayers>
                    <saw:edgeLayer type="column" columnID="c40de1ebbb479fed9"/>
                    <saw:edgeLayer type="column" columnID="cbcfb4eb7e495e585"/>
                    <saw:edgeLayer type="column" columnID="c360232c0f7b4d0cc"/>
                    <saw:edgeLayer type="column" columnID="c67b7293faf7d4c91"/></saw:edgeLayers></saw:edge>
                    <saw:edge axis="column"/></saw:edges></saw:view></saw:views></saw:report>
                    • 7. Re: Duplicate Data is missing in the report which is in database
                      980613
                      Hi Sri,

                      I checked content tab of fact source and distinct box is not checked.

                      We always need detail level data so we did not added aggreagtion in this subject area. If I capture physical query and run against db I am getting all rows back so BI server internally doing something to reduce row count for duplicate data.
                      • 8. Re: Duplicate Data is missing in the report which is in database
                        980613
                        Hi Ram,

                        We are getting all rows back if I add Id column in report or set descriptor Id in RPD. But this one is Ad hoc subject area and we dont want user to see ID column and also we have around 15 columns in this SA which has duplicate data. So I have to set descriptor Id for all of them.
                        • 9. Re: Duplicate Data is missing in the report which is in database
                          JasonHudson
                          Thank you.

                          There's nothing you've done that's specifying this behavior, so MuRam's suggestion is what you'll want to do. If you don't bring back information that distinguishes one record from another the data will be aggregated as specified in your repository. It might be more obvious if you consider that if it didn't do this, every report would be returning every record on it's own row, and that is part of what OBIEE is meant to avoid. To get the results you want you should add another column (or columns) that distinguishes the duplicate records from each other. You can add this column to the criteria but not to the table view so it won't have to clutter your screen, but it will show up if you export the data.
                          • 10. Re: Duplicate Data is missing in the report which is in database
                            980613
                            Do we have ability to disable OBIEE feature so it returns duplicate rows too?
                            • 11. Re: Duplicate Data is missing in the report which is in database
                              JasonHudson
                              Yes and no. The behavior is the result of how your repository is configured. You could probably get what you want by removing any aggregation from your measures, but that doesn't sound like a good idea to me...

                              You could also create a new measure just for this kind of instance, which sounds like a better idea. If you copied the measure in your Business Layer but removed the aggregation from that you could publish it as "Hours (Non-Aggregated)" perhaps.

                              Might that work for you?
                              • 12. Re: Duplicate Data is missing in the report which is in database
                                Sasi  Nagireddy
                                Can u check the join conditions also? Some time the key and the way we join also effects the details data.

                                Do let me know the updates?

                                Thanks.