Power BI and Microsoft Dynamics Integration

[Erika16]

Hello Power BI community 

We built a data model to bring data from Microsoft Dyanamics to Powr BI. There are two ways of using it via Power BI  quary or via SQL. In terms of performance? which of the two would you suggest? I don't fully undestand where the data sits when I bring it from Microsoft Dyanamics to Power BI. Could someone please explain that to me? 

Many thanks as always!!! 

Erika 

[collinq · Accepted solution]

Hey @Erika16 ,

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.

[collinq]

HI @Erika16 ,

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. 

[Erika16]

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 · Accepted solution]

Hey @Erika16 ,

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.

[Erika16]

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 · Accepted solution]

Hey @Erika16 ,

 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.