Using Database Images in Tabular BI Semantic Models with Power View

[tweetmeme source=”denglishbi” only_single=”false”]

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.

Tada!

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

Success!

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.

PASSMN October Monthly Meeting Follow Up

[tweetmeme source=”denglishbi” only_single=”false”]

Well another month is coming to an end and this past week I did a presentation on “An Introduction to BI Semantic Model & Delivering Self-Service Reporting with Power View (Crescent)”.  So the momentum from the PASS Summit continued to roll on and we continued with the SQL Server 2012 content and providing insights into what is coming showcasing the new features in Analysis Services, PowerPivot, and showing the new feature that will be available in the Reporting Services SharePoint 2010 Integrated Mode – Power View!

If you were not able to attend, no worries, you can check out the recording, watch some short video clips of the demos, and take a look at the presentation content on SlideShare.

Intro to BI Semantic Model & Delivering Self-Service Reporting with Power View from Dan English on Vimeo.

PowerPivot Denali CTP3 Demo from Dan English on Vimeo.

Project “Crescent” Demo from Dan English on Vimeo.

Intro to BI Semantic Model & Self-Service Reporting with Power View

Next up is ScarePoint Saturday to do a “Inside PerformancePoint” presentation and then onto MN SQL Saturday #99 to do the “Inside PerformancePoint” presentation again!

I mentioned a setup of using XBox Kinect with SQL Server 2012 at the user group. There is a video of this if you want to check it out on YouTube here – “SQL Kinection – Control SQL Server 2012 with gestures using Microsoft Kinect!”

Using Images in Tabular BI Semantic Models with Crescent

[tweetmeme source=”denglishbi” only_single=”false”]

One of the really neat features with the new Reporting Services authoring tool code named ‘Crescent’ is that you can reference images directly from your model.  This can look really cool when you use the images in a card visualizations, part of a table, as tiles, and as slicers to use as filters within the report.  Here is a nice report that is part of the Hello World Picnic Sample that came out with the CTP3 release of SQL Server ‘Denali’ (download from Project Crescent Samples and download images from Sample Images and you can find information on the Crescent Samples for CTP3 in the readme here).

So this is really cool and provides a really nice visualization perspective to the report both from what you are looking at and to add a little color and life to the report.  When you are looking at a sales report for products, wouldn’t it be nice to see the image of the product or category that you are looking at.  So what better place to store this information than in your BI Semantic Model that you can then leverage with your reporting tool like Crescent.  So how do you do this?

Well in the current CTP3 there are a couple of different ways that you can go about this.  The first option is to store your images in say a SharePoint library and the provide the Image URL references in the model.  That is how the example above is being done.  That report is actually leveraging a PowerPivot Tabular model that was built in Excel 2010 leveraging the CTP3 release of the PowerPivot add-in which you can download here.  The second option that you have right now is to store the images in a database table and reference that way use base64-encoded strings.  Getting this option to work in CTP3 is by no means straightforward either and took me quite a while to figure out.  Just using a base64-encoded string was a puzzle and luckily Robert Bruckner provided that solution as to how to format the binary data in the database to this format.  There is a third option which is simply referencing the binary data from the database, but currently the CTP3 release of Crescent will not recognize this field type in the model at all.

So lets take a look at the two working options we have that we can use with the CTP3 release of Crescent along with the Tabular BI Semantic Models using both a PowerPivot and a Tabular Analysis Services solution as examples.

Image URL

As I mentioned, the Hello World Picnic PowerPivot model uses this setup.  So I will highlight how this is done.

• First you need to have images available for what ever you want to display.  The download link is available above for the samples and then the instructions are provided in the information readme.
• Next you need to place the images in a location that can be referenced as a URL in the model.  In this example they are placed in a document library within a folder.

images are stored in a SharePoint document library called Shared Documents buried down a couple of levels –> /Images/HelloWorldPicnic/

• Then within your table in the PowerPivot Tabular model you can provide your URL to the image.  In this example they store the name of the image in the table and then use a calculated column to generate the Image URL.  In order to activate the option to set this as an Image URL in the Reporting Properties (which is in the Advanced tab – might need to turn on the Advanced Mode in the file setting first) you need to define the Table Identifier column in the table.  This means that you need to specify which column makes the table unique to identify each record (in this example it is the ItemId column).

need to specify a table identifier – a column that defines the table and makes each record unique

a calculated column is used here that includes the image name column from the table along with the rest of the string to the SharePoint location and concatenates everything together

• Then once you have deployed your file to SharePoint you can create a Crescent report from the PowerPivot file and you will now see the field in the field list to select from along you can start leveraging this in your report design.

the Items Name and Drawing fields are selected from the Field List which get added to a Table

So that one wasn’t too bad.  And this is a nice option because having the images in a document library location is a nice spot where end-users can maintain these as needed and swap them out.

Okay, let’s take a look at the other option that is currently available and that is storing the images in the database and using a base64-encoded string.

Base64-Encoded String

So what if the images are already stored and available in a database table? Well there is a solution that can allow you to do this, at least if you are doing the Tabular Analysis Services project type.

• Here we already have the images stored in a database table in as a varbinary(max) data type – just a warning, I believe that the max size for the images is 25K, I don’t believe that has changed yet, so just be aware of that in case your images do not show up. In this example we will be using the AdventureWorksDWDenali CTP3 sample database leveraging the product dimension data – you can download the sample databases and projects for Denali CTP3 here.

table contains a varbinary(max) column called LargePhoto that contains some images of our products

• Once the table is imported into a Tabular Analysis Services project we need to modify the Table Properties for the product dimension and change the reference from Table Preview to Query Editor to generate our base64-encoded string that we will use in our model instead of the binary data that is already available from the LargePhoto data (this is currently not supported by Crescent right now, but will be in next release).

now the key here is to add the logic to generate the base64-encoded string which is the following – thanks to Robert Bruckner for providing the logic:

cast(N” as xml).value(‘xs:base64Binary(xs:hexBinary(sql:column(“[dbo].[DimProduct].LargePhoto”)))’, ‘varchar(MAX)’) as LargePhoto64

• Next we need to once again set the Table Identifier (Product Id column – in this example the ProductKey column has been renamed to Product Id) and then set the properties on the new LargePhoto64 column for Image URL to True along with Representative Image to True

set the Product Id as the Table Identifier (which is actually the ProductKey column)

set the Image URL and Representative Image properties to True for the LargePhoto64 column

• The next step is the hack that we have to perform to the Code portion because if you try to change the Image URL property back to False it will also set the Representative Image to False and that is not what we want.  You can right-click on the bim file in the Solution Explorer and select ‘View Code’ or pick the option from the ‘View’ file menu.  Once you are in the Code behind do a search for LargePhoto64 and it will take about three times to find the right spot we are looking for which contains the Type property setting that displays ImageURL.  We need to change this to just Image.

find the LargePhoto64 column where the Type property is located with ImageURL

now change the Type so that it does not include the valuens=”ddl300” and remove URL from the value so that it is just <Type>Image</Type>

• Now you want to Save this change and then double click on the bim file in the Solution Explorer to go back into the Design view and deploy the database to your Tabular instance of Analysis Service.
• Setup the BISM Connection File to you model (see my previous posting on this if you are not familiar with how to setup data connections to a Tabular BI Semantic Model – Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration) and then click on the connection file in your SharePoint library and you will now be able to utilize the LargePhoto64 column as an image in your reports.

the Product Name and LargePhoto64 columns were selected from the Field List and added to a Table

So there you go, not too bad, huh?  You have two different options right now for using Images in your models.  In the next release we should see direct support with Crescent in being able to utilize binary data directly instead of having to use the base64-encoded string method.  But for the time being this is what you can use.  My preference would be the Image URL.  This just seems not only more straightforward, but also easier to maintain and update, especially from an end-user standpoint.

Enjoy!

Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration

[tweetmeme source=”denglishbi” only_single=”false”]

Update (7/22/2011): Added a note about an IE Security setting for SharePoint menus at the end of the post.  I will add a PDF of this posting either tonight or over the weekend if anyone is interested in that.

Update (7/24/2011): Added PDF download of the posting for printing, offline, and eReader availability at end of posting in the File Download section.

With the recent release of the SQL Server Denali CTP3 there have been a few changes in the installation process.  One of those is with how the SharePoint integration configuration works.  There was new post yesterday in regards to this process Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3).  In this post is goes through the steps assuming that you have installed SharePoint 2010 first.  What if you are like myself and installed all of the SQL Server components (SQL Server ‘Denali’ CTP3 Install Experience), including Reporting Services, before SharePoint 2010?  Well the process is a little different because since you didn’t have SharePoint installed first the integration process will not be completed by just installing SharePoint 2010 next.  There are a few steps you can take and I am going to go over the PowerShell option that I took through with the help of some documentation I was provided from Microsoft with previous CTP testing.

So here are the steps that I went through to get this working (this was done on a Windows 2008 R2 VM):

1. Installed all of the SQL Server Components (installed Multidimensional SSAS and just installed the SSRS and did not configure it) – SQL Server ‘Denali’ CTP3 Install Experience
   
2. Next I went through the installation process again and installed an additional SSAS named instance in the Tabular mode.
3. Then I installed SharePoint 2010:  pre-reqs and then SharePoint (but did not run the configuration wizard – unchecked the box and closed the process down after the install).  I had to actually run the pre-req install twice and had to do a reboot prior to the SharePoint install as I recall.
   
4. Then I installed the two SharePoint 2010 SP1 files – one for Foundation and the other for Office Server (this is going to be required to get SharePoint to work with Denali for the database engine).  I believe a reboot was required after this as well. http://support.microsoft.com/kb/2510766
5. I then did the SQL Server install again to install PowerPivot for SharePoint creating another named instance of SSAS for PowerPivot.
6. The next step can be done a few different ways and I picked the new PowerPivot for SharePoint Configuration Tool (need to ensure your pass phrase meets the security requirements) – this option is available in the Tools page of SQL Installation or in the Configurations folder in your SQL Server install folder in the Start Menu. This process will configure the PowerPivot integration, activate it, and create a PowerPivot SharePoint Site Collection.
7. I then installed the ADO.NET Data Services for .NET 3.5 SP1which is required if you want to use PowerPivot against an Atom Feed in SharePoint.  A reboot is required after this install.
8. Setup the SSRS service and application (since I installed SQL and SSRS prior to SharePoint the Integration with the add-in did not complete – because no SharePoint).
   1. Run the Microsoft SharePoint 2010 Management Shell as Administrator
   2. Type the following PowerShell command> Install-SPRSService
   3. Type the following PowerShell command> Install-SPRSServiceProxy
   4. Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      
   5. Verify that the service is started in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Services on Server and now look for the SQL Server Reporting Services Service to see that it has now been setup and is running
   6. Now we are going to create a new SQL Reporting Services Application in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Service Applications and create a new SQL Server Reporting Services Application
      
   7. Specify a name for the Application, create a new application pool, and specify the database server name.  Down below you will also need to select the web application to associate this application with and I used the Windows Authentication for the database authentication
      
   8. Now we should be all set and you should see the new application that you just created
      
9. Now you are off to the races and can create a Reports Library in SharePoint and add the content types including the new BISM Connection File for using to create Crescent Reports! You will need to install Silverlight to get the SharePoint Create window and this will be needed for Crescent as well since it is a Silverlight application.  If you do not see the Reports Library option you will want to make sure that the Enterprise Features are enabled not only at the Site Collection Level, but also at the Site level. I left out the Report Builder Model content type because from here on out it is all about the BI Semantic Model (BISM)
   
   
10. Now you can create a new BISM Connection File and or Report Data Source to get started with Crescent (shown below in the respective order). After you create these you will not see them in the library.  In order to see them you can go to the Library tab in the Ribbon and select the ‘All reports and dashboards’ option for the view and then you will see the connection and data source files you have created (by default it simply displays just the reports so it is not cluttered)
    
    
    
11. Now you can launch Crescent from the context menu from the connection and data source files you have created.  In order to create these connection files and data sources I installed and deployed the Denali Samples from CodePlex.  There is one issue with the Tabular project that you need to be aware of when you want to deploy this that has been documented here – Problems with the SSAS Tabular Model Projects Denali CTP3 samples (according to the site the samples have been fixed, so if you haven’t downloaded them you should be okay).
    
    

So there you go, you are off to the races.  You know have SSRS in the new SharePoint Integrated Mode setup running as a SharePoint Application (no longer running as its own Windows Service, completely setup and managed by SharePoint now) and you are creating awesome reports with Crescent.

Enjoy!

NOTE: If you are having an issue in say Step 9 and you are not getting menu options in SharePoint you need to go into your IE Security Settings and enable the Active Scripting.  Once you do that you should be able to use the menu options in SharePoint.

File Download: