Prerequisite: Power BI Desktop Tool, Power BI Online Service, SSMS and SSDT Connecting Power BI Desktop Tool with Azure SQL Data Warehouse: With the new Azure SQL Data Warehouse Preview released in June 2015, we can connect to Power BI Online or Desktop tool and create stunning reports/dashboards to visualize and analyze your data. Before connecting with Power BI, we will first move few records in Azure SQL Data Warehouse from SQL Server in Azure VM using SSIS as shown below: Now we can connect to Azure SQL Data Warehouse from SQL Server and query table to view records inserted as shown below: Once data is loaded in Azure SQL Data Warehouse, we can now start creating reports and later publish it to Power BI Online. Find the steps given below to connect to Power BI Desktop tool:   Step 1: Open Desktop tool and click ‘Get Data’ from ribbon. Select ‘Microsoft Azure SQL Data Warehouse’ and click ‘Connect’   Step 2: Enter Server Name, Database and later credentials to connect to your Azure SQL Data Warehouse   Step 3: Once connected, you can select required tables. In our case it is ‘Projects’ and click load   Step 4: Design report and later save & publish it to Power BI Online   Step 5: You can pin visualizations to dashboard and also schedule refresh without the need for Power BI Personal Gateway   Direct Connectivity to Power BI Online from Azure SQL Data Warehouse: We can also directly load tables in Power BI Dataset using option ‘Open in Power BI’ available in Microsoft Azure as shown below:   Once you hit ‘Open in Power BI’, you will be taken to Power BI Online and details like server name and database name will be already provided by default. Later you need to just enter password for database and then you are good to go.   You can create reports from the imported dataset and pin visuals to dashboard similarly as in case of reports published from Power BI Desktop tool. Find the screen capture as shown below:   Since dataset is directly imported in Power BI connecting to Azure SQL Data Warehouse, the dataset is automatically refreshed at regular interval without the need to schedule refresh. Find image shown below:  

Prerequisite: MySQL, Power BI Desktop Tool, Power BI Personal Gateway Purpose of the setup: MySQL is one of the popular open source database used right after Microsoft SQL and Oracle. Power BI being one of the most powerful tool used for reporting and data visualization with different data sources supported. Here we will be looking on how to connect and refresh on-premise MySQL database to Power BI. Steps for establishing connectivity between MySQL and Power BI: Here we have one database created in MySQL named ‘emp_details’ which has table-‘emp_salary’ with 4 records. Now, we will pull this MySQL data to Power BI and create a .pbix file For that first we will open Power BI Desktop tool and click on Get Data Here we will select ‘Database’ option on the right hand side and then select the ‘MySQL Database’ and click on ‘Connect’ Then it will ask for server name and database. Enter the appropriate server name and database (which you want). And then click OK. Now, it will ask for the username and Password. Then click on Connect Now the list of all database and its related table will appear. You select your required table and click on Load. Now, create a report in Power BI Desktop tool and save it with some appropriate name. Then, login to Power BI online and using GetData option pull the .pbix file you have created. You will find the .pbix file under Dataset on left hand side of the screen. Now, create a simple report and pin it to dashboard. Then schedule Refresh and click to Apply. Now when we select the Dataset and click on Refresh Now , will get Now, to check the Refreshing of data add a new data or update the Existing one in your MySQL table. Now, again go to Power BI Online and click on the Dataset and select Refresh Now . You will find the changes reflected on your dashboard.   You can read more about scheduling on-premise data sources in our previous blog given below: https://www.cloudfronts.com/on-premise-data-refresh-in-power-bi/

To migrate Scribe integration setup from one AOS to another in AX 2012, below mentioned steps has to be performed, On the AOS server, in IIS go to the Default Web Site node. Under the node check if there is MicrosoftDynamicsAXAif60 web site. If not, go to the below link to install Microsoft Dynamics AX web services on IIS. [https://technet.microsoft.com/en-us/library/gg731848.aspx] In AX, System Administration module→Setup→Services and Application Integration Framework open the Web sites form. Create a new record for the above web site. Enter appropriate name, description and correct virtual directory path. Next go to System Administration module→Setup→Services and Application Integration Framework→Inbound ports. Follow the below steps to modify the inbound port, Select the inbound port for scribe integration. Deactivate the port. Note : Before you do this, make sure no user is online(System Administration module→Common→Users→Online users) and no messages are being processed(No xml in ScribeIn queue). On right side under the ‘Address’ group, click the drop down next to URI textbox. Select the new web site from ‘Select Web site’ form and click OK. Check if the port has all its service operations. If not, you can add them. Activate the port. This will generate new wsdl url. Copy-paste this wsdl url in a text document, we will use it later. Now, login to the Scribe server. In the Collaborations folder open any dts file in scribe workbench. In the dts file, open the Connection Manager and click ‘Edit’. Click Change Connection. Select ‘Web Services’ under Adapters. Click OK. In the Web Service Connection Manager form, select the AX web service and click ‘Edit’. Change the WSDL url. Put the url from step-3. Validate the url and Save. Perform steps-4 to step-8 for all other dts files. By doing the above steps, Inbound port url, in AX, will be changed and AX web service connections in Scribe will point to the changed url.  

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