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