Power BI Desktop – Connecting to Data (part 1/3)

In the following article I want to show you how easy it is to use Power BI Desktop and how quickly you can create your first visualizations.

Get Data

Open Power BI Desktop and click Get Data function.

So, we need data, but we don’t have any! Why don’t we get some data from the Web?

To do this we can simply click on the Search bar any type Web. In the search result, select Web and click Connect.

Now we need to specify the URL. In this example, you can search for “IMF World Economic Outlook Database” on the web to get the GDP data. Please note that you might get a different result depending on the time you are looking for it, right now October 2017 version is the latest one that we will use in this example.

Click on the search result.

We would like to get data By Countries (country level data).

We would like to get the data for All Countries.

Click Continue.

Select Gross domestic product, current prices in U.S dollars and click Continue.

Click Prepare Report.

The next page will show you a table with result, we can download the result but in this case we would like to use the URL of this page.

You can copy the URL of this page (link) and paste it in the Power BI URL request field and click Ok.

This opens the Navigator, that allows us to select the information that we want to retrieve from the selected Web Page.

Select Table 1 in this example. To view the correct result, you can toggle between the Table View and Web View.

If you click Load, it will immediately load the data into the data model. In case you click Edit, then you will first open the Query Editor. The query editor is a separate tool in Power BI desktop that allows us to prepare data and then load the clean data into the data model.

Click Edit. Here you can see the query editor with the project data.

In the following topics, check out what we can do with this data:

Power BI Desktop – Working in Query Editor (part 2/3)

Power BI Desktop – Working in Data Model and Creating Visualization (part 3/3)

SSRS Reports for Dynamics 365 Using FetchXML

To be able to create SSRS reports for Dynamics 365 using Visual Studio, you need to perform the following actions:

Pre-Requisites

  • Install Visual Studio 2015. For more information click here.
  • Install Microsoft .NET Framework 3.5 Service Pack 1. Click here to download and install.
  • Microsoft Dynamics 365
    Report Authoring Extension is required to author Fetch-based reports used with Microsoft Dynamics 365 by using SQL Server Data Tools. Click here to download and install.
  • Microsoft SQL Server Reporting Services is required for reporting features in Microsoft Dynamics 365. Click here to download and install.

Once you have completed these steps, you are ready to start creating SSRS reports for Dynamics 365. The following sections explains how you can create SSRS reports using FetchXML from Dynamics 365.

Download Fetch XML from Dynamics 365:

  • In Dynamics 365, click Advance Find.
  • Build a query, for example, to populate all active contact details. You don’t need to save it.
  • Click Download Fetch XML. This file will be used in the report.

  • Open the file in notepad to check the query.

NOTE: We will use this query later in Visual Studio Query Builder.

SSRS Reports Using Fetch XML in Dynamics 365:

  • Open Visual Studio and start a New Project.
  • Click Business Intelligence > Reporting Services > Report Server Project Wizard.
  • Specify project Name and Solution Name.
  • Click Ok.

  • This will open the Reporting Wizard.

  • Click Next to Select the Data Source:
    • Select New Data Source.
    • Specify the Name.
    • Select the Type as Microsoft Dynamics 365 Fetch.
    • Specify the Connection String (this is the URL you use to connect to Dynamics 365)
    • Click Credentials and Use a Specific User Name and Password.
    • Click Ok.

    NOTE: If multiple CRM Organizations are available, there may be dialog box which may ask you to Login or select the right organization. Provide the credentials and Login to relevant organization.

  • Copy and Paste the query from Fetch XML to the Query Builder in Visual Studio and click Run. The system will fetch the result from Dynamics 365.

  • Click OK. The system will display the Query String that you just ran.

  • Click Next. Select the Report Type (Tabular or Matrix).

  • Design the report and click Next.

  • While Completing the Wizard, specify Report Name and select Preview Report and click Finish.

  • The system will show you a preview of how your report will look like in Dynamics 365.

Build the RDL file:

  • In the Solution Explorer, right-click on the Project and click Properties.

  • In the Target Server Version field, select SQL Server 2008 R2, 2012 or 2014. Click Apply. (This is Important to avoid errors!).

  • In the Solution Explorer, right-click on the Project and click Build or Rebuild.

  • In the Solution Explorer, right-click on the Solution and click Open Folder in File Explorer.

  • You will find the RDL file placed in the ..\Client Details Report\bin\Debug folder.

