Category: Business Intelligence – BI

Power BI, Power BI Service, SQL Server Reporting Services - SSRS

SSRS 2016 and Power BI

SSRS 2016 and Power BI

Without a doubt SQL Server Reporting Services (SSRS) is one of the most powerful reporting tools for several years. There are tons of features that you can use to make a report that suits your customers’ needs. Despite programmability and extensibility are key strengths of Reporting Services platform when it comes to creating dashboards, SSRS has absolutely nothing to offer as SSRS is a report authoring tool. So it never supposed to offer dashboards. In old days we could create web parts in SharePoint or we could install Performance Point and include SSRS reports in Performance Point dashboards. But, setting up and implementing dashboards in SharePoint/Performance Point was always a painful job. Happily with the new version of SQL Server 2016 we are able to pin visuals from existing on-prem SSRS reports to a Power BI dashboard. In this article I explain how SSRS 2016 and Power BI integration works.

Requirements

When you meet the above requirements you can pin visuals from existing SSRS reports to Power BI or you can create brand new reports and pin the visuals to Power BI.

Note: You can only pin report visuals to Power BI that means you won’t be able to pin tables and matrix to Power BI.

Note: If you don’t want to install the developer edition of SQL Server 2016 OR for any reason you cannot use the developer edition, don’t worry, the functionality I’m going to explain is available in other editions of SQL Server 2016. Indeed, the only editions that doesn’t support SSRS integration with Power BI are “Express Edition” and ” Express with Tools” editions. Check this out for more information.

Register SSRS with Power BI

After installing SQL Server you need to configure Reporting Services. As configuring Reporting Services is out of scope I leave it to you.

Note: At the time of writing this article I was using SQL Server 2016 CTP3. The same principles apply to SQL Server 2016.

  • Open “Reporting Services Configuration Manager”
  • Enter the “Server Name” and “Reporting Services Instance” then click “Connect”

SSRS 2016 Continue reading “SSRS 2016 and Power BI” →

BISM Multidimensional, On-premises Data Gateway Enterprise Mode, Power BI, Power BI Desktop, Power BI Service

Connect Live Power BI to SSAS Multidimensional

Connect liv Power BI SSAS Multidimensional

In one of my previous posts I explained how to use Power BI on top of your SSAS Multidimensional using Data Import Scenario. You can also find definitive explanation about “Managing Analysis Services Multidimensional Model” here. In this post I show you how to connect live your SSAS Multidimensional model with Power BI. “Connect Live SSAS Multidimensional” means making a Direct Connection from Power BI Desktop to your SSAS Multidimensional instance.

As it is a direct connection you’ll be able to see/use the following SSAS Multidimensional objects:

  • Cubes
  • Perspectives
  • Measure Groups
  • Measures
  • Dimensions
  • Dimension Attributes
  • Hierarchies including Parent Child

In this post you’ll learn:

  • How to connect live from Power BI Desktop to SSAS Multidimensional
  • Creating reports using SSAS objects like hierarchies
  • Publishing your reports from Power BI Desktop to Power BI Service

Requirements

To be able to successfully create and publish your reports using Power BI Desktop on top of SSAS Multidimensional you will require:

  • The latest version of Power BI Desktop (Current version is 2.31.4280.361 64-bit (January 2016))

Power BI Desktop Version

Continue reading “Connect Live Power BI to SSAS Multidimensional” →

BISM Multidimensional, BISM Tabular, On-premises Data Gateway Enterprise Mode, Power BI, Power BI Desktop, SQL Server Analysis Services - SSAS

Power BI Enterprise Gateway, Everything You Need to Know

On-premises Data Gateway (aka Power BI Enterprise Gateway) is release a while ago (2 Dec. 2015), but, with the latest release on 22 Dec. 2015 Power BI Enterprise Gateway now supports live connections to both SQL Server Analysis Services Multidimensional and Tabular models as well as SAP HANA. In this post I’ll explain lots of important aspects of the Power BI Enterprise Gateway including installation,  configuration for different data sources including SSAS Multidimensional, Tabular and SQL Server Database and much more. If you need to have the lowest possible latency then you need DirectQuery/Explore Live feature on top of your on-premises data sources. The good news is that Power BI Enterprise Gateway now supports all following data sources:

  • SQL Server Database
  • SQL Server Analysis Services Multidimensional
  • SQL Server Analysis Services Tabular
  • SAP HANA

    In this article you’ll learn how to install and configure Power BI Enterprise Gate Way, how to manage different live data sources, how to create reports on top of live data sources and more.

  • Note 1: If you want to use DirectQuery to connect to your on-prem SQL Server Database OR Explore Live your SQL Server Analysis Services Tabular model then you might not need to install and use the Power BI Enterprise Gateway. In those cases you can install Power BI Personal Gateway to connect to an instance of SQL Server OR install Power BI Analysis Services Connector to connect to your on-prem instance of SQL Server Analysis Services Tabular model rather than installing the Power BI Enterprise Gateway. But, bear in mind that selecting the best gateway is really depending on your use cases, your data sources and the environment you’re working on.

  • Note 2: The Power BI Enterprise Gateway and Power BI Personal Gateway CAN be installed on the same machine.

    Downloading and Installing Power BI Enterprise Gateway

    You can download the gateway from the dedicated download page for On-premises Data Gateway on Microsoft website or after logging into Power BI Service with your account, click on “Data  Gateway” from the download menu:
    downloading on-premises data gateway from Power BI service

    OR you can go straight to the gateway page then download the Power BI Gateway – Enterprise (Preview):

    Direct Link to Download Power BI Enterprise Gateway

Continue reading “Power BI Enterprise Gateway, Everything You Need to Know” →

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!