Using PowerPivot Model Images with Excel 2013 Power View
Posted by denglishbi on September 13, 2012
As you have heard and possibly seen in my other Excel 2013 posts, Power View is coming to the Office suite. This is great news to everyone that uses Excel. The one thing you might be curious about is how to include and reference images within your data model. This post will cover the options that you have and provide a few screenshots of the process.
If you are working with data that resides in a database and the images are stored in the database table you will be able to work with them once you load the data into your data model within PowerPivot. This hasn’t changed at all, the only thing that has changed is that now you don’t have to upload your PowerPivot file into SharePoint to create the Power View report. Instead you can simply insert a new Power View report and create your reports.
Here is a quick look at this type of a setup.
First the database tables have to be in place. In this example I am going to reference two different tables that will have images in them, one for authors and the other for books.
Once the records have been added to the tables we just need to add the images and one way to do this is with some T-SQL and OPENROWSET command as displayed in this blog post here – Insert binary data like images into SQL Server without front-end application.
Once the data is in place we need to create our model, so we will open up Excel 2013, go into the PowerPivot window, load the data into our model, define relationships, and do some advanced setup on our tables.
Let’s take a quick walkthrough of this process if you are not familiar with this yet.
Once you have activated the PowerPivot add-in in Excel 2013 you will be able to select that tab in the ribbon and manage your data model.
Once you are in the PowerPivot window you will be able to get external data and import data into your model.
Select the tables you want to work with in your model.
Import the data.
Switch over to Diagram View and drag-and-drop the fields to establish relationships between the tables.
Select the Author table and define the Table Behavior so you can define the default label and image for each record. Do the same for the Book table. Now that you have that setup one last step you can do before you start to use the model is to perform some cleanup.
Here we have hidden the two row identifier columns in each of the two main table and also hid the reference table that relates the two tables. You can do this by simply right-clicking on the items and selecting ‘Hide from Client Tools’ from the context menu.
Now you can switch back to Excel and from the Insert tab in the ribbon select Power View.
Now we will see the two tables in our model and the item that we are particularly interested with is that the Photo column is available and the key step was the configuration step in the Table Behavior.
Now we can select the items in the field list and start to put together a report such as this –
Web Server Images
Another option you have for referencing images in your Excel PowerPivot model is to store the image files on a web server. The one thing that you need to make sure is that the web site is setup with Anonymous authentication and users have permissions to the file location. Once that is setup you can establish a URL reference to these images files in your table within your model. After the URL is configured you will need to review the advanced settings just like the database image setup so that you can reference the images in your Power View report.
Let’s take a quick look at the difference here. First we have the anonymous web site.
Then we place the files on the web server to reference in the location we defined for the web site.
Make sure that users have access to the location.
Now we go through the similar steps as we did in our first example, but after we define our relationships we need to go back into our tables and define the URL paths for our images.
In both tables we will create a calculated column called PhotoURL and with some DAX setup the paths to the images.
The secret here is to make it data driven, if you are not able to do this then you would need to store the URL values in the source table and import them. Luckily with a little DAX we could setup a formula that will work for each record. Now if someone wants to replace one of the images on the web server they can do so and it would be reflected in the report with a refresh.
The next step is to go into the Table Behavior like we did in the previous walkthrough and make reference to this new column for our default image.
Once this is done we can go ahead and switch back to Excel and create a Power View report, just like we did previously.
The one thing to note here is that when you switch over to the PhotoURL reference you will be prompted with a security warning about accessing external pictures. Go ahead and click ‘Enable Content’.
Now we can look at our same report –
Only this time we are using the PhotoURL versus the Photo field.
And there you go, two different options for referencing and using images within your Excel 2013 Power View reports. Using the web server setup is nice because then you can swap out the images a bit more easily, but you still have the option to store the images in the database if that is how you where you want to keep them.
Now go ahead and try this on your own and really spice up your reports with some nice images:)