Its better first to create file table to store image data in SQL database. Following steps will guide you.
1.Go to SQL configuration manager and right click on SQLSEVER and enable “FILE STREAM” option.
2. Then run the following System Stored procedure as below and create a database called StudentDB
"exec sp_configure filestream_access_level, 2;
“create database StudentDB”
3. Then create a file table by executing following command.
"Alter database StudentDB
add filegroup FileStreamGroup3 contains filestream;
Alter database StudentDB
add file (
name = FileStream1,
filename = 'c:\Temp\FileStreamFileGroup3'
) to filegroup FileStreamGroup3;
Alter database StudentDB set filestream ( directory_name = 'StudentDB' );
Alter database StudentDB set filestream( non_transacted_access = full );
create table StudentImages as filetable;
4.Go to file table in studentDB database. Then select “StudentImages” table and right click on it and select “Explore file table directory”. There you can add any image file on that folder. finally all the images will be stored in the Student Image database.
5.Then create another table called “Student” on StudentDB database in order to track image path and name.(this is not a file table). use following command
CREATE TABLE Student(
ID int NOT NULL,
name varchar(100) ,
6.Run following SQL code to store data in “Student” table by acquiring data from “StudentImages” file table.
DECLARE @root nvarchar(100);
DECLARE @fullpath nvarchar(1000);
DECLARE @Imagename nvarchar(500);
DECLARE @RowNumber int = 1;
DECLARE @RowCount int;
DECLARE @ID int;
SET @RowCount = (SELECT count(stream_id)FROM StudentImages)
WHILE @RowNumber<= @RowCount
SELECT @root = FileTableRootPath();
SELECT @fullpath = @root + file_stream.GetFileNamespacePath(), @ID=@RowNumber, @Imagename = LL.name
FROM (SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY stream_id) AS Rank FROM StudentImages)L)LL
WHERE LL.Rank= @RowNumber
SET @RowNumber = @RowNumber+1
INSERT INTO Student VALUES ( @ID, @Imagename, @fullpath)
7.If your are using a tabular model to create reports in Power BI try to split the name column into 2 parts in order to extract the name out of name column in “Student” table,as at first in Student table image file name is recorded in “name” column like “.png” .Therefore we have get only the name part without “.png”. use the following command to do that. Following code is still valid if your using directly SQL database without tabular model as the data source for Power BI.
WHEN Name LIKE '%.%' THEN LEFT(Name, Charindex('.', Name) - 1)
END as Student_Name,ID,Path
8.Then create a R visual in Power bi and at there run the following code to display the image for selectedcan select a student from by creating filter in Power BI using “Student name” in student table.
im <- load.image(dataset$Path)
But to run the above R code you should have install 3.3.3 or greater version of R and try to install the “imager” pakage on it.
9.Finally you can have image on selected student by the filter you have already created in Power BI.
Blog courtesy Lahiru Dhananjaya