BI & Data Blog | shadowfax

Prathamesh Sable
- Sep 2, 2020
- 5 min read

Analyze in Excel – the Advanced Methodology

A common ask from users is the ability to view data related to visuals in excel. While there is an option to export data, the format is often unfriendly and has limitations. What’s more, this data becomes disconnected and is not updated if a user looks at after the data is refreshed.

This post will explore a way to add a pre-built excel report that can be designed in advance with company branding and advanced features or VBA. Additionally, it will be linked to the report’s data model via live connect. This means it will continue updating if reopened, as well as maintaining Row Level Security.

End Result

The end result will be a download button that can open a well designed excel report.

Step 1 – Connect to Excel

There are two ways to connect:

Option 1: This option may not be available to you depending on your office license.

Open Excel and navigate to the data tab. Click Get Data the select “From Power BI dataset”. From the list, select the report that contains the model.

Option 2:

The other way is to navigate to the service (app.powerbi.com) and open the report that contains the model. Click the ellipsis and select Analyze in Excel.

This will download a file which has the extension “ODC”, which stands for Office Data Connection. This file contains information that will tell excel how to connect to the dataset on the service.

For more information, see: https://docs.microsoft.com/en-us/sql/reporting-services/report-data/use-an-office-data-connection-odc-with-reports?view=sql-server-ver15

Click on the file to open in excel. Once opened, Excel will present you with an empty PivotTable. If you check the PivotTable fields you will notice that you can now see the fields and measures. The same ones which were created in your Power BI model.

We have now live connected to the model we have published in Power BI service. This works the same as using a live connection in Power BI. Your Excel file has access to all the logic and data is updated as soon as the model is updated. Refreshing the data source in Excel pulls the latest data directly from the Power BI model.

Step 2 – Develop a report

The PivotTable in the report is an OLAP (Online Analytical Processing) PivotTable and has a few differences from standard PivotTable made from flat data.

Just like Power BI, dimensions from multiple tables can be used in the same PivotTable. The relationships set up in the model will be observed.

Only explicit measures may be used in the VALUES section of the PivotTable. This means that unlike Power BI, you cannot drag a numeric column into VALUES and select a summarization (also known as an implicit measure). All values must have a specific measure written in the model file (or use an extension OLAP pivot table tools).

The PivotTable sends a new query each time some element in Excel is changed. For example, if you apply a filter, Excel will send a new query to the model. This works similar to the Direct Query mode in Power BI.

Now we have a connection set up, you can make a pre-defined report that you can give directly to your end users.

You can add a slicer by clicking on the PivotTable, click on the PivotTable Analyze tab and select Insert Slicer (or use Timeline for dates).

There are many advantages of using a live connection.

The Excel file can be branded with company logos
Pre set filters can be added.
More advanced features such as Data Solver, VBA and macros can be added.

In addition, the data can be updated inside the workbook, or set to be refreshed on open. Users will also be prompted to sign in and authenticated through their Office account. Features such as Row Level Security will continue to work.

Keep it FRESH

You can set the report to refresh every time it is opened. Navigate to the Data tab and click “Queries and Connections”.

There will be one query, usually starting with pbiazue://. Right click this query and select properties.

From here you can enable “Refresh data when opening the file”.

Step 3 – Upload the excel

Now, that the excel is created, you can distribute it to end users. A helpful way is to create a download link directly in the report.

A good place to host this would be SharePoint – you can link a SharePoint site directly to the Power BI workspace. It is secured through Active Directory, plus if users are already logged into Power BI they do not need to sign in again.

First, navigate to the online SharePoint location where you wish to host and upload your excel file.

Next, right click the file, and select download. This will download the file back to your local machine.

Now, navigate to your browser’s Downloads page. You can access through the settings of the browser, or if you are on a Windows machine, most browsers you can use the shortcut Ctrl-J. From here you will see the file you just downloaded. Right click the link and select what resembles “Copy link”. This may vary depending on the browser:

Chrome:

