Sonoma Partners Microsoft CRM and Salesforce Blog

Using Q & A to Create Reports in Power BI

Today's blog post was written by Brendan Landers, VP of Consulting at Sonoma Partners.

Power BI Q&A enables users to ask natural language questions and get answers in the form of visuals or reports automatically created with the data that best answers their question.  Q & A has been available in Power BI for some time now, but it was only available for datasets with visualizations that had been pinned to dashboards in the Power BI Service.  The end user could leverage Q & A within those dashboards, but someone had to design the dataset and visualizations with Q & A in mind for it to be useful.  This video published by the Power BI team showcases how the functionality works through Dashboards.

With the December release of Power BI, you can now use Q & A to create your reports.

In this blog post, I will show you how to do so in the latest version of the Power BI Desktop.

To illustrate, I have a simple excel file with four tabs which I am using as the data set.  The tabs are:

  • Customer – a sample customer list
  • Opportunities – a sample list of opportunities
  • Orders – a sample list of orders
  • Users – a sample list of AEs

We could easily connect this to Dynamics or Salesforce to get those objects for your data set, but I am using excel for the sake of simplicity.  Additionally, I have setup simple relationships between the objects using the autodetect feature. 

Brelanders 1

Once I have my data setup, I can start using Q & A to create reports. 

*Please note Q&A for creation is a Preview feature, and you may need to enable it through options (Click on Start > Options and Settings > Options > Preview Features).

Next, to use the new feature you will first click the Ask a Question button in the ribbon. 

Brelanders 3

Once you do so, you will see a blank visualization appear with the Q & A box available for input.

Brelanders 4

Now we are ready to build a chart.  First, let’s look at the Weighted Revenue by Year for each of our AEs.  The fields I need in my data set for this report are the following:

  • Weighted Revenue (from Opportunity)
  • Expected Close Date (from Opportunity)
  • AE (from User)

In the Q & A input box, I will start typing.  I first start with Weighted Revenue.  As soon as I begin typing you will notice intellisense auto-showing the fields.  In this case, by just typing the letter "W" I can see the field I need.

Brelanders 5

I select Weighted Revenue, and Power BI provides a Card visualization with the sum of weighted revenue in my Opportunity table.  Pretty cool.  Next, I want to see the data by AE.  To do so, I type the word "by," the field I need, and voila!  After that selection, Power BI shows me a bar chart of Weighted Revenue by AE.  Even cooler.

Brelanders 6

While that is interesting, I’d like to take it a step further to make this more meaningful.  To do so, I am going to add Expected Close Date into the mix to see Weighted Revenue by AE by Year.  I’m guessing at this point you get how I might do so, but for those who jumped to the end, I will add the words "By Expected Close Date" to my Q&A.  As I type in Expected Close Date, I notice that Power BI starts to show off a bit and, since it’s a date field, it serves me some options from a date hierarchy. 

Brelanders 7

I am going to select the Expected Close Year option which will show the data by rep by year.

Brelanders 8

And we are done!  In literally seconds any user can create meaningful charts with simply typing what they want.  The coolest.

If you have questions about this post or anything Power BI related, feel free to reach out.

Topics: Analytics Microsoft Dynamics 365

Power BI Accelerator for XRM Toolbox

Today's blog post and tool were developed and written by Keith Mescha, Principal Architect at Sonoma Partners.

The Dynamics CRM community asked, so we are delivering.

Power BI Accelerator for the XRM Toolbox is now available for download from our website.

If you are implementing or supporting a Dynamics CRM implementation and don’t know about XRM Toolbox then push that rock off you and go read this first.

Powerbi xrm1

In case you missed it back in June we release a tool for helping you with using your Dynamics data in Power BI. Please read the original blog post for more details or watch our video overview on our You Tube Channel. Additionally, I was recently asked to speak about this on the CRM Audio podcast. The community response to this original tool was great and we received some great feedback which we have incorporated into the current build (v1.1.8).

Many folks, including a couple we ran into at the CRMUG Summit last month have asked us to build a version for XRM Toolbox. One huge advantage of this is that you don’t have to be a CRM admin in an org to generate Power BI queries. Seeing that our original implementation was solution driven you had to have access to the Setup portion of Dynamics.

Now with this new XRM Toolbox Plugin you can use that framework to connect to any org you have access to and generate the Power BI queries you need without having admin access to the org. You also don’t need to install anything into your org to utilize this version as you do with the managed solution version.

All the prior fixes in the managed solution version are included in this 1.0 release. As will all first releases there are some known issues but we wanted to get it out so you can start to benefit immediately. Happy to take any feedback via comments to this post and we will do what we can to continue to enhance the plugin over time and release new versions.

The plugin can be downloaded on our website for you to try out. We hope to get this into the Plugin Store soon but for now it’s a manual install with 2 easy steps.

  1. Download/Run XrmToolbox on your machine if you haven’t already to generate the plugins folder.
  2. Place the DLL in %appdata%\MscrmTools\XrmToolBox\Plugins.
    1. %appdata% is C:\Users\<username>\AppData\Roaming\ if it’s for some reason unbound on your machine.

Once installed you can connect to your org just like any other XRM Toolbox plugin. Upon connection, you can press the “Retrieve Entities” button. From there the interface is very similar to what is spelled out in the original blog post.

Generate a query in 3 easy steps

  • Pick an entity
  • Choose a view
  • Click Generate

Powerbi xrm2

After you click Generate you are presented with a screen that allows you to copy the queries into Power BI Desktop Directly. If you used the previous solution you will notice we cleaned up the query generated in this version so it’s bit easier to read and debug.

Powerbi xrm3

From here normal Power BI modeling best practices kick and you should have some reports and dashboards up and rolling in no time.

Please be aware of a few small known issues we have on our list to address in the near future.

  1. The user is required to hit Retrieve Entities once they load the tool and connect to an Org.
  2. There are some performance issues with drawing the attribute checkboxes where the page freezes for larger entities with many attributes.
  3. Personal/System Views are in the same drop down and not split out so it’s not apparent they are all there.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365

Connecting the Power BI On-Premise Gateway to a SQL Server Analysis Services Tabular Database

Today's blog post was written by Neil Erickson, Principal Developer at Sonoma Partners.

We’ve been surfacing a lot of our information for internal users via Power BI, and recently we wanted to author a round of reports that accessed an SSAS tabular database directly. We ran into some early snags, which were easily resolved by following some of the solution posted by the Power BI Team here.

Our takeaways from these early issues were to ensure a few things:

  1. Confirm that the account entered in the gateway configuration has been given Full Access within tabular database.

    Nerickson 1
  2. Confirm that the UPN that Power BI is using, the one on your Office 365 User object, matches the UPN of the target User in Active Directory.

Even with these changes in place we continued to see our report failing to load anything. We knew that the gateway was using EffectiveUserName to impersonate the end user, so we tried to determine if this was the culprit. In SSMS you can pass in the EffectiveUserName by going to the Connection Options and selecting the “Additional Connection Parameters” tab. By doing this, we captured in SQL Profiler the same error that we were seeing when the Power BI report would fail.

Nerickson 2

The following system error occurred: The user name or password is incorrect.

Nerickson 3

Nerickson 4

At this point we were confident that permissions were set properly within Analysis Services, so we began looking elsewhere. The step of verifying the UPN led us to Active Directory, and we discovered that making the account that runs Analysis Services an Domain Administrator solved the issue. This was not desirable as a long-term solution for obvious reasons so we continued to try various settings. Eventually whittled down the necessary permissions to Read on the AD User that is being specified by EffectiveUserName. To achieve this, we took the following steps.

1. Create a new Security Group and add the account running the SSAS Service to this group

We did this because we like to separate accounts that run different applications, and we like to retire old account when migrating to new versions of applications. It seems like this permission will be common, so our preference was to add the permission one time and manage the group membership as needed.

2. Grant the new security group read permissions to user objects in Active Directory

This permission can be granted to individual objects, or to an OU at a level that is sufficient to cover any users that would be passed into EffectiveUserName.

Nerickson 5

If this is being granted to an OU, click into “Advanced” and verify that is will apply to descendant objects as well. If it reads “This object only” edit the row and set “Applies To:” to include descendant objects as well.


Nerickson 6


Nerickson 7

3. Restart the SSAS Service

Initially the report in Power BI still was showing the error but after waiting a few minutes and trying again it work. Are assumption here is Power BI has some caching so key point is when changing domain level security make sure you provide ample time between tests for settings to proliferate through the various layers.

Now our report is accessible on and is connecting to our On Premise Analysis Services Tabular Model deployed in our Data Center via the Enterprise Gateway.

Nerickson 8

