Design custom reports using OData and Power BI

 

Updated: November 1, 2016

Applies To: Dynamics Marketing

System_CAPS_importantImportant

Microsoft Dynamics Marketing is scheduled to be retired on May 15, 2018. After that date the service will no longer be available. Please plan accordingly. For details, see the blog post Microsoft Dynamics Marketing service will be discontinued, and learn what’s coming next.

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

To enable the OData feed for your Dynamics Marketing instance and find the URL at which you can access it, do the following:

  1. Go to Settings > Administration > Site Settings.

  2. Scroll down to the Organization Data Service section.

    Organization data service settings

  3. 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

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.

System_CAPS_importantImportant

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”.

System_CAPS_importantImportant
  • 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.

  1. 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.

  2. Connect to the Dynamics Marketing data feed.

    1. Launch Excel.

    2. 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.

    3. 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.)

    4. A dialog box prompts you to configure your data connection. Choose the Organizational account option.

    5. 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.

    6. 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.

      System_CAPS_noteNote

      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.

    7. You now return to Excel. Click Save to save your new OData feed.

      More information: Access OData Feeds from Power Query

  3. Design your query. More information: Microsoft Power Query for Excel Help

  4. Create analytic views by using Power View. More information: Power View: Explore, visualize, and present your data

  5. Share the queries and specify who can access them. More information: Share Queries

  6. Save the workbook on Power BI sites. More information: Save a workbook to a Power BI for Office 365 site

System_CAPS_tipTip

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

Show: