Posts Tagged 'PivotViewer'

SVG templates for data bound graphics

One of the most frequent questions I get about the HTML5 PivotViewer is how to create data bound HTML templates, styled with CSS. Invariably the answer is that it cannot be done due to the nature of the HTML5 canvas element, however there is an alternative.

Early on when building the HTML5 PivotViewer I was faced with a technical decision of how I was going to display and animate up to several thousand images at once without affecting performance – especially on a mobile device. Ultimately there were three choices either plain ol’ HTML like Isotope, SVG based like D3 or the canvas. In the end I chose the canvas which provided the best performance and support for raster images (Deep Zoom for backwards compatibility and large image support).

While the canvas element was the best choice it presents a problem of how to easily create and style a template that can then be converted into a raster image using only JavaScript. The solution I’m going to demonstrate is to use SVG.

What is SVG?

If you’re not familiar with SVG it’s a vector image format that is defined with XML. This means that it’s possible to create vector images with a just a text editor! If you don’t find generating images from text very intuitive there are also plenty of tools available for creating SVG images, I’d recommend Inkscape as a solid and free choice.

One of the huge benefits of using SVG is that it’s just XML and therefore  possible generate an SVG image using code. With a little jQuery it’s as simple as:

$('#svgContainer').append(
  '<svg xmlns="http://www.w3.org/2000/svg">' + 
  '<circle cx="50" cy="50" r="25" stroke="none" />' +
  '</svg>'
);

SVG templates with ICanHaz.js

While this is ok for simple examples, creating elements using string concatenation is messy and difficult to maintain. Instead I like to use a little library called ICanHaz.js which uses Mustache syntax for data binding to a template. I’ve been using ICanHaz.js for a while now to create templates for all the PivotViewer UI. It provides a much cleaner separation of the UI from the code and has a simple syntax. To demonstrate I’ve got to two examples.

For the first example I’m going to create a simple SVG chart with which I can bind my chart data. The SVG template with Mustache tags is below:

