Prerequisite: Power BI Desktop tool, SQL Server Database Purpose of the setup: Purpose of this blog is to demonstrate connecting SQL as a data source to Power BI Desktop Tool using Direct Query Mode. Procedure: To get this done we have created a Power BI Desktop Tool file with source data of SQL file connected using Direct Query mode. (a)   (b) Fig 1: Power BI Desktop Tool file using Direct Query Mode As we know, previously there were many restriction in using Direct Query Mode like we could not view the relationship, cannot create calculated columns and measures etc. But now, there are many updates in Direct Query mode few of which will be demonstrated below. Ability to create calculated columns in Direct Query. Ability to create measures in Direct Query. Ability to change the datatype when connected to Direct Query. Ability to assume Referential Integrity on relationships in Direct Query Ability to create calculated columns in Direct Query Now we can create calculated column in Direct Query using DAX function. Right click on the table area and select calculated column then write the required DAX query to get the column. Below is an example where we have created a calculated column in Direct Query. Fig 2: Create New Column option in Direct Query Mode   Fig 3: Created calculated column in Direct Query Mode   Ability to create measures in Direct Query Now we can create measures in Direct Query using DAX function. Right click on the table area and select measures then write the required DAX query to get the column. Below is an example where we have created a measure in Direct Query. Fig 4: Create new Measure option in Direct Query Mode   Fig 5: Created Measured in Direct Query Mode   Ability to change the datatype when connected to Direct Query Now we can also change the datatype in Direct Query. Fig 6: Change Datatype option in Direct Query Mode   Ability to assume Referential Integrity on relationships in Direct Query A new setting has been added to the Edit Relationship dialog: a checkbox to “Assume Referential Integrity”. This setting is applied to imported and created data model relationships. To access the setting select the Home tab -> Manage Relationships, select a relationship, and click Edit. Here we have added few tables to our Direct Query file to create some relationship between the tables and selected one relationship to get the Referential Integrity option. Fig 7: Assume Referential Integrity in Direct Query Mode   This is an advanced setting, and is only enabled when connecting to the data in Direct Query mode. It will enable more efficient queries to be generated when it is known that: The From column of the relationship is never Null/blank For every value of the From column, there is a corresponding value in the To column By setting the property in these cases, it allows more efficient queries to be sent to the backend database using INNER JOINs instead of OUTER JOINs. This will result in faster loading of dashboards for the end user.

Prerequisite: Power BI Desktop Tool, Power BI Online, CRM Online and HTML Purpose of the Setup: Purpose of this setup is to embed interactive Power BI reports in CRM Online Dashboard so that it can be viewed by end users directly within CRM Online instead of login into Power BI account. Find the steps given below for publishing Power BI reports to CRM Online. Step 1: Create a new Web Resource in CRM Online as shown below   Step 2: Give this Web Resource a name and select the HTML file which contains the necessary iframe code to access Power BI reports. Save and publish Web Resource created. You can find more about steps for creating an HTML webpage for accessing Power BI reports here.   Step 3: Once you create Web Resource, you can find the same in the list of Web Resources as shown in the image below   Step 4: Now navigate to Dashboard. Create a new Dashboard and give this Dashboard a name.  Select Web Resource to be inserted in Dashboard as shown below Step 5: After that select the Web Resource created and uncheck restrict cross frame scripting option and hit OK   Step 6: Once you save and publish Dashboard, you can see Power BI reports as shown below directly from your CRM Online  

Prerequisite: Power BI Online report and a sample HTML webpage Purpose of the Setup: Purpose of this setup is to embed interactive Power BI visualizations in your blog or website and share the stunning visuals through your emails or social media communications and reach millions of users on any device, any place, for an engaging experience. Find the steps given below for publishing Power BI reports into your webpage or blog. Step 1: Create a dummy webpage with some content, as shown below so that we can go to iframe page.   Step 2: Now, let’s move on to the Power BI Online Service account part. Firstly, open your Power BI Online Account and select the report to be published. Once you select the report, you click on File menu there you will find a new option as Publish to Web. Select the option to proceed further.   Step 3: Now, it will show option to Create embed code, click on that and then it will display a warning with Publish option. Click on Publish.     Step 4: Then you will get a Success message! The message window will show 2 options i.e. Link you can send in email and Html you can paste into your blog or website.   Thus, Power BI has automatically created the link and embed code which can be used to get the dashboard in our webpage. We get different pixel size option also. You can use the link to share with others or you can paste the html code generated here into your blog or website. Step 5: Now, we can paste the embed code into the webpage html code. We have given sample code below: <! DOCTYPE html> <html> <head> </head> <body> <iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiZjAzYTNjYzQtNjI1ZC00NTA2LWEyYjQtZjM3YWQ2OGNlYzBiIiwidCI6IjI2YzRiMmU0LWVjMDctNGM3Yi05MmU1LTk3ZjUyODY1ZTk4YiIsImMiOjEwfQ%3D%3D” frameborder=”0″></iframe> </body> </html> Step 6: Now, with this code, when we click on the Power BI Demo button “Demo Registration” on the webpage created initially we will get the report directly on the our webpage.   This is the final output that we get. Thus, we have published Power BI report to our webpage. Now, we can view the code and link again through setting icon and clicking on Manage embed codes. We can also delete the report published. To view the code: Setting -> Manage embed codes -> Right click on ellipses against the report -> Get Code   </>Get Code will again display the link and embed code.   And Delete option will confirm the deleting action and the delete the embed code for the particular report.  

