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

8 REPLIES 8
nrsleight Visitor
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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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?