<script type="text/html" id="svgTemplate">
  <svg xmlns="http://www.w3.org/2000/svg">
    <g id="chart">
      <path d="m10 10 V10 210 H10 210" stroke="#000" stroke-width="1px" fill="none"/>
      <text x="220" y="20" fill="black">Legend</text>
      {{#series}}
      <text x="220" y="{{ypos}}" fill="black">{{name}}</text>
      <rect x="280" y="{{ypos}}" transform="translate(0,-13)" width="15" height="15" fill="{{colour}}" />
      {{/series}}
    </g>
    <g id="data">
      {{#series}}
      <g id="series-{{name}}" fill="{{colour}}">
        {{#data}}
        <circle cx="{{xAxis}}" cy="{{yAxis}}" r="2" stroke="none" />
        {{/data}}
      </g>
      {{/series}}
    </g>
  <svg>
</script>

Here I’ve got the SVG template wrapped in a SCRIPT tag with a type of text/html. There are a few SVG elements but the real power is the extra Mustache tags. There are two key tag types in the example, the {{#data}} and {{/data}} define a repeating section and the {{xAxis}} and {{yAxis}} tags correspond to properties in my data set.

Now that I’ve defined the temaplte I can then use ICanHaz.js to grab the template and apply the data in the chartData object.

$(document).ready(function() {
  var $container = $('#svgContainer');

  var chartData = {
    series: [
	  {
      name: 'Series 1',
      colour: 'red',
      ypos: 50,
      data: [
	    { xAxis: 125, yAxis: 60}, 
		{ xAxis: 15, yAxis: 177}, 
		{ xAxis: 33, yAxis: 105}]
      },
      {
      name: 'Series 2',
      colour: 'blue',
      ypos: 70,
      data: [
	    { xAxis: 44, yAxis: 60}, 
		{ xAxis: 66, yAxis: 77}, 
		{ xAxis: 130, yAxis: 130}]
      }
    ]
  };

  var template = ich.svgTemplate(chartData);
  $container.append(template);
});

The chartData object contains two series, each containing an array called data (remember {{#data}} and {{/data}}) that contains each of the points xAxis and yAxis. In the code above the key line is:

var template = ich.svgTemplate(chartData);

Which is where the chartData is bound to the svgTemplate. If we run the example in a browser we should see the following:

SVG Chart

While this is a simple example, and the chart still needs a little work before I would be happy to put it into produciton, it does demonstrate the potential power and speed of templating with SVG.

SVG templates and the canvas

Once we’ve got a data bound template the next step is to convert the XML in the SVG image into canvas draw methods. This time I’m going to rely on the excellent 3rd party library canvg to do the heavy lifting. It’s constructor accepts the SVG and the id of a canvas element. It then parses the SVG and draws it onto canvas – simple!

The following SVG template is a simplified version of the PASS Summit 2012 tiles I’ve created in a previous collection, without the speaker images.

<svg xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="grad1" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" stop-color="#C3381B" />
      <stop offset="14%" stop-color="#F68D29" />
      <stop offset="28%" stop-color="#E1B524" />
      <stop offset="42%" stop-color="#87A63F" />
      <stop offset="56%" stop-color="#2A854F" />
      <stop offset="70%" stop-color="#266B8F" />
      <stop offset="100%" stop-color="#1C4161" />
    </linearGradient>
  </defs>
  <rect x="0" y="0" rx="10" ry="10" width="256" height="256" stroke="none" fill="{{Category}}" />
  <rect x="16" y="16" rx="10" ry="10" width="224" height="224" stroke="none" fill="#000" />
  <rect x="24" y="168" rx="10" ry="10" width="208" height="64" stroke="none" fill="#fff" />
  <circle cx="128" cy="100" r="40" stroke="url(#grad1)" stroke-width="30" fill="none"/>
  {{#Title}}
  <text x="30" y="{{ypos}}" style="font-family: arial; font-size: 11px;">{{Text}}</text>
  {{/Title}}
</svg>

I can then dynamically create a canvas element, use canvg to apply the data bound SVG to the canvas, cache the canvas element in an array and then remove the canvas from the DOM. In this example I’m using the JavaScript implementation of Mustache as I don’t need all the functionality that ICanHaz.js provides.

if (!this._items[item.Img]) {
  var borderColour = GetColour(item.Facets["Category"][0].Value);
  //attach a canvas to the DOM
  $('#pivotviewer')
    .after("<canvas id='" + item.Id + "' width='256' height='256'></canvas>");
  var data = {
    Category: borderColour,
	//TODO: implment word breaker.
    Title: [ { Text: item.Name, ypos: 190 } ]
  };
  //use Mustache to bind the data to the template
  var databound = Mustache.render(this.template, data);
  //use canvg to convert the SVG to canvas methods
  canvg(item.Id, databound);
  //cache canvas
  var canvas = $('#' + item.Id);
  this._items[item.Img] = canvas[0];
  canvas.remove(); //once cached remove it from the DOM
}

BadReports You can see it all in action here: http://pivot.lobsterpot.com.au/json.htm. this collection is based on a JSON data source, with the newly added Spatial type (see Locations for an interactive map).

LobsterPot HTML5 PivotViewer – now Open Source!

Two months ago I posted about a project that I’ve been working on during down time here at LobsterPot, a port of the Silverlight PivotViewer control that has been built exclusively on web technologies – HTML5 and JavaScript. If you’re not familiar with PivotViewer it is a visualisation tool that I’ve always felt never got the attention it deserved.

So I put an early version out there to see what people thought – not expecting much. Well I can honestly say that the response has been overwhelmingly positive, I’ve been inundated with requests to finish it off as people were exited to build collections with their own data.

So I’m pleased to announce that the LobsterPot HTML5 PivotViewer is now an Open Source project hosted on CodePlex. You can find it here: http://lobsterpothtml5pv.codeplex.com.

The control is still very much a work in progress and there are still pieces of functionality that is missing. I’ll be updating the documentation over the next few days and the plan is to continue work on the control so that it can render static CXML based collections as well as its Silverlight counterpart.

If you’ve got an existing CXML based collection then please download the source and let me know how well it does/doesn’t work as well as if there are any bugs or functionality that is missing. The LobsterPot HTML5 PivotViewer has been built as a jQuery plugin with extensibility in mind. I’ll be posting more about ways that the control can be enhanced, including how get started extending it to work with other data sources.

Going forward the plan is to have two versions of the control: The open source version that will support static CXML based collections and a paid version that will be enhanced with dynamic collections, tile templates and additional views for mapping, data grids and charts. If you’re interested in having LobsterPot build a collection for you please contact us.

 

 

Picking MaxWidth for PivotViewer Semantic Zoom

The benefit of creating an implementation of PivotViewer and Deep Zoom from scratch is that you get a better understanding for how it all works under the covers. (If you haven’t had a look at the HTML5 PivotViewer you can check it out here - http://pivot.lobsterpot.com.au/html5.htm)

The Silverlight 5 version of the PivotViewer control brings a whole heap of long awaited features including dynamic collections, item templates and Semantic Zoom. I’ve been playing around with item templates a bit lately as I prepare for my SQL Saturday talks later this month in Brisbane, Wellington and Adelaide and I thought I would go into a bit more detail on picking the correct MaxWidth for a PivotViewerItemTemplate.

If your unfamiliar with the Silverlight 5 PivotViewer item templates can be defined in XAML and data bound to items the PivotViewer controls ItemsSource. The item templates can be further enhanced by specifying a value for the MaxWidth property – a value that tells the control when to render the item/tile based on the current tile size or zoom level. For example creating three item templates, one with minimum detail for when the collection first loads, one with a little more detail when only a few tiles are visible and a final one for when only one tile is displayed would look something like this:

<pivot:PivotViewerItemTemplate MaxWidth="150">
	<Border Width="30" Height="20">
		<TextBlock Text="150" />
	</Border>
</pivot:PivotViewerItemTemplate>
<pivot:PivotViewerItemTemplate MaxWidth="500">
	<Border Width="30" Height="20">
		<TextBlock Text="500" />
	</Border>
</pivot:PivotViewerItemTemplate>
<pivot:PivotViewerItemTemplate>
	<Border Width="30" Height="20">
		<TextBlock Text="> 500" />
	</Border>
</pivot:PivotViewerItemTemplate>

While this example is simplistic – it only displays some text at each level, it demonstrates how the MaxWidth property is used to specify when to display a tile. The first template will get used until the tile becomes greater than 150 pixels, the second will be visible until 500 pixels and the last will be visible for all width after that.

While choosing a value for MaxWidth seems straightforward enough, having a closer look behind the scenes reveals that there is a little bit more to picking the right value. When we talk about MaxWidth what we are really referring to is the level at which the template applies, and when I say level I mean a level in the context of a Deep Zoom pyramid (http://msdn.microsoft.com/en-us/library/cc645077).

Even though the Silverlight 5 PivotViewer does away with the Deep Zoom dzc and dzi files, behind the scenes Deep Zoom tiles are still being rendered client side by Silverlight. Deep Zoom works by splitting up an image into smaller pieces where the widths equal a power of 2.

Image pyramid used by Deep Zoom

In the context of PivotViewer the current level is determined by the current width of the tile rounded down to the nearest power of 2. For example if the tile is 256 pixels wide then it is at level 8 (2^8), and when it’s 300 pixels wide it also gets rounded down to level 8.

Note: The formula to work out the level based on the width is: Floor(Log(width)/Log(2)).

To put this to the test I created 4 tile templates with the MaxWidth set to 300, 500, 700 and > 700. The result was that only the tiles with a MaxWidth of 300, 700 and > 700 were actually rendered by PivotViewer – it was if the item template with the MaxWidth set to 500 was never defined.

Unfortunately the official documentation mentions nothing about this (or anything really: http://msdn.microsoft.com/en-us/library/system.windows.controls.pivot.pivotvieweritemtemplate.maxwidth(v=vs.95).aspx) but as the item templates get converted to Deep Zoom behind the scenes, the MaxWidth of an item template can really only be set to powers of 2.

Item counts in PivotViewer

As much as I like the PivotViewer control, displaying a total count of filtered items is a useful feature that does not exist in the current control, which is why a while ago I added it as part of the breadcrumb at http://pivot.lobsterpot.com.au.

Today I saw this post on the forums http://forums.silverlight.net/t/247694.aspx/1?Get+the+information+that+are+currently+on+the+filter+panel and it reminded me about a feature I never got around to finishing, but I thought I might post here. This function drills through all the controls to return the count displayed next the to facet item name:

///  /// Gets the current count of a facet item /// 
private int GetFacetItemCount(string facetName, string facetItemName)
{
	int count = 0;
	Grid partContainer = (Grid)this.GetTemplateChild("PART_Container");
	var GridFilter = ControlHelper.GetChildObject(partContainer, "FilterPaneRoot");
	var categories = ControlHelper.GetChildObject(GridFilter, "PART_CategoriesContainer");
	var accordionItems = ControlHelper.GetChildObjects(GridFilter);
	foreach (var accordionItem in accordionItems)
	{
		//facet name
		var categoryText = ControlHelper.GetChildObjects(accordionItem, "m_categoryName");
		foreach (var cTxt in categoryText)
		{
			var facetTextBlock = ControlHelper.GetChildObject(cTxt, "PART_MainTextBlock");
			if (facetTextBlock.Text == facetName)
			{
				var itemsControl = ControlHelper.GetChildObjects(accordionItem, "m_facetItemsControl");
				foreach (var item in itemsControl)
				{
					var btns = ControlHelper.GetChildObjects

Addressing the elephant in the room: The HTML5 PivotViewer

I’ve been working with the Silverlight PivotViewer control for quite a while now, and if you read this blog you’ll also know that I’ve had some success in customising it. Now don’t get me wrong, it’s a fantastic control, I love they way it allows you to quickly visualise relationships between items, but for a while now the elephant in the room has been lack of support for the iPad and like devices.

Now I’m not going to go into a rant on Silverlight as a technology, it’s future and what I think of the iPad or anything else – to be honest it’s a boring discussion. What I’m interested in is providing the best user experience that clearly presents the data in a way that users can make the most of. By creating applications that are based on web standards then I don’t have to worry about it. In fact recently Microsoft’s Internet Explorer team and Zeptolab partnered up to produce an excellent example of how well HTML5 games perform extremely well by creating a version of Cut the Rope – check it out at http://www.cuttherope.ie/.

So before I got any further I encourage you to check out the latest version of a project that I’ve been working on: the HTML5 PivotViewer. I’m calling this a beta and while functionally it’s incomplete compared to the Silverlight version, it easily demonstrates that modern browsers have the power to process and render data at near native speeds.

So while the HTML5 PivotViewer control is functionally incomplete, there is no trickery involved, as it is in fact reading a CXML file (the XML schema that the PivotViewer control consumes) and is rendering images from a DeepZoom collection (at this point in time I’m only displaying images at level 6 which is why they are a bit blurry).

As with any serious JavaScript development these days I’ve leaned heavily on the fantastic jQuery library – in fact the control has been built as a jQuery plugin which means creating an instance of the control is remakable simple:

<div id="pivotviewer" style="height:600px;"></div>
<script type="text/javascript">
	var debug = false;
	$(document).ready(function () {
		$('#pivotviewer').PivotViewer({ CXML: "/Collections/PASS Summit 2011.cxml" });
	});
</script>

If you’re not familiar with jQuery (or JavaScript), the key parameter to take note of here is calling the PivotViewer() function and setting the CXML property to the  location of the Pivot collection – and that’s all there is to it!

This project started out as a proof of concept to see if it could actually be done, and I’ve been continually surprised that not only can it be done, but that it performs extremely well – even on relatively low powered devices (like my ancient LG GW620).

So what’s next? I’ve got a whole heap of functionality that I’m going to be adding, including the Data and Map views that I’ve previously added to the Silverlight version of the control.

Let me know what you think, I’m open to suggestions on what features you’ve been missing in PivotViewer.

Customising PivotViewer: Adding custom views

This is the second post in a series on customising PivotViewer. In the last post I covered how to attach to the UI events, in this post I’ll  show the process of adding a custom view.

The key steps in creating a custom view is first adding the view change button to the control bar, second is adding the view control and finally hooking it all up.

The button for the view can be any UIElement type, for the Grid View on the LobsterPot PivotViewer I created a simple UserControl. Adding the new button can be done in the overloaded OnApplyTemplate() method by inserting it into the GridControlBar. I’ve created a mini example below that contains the key elements for adding the button – I’ve left out the definition for GridControlBar for brevity, to see how it’s defined have a look at the post on Events.

public class LobsterViewer : System.Windows.Pivot.PivotViewer
{
    private GridViewButton _pivotGridButton { get; set; }

    public LobsterViewer()
    {
        _pivotGridButton = new GridViewButton();
        // Click event
        _pivotGridButton.Click += new EventHandler(_pivotGridButton_Click);
    }

    public override void OnApplyTemplate()
    {
        base.OnApplyTemplate();
        var controlBarPanel = (DockPanel)VisualTreeHelper.GetChild(GridControlBar, 2);
        controlBarPanel.Children.Insert(1, _pivotGridButton);
    }

    ///
    /// Turn on Grid and deselect pivot view controls
    ///
    private void _pivotGridButton_Click(object sender, EventArgs e)
    {
        // Disable other views
        // Enable custom view
    }
}

The next step is to add the custom view control to the PivotViewer. This is done in exactly the same way as the button, it’s a simple UserControl and is inserted in the overloaded OnApplyTemplate method. Again I’ve left out the definition of PartContainer for brevity.

private PivotDataGrid _pivotGrid { get; set; }
public LobsterViewer()
{
    _pivotGrid = new PivotDataGrid();
}
public Grid GridViews
{
    get
    {
        return ((Grid)
                ((CollectionViewerView)(PartContainer)
                    .Children[0])
                    .Content)
                    .Children[0];
    }
}

public override void OnApplyTemplate()
{
    ...
    //Insert grid control
    _pivotGrid.Visibility = System.Windows.Visibility.Collapsed;
    _pivotGrid.SetValue(Grid.ColumnProperty, 1);
    _pivotGrid.SetValue(Grid.ColumnSpanProperty, 2);
    _pivotGrid.SetValue(Grid.RowProperty, 0);
    _pivotGrid.SetValue(Grid.RowSpanProperty, 1);
    GridViews.Children.Add(_pivotGrid);
}

It’s worth noting that I’ve set the initial visibility to Collapsed as I didn’t want it to be visible when then PivotViewer is first loaded.

The final step is to display the new view when the button is clicked and to hide the tiles. From the previous post we already have the view change events so it’s straight forward to set the visibility of the new view to Collapsed. Hiding the tiles can be done by getting the first child control in GridViews and setting its visibility to Collapsed.

///
/// Visibility of tiles
///
public Visibility TileVisibility
{
    get
    {
        return GridViews.Children[0].Visibility;
    }
}

///
/// Turn on Grid and deselect pivot view controls
///
private void _pivotGridButton_Click(object sender, EventArgs e)
{
    TileVisibility = System.Windows.Visibility.Collapsed;
    _pivotGrid.Visibility = System.Windows.Visibility.Visible;
}

That’s it for now, but please let me know if this is useful. Next post will be on adding a map control as a view and passing the latitude and longitude coordinates via the CXML.

Customising PivotViewer: Events

This is the first post in a series of posts that I hope to put together on customising the PivotViewer control, which will give an insight into how the LobsterPot pivot collections were created. This post will be focused on how to attach to the UI events that will be needed when adding a custom view to the PivotViewer control.

Before I get started I’d like to mention that while I have no affiliation with Silverlight Spy I recommend it highly and could not have done any of this easily without it. I’d also like to thank Xper360 (blog|twitter) and Tony Champion (blog|twitter) for their work in customising the PivotViewer control – It’s well worth looking at the series of posts by Xpert360 which provide an excellent start for customising the PivotViewer control. Also make sure to download the lessons provided by Tony Champion - http://pivotviewerlessons.codeplex.com/ which are also excellent.

One of the first challenges with customising the PivotViewer control is to get the right user events. Out of the box PivotViewer only exposes basic events, which means to get anything useful such as view change and filter we will have to create our own event handlers.

To add a custom view control the first event needed is to know when the views have been changed. This will then let us know when to show and hide the custom view. Lucky for us attaching to the view change events are fairly straight forward:

///
/// The main Grid
///
public Grid PartContainer
{
    get
    {
        return (Grid)this.GetTemplateChild("PART_Container");
    }
}

///
/// Top control bar containing the view controls, sort list and zoom controls
///
public ControlBarView ControlGrid
{
    get
    {
        return ControlHelper.GetChildObject<ControlBarView>(PartContainer, "PART_ControlBar");
    }
}

///
/// Top control bar grid
///
public Grid GridControlBar
{
    get
    {
        return (Grid)VisualTreeHelper.GetChild(ControlGrid, 0);
    }
}

///
/// Bind to view switch events
///
private void BindViewSwitcherEvent()
{
    // ViewSwitcher contains the two Grid and Graph controls
    var viewSwitcher = ControlHelper.GetChildObject<ContentControl>(GridControlBar, "ViewSwitcher");
    var viewControls = ControlHelper.GetChildObject<StackPanel>(viewSwitcher);
    foreach (var viewBtn in viewControls.Children)
    {
        // First remove any events that might have already been attached
        ((ListBoxItem)viewBtn).MouseLeftButtonUp -= new System.Windows.Input.MouseButtonEventHandler(viewSwitcher_MouseLeftButtonUp);
        ((ListBoxItem)viewBtn).MouseLeftButtonUp += new System.Windows.Input.MouseButtonEventHandler(viewSwitcher_MouseLeftButtonUp);
    }
}

The next step is to handle mouse events from the facet Category filters. What makes this slightly trickier is the facet category items are created and destroyed dynamically, which means we also have to attach and detach to the events dynamically.  To avoid potential memory leaks a stack is used to push current UI objects when attaching and pop them off again to detach and clean up. The BindAllAccordianButtons method below is called once the collection has been loaded, and every time the Facet Category filters change.

// Memory management
private Stack _uiObjects;
private Stack _uiThumbObjects;

///
/// Filter pane grid control
///
public Grid GridFilter
{
    get
    {
        return ControlHelper.GetChildObject(PartContainer, "FilterPaneRoot");
    }
}

///
/// Bind to click events for all visible accordion buttons
///
private void BindAllAccordionButtons()
{
    //cleanup old attached events
    while (_uiObjects.Count > 0)
    {
        _uiObjects.Pop().Click -= new RoutedEventHandler(fb_Checked);
    }
    while (_uiThumbObjects.Count > 0)
    {
        _uiThumbObjects.Pop().DragCompleted -= new DragCompletedEventHandler(t_DragCompleted);
    }
    // Get all CustomAccordionItems in the PART_CategoriesContainer
    var categories = ControlHelper.GetChildObject<StackPanel>(GridFilter, "PART_CategoriesContainer");
    var accordionItems = ControlHelper.GetChildObjects<CustomAccordionItem>(GridFilter);
    foreach (var accordionItem in accordionItems)
    {
        //clear buttons
        var clearButtons = ControlHelper.GetChildObjects<button>(accordionItem, "m_clearButton");
        foreach (var cBtn in clearButtons)
        {
            cBtn.Click -= new RoutedEventHandler(fb_Checked);
            cBtn.Click += new RoutedEventHandler(fb_Checked);
        }

        var filterButtonsWrapper = ControlHelper.GetChildObjects<ScrollContentPresenter>(accordionItem, "ScrollContentPresenter");
        foreach (var wrapper in filterButtonsWrapper)
        {
            var filterButtons = ControlHelper.GetChildObjects<button>(wrapper);
            var filterCheck = ControlHelper.GetChildObjects<ManualToggleButton>(wrapper);
            //attach click to buttons
            foreach (var fb in filterButtons)
            {
                _uiObjects.Push(fb);
                fb.Click += new RoutedEventHandler(fb_Checked);
            }
            //attach click to checkbox's
            foreach (var fc in filterCheck)
            {
                _uiObjects.Push(fc);
                fc.Click += new RoutedEventHandler(fb_Checked);
            }
        }
        //Range Slider
        var rangeSlider = ControlHelper.GetChildObjects<Thumb>(accordionItem);
        foreach (var t in rangeSlider)
        {
            _uiThumbObjects.Push(t);
            t.DragCompleted += new DragCompletedEventHandler(t_DragCompleted);
        }
    }
}

///
/// Collection filtered (via facet accordion button)
///
private void fb_Checked(object sender, RoutedEventArgs e)
{
    // Do something
}

///
/// Collection filtered (via range slider)
///
public void t_DragCompleted(object sender, DragCompletedEventArgs e)
{
    // Do something
}

I’ve put in some place holders for the event handlers, but this is where any custom actions would be placed for updating the UI based on the current filter.

I’ve got more posts on the way, but by applying this code into your extended PivotViewer class it will make adding custom views a whole lot easier.

Using SSIS to generate PivotViewer CXML from SSAS

I completely forgot but a few months ago I put together an SSIS package to generate CXML from a cube in a way very similar to how PivotViewer for Reporting Services generates its tiles – only without SharePoint.

When designing the SSIS package I really wanted to make it configurable so that it can be easily adapted to work from any cube source. Hence there are a few design decisions that may seem a little odd – more on that later.

My example cube is based on a blog site I scraped and so has some familiar dimensions – News and Terms (Tags) with the measures being a count of news items and terms. Below is how to package has been put together.

As you can see the solution is in two core steps. The first is to generate the DeepZoom images and the second is to create the CXML.

Generating the images requires the unique identifier of each item to appear in the collection, then passing that identifier as a parameter to a Reporting Services report to render the tile as an image. As my data is based on a cube I use the following query to get a list of the items:

SELECT [[News]].[News]].[News]].[MEMBER_UNIQUE_NAME]]] as [Items]
FROM OPENROWSET('MSOLAP',
	'DATASOURCE=.; Initial Catalog=BizCube;',
	'select
		non empty{[Measures].[News Count]} on 0,
		non empty{
			([News].[News].[News].members,
			[Terms].[Term].[Term].members)
		} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME on 1 from [Biz]')

As mentioned making it flexible means that I’ve had to make some design decisions, which means using the SQL OPENROWSET to query the cube from a SQL instance. The main reason for this is to have a consistent alias for the returned column – in this case [Items], which then means the data flow path does not need to change as it’s always expecting a column called [Items]. The result of the query gives me results in the form “[News].[News].&[395]” where the identifier can easily be extracted. I then have a script task to query the report passing the identifier as a parameter and specifying the report be rendered as a png which can then be downloaded. Once downloaded the image is then fed into the DeepZoom DZConvert.exe tool to create the tile.

The next step is to create the CXML. This is done by querying the cube again but this time returning all the facets that I want to use in the Pivot collection. The query looks like this:

select 'Key:key|Title:String|Caption:String|Url:link|Term:String|Term Count:Number' as [Items] union all
select
	cast([[News]].[News]].[News]].[MEMBER_UNIQUE_NAME]]] as varchar(255)) + '|' +
	cast([[News]].[News]].[News]].[MEMBER_CAPTION]]] as varchar(255)) + '|' +
	cast([[News]].[Caption]].[Caption]].[MEMBER_CAPTION]]] as varchar(255)) + '|'+
	cast([[News]].[Post Url]].[Post Url]].[MEMBER_CAPTION]]] as varchar(255)) + '|'+
	cast([[Terms]].[Term]].[Term]].[MEMBER_CAPTION]]] as varchar(255)) + '|'+
	cast([[Measures]].[News Terms Count]]] as varchar(255)) + '|' as [Items]
