Using Database Images in Tabular BI Semantic Models with Power View
Posted by denglishbi on December 9, 2011
Now that the SQL Server 2012 RC0 release is out I wanted to do a follow up posting to my Using Images in Tabular BI Semantic Models with Crescent (now officially known as Power View). With this release there is now full support for using images stored in the database – binary data type support has now been added to model development in PowerPivot and Tabular Analysis Services projects.
In this example I will use the AdventureWorksDW database sample again that I used in the previous posting. First I will load the data into a model using SQL Server Data Tools (formerly known as Business Intelligence Development Studio – BIDS) inside a Tabular Analysis Services project.
Nothing new here except for the fact you will notice some changes in the Properties area for the column compared to what it looked like in the CTP3 release where now we have two sections – Basic and Reporting Properties.
If we just go ahead and deploy the project as-is without making any changes what will happen? Will we be able to start using the LargePhoto column? Let’s take a look. If we deploy the project and then setup a BI Semantic Model Connection in SharePoint
And then launch Power View
We will see that the LargePhoto column is not available in our Field List to select from
And yes, now the field list is in alphabetical order! Awesome.
So how do we make this column available? Let’s go back into the model design. In order to make this column available we need to do two things.
First we need to set the row identifier property on the Product table and we will do this using the ProductKey column and set that property to True.
Next we need to set the default image property on the LargePhoto column and change this value to True.
Now we deploy our model again to our server and refresh the browser to reconnect to our model connection. Now what do we see in the field list?
We now see the LargePhoto column and we can use this in our visualizations that we add to the Power View canvas.
Now how do we do this in PowerPivot with the RC0 add-in? Well this has changed a bit as well now as far as the UI.
First you need to switch to Advanced Mode in the PowerPivot Window
Now with the Product table selected switch to the Advanced tab in the Ribbon and click on the new Table Behavior option in the Reporting Properties section
Now we will get a new dialog box where we will need to set the row identifier and default image properties for our Product Table
Now that we have this set we can upload this to our PowerPivot Gallery and launch Power View
And now we should see the exact same list and options like we had with our Tabular project
And that is the quick update I wanted to post in regards to the change in RC0 for support for database images and to show you how some of the property and menu options have changed.
Enjoy and don’t forget to get your copy of SQL Server 2012 RC0 so you can start testing it out and planning your roadmap change to the next release of SQL Server.
If you are looking for resources and information to the RC0 release check out this great list that Nick MacKechnie put together here – SQL Server 2012 RC News.