Forum Discussion
Power BI and Microsoft Dynamics Integration
Super UserHI Anonymous ,
You stated that you built a data model from the Dynamics model - did you mean that you built one in a tool? If so, then that is probably in SQL or Azure SQL and then you would query that database.
If you mean that you are building a model inside of Power BI directly from Dynamics then you are going to be using SQL. I STRONGLY recommend that you do NOT hit production Dynamics but that you get a data load - of whatever frequency you need - and that you hit the resulting data. Hitting the production Dynamics data will almost assuredly cause corruption in the data at worst and will cause slowdowns in the production system at best.
All that said, if your question is about modifying the data via SQL first or via Power BI from SQL database then the answer is "whichever you prefer". I prefer doing it all in Power BI. My reasoning is that I have found organizations tend to have one or two qualified SQL people and then they are not available when needed. Also, the SQL person may not understand your need for the data and might make cubes or other views that are not pertinent to you. And, when I do the modifications in Power BI, then I can control what is being shown how.
The only exception to my preference is when the data is quite large. Like terrabyte or petabyte size - becuase at the scale, SQL is the better engine for modification at scale and this is where you get into some more of the detailed pionts about data management and movement.
Many thanks indeed for coming back to me!
As I read you, I notice how little close to nothing I understand on this topic. What I mean is I open Power BI and I can bring my data either from
a) SQL, would be like these:
b) From Power BI, I go to Transform / Advance Editor and bring the data like these:
Do I make sense? If so, any preference into wich one I should use?
Many thanks!!! 😃
- collinq
Hey Anonymous ,
Thanks for the clarification! OK, the answer to the clarified version ofthe question is "it depends". Personally, I prefer SQL. But, that is because I understand SQL thoroughly. And, normally, the organizations I work with have their data in SQL in a mature manner (they use SQL for data modifications, stored procedures, etc). Also, SQL is a bit more structured and standard across datasets while CDS can be a little more customized in some ways that I do not always anticipate. The basics f how the data is stored is a bit different.
BUT, if your organization uses mostly CDS and you are comfortable with it and understand the data and structure within CDS in yourenvironment, then use that!
From a "which is better perspective" from a systems perspective the answers come down to the one that your organization is givine resources to. If your SQL server (online or internal) is robust and your CDS area is given minimal system resources (or vice-versa) then that will affect performance and should be a factor as well. Also, one set of data being written to/reported from more often (basically - which is more stable from a data perspective). And, then, datasize...it is my understanding (I am NOT an expert in this area) that SQL will handle large datasets easier and feed them to Power BI more easily than CDS does.
Many thanks for coming back to me!
Two more questions please, hope you don't mind.
1. Performance
In terms of the performance, is it faster using SQL?
2. Quary
If I bring the data as I showed on the second example, where is the data storage? Is it storage on Microsoft Dynamics and Power BI only shows it? Or is it storaged on Power BI?
Many thanks indeed! You have no idea how massively helpful this is. 😊