A PowerBI novice's guide to embedding dynamic reports in Smartsheet dashboards

Philip Robbins

I've recently embarked on a foray into linking PowerBI with Smartsheet, with the goal of creating dashboards where data can be sliced and diced interactively, and which update with new data automatically.

The purpose of this post is to share the steps I had to go through, along with a few learnings, so that other novices can take a shortcut to reaching the same result. For clarity, when I started this I was completely new to PowerBI, but already a Smartsheet power user.

Note that as this uses the Smartsheet Live Data Connector, it is applicable to Windows users only. Sorry Mac folks 🤷‍♂️

Also, I appreciate that few, if any, of the following steps involve Smartsheet, but I hope this post is still relevant and helpful to the Smartsheet community.

Step 1: Install the Smartsheet Live Data Connector

This was very straightforward using the comprehensive documentation available here. Seriously, this is way better than any of the Smartsheet help articles I've come across. There are links to download the connector and running the install was very straightforward.

Step 2: Set up the ODBC data source

Some information for this step can be found within the same guide. It's marked as optional, which might be true depending on how you're planning on using the Connector, but I had to do it for connecting to PowerBI.

Firstly I searched for and opened the ODBC Data Source Administrator from the Windows search bar. I then selected New and within the pop-up window selected Smartsheet Live Data Connector. You then get to name your connection and either put in your Smartsheet credentials or use an API token for SSO login.

Step 3: Connect to a sheet in PowerBI Desktop

Upon opening the application, you should see an option to Get Data. From the menu, select Other and ODBC, then select Connect.

Select the data source you named in Step 2 and select OK. This should then trigger the Navigator to open.

IMPORTANT: The Navigator has a query limit of 10,000, which means it may not return all the sheets and reports you have access to (working for a Smartsheet Aligned Partner I have access to a lot of sheets and reports). Per another of my posts, for sheets I couldn't find, I had to go back to Step 2 and connect with one of my alias accounts that doesn't have access to as many assets.

Assuming you can access the asset you want to connect to, select it and then select Load.

Step 4: Create and publish your report to PowerBI

I'm not going to give any guidance on how to use PowerBI here - I managed to figure it out as I went along. The one gotcha I'll note is that if you want users to be able to filter your report from the dashboard, ensure you build some slicers into it.

Step 5: Get the embed code from the PowerBI web app

To be able to get an embed code for your report, someone with admin permissions for your PowerBI will need to ensure the following two toggles are enabled:

Assuming the above has been done, open your report in the PowerBI web app, then go to File -Embed report - Publish to web (public), then follow the prompts to get your code (the one you want for a Smartsheet dashboard is the Link you can send in email).

Step 6: Add the link to your Smartsheet dashboard

Hopefully you'll know how to do this already. It's the Web Content Widget that you want to paste the link into.

At this point you should have a dashboard with an embedded PowerBI report - hooray! The final step is to ensure it updates with new data.

Step 7: Set up refresh cadence

Back in the PowerBI web app, go to My workspace on the left-hand bar and select the Dataset that relates to your report. Go to File - Settings and under Gateway and cloud connections, set up a Gateway.

Note that your organisation may already have something set up that works across the whole organisation. If so, I'm guessing you should be able to skip to the Refresh options below. These were all greyed out for me, so I had to set up a Personal Gateway, which I managed to do quite easily from Cloud connections. This does mean that refreshes can only happen when my device is switched on, so consider that when scheduling them.

Once the Refresh options are available to you, you can configure them however you wish. Depending on your PowerBI subscription level, the frequency of refreshes you can run will vary.

Step 8: Share your funky new dashboard!

The steps above got me to where I was looking to go, but there could be plenty of other ways of reaching the destination. I'm new to PowerBI but learning and experimenting. If anyone has any advice they want to add, please drop a comment in the thread.

And if you're looking for support in setting up a solution in Smartsheet, we at Prodactive are here to help, so do get in touch.

Phil Robbins

https://www.prodactive.co.uk/

https://www.youtube.com/%40Smartsheetexpert

Makiko Joans

@Philip Robbins, hello! I know the live connection and frequent data update may not be idealistic. But do you know if we can automatically update the powerBI visuals from the smartsheet data source almost live fresh?

Philip Robbins

@Makiko Joans I imagine you can only update it as frequently as the PowerBI refresh can run. I haven't looked deeply into the settings myself.

James Keuning

https://community.smartsheet.com/discussion/comment/461732#Comment_461732

That is going to depend on your Power BI license. Pro licenses allow eight refreshes per day and you can schedule them.

You can also connect directly to Smartsheet from Power BI. Power BI - Get Data - Smartsheet. So there is no need to use the LDC.

Makiko Joans

@James Keuning Hello! Thanks for your post. So, the power BI dashboard only updates 8 times per day. But the smartsheet can get the PBI dashboard view at almost live with direct connect?

James Keuning

I will be happy of someone corrects me on this, but this is my understanding: Direct Connect for "instant" updates in Power BI requires an Excel data soure stored in OneDrive. So you need to move the data from Smartsheet to Excel. You can do this with Data Shuttle, a premium app. You can also do it with Power Automate. I suppose you could use the live data connector, but you need a way to trigger it repeatedly or at least every time Smartsheet data changes. And of course if using the LDC, you need a way to get the local file into OneDrive, and there are a number of ways to do this.