Category Archives: Design

PivotViewer Shorts Part 4: Restyle the ZoomSlider child controls

Introduction
By default the SL4 PivotViewer control displays a custom control for controling the zoom-level of the tile views. A few people have expressed a wish to totally reskin the control and this is a step along that path that hints at the technique needed to dig deeper into the PivotViewer visual tree.
 
This short post shows how to locate the UI element for the control bar, the ZoomSlider, all its visual elements and then alter their background.
 
PivotViewer ZoomSlider Re-Styling

PivotViewer ZoomSlider Re-Styling

 

Locating the UI Elements

The flow of the code goes something like this:

  • Locate the “PART_Container” in the tree. This is a Grid control.
  • Or… go direct to the “PART_ControlBar” element.
  • Locate the ControlBarView to access the child controls.
  • Build a list of child controls using VisualTreeHelper.
// Locate UI Elements
    ControlBarView ControlBar = (ControlBarView)this.GetTemplateChild(“PART_ControlBar”);
    Grid ctrlBarContent = (Grid)ControlBar.Content;
    DockPanel ctrlBarDockPanel = (DockPanel)ctrlBarContent.Children[2];
    ContentControl ZoomSlider = (ContentControl)ctrlBarDockPanel.Children[2];
    ContentControl cc1 = (ContentControl)ZoomSlider.Content;
//

// Build a list of the child controls
    var ctrls = GetChildsRecursive(cc1);
//

// Pick out controls and re-style as you wish 
    foreach (UIElement ctrl in ctrls)
    {
        string s = ctrl.GetType().ToString();
        switch (s)
        {
            case “System.Windows.Controls.Button”:
            Button b = (Button)ctrl;
            b.Background = new SolidColorBrush(Colors.Orange);
            break;
        case “Microsoft.Pivot.Internal.Controls.ClickDragSlider”:
            ClickDragSlider cds3 = (ClickDragSlider)ctrl;
            cds3.Background = new SolidColorBrush(Colors.Green);
            break;
        default:
            break;
        }
    }
;

// Helper function for child controls
IEnumerable<DependencyObject> GetChildsRecursive(DependencyObject root)
{
    List<DependencyObject> elts = new List<DependencyObject>();
    elts.Add(root);
    for (int i = 0; i < VisualTreeHelper.GetChildrenCount(root); i++)
        elts.AddRange(GetChildsRecursive(VisualTreeHelper.GetChild(root, i)));
    return elts;
}
+
FAQ
If you want to try out the code there are a few things to watch out for.
+
You will need some internal Microsoft Pivot references and usings too. Depending upon the extent of your other customizations you will need these:
+
using System.Windows.Pivot;
using Microsoft.Pivot.Internal.Controls;
using Microsoft.Pivot.Internal.Model;
using Microsoft.Pivot.Internal.ViewModels;
using Microsoft.Pivot.Internal.Views;
+
Further things to do
Conclusion
This article is a brief guide to locating some visual elements of the Silverlight PivotViewer control in order to customize the styling of the ZoomSlider control. It targets the developer who has already started customizing the Silverlight PivotViewer control beyond the public API.
 
Any comments or request for future topics are welcome.
 

Adventures with PivotViewer Part 10: Let’s do the Timelapse Warp

Introduction
Some data sets that have a datetime facet lend themselves nicely to data play back using time lapse. Sometimes playing back data like this can lead to insights that may have been missed through other visualization representations. Finding good examples is hard especially when you can argue that various trends or anomolies would also show up just as easily in more basic graph plots.
 
It would be nice if the PivotViewer filter panel was bound to data so that a timer could fire and animate the selected datetime range so automatically animating the selected PivotViewer items view in a time lapse. For now we can simulate this by manipulating the control’s ViewState and progressing the range for a target datetime facet.
 
Sample Collection Data
We decide to choose a large data set from the FAA (Federal Aviation Administration) in association with the NTSB (National Transportation Safety Board) . The sample collection covers aviation accident data from 2008 and 2009.
 
NTSB 2008 - 2009 Aviation Incidents by Severity

NTSB 2008 - 2009 Aviation Incidents by Severity

 FAA data for aviation incidents by severity for 2008 and 2009 to give you an overview. Incidents resulting in one or more fatalities are colored red.

NTSB 2008 - 2009 Aviation Incidents by Month

NTSB 2008 - 2009 Aviation Incidents by Month

FAA data for aviation incidents by month for 2008 and 2009 which shows the seasonal variation that peaks in August. Be careful not to misinterpret this as the overall volume of flights is not represented.

NTSB 2008 - 2009 Aviation Incidents by Day-of-Week

NTSB 2008 - 2009 Aviation Incidents by Day-of-Week

FAA data for aviation incidents by day-of-week for 2008 and 2009 which shows a modest peak on saturdays.

NTSB 2008 - 2009 Aviation Incidents for top States

NTSB 2008 - 2009 Aviation Incidents for top States

FAA data for aviation incidents by state for 2008 and 2009 which shows the states with most incidents: California, Texas, Florida, Arkansas, …

Timelapse of Incidents by State
I don’t know the real reasons for the observed trends though some are likely to be seasonal and others definitely related to holiday periods and holiday destinations resulting in increased traffic.
 
NTSB Aviation Incidents by State - Jan 1, 2008

NTSB Aviation Incidents by State - Jan 1, 2008

Starting in January 2008 the incidents are relatively light with the most in California and Florida.
NTSB Aviation Incidents by State - Jan 15, 2008

NTSB Aviation Incidents by State - Jan 15, 2008

For whatever reason the number of incidents pick up sharply for Texas as we move into early February 2008.
NTSB Aviation Incidents by State - Feb 5, 2008

NTSB Aviation Incidents by State - Feb 5, 2008

Throughout February 2008, the number of incidents increases overall but most markedly in Florida. Meanwhile the incident rate reduced in California.
NTSB Aviation Incidents by State - April 1, 2008
NTSB Aviation Incidents by State – April 1, 2008

Moving forward to April 2008 the distribution of incidents changes again with Texas ahead and Arkansas suffering more.

NTSB Aviation Incidents by State - Jun 24, 2008
NTSB Aviation Incidents by State – Jun 24, 2008

Moving forward to June 2008 the number of incidents across the states builds up and California is tops again but now followed by a peak in Illinois for what reason I do not know.

Here is a short video screen capture of a segment of the timelapsed playback.

FAA / NTSB Video Timelapse

Click to play clip of PivotViewer Timelapse

 There are a lot of additional facets to the full collections of FAA / NTSB data and many more trends and interesting visualization opportunities. Stay tuned to our blog…
 
Links 
Conclusion
This article is just a brief introduction to the possibility of playing PivotViewer collection data back as a timelapsed visualization opportunity. It is arguable whether or not this somewhat fashionable approach to visualization really gives a better insight than more traditional visualization paradigms. It all depends on whether there is really any trends in your data sets that are obscured when viewed as simple charts and graphs. Any comments or request for future topics are welcome.

Adventures with PivotViewer Part 9: Multi-Layered Trading Cards

Silverlight PivotViewer Layered Trading Cards

Silverlight PivotViewer Layered Trading Cards - click to view video

Introduction
Adding multi-layered trading cards is a feature enabled by the XAML data template trading card support for the version of the Silverlight PivotViewer control that will be distributed in the Silverlight 5 SDK. This article shows you how to implement a similar effect in the previous version of the control.

If you are new to our blog then you may care to take the time to work through the other posts in this series and build up you portfolio of PivotViewer customization skills.

Deep Zoom Collections and Images

Silverlight PivotViewer Product Catalogue collection

Silverlight PivotViewer Product Catalogue basic collection images

The easiest way to achieve this effect is to generate a full set of deep zoom collection imagery for each desired layer. Also generate full sets of deep zoom images for the desired layers of the trading cards.
 
Simply merge together the folders of the image layers according to your trading cards layer designs. By default the layers 0 to 7, equating to deep zoom folders 0 to 7, are used for the deep zoom collection imagery for the lower resolutions. Only the layers 8 and above are used at runtime by PivotViewer for the individual deep zoom images: folders 7 and below can be deleted and do not need deploying.
 
PivotViewer Low-Res Trading Card Images

PivotViewer Low-Res Trading Card Image transition

 
It is important that all source images for a trading card are the same height and width. The PivotViewer Excel tool is useful for trying this as you can simply copy the Excel spreadsheet and replace the trading card images.
 
I would suggest that you use common low resolution images for the zoomed out top layers of the deep zoom pyramid. For the primary theme choose a facet category with fewest values that can be represented by color. Choose images that represent a secondary attribute such as a category-type facet. By following this approach the users can quickly and easily work with the collection trading cards at low resolutions by visual association of color and category icon.
 

PivotViewer Hi-Res Trading Card Images

PivotViewer Hi-Res Trading Card Image Transitions

 
At the other end of the scale with the highest resolution images you probably will want more information, mini-graphs, etc. These images can be captured from a simple custom tool that uses a XAML data template bound to the collection data. If you do this you ought to be able to reuse your code with the vNext version of PivotViewer.
 
PivotViewer Trading Card

PivotViewer Trading Card Hi-Res from XAML

 
Further things to do
Conclusion
This article is just a brief run through that introduces multi-layered visualization of Silverlight PivotViewer control trading cards. It targets the developer who has already started customizing the Silverlight PivotViewer control in preparation for the next version scheduled to be distributed with the Silverlight 5 SDK. Any comments or request for future topics are welcome.

PivotViewer vNext in Silverlight 5 SDK

Original post is here: xpert360.wordpress.com
Only view on original blog to avoid malicious sites
[Last updated: 2011-05-19]


Advanced Features in Silverlight 5

MIX11 Agenda - Advanced Features in Silverlight 5

The Las Vegas MIX11 conference has come and gone. Windows Phone 7 “Mango”, Internet Explorer 10 platform preview, HTML 5, Silverlight 5 beta and Kinect SDK.
 
If you were interested in the next version of the Silverlight PivotViewer control you would be forgiven if you missed it as the MIX11 site had the “New Technologies for Immersive Content Creation” tagged as PivotViewer on April 14th 2011. The actual session of interest for the vNext PivotViewer was Nick Kramer giving a presentation entitled “Advanced Features in Silveright 5″ in the same session slot. The RN Studio (Alpha) session included using a PivotViewer collection asset, the 2009 IUCN Red List Species. This was interesting but nothing new as far as PivotViewer version.
 
New Technologies for Immersive Content CreationDate: April 14, 2011 from 3:00PM to 4:00PM | Day 3 | RES06 | Speakers: Eric Stollnitz, Joseph Joy
 
Advanced Features in Silverlight 5Date: April 14, 2011 from 3:00PM to 4:00PM | Day 3 | MED12 | Speakers: Nick Kramer
 
PivotViewer is flagged as coming soon post-beta as it did not make the cut for the Silverlight 5 SDK beta release. To live up to this status the PivotViewer control crashed with an access violation mid-demo as Nick looks on and adlibs.
 
PivotViewer vNext crashes at MIX11

PivotViewer vNext crashes at MIX11

 
“PivotViewer V1 was shipped in June 2010 separately from Silverlight, the V1 came in its own SDK download”. There was then a maintenance release in September 2010. “There was a lot of good feedback to Microsoft about the control but a lot of people said they wanted to take it to the next level”.
Nick said “We want to take that Pivot experience that lets you analyze large datasets in a very rich visual way, create interactive graphs, filter on the fly, figure out which part of the data is interesting to you.”
 
“We want to bring that to more scenarios make it easier to plug in your data”
  • Ships as part of the SL5 SDK
  • Dynamic client-based collections (ItemSource, binding)
  • XAML-based visuals (trading card templates)
  • Customizability (fonts, colors, sizes)
 
PivotViewer XAML templated tiles at MIX11

PivotViewer XAML templated tiles at MIX11

 
We first had a glimpse of the vNext control during the Silverlight Firestarter keynote in December 2010. Since then the sample demonstration has moved on to show more of the XAML template driven trading cards. Nick’s demonstration showed building trading cards with standard Silverlight DataTemplates and data binding to the Pivot collection. You can define one or more layouts which are automatically faded in/out by PivotViewer based on user-defined tile size thresholds (in pixels). So you see different trading card visuals based on the zoom level which was previously harder work to achieve with deep zoom image collections.
 
PivotViewer XAML templates at MIX11

PivotViewer XAML data templates at MIX11

 
The newly exposed client side collection feature is implemented using the usual ItemsSource property of the control. This brings generation and manipulation of your Pivot collections in line with other Silverlight controls.
 
Where does this leave us?
 
Ships as part of the SL5 SDK
  • So the PivotViewer vNext control now joins the SDK family of controls.
  • When will we get to download and use it? We just don’t know, officially just later in 2011.
  • Without a clear roadmap or detailed feature list this does leave existing development users in somewhat of an uncertain position.
  • In many ways there are far more questions than answers raised so far from the brief glimpses afforded to us.
 
Dynamic client-based collections
  • Dynamic data in SL5 will be available the same as any other data context, on the client, and is to be welcomed.
  • This has been interpreted by some as the end of CXML-based collections. It has not yet been said that the new dynamic data will replace CXML.
  • So will both old and new methods of supplying collections be available?
  • Will existing collections have an easy upgrade path?
  • We saw add-to-collection, are item data updates and item removal working too?
  • The answer appears to be yes to all these questions. The collection building has been separated from the visuals and existing CXML collections can be loaded as is. There are some issues with the visuals to be resolved.
 
XAML-based visuals
  • The new method, whilst highly applicable to line-of-business applications, is it suitable for photography collections?
  • Are Deep Zoom based CXML collection still supported?
    Define multiple visuals in XAML for different zoom levels, with this new structure how well does it scale?
  • How well does it perform with the image processing on the client?
  • Collection size was advised as up to 3,000 items, we squeezed acceptable performance with 10,000 items. with all the UI structures and memory use how well does this scale now on the client?
  • XAML DataTemplate trading cards, are they still ‘dumb’ bitmaps or allow UI interaction?
  • Do we still have support for PivotViewer ‘CustomActions’?
 
Customizability
  • New access to fonts, colors, sizes.
  • Any access to Backgrounds and Borders – filter panel, info panel, collection views?
  • Header customizations – hide collection path, filter criteria?
  • Customization of filter panels beyond fonts?
  • We have not yet seen custom info panel limits, how does this work?
  • Support for user-supplied XAML custom views? – view menu buttons, location for geolocation, maps, other graphical visualizations
 
 

Adventures with PivotViewer Part 6: Pimp it sample code

 

So let’s continue where the last post left off and finish the high-level pimping of the PivotViewer control…

If you want to follow along then you need to locate the PivotViewerSample code that shipped with the control. By default it is here for the August 2010 release:

C:\Program Files (x86)\Microsoft SDKs\Silverlight\v4.0\PivotViewer\Aug10\Source\PivotViewerSampleXapCode.zip

In the previous post the sample code showed you how to hide the left side Filters Panel and the right side Info Panel. It showed you also how to alter the background to help brand the control’s appearance.

The original code was a bit raw so I have put together a small demo application that wraps the functionality up into the custom PivotViewer class override. The sample allows you to toggle the Visibility of the tiles as well as the filters and information panels.

The custom properties can simply be used as in this sample:

...
if (PivotViewer.TileVisibility == Visibility.Visible)
{
PivotViewer.TileVisibility = Visibility.Collapsed;
}
else
{
PivotViewer.TileVisibility = Visibility.Visible;
}

C# Sample: Hide/Show the collection tiles

And the background can be set to ImageBrush like this:

...
// Main tile view background - ImageBrush
StreamResourceInfo sr = Application.GetResourceStream(new Uri("PimpThePivotViewer;component/Resources/Island.jpg", UriKind.Relative));
BitmapImage bmp = new BitmapImage();
bmp.SetSource(sr.Stream);
PivotViewer.GridBackground = new ImageBrush() { ImageSource = bmp };
sr.Stream.Close();

C# Sample: ImageBrush

The sample has a button to toggle the background between:

  • SolidColorBrush = Colors.Black
  • SolidColorBrush = Colors.Transparent
  • RadialGradientBrush
  • LinearGradientBrush
  • ImageBrush

    You can try out the sample Silverlight application here:
    Pimp-the-PivotViewer

    Here is the source code for you to play with, if you find it useful, let us know. We have extended the PivotViewer control in other ways to.

    Pimp-the-PivotViewer source code ZIP.

    Yoga Exercise collection with custom PivotViewer Ypga Theme applied

    PivotViewer Yoga Theme

  • Adventures with PivotViewer Part 5: Pimp my PivotViewer

    If you are not partial to a little code-behind then I would leave now as there is no blendability whatsoever to be found here that is for another day…

    And if you are here then you should be interested in these two items so I will give them a plug:

    So let’s pimp the control…

    Skinning is used to good effect to brand the PivotViewer control and integrate it into this GameMarx website An Xbox Live Indie Game search pivot.

    If you want to follow along then you need to locate the PivotViewerSample code that shipped with the control. By default it is here for the August 2010 release:

    C:\Program Files (x86)\Microsoft SDKs\Silverlight\v4.0\PivotViewer\Aug10\Source\PivotViewerSampleXapCode.zip

    To achieve a similar effect to the GameMarx website you need to set the background to black and hide the Filter Panel and the Info Panel:

    ...
    using System.Windows.Controls;
    using System.Windows.Pivot;
    using Microsoft.Pivot.Internal.Views;

    namespace PivotViewerSample
    {
    /// <summary>
    /// Override of PivotViewer
    /// </summary>
    public class CustomPivotViewer : PivotViewer
    {

    public override void OnApplyTemplate()
    {
    base.OnApplyTemplate();

    Grid partContainer = (Grid)this.GetTemplateChild("PART_Container");
    CollectionViewerView cvv = ((CollectionViewerView)(partContainer).Children[0]);
    Grid container = cvv.Content as Grid;
    Grid viewerGrid = container.Children[1] as Grid;

    // Background
    container.Background = new SolidColorBrush(Colors.Black);

    //
    // Filter Panel
    //
    // Hide it
    viewerGrid.Children[2].Visibility = System.Windows.Visibility.Collapsed;
    // Reuse the space occupied by the grid column
    viewerGrid.ColumnDefinitions[0].Width = GridLength.Auto;

    //
    // Info Panel
    //
    // Hide grid column that contains it
    viewerGrid.ColumnDefinitions[2].Width = new GridLength(0);

    }
    }
    }

    C# Sample: CustomPivotViewer

    If you prefer not to use your own filter UI controls then don’t hide the Filter Panel, ditto the Info Panel, but you all probably want the background changed at some point.

    Let us now see the effect of a LinearGradientBrush:
    PivotViewer background LinearGradientBrush

    ...
    // PivotViewer background
    GradientStopCollection gsc = new GradientStopCollection {
    new GradientStop { Color = Colors.Orange, Offset = 0.0 },
    new GradientStop { Color = Colors.Yellow, Offset = 0.5 },
    new GradientStop { Color = Colors.Green, Offset = 1.0 }
    };
    container.Background = new RadialGradientBrush(gsc) {
    Center = new Point(1.0, 1.0),
    GradientOrigin = new Point(1.0, 1.0),
    RadiusX = 1.0,
    RadiusY = 1.0
    };

    C# Sample: LinearGradientBrush

    And finally, but not least, for this blog post an ImageBrush:

    PivotViewer Desert Island Theme

    PivotViewer Desert Island Theme

    ...
    // PivotViewer background
    StreamResourceInfo sr = Application.GetResourceStream(new Uri("PivotViewerSample;component/Resources/bgrd.jpg", UriKind.Relative));
    BitmapImage bmp = new BitmapImage();
    bmp.SetSource(sr.Stream);
    container.Background = new ImageBrush() { ImageSource = bmp };
    sr.Stream.Close();

    C# Sample: ImageBrush

    There are other brushes, not least, the VideoBrush but it can start to be a bit overwhelming. Now let us see some cool PivotViewer applications out there!

    Using Table Value Functions to Replace ‘Get’ Stored Procedures?

    I learnt so many useful techniques, tricks and pointers at this SQLBits conference in York: tally tables, common table expressions (CTEs), cross applies, query tuning techniques, parallelism, data visualisation, to name but a few. However, the most useful technique I learnt was divulged in the first couple of hours during the training session given by Rob Farley.

    In amongst the banter, jokes and excursions, a la Geordie comedian Ross Noble, was a complete gem which will make a huge difference to our existing and future projects.

    Put simply, never use stored procedures for simple retrieves, but replace these with Inline Table-Value Functions (TVF).

    Given our love of stored procedures, this is a difficult pill to swallow at first, but the benefit can be seen through the following example…

    Suppose we have written a stored procedure that returns a dataset to the business layer of the application. In our case, we will have delivered the dataset via a WCF service.

    Our stored procedure might be something like:
    CREATE PROCEDURE dbo.usp_MyTable_Get (@FilterParms…)
    AS
    BEGIN
    SELECT T.Column1, T.Column2, T.Column3, LT.Column2, …
    FROM dbo.MyTable AS T
    INNER JOIN dbo.MyLookupTable AS LT ON LT.IDCol = T.Column4
    WHERE T.ColumnN = @FilterParm1

    END

    Now suppose that the application development team ask for an additional column to be included in the returned dataset. This may be because the dataset returns a foreign key identifier and the developers need to see the lookup description in the returned dataset. To return the lookup value we need to include an additional join on the lookup table.

    Before going ahead, we’d confirm that the developer hasn’t or can’t get the lookup description from content held in memory; we’d explain that we’ll need to create a new stored procedure just for this situation, or, we’ll need to include it in the existing one which will (unnecessarily) adversely impact performance for instances where the service is used but the additional column is not required.

    We have seen this situation many times and it is frustrating that an optimum one-size-fits-all solution hasn’t presented itself – until now…

    The solution is to replace the stored procedure with an inline TVF which includes every possible join for foreign key lookups and every possible output column, and then build up SQL SELECTs on this TVF with the required columns relevant to each individual situation, letting the Query Optimizer exclude joined tables where necessary.
    So, the above stored procedure is replaced with:

    WCF Service
    public List GetMyTable(dtoRequiredColumns, dtoParameters, dtoFilter)
    {
    List dtoOut = new List()
    string strSQL = ""

    foreach (col in dtoRequiredColumns)
    {
    if (col.IsRequired) strSQL += ", " + col.Name
    }

    strSQL = "SELECT" + strSQL.Substring(2) + " FROM dbo.tvfMyTableGet"

    // add any WHERE and ORDER BY clause

    SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(strSQL, SQLConnection)
    DataTable dtResult = new DataTable()
    SqlDataAdapter.Fill(dtResult)

    foreach (DataRow drRow in dtResult.Rows)
    {
    // add row to dtoOut
    }

    SqlDataAdapter.Dispose()

    return dtoOut
    }

    and the TVF definition is something like the following:

    CREATE FUNCTION dbo.tvfMyTable
    RETURNS TABLE
    RETURN
    SELECT T.every column…,
    LT1.everycolumn…,
    LT2.everycolumn…,
    LT3.everycolumn…,

    FROM dbo.MyTable AS T
    INNER JOIN dbo.MyLookupTable1 AS LT1
    ON LT1.IDColumn = T.ForeignKeyID1
    INNER JOIN dbo.MyLookupTable2 AS LT2
    ON LT1.IDColumn = T.ForeignKeyID2
    INNER JOIN dbo.MyLookupTable3 AS LT3
    ON LT1.IDColumn = T.ForeignKeyID3

    GO
    So the function includes every column in MyTable, and is joined to every table that provides a lookup for foreign keys.

    And here’s the clever thing, at run-time the optimizer looks at which columns are included in the SELECTed column list and excludes those JOINs on tables that are not required for that particular query. In addition, query plans are cached (however, note that different length string parameters will generate additional cached plans).

    The objections might go something like – underlying data is made more secure by placing these behind a stored procedure ‘firewall’. Of course, just as with stored procedures, we can grant execute permission to the function and not the underlying tables or views. And, this looks like dynamic SQL, which is bad! But, we are not allowing any injection to take place here – the listed columns are defined within the dtoRequiredColumns object and full control of the ‘dynamic SQL’ is maintained.

    Here at last is the one-size-fits-all solution, so thanks go to Rob Farley for this.

    Read more from Rob Farley’s Blog here .

    What Happened to Database Design?

    Database design seems to take a much lower priority on a system architecture than it used to, and I can understand why…

    These are my rather generalised stakeholder categorisations based on personal industry experience;

    Customers are not interested in the boring table schema demo or SQL scripts in Agile projects, and who can blame them, why would they be when there is a flashy UI to look at when it come round to sprint reviews?

    Project managers are not interested as it is not a demonstrable feature. How far does the statement ‘ we have developed a fully normalised and performance tuned database for you’ really go with a customer. The customer generally wants a fully functional system that looks good that they can show their manager. Main interest factors are cost and time overruns.

    UI developers refer to it as a repository and don’t care what it looks like, as far as they are concerned it can be a stack of XML files. In actual fact, these days they only care about the service layer as the database is abstracted from their layer.

    Service layer developers are generally happy if they don’t have to join one table to another table. So completely de-normalised tables can be quite common when developed by WCF developers. In fact if tables are completely de-normalised they rarely need to address joining tables or addressing foreign keys.

    The only parties actually interested in a good database design are the database developer as they realise that they can write SQL that really performs, and the DBA as they can see the benefits of a scaleable system that is unlikely to bloat over time.

    One thing we’ve noticed is that with all the layers involved in even medium-sized systems these days, most developers tend to specialise in either one or two of the layers.  It’s tricky to find a SQL developer/DBA with good UI knowledge and vice versa. Most people pick the closest tier to their speciality and learn a little bit more about how it interacts with their own tier. This makes them more marketable and allows them to fully specialise with their own technology.

    I’ve interviewed a lot of candidates over the last decade and there is a definite shift away from the data layer. I can see why…

    • Good database architecture can be complex.
    • Experience counts when normalising a database correctly.
    • Understanding set theory is a must for good SQL.
    • SQL itself is a fairly large language these days.
    • UI work is much, much sexier !

    We aren’t the only ones to have noticed this shift. Software Tools are starting to come out to address the lack of knowledge. Entity framework now has the option to ‘code first’ and generate a database layer from the service layer.

    Whilst it’s great that there are tools and support out there for the lonely UI developer struggling to create a demo, and has little database knowledge. I get very disappointed about the number of these prototype systems which make it into production and are left for the DBA to sort out when performance starts to suck.  It brings back memories of little MS Access database systems that got upgraded to SQL Server and shoved into a full business use. They generally grew and grew, degraded and degraded to the point of complete failure.

    So what’s the answer?  I wish I knew.

    There is an excellent and diverse SQL Community out there willing to pass on knowledge; PASS, SQLBits, SQLServerCentral, SQLServerClub,  and I still only look for recruits that have some decent database skills in their portfolio. I firmly believe that a database is the most important foundation in a system architecture.

    But perhaps part of the problem is that creating a good data layer is that it is time consuming and can be costly when done correctly. If we can all find ways of building standards and perhaps abstracting the table design changes using template stored procedures then we may at least get the Service Layer guys more interested!

    Until then spare a thought for where the application is going to end up. Don’t throw your ‘code first’ database at your Production DBA and run, try spending some time up front talking to them and actually try to design a database first. You will be much more marketable in future, although probably a lot less interesting to the rest of the project team…!