Prerequisite: Microsoft Excel, Power BI Online Service, Power BI Publisher for Excel Power BI Publisher for Excel: With the help of introduction to Power BI publisher for Excel, we can now easily share key Excel insights directly to Power BI online. Using this feature PivotTables, charts, cell range from Excel sheet can be easily shared across different location to a single location in Power BI Dashboard. Note: Power BI publisher for Excel is still in preview mode. To download the Power BI publisher for Excel, click here. Once downloaded and installed, following screen will be shown when you open Excel:   You can then click on Sign up and start using the publisher as shown in screenshot below:   Login to Power BI using your account   You can find the publisher installed on top ribbon under Power BI tab as shown below:   Steps for sharing Excel data to Power BI online dashboards: Step 1: Select desired data from the Excel spreadsheet and click the Pin button on the Power BI ribbon above.   Step 2: When you clicked on Pin, you will be prompted whether to pin to an existing dashboard or create a new dashboard and then pin. You can also view the preview at the right side to see how this will look.   Step 3: Once you log in to your Power BI Online account, you will be able to see the pinned Excel data in Power BI Dashboard.   Step 4: You can also manage tiles that have been already pinned using Pin Manager on top of the ribbon as shown below.   Using Pin Manager, you can update tile data for which you have already pinned to dashboard or also you can remove the tile pinned on Power BI dashboard.  

Prerequisite: Power BI Desktop Tool and knowledge on DAX functions Purpose of the Setup: To learn how we can leverage DAX functions to create complex Power BI reports. Here we will be looking at different DAX functions for the below mentioned reports in Power BI; Calculate and compare Sales Figure by Day Calculate and compare Sales Figure by Date Calculate and compare Sales Running Total Source File Details: To demonstrate this concept, we have created an excel file with dummy data and connected it to Power BI Desktop Tool. In the Excel source file, we have created few sheets along with sample data as shown below: Fig: Modified Sales

Prerequisite: Power BI Desktop Tool, PostgreSQL Database, pgAdmin III, Visual Studio 2008 or higher Introduction Power BI supports connectivity to different databases such as SQL Server, MySQL, Oracle and many more (list of all supported databases given here ). By providing necessary details such as server name, database name, username & password, you can easily connect to database and start exploring data using different visualizations in the form of reports/dashboards. This article will walk you through the necessary steps required to configure in your system before establishing connection of Power BI Desktop Tool to PostgreSQL Database. After installation of PostgreSQL database in your machine, if you try connecting Power BI Desktop Tool to PostgreSQL Database, you will get following error as shown in the screenshot below:   Following are the steps required to connect Power BI Desktop Tool to PostgreSQL Database: Setup PostgreSQL Client Connect Power BI Desktop Tool to PostgreSQL Steps to set up PostgreSQL Client: Step 1: Download and unzip the Npgsql package from the link mentioned here You will find two DLL files named as Npgsql.dll and Mono.Security.dll as shown in screen capture below:   Step 2: You need to have Visual Studio 2008 or later. Open visual studio command prompt using administrative rights as shown in the screen capture below:   Step 3: Locate and Open machine.config from the location given below: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config \machine.config .Net framework and Npgsql version might vary depending on the version of installation. Add the following below <DbProviderFactories> tag: <add name=”Npgsql Data Provider” invariant=”Npgsql” description=”.Net Framework Data Provider for PostgreSQL Server” type=”Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7″/> Find the screenshot given below for this step:   Step 4: Now in your visual studio command prompt, use the following command to successfully move DLL files to C:\windows\assembly gacutil -I “C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Npgsql.dll” Where C:\Users\ajagd_000\Downloads is the file location for the Npgsql package downloaded. Similar command you can run for Mono.Security.dll file gacutil -I “C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Mono.Security.dll” Step 5: Once this is done, you can now try connecting Power BI to PostgreSQL. However you will notice below error   Once you restart your machine, you can now connect to PostgreSQL and start playing with your data.   Steps for connecting Power BI Desktop Tool to PostgreSQL: Step 1: Open pgAdmin III interface to create database, table and few records to display in your report as shown in screen capture below   Step 2: Open Power BI Desktop Tool and click ‘Get Data’ and select PostgreSQL Database as shown below   Step 3: Enter server name (in our case it is localhost), database name, username and password as shown in below screen capture   Step 4: Select required table from database and click load as shown in screen capture below   Step 5: Once data is loaded in model, you can start playing around with the data and create beautiful visualizations/reports as shown in image below and later publish it to Power BI Online Service.  

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.