from OPENROWSET('MSOLAP', 'DATASOURCE=.; Initial Catalog=BizCube;',
	'select non empty{
		[Measures].[News Terms Count]
	} on 0,
	non empty{(
		[News].[News].[News].members,
		[News].[Caption].[Caption].members,
		[News].[Post Url].[Post Url].members,
		[Terms].[Term].[Term].members
	)}
	DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_KEY on 1 from [Biz]')

The query consists of two parts, the first creates a ‘|’ delimited list of key/value pairs of the facet name and its type – this is used to construct the FacetCategories section of the CXML. The second part queries the cube for the corresponding FacetCategory values and delimits them with a ‘|’. Again like the previous query it’s done using OPENROWSET in order to alias the column as [Items].

The result of the query is then passed to a script task where the CXML is generated. To make it even easier the both queries are generated via variables, so changing cube sources should only be a matter of updating the values and hitting execute!

The solution can be downloaded from here (37KB zip) so please let me know what you think!

Also stay tuned as I’m planning on putting together a series of posts on how we built pivot.lobsterpot.com.au (finally!)

Silverlight 5 and PivotViewer

I’m a little late in writing this, but at MIX11 Microsoft announced that Silverlight 5 – the next version of Silverlight will also contain the next version of PivotViewer out of the box!

Nick Kramer gave an overview and demo of the features in his session on Advanced Features in Silverlight 5 (The PivotViewer part is at 36:45)

While the basic functionality remains the same there are some impressive new features that will make using the PivotViewer control much better to use. The most notable feature is the ability to databind to client based collections, while I’m unsure if cxml is dead (not that I’ll miss it), but data binding will bring true dynamic collections without having to worry about creating custom HttpHandlers or Mvc.ActionResults to serve up CXML.

Another great addition is the ability to create XAML based tiles instead of images and in addition the ability to specify different tiles at different zoom levels. This will be a huge bonus especially for adding additional item based data on a tile when zoomed in. Previously the way to do this was to use reporting services to either pre-render the tiles and generate a DeepZoom collection, or use PivotViewer for Reporting Services (a.k.a. PivotViewer for PowerPivot, SharePoint 2010 Enterprise and SQL Server 2008 R2 Enterprise)

Another welcome feature is the ability to get additional data for an item, giving the user the ability to drill-down even further into the data.

PivotViewer version 2

A tile at the maximum zoom level showing the Get Data button

While the Silverlight 5 beta is available for download unfortunately for us PivotViewer (Can I call it v2?) has not been included.

All in all the next version is looking fantastic and a definite step forward for PivotViewer.

Also stay tuned for my next post, I just remembered I put together an SSIS package for generating CXML and DZ Collections based on SSAS cubes and I forgot to post it!

Update: As promised my post on CXML from SSIS

Extending PivotViewer with Bing Maps

Lately I’ve been extending the PivotViewer control with extra functionality to see how far the PivotViewer experience can be taken. Most notably LobsterPot has launched the Pivot showcase which features the enhanced PivotViewer – I’ve blogged about this recently.

Today the next update to the PivotViewer has been released and it now features a Bing Maps view for any collection with geographic data. So far only the flickr collection has this feature, but were are planning more in the future. The map feature can be activated via the new map view control, see screenshot top right.

Silverlight PivotViewer with Bing Maps

At this point in time the mapping experience is pretty basic, but I’m planing to add smarter clustering around points that are close together, item details when clicked and to view a larger version of the image when zoomed in.

Next Page »


Roger Noble

Developer and Data Visualisation Consultant at Roger Noble Analytics
Follow me on Twitter
LinkedIn

Twitter