Download, connect, and customize sample Power BI workbooks
Applies To: Dynamics Marketing
Microsoft Dynamics Marketing has been discontinued and is no longer available. All customer data related to this product has been permanently deleted from all Microsoft servers and is no longer available for extraction. This documentation is being provided for historical purposes only.
Microsoft Dynamics Marketing provides a number of sample analytic workbooks that let you use visualizations and analytic features of Power View, PowerPivot, and Power Map. Each workbook has a predefined Power Query connection to connect to the Microsoft Dynamics Marketing OData feed service URL. You also get ready-to-use analysis functions with predefined options for filtering and customizing the views.
Download and install the sample workbooks
The sample workbooks are available for download on the Microsoft Download site. To download and install them:
Download the analytics workbooks installer from the Microsoft Download site.
Run the install program that you downloaded.
The install program installs the workbooks here:
C:\Program Files (x86)\Microsoft Dynamics Marketing\Analytic Workbook Samples\
Open any of the workbooks stored here to get started.
Connect a workbook
You must have Power Query installed to use the sample notebooks. If you are using Microsoft Excel 2016, then Power Query is already included. If you are using an older version of Excel, then install the Power Query for Excel from the Microsoft Power Query for Excel download page.
Always use the latest version of Power Query; if you’re having trouble with your connection, please check your Power Query version against the latest version published on the download page.
We strongly recommend using the 64-bit version of Microsoft Office Excel. You may experience program errors with large data sets if you use the 32-bit version.
Always work with a writable (not read-only) copy of your Excel workbook. You may experience program errors when working with a read-only workbook that includes OData feeds.
Find the OData feed URL for Microsoft Dynamics Marketing.
Go to Settings > Administration > Site Settings.
Under Organization Data Service, select the Enable Data Service check box.
Note the URL just below the protocol, and copy it.
Open one of the workbooks that you downloaded and installed.
If you see a security warning, click Enable Content.
If your notebook is read-only, then do a Save As to save it with a new name and/or location.
Do one of the following, depending on which version of Excel you’re using:
If you’re using Excel 2016, then go to the Data tab and click Show Queries.
If you’re using an older version of Excel, then go the Power Query tab and click Show Pane.
You now see a list of queries in the Workbook Queries pane. If your list of queries includes one called MarketingServiceURL, do the following (if you don’t have this query, skip this step):
Right-click on the MarketingServiceURL query and choose Edit. When the Query Editor opens, on the Home tab, click Advanced Editor.
Replace the Source URL value with the URL you copied in Step 1c, and then click Done. In the Query Editor, click Close & Load.
You might now (or soon) be asked to enter user credentials for accessing the feed. If so, see the note at the end of this procedure for instructions.
If your list of queries does not include one called MarketingServiceURL, do the following (if you do have this query, you should have already done the previous step and should skip this step):
Right-click the first unedited query in the Workbook Queries list, and then choose Edit. (You will eventually need to edit all of the queries.)
The Query Editor window opens. Under Applied Steps, double-click on the Source step.
Enter the OData feed URL that you copied in Step 1c, and then click OK.
You might now be asked to enter user credentials for accessing the feed. If so, see the note at the end of this procedure for instructions.
To test that no errors occur because of a mismatching data model, select the last step under Applied Steps.
Click Close & Load on the Home tab of Query Editor window ribbon.
Repeat the above steps for each query in the Workbook Queries list.
On the Power View tab, choose Refresh > Refresh All.
At some point while setting up your OData connection, you may see a pop-up window called Access an OData feed, which asks for the credentials required to access your Dynamics Marketing OData feed. Usually, you only need to do this once. The point in the procedure when you are asked for this depends on which sample workbook you are using and a few other factors.
In the Access an OData feed window, click Organizational account.
If the Access an OData feed dialog displays a set of radio buttons asking you to choose between two URLs, select the one that ends with “/analytics”. You may have trouble authenticating your account if you choose the other option.
Click the Sign in button to open a sign-in window. Enter the user name and password for an account that has access to the OData feed on your Dynamics Marketing instance and then submit the form.
Click Connect to open the connection.
More information: Access OData Feeds from Power Query
Design and generate reports
Grant permissions for reports and OData
Generate built-in reports and work with analytical views
Design custom reports using OData and Power BI
Connect Dynamics Marketing to your Power BI Dashboard