CONNECTING POWER BI TO ONPREMISE DYNAMICS CRMGP DATA

CONNECTING POWER BI TO ON-PREMISE DYNAMICS CRM/GP DATA

Introduction - Your Presenter • Steve Ivie • Enterprise Solution Architect, Tribridge – Over 50+ implementations Microsoft Dynamics 365, CRM, ERP and BI&A – Author of • Building Dynamics CRM 2015 Dashboards using Power BI • Building Power BI Dashboards for Dynamics 365 (Winter 2017)

Objectives • Options for connecting to your On-Premise CRM and ERP data using Power BI • Review a few joined datasets and reports • Share your data by pushing it to the Cloud for end users to view 3

Data Gateway • On-premises data gateway: – – – • Direct Access to Dynamics CRM & ERP SQL Databases Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, Power. Apps, Flow or Logic Apps along with SQL Access to the Dynamics CRM database. Includes support for both schedule refresh and Direct. Query Personal: – – Direct Access to Dynamics CRM & ERP SQL Databases This is for Power BI only and can be used as an individual without any administrator configuration. This can only be used for on-demand refresh and schedule refresh. This will launch the installation of the personal gateway Personal SQL Access to the Dynamics CRM Database https: //powerbi. microsoft. com/en-us/documentation/powerbi-gateway-onprem/

Excel • • Use Exported Dynamics CRM Excel files on premise, data files can be stored Locally, on One. Drive or Share. Point and used a data sources in Power BI. Set up On-Premise Data Refresh in Power BI will help refresh data directly from the data source to dataset. https: //community. dynamics. com/crm/b/microsoftdynamicscrmsolutions/archive/20 15/08/13/on-premise-data-refresh-in-power-bi

Azure • • • Copy & Synchronize Dynamics CRM databases to Azure Straightforward connection to an Azure SQL Database Using the Power BI Desktop, connected to Azure SQL Database, you can Create & Publish reports Power BI has a multitude of Azure connections available Direct. Query with Azure SQL Database gives realtime data visualization. https: //docs. microsoft. com/en-us/azure/sql-database-get-started

ADFS / IFD • Enable OAuth on the Microsoft Dynamics 365 Server. To do this, open a Windows Power. Shell window and run the following Power. Shell commands. Add-PSSnapin Microsoft. Crm. Power. Shell$fedurl = Get-Crm. Setting -Setting. Type Claims. Settings$fedurl. Federation. Provider. Type = 1 Set -Crm. Setting $fedurl

ADFS / IFD • • After you run the previous commands you need to restart IIS. In a command window, type IISReset, and then press ENTER. Register the Power BI Desktop OAuth 2. 0 client with ADFS. To do this, open a Windows Power. Shell window and run the following Power. Shell command on the PC where you are running Power BI Desktop that will be used to publish your reports to the Power BI service. Add-Adfs. Client -Clent. Id "a 672 d 62 c-fc 7 b-4 e 81 -a 576 -e 60 dc 46 e 951 d" -Name "Microsoft Power BI" -Redirect. Uri @("https: //de-userspreview. sqlazurelabs. com/account/reply/", "https: //preview. powerbi. com/views/oauthredirect. html") -Description "ADFS OAuth 2. 0 client for Microsoft Power BI"

ADFS / IFD • In Power BI Desktop sign in to Power BI, and then use the standard OData Feed connector to connect to your Dynamics 365 (on-premises) system by using Windows or OAuth authentication. To do this… – Connect to a data source in Power BI Desktop or Excel using the OData Feed connector. • For internal (non-IFD) deployments enter the URL for the OData feed similar to https: //server/crmorganization/api/data/vn. 0 where server/crmorganization is the web app path for the Microsoft Dynamics 365 organization and vn. 0 is the version of Microsoft Dynamics 365, such as v 8. 0 for Microsoft Dynamics 365. For example, https: //contoso 1/contosocrm/api/data/v 8. 0. – • When you are prompted to sign-in to access the OData feed, select Windows from the list of authentication types, and then enter your Dynamics 365 (on-premises) credentials. For IFD deployments, enter the URL for the OData feed similar to https: //[organization_URI]/api/data/vn. 0, where organization_URI is your Internet accessible full domain name and vn. 0 is the version of Microsoft Dynamics 365, such as v 8. 0 for Microsoft Dynamics 365. For example, the Contoso organization has this URL: https: //crmweb. contoso. com/api/data/v 8. 0. – When you are prompted to sign-in to access the OData feed, select Organizational account from the list of authentication types, and then enter your Dynamics 365 (on-premises) credentials.

Multi-Source Dataset • • • The organization CRM & ERP database tables are loaded from which you can build reports. Publish your multi-source reports to Power BI and build dashboards. Refresh your reports by publishing them again with Power BI Desktop or Excel on a frequent basis.

Embed Power BI Tiles • • Embed Power BI tiles on your personal dashboard Add a Power BI dashboard on your personal dashboard Sharing a personal dashboard that contains Power BI visualizations Applies to Dynamics CRM 2016 and Dynamics 365 Online

Embed Power BI Reports • • Purpose of this setup is to embed interactive Power BI reports in CRM Online Dashboard so that it can be viewed by end users directly within CRM instance Create an HTML webpage for accessing Power BI Reports

Publish to the Web • Power BI “publish to web” allows you to easily expose a Power BI report to the world through an iframe that can be embedded wherever you like