US - Illinois - Chicago - CRM

Expand all | Collapse all

Power BI and CRM On Premise

  • 1.  Power BI and CRM On Premise

    Posted Aug 10, 2019 08:43 AM

    We're starting to roll out a few instances of Power BI.  Is there anything to be concerned about from a performance standpoint regarding this tool hitting our production CRM database?  Microsoft allows it in the cloud so I'm thinking it may be okay.  I don't want to have to define a whole new data warehouse to enable this tool in the organization.  I'd appreciate any thoughts and experiences.

    Donna Liotta
    Manager Business Applications
    National Safety Council
    Itasca IL

  • 2.  RE: Power BI and CRM On Premise

    Posted Sep 04, 2019 08:05 PM
    Hi Donna, PBI won't be much different than SRS with regard to the CRM DB performance.  Similar bottlenecks apply for very large datasets. We've found the interactive nature of PBI can create additional load that we didn't have in our SRS reports.   We are pulling data from our on premise instance into a separate Datawarehouse-----in our case we are using PBI to connect to AAS via DirectQuery.  Underyling, we use SQL Row Level Security.  We have significant performance problems with the Row Level security - but we are parsing hundreds of thousands of rows at times.   Looking for a solution............

    Kurt Holzwarth
    Director of Information Services
    Spraying Systems Co.
    Wheaton IL

  • 3.  RE: Power BI and CRM On Premise

    Posted Oct 15, 2019 01:01 PM
    Donna, a great question to something that doesn't get a whole lot of visibility due to the "on-prem vs online" dynamic being pushed today. It might be best to set up some standards for the organization to leverage the Import vs Direct Query functionality within PowerBI, not only does Import contain more PBI functionality, but it also reduces the overall load on the source system over time. Also, it is worth noting that if performance is a true concern, using an on-premise "cached" SQL DB or leverage Azure to do a similar option. Then, PowerBI can connect to these sources instead of the Dynamics environment directly.

    Hope this helps!

    Timothy Noce
    Manager - CRM
    AEP Energy, Inc.
    Chicago IL

  • 4.  RE: Power BI and CRM On Premise

    Posted Nov 12, 2019 02:14 PM
    Great suggestions from Timothy. I see this thread is kind of old and you might have solved these problems already. I'll just share what we recently did in our internal dashboard project.

    We don't do power BI but use Tableau and I can connect with some of the points mentioned here. We don't do live queries on data sources like CRM/SAP. All required data gets cached in Tableau on defined schedules. That sounds like the Import option in power BI.

    Other point is carefully understand the user interactions in the dashboards and accordingly prep the data sets in the datawarehouse layer. The slicing and dicing you want to allow in the UI will often dictate the backend data model. The models always need to be built with performance in mind. Extract plain data with minimal hit on the source system, massage and then load it into the data layer which will be read by power BI.

    Finally, to not overburden the CRM transactional database, we used a always on - high availability feature to replicate the full database into a readonly replica. All our external reporting loads now hit this copy.

    Hope some of this helps..

    Mayank Agrawal
    CRM Team Lead
    Medline Industries
    Northfield IL

  • 5.  RE: Power BI and CRM On Premise

    Posted Nov 12, 2019 04:58 PM
    Edited by Nelson Johnson Nov 12, 2019 05:00 PM
    When using PBI Desktop, each user is going to have their own version of the PBI report file and data from different points in time, depending on when (or if) they refreshed. This can cause users to lose confidence in the data if they start comparing numbers and find they are different.

    If you buy Power BI Pro ($5/user/month) and publish your reports on-line, you can also use the Azure Gateway to allow the Power BI service to pull data from your SQL database on a schedule which will give you control of when that data is extracted. Then everyone is using the same version of the report and they are querying form the same dataset and the numbers should be consistent.

    If this answered your question, please click on the arrow button next to Reply Inline and choose 'Make Best Answer.'
    Nelson Johnson, Solution Architect
    BroadPoint, Inc., Bethesda MD
    Link with me!

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