none
problem loading data directly to data model RRS feed

  • Question

  • UFC
    I've a problem with directly loading data from power query to the datamodel in excel. It's quite interesting that i can't find anything in google.
    My query pulls data from sql analytics with mdx statement. Does adding and filtering data and that's it. When i load the data to the worksheet it's loaded fast (only 3k rows). But if i want to load the data to the datamodel it gets stuck. I'm using excel 365 build 1811. Do you have any idea what the problem could be?
    Here is the query:

    let
        GetProjectNumbers = 
            let
                Quelle = Excel.CurrentWorkbook(){[Name="tblProjectNumbers"]}[Content],
                #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Project number", type text}, {"Project name", type text}}),
                #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Project number] <> null)),
                #"Andere entfernte Spalten" = Table.SelectColumns(#"Gefilterte Zeilen",{"Project number"}),
                #"Project number" = #"Andere entfernte Spalten"[Project number],
                AddMDX = Text.Combine(List.Transform(#"Project number", each "[Project].[Project Definition Code].[Project Definition Code].&[" & _ & "]"), ", ")
                //[Project].[Project Definition Code].[Project Definition Code].&[M-121-05416]
            in
                AddMDX,
        GetNewProjectNumbers = if GetProjectNumbers = "" then "[Project].[Project Definition Code].[Project Definition Code].&[M-121-10650]" else GetProjectNumbers,
        GetEvaluationDate = 
            let
                Quelle = Excel.CurrentWorkbook(){[Name="tblEvaluationDate"]}[Content],
                #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Evaluation date", type date}})
                //#"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Evaluation date] <> null))
            in
                #"Geänderter Typ",
        GetNewEvaluationDate = if GetProjectNumbers = "" then #date(2010,1,1) else if GetEvaluationDate[#"Evaluation date"]{0} = null then#date(2010,1,1) else GetEvaluationDate[#"Evaluation date"]{0}, 
        GetShortID = 
            let
                Quelle = Excel.CurrentWorkbook(){[Name="tblShortID"]}[Content],
                #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Short-ID", type text}, {"Short-ID description", type text}, {"to use", type text}}),
                #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each [to use] <> null),
                #"Andere entfernte Spalten" = Table.SelectColumns(#"Gefilterte Zeilen",{"Short-ID"})
            in
                #"Andere entfernte Spalten",
        
        Quelle = AnalysisServices.Database("svipcbi", "OLAP_BI", [Query="
                SELECT
                    NON EMPTY { [Measures].[Posted H] } ON COLUMNS,
                    NON EMPTY { ([Project].[Project Definition Code].[Project Definition Code].ALLMEMBERS,
                    [Project].[Project Definition Description].[Project Definition Desc].ALLMEMBERS,
                    [Project].[Short ID].[Short ID].ALLMEMBERS,
                    [Organization].[Company Code].[Cost Center - Detail].ALLMEMBERS,
                    [Project].[WBS Project Code].[WBS Project Code].ALLMEMBERS,
                    [Project].[WBS Element Description].[WBS Element Description].ALLMEMBERS,
                    [Employee].[Employee Name].[Employee Name].ALLMEMBERS,
                    [Employee].[Employee Cost Center].[Employee Cost Center].ALLMEMBERS,
                    [Time].[Date].[Date].ALLMEMBERS) } ON ROWS
                FROM ( SELECT ( {" & GetNewProjectNumbers & "} ) ON COLUMNS 
                FROM [ITRS418 Hours FY])"
                // WHERE  [Project].[Project Definition Code].[Project Definition Code].&[" & GetNewProjectNumber   & "]"
                ,Implementation="2.0"]),
        #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"[Time].[Date].[Date].[MEMBER_CAPTION]", type date}, {"[Measures].[Posted H]", type number}}),
        #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"[Project].[Project Definition Code].[Project Definition Code].[MEMBER_CAPTION]", "Project Definition Code"}, {"[Project].[Project Definition Description].[Project Definition Desc].[MEMBER_CAPTION]", "Project Definition Desc"}, {"[Project].[WBS Project Code].[WBS Project Code].[MEMBER_CAPTION]", "WBS Project Code"}, {"[Project].[Short ID].[Short ID].[MEMBER_CAPTION]", "Short ID"}, {"[Project].[WBS Element Description].[WBS Element Description].[MEMBER_CAPTION]", "WBS Element Description"}, {"[Employee].[Employee Name].[Employee Name].[MEMBER_CAPTION]", "Employee Name"}, {"[Organization].[Company Code].[Company Code].[MEMBER_CAPTION]", "Company Code"}, {"[Organization].[Company Code].[Cost Center - Detail].[MEMBER_CAPTION]", "Cost Center - Detail"}, {"[Time].[Date].[Date].[MEMBER_CAPTION]", "Date"}, {"[Measures].[Posted H]", "Posted H"}}),
        FilterByShortIDandDate = Table.SelectRows(#"Umbenannte Spalten", each List.Contains(GetShortID[#"Short-ID"], _[#"Short ID"]) and _[Date] <= GetNewEvaluationDate ),
        #"Angefügte Abfrage" = Table.Combine({FilterByShortIDandDate, tblManualActualData}),
        #"Entfernte Spalten" = Table.RemoveColumns(#"Angefügte Abfrage",{"[Employee].[Employee Cost Center].[Employee Cost Center].[MEMBER_CAPTION]"}),
        AddEmptyRowIfNeeded = if Table.IsEmpty(#"Entfernte Spalten") then Table.InsertRows(#"Entfernte Spalten",0,{[Project Definition Code= "", Project Definition Desc= "", Short ID = "", Company Code= "", #"Cost Center - Detail"= "", WBS Project Code = "", WBS Element Description = "", Employee Name= "", Date= "", Posted H="" ]}) else #"Entfernte Spalten",
        TableBuffer = Table.Buffer(AddEmptyRowIfNeeded)
    in
        TableBuffer
    Many thanks


    Erhard

    • Edited by jimmy80211 Thursday, August 22, 2019 1:35 PM
    Wednesday, August 21, 2019 6:58 PM

Answers

  • Hi Jimmy. So it looks like the difference in perf is caused by the fact that we read data from the workbook using the UI thread. When loading to a sheet, there's not much contention for the UI thread. But when loading to the data model, apparently the data model is also utilizing the UI thread and competing for it. Thus things take longer. On an M level, the GetShortID value seems to be getting re-calced for every row. Using Table.Buffer causes it to only be calculated once, and thus much less data is getting read (or re-read) from the current workbook.

    I've notified the Excel team about this and they've filed a bug to look into it.

    Ehren

    10 hours 48 minutes ago
    Owner

All replies

  • Hi Erhard,

    does getting stuck mean that it doesn't load at all? Then it's probably a problem with the connections/keys. Please have in mind that PQ is case sensitive.

    Otherwise you might also try to remove the last buffering-step.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, August 24, 2019 7:33 AM
    Moderator
  • Hello Imke

    What a pleasure :)

    Unfortunately I tried to remove the Buffer-step... and actually tried to solve the problem by adding it. I already tried to move the query to a new file without success. About connection keys... i never maintained them manually... simple select"add to datamodel" when loading it. Where could i change them?

    About your first questiion... it loads... but takes looooong. Sometimes i tried to interrupt without success... and then i had to force excel to close. So when loading the query into a sheet it takes 20 seconds but in the data model takes minutes, never understood what determined it whether query was loaded in several minutes or not loaded at all (force excel to close after 15 min or so)

    Many thanks

    Erhard

    Sunday, August 25, 2019 8:11 AM
  • Hi Erhard,

    the only difference between loading to sheet vs. loading to data model I can think of is handling the relationships within the data model. Hence my question about the connections/keys (between the tables in the model). 

    If your query wouldn't have loaded at all (or not until you closed the file), it could have been an issue with non-matching keys between Fact- & Dimension-Tables. But as you now confirm that it loads, but only too slow, we can exclude this from the possible reasons.

    Are there any other tables in the model at all? If so - please isolate the query by copying it to a new Excel-file and run it as the sole table in the data model.

    If the duration is still too slow, we can exclude issues with connections in the model. Will try to connect this thread to someone from the MS team then.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Tuesday, August 27, 2019 5:12 PM
    Moderator
  • Hello

    I tried to only load this query without success.

    Find herewith enclosed the test documentation... (note: not able to upload pictures)

    BR

    Erhard

    - Copied needed sheets (filter function – append function) and the query to new file.

    - It loaded in approx.. 30 seconds into a sheet

    - Then I changed the load-option like this (only connection and and add to data model)

    - After waiting 5 minutes I forced Excel to close (not able to interrupt query at all)


    Wednesday, August 28, 2019 5:35 AM
  • Hello

    Any news on this regard?

    Br

    Thursday, September 12, 2019 3:38 PM
  • Not yet.

    One possible reason could be that once you load the model, a refresh all wlll also trigger the refresh of all connected pivot-tables and cube functions. Do you have many objects connected to the table in the data model?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, September 15, 2019 6:05 PM
    Moderator
  • Hello Imke,

    I didn't get exactly what you need to know. However the original file contain a connection to SQL Server and Analysis Service, both of them using one table from the Excel to filter the query and both data connection integrate one table each to integrate manual data.

    But when I tested, I reduced the file to only one query to the data connection (Analysis Service) that uses 2 tables from the Excel file to filter and include manual data. Using this test file always results in blocking Excel - forcing Excel to close after a long time because of not loading the data.

    Also deleting the integration of the manual data within the query and deleting the buffer-step at the end with the same outcome ... at least it showed at some point that started loading data... but no comparison with the performance of the direct load to a Excel table. Ended up forcing Excel to close too after a long time waiting

    BR

    Friday, September 20, 2019 7:26 AM
  • no ideas so far?
    Wednesday, October 9, 2019 12:06 PM
  • Hello

    I've done now several analysis and I was able to boil it down to the following:

    It's exactly this part of the query, that blocks the query loading to the datamodel (or at least verrrryyy slow)... because as already established above, the query always loads into a excel-table very quickly

    List.Contains(GetShortID[#"Short-ID"], _[#"Short ID"])

    Hope this helps

    thanks

    Wednesday, October 9, 2019 2:34 PM
  • Hello

    I've now also excluded any connection to external data sources and so I could you provide a file, where loading a query to a data model will cause problem. So tell me a mail-address were to send the file.

    BR

    Jimmy

    Thursday, October 10, 2019 5:35 AM
  • Hi Jimmy. Could you post a OneDrive link (or something similar) here? I'm curious to see the repro as well.

    Ehren

    Thursday, October 10, 2019 5:08 PM
    Owner
  • Hello all

    sorry for the late reply... I'm not informed once somebody replies on my post... anybody knows how to activate?

    Nevertheless, here the link to our problem child.

    https://togetherglobal-my.sharepoint.com/:x:/g/personal/erhard_holzer_leitner_com/EU1_gL4ok05Jo6Cn9T_eU0MBWIXuGyuaAPtAmmL6B2RPSA?e=Efc7Gi

    Loading the query takes 20 seconds, once the destination is changed to only connection and load to data model it takes a lifetime to load.

    many thanks for your support

    Jimmy

    Friday, October 18, 2019 5:09 AM
  • Hi Jimmy. I got an error when I tried to access the link. It says "We're sorry, but ***@microsoft.com can't be found in the togetherglobal-my.sharepoint.com directory."

    Ehren

    Monday, October 21, 2019 11:41 PM
    Owner
  • Hello

    This link should work

    https://1drv.ms/x/s!An_KOwbVBJqNn1Ide65JDGljghNa?e=TZ7uiG

    Tuesday, October 22, 2019 6:08 AM
  • Thanks, Jimmy. We're still looking into the root cause, but in the meantime I'm happy to share a workaround suggested by Curt from our team: if you put a Table.Buffer call around the result of GetShortID, then both the sheet loading and the data model loading complete in a few seconds.

    EDIT: Also, we have a question. Is the GetShortID data being pulled from the current workbook or somewhere else in your "real" solution?

    Ehren


    Wednesday, October 23, 2019 6:55 PM
    Owner
  • Hello Ehren

    ooooh.. .good news. So I will give it a try to use Table.Buffer for the GetShortID in the final solution.

    In the real solution I use the 2 local tables (Project and ShortID) so filter SQL and Analysis-Services query.

    Thanks a lot for your support

    Thursday, October 24, 2019 8:52 AM
  • Hi Jimmy. So it looks like the difference in perf is caused by the fact that we read data from the workbook using the UI thread. When loading to a sheet, there's not much contention for the UI thread. But when loading to the data model, apparently the data model is also utilizing the UI thread and competing for it. Thus things take longer. On an M level, the GetShortID value seems to be getting re-calced for every row. Using Table.Buffer causes it to only be calculated once, and thus much less data is getting read (or re-read) from the current workbook.

    I've notified the Excel team about this and they've filed a bug to look into it.

    Ehren

    10 hours 48 minutes ago
    Owner