Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Power BI best practices - your advice

  • 1.  Power BI best practices - your advice

    GOLD CONTRIBUTOR
    Posted May 14, 2019 09:13 AM
    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?

    Thank you-
    Adam

    ------------------------------
    Adam Posegate
    Fox Valley Technical College
    Appleton WI
    ------------------------------


  • 2.  RE: Power BI best practices - your advice

    SILVER CONTRIBUTOR
    Posted May 15, 2019 09:15 AM
    Hi Adam

    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/ 

    ------------------------------
    Ulrik Carlsson
    Sr. Solution Architect (CRM Chart Guy)
    eLogic
    Petaluma CA
    ------------------------------



  • 3.  RE: Power BI best practices - your advice

    GOLD CONTRIBUTOR
    Posted May 15, 2019 02:56 PM
    The Power BI Query Builder is really great! I will explore this further. The queries already work much more quickly than the sample PBIX from Microsoft. Maybe this will solve my problem.

    Adam

    ------------------------------
    Adam Posegate
    Fox Valley Technical College
    Appleton WI
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role