Design custom reports using OData and Power BI
Applies To: Dynamics Marketing
Microsoft Dynamics Marketing is no longer available as of May 15, 2018. Your organization has until August 13, 2018 to retrieve your data. For advice about how to download data and assets, and how to clean up Dynamics 365 instances that were previously integrated with Dynamics Marketing, see Prepare for the scheduled sunset of Dynamics Marketing. For more information, see also the customer FAQ and read the blog post Microsoft Dynamics Marketing service will be discontinued, and learn what’s coming next. If you have additional questions, please reach out to MDMquest@microsoft.com.
Create custom reports using Microsoft Office Excel and Power BI for Office 365. Discover and access the data you need, model and analyze it, and visualize it to drive insights.
Using Open Data Protocol (OData), you can access information exposed by a data source, query it, and structure it through create, read, update, and delete operations. Microsoft Dynamics Marketing provides OData feeds that you can connect to from within Excel to create analytic reports using Power Query, Power View, PowerPivot, and Power Map. Using OData feeds you can get read-only access to data tables, which can be filtered and sorted.
With Power BI and Excel, you can:
Do analysis by selecting measures, dimensions, and filters.
See the history of past marketing activities and identify patterns that let you understand why they performed as they did. Power BI and Excel can also combine data from the marketing database with external data sources, like geographic mapping.
See visual summaries in dashboards of the current state of marketing activities, and see progress toward goals.
More information: Self-Service BI in Excel
Enable OData and find your OData URL
To enable the OData feed for your Dynamics Marketing instance and find the URL at which you can access it, do the following:
Go to Settings > Administration > Site Settings.
Scroll down to the Organization Data Service section.
Use the Enable Data Service check box to enable or disable the service. While the service is enabled, you can also read and copy the OData feed URL from here. You will need this URL when setting up a Power BI dashboard, sample workbook or custom workbook.
More information: Administer your site
Available OData feeds and documentation
Microsoft Dynamics Marketing provides the following OData feeds for analytic reports:
Financial feeds: These contain feeds that cover financial transactions and marketing budgets. They allow you to analyze budget utilization, marketing project spending, and revenue comparison.
Scoring engine feeds: These contain feeds to the scoring model which contains a set of rules to define score calculations for a lead.
Marketing database feeds: These contain feeds of all prospects, lead and customer contacts, and transactional information for easy access and analysis.
Behavioral feeds: These contain feeds related to the online activity of prospects on observed websites and landing pages.
Marketing activities feeds: These contain feeds for channels, marketing contexts, programs, campaigns, and email messages related to a marketing activity. This includes all campaign responses and results of behavioral analysis of how email marketing messages have been consumed by prospects.
For a complete technical reference about the feeds that are available, see OData feeds overview.
Many OData feeds include columns that establish relations with other tables. These columns always have a name that ends with “id”. In this case, there will also be another column with the same name that doesn’t end with “id”. For example, your feed may have columns named “eventid” and “event.” Normally, you will be able to expand the non-id column to show additional data columns fetched from the related table. However, for records where no value is included in the “id” column (for example, it shows a value of “0” or “-1”), no relationship has been established between the two tables, so all of your expanded columns will just show a value of “error” (but other records where the relation is established (if any) will return valid values). To prevent “error” columns from appearing, you could filter the results to hide all rows where the relevant “id” column has a value of “0” or “-1”.
Set up an Excel workbook for OData and Power BI
Always be sure to use the latest version of Power Query. If you’re having trouble with your connection, check your Power Query version against the latest version published at the Microsoft Power Query for Excel 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.
If you are using Microsoft Excel 2016, then Power Query is already installed (so you can skip this step). 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.
Connect to the Dynamics Marketing data feed.
If you’re using Excel 2016, then go to the Data tab and choose New Query > From Other Sources > From OData Feed. If you are using an older version of Excel, then go the Power Query tab and choose From Other Sources > From OData Feed.
Regardless of which version of Excel you are using, the OData Feed window opens. Enter the OData feed URL for your Dynamics Marketing organization and click OK. (For details about how to find your OData URL, see Enable OData and find your OData URL.)
A dialog box prompts you to configure your data connection. Choose the Organizational account option.
If you see a set of radio buttons for choosing between two URLs, choose the URL 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 and then submit the form.
Because the workbook is shared between multiple users, the credentials to sign in to Dynamics Marketing aren’t stored in the workbook. All users should use their own credentials.
You now return to Excel. Click Save to save your new OData feed.
More information: Access OData Feeds from Power Query
Design your query. More information:** **Microsoft Power Query for Excel Help
Create analytic views by using Power View. More information: Power View: Explore, visualize, and present your data
Share the queries and specify who can access them. More information: Share Queries
Save the workbook on Power BI sites. More information:** **Save a workbook to a Power BI for Office 365 site
Several sample Excel workbooks are available that contain predefined Power Query connections, queries, and presentational tools. All you need to do is add your sign-in credentials and specify the URL for your own OData feeds. More information: Download, connect, and customize sample Power BI workbooks
Design and generate reports
Grant permissions for reports and OData
Generate built-in reports and work with analytical views
Download, connect, and customize sample Power BI workbooks
Connect Dynamics Marketing to your Power BI Dashboard