Save the link you have copied to be used later.

Step 4 – Adding to the Report

For my download button, I am just using a down arrow image which I downloaded from an open license image site

In Power BI Desktop, open the Insert tab and select Image. Select the image you wish to insert.

After it has been added, select the image and open the visualizations pane. Turn on Action and change type to web URL. Then paste in the URL (the direct download from SharePoint) you saved earlier.

To tidy it up, I will add it next to the related visual. My visual has a text box above it. I have left some white space for a download button.

Step 5 – Using the button

Now, users can navigate to the report and click the download button. This will download the pre-built excel file. Users will be required to sign in and Row Level Security will still apply. In addition, users can use the “Refresh” button in the data tab to refresh data directly from the connected report.

Due to this being a Live Connection users will be required to enable content. In addition, they will need to sign into excel so that they can authenticate to the data model.

Tips and Tricks

Large tables will still need to send DAX queries for each value, so make sure the DAX is efficient.
If using CUBE formulas, these are sent as individual queries to the model. Try to use a single pivot table rather than multiple formulas where possible.
Slicers are useful, but can slow down reports, especially if you have many. One way you could speed it up is by right clicking on the slicer and going to slicer settings. Then untick “Visually indicate items with no data”. This will now always show all items in the slicer – even if there is no data associated with it. While this is less helpful, it can often dramatically speed up reports.’

Download OLAP PivotTable Extensions (https://olappivottableextensions.github.io). This is an amazing extension that can help see information about OLAP PivotTables. The functions are detailed on the website, but two features which are worth calling out:

Ability to see MDX query – this shows the query that is sent to the cube for the PivotTable
Ability to add calculations – you can add measures (written in MDX) to the specific PivotTable without having to add it to the cube

Post reference: https://powerbi.tips/2020/07/analyze-in-excel-the-advanced-method/

Prathamesh Sable
- Sep 2, 2020
- 5 min read

Using Visual Studio Code with Power BI

You can do much more with TOM than simply print a list of tables or update measures. This article is just the first of a series of posts I plan to write describing some of the more useful things you might like to try, such as:

Bulk copying measures from one Power BI report to another
Auto creating “Sum of”, “Max of” measures for every numeric column in your model
Migrating AAS models to Power BI Premium
Customizing Power BI Desktop by registering your C# console app as an external tool to the toolbar

I’m not suggesting or recommending this approach should be your primary method of working with Power BI models. We have wonderful tools already – but it is always good to know you can quickly connect to your models to complete some tasks that aren’t so easy in existing tools.

Not all features of TOM work with Power BI desktop. You can browse the entire model, but only add particular objects such as measures. Currently, you cannot add a table to Power BI Desktop – although this will work if you connect to a model hosted in Power BI Premium.

Working with the TOM like this is a great way to improve your understanding of the AS database engine and will also improve your experience when using some of the advanced features of Tabular Editor.

I came across with a this nice article and tried it myself and it worked successfully.

The exercise

The following step by step exercise will be to download and configure VS Code to the point where it can connect to a Power BI Desktop file and add a new measure. The exercise will be on a clean VM.

Step 1 : Download Visual Studio Code

Open a browser and navigate to code.visualstudio.com then download and run the installer for the current version for Windows. Use default settings when prompted during the install.

Step 2 : Install .Net Core SDK

On a browser navigate to the download page for the .Net Core SDK. Note this is not the same as the .Net Core runtime (which gets installed by the SDK) – it needs to be the SDK. I use version 3.1.301, so any version that matches this or later is fine.

Download and install the SDK executable using default settings.

Once the SDK has installed, you can check by opening VS Code and running the following command at a terminal:

dotnet --info

To open a new terminal window in VS Code, click the Terminal menu item from the top – then select New Terminal from the drop-down menu. This action will open an area at the bottom of the VS Code screen where you can type commands such as dotnet –info

The dotnet –info command should display a list of text in the terminal window, and we are looking to see that the .Net Core SDK install was successful as per the following image (you may need to scroll up):

Step 3 : Create a C# console app

At the terminal window, type the following three commands to create a brand new folder for our project.

md PBI-Tool

cd PBI-Tool

dotnet new console

The first command creates a new empty folder (md = make directory) for the exercise. The second command moves into the new folder (cd = change directory), while the final command runs some scripts to download a template for a c# console app in the new folder.

Browsing this new folder from File Explorer should show the following :

Step 4 : Add TOM client libraries

Now we have a sample application; we can add the .Net Core TOM client libraries by typing the following command into a terminal window.

dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 --version 19.4.0.2-Preview

This command downloads and installs the relevant DLL files needed to connect with Analysis Services databases.

Step 5 : Open the project

VS Code works with folders, so to open the newly created .Net project, we need to click the Open Folder button in the top right, and then navigate to the PBI-Tools folder created using the md command at step 3.

If you are unsure where the folder is, you can derive this from the prompt in the terminal window. In my case, the folder is c:\Users\seamark\PBI-Tool.

Once VS Code has opened the folder, you can open the Program.cs file shown at step 1. If you see a prompt to install a C# extension in the lower right-hand corner, click install, including any additional dialogs asking

And that’s it. We are all set up and ready to start writing code!

Step 6 : Write some TOM code

First off, we will create a small script that will connect to a local instance of Power BI Desktop and display a list of all the table names.

Paste the following code into the VS Code editor over the top of all the existing code. The port number shown in line 11 will need to be updated.

You can use any of these techniques to find the current port number of your Power BI Desktop file. Note this will change around and will not match the number shown in my example.

using System;

using Microsoft.AnalysisServices.Tabular;

namespace PBI_Tool

{

class Program

{

static void Main(string[] args)

{

Server server = new Server();

server.Connect("localhost:50654");

Model model = server.Databases[0].Model;

foreach(Table table in model.Tables)

{

Console.WriteLine($"Table : {table.Name}");

}

When you are ready to run, click the Start Debugging menu option from the Run menu, or press F5. If you get prompted for a debugging environment, chose .Net Core.

The code should connect to Power BI Desktop and iterate through every table in the model and write the name of each table out to the console window.

Step 7 : Bonus Code

As a bonus step, here is an example of how you can add a measure to the model we just connected in the previous step. This script connects to the same model as previously done, and then looks for a table called “Sales” at line 14 (you may need to change this to match the name of a table in your model).

Then the code checks to see if there is already a measure called “VS Code Measure”. If it can’t find one, it creates a new measure and adds it to Power BI Desktop. If you re-run the script a second time, it will update the contents of the measure to a different DAX expression.

using System;

using Microsoft.AnalysisServices.Tabular;

namespace PBI_Tool

{

class Program

{

static void Main(string[] args)

{

Server server = new Server();

server.Connect("localhost:50654");

Model model = server.Databases[0].Model;

Table table = model.Tables["Sales"];

if (table.Measures.ContainsName("VS Code Measure"))

{

Measure measure = table.Measures["VS Code Measure"];

measure.Expression = "\"Hello Again World\"";

}

else

{

Measure measure = new Measure()

{

Name = "VS Code Measure",

Expression = "\"Hello World\""

};

table.Measures.Add(measure);

}

model.SaveChanges();

}

Press F5 to run and voila! Jumping over to Power BI Desktop should show a new measure in the Sales table which shows the text “Hello World” when added to the canvas. Run the report a second time will update the expression to now show “Hello Again World”.

Quickfire Summary of steps

Install VS Code
https://code.visualstudio.com
Install .Net Core SDK
https://dotnet.microsoft.com/download/dotnet-core
Create new console app in new folder
dotnet new console
Add TOM client libraries
dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 –version 19.4.0.2-Preview
Write your code
using Microsoft.AnalysisServices.Tabular;
Server server = new Server();
server.connect(<your AS DB here>) ;

Article reference and tried and tested from : https://dax.tips/2020/07/09/using-visual-studio-code-with-power-bi/