cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mjfulke Regular Visitor
Regular Visitor

How to use Images Stored in a SQL Server Table with Power BI

 Power BI has several options to display images in a report or dashboard but they mostly require that the image is a url to a publicly accessible website.    What I'll show you in this post is a way to take images stored in a database and bind them to a table visualization.   This will work with lots of visuals that display images.   My use case was to display chart images generated from R using SQL Server's Machine Learning Server


@Meagan Longoria recently introduced me a great article from @SQLJason Embedding Images in Power BI using Base64.   This gave me all the information I needed to look at solution to bind image data stored in a SQL Server table.   

Definitely read SqlJason's article, paying close attention to limitations etc.  This solution is meant for relatively small images you would show in a report.  If you use it for large images it will likely fail.  Reduce the resolution in those cases.

 

Getting images into SQL Server

 Go to your favorite SQL Server and create a table and insert some images.  Here's my sample code to give you one way you can get images into a database table

 

--CREATE Table Logo
--(
--    LogoId int,
--    LogoName varchar(255),
--    LogoImage image
--)

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  1,'Power BI',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\powerbi.png',SINGLE_CLOB)  as ImageFile

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  2,'Azure',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\azure.png',SINGLE_CLOB)  as ImageFile

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  3,'Microsoft',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\microsoft.png',SINGLE_CLOB)  as ImageFile

 

Get the image data using Power BI Desktop

Now that you have some images in a SQL table, switch over to Power BI Desktop and get the data.
(In Desktop:  Get Data, SQL Server, Login to your SQL Server, and pick your table that stores images. 

2018-03-23_15-21-1.jpg

Change the binary column to Text

 

 2018-03-24_9-51-27.jpg

 

Now create a custom column and append a URI to tell Power BI these are png images.   If using jpeg use jpeg instead of png.

 

2018-03-24_9-52-27.jpg

 

Close & Apply the data.


This is an important step. In the Modeling tab, change the image column ([Logo]) to a  Data Category of Image URL.

 2018-03-24_10-11-07.jpg

 

Now you can use a visuals, like a table, and display your images that came from a SQL Server vs. having to find a way to store images on a public internet site.   

 

2018-03-24_10-17-28.jpg

 

 

I hope this works for you like it did for me.  


 

1 ACCEPTED SOLUTION

Accepted Solutions
mjfulke Regular Visitor
Regular Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

17 REPLIES 17
nrsleight Frequent Visitor
Frequent Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

Could you do a similar thing with SSAS?

mjfulke Regular Visitor
Regular Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

syale Frequent Visitor
Frequent Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

Is there a size limit for the png?

mjfulke Regular Visitor
Regular Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

There is a limit.  Nothing documented but they have to be "smaller".  I tested with a jpg that was 3MB and it failed to render.

andy_scott42 Regular Visitor
Regular Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

I'm using this method to display images in a table and some of the images are being cropped. Is this because the image size is too big? 

mjbernier Frequent Visitor
Frequent Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

Tried all the steps laid out here, but it didn't work. All I see are either the URLs or the "broken image" symbols. Figuring it might be an image size issue, I reduced the images to less than 100K and still no joy. I checked the Ideas forum and this one was first introduced over THREE YEARS ago with no response from Microsoft. Why do we have to take in-house images sitting in tables and export them to a file server, then link to them in order to display them in an in-house report? It's beyond ridiculous.
andy_scott42 Regular Visitor
Regular Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

I've found that the maximum length a string can have to be processed by Power BI is 32766 characters.

So once you've converted your image to a Base64 string, if the length of the string is greater than this then it will crop the image.

 

Highlighted
lunds007 Occasional Visitor
Occasional Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

I ran into the same issue!  Cropped photos Smiley Mad Anybody come up with a genius solution to this yet?

DNickell Occasional Visitor
Occasional Visitor

Re: How to use Images Stored in a SQL Server Table with Power BI

andy_scott42 seems to have it nailed.   Whether or not you'll be able to use the techniques in this article will depend on how big your images will be (in Pixels) and how detailed

 

In the screenshot below .. the one I will probably mimic for my work is #3.  Using Snagit, I take the image and resize it to something less than or equal to 100x100 pixels.   I then resize the canvas to 105x105 pixes to give a border.  I don't expect to need anything displayed in my visualizations that will be any bigger than that.

 

Explainit.png

 

#1 is small and low resolution.  Its too blurry and quite unappealing given the 4 bit color application

#2 is small and as a result - blurry

#3 will work fine (see description above).  Its 9KB (well under the 32KB mentioned as the limit)

#4 is a big image with only 16 colors.   The reduced color works for the most part, but that may not hold for other images

#5 illustrates what can happen to an image larger than 32kb

 

So -- think about how big your images really need to be for your reports/dashboards.  Format in advance.  If, at your target image size you're still over 32KB, then consider reducing resolution or color

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 184 members 2,527 guests
Please welcome our newest community members: