Silverlight Pivot Viewer – Automatic Pivot Collection Generation via Customized Pauthor tool
The Microsoft Silverlight Pivot Viewer is a new innovative way to look at a massive amount of data in a fast visually comparative way. PivotViewer utilizes Microsoft’s Deep Zoom technology which enables it to take advantage of high resolution imagery without taking a hit on performance.
Lately, we have been utilizing PivotViewer for Business Intelligence applications. If you are using PivotViewer to simply navigate and categorize images, the images themselves aren’t very important. However, if you are intending to utilize PivotViewer for a BI application, you will want to communicate the business information visually, that is the point after all, isn’t it? There are several ways to generate PivotViewer collections. The pivot collection creator for excel (here) provides a fast, simple way to generate PivotViewer collections. The main drawback of the excel tool is that the images for each item must already exist, plus this is still a very manual process. This is where the Pauthor tool comes into play.
The Pauthor tool is an open source command line tool for generating pivot collections from various formats, such as excel, csv, and cxml formats. Pauthor allows for the generation of pivot images from an html template, as well as the creation of new data sources and target sources that implement an OLE DB driver. We created a SQL Server datasource based on the OleDBCollectionSource. This allowed us to use tables, or views, or even stored procedures to generate the pivot collection facet categories, items, and collection text.
public class SqlCollectionSource : OleDbCollectionSource
{
/// <summary>
/// Creates a new Excel collection source and sets its <see cref="BasePath"/>.
/// </summary>
/// <param name="basePath">the path to the Excel file containing the collection's data</param>
public SqlCollectionSource(String serverName, String dbName, String collectionTableName, String facetTableName, String itemTableName)
: base(String.Format(ConnectionStringTemplate, serverName, dbName), ".")
{
m_serverName = serverName.ToLowerInvariant();
m_dbName = dbName.ToLowerInvariant();
m_collectionTableName = collectionTableName.ToLowerInvariant();
m_facetTableName = facetTableName.ToLowerInvariant();
m_itemTableName = itemTableName.ToLowerInvariant();
}
protected override void LoadHeaderData()
{
this.ConnectionString = String.Format(ConnectionStringTemplate, m_serverName, m_dbName);
this.UpdateDataQueries();
base.LoadHeaderData();
}
private void UpdateDataQueries()
{
//String connectionString = String.Format(ConnectionStringTemplate, this.BasePath);
using (OleDbConnection connection = new OleDbConnection(this.ConnectionString))
{
connection.Open();
DataTable schema = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
String firstTableName = null;
foreach (DataRow row in schema.Rows)
{
String table = row["Table_Name"].ToString();
String lowerTable = table.ToLowerInvariant();
if (lowerTable == m_collectionTableName)
{
this.CollectionDataQuery = String.Format(CommandTemplate, table);
}
if (lowerTable == m_facetTableName)
{
this.FacetCategoriesDataQuery = String.Format(SortedCommandTemplate, table);
}
if (lowerTable == m_itemTableName)
{
this.ItemsDataQuery = String.Format(SprocTemplate, table);
}
if (firstTableName == null) firstTableName = table;
}
schema = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Procedures, new Object[] { null, null, m_itemTableName, null });
foreach (DataRow row in schema.Rows)
{
String table = row["Procedure_Name"].ToString();
table = table.Remove(table.IndexOf(';'));
String lowerTable = table.ToLowerInvariant();
if (lowerTable == m_itemTableName)
{
this.ItemsDataQuery = String.Format(SprocTemplate, table);
}
}
if (this.ItemsDataQuery == null)
{
this.ItemsDataQuery = String.Format(CommandTemplate, firstTableName);
}
}
}
private const String ConnectionStringTemplate = "Provider=SQLNCLI10; Server={0};" +
"Database={1}; Trusted_Connection=yes;";
private const String CommandTemplate = "SELECT * FROM [{0}]";
private const String SortedCommandTemplate = "SELECT * FROM [{0}] order by sort";
private const String SprocTemplate = "exec [{0}]";
private String m_serverName = string.Empty;
private String m_dbName = string.Empty;
private String m_collectionTableName = string.Empty;
private String m_facetTableName = string.Empty;
private string m_itemTableName = string.Empty;
}
Customizing the HTML template functionality
The base HTML template functionality allows you to specify an html file incorporating meta tags like {name} {href} and any other custom pivot facet categories your items use within the html itself. Pauthor then iterates through the collection item datasource and creates a new html file for each one by replacing all of these tags with the item data source values. A web browser control is then run within the code to load up the newly generated html file and save an image out to the file system. Deep zoom images are then created for the pivot collection based off of this saved custom image as well as the xml of the pivot collection itself. We also customized the html template functionality allowing for a template query string to be used.
Example: /html-url http://www.example.com/PivotCard?itemPrimaryKey={itemPrimaryKey}
This immediately increases the flexibility of the images we create, as we can now generate any html we want using standard web technology based on the values we receive off of the query string as opposed to scattering meta tags in an html file. Managing a web page which generates these Pivot Images is much easier to maintain than trying to generate images manually using a graphics library. You can imagine the time involved in changing an image template, moving lines around, drawing text with wrapping capabilities, etc, each time your Pivot Card format changes.
Pivot Collection Generation
Pivot collection generation is now a breeze! There are no longer any manual steps with having our data in a database and images automatically generated and saved on the web server which serves up the pivot collection. Collection generation can now be kicked off with a nightly scheduled task or even more frequently if needed. We are currently working on creating a means of generating pivot cards on a one off basis so that the collection can be maintained in near real time without incurring the cost of generating the entire collection each time an item changes. Stay tuned!
Affirma Consulting Blog 