Some time in the past I wrote a weblog put up about Energy BI Writer for Excel. Immediately I wish to clarify some new options added to the writer. On this put up you learn to analyse Energy BI knowledge 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 immediately from Excel, but in addition we are actually 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 will likely be enabled by default after you put in it, nevertheless, if you happen to don’t see the “Energy BI” tab within the ribbon in Excel you may allow it from File –> Choices –> Add-ins –> COM Add-ins –> tick Microsoft Writer for Excel.
Analyse Energy BI Service Stories or Datasets in Excel (From Energy BI Service)
Beforehand we might analyse Energy BI knowledge in Excel immediately 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 approach, it downloads an “odc” file that may very well be opened in Excel.
- Now you may analyse the info in Excel utilizing pivot tables and pivot charts.
Analyse Energy BI Service Stories or Datasets in Excel (Straight 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 experiences and datasets immediately from Excel:
- In the event you haven’t already logged into your Energy BI service you’ll immediate to login. Enter your electronic mail handle related to Energy BI service account and click on “Signal In”
- Choose a workspace, report or dataset then click on join
- Now you may analyse the info in Excel with no need to obtain “odc” connection file like what we used to once we needed to analyse a report or dataset from Energy BI service.
- In the event you shouldn’t have any knowledge in your Energy BI service, the revealed add-in detects that and gives you to create pattern knowledge in Energy BI. Click on “Hook up with Pattern”
- In the event you login to your Energy BI service you’ll see the pattern
When 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.
Observe: To make this work it’s a must to shut Excel and reopen it whilst 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 couple of limitations utilized to the present model of Energy BI Writer for Excel. On this part I briefly focus on these limitations.
Shared knowledge is NOT obtainable in Energy BI Writer for Excel
The info that’s shared with you in Energy BI is just not obtainable in Energy BI Writer for Excel. To test this limitation, open your net browser and login to a Energy BI service account and be sure to have some knowledge shared with you. The next screenshot reveals an account that has no datasets and experiences, 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 knowledge is NOT obtainable.
Energy BI Workspace Teams
In case your Energy BI account is a member of a workspace group that shared dashboards, experiences and datasets with its members, and if “Edit Energy BI Content material” is just not granted to your account, then you definately received’t have the ability to see experiences 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 reveals 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 suitable to edit Energy BI content material on “Gross sales (Edit enabled)”. As you see there’s a report and in addition 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 info related to the “Gross sales (Learn solely)” workspace. Certainly the “Gross sales (Learn solely)” workspace is just not even within the listing.
Nonetheless, I can hook up with the “Gross sales (Edit enabled)” workspace and analyse it in Excel.
Evaluation Providers (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 info, then you have to be conscious that the Energy BI Writer for Excel connects to SSAS by way of your native community. Which means that any person attempting to analyse an SSAS dataset in “Join Reside” mode should be related to your native community. The person must be additionally authenticated in your SSAS server.
That is what you see if you happen to attempt to hook up with an SSAS dataset or report from Energy BI Writer for Excel:
Admin Settings for On-premises Evaluation Providers Information Controlling “Analyse in Excel”
In case you are an Office365 World Admin and also you wish to management whether or not or not the customers in your organisation can analyse your on-premises SSAS knowledge in Excel, with the July 2016 replace you are able to do that. This feature 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”
- Change off “Enable customers to Analyse in Excel with on-premises datasets” then click on “Apply”
Now, if you happen to click on “Analyse in Excel” in Energy BI Service you get the next message:
You received’t have the ability to hook up with a dataset/report and Analyse in Excel from “Energy BI Writer for Excel”.