Please reach out if you have any questions by filling out our contact us form or commenting below.

New Call-to-action

Topics: Analytics Microsoft Dynamics 365

What if Power BI could help you forecast better?

Today's blog post was written by Brendan Landers, VP of Consulting at Sonoma Partners.

A new feature in the August release of Power BI is the What If parameter. This allows you to define values for a slicer that can be used in DAX formulas allowing you to look at data differently, based on what if scenarios. From my perspective, this is applicable when looking at opportunities.

Traditionally, we look at weighted revenue in forecasting opportunities with varying levels of success. Probability is typically self-reported by the sales representative or based on an opportunity status that is self-reported by the representative. While we are working on predicting opportunity outcomes using Azure Machine Learning, I still believe past performance is the best indicator of future results. I know with certainty what percentage of opportunity revenue we’ve closed in the past, so we can apply that to future opportunities to obtain forecast.

Below I hope to highlight how you can use the What If parameter in Power BI to look at a forecast.

The example will be rather basic to illustrate the capability of the feature, but you could get quite complex with this type of analysis.

First, in the Power BI Desktop I’ll start with a simple data model that includes Customers, Opportunities, and Users. I’ll create two charts. First, using a Clustered Bar Chart I’ll show the Expected Revenue by Sales Rep. Next, using a Clustered Column Chart we’ll show the Expected Revenue by Year and Quarter.

Blanders 1
Click the image to expand.

Next I am going to add in a What If Parameter.  To do so, under Modeling in the What If section, select New Parameter. 

Blanders 2
Click the image to expand.


The What-if parameter dialog is launched.  Here I will Name my parameter What If Percentage.  I will leave the Data Type as a whole Number, and set the Minimum to 25 and Maximum to 125.  This will allow me to see what if scenarios between 25% and 125% of the current pipeline, which I will show in a minute.  Leave the Add slicer to this page box checked and click OK

You will now see a new table on the right-hand side for What If Percentage.  Under the table, you will see two options.  The top option is the slicer values.  To add this to the page, create a new slicer chart and select the field.

Blanders 3
Click the image to expand.

We can now toggle the slicer between the values of 25 and 125, but we need to apply this to the expected revenue field to see the data in motion.  To do so, I’ll create a new measure in the Opportunity table called What If Revenue.  The formula is as follows:

What If Revenue = sum(Opportunities[Expected Revenue]) * ('What If Percentage'[Parameter Value]/100)

You can see we are multiplying the Opportunity Expected Revenue with the What If Parameter (after converting it to a percentage by dividing by 100).  We now have a dynamic value in our Opportunity table.  Next, we will add the new measure as values in our bar and column charts to view the Expected Revenue side by side with the What If Revenue.

Blanders 4
Click the image to expand.

So, now you can see with an Achieve Percent of 100, the values are equal, but as I slide the slicer I see the what if value change in real-time.  For example, if I slide the slicer to 60, I can see what the numbers would look like if we hit 60% of the expected revenue.

Blanders 5
Click the image to expand.

I hope this illustrates how you can apply the new what if parameter to your Dynamics 365 for Sales data to help forecast your pipeline. Let us know if you have any questions by commenting below.

Topics: Analytics Microsoft Dynamics 365

Power BI Accelerator for Dynamics 365 Update

Today's blog post was written by Keith Mescha, Principal Architect at Sonoma Partners.

In case you missed it, back in June we release a tool for helping you with using your Dynamics data in Power BI. Please read the original blog post for more details or watch our video overview on our YouTube Channel. I also covered this topic on the CRM Audio podcast.

The community response to this tool was great, and we received some feedback that we have incorporated into a fresh build. V1.1.7 was released and our download page has been updated with this version that addresses these key areas:

  • Datetimes will actually be of the datetime type in Power BI (you don’t need to do any conversion, we do that for you).
  • If the query you run has no data, an empty table will be generated with the correct column headings. Previously an error saying that “Column1 does not exist” was popping up.
  • There was an unreported error that sometimes the URL being generated was incorrect. For example, /actioncard instead of /actioncards.
  • The entity list was sometimes not in Alpha order. It now is sorted correctly
  • Removed non-user entities from the list that are not available through advanced find (e.g. actioncard). This should only remove some system entities that normal users don’t need access to anyhow.

We have made this Dynamics Managed Solution available on our website for you to try out.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365

