Some time in the past I wrote a weblog put up about Energy BI Writer for Excel. Right this moment I need to clarify some new options added to the writer. On this put up you discover ways to analyse Energy BI information in Excel. Utilizing the brand new Energy BI Writer for Excel, not solely can we pin an Excel vary or chart to a Energy BI dashboard straight from Excel, but in addition we at the moment are in a position to simply hook up with a Energy BI service, choose any group workspaces and analyse a desired report or dataset.

• Desktop variations of Microsoft Excel 2007 and later
• Obtain and set up Energy BI Writer for Excel
• Energy BI Writer for Excel add-in can be enabled by default after you put in it, nonetheless, in case you don’t see the “Energy BI” tab within the ribbon in Excel you possibly can allow it from File –> Choices –> Add-ins –> COM Add-ins –> tick Microsoft Writer for Excel.

Analyse Energy BI Service Experiences or Datasets in Excel (From Energy BI Service)

Beforehand we may analyse Energy BI information in Excel straight from Energy BI service by:

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

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

• Doing both manner, it downloads an “odc” file that could possibly be opened in Excel.

• Now you possibly can analyse the information in Excel utilizing pivot tables and pivot charts.

Analyse Energy BI Service Experiences or Datasets in Excel (Immediately From Excel)

With the brand new Energy BI Writer add-in for Excel we are able to now simply hook up with a Energy BI service account and analyse the reviews and datasets straight from Excel:

• In the event you haven’t already logged into your Energy BI service you’ll immediate to login. Enter your electronic mail tackle related to Energy BI service account and click on “Signal In”

• Choose a workspace, report or dataset then click on join

• Now you possibly can analyse the information in Excel while not having to obtain “odc” connection file like what we used to after we needed to analyse a report or dataset from Energy BI service.

• In the event you do not need any information in your Energy BI service, the revealed add-in detects that and affords you to create pattern information in Energy BI. Click on “Hook up with Pattern”

• In the event you login to your Energy BI service you’ll see the pattern

In case you have completely different Energy BI service account and also you’d like to change between your accounts and analyse the completely different accounts from Excel simply click on “Profile” from “Energy BI” tab from the Excel ribbon then click on “Signal Out”.

Now click on “Signal In” and use your different Energy BI service account credentials.

Notice: To make this work you must shut Excel and reopen it when you switched to a different account from the Energy BI Writer in any other case you’ll get the next error:

“Error Code: 403 (Forbidden) GroupNotAccessible”

There are a number of limitations utilized to the present model of Energy BI Writer for Excel. On this part I briefly talk about these limitations.

Shared information is NOT obtainable in Energy BI Writer for Excel

The info that’s shared with you in Energy BI just isn’t obtainable in Energy BI Writer for Excel. To examine this limitation, open your net browser and login to a Energy BI service account and be sure you have some information shared with you. The next screenshot exhibits an account that has no datasets and reviews, however, it has a shared dashboard.

Now hook up with the identical account from Energy BI Writer from Excel and also you’ll the shared information is NOT obtainable.

Energy BI Workspace Teams

In case your Energy BI account is a member of a workspace group that shared dashboards, reviews and datasets with its members, and if “Edit Energy BI Content material” just isn’t granted to your account, then you definately gained’t be capable of see reviews or datasets in Energy BI Writer. You additionally can’t see the workspace that you just don’t have “Can Edit Energy BI Content material” permission on within the Energy BI Writer.

The next screenshot exhibits a Energy BI account that may be a member of two workspace teams named “Gross sales (Learn solely)” and “Gross sales (Edit enabled)”. The account doesn’t have “Can Edit Energy BI Content material” rights on the “Gross sales (Learn solely)” workspace. However, it has the appropriate to edit Energy BI content material on “Gross sales (Edit enabled)”. As you see there’s a report and likewise a dashboard shared with this account beneath “Gross sales (Learn solely)” workspace.

After I hook up with that account from Energy BI Writer, I can’t see the information related to the “Gross sales (Learn solely)” workspace. Certainly the “Gross sales (Learn solely)” workspace just isn’t even within the listing.

Nevertheless, I can hook up with the “Gross sales (Edit enabled)” workspace and analyse it in Excel.

Evaluation Companies (SSAS) On-premises

If you wish to analyse a Energy BI dataset or report that originates from an on-premises SSAS Multidimensional or Tabular mannequin and the dataset in Energy BI makes use of “Join Reside” to entry the information, then you have to be conscious that the Energy BI Writer for Excel connects to SSAS via your native community. Which means that any person attempting to analyse an SSAS dataset in “Join Reside” mode have to be linked to your native community. The person ought to be additionally authenticated in your SSAS server.

That is what you see in case you attempt to hook up with an SSAS dataset or report from Energy BI Writer for Excel:

Admin Settings for On-premises Evaluation Companies Information Controlling “Analyse in Excel”

If you’re an Office365 World Admin and also you need to management whether or not or not the customers in your organisation can analyse your on-premises SSAS information in Excel, with the July 2016 replace you are able to do that. This selection is enabled by default so if you wish to disable it:

• Log into Energy BI Service
• Click on “Settings” then click on “Admin Portal”

• Click on “Tenant settings”
• Swap off “Permit customers to Analyse in Excel with on-premises datasets” then click on “Apply”

Now, in case you click on “Analyse in Excel” in Energy BI Service you get the next message:

You gained’t be capable of hook up with a dataset/report and Analyse in Excel from “Energy BI Writer for Excel”.