Skip to main content

Notifications

Announcements

Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Power BI Data Connection
Customer experience | Sales, Customer Insights,...
Suggested answer

Power BI Data Connection

(3) ShareShare
ReportReport
Posted on by Liamh501 31
Hi,
 
We have on premise Dynamics 365 CRM and I am going around the houses trying to connect to Power BI and it to make sense.
 
I'm trying to connect via the internal database but with everything pivoted and the work its going to take to make it all make sense, I was wondering if there was a better way to see the data that makes sense and almost straight out of the box?
 
Thanks
 
Liam
Categories:
Microsoft Dynamics CRM
  • Liamh501 Profile Picture
    Liamh501 31 on at
    Power BI Data Connection
     
    Thankyou so much for taking the time to reply. That's really useful.
     
    I'm running into a few issues when trying to connect through the API (your recommended).
     
    Can you see any reason why this wouldn't work below? I have triple checked and it is the correct URL. We have on prem Dynamics 365 Customer Engagement
     
     
     
    Below is what I can see when I look for dynamics.
     
     
     
    Thanks
     
    Liam
     
     
  • Daivat Vartak (v-9davar) Profile Picture
    Daivat Vartak (v-9d... 7,336 Super User 2025 Season 1 on at
    Power BI Data Connection
    Hello Liam,
     

    You're right, connecting Power BI directly to the pivoted structure of the Dynamics 365 on-premise database can be a significant and often frustrating undertaking. The schema is designed for transactional processing, not analytical reporting, leading to complex joins and transformations.

     

    Fortunately, there are indeed better ways to connect Power BI to your on-premise Dynamics 365 CRM data in a more structured and "almost straight out of the box" manner. Here are the most common and recommended approaches:

    1. Using the Dynamics 365 Connector in Power BI Desktop (Recommended):

    This is the most direct and often easiest way to connect to your Dynamics 365 data in a reporting-friendly format.

    • How it works: The Dynamics 365 connector in Power BI Desktop understands the underlying structure of Dynamics 365 and presents the entities and fields in a more logical way for reporting. It handles many of the complex joins for you.

       

    • Connection Method:

      • In Power BI Desktop, go to Get Data.
      • Search for and select Dynamics 365.
      • Click Connect.
      • You'll be prompted for the Web API URL of your Dynamics 365 instance. This is typically in the format https://<your_crm_organization_url>/api/data/v9.x/ (replace <your_crm_organization_url> with your actual URL and v9.x with your CRM version's Web API version).
      • You might be asked to authenticate using your organizational account.
      • Once connected, you'll see a Navigator window displaying a list of your Dynamics 365 entities (Accounts, Contacts, Opportunities, etc.).
      • Select the entities you want to report on and click Load or Transform Data. 

    • Benefits:

      • Simplified Data Selection: Entities and fields are presented in a more understandable structure.
      • Handles Relationships: Power BI often automatically detects and manages relationships between entities.
      • Metadata Awareness: The connector understands Dynamics 365 metadata, such as option set labels.
      • Relatively Straightforward: Easier to set up compared to direct database connections. 

    • Considerations:

      • Performance: For very large datasets, performance might be a concern. Consider using dataflows or data warehousing for optimization.
      • Web API Access: Ensure your Power BI Desktop machine can access the Dynamics 365 Web API URL.
      • Security: Data access is governed by the user account you use to connect.  

      •  

    2. Using Dataflows in Power BI Service:

    Dataflows provide a way to prepare, shape, and store data in the Power BI service. You can use the Dynamics 365 connector within a dataflow.

    • How it works: You create a dataflow in the Power BI service, connect to your Dynamics 365 Web API using the connector, transform the data as needed, and then Power BI Desktop can connect to these curated dataflows.

    • Benefits:

      • Centralized Data Preparation: Data transformations are done in the cloud and can be reused by multiple reports.
      • Improved Performance: Dataflows can improve query performance, especially for large datasets.
      • Scheduled Refresh: Dataflows can be scheduled to refresh automatically.
      • Data Shaping: You can perform more complex data transformations using Power Query Online. 

    • Considerations:

       

      • Requires a Power BI Pro or Premium Per User license to create and share dataflows.
      • Adds an extra layer of setup compared to direct connection.


        •  

    3. Utilizing the Common Data Service (CDS) Connector (if applicable):

    If your on-premise Dynamics 365 environment has been configured with the Azure Data Lake Service (ADLS) for analytical data, you might be able to leverage the Common Data Service (CDS) connector in Power BI. This provides an optimized way to access the data in the lake.


    • How it works: Data from Dynamics 365 is periodically exported to ADLS in a denormalized and analytical-friendly format. Power BI can then connect to this data in the lake via the CDS connector.

    • Benefits:

       

      • Optimized for Analytics: Data is structured for reporting and analysis.
      • Scalability: ADLS can handle very large datasets.
      • Performance: Queries against the data lake are generally faster for analytical workloads.

    • Considerations:

      • Requires setting up the Azure Data Lake Service integration with your on-premise Dynamics 365.
      • Data latency might exist depending on the export schedule.
      • Configuration can be more complex initially.

        •  

      •  

    Why Avoid Direct Database Connection for Reporting (Generally):

    • Complex Schema: The database schema is highly normalized and optimized for transactional processing, requiring numerous joins to get meaningful reporting data.
    • Metadata Challenges: Understanding option set values, status codes, and other Dynamics 365-specific metadata requires extra effort.
    • Potential for Breaking Changes: Direct database access can be more susceptible to issues if the underlying database schema changes during upgrades.
    • Security Concerns: You need to carefully manage database access credentials.


      •  

    Steps to Get Started (Recommended Approach):

    1. Install Power BI Desktop: If you haven't already, download and install Power BI Desktop.
    2. Get Your Web API URL: Obtain the correct Web API URL for your on-premise Dynamics 365 instance.
    3. Connect via the Dynamics 365 Connector: In Power BI Desktop, use the Dynamics 365 connector and enter your Web API URL.
    4. Explore Entities: Browse the list of entities in the Navigator and select the ones you need for your reports.
    5. Transform Data (if necessary): Use Power Query within Power BI Desktop to perform any necessary data shaping, filtering, or calculations.
    6. Build Your Reports: Create visualizations and dashboards based on the loaded data.
    7. Publish to Power BI Service: Once your reports are ready, publish them to the Power BI service for sharing and collaboration.

      8.  

    By using the Dynamics 365 connector, you'll likely find a much more intuitive and "almost straight out of the box" experience for accessing your CRM data in Power BI compared to wrestling with the raw database structure. Start with this approach and explore dataflows or ADLS integration if you encounter performance issues with large datasets or need more advanced data preparation capabilities.

     
    If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.
     
    My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.
     
    Regards,
    Daivat Vartak
  • Liamh501 Profile Picture
    Liamh501 31 on at
    Power BI Data Connection
     
    Apologies, I think I may have worded that wrong.
     
    I'm having problems from a purely data point of view and getting the data to read in the correct way.
     
    E.G I can bring in multiple tables from a database from our on-Prem, but everything is pivoted and in lookup tables which is going to take some point to unpack.
     
    I didn't know if there was a better way to do this and out of the box connection
     
    Thanks
     
    Liam
  • Suggested answer
    Daniel Norris Profile Picture
    Daniel Norris 43 on at
    Power BI Data Connection
    Have you tried using the out of the box templates for Marketing/Customer Insights? (i am making this assumption based on the forum)
     
    It depends what you what data you want to present.
     
    Try using one of these Download and use marketing analytics templates and sample reports for Power BI - Dynamics 365 Customer Insights | Microsoft Learn and linking into your Dynamics environment. They have options to use sample data too so you can understand what it might look like for you. 
     
    I haven't used these for real time yet, only outbound, but it might work. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Daivat Vartak (v-9davar) Profile Picture

Daivat Vartak (v-9d... 225 Super User 2025 Season 1

#2
Muhammad Shahzad Shafique Profile Picture

Muhammad Shahzad Sh... 106 Most Valuable Professional

#3
Eugen Podkorytov Profile Picture

Eugen Podkorytov 102

Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans