In this writing, I want to share my current problem, and ask a question about how to solve this problem. I have a simple solution to this and I am, for now, doing it in this way. but I am not 100% sure whether I am doing it in the correct way, or if there is a better way.
I tried to search for similar issues on the Power BI Community (Home - Microsoft Power BI Community), but unfortunately, I could not find any good answers.
I have a problem connecting systemusers table and some other tables in the database in Dynamics 365 to Power BI. The problem is the refreshing speed from the very first step in Power Query Editor in Power BI Desktop (or in Power Query online) after I click the entity name = "systemusers" to connect, and I cannot move on to the next step. It took me more than 15 minutes to refresh and see the result on the screen.
Not only one but almost all companies' systemusers tables give me the same issue. Not only 2000 FTE company size but also 200 FTE company size gave me a similar issue. Perhaps I do not have much knowledge in connecting this to Power BI.
Maybe, I am absolutely wrong in trying to connect systemusers entity to Power BI. Perhaps I do not need to connect this to Power BI. Maybe the same information is stored in another entity in the database.
However, systemusers entity gives me some of the information that other entity does not have. For instance, I need to use the hierarchy information of the company in order to implement RLS, and I could not find the hierarchy data other than systemusers table. I use the information from systemusers entity to create hierarchy information. Also, many other tables contain GUID columns to show owers' or creators' information, while systemusers table also contains email address information or user_number information that shows employees' identification in a more simple way.
A solution to this may be very easy -> Systemusers table is not meant to be used, because there are so many extendable columns that are connected to almost all other tables in the database. So, the solution might be, "Do not connect systemusers table to Power BI." If I can find the same information in the other table, I will not connect systemusers table. Until someone advises me which entity name is for the alternative, I will try to find a better way. So far I have found only one way.
I will describe what methods to connect systemusers table made me fail, and what solution I am using for now.
My first attempt was to click "Dataverse". But it took me over 15 minutes to connect to systemusers table. And then, I tried Common Data Service (Legacy), dynamics 365 Online (legacy), and Dynamics 365 (Dataverse). All of them showed me the same performance.
I tried some other ways to get data, but some of them were worse than the above.
So, I decided,
Do not - bring all columns from systemusers table and then select relevant columns in Power Query Editor
Do- only bring the columns that I want before I bring them into Power Query Editor.
In order to do this, I wrote an SQL statement like below.
One drawback is, when trying to execute more transforming steps in Power Query editor after writing an SQL statement, Query Folding may not be applied anymore. So, try writing all transformation steps when authoring an SQL statement, and do not add any transformation steps in Power Query editor.
To summarize,
Please let me know if there is other than using systemusers table when analyzing Dynamics 365 data, or, if there is a better way when importing systemusers table into Power BI.
If connecting to a certain table in the database shows extremely slow performance, try authoring an SQL statement to get only relevant columns from the table.
I hope this helps provide one of the solutions to the slow connection to Dynamics 365 and start authoring an SQL statement in Power BI.
Comments