While reading another blog post this morning I was reminded of the super helpful metadata function that is OBJECTPROPERTY.  I often have to stop and remind myself that while all the information raised by OBJECTPROPERTY is available directly through the catalog views it is in many cases simpler to deal directly with the function.  For example, solving the blogger’s original question of objects without indexes can be accomplished in a simple query:

We can extend our search to find all objects without an index, views and tables alike.

There is a wealth of information available to you via OBJECTPROPERTY.  What metadata queries could you simplify today?

With all the current fuss about PowerPivot it is easy to overlook one of the other hot technologies coming our way, Pivot Viewer.  The Pivot Viewer is a Silverlight data visualization tool for rendering large amounts of data in a more meaningful way.  Today’s post is about showing the relative ease of putting together a quick demo of Pivot Viewer.

For this demo you will need a few items – Excel 2010, the Pivot Viewer browser, and the Pivot Collection Tool for Excel.  The Excel add-in can be downloaded here.  Additionally, to view the collections you will want to download the Pivot Viewer browser here.  Windows Vista or Windows 7 are required to install the browser.

Once you have installed the add-in, open Excel and go to the “Pivot Collections” tab in the ribbon.

Choose “New Collection” from the ribbon and a new workbook will be created with two tabs, Collection Items and Collection Properties.  Collection Properties sets the basic title and imaging of the collection, for use in the Collection Gallery later.

The real meat of the demo is in the Collection Items tab.  Here we will choose our images and add descriptive data.  You can import photos via the “Import Images” or “Choose Image” button of the ribbon.  Obviously, for larger collections the import will be the better path.

For demo sake today I simply imported a couple images from my Photos library.  This adds  rows to my worksheet where I can add a name and description.  Additionally, new columns can be added which will be included in the collection.  I created the following rows in my workbook:

At this point I have a basic collection with two pieces of metadata that can be seen by the user (Date Taken and Description).  If I select “Preview Collection” I can get a sense of how the collection will look for the user, very similar to the preview available in Visual Studio with SSRS reports.

Let’s have a look at this collection by choosing “Publish Collection”.  I will be prompted to save the collection somewhere and then the Pivot Viewer browser opens.  I can then navigate around in my collection.  You will notice the slicers on the left are populated with the “Date Taken” column details.

A very simple example I realize, I mainly wanted to show how quickly this can be created with the the tooling provided.  It would be quite simple to build a compelling demo for various groups within your organization, giving them a taste of the power of this tool.

I have been following a Connect item about Visual Studio 2010 and the lack of support for BI project types.  This recent comment from Jeff Bernhardt, Product Unit Manager for SSIS caught my eye today (emphasis mine):

First of all, thank you to those customers who have offered feedback regarding how your work and productivity are impacted by Integration Services (SSIS) not yet being available in Visual Studio 2010. As developers ourselves, we certainly understand the frustration you have reported in having two Visual Studio instances installed and maintained on your development machines. While this side-by-side configuration works, it is certainly a less ideal solution to operating SSIS from within VS 2010. Unfortunately, the staggered ship cycles of the two products (VS and SSIS) and some convoluted dependencies did not allow us to reunite the development environments of both tools until the next version of SQL Server.
Meanwhile, please be assured that SSIS projects continue to be supported by Visual Studio both now and into the foreseeable future. Until the next version of SQL Server, we will be happy to discuss with any Visual Studio 2010 customer ways in which they can optimize the interoperability of VS and SSIS. We apologize for any inconvenience and are committed to improving your user experience both now and in the future.
Jeff Bernhardt
Product Unit Manager
SQL Server Integration Services

All I can say is holy smokes.  The "current" version of SQL Server RTMed a week or so ago.  The delivery date of a "next" version is at least a year away, in a best case scenario.  Leaving developers out in the cold for this length of time will seriously impact the business discussions around BI projects.  I am at a loss over this decision.

When you add in the fact that little if anything was added to the SSIS product in SQL Server 2008 R2 it makes you question the dedication it enjoys within the product team.

I know from personal experience that Connect items are heavily monitored so if this also strikes you as something that needs to change make sure you vote (and get others to do so as well).  It sounds like a grassroots effort will need to take place for the decision to change.

Bloomberg has a review of SQL Server 2008 R2 that can provide a great resource to anyone trying to get an idea of what is included in the new release or sell it as an upgrade within their organization.

Slide deck from today's session below.  Thanks for the great interaction!

SQLSaturday_MDS.pptx (1.47 mb)

Thanks to everyone that came to my session on DVMs.  You guys asked some great questions!  Slides and code samples attached below.

SQLSaturday_Apr24_DMV.zip (1.29 mb)

A little late with this one but the SQL CAT team has a great blog post about partition lock escalation in SQL Server 2008. 

The post describes the introduction of table level lock escalation control made available via TSQL syntax.  In prior versions a trace flag was used to alter lock escalation.

A great feature added to an already great feature.  Enjoy!

I was in a conversation this morning with a couple other SQL guys (Twitter again) and the subject of Dynamic Management Views (DMVs) came up in regards to SQL Azure.  Running through the MSDN details it did not seem that they were supported.  After a couple emails with the SQL Azure team I have been given assurances that they will be added in a few months. 

The DMVs that will be available are only those which have database scope.  This is another great step in making SQL Azure a very viable alternative to local hardware.

My list of items in Program Files (x86) keeps shrinking and thank goodness for that.  The continued drive to 64 bit is joined by SSMS and is available here.  This build also supports SQL Azure if you need additional sales pitch.

The OSLO story just continues to amaze and perplex me.  I am a bit late with the reference but Doug Purdy posted on the 10th about the next transformation of OSLO, now being called SQL Server Modeling.  What should not be missed are the roughly 60 comments on this post - many of those taking an adversarial feel.

While a part of me enjoys the existence of SQL Server continuing I was really hoping to see some sort of transcendent technology coming our way.  I suppose the PDC conference next week could still have some big "wow" moment but that now seems less and less likely.

I would love to hear from you, dear reader...what do you think about this change in name (and likely direction)?