Category: Power BI Service

Excel, Power BI, Power BI Publisher for Excel, Power BI Service

Analyse Power BI Data in Excel

A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.

Requirements

  • Desktop versions of Microsoft Excel 2007 and later
  • Download and install Power BI Publisher for Excel
  • Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.

Enable Power BI Publisher for Excel

Connect to and Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)

Previously we could analyse Power BI data in Excel directly from Power BI service by:

  • Log in to Power BI Service
  • Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”

Analyse Power BI Data in Excel from Power BI Service

  • Clicking ellipsis button of a desired report and clicking “Analyse in Excel”

Analyse Power BI Reports in Excel from Power BI Service

  • Doing either way, it downloads an “odc” file that could be opened in Excel.

Analyse Power BI Data in Excel from Power BI Service Enable Data Connection

  • Now you can analyse the data in Excel using pivot tables and pivot charts.

Analyse Power BI Data in Excel

Continue reading “Analyse Power BI Data in Excel” →

Power BI, Power BI Desktop, Power BI Service

Power BI Synonyms, Take Q&A Experience to the Next Level

In April 2016 a bunch of fantastic features were added to Power BI Desktop. Some of these features, like Query Parameters, Power BI Templates and new drill action to see records, quickly grasped my attention. I wrote about Query Parameters before. You can learn how to use Query Parameters in Power BI Desktop here or more complicated use cases like Query Parameters and SQL Server 2016 Dynamic Data Masking (DDM) here.

Another cool feature is adding Synonyms to the model. Power BI Synonyms can significantly improve the Q&A and query experience. With synonyms, we can now add descriptions to the data model objects such as tables, columns and measures in the Power BI Desktop. The descriptive information could include names that the end-users may possibly use to refer to an object or abbreviations used across the business. Addin these descriptions or, as the name suggests, synonyms makes using Q&A even easier for our customers to find what they are looking for. The customers don’t know all table,  column or measure names. Defining a standard list of names for tables, columns, or measures makes Q&A much more helpful.

For instance, we can add the following synonyms:

Note: The following tables and columns are from AdventureWorksDW.

Original Name Object Type Synonym
FactInternetSales Table Internet Sales, InternetSales
OrderQuantity Column Order Quantity, Order Qty, ord qty
SalesAmount Column Sales Amount, Sales Amt, Internet Sales Amount, Internet Sales Amt
TaxAmt Column Tax Amount, Tax Amt
Freight Column freight
OrderDate Column order date

How it works

It’s easy to set up synonyms in Power BI Desktop. Switch to Model view, then click “Synonyms” from the “Modeling” tab from the ribbon. Then, simply enter the synonyms.

Power BI Desktop Synonyms

After we publish a Power BI Desktop model to Power BI Service, the synonyms will play a great role in Q&A so that when the customer types “ord qty” the Q&A engine will recognise it as “OrderQuantity” and display the results. It’s really cool, isn’t it?

But let’s think a little bit out of the box. What if we add some translations as synonyms? Hmm. I think it would be really great if a Spanish customer could type Spanish column names in Q&A rather than English. I added some translations to FactInternetSales columns and DimDate columns.

Power BI Desktop Synonyms

Thanks to Google Translate for French and Spanish translations. Sorry French and Spanish guys, if the translation looks funny. Smile

Now, I publish the model to Power BI Service. To do so, just click on “Publish” from the “Home” tab from the ribbon.

Publish Power BI Desktop Model

Continue reading “Power BI Synonyms, Take Q&A Experience to the Next Level” →

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” →

Power BI, Power BI Service

Power BI and iFrame

Power BI is growing very quickly and there are tons of fantastic features added to it during the past few months. So there are lots of Power BI subjects we can talk about these days. In this article I want to represent a new feature added to Power BI Service which is “Publish to Web”. Publish to Web is basically putting Power BI reports in an iFrame. So now we can combine Power BI and iFrame  together and embed Power BI reports into a web page to share data insights to the Internet. Keep in mind that the data we publish to web will be available to anyone on the Internet. So we really need to make sure we do NOT share any confidential information and we have the rights to share that information.

Note: Publish to web feature is in Preview  so it is available for all users with no costs during the preview. So you don’t need to have a Pro account to be able to use this feature for now.

I also explain how to embed a rich media content in your Power BI Dashboard.

Note:  Publish to web and embedding a rich media content to your dashboard are different features available in Power BI Service.

Power BI Publish to Web

Publishing a Power BI report to web not only creates a link that you can send in email, but also creates HTML iFrame you can put in your blog or website. I would like to remind you again that the “Publish to Web” feature is only available in Power BI Service so don’t get confused by looking for such a feature in Power BI Desktop.

Enabling Power BI Publish to Web

To enable Power BI Publish to Web follow the steps below:

  • Login to your Power BI Service
  • Open a desired report
  • Click “File” menu then “Publish to Web”

Power BI and iFrame

Continue reading “Power BI and iFrame” →