Forum Discussion
Helper IIIData loads in Power Query but not in Power BI
IanR Yeah, the column names or values in yellow are what I'm referring to. If you can expand and dig in as you describe that is the functionality that will slow things down exponentially. I can't speak to the "why" or "where" in the processing, all I know is it kills the processing in Power BI.
There should be relationships that you can use in these entities to build relationships within Power BI. For all of the translations from ID to name or description, those you will most likely have to re-create in Power BI. You can build those columns of values within the table, or add them as seperate dimensions if the lists are huge and relate them via the ID.
Another tool I've used to see the actual names vs. what CRM (Dynamics 365) shows is the XRM Toolbox. You can download the schema and it is extremely helpful as it shows you all the value transformations for dropdowns, etc.
I've used the above methods, only pulling data, and everytime the data loads in under 5 minutes.
IanR After you enabled the load did you refresh the data in the model? You will need to reprocess the model, and by extension pull in the data now.
In addition, if you are pulling from CRM online (sounds like it) if you remove all the tables, functions, etc that are essentially connections to other entities in CRM your load will be much faster. Only pull in the data from the current entity and then build a model on the entities you've independantly pulled. (Just quick thought as I've dealt with the pain of CRM Online/Odata)
Helper IIIHi Eno1978,
I have tried to reproduce the problem with much a much smaller model, one that contains one small table and I haven't been able to reproduce it. Either I had forgotten to refresh (several times) as you suggest or it only kicks in with large OData datasets. I'll assume the former for now.
On the subject of OData, can you explain what you mean by 'remove all the tables, functions, etc that are essentially connections to other entities in CRM your load will be much faster'?
Thanks
Ian
IanR When you connect to CRM you will notice that there are values in some entities that are highlighted yellow. You can choose columns from these related objects. (This is also a setting in Options that you can disable. Under Current File -> Data Load -> Relationships - "Import relationships from data sources")
These highighted yellow "values" are recognized paths and relationships to other entities within CRM. If you choose the values in this way, your queries to pull the data will slow down exponentially. I either remove the setting, or remove those columns from all my connections in order to speed up the ingestion.
I'm making some assumptions here that you are using these, as without them, things should be fairly quick.
- IanR
Hi Eno1978,
Do you mean the columns that start off containing 'Record' or 'Table'? Those are the only column contents that I can see that are not in the standard font colour. A couple of questions:
The Table columns I had a hunch that I had to be careful with - as they are looking at other tables in CRM. However, despite the lack of query folding, won't the heavy lifting for these, the looking up in other tables, be done on the source server and therefore be relatively quick?
Clicking on the double headed arrow symbol in the header for the Record columns opens a dialog that offers a list of options; mostly Id, Logical Name, Name and Row Version. For ID columns I don't know of any other way of getting at the data. For example, in the OpportunitySet table all the useful ID columns, such as opportunity ID or parent contact ID, are of the Record type. Without expanding these columns I don't think I could bring these IDs into the model. Ordinarily I would only select Id and possibly Name. I have started to use the Name column less as I can look these values up in the root tables (e.g. use ContactSet table for parent contact name) but I don't see a way of not expanding these to get to the ID. Am I missing a trick?
Thanks
Ian
IanR Yeah, the column names or values in yellow are what I'm referring to. If you can expand and dig in as you describe that is the functionality that will slow things down exponentially. I can't speak to the "why" or "where" in the processing, all I know is it kills the processing in Power BI.
There should be relationships that you can use in these entities to build relationships within Power BI. For all of the translations from ID to name or description, those you will most likely have to re-create in Power BI. You can build those columns of values within the table, or add them as seperate dimensions if the lists are huge and relate them via the ID.
Another tool I've used to see the actual names vs. what CRM (Dynamics 365) shows is the XRM Toolbox. You can download the schema and it is extremely helpful as it shows you all the value transformations for dropdowns, etc.
I've used the above methods, only pulling data, and everytime the data loads in under 5 minutes.