Accelerate Your Dynamics 365 to Power BI Analytics Path

Today's blog post was written by Keith Mescha, Principal Architect at Sonoma Partners.

If you have tried to connect to your Dynamics 365 org via the OData API feed through Power BI, you probably struggled to make sense of how to build something useful from that experience. I’m sure you have read all the blogs out there suggesting the solution of syncing the data down to a SQL database and building your reports from there. It is often a decent solution but requires additional setup, configuration, licensing, and gets beyond the abilities of many Dynamics Admins. We do support that approach and have delivered several solutions using this method including for our own reporting needs. However, we also felt there is a need for a better, quicker option for customers.

To that end, we sat down and designed an accelerator for Dynamics to make this a reality.

Why is this a better option?

  1. You can use existing Dynamics system or personal views to auto create Power BI Queries. We don’t limit you to this as you can also augment those with other fields, but they serve as good starting point. Not sure what you need in your report, use advanced find get close and save that as a view and then build that out more later as you go.

  2. Options sets and lookup values are automatically mapped for you in the query. When hitting the OData API by default, you get the integer values and GUIDs of options sets and related records. This requires a lot of additional work to relink related tables and values which is tedious work that is hard to maintain over time. With this tool, we do that mapping for you based on the Dynamics configuration.

  3. All fields are renamed to the labels as you have defined them in your Dynamics configuration. This cuts down on a bunch of work typically required when pulling in data other ways as schema names are what usually comes over. No more typos or copy paste errors, and the reports you build will have the same field names as users see in Dynamics on forms and views.

  4. Also is using the well-documented FetchXML queries options in the WebAPI you are limited to the 5,000 record limit to you queries. Well we have written some sweet M code to overcome that limit paging. Best of all you can deploy this to the PowerBI Service and schedule updates of your data so you are not limited to just PowerBI Desktop.

*Currently this solution will work in PowerBI Desktop for Online and on Prem IFD enabled Dynamics orgs. However, if you deploy your pbix file to the Online PowerBI Service you will only be able to schedule data refreshes if you have a Dynamics 365 Online Org.

Overall the main goal here is to let you work in Power BI building cool charts and analysis to solve your challenging business problems and less time plumbing and wrangling your data into Power BI from Dynamics.

Here is a quick walkthrough of what this solution provides.

Once you install our managed solution into your org you can access the tool via the solutions area of Dynamics.

Keith 1

Upon launch, you can pick any entity and any view saved against that entity. In this example, we are using the out of the box Active Accounts view. After selecting that view I can chose additional attributes or even select the “All Attributes” option and pull all fields. I will caution that the more you pull, the slower and harder your data will be to work with in Power BI so the best practice is to be as selective as you can in building out a query.

Keith 2

One you have all the attributes you need you can click the Generate button.

Keith 3

At this point we present to you a screen where you can copy queries to the Power BI desktop application. The first item is the URL for the API of the org you are in. Be diligent with the version at the end of the query. Dynamics 365 is going to be V8.2. This serves as the anchor for other queries and should be created first in Power BI as other queries will rely on this.

Keith 4

Copy this URL into a Blank query in Power BI desktop and rename that query “CRMServiceUrl”. Casing is important here so be sure to make sure it’s put in exactly as seen here.

Keith 5

On the Accelerator page hit the Next button to move onto the next query which is the main data query for the Account view we selected. As you can see here there is quite a bit going on. What this step is doing is generating a FetchXML query using an OData Query against the WebAPI. This is returned to us as a JSON document which we use the M language of Power BI to parse into a table and then rename all the fields to the Labels as you have defined in your org configuration. Finally returning that into usable data set, essentially doing all the heavy lifting of pulling your data out of Dynamics for you so you can focus on building reports.

Keith 6

Paste that query into Power BI as another blank query and rename it “Accounts.”  Notice that this query is referencing the URL we created in our first data set.

Keith 7

This point you should have a valid data set with fields that match your labels on the entity as setup in Dynamics. You will need to follow the auth prompts and login to your org to refresh the data.

Keith 8

You can build whatever reports you need at this stage in Power BI. If you want to pull other related data from Dynamics, you can do that and model the relationships in Power BI as needed.

Keith 9

Here I have created a simple chart counting the number of accounts by City. I then deploy that solution to My Workspace on At this point I can go into my deployed data set and schedule a refresh for my data.

Keith 10

From here I can enter credentials for my org. Note that the data refresh will use that account when making the calls to the API so if be sure to use an account that has the rights to read all the data.

Keith 11

Choose Oauth2 as the Authentication method, which from we have found is only currently available with Online orgs. We have tried on IFD enabled orgs and do not get the Oauth2 prompt to allow us to configure the refresh. You will be prompted to enter your credentials on a few other pages not show here. After this step is complete your data will start refreshing on the schedule you set.

Keith 12

You can check the Refresh history from the dataset area in

Keith 13

From here you can keep on building out additional queries by using our solution then copying those queries into your Power BI desktop file. After you make changes deploy them to the Power BI Service for use in dashboards.

We have made this Dynamics Managed Solution available on our website for you to try out.

*Please make sure that you have downloaded and are always using the latest version of Power BI Desktop when using this solution.

If you'd like to view this in solution in action, you can watch our demo video here.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365

Power BI Online Integration with Dynamics CRM On-Premise

Today's blog post was written by Hayden Thomas, Associate Developer at Sonoma Partners.

Integrating Power BI online with Dynamics CRM On-Premise is not currently supported natively. Recently we had a need to integrate a Power BI Report with a Dynamics CRM On-Premise environment, so we needed to create a custom solution to enable embedding reports and dashboards from Power BI into CRM.

Power BI natively allows reports to be ‘Published to Web.’  Doing this would allow us to simply IFrame the report on a Dynamics form or dashboard, but this makes it accessible to anyone who may have the link which is not very secure. This is unsuitable as the reports we’re looking to embed may have sensitive data which we want to make absolutely sure no one has outside access to.

In our case, we are connecting to Power BI through Azure. Azure uses OAuth 2.0 and Active Directory services for authentication. We need to be able to store:

  1. A Client ID that represents a connection to Power BI through Azure.
  2. A Tenant ID for our Azure Active Directory.
  3. An access token that will allow us to request a report from Power BI.
  4. A refresh token that will allow us to programmatically keep our authentication alive, so that we don’t need to continuously keep putting in our username and password.
  5. The lifespan of the authentication and the date-time we obtained it, so we can check if our current authentication is still good.

The Client and Tenant ID are the same for all of the users in our org, so we simply created a configuration record to hold these values.

The other fields will either be created for every user and we need to ask the user to enter their authentication to populate these fields, or we need to create a service account that can be authenticated in the background without the user needing any information on how the reports are being displayed.

Our solution used the second option. Our reports are shared amongst a group in Power BI. In order to have everyone log in and have their own tokens, it would require them to all be added to that group (and in turn, require everyone to have a Power BI Pro license). This also allowed us to just add the authentication fields to the configuration record, along with the service account’s username and password.

Powerbi brendan 1

Our next step is to make sure we actually have an app registered to our Azure AD that we can authenticate this user against. If we log in to, we can register one directly to ensure that it’s set up correctly. In order to make sure we don’t need to handle anything with redirect URLs, since we expect to move this around to different orgs without much issue, we use Native app from the App Type drop down, and for our redirect URL we use this link. For our case, where we only want to be able to read dashboards/reports, we only give it the read all dashboards and read all reports access levels. Once done, we can click Register App to obtain the Client ID we will use for our configuration record.

We have our app created, but haven’t yet given permission from our service account to the app to be able to log the user in programmatically. In order to give permission, we wrote a LINQPad script that does nothing but connect to the Client ID of our app, and allow the user to log in to give access.

Powerbi brendan 2

Running the script will pop up a dialog to allow a user to log into the App created with the specified client ID.

Powerbi brendan 3

In order to connect and display the report, we look to the Power BI documentation on how to show a report in an IFrame. We see that we need an embed URL and an access token. Since we need to send information to the IFrame after it’s already set, and because we want to be able to use different reports in different areas, we create an HTML web resource that’s got an Iframe in it, and set the frame contents accordingly using JavaScript.

Powerbi brendan 4

Excess code for styling and other libraries used in JavaScript removed for brevity.

The JavaScript in this page does a number of things. When the resource initially loads it parses the report ID and the group ID, in which the report is stored, from the query string. This lets us use the same web resource on the same page to load multiple reports. In the web resource properties on dynamics, we can set this report and group ID field accordingly in the custom parameters.

Powerbi brendan 5

It then triggers a custom action that takes in both of those parameters. The custom action triggers some plugin code that loads the configuration record, ensures the authentication is up to date, and then queries Power BI for the embed URL for that report.

For ensuring our authentication is up to date, we see if we have an access token or if our token has expired (based on the authentication lifespan and authentication obtained date time fields we have on our configuration). If we don’t have a refresh token, we need to use the password grant_type along with the service account username and password. (If we can refresh, we do something similar using grant_type refresh and the refresh token that we have stored in our configuration record. More details on Azure OAuth operations can be found here: In this example, we deserialize into an AccessToken model class that’s described by the documentation above.

Powerbi brendan 6

With our access token, we can query Power BI for the reports which are shared with the Group ID we sent added as a parameter by doing an HTTP GET request against with an Authorization: Bearer ACCESS_TOKEN header.

Powerbi brendan 7

This will give us a response that’s a JSON string which will be an array of all of the reports for the group. Each entry in the array will contain the report ID and the embed URL. There are additional fields, such as the display name for the report, but they’re unimportant for what we’re doing. We simply need to find the entry that has the report ID that we passed in, and return the embed URL and access token back to the web resource. Powerbi brendan 8

Once we have those fields in the client side, we can simply set the source of our Iframe to the embed URL we received, and post the access token to it.

Now we can see our Power BI report as an iFrame.  In this case we embedded as a Dashboard in Dynamics CRM.


Topics: Analytics Microsoft Dynamics 365 Microsoft Dynamics CRM

Import and Export Better than Art Vandelay

Today's blog post was written by Nick Costanzo (Principal Consultant) and Nathan Williams (Consultant) at Sonoma Partners.

If you've ever had to use the native import tool for Dynamics 365, you've more than likely had the experience of running into import errors of some sort. These errors are not always easy to resolve, and if you're importing large volumes of data, sorting through the errors can be very time consuming. Here at Sonoma Partners, we've had situations where client import files have 50k+ records and have resulted in thousands of errors on the initial import into a test environment. Dynamics 365 offers the ability to export the errored rows, but it doesn’t include the error codes. The export only includes the rows with the data you had already included in your import, which is not very helpful. Furthermore, you cannot access the error logs through Advanced Find.

Our team set out to find a better way to tackle this situation using Power BI.

Through our efforts, we came up with the following approach to better analyze these errors and resolve them more quickly. After all, we don’t want you to start yelling, “George is getting angry!” while dealing with import errors.

Here’s the approach we took:

1. First connect to CRM by choosing Get Data > OData Feed:

Nick c 1

2. Then choose the Import Logs and Import Files entities.

3. Next pull in the Web Service Error Codes published on MSDN, by choosing Get Data > Web:

Nick c 2

a. Note: Power BI will recognize the table of error codes on this page, but you will need to massage the data to get the Error IDs and text into their own columns:

Nick c 3

4. Now you can create your data model with relationships between these 3 tables:

Nick c 4

5. With your relationships in place, you can now create a report with visualizations to categorize your errors:

Nick c 5

  1. Create a slicer for the Import Date.
  2. Create a slicer for the File Name, in the event you have multiple files to import.
  3. Create a slicer for the Target Entity.
  4. Create a bar chart to count the errors per Field and Error Name.
  5. Create a bar chart to group the error by the field Value (i.e.  GUID from the source system).
  6. Create a table to display the record(s) based on which slicers have been selected.

6. The report now allows you to easily focus on which errors need to be fixed. In this case, we can see that 2 records were responsible for 1468 and 305 errors where the lookup could not be found. By fixing these 2 values, we’re much closer to a clean data set and can move on to the next ones.

7. Once you have resolved all errors in your source files, you can now reimport with a much higher level of confidence that the job will be successful.

If you wanted to take this a step further, you could set this up to analyze your data before importing to make sure it's clean. You would need to setup your lookup tables as data sources, and update the data model with those as well.  If you’d like help with these feel free to contact us, and our Power BI team would be glad to help!  Either way, you can certainly do more importing and exporting than Art Vandelay ever did!

Download our infographic on D365 for manufacturing

Topics: Analytics Microsoft Dynamics 365

Shifting to a PowerBI World

Today's blog post was written by Keith Mescha, a Principal Architect at Sonoma Partners.

Power B-What?

There's been a bit of buzz recently in the CRM circles around Power BI and how it fits into the overall CRM landscape. There is certainly no shortage of blogs, podcasts and event demos showcasing the power of this toolset. On a personal note, I'm am seeing the same level of interest based on the meetings on my calendar over the past few weeks.

What we're finding at Sonoma is that the topic of Power BI is still very confusing for most customers. In this multi-part series we hope to help demystify some of the confusion through real-life examples and suggest some best practices based on learnings from our internal use as well as implementations with our clients.

The Basics

Power BI is Microsoft's analytics and dashboard Business Intelligence (BI) suite of tools and solutions. It's made up of a few main offerings:

  • Power BI Desktop – Free development tool for building data models and dashboards
  • Power BI Service – Online PaaS offering to host and share Power BI solutions with a monthly licensing per user subscription based model
  • Power BI Embedded – Online PaaS offering for embedding Power BI solutions into applications for external consumption with a session based subscription model
  • Power BI Mobile – Free mobile app for consumption of deployed Power BI solutions on a mobile device
  • Power BI Enterprise Gateway – On Premise software used to extend on premise data to the Power BI Service

For latest and greatest features and release notes, refer to the Power BI site.

Real-Life Example

Every company I know has data quality issues or challenges to overcome. Unfortunately, we here at Sonoma are not exempt. One of the things we constantly struggle with is incomplete data and stale data. Often times, we will create a record without knowing all of the various data points we typically want to capture. If we do not go back and fill that information in once it becomes known, we are left with an incomplete and potentially inaccurate record. The genesis of the original report request was to provide a means for the data steward team to monitor newly created records.

The original ask was for a custom SSRS report that listed out all Accounts that were missing specific data across a pre-determined list of fields. This report would then be provided to our data steward team on a regular basis to ensure the data is augmented appropriately. When one of my colleagues stopped by to chat about the report, I happened to be in the middle of building a dashboard in Power BI for a customer. He was curious what I was working on, and as we talked and he explained his need, I fired up a new instance of Power BI desktop in an attempt to solve the problem.


Get Your Data

I pulled up the 'Filtered Account' view from the 'Get Data' option in Power BI. My User Account is a System Admin and the Filtered Account view resolves all the option set and lookup fields for us, so this was a quick and easy solution. When getting data for many of the source types, you have an option to Import or Direct connect. I’ll go deeper on that topic in my next post but for this solution I chose to import.


Shape Your Data

Initially Power BI pulls in all the attributes in the table, this can be a bit overwhelming so filtering down the attributes was our next step. My colleague had a list off the fields he wanted to audit and report, so I filtered down the data set to only include those attributes.


Within 15 minutes my colleague and I had a prototype dashboard built with the fields he needed, including a couple charts to use as filters. From there we iterated on a few designs, and ultimately landed on the below look and feel. End to end this entire process took a couple hours to build, including some custom columns to allow linking to our Account Forms on Dynamics and Salesforce.


Deploy Your Solution

Once we had a solid solution built in Power BI Desktop, I created a new O365 group for internal reporting, added a few users to the group and then deployed the Power BI solution to the Power BI service.

One key thing to note here is that we had to assign Power BI licenses to these users before they could login and see our dashboard. The fact that we wanted to schedule this a couple times per day and that we were managing this through our 0365 AD bumped us from the free to the paid license. We had a handful of users requiring access and plenty of licenses available so this was a decent option for us. If you have a different need there are other options, we can consider for staying on the free option.


Manage Your Solution

From there my colleague could interact with the report by logging into


After my colleague was happy with the presentation of the data and a few more cleanup steps, I was able to setup the data refresh of the data set in Power BI. We already had a Power BI Enterprise Gateway installed on our SQL Server for another solution, so including my data set in the refresh was very simple with just a few clicks.

Now that our solution was deployed, our data stewards have logged in and subscribed to the dashboards. Every morning they get an email with a snapshot of the dashboard and a link to access the dashboard where they get direct access to the records in Dynamics or Salesforce so they can easily update those with missing data.

In summary, the Power BI platform is a great set of tools that are easy to learn. There is quite a bit of information available through various internet communities, so getting started is easy. As shown here, from idea to solution was less than one day for a very simple deliverable. The toolset can extend into much larger solutions as needed, so please give us a call and let us help you take the next step in your analytics journey within your CRM applications.

In our next Power BI post, we will discuss in more detail the step of getting your data and how to decide on Data Import Vs Direct Query.

Topics: Analytics CRM Best Practices CRM for Professional Services Microsoft Dynamics 365 Salesforce