Forum Discussion
Power BI and Microsoft Dynamics Integration
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.
Hey Anonymous ,
Well, SQL is designed to be a place for data and is extermely robust in that area. So, a strong argument could be made that it is the faster tool - but that is based on 'standards' and setup. Assuming all is 'normal' then SQL should be a faster tool - BUT, as I stated before, if all the resources are on the server(s) hold the CDS data then this would not be the case.
As for where the data resides, the default database for Dynamics is Azure SQL so it is already in SQL. Power BI itself does not store data, it pulls the data into a data model and holds all of your modifications in the applied steps but it does not "store" the data from a "can I go get that data with some other tool" perspective.
In your case, if the folks that have built the CDS and have modified the data in the way that you need, then perhaps that data source would be better for you in the long run because it has the mods already.
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!!! 😃
Super UserHey 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. 😊
- collinq
Hey Anonymous ,
Well, SQL is designed to be a place for data and is extermely robust in that area. So, a strong argument could be made that it is the faster tool - but that is based on 'standards' and setup. Assuming all is 'normal' then SQL should be a faster tool - BUT, as I stated before, if all the resources are on the server(s) hold the CDS data then this would not be the case.
As for where the data resides, the default database for Dynamics is Azure SQL so it is already in SQL. Power BI itself does not store data, it pulls the data into a data model and holds all of your modifications in the applied steps but it does not "store" the data from a "can I go get that data with some other tool" perspective.
In your case, if the folks that have built the CDS and have modified the data in the way that you need, then perhaps that data source would be better for you in the long run because it has the mods already.