Installing SQL Server 2012 SP1 CTP4 Experience

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

A standalone version of the SQL Server 2012 SP1 was released this week.  It is not the final release, just a community technology preview (CTP4), but a release that we can test out none the less.  The exciting part of this release to me are the enhancements that have been added to Power View.  Here you will see the steps I went through to upgrade my evaluation virtual machine I have setup that is based on the Appendix B that comes with our book – Visualizing Data with Microsoft Power View.

First you will need to download the SQL Server 2012 SP1 CTP and you can do that from here – SQL Server 2012 SP1 CTP (there is even a full SQL 2012 slipstream version you can download as well that includes the service pack).  You also might want to download some of the additional features that are provided with the feature pack and you can get that from here – Microsoft® SQL Server® 2012 SP1 Community Technology Preview Feature Pack.

Once you have the service pack downloaded you can run this on your machine and follow through the screenshots below.
 

I had one item that I needed to shutdown so that I could avoid a restart after I installed the service pack and that was related to IIS services (these are running because of SharePoint 2010).

So I had to stop the IIS Admin Service and the World Wide Wed Publishing Service.

I did a refresh check and I was good to go.

Running RTM Evaluation Edition – 11.0.2100.60

Updating the default instance of SQL 2012

Updating 2nd instance of SQL 2012 (POWERPIVOT)

Instance MSSQLSERVER overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:15:42
  End time:                      2012-09-22 08:33:33
  Requested action:              Patch

Instance POWERPIVOT overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2012-09-22 08:33:57
  End time:                      2012-09-22 08:35:24
  Requested action:              Patch

Patch Level: 11.1.2845.0

Verify I can connect to my instances of the database engine and Analysis Services along with reviewing the build numbers.

Microsoft SQL Server 2012 – 11.0.2845.0 (X64)
    Sep  1 2012 02:36:04
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Now to go start into SharePoint and check out Power View!

New Ribbon layouts, icons, and features (images, hyperlinks, field list control)

More style, text, and background options

New visualizations – Pie and Map charts along with font sizes

Pie Charts, but no data labels or sorting options.

Bing geo-encoded maps

Pre-defined drill up/down capabilities

Support to restore Excel 2013 PowerPivot files in Tabular SSAS

SQL Saturday #149 is coming up next:)

SharePoint 2010 will not support the Power View that is in Excel 2013 files.

I noticed that my PowerPivot was not working yet, so need to run the PowerPivot Configuration Tool.

New version detected, time to upgrade.

Run the upgrade process.

After this is done we go back in and test out PowerPivot again

Now it works just fine.  The one thing I noticed is that it doesn’t appear to be working with the Excel 2013 PowerPivot files though.

Maybe these will not work in SharePoint 2010, not quite sure yet, will need to look into this more.

For more information on the new features available in the updated version of Power View check out some of these references:

• Power View meet Microsoft Excel 2013
• Power View meet Microsoft Excel 2013 Part 2
• Learn More about Power View in Excel 2013 Preview
• Sean Boon’s Blog (great series on Olympic data)

Here are some more links for information on the SP1 updates:

• New or Enhanced Features in SQL Server 2012 SP1
• SQL Server 2012 SP1 CTP 4 Release Notes

This service pack also includes performance improvements with slicers and filters with PowerPivot and Tabular SSAS as well as I demoed here in this post and video – Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo.

Now we just need to wait for the final release and hopefully that will be coming out in a couple of months now, should be very soon. Enjoy!

MN SQL Saturday 149 Pre-Cons & Main Event Next Week Sept 28 & 29

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

Just a quick reminder that next week the MN SQL Saturday 149 takes place.  On Friday, September 28 there are two all day pre-con events.  There is a Business Intelligence pre-con where Brian Larson and I will be going over Microsoft BI including items such as PowerPivot, SharePoint, Tabular SSAS, and Power View.  I also know that there is an added bonus to signing up for our session besides just getting your lunch paid for on Saturday, you will get a copy of our Visualizing Data with Microsoft Power View book!  Down below I have included the tentative agenda for the day as well.  In addition to our pre-con SQL Server MVP Ted Krueger is doing one on T-SQL performance tuning.

• BI1 – Dan English & Brian Larson: Unlocking Insight – Be A Data Hero

9:00 to 9:30 – Welcome and introductions
9:30 to 10:45 – Talk about Microsoft BI
10:45 to 11:00 – Break
11:00 to 12:00 – PowerPivot
12:00 to 1:00 – Lunch
1:00 to 2:00 – Team/Corporate BI: SharePoint and Tabular SSAS
2:00 to 3:00 –  Building the reporting model
3:00 to 3:15 – Break
3:15 to 4:30 – Power View
4:30 to 5:00 – Questions

• DB1 – Ted Krueger: Make Me A Tuning Believer

In addition to doing the pre-con I will also be doing a session first thing the following morning.

• Power View – Bringing your Data to Life!

The world of self-service BI just keeps getting better. From self-service analysis with PowerPivot to self-service reporting with Microsoft Power View. This session will discuss the new BI Semantic Model concept, show you how to build models with PowerPivot and Analysis Services, and then go over using the new browser based reporting tool Power View to provide highly interactive reporting solutions. There is fun to be had by all and who knew that analyzing data could be so fun, come and find out more and see how it all works and what to expect with the upcoming release of SQL Server 2012 SP1 and even Excel 2013!

So if you are in the area and are available next week, take this opportunity now to review the schedule – http://sqlsaturday.com/149/schedule.aspx

Hopefully you can make it, there is a great lineup and a great opportunity to network with the SQL Family:)

Using PowerPivot Model Images with Excel 2013 Power View

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

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.

Database Images

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.

Conclusion

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:)