Consider migrating the data from Microsoft dynamics CRM online to Microsoft dynamics GP. Our goal is after the data migration is completed successfully, we want the result of the data migration (successful/failure) to go back to the source system. Steps: Scenario: 1st block: We query the source account entity 2nd block: We try to update/insert the target customer entity 3rd block: We update the source account entity, based on the result of the 2nd block (success/ failure) 4th block: We also update the target customer block based on the result of the 2nd block (success/ failure) In the target block (Update/Insert Customer), uncheck the error handling checkbox [Note: Error handling checkbox, is used to check if there are any fatal errors in the executions] After the data is inserted, we can have an update block for the source entity(Update account) that will write back the result (whether the data is successfully migrated or not) to the source system. In this example, GP2015_CustomerUpdateInsert is for inserting the data in the target system. As we can see in field’s column, we can capture information about the previous block by the highlighted fields, which can then be stored in the source system for tracking if the data went through.

Purpose of this blog is to refresh on-premise data sources available in Power BI. Prerequisite: Microsoft SQL Server Management Studio, Microsoft Excel 2013, Power BI Desktop Tool, Microsoft Data Management Gateway Steps for On-Premise Data in Power BI: Setting up On-Premise Data Refresh in Power BI will help refresh data directly from the data source to dataset. Here we will be looking on how to refresh on-premise data in Power BI using SQL data and Excel data. First we will be working on Excel data and then SQL data. I] Working on Excel Data: To begin with Excel Data we must have the following things on your PC Microsoft Excel 2013 Power BI Desktop Tool Microsoft Data Management Gateway Login to Power BI Service Create an Excel worksheet in Microsoft Excel 2013. And save it with some appropriate name.   Now open Power BI Desktop   Click on Get Data and select the type of file you need like in our case it is Excel and then click on   Select the Excel file from the appropriate location and click Open. You will get a Navigator window, select the sheet(s) you require and click on load   The data gets loaded.   Once loaded, you can view the sheet(s) and the columns in Field section on the Right hand side of the Power BI Desktop window. Now, you can create any chart or report in this and main is to save the file. This is saved with an extension of .pbix Now open Power BI Home page and create a login (If you have created your login already then use the same). Meanwhile it will ask you to install Microsoft Data Management Gateway. Install Microsoft Data Management Gateway on your machine.   Click on Get Data tab (at the left hand side bottom). Choose the saved .pbix file you saved using Power BI Desktop. Create the required chart(s) and report(s).   Here, chart based on Employee ID and salary is taken. Now the employee with name Bhavna (ID 1004) is 50 K. So, change it in the excel file, say, we change it to 20 K. Now save the changes done in Excel file and go to Power BI. Click on the dataset where you created the report using this Excel data and schedule the refresh. For scheduling the refresh click on the Dataset and click on SCHEDULE REFRESH you will get the below window check the gateway connections and the schedule time and click the Apply button at the bottom of that widow.   Now click on REFRESH NOW. The data gets automatically refreshed and you can see the changes in the report. As shown in above bar chart the salary that has been changed is reflected here. This is how refresh of on-premise in Power BI works using Excel data. II] Working on SQL Data: To begin with SQL Data we must have the following things on our PC Microsoft SQL Server Management Studio Power BI Desktop Microsoft Data Management Gateway You have to create a login to Power BI Create a Table in Microsoft SQL Server Management Studio. And save it with some appropriate name. For demo purpose I have created a table named Emp_Details with following rows and data. Now similarly Get Data from the SQL table in Power BI and create Dataset. Here the salary of employee name Aurick is 2 K Now we will update the SQL data using Update query and change it to 20 K Run the select query to check the changes. And save the change done. Now, go to Power BI and similarly SCHEDULE REFRESH (as done for Excel data) and then refresh the dataset which you have used to create the report. As you can see the Dataset is refreshed and the Salary of Employee named Aurick is changed to 20 K.   There are many more on-premise data sources that can be used to refresh data using Data Management Gateway in Power BI Supported on-premises data sources: Custom SQL/Native SQL Access Database File (CSV, XML, Text, Excel, Folder) IBM DB2 Database MySQL Database Oracle Database PostgreSQL Database SharePoint List SQL Server Database Sybase Database Teradata Database Supported online data sources: AppFigures (Beta) Azure Blob store Azure HD Insight Azure Marketplace Azure SQL Database Azure Table store Blank query (query that is not accessing any data source) Dynamics CRM Online Facebook GitHub (Beta) Google Analytics Hadoop File (HDFS) OData Feed Salesforce Sweet IQ (Beta) Twilio (Beta) QuickBooks Online (Beta) Web Zendesk (Beta)  

Note:  This article assumes experience on Scribe Insight for Data Integration/Migration and understanding of CRM Online Activities like Email, Letter, phone Call etc. have Attributes of type Party List that have to be set up using Activity Party entity as the source and destination. Steps for filtering Activity Party: Select Primary data object as Activity Party and Parent data object as ‘Activity Pointer’ The Required (Inner Join) should be checked. For details on Activity Pointer entity, kindly refer the below link: https://msdn.microsoft.com/en-us/library/gg327870.aspx Now in filters, we can add a condition based on Activity type Codes E.g.: 4210 for Phone Call Refer the below link for more information on Activity type Codes https://msdn.microsoft.com/en-in/library/bb887791.aspx In this way we can simultaneously run packages for Activity Parties for all the Activities by filtering them.

The CALCULATE function in DAX is the magic key for many calculations we can do in PowerPivot. Below is the syntax: CALCULATE( <expression>, <filter1>, <filter2>… ) The expression that we put in the first parameter has to be evaluated to return the result (i.e. a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on. This expression is evaluated in a context that is modified by the filters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following data model we have imported in PowerPivot named ‘Contract’ & ‘Project’ Scenario 1 Compare Contract & Project data model on YearMonth Column and take sum of multiple records of revenue column of Project data model into Contract data model Project data model has StartYM & StartRevenue Column as shown below And Contract data model has YM column, using Project data model StartYM, StartRevenue columns & Contract data model YM column, here we have derived StartR column with the help of Calculate() DAX function as shown below Formula is =calculate(sum(Project[StartRevenue]),filter(project,Project[StartYM]=Contract[YM])) Scenario 2 Calculate running total of ToDo column in ‘Contract-ToDo’ data model on basis of YearMonth column as shown below Formula is =calculate(sum(‘Contract-ToDo'[ToDo]),filter(‘Contract-ToDo’,’Contract-ToDo'[YearMonth]

Key Technologies: Power BI, PowerPivot, Business Intelligence Note: This article assumes experience on PowerPivot for Excel 2013/2010 Many a time data needs to be imported and processed from systems belonging to different locations and hence having different time zone. For example, consider the following scenario. A user residing in India having IST time zone might need to import data into PowerPivot model from CRM Online System having CEST as time zone required for Power BI Dashboards or for performing Data Analysis. There might be critical business data related to revenue or sales that matters the most. If date time is not properly converted or processed, end user might see incorrect output. When working with PowerPivot, after importing data into model, new calculated columns needs to be created before working on revenue reports or any business related critical data so that proper reports/results are displayed to end user. Here, we are converting to match data for CEST time zone. Similarly for other time zone, we can add/subtract UTC offset as shown below: Central European Summer Time (CEST). Offset UTC +2:00 hours Eastern Standard Time (EST). Offset UTC -5:00 hours Central Standard Time (CST). Offset UTC -6:00 hours Mountain Standard Time (MST). Offset UTC -7:00 hours Pacific Daylight Time (PDT). Offset UTC -7:00 hours

Key Technologies: Scribe Insight, CRM Online Note: This article assumes experience on Scribe Insight for Data Integration/Migration and understanding of CRM Online Sources: CRM, Salesforce TASK Activities like Tasks need to be migrated and linked to an entity (Account, Contact, Lead, Opportunity and so on). For storing the GUID from CRM, perform a seek step on the entity and then insert the task associated with it. Store the Guid of the Entity from seek step in a Variable (Leadid/Contactid/Accountid/Opportunityid) and then use it in the next step. Mappings required for Tasks from source to CRM NOTES Notes and Attachments are stored in Annotation entity in CRM Mappings required for Notes Attachment Mappings required for Attachments In Scribe, configure source and and select ‘annotation’ entity in the target(CRM). 1. Seek step will lookup ‘accountid’ and if it finds one, will continue to next step else the record will be skipped 2. Note/attachment will be inserted to the respective Id found. Similarly design for contacts, leads and opportunities by selecting the respective entity.

Key Technologies: Scribe Insight, CRM Online Note: This article assumes experience on Scribe Insight for Data Integration/Migration and understanding of CRM Online For all the Activities like Email, Letter ,Fax ,Phone Call etc. we need two packages to be set up a: Activity Entity to Activity Entity (Eg: Email to Email) [with Status= Open] b: Activity Party to Activity Party (For Party List fields of Email like bcc, cc, from, to) c: Status Update for the Activity Entity 1. For Activity Entity to Activity Entity (Eg: Email to Email) There are three field mappings which are mandatory to map ° RegardingObjecttypecode ° RegardingObjectId ° ActivityId (Guid of the Entity) RegardingObjecttypecode indicates to which entity is the record related Refer to following article for the Object Type Codes: https://msdn.microsoft.com/en-in/library/bb887791.aspx As per the RegardingObjectTypeCode we need to Setup the RegardingObjectId If(S70=1,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “account”, “tam_accountguid”, “accountid” ), If(S70=2,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “contact”, “tam_contactguid”, “contactid” ), If(S70=3,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “opportunity”, “tam_opportunityguid”, “opportunityid” ), If(S70=4,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “lead”, “tam_leadguid”, “leadid” ),#null!)))) Where S70=RegardingObjectTypeCode S67=RegardingObjectId Apart from these three fields the other required fields can be mapped [NOTE: The Status of the activities should be set to Open for the Initial Migration so that the activity party are set correctly. Once the Party list attributes are set up after running Activity Party for that Activity we can then update the activities for the Status again] 2. For Activity Party to Activity Party Perform a seek on Activity Entity (eg: Email) [Note: We should lookup on activityid as well as activitytypecode so that all the activity entities are not processed and filtering is done] ActivitytypeCode indicates which Activity Entity is it related to Eg: Appointment=4201 Email=4202 Store the Guid of the Activity Entity in a variable for Step 2 Mapping of the following fields is required: ° ActivityTypeCode needs to be hardcoded   [Eg: If we are migrating Activity Party for Email it should be 4202, For Letter 4207 etc.] ° ActivityId to Variable created in Step 1 ° ActivityPartyid to ActivityPartyid ° PartyObjectTypeCode to PartyObjectTypeCode PartyObjectTypeCode indicates to which entity is the record related. As per the PartyObjectTypeCode we need to Setup the PartyId as: If(S26=1,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “account”, “tam_accountguid”, “accountid” ), If(S26=2,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “contact”, “tam_contactguid”, “contactid” ), If(S26=3,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “opportunity”, “tam_opportunityguid”, “opportunityid” ), If(S26=4,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “lead”, “tam_leadguid”, “leadid” ), If(S26=8,DBLOOKUP2(FILELOOKUP(S25, “XREFUsers.INI”, “Users” ),”0″, “Adapter for Dynamics CRM”, “systemuser”, “fullname”,”isdisabled”,”systemuserid” ),#null!))))) Where S26=PartyObjectTypeCode S24=PartyId S25=PartyIdName ° ParticipationMaskName to ActivityFieldName So the Mapping can be mentioned as: Where S23= ParticipationMaskName IF(S23=”Optional attendee”,”optionalattendees”,(IF(S23=”Organizer”,”organizer”,(IF(S23=”Required attendee”,”requiredattendees”,””))))) Where S23=ParticipationMaskName Refer to following article for the Activity Party Entity: https://msdn.microsoft.com/en-us/library/gg328549.aspx List of ParticipationTypeMask and ParticipationTypeMaskName: 3. Status Update for the Activity entity We have to lookup on the ActivityId Update the Status and Status Reason of the Activity as specified in the source

Key Technologies: Microsoft Business Intelligence, Power BI Note: This article assumes experience on Power BI for Office 365 using Excel 2013 and the Microsoft BI stack Power BI transforms data into rich visualizations independent of Office 365 and SharePoint Online. Newly available features: Powerful Dashboards using HTML5 without the need to install Silverlight New Visualizations =:Combo Charts =:Filled maps =:Radial gauges Support for popular SaaS Microsoft Dynamics CRM Online Salesforce Zendesk Marketo SendGrid GitHub Connectivity to on premise SQL Server Analysis services tabular models Available for iPhone, iPad & Windows tablet Q&A using Natural Language Processing Power BI Designer Tool Power BI Designer puts visual analytics at your fingertips with intuitive report authoring Drag-and-drop to place content exactly where you want it on the flexible and fluid Designer canvas Quickly discover patterns as you explore a single unified view of linked, interactive visualizations Includes data import using “Get Data”, Data modelling using “Manage” and creating reports using “Report” With “Get Data” option we can import data from different sources such as databases, web pages, data files like text, csv, xml, etc. Click on “Manage” to create relationships between different tables Create reports using different visualizations available Save the file and upload it to www.powerbi.com