Import the RDL file in Dynamics 365:

  • In Dynamics 365, open the Reports section and click New.

  • In the New Reports form
    • Select Report Type as Existing File
    • Click Choose File to select the RDL file
    • Specify Name
    • Select Display In as Report area

  • Click Save and Close, this will import the report in the Available Report List, from where you can now run the report.

Have Fun!

Connect Power BI Desktop to Dynamics 365

Do you want to connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis, produce beautiful reports, then publish them for your organization to consume on the web and across mobile devices. Well, then you can use the Power BI suite of business analytics tools that deliver insights throughout your organization. You can create personalized dashboards with a unique, 360-degree view of your business, and scale across the enterprise, with governance and security built-in. For more resources on Power BI, click here.

The following article explains how you can connect Power BI Desktop with Dynamics 365.

Copy URL from Dynamics 365

  1. Log in to Dynamics 365.
  2. Click Settings > Customizations > Developer Tools.
  3. Copy the URL under Instance Web API (save it for further use).

Connect Power BI to Dynamics 365

  1. Open PowerBI Desktop and click Get Data > Dynamic 365 (online) and click Connect.
  2. Enter the Web API URL and click OK.
  3. On the OData Feed, click Organizational Account.
  4. Click Sign in to enter your credentials. When you see the message “You are currently sign in.”, you can click Connect.
  5. On the Navigator, select the entities to load into PowerBI and click Load.
  6. The data and entity fields are loaded into PowerBI Desktop. You can now start building reports.

Dynamics 365 – Sales Important Terminology

Want to get started with Dynamics 365 for Sales? Following are basic terminologies that you should be aware of:

Accounts:

  • Accounts is an organization which includes:
    • Customer
    • Vendor
    • Partner
    • Affiliate or Other
  • So, when you are doing business with any company, you capture those company details in the Accounts entity.

NOTE: An Entity is a place where you can store records.

Contacts

  • Contact is an individual.
  • Contact is also your customer, but an individual person.
  • Contact can be associated maximum with one Account, which is called its Primary Contact.
  • For company, you are doing business with, you will need a person whom you are going to interact with. That becomes your primary contact.
  • Therefore, Contacts and Accounts are your customers, where Accounts are organizations and Contacts are individuals.

Leads

  • A Lead is a Prospect and it can be your Potential Customer or Potential Sale. This means that there is a person who has never done a business with you and you have met for the first time and there is a possibility of doing business. On other hand there can be an existing customer who want to work with you on a new Opportunity.
  • Whenever you meet someone, they give you their business card or they share some information about themselves and that becomes your lead.
  • You capture your lead information in Dynamics 365 Sales module.
  • Every sales process starts with a Lead generation or Leads. You interact with them and you start following up with them.

Opportunities

  • Opportunity is a potential sale, which means that the lead is almost ready to buy.
  • You can create an opportunity manually and not necessarily convert a Lead to and Opportunity.
  • Opportunity is attached to an Account or a Contact, because it is essential to know whom are you going to sell. There you identify the customer needs, what are the pain points they have, what exactly is the solution they are looking for and what proposal you have suggested to them.

Quotes

  • When your Lead becomes ready to buy, that becomes an Opportunity and when the prospect is entrusted in your product & Services they may ask you for a Quote.
  • Quote is a document for Prospect & Customer with all the details, such as the product they want to purchase, the quantity that they are looking at, the price you are offering to them, payment terms you have defined and some other important details.
  • Once the quote is ready, you can send it to the prospect or the customer, based on which they can take a decision.

Orders

  • Orders is a confirmation from a customer that they are purchasing your product or services.
  • It can be created from a Quote, which means that you do not have to manually enter it again and all the information from the Quote will be transferred to the Order.
  • Orders can also be created manually, so it is not necessary that you follow the entire process. So in case the customer has already given an order, you can create it directly in Dynamics 365 for Sales.

Invoices

  • It is a document that you use to bill your customer for using your product and services.
  • It is sent to the Customer once you have fulfilled the order. Sending an invoice ensures that the customer is going to pay you for the product and services that they have used.
  • It can also be created manually so that you do not have to follow the entire process, for example in case you have a repeated customer who uses your product and services regularly.

Competitors

  • It is a place where you capture all your competitors
  • When you are bidding for any project and if some other company is also bidding for the same project then you should know what they are good or bad at, what are their strengths and weaknesses, what is their product offering, what kind of quality they offer, etc. So you can record all kind of information about your competitors and keep track of them by attaching it in your sales process.

Sales Literature

  • It is a centralized repository for sales related documents such as Brochures, Product guides, Competitor Information, Pricing & Discounts, etc.

Product Catalogue

  • A product catalogue is a collection of products and their pricing information.
  • When you have product and services, you can created it under Product,
  • You can define a Unit Group, which means how do you sell and purchase a particular product, for example PCs can be sold in numbers and for training you can charge your customers on hourly basis.
  • You can define the Price List based on the Segmentation of your Customers. So you can define multiple price lists.
  • You can define Discount Lists, for example if a customer buys from 0-10, he may be eligible for 1 0 % discount, if he buys from 11-20, he may be eligible for 20 % discount, etc.

Goals

  • Goals are used to keep track of progress on achieving target revenue that you have planned for a particular period (Quarter, Year, etc.)

Goal Metrics

  • Goal metrics explains how the goal number or figure is measured.
  • It is important to define what the goal metrics is, i.e., is it a Count or an Amount. For Sales, it is going to be Amount and say if you want to see how many calls a salesperson has made in a day then that becomes your Count.
  • Actual revenue is calculated through rollup fields, so you can define those rollup fields for calculations for example, whether it is going to be sum of all opportunities that have status 1 or sum of all the Opportunities which are closed.

Now you can dive deeper into the Dynamics 365 Sales process. Cheers!

What is Dynamics 365?

If you have just started with Dynamics 365, then I think the following article will be a good point to begin with. There are too many links out there and too much of information floating around and I needed a point to begin with. Here is a summary of overall idea behind Dynamics 365, which I could gather and I hope it helps you too.

  • It is a cloud based business application
    • In other words, it is a collection of applications that is called Microsoft Dynamics 365.
    • It means you do not have to install anything on your servers or on your premise, i.e. you do not need to invest in all those costly infrastructures. All you need is an internet connection and a browser to access the applications and start managing your business.
    • So, it is a complete cloud based Business Application.
  • It is flexible, extensible and mobile
    • Dynamics 365 is flexible because you do not have to take the entire suite of Dynamics 365 to run your business. In case you just need to start with one business function then you can take that function in Dynamics 365 and start using it. You can add new business functions to your deployment as and when needed.
    • Dynamics 365 comes with a lot of out of the box functionalities which you can use instantly for your business, however, it is extensible as well, which means if you have any specific requirements which are not available in Dynamics 365, then the system is capable to extend its functionalities, i.e. you can build those functionalities in the system and you can even integrate it with other systems to make sure you have an entire solution.
    • Dynamics 365 is mobile, which means you do not have to depend upon your Laptop, Desktop or PCs, but you can also access it via Tablets and Smart Phones. So, you can access Dynamics 365 and manage your business as and when required.
  • It is a combination of ERP and CRM functionalities
    • It means that everything you need to manage your Enterprise Resources, it is available in Dynamics 365 and anything that is required to manage your Customers is available in Dynamics 365 as well.
    • ERP – Financials & Operations
      • In ERP, we have Financials and Operations. Financials is used for small and medium sized companies where they need a quick solution to get started and manage their entire business in cloud.
      • In case your company is big or a large organization then you need Operations, which provides entire solution for your business.
    • CRM – Sales, Marketing, Service, Field Service & Project Service
      • Customer Relationship Management provides you with Sales, Marketing, Service, Field Service and Project Service automation.
  • It works best with analysis tools such as Power BI and Cortana for intelligent analysis
    • Every company needs dashboard and intelligence to their business so that they can make quick and informed decisions.
    • So, Dynamics 365 integrated with Power BI and Cortana well so that it gives intelligence to your business.
  • It works best for Small, Medium and Large Enterprises
    • This essentially means any company can use Dynamics 365.

So, in a nutshell this is what Dynamics 365 is all about. Now you can go ahead and get into the nitty gritty of its features. It looks cool by the way J

Cheers!

Task Management

 

To-Do’s

When you click To-dos, you will get overview of all the To-dos in the system.

Every To-do will have a Status and based on the status you can look, filter and sort if you want to pay attention to those that are not completed.

Mostly the To-dos are used in the context of opportunities, where you can assign To-dos and Activities to opportunities. However, you can also create to-dos which are not related to opportunities.

So when you open such a To-do, you will see very typical information such as It has a Number and a Description; It is assigned to a salesperson; It is a to-do for specific contact; You see the Status, the Priority, the Starting Date and the Ending Date.

Under Related Activities fast tab, you can really track or find the origin of this to-do, for example in this case the to-do is related to an opportunity. To-do can also be related to campaign.

You can also make a to-do recurring as well.

You can also define the Type of to-do, which can be Blank, Meeting and Phone Call. You can synchronize this between Outlook and Relationship Management.

A Phone Call type to-do is typically an activity which is caused by phone call which can have a link with an interaction.

Send E-Mail Meeting Invitations

SCENARIO:

You need to create a to-do of the meeting type with Annette Hill as the to-do organizer and with an Beebe and Yvonne McKay as the meeting attendees. Moreover, you need to send the invitation to Yvonne McKay.

PROCESS:

Edit all the relevant contacts and check if the communication details are available.


Open Sales people (Annette Hill) and create To-do.


Use the wizard to create the To-Do.


Make Annette Hill the To-Do Organizer and make Richard Lum and Yvonne McKay as attendees.

Specify relevant template for e-mail invitation.

Finish the wizard.


Select the To-Do just created and Edit the To-do.


Navigate to Attendee Scheduling.


 

Edit Attachment as required and save it.



Make sure the Send Invitation field is selected and click on the Send Invitation action.


This will send invitation to all the recipients.

Delete Cancelled To-Dos

You can use Delete Cancelled To-dos batch job to clean up all the cancelled To-dos from the database. You can make use of various filters while running the batch job.

Campaign Management

A campaign is any sort of activity that involves a number of contacts. It can be anything from sending out catalogue and special offers to organizing a special event.

You can record contact responses to campaigns as interactions, which means that a response to a campaign becomes an interaction in Dynamics NAV, and that interaction linked with the campaign.

Setting up Campaigns

Campaign Status Codes

Click Departments/Sales & Marketing/Marketing/Status under Campaign.

Here you can define campaign status codes or use the preconfigured ones as shown in the following example.

The purpose of these campaign status codes is to track the status of your campaign. Once you have configured these campaign statuses, you can start working with the Campaigns.

Click Departments/Sales & Marketing/Marketing/Campaigns to open campaigns.

So, if you Edit a campaign, for example Event campaign in this case, you will see that:

  • It has a number
  • It has a description
  • It has a status code, which you can configure.
  • It has a starting date and an ending date
  • It has a link to the salesperson
  • It has a status to show if the campaign is activated or not
  • You can find invoice details, if you have links with sales orders and so on

From the campaign, you can click on Navigate and click on Opportunities to see if there are any opportunities linked to the campaign.

In the same way you can also track the Segments, a subset of contacts that you have linked to the campaign.

Similarly, you can click on Entries, to see is there are any Interaction entries logged for the specific campaign.

If you want to do more than tracking responses for instance, you can also configure specific Sales Prices for a campaign. For instance, for a specific item or for a number of items, you can specify a minimum quantity and specific unit price during a specific starting and ending date.

Now, these will only have impacts on your quotes and orders, if you have the Sales process or Discounts. And the moment that you activate these sales prices and discounts, that is also the moment that you campaign will be activated.

Creating Interactions for Campaigns

After you have created a campaign, and maybe you have already initialized some actions like e-mail campaign which was sent out to a segment of contacts, you might expect, for instance, some reactions to that campaign.

These reactions can be recorded as interaction, and that is simply done by providing a campaign code in the interaction window.

The interaction will then be tagged by a campaign code and you can later view the recorded interaction in the campaign Entries window.

So, for example, let us assume that you activated and released a new spring campaign with new prices and so on. You send out an e-mail and Beef House is registered. You receive a phone call from Beef House showing interest.

So you select the contact Beef House and click Create Interact.

Follow the create interaction wizard to create the interaction. While following the wizard, choose the Campaign details. You can also assign opportunities to this campaign.

When all the details are specified, Finish the wizard.

Now, select the campaign, Spring offer in this example, and Navigate to Entries. You can see the campaign entries. To look more about the campaign entries, you can select the entry and click on Interaction Log Entry for more details.

This way, we can basically make interactions from reactions and then they are linked to the campaigns.