cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aron_Moore
Solution Specialist
Solution Specialist

Data duplication

Hello all,

 

Any have or seen this issue? We have a financials dataset that refreshes every two hours. Twice now I have been alerted by the users that the values seem to twice as high as they should. I look at the source tables in the database and everything looks fine, I manually refresh the dataset in the service and the values go back to "normal".

 

Seems that the refresh doesn't always dump the old data. Any ideas on what could be going on, how to verify/troubleshoot, or thoughts on how to monitor/prevent?

 

Thank you

7 REPLIES 7
Aron_Moore
Solution Specialist
Solution Specialist

Anyone else experience this?

 

My users are still seeing this. About ready to open a ticket, but hesitate as the issue is difficult to demonstrate/replicate.

Hi @Aron_Moore. Thanks for reporting this. I'd recommend you do the following:

 

  • Update the name of this thread (if that's possible) to indicate this is happening with SAP HANA data.
  • If you are able to catch the report when it's in this "duplicated" state, download it from the PBI service and examine the Data view. It might provide insights into the nature of the duplication. (For example, does every row repeat AA BB CC, or does the entire data set repeat ABC ABC? Or does the duplication seem to be somewhat random?)
  • If you are able to share your M queries with us, that would be helpful. You can copy them from the Advanced Editor, or through the "Copy session diagnostics to clipboard" link in the About dialog. Just be sure to redact any sensitive/private information first.

Ehren

Aron_Moore
Solution Specialist
Solution Specialist

Thanks Ehren.

 

  • The source data is SAP HANA. Doesn't seem I can adjust title.
  • Great idea on the export! We will watch for duplication and check the downloaded .pbix.
  • Here is the model and the queries:
  • Capture.PNG
  • Z_COPA_RP
    • let
          Source = SapHana.Database("hana"),
          Contents = Source{[Name="Contents"]}[Data],
          #"hana" = Contents{[Name="hana"]}[Data],
          Z_COPA_RP1 = #"hana"{[Name="Z_COPA_RP"]}[Data],
          #"Added Items" = Cube.Transform(Z_COPA_RP1, {{Cube.AddAndExpandDimensionColumn, "__AP_CHECK", {"__AP_CHECK"}, {"__AP_CHECK"}}, {Cube.AddAndExpandDimensionColumn, "0ACCNT_GRP___T", {"0ACCNT_GRP___T"}, {"0ACCNT_GRP___T"}}, {Cube.AddAndExpandDimensionColumn, "0CO_AREA___T", {"0CO_AREA___T"}, {"0CO_AREA___T"}}, {Cube.AddAndExpandDimensionColumn, "0COSTCENTER___T", {"0COSTCENTER___T"}, {"0COSTCENTER___T"}}, {Cube.AddAndExpandDimensionColumn, "0COSTELMNT___T", {"0COSTELMNT___T"}, {"0COSTELMNT___T"}}, {Cube.AddAndExpandDimensionColumn, "0CURRENCY___T", {"0CURRENCY___T"}, {"0CURRENCY___T"}}, {Cube.AddAndExpandDimensionColumn, "0CUST_GRP1___T", {"0CUST_GRP1___T"}, {"0CUST_GRP1___T"}}, {Cube.AddAndExpandDimensionColumn, "0DOC_CURRCY___T", {"0DOC_CURRCY___T"}, {"0DOC_CURRCY___T"}}, {Cube.AddAndExpandDimensionColumn, "0LOC_CURRCY___T", {"0LOC_CURRCY___T"}, {"0LOC_CURRCY___T"}}, {Cube.AddAndExpandDimensionColumn, "0MAT_PLANT___T", {"0MAT_PLANT___T"}, {"0MAT_PLANT___T"}}, {Cube.AddAndExpandDimensionColumn, "0MATERIAL___T", {"0MATERIAL___T"}, {"0MATERIAL___T"}}, {Cube.AddAndExpandDimensionColumn, "0MATL_TYPE___T", {"0MATL_TYPE___T"}, {"0MATL_TYPE___T"}}, {Cube.AddAndExpandDimensionColumn, "0PLANT___T", {"0PLANT___T"}, {"0PLANT___T"}}, {Cube.AddAndExpandDimensionColumn, "Z_PRDH2", {"Z_PRDH2"}, {"0PROD_HIER Hierarchy Level 2"}}, {Cube.AddAndExpandDimensionColumn, "Z_PRDH3", {"Z_PRDH3"}, {"0PROD_HIER Hierarchy Level 3"}}, {Cube.AddAndExpandDimensionColumn, "0PROD_HIER___T", {"0PROD_HIER___T"}, {"0PROD_HIER___T"}}, {Cube.AddAndExpandDimensionColumn, "0PROFIT_CTR___T", {"0PROFIT_CTR___T"}, {"0PROFIT_CTR___T"}}, {Cube.AddAndExpandDimensionColumn, "0SHIP_TO___T", {"0SHIP_TO___T"}, {"0SHIP_TO___T"}}, {Cube.AddAndExpandDimensionColumn, "0SOLD_TO___T", {"0SOLD_TO___T"}, {"0SOLD_TO___T"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCUSTGRPTTXT", {"4Z_COPA_RP_ZCUSTGRPTTXT"}, {"4Z_FICPACM-ZCUSTGRPTTXT"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_AMOUNTLOCAL", {"4Z_COPA_RP_AMOUNTLOCAL"}, {"Amount"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_AMOCAC", {"4Z_COPA_RP_AMOCAC"}, {"Amount - CA Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_AMOCCC", {"4Z_COPA_RP_AMOCCC"}, {"Amount - CC Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAMOUNT", {"4Z_COPA_RP_ZAMOUNT"}, {"Amount - Trans Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_AMOUNTCC", {"4Z_COPA_RP_AMOUNTCC"}, {"Amount Company Code"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAOP", {"4Z_COPA_RP_ZAOP"}, {"AOP"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAOP_OCOGS", {"4Z_COPA_RP_ZAOP_OCOGS"}, {"AOP OCOGS"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAOP_OCOS", {"4Z_COPA_RP_ZAOP_OCOS"}, {"AOP OCOS"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_BILL_TYPE", {"4Z_COPA_RP_BILL_TYPE"}, {"Billing Type"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_BUS_AREA", {"4Z_COPA_RP_BUS_AREA"}, {"Business Area"}}, {Cube.AddAndExpandDimensionColumn, "0CALYEAR", {"0CALYEAR"}, {"Calendar Year"}}, {Cube.AddAndExpandDimensionColumn, "0CALMONTH", {"0CALMONTH"}, {"Calendar Year/Month"}}, {Cube.AddAndExpandDimensionColumn, "0CALQUARTER", {"0CALQUARTER"}, {"Calendar Year/Quarter"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_COSTELEMCATE", {"4Z_COPA_RP_COSTELEMCATE"}, {"Category to Bucket Cost Elem"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_CHRT_ACCTS", {"4Z_COPA_RP_CHRT_ACCTS"}, {"Chart of Accounts"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCOGSCAC", {"4Z_COPA_RP_ZCOGSCAC"}, {"COGS - CA Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCOGSCCC", {"4Z_COPA_RP_ZCOGSCCC"}, {"COGS - CC Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCOGS", {"4Z_COPA_RP_ZCOGS"}, {"COGS - Trans Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_COMP_CODE", {"4Z_COPA_RP_COMP_CODE"}, {"Company Code"}}, {Cube.AddAndExpandDimensionColumn, "0CO_AREA", {"0CO_AREA"}, {"Controlling Area"}}, {Cube.AddAndExpandDimensionColumn, "0COSTCENTER", {"0COSTCENTER"}, {"Cost Center"}}, {Cube.AddAndExpandDimensionColumn, "0COSTELMNT", {"0COSTELMNT"}, {"Cost Element"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCOUNT", {"4Z_COPA_RP_ZCOUNT"}, {"Count"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_COUNTRY", {"4Z_COPA_RP_COUNTRY"}, {"Country Key"}}, {Cube.AddAndExpandDimensionColumn, "0CURRENCY", {"0CURRENCY"}, {"Currency Key"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ACCNT_ASGN", {"4Z_COPA_RP_ACCNT_ASGN"}, {"Customer Account Assignment Group"}}, {Cube.AddAndExpandDimensionColumn, "0ACCNT_GRP", {"0ACCNT_GRP"}, {"Customer account group"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_CUST_GROUP", {"4Z_COPA_RP_CUST_GROUP"}, {"Customer Group"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCUST_GRP", {"4Z_COPA_RP_ZCUST_GRP"}, {"Customer Group (ZSEG)"}}, {Cube.AddAndExpandDimensionColumn, "0CUST_GRP1", {"0CUST_GRP1"}, {"Customer Group 1"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_CUSTGROUPTEX", {"4Z_COPA_RP_CUSTGROUPTEX"}, {"Customer Group Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDATE", {"4Z_COPA_RP_ZDATE"}, {"Date"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDISCCAC", {"4Z_COPA_RP_ZDISCCAC"}, {"Discounts - CA Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDISCCCC", {"4Z_COPA_RP_ZDISCCCC"}, {"Discounts - CC Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDISCNTS", {"4Z_COPA_RP_ZDISCNTS"}, {"Discounts - Trans Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_DISTR_CHAN", {"4Z_COPA_RP_DISTR_CHAN"}, {"Distribution Channel"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_DIVISION", {"4Z_COPA_RP_DIVISION"}, {"Division (Transaction)"}}, {Cube.AddAndExpandDimensionColumn, "0DOC_CURRCY", {"0DOC_CURRCY"}, {"Document Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCSTGRPEX", {"4Z_COPA_RP_ZCSTGRPEX"}, {"Exception: Customer Group"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDIVEX", {"4Z_COPA_RP_ZDIVEX"}, {"Exception: Division"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZPFCTREX", {"4Z_COPA_RP_ZPFCTREX"}, {"Exception: Profit Center"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZSEGEX", {"4Z_COPA_RP_ZSEGEX"}, {"Exception: ZSEG Table"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZFICUSTH", {"4Z_COPA_RP_ZFICUSTH"}, {"FI Customer (SO)"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_FICUSOMERH_T", {"4Z_COPA_RP_FICUSOMERH_T"}, {"FI Customer Historical Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_FI_TEXT", {"4Z_COPA_RP_Z_FI_TEXT"}, {"FI Customer Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_FISCYEAR", {"4Z_COPA_RP_FISCYEAR"}, {"Fiscal Year"}}, {Cube.AddAndExpandDimensionColumn, "0FISCPER", {"0FISCPER"}, {"Fiscal year / period"}}, {Cube.AddAndExpandDimensionColumn, "0FISCVARNT", {"0FISCVARNT"}, {"Fiscal year variant"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_FUNC_AREA", {"4Z_COPA_RP_FUNC_AREA"}, {"Funtional Area"}}, {Cube.AddAndExpandDimensionColumn, "0SHIP_TO", {"0SHIP_TO"}, {"Goods Recipient"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZGRREVCAC", {"4Z_COPA_RP_ZGRREVCAC"}, {"Gross Revenue - CA Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZGRREVCCC", {"4Z_COPA_RP_ZGRREVCCC"}, {"Gross Revenue - CC Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZGRREV", {"4Z_COPA_RP_ZGRREV"}, {"Gross Revenue - Trans Currency"}}, {Cube.AddAndExpandDimensionColumn, "0INFOPROV", {"0INFOPROV"}, {"InfoProvider"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZITMCATEG", {"4Z_COPA_RP_ZITMCATEG"}, {"Item Category"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_SORD_ITEM", {"4Z_COPA_RP_SORD_ITEM"}, {"Item Number in Sales Order"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ITM_TYPE", {"4Z_COPA_RP_ITM_TYPE"}, {"Item Type"}}, {Cube.AddAndExpandDimensionColumn, "0LOC_CURRCY", {"0LOC_CURRCY"}, {"Local Currency"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_MANUALENTRY", {"4Z_COPA_RP_MANUALENTRY"}, {"Manual JE"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_MANUALJEREF", {"4Z_COPA_RP_MANUALJEREF"}, {"Manual JE Ref Doc"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_MANJEREFITM", {"4Z_COPA_RP_MANJEREFITM"}, {"Manual JE Ref Doc Item"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_MANUALKEY", {"4Z_COPA_RP_MANUALKEY"}, {"Manual Key"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZMARGIN_AOP", {"4Z_COPA_RP_ZMARGIN_AOP"}, {"Margin By AOP"}}, {Cube.AddAndExpandDimensionColumn, "0MATERIAL", {"0MATERIAL"}, {"Material"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_MAT_SALES", {"4Z_COPA_RP_MAT_SALES"}, {"Material (Sales)"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZMATAGRP", {"4Z_COPA_RP_ZMATAGRP"}, {"Material Account Assignment Group"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_MT_TEXT", {"4Z_COPA_RP_Z_MT_TEXT"}, {"Material Text"}}, {Cube.AddAndExpandDimensionColumn, "0MATL_TYPE", {"0MATL_TYPE"}, {"Material Type"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_OCOS_PERC", {"4Z_COPA_RP_OCOS_PERC"}, {"OCOC Percentage"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_OCOS_DOLLARS", {"4Z_COPA_RP_OCOS_DOLLARS"}, {"OCOS Dollars"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZPRCTRY", {"4Z_COPA_RP_ZPRCTRY"}, {"Partner Country"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZPRGEOG", {"4Z_COPA_RP_ZPRGEOG"}, {"Partner Geography"}}, {Cube.AddAndExpandDimensionColumn, "0PLANT", {"0PLANT"}, {"Plant"}}, {Cube.AddAndExpandDimensionColumn, "0MAT_PLANT", {"0MAT_PLANT"}, {"Plant Material"}}, {Cube.AddAndExpandDimensionColumn, "0FISCPER3", {"0FISCPER3"}, {"Posting period"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_PFAM_TEXT", {"4Z_COPA_RP_Z_PFAM_TEXT"}, {"Product Family Text"}}, {Cube.AddAndExpandDimensionColumn, "0PROD_HIER", {"0PROD_HIER"}, {"Product Hierarchy"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_PLINE_TEXT", {"4Z_COPA_RP_Z_PLINE_TEXT"}, {"Product Line Text"}}, {Cube.AddAndExpandDimensionColumn, "0PROFIT_CTR", {"0PROFIT_CTR"}, {"Profit Center"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_PROFIT_CENTE", {"4Z_COPA_RP_PROFIT_CENTE"}, {"Profit Center (Transaction)"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_PC_TEXT", {"4Z_COPA_RP_Z_PC_TEXT"}, {"Profit Center Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_QUANTITY", {"4Z_COPA_RP_QUANTITY"}, {"Quantity"}}, {Cube.AddAndExpandDimensionColumn, "0RECORDTP", {"0RECORDTP"}, {"Record type"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_REF_DOC_NO", {"4Z_COPA_RP_REF_DOC_NO"}, {"Reference document number"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_REGION", {"4Z_COPA_RP_REGION"}, {"Region (State, Province, Country)"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZSPRCST", {"4Z_COPA_RP_ZSPRCST"}, {"Reporting Customer"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZSPRCST_TEXT", {"4Z_COPA_RP_ZSPRCST_TEXT"}, {"Reporting Customer Text"}}, {Cube.AddAndExpandDimensionColumn, "0REQTSN", {"0REQTSN"}, {"Request Transaction Sequence Number"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_DOC_NUMBER", {"4Z_COPA_RP_DOC_NUMBER"}, {"Sales Document"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_DOC_TYPE", {"4Z_COPA_RP_DOC_TYPE"}, {"Sales Document Type"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_SALESORDERIT", {"4Z_COPA_RP_SALESORDERIT"}, {"Sales Order Item No"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_SALESORDENO", {"4Z_COPA_RP_SALESORDENO"}, {"Sales Order Number"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_SALESORG", {"4Z_COPA_RP_SALESORG"}, {"Sales Organization"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_SEGMENT", {"4Z_COPA_RP_Z_SEGMENT"}, {"Segment"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_SEG_BU", {"4Z_COPA_RP_Z_SEG_BU"}, {"Segment Business Unit"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_SEG_PL", {"4Z_COPA_RP_Z_SEG_PL"}, {"Segment Family Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_SEG_PC", {"4Z_COPA_RP_Z_SEG_PC"}, {"Segment Profit Center"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_C_TEXT", {"4Z_COPA_RP_Z_C_TEXT"}, {"Ship-to  Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_CUSTOMER", {"4Z_COPA_RP_CUSTOMER"}, {"Ship-to Number"}}, {Cube.AddAndExpandDimensionColumn, "0SOLD_TO", {"0SOLD_TO"}, {"Sold-to Party"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_Z_S_TEXT", {"4Z_COPA_RP_Z_S_TEXT"}, {"Sold-to Text"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_VAL_TYPE", {"4Z_COPA_RP_VAL_TYPE"}, {"Valuation Type"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_VERSION", {"4Z_COPA_RP_VERSION"}, {"Version"}}, {Cube.AddAndExpandDimensionColumn, "Z_PRDH2___T", {"Z_PRDH2___T"}, {"Z_PRDH2___T"}}, {Cube.AddAndExpandDimensionColumn, "Z_PRDH3___T", {"Z_PRDH3___T"}, {"Z_PRDH3___T"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAMOVRLAY", {"4Z_COPA_RP_ZAMOVRLAY"}, {"ZSEG: AccountManager - Overlay"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZDIVISION", {"4Z_COPA_RP_ZDIVISION"}, {"ZSEG: Division"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZFICUST", {"4Z_COPA_RP_ZFICUST"}, {"ZSEG2: FI Customer"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZZPROJECT", {"4Z_COPA_RP_ZZPROJECT"}, {"ZSEG2: Project"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZPRFT_CTR", {"4Z_COPA_RP_ZPRFT_CTR"}, {"ZSEG5: Profit Center"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZAM", {"4Z_COPA_RP_ZAM"}, {"ZSEG6: Account Manager"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCPM", {"4Z_COPA_RP_ZCPM"}, {"ZSEG6: Customer Program Manager"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZCPMORLAY", {"4Z_COPA_RP_ZCPMORLAY"}, {"ZSEG6: Customer Program Manager - Overlay"}}, {Cube.AddAndExpandDimensionColumn, "4Z_COPA_RP_ZGAM", {"4Z_COPA_RP_ZGAM"}, {"ZSEG6: Global Account Manager"}}}),
          #"Renamed Columns" = Table.RenameColumns(#"Added Items",{{"0PROD_HIER Hierarchy Level 2", "Product Line"}, {"0PROD_HIER Hierarchy Level 3", "Product Family"}, {"Ship-to  Text", "Ship-to Party Text"}, {"Sold-to Text", "Sold-to Party Text"}, {"Fiscal year / period", "Month/Year (Key)"}, {"Discounts - CA Currency", "Discount USD"}, {"4Z_FICPACM-ZCUSTGRPTTXT", "Customer Grp Text"}, {"Fiscal Year", "Year"}, {"Product Family Text", "Product Family Text"}, {"Product Line Text", "Product Line Text"}, {"AOP", "AOP Original"}, {"AOP OCOGS", "AOP OCOGS Original"}, {"AOP OCOS", "AOP OCOS Original"}, {"Country Key", "FI Customer Country"}, {"Partner Geography", "FI Customer Geography"}})
      in
          #"Renamed Columns"
  • ZAOP_PCPL
    • let
          Source = SapHana.Database("hana"),
          Contents = Source{[Name="Contents"]}[Data],
          #"hana" = Contents{[Name="hana"]}[Data],
          ZAOP_PCPL1 = #"hana"{[Name="ZAOP_PCPL"]}[Data],
          #"Added Items" = Cube.Transform(ZAOP_PCPL1, {{Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZYEAR", {"4ZAOP_PCPL_ZYEAR"}, {"Fiscal year"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_PRD_LINE", {"4ZAOP_PCPL_PRD_LINE"}, {"Product Line"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_PRCTR", {"4ZAOP_PCPL_PRCTR"}, {"Profit Center"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZQTR", {"4ZAOP_PCPL_ZQTR"}, {"Quarter ( 1 - 4 )"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZAOP_ACT_OCOS", {"4ZAOP_PCPL_ZAOP_ACT_OCOS"}, {"Act OCOS"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZAOP", {"4ZAOP_PCPL_ZAOP"}, {"Annual Operating Plan"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZAOP_OCOGS", {"4ZAOP_PCPL_ZAOP_OCOGS"}, {"AOP OCOGS"}}, {Cube.AddAndExpandDimensionColumn, "4ZAOP_PCPL_ZAOP_OCOS", {"4ZAOP_PCPL_ZAOP_OCOS"}, {"AOP OCOS"}}}),
          #"Reordered Columns" = Table.ReorderColumns(#"Added Items",{"Fiscal year", "Quarter ( 1 - 4 )", "Profit Center", "Product Line", "Annual Operating Plan", "AOP OCOGS", "AOP OCOS", "Act OCOS"}),
          #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Product Line", "Product Family"}, {"AOP OCOS", "AOP OCOS Original"}})
      in
          #"Renamed Columns"
  • Key Table
    • let
          Source = SapHana.Database("hana"),
          Contents = Source{[Name="Contents"]}[Data],
          #"hana" = Contents{[Name="hana"]}[Data],
          ZPCPFCAL = #"hana"{[Name="ZPCPFCAL"]}[Data],
          #"Added Items" = Cube.Transform(ZPCPFCAL, {{Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_PC", {"4ZPCPFCAL_PC"}, {"PC"}}, {Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_PF", {"4ZPCPFCAL_PF"}, {"PF"}}, {Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_QUARTER", {"4ZPCPFCAL_QUARTER"}, {"Quarter"}}, {Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_FYEAR", {"4ZPCPFCAL_FYEAR"}, {"Year"}}, {Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_BU", {"4ZPCPFCAL_BU"}, {"Business Unit"}}, {Cube.AddAndExpandDimensionColumn, "4ZPCPFCAL_SEG", {"4ZPCPFCAL_SEG"}, {"Segment"}}}),
          #"Reordered Columns" = Table.ReorderColumns(#"Added Items",{"Year", "Quarter", "PC", "PF"}),
          #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"PC", "Profit Center"}, {"PF", "Product Family"}})
      in
          #"Renamed Columns"
  • The other tables are basically the same, just lookup for supplemental info

Hi @Aron_Moore,

 

A few questions to get a better grasp of the issue:

1. How many rows are being retrieved? How many of those are duplicate?

2. Does this happen within a particular range or rows? (i.e. if you retrieve 50,000 rows, does the duplication only occur within a certain range, like 0-4000?).

3. Is the data sorted in any way? If so, it it sorted by a key?

4. Does this happen when retrieving rows from a single query or just when combining multiple queries?

 

Jorge

Aron_Moore
Solution Specialist
Solution Specialist

Thanks @jjgb

 

 

1. How many rows are being retrieved? How many of those are duplicate?

  • Normally 39k'ish retrieved. Not sure about number of duplicates, I only see the measures on the report exactly doubled (e.g. revenue should show $100M, but shows $200M). I have thought about putting an alert in to trigger if a measure we know that should remain fixed changes, or maybe I'll do on on row count... not a bad idea

2. Does this happen within a particular range or rows? (i.e. if you retrieve 50,000 rows, does the duplication only occur within a certain range, like 0-4000?).

  • Not sure. Next time we experience the duplicates we'll export the .pbix to check the raw data.

3. Is the data sorted in any way? If so, it it sorted by a key?

  • Not sorted in the .pbix model. The source table is sorted by transaction date, I believe.

4. Does this happen when retrieving rows from a single query or just when combining multiple queries?

  • The dataset is our most used and seems to be the only one exhibiting this behaviour. It may be others as well, but they aren't used as frequently and thus may go unoticed.
v-huizhn-msft
Microsoft
Microsoft

Hi @Aron_Moore,

"Twice now I have been alerted by the users that the values seem to twice as high as they should." What do you mean of this sentence? You got an alert/error message said that? Please share more details.

Thanks,
Angelia

No errors or alerts really.

 

Say the users know they've entered around 100 purchase orders over the period with a total revenue of $100 million, but when they use the dataset from the service it shows 200 and $200. They know that's not right, so they reach out to me. I refresh the dataset manually and it gets resolved.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors