1. Power BI best practices - your advice

I am looking for advice on best practices for Dynamics 365 data and Power BI. Power BI has a built-in option to connect to Dynamics 365, but every demo I have seen at user group meetings have used a copy of the database (either to SSAS or Azure). What are the benefits of doing this? What are the best practices for this?

2. RE: Power BI best practices - your advice

The reason you often see an Azure SQL database or similar in demos, is because they load faster and the data has already been transformed at the source. I.e. when you load the data into Power BI, it look exactly like what need. When connecting directly to Dynamics 365 (or CDS) you get database schemanames, you have to deal with changing the option set values (0,1, 2) to the labels (Open, Won Lost) and lookup fields etc. Maintaining an Azure SQL or SSAS is of course an extra layer you have to manage, so unless you need it for another purpose too, or have more than a million or so records, you can connect directly to Dynamics 365. The Azure SQL has one key benefit, which is DirectQuery. That means your Power BI data can be set up to only lag around a minute, whereas when connecting directly to Dynamics 365, you will have to wait for the next refresh, which could be an hour or so. Of course adding an Azure SQL will cost you a little money too.

For help with connecting directly to Dynamics 365, feel free to check out this tool that a friend and I have created. It creates a Power Query string for you, based on fields or views that you select and does all the renaming, changing option set values to labels, name of lookup fields etc, for you. Basically the transformation steps that you need to do regardless. https://crmchartguy.com/power-query-builder/

I am looking for advice on best practices for Dynamics 365 data and Power BI. Power BI has a built-in option to connect to Dynamics 365, but every demo I have seen at user group meetings have used a copy of the database (either to SSAS or Azure). What are the benefits of doing this? What are the best practices for this?

The reason you often see an Azure SQL database or similar in demos, is because they load faster and the data has already been transformed at the source. I.e. when you load the data into Power BI, it look exactly like what need. When connecting directly to Dynamics 365 (or CDS) you get database schemanames, you have to deal with changing the option set values (0,1, 2) to the labels (Open, Won Lost) and lookup fields etc. Maintaining an Azure SQL or SSAS is of course an extra layer you have to manage, so unless you need it for another purpose too, or have more than a million or so records, you can connect directly to Dynamics 365. The Azure SQL has one key benefit, which is DirectQuery. That means your Power BI data can be set up to only lag around a minute, whereas when connecting directly to Dynamics 365, you will have to wait for the next refresh, which could be an hour or so. Of course adding an Azure SQL will cost you a little money too.

For help with connecting directly to Dynamics 365, feel free to check out this tool that a friend and I have created. It creates a Power Query string for you, based on fields or views that you select and does all the renaming, changing option set values to labels, name of lookup fields etc, for you. Basically the transformation steps that you need to do regardless. https://crmchartguy.com/power-query-builder/

I am looking for advice on best practices for Dynamics 365 data and Power BI. Power BI has a built-in option to connect to Dynamics 365, but every demo I have seen at user group meetings have used a copy of the database (either to SSAS or Azure). What are the benefits of doing this? What are the best practices for this?