Purpose of this blog is to showcase latest Power BI updates. Prerequisite: Power BI Desktop Tool, Power BI Personal Gateway and Power BI online service account. Purpose of the setup: To learn about latest Power BI updates from October month. Here we will be looking the below mentioned Power BI updates; Read-only Members in Power BI Groups Semi-select support for DAX formulas in Data view   Read-only Members in Power BI Groups: Power BI have introduced a very good feature in Power BI online Service using which the user can create group in Power BI Online account with multiple users and these users can be assigned role as ‘Admin’ or ‘Member’.Let’s have a look. With such Power BI groups the user and his colleagues can come together to collaborate, communicate and connect with their data. The user can create a group either in Power BI or Office 365. Then, the user can invite co-workers into this group workspace where he can collaborate on his organization’s shared dashboards, reports and datasets. Up until now, all members had all the rights. Many of us required read-only membership to the groups so that the users you want to keep up to date but not have edit permissions on our dashboards and reports. So, this update exactly helps us to do the same; let’s have a look on how to do this. Now to begin with, first we’ll create our group by clicking the “+” icon near Group Workspaces. Now let’s add a few users to our group. FYI, Power BI Groups have two roles: Admins and Members. Now here, we will set one user to Admin and the remaining users to Members. We will click on Save to create the group. Now, let’s take a look what difference take place when an Admin and a member login to Power BI Online account and use the created group. As you can see, when person with Admin rights, login, than that user has right to ‘Explore’, ‘Schedule Refresh’, ‘Rename’, ’Delete’ etc. options available. Whereas, when a person with member rights login can only view the reports and Dashboard. Member of the group do not have the Dataset access and thus, cannot perform any edit operation. Thus, now we have option introduced for us to quickly switch a user from Member to Admin and vice-versa which will be very helpful.   Semiselect support for DAX formula in Dataview : Power BI have introduced another very good feature in Power BI Desktop Tool using which the user can create new column using DAX function by directly clicking on the column. Let’s have a look First we have to open the Dataview Screen. Now, before this update user had to manually type the table and the column name to write the DAX function. With this update the user can directly click on the column name and create the DAX function as per the requirement.  

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]