What are content packs? Content packs let users package up and share your own dashboard, reports, and datasets with your co-workers. It can be useful where a user needs to regularly distribute reports to team instead of sending email request repeatedly. Content packs are easy to find as they are all in one location, the content gallery. And since they’re part of Power BI, they leverage all the great features of Power BI including interactive data exploration, new visualizations, Q&A, integration with other data sources, data refresh, and more. Difference Between content packs and sharing dashboard Content packs are different from dashboard sharing which gives your co-workers read-only access to your dashboard and any associated reports. Steps for creating content packs Here we have two Pro Power BI user account. One with name as Kailas where we will be creating content pack and publishing to another Pro Power BI user account Ashish. Ashish can view the content pack and personalize/customize to create his own local copy. Steps for doing the same is as given below: Step 1: Log in to your Power BI account and on top right corner click on ‘Create Content Pack’ as shown below Step 2: Here you can specify email address to share your content pack with either specific members or group. Also select dashboard that needs to be shared via content pack. Automatically it will select related reports and dataset. Click on ‘Publish’ Step 3: On another Power BI user account click ‘Get Data’ and under ‘My Organization’ click ‘Get’ Step 4: Here you can see the name of the content pack shared. Here in this case it is ‘Content Pack Test’. Later click connect Step 5: You can see dashboard, reports and dataset shared here with * marked Step 6: Once you click on dataset, it will prompt you with the below message. Click save and it will personalize to create a local copy so that user can play around and save any work done. You can also check content packs created by clicking on ‘View Content Pack’ as shown below   For more details you can also refer the link given below: https://support.powerbi.com/knowledgebase/articles/651040-introduction-to-organizational-content-packs  

Prerequisite: Power BI Desktop Tool, Power BI Service, Microsoft Dynamics CRM Online Changing User’s in Power BI Desktop: Many a times we need to change user in Power BI Desktop for developing reports to get data from different perspective. As Power BI Desktop provides data source as CRM Online and also supports data refresh, it plays an important for Power BI users. So here is a trick to quickly change user in Power BI Desktop with CRM Online as data source. Find the steps given below: Step 1: Open your Desktop file and go to File → Options and settings → Data source settings. Step 2: Select the OData URL and right-click and then edit. Step 3: After that under credentials click ‘edit’ and then under ‘Organizational account’ hit ‘Sign in as different user’. You will be prompted to enter new user credentials. That’s it! Changing CRM Environment in Power BI Desktop: This can often be an important option which can be of use to Power BI users. Many a times, report needs to be created and tested first on Sandbox environment and later migrated to Production environment. Once reports are properly verified for look and feel and data manipulations, user can change the environment to some other desired environment. Following are the steps for achieving the same in Power BI Desktop: Step 1: Hit Edit Queries in ribbon Step 2: Click on Advanced Editor and on second line enter your OData URL (for required environment) Source = OData.Feed(https://_____________.crm5.dynamics.com/XRMServices/2011/OrganizationalData.svc) Then click done. This will fetch data from specified CRM Online environment.  

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