Author: Soheil Bakhshi

On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power Pivot, Power Query

Refreshing On-Prem SQL Server Database on Power BI using Power BI Personal Gateway

image It’s been awhile that lots of us were waiting for this feature. And some of us like me just tried to build it in our way. I spent some time to develop something similar using OData in combination with IIS and Basic Authentication features. Well, it was sort of successful and unsuccessful simultaneously! I mean, I was able to refresh SQL Server data remotely, but, when it came down to refreshing the dataset uploaded into the cloud Power BI it just failed. It was mainly because of the method that Power BI uses to refresh data.

By the way, I’m glad to see that we are finally able to refresh an on-premises SQL Server database from Power BI website. Refreshing data is very crucial for every report and dashboard which is working on top of frequently changing database.  So we need to be able to schedule a data refresh on the cloud. Yesterday Microsoft announced a new gateway specially designed for supporting data refresh for on-premises data sources as below:

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List
  • SQL Analysis Services Tabular model (uploaded data, not live connections)
  • File (CSV, XML, Text, Excel, Access)
  • Folder
  • Custom SQL/Native SQ

As you see SQL Server is not the only one.

Installing Power BI Personal Gateway

It’s easy to install the Gateway. Just make sure that meet the following requirements:

  • The machine that you’re going to install the Gateway on it should be always up and running
  • You can NOT install the Gateway on the same machine as a Power BI Analysis Services Connector

image

DAX, Power BI, Power BI Desktop, Power Pivot, Power Query, Power View

What is new for BI in Excel 2016

It’s been awhile that we are waiting for a sensible improvements in Microsoft self-service BI. The good news is that finally there will be some cool new features added to the next version of Excel which is Excel 2016. By some, I mean, well, there not a lot new BI features, but, some. Something is better than nothing, not too bad though!

Integrating BI features with Excel:

Power View and Power Map:

As you know, Power Pivot was integrated as a built-it feature to Excel 2013. Now I’m really happy that the same thing happened to Power View and Power Map. So you don’t need to install them separately. You can now turn these features on from:

File–> Options–> Advanced-> (scroll down the page) Data-> Enable Data Analysis Add-ins: Power Pivot, Power View, and Power Map

image

OR you can still turn them on from “COM Add-ins”:

Continue reading “What is new for BI in Excel 2016” →

Mobile BI, Power BI, Power BI Desktop

Hold Your Dashboards in Your Pocket, Part 2: Use Your Predefined Dashboards on Your Windows Devices

In a previous post, “Hold Your Dashboards in Your Pocket, Part 1: Use Your Predefined Dashboards on Your IOS Devices“, I explained how to use Power BI app on your IOS devices.

In this post I express how to use Power BI app on your Windows devices. It’s relatively easy to install and use the Windows app.

  • Go to www.powerbi.com and click on the “Download from Windows Store” button


  • It automatically leads to the app page on your Windows Store
  • Click Install to install the app


  • After the app installed successfully open it from start menu

060215_0105_HoldYourDas3.png

  • Click Sign In


 Continue reading “Hold Your Dashboards in Your Pocket, Part 2: Use Your Predefined Dashboards on Your Windows Devices” →

SQL Server, SQL Server Reporting Services - SSRS, T-SQL

Querying SSRS Report Definition Using T-SQL

Do you want to have all reports that used a table in their report definition?

Are you looking for a report that has a desired parameter name?

Have you written a new version of a SQL view or stored procedure and you need to modify all the reports working on top of the version of the object, but, you don’t know what those reports are?

Have you modified an SSAS object and you need to know which reports might be affected?

If you have any of the above questions or in general you need to retrieve all SSRS reports which have a specific string in their report definition, just connect to the SQL Server instance which holds your   REPORTSERVER database through SSMS and simply execute the SQL scripts below:

SELECT C.NAME

       , CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) AS REPORTXML

FROM  REPORTSERVER.DBO.CATALOG C

WHERE  C.CONTENT IS NOT NULL

            AND  C.TYPE = 2

          –AND  C.NAME LIKE ‘%REPORT_NAME%’

     AND CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) LIKE ‘%DESIRED_STRING%’

Enjoy!