I highly recommend that companies take the time to develop a clear strategy and intelligent roadmap for mobility in the organization. An Enterprise Mobility Strategy defines the business needs, value proposition, and phased roadmap for addressing the major issues surrounding mobile computing.

Mobility issues include:

• Corporate policies and procedures for mobile computing, including defining a matrix of fully supported, partially supported, and non-supported platforms and devices.
• Standard productivity applications for mobile devices.
• Mobile development architecture, tools, and best practices.
• Mobile business intelligence capabilities.
• Mobile Device Management (MDM), including provisioning, management and support, security, and decommissioning of mobile
  devices.

An Enterprise Mobility Strategy is a critical first step to having a clearly defined, proactive plan for mobility in your  organization.  Anexinet’s Enterprise Mobility Strategy service is typically a four to six week engagement that delivers a comprehensive findings and recommendations document, including current state, gaps/risks, near-term recommendations, and an intelligently phased roadmap for mobility in your organization.

I met with an unexpected behavior in PowerShell’s count function. In SSIS, we needed to check if the contents of a folder were empty before running a process on the contents. The reason being, if the folder was empty, the process would throw an error. So very simply, we hooked up PowerShell to an execute process task, set the working directory to the location that we wanted to check the contents for and then mapped the StandardOutputVariable to capture the result (shown below). In the argument we put the following command:
(Get-Childitem).count -as [int]
Get-Childitem is PowerShell’s cmdlet for getting the contents of a parent item. Wrap that in a count and we have the number of files in the folder. Lastly, we convert that to an integer value.

Since this operation was generally operating on 5 to 10 files at a time or none at all, everything ran as expected. However, I tried reprocessing a single file the other day, only to find that PowerShell returned “0” for the count. After adding more files, I found that it suddenly started returning the correct count again. It seems the function above cannot count items when there is only one. After Googling around for a while, I found that if the Get-Childitem collection is converted to an array, PowerShell can return the correct count for a single item. This is done by adding an “@” sign to the beginning of the statement:
@(Get-Childitem).count -as [int]
Easy as 0, 2 ,3!

I recently had an odd request from a client who needed a query which will pivot data from a key – value table.  This is not a normal request in that reporting tools would normally deal with the pivoting of data in a dynamic way, rather than your database tools. Needless to say, this was needed to feed data to an application which was expecting variable columns.

The T-SQL below used the XML Path function to convert the columns to a CSV and then uses the PIVOT function (and dynamic sql) to return the values for each column.  Hope this is helpful to someone!

Create Table #tb (KeyColumn varchar(20), Value int);

INSERT INTO #tb
SELECT ‘Bodington’, 1
UNION All
SELECT ‘Budweiser’, 2
UNION ALl
SELECT ‘Petit_Syrah’, 3

declare
@sql as nvarchar(max),
@cols as nvarchar(max) = (SELECT
STUFF(
(
SELECT
‘,’ + KeyColumn
FROM #tb
FOR XML PATH(”)
), 1, 1, ”
) As concatenated_string)

SET @sql =

‘SELECT ‘+ @cols + ‘
FROM
(
SELECT
KeyColumn
,Value
FROM #tb
) AS tb1
PIVOT (
Max(Value) for KeyColumn IN (‘+ @cols + ‘)
) as pvt’
Exec sp_executesql @sql

DROP TABLE #tb

Recently, I developed a package which read the contents of an excel file and inserted rows into several SQL tables.  Note that development was on a 32 bit machine.  When I deployed the package to our 64 bit dev server, I received the following error:

SSIS
Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not
supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Todd McDermid offers good advice regarding this problem on the MS forums: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9/

In my case, I have a master package executing many other packages.  I did not want to have the whole master run in 32-bit mode, so I replaced my execute package task with an execute process task and then referenced the 32 bit DTEXEC.exe.  I then ran the package again, but received a new error:

[Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0×00000000.

An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0×80040154  Description: “Class not registered”.

Now, the issue was that MS Office was not installed on the machine.  Luckily, you can install the data providers from MS:

Microsoft Access Database Engine 2010 Redistributable
http://www.microsoft.com/download/en/details.aspx?id=13255
However, I installed the 64 bit provider and received the same error.  I then uninstalled the 64 bit, installed the 32 bit and Yahoo – it finally worked.

Final Answer – To read excel on a 64 bit machine in SSIS, you need to install the 32 bit data providers and execute the package from the 32 bit version of the dtexec.exe.

I recently learned about a feature in SSMS that I never paid much attention to – the ability to save queries in a solution. I normally organize my queries into folders in the project folder for short term use with the project that I am working on. However, I found that the solution method of storing queries could prove very useful for the ones that you use on a regular basis.
First off, this is what it looks like:

To access the solution panel, Go to view> Solution Explorer (or CTLR + ALT + L)
To add a project, right click on the solution explorer and add new project. Right click on the Connections folder to add a new connection (Use the options tab to specify a database. You can add a new query by double—licking the connection or by right clicking on the Queries folder.

Note that when you rename a query it must end in .sql or the file will be moved under the miscellaneous folder. To run a query, simply double click it and it will open it up in ssms.

This is a nice way to organize your queries and easily find them (rather than hunting through windows explorer). There is also source control support for this solution, though I have not tested that as of yet.

PhoneGap is an opensource project and Dan notified the discussion group that the article was there, and got some feedback from Simon Mac Donald.  Dan then decided to offer the code to the PhoneGap project when he realized that others were finding it useful.  Simon recommended some small naming changes and gave some general instructions on how to submit it.  

Look to see Dan’s work officially in the PhoneGap framework soon!

During the first half of the Tech Tuesday held at the Kennett Square Near Site on 9/13/2011, we took a look at the Microsoft LightSwitch Framework.  Offered as an extension to the Visual Studio 2010 IDE, LightSwitch is touted as a collection of starter kits, wizards, and templates that “help you create and easily release custom business apps that look professional and polished, with no coding required”. 

Before getting into the details of the framework, we took a two-minute tour of a data management system that was implemented using LightSwitch – the Wind Demographics Database Interface:

 When using Visual Studio to create a new LightSwitch project, the framework for a three-tier application is automatically generated.  The front-end is a Silverlight client that can be configured as a desktop application, or as a browser-based application hosted by a Web server or the Cloud.  “Pages” are added by creating Screens that are based off of Search, New, and Edit templates.  The middle (logic) tier is generated using Microsoft ADO.NET Entity Framework and provides a service layer to connect to various third-tier data layers (SQL Server, Oracle, SharePoint, RIA Services).  The logic tier offers Entities and Entity Sets that are types (classes) that match the underlying data service constructs. 

Using Visual Studio LightSwitch, we were able to stand up very simple web application that connected to a SQL Server database in about 7 minutes (including compilation time).  This led us into our “What’s Hot” discussion that centered on some of the impressive features of LightSwitch:

• Relational Data Model – all the CRUD is done for us
  • No need to write SELECT, INSERT, UPDATE, and DELETE stored procs for all of the data objects in the model
• Free Validation
  • User-edited fields are automatically checked for type and length
• Free Dirty-Checking
  • If a user edits data and attempts to close the tab, screen, or even the whole application, the Framework automatically prompts the user to Save changes, Discard changes, or Cancel – no need to write custom JavaScript
• Built-in templates
  • Search, New, Edit screens provide default layouts for the generated data types, with the entity fields and related collections automatically added to the screen
• ASP .NET Security
  • Windows Active Directory can be tied in automatically, so that users and roles can be managed through the application by administrators – all without custom security code modules

Next we went over some of the less-than-ideal requirements or drawbacks of LightSwitch:

• Data Model must be normalized
  • For example, tables that are missing primary keys cannot be imported into the Logic tier
  • No class inheritance for Screens
    • This leads to a great deal of “copy-paste” code
    • Customization of application appearance requires creation of custom Shell and/or Theme Extensions
      • Even some as “simple” as adding a logo to the application turned out to be not so simple
      • Unit testing is not supported out of the box

To wrap things up, we discussed the overall impressions of LightSwitch.  It seems ideal for straightforward data management applications, not only for LOB apps but also for lighter-weight “utility” applications.  For example, some of the developers began contemplating the use of LightSwitch to provide a quick and easy interface into databases that normally require “manual” management.  LightSwitch offers extensibility and the means to create custom business logic by writing VB or C# code; however, the trade-off is the need to have some technical expertise to create any type of practical application, and to navigate configuration and deployment aspects.  Another catch is the necessity to have a sound data model – the automated features of data modeling in LightSwitch are only possible with a normalized data model with primary and foreign keys in place.  At the end of the day, LightSwitch is a powerful framework that can stand up applications quickly as long as the proper conditions are met and the proper expertise is driving the effort.

Tuesday, August 9, 2011

by Mark Wojciechowicz

This past Tech Tuesday, I presented techniques for data visualization as espoused by Stephen Few, the author of Now You See It.  We first looked at some common best practices for visualizing data in any tool and then took some of those techniques to SSRS.  For those who have not worked much in SSRS before, I demonstrated how easy it is to get started.  In this post, I will cover some of the key considerations in displaying data.  At the end of the article, you can find links to the source files.  The data that was used for the demonstration was taken from the www.beerinstitute.org (when you are not presenting data to sales people you have to have some catchy subject matter!)

Pre-attentive Attributes

First things first, by data visualization we mean to take that raw set of numbers that’s presented in a giant data grid and present it in way to draw at the meaning and trends in the data.  The human mind can understand images much more quickly than it can read numbers and assess their relative weighting to each other.  Those images which are most easily understood by the mind are called pre-attentive attributes.  Some of these attributes are much easier to absorb than others and make it so that the audience of the report can comprehend the data with little explanation.

Not all charts are created equal

Some charts take much more work to understand than others.  So let’s compare a pie chart, bubble chart and column graph. 

A pie chart presents triangular shaped areas that are hard to accurately compare the slices relative value.  In the image above, how much more is D3 compared to D8, and better yet, which is D3 and which is D8?  The default settings for a pie chart often include a legend with an array of colors that are time consuming to match up to their slices.

Bubble charts look cool!  The sales guys love them when they want to compare 3 different data points at once.  How much greater is D5 compared to D6?  If we went around the room, we would probably hear numbers like 10 times as much, while in reality it is only 6 times larger.  Circle areas are even more difficult to compare than pie slices. 

When we look at the old standby, the column chart, we more easily see which lines are the largest and the relative difference between them.  In most charting tools, we still have to battle some obtrusive defaults as we’ll see in the image below.

In the above graph, there are some common charting problems:

• The scale starts at 10, rather than 0.  This creates an optical illusion having us think that D1 is a 3^rd greater than D2.  This is the same dataset throughout all of these charts, however.  Looking back to our previous bar chart, you will see that these two data points are far closer to each other.
• The order of the dataset is alphabetical rather than by value.  This makes it more difficult to see exactly how the data points compare to each other.  You might even be prone to sorting them in your head.
• Last, the multi-color bars merely add distraction.  A single series should all be the same color unless you want to highlight a particular data point.

If it is necessary to zoom in on the data, we can use a different image to display them which takes away the optical illusion.  Below, I have replaced the part chart with a line chart.  I made the line invisible and added horizontal markers.  Now, you can see the distance between the columns, but you are given to using the scale and their 2-dimensional distance to compare them.

Now who doesn’t love 3-D charts?

Well, me.  And probably just me at that — but let me explain the challenges.  3-D images are really just 2-D images with shadow and line effects.  Comparing the exact difference in bars is very difficult, even with the help of a gridline.  Not only this, data points can be hidden behind others making it very hard to get the whole picture.  Even with a cool charting tool that lets you spin the image around, there is almost always a hidden data point and, thus, a challenge to your short term memory.  And I sure don’t need challenges to my memory.  A solution to the 3D graph is below when we discuss Trellis Charts.

So what’s good to include in a chart?

First, try to leave out any additional markup that is distracting to what you are trying to feature – the data.  Some of these things are:

• Chart borders
• Gridlines
• Background colors
• Labels (labels can be delivered through a mouse over)
• Multiple colors for the same series
• 3D presentations

Some good practices in charting are:

• Ordering a series by value
• Using 0 scale or 2-D positioning with markers
• Using lines to connect a time series

Reporting Services

Now, that we have these principles in place, we can see how this plays out in Reporting Services.  SSRS 2008 R2 offers a few new tricks which can help make the process of building a visual report much easier.  Sparklines can be dragged onto a report table over a data point and can be quickly configured to show the trend for a category in a tiny graph that wastes no space and gets straight to the point.

We can easily compare the annual trend of beer sales across beer sources in the table above.  However, sparklines do not capture the relative difference between each data point. To do this, we must convert the sparkline to a regular chart (right click the sparkline and select convert to chart), clean up all its decoration and scale it according to the highest month for all beer sales amongst all varieties.

Now, we can see that domestic draft is small potatoes compared to domestic packaged beer.  When we have many categories to compare across each other, we can fit these charts into a tablix.  This creates a trellis chart.  Trellis charting paints the same chart across rows and columns to compare relative differences to each other. 

Please note that, like a regular chart, the categories should be ordered by greatest amount to smallest in order to aid comparison.  While we covered some other cool tricks in reporting services like indicators, one last point that I would like to highlight is the use of bullet graphs.  Bullet graphs offer a very compact way to indicate status with relative weighting.  A bullet graph is nothing more than a bar chart (or stacked bar chart) with a target line (a marker).  See below.

The bullet graph shows an orange bar seeking to cross a target line.  In order to make this affect realistic, I used the Datamining excel add-in to forecast sales of beer.  This forecast number became the marker point and the actual sales are the bar.  The eye is immediately drawn to those lines that make the cross.  In addition, you can see the relative difference in magnitude.  We would probably care much more about forecast accuracy for packaged beer than the other smaller categories.

In the last column, I illustrate a poor use of indicators.  Indicators can be a great way to subtly highlight a data point that is in trouble.  But it tends to dumb down the data when it is used to emphatically.  You also might draw a completely different conclusion looking at a bullet graph versus an indicator.  While import packaged goods had a very good 2010, it pales in comparison to the relative importance of domestic packaged beer.  And I’m not just saying that because I like micro-brewed beer.

I hope these charting tips are helpful.  To learn more, please check out Stephen Few’s work.  Links to the BeerInstitute database and SSRS project are below.

References:

Few, S. (2004, September 4). www.perceptualedge.com. Retrieved July 25, 2011, from www.perceptualedge.com/articles/ie/visual_perception.pdf

Few, S. (2009). Now You See It. Oakland, CA: Analytics Press.

Ware, C. (2004). Information Visualization: Perception for Design, Second Edition. San Francisco: Kaufman Publishers.

Prior to 2011, our exposure to WCF was limited, primarily delivering WCF services using the Visual Studio “WCF Service” template. This approach allowed us to create true WCF services hosted in IIS and exposed over HTTP. This was perfect for the environments that we were already working in since those projects were Web based projects and, therefore, IIS was readily available. With IIS came access to existing SSL certificates to secure the content of the messages flowing to and from the service. And, since the organization was already using IP filtering to limit access into the DMZ, we had a way to “authorize” the client making the calls in to our services. We successfully deployed several WCF services in this fashion to support both internal and external communication and we delivered that project with a sense of accomplishment and feeling pretty good about our WCF knowledge.

Then came 2011. We started looking deeper into SOA from an architectural position and decided to do a deeper dive into the WCF technology. At that same time, we started working with a client that was looking at using WCF to support a centrally hosted SOA based routing engine. The deeper we dove and the more requirements that were gathered, the more we realized how little we really knew and understood about WCF. It became clear to us that WCF was a lot more than glorified IIS URLs.

Below are concepts that we feel represent some of the more important aspects of WCF:

• WCF Hosting: WCF services can be hosted in IIS or any other windows process using the System.ServiceModel.ServiceHost class. When hosting in IIS you use the web.config file to configure services and IIS takes care of the rest. When using a Windows process (e.g. a Windows service, a console application, or even a Windows forms application) you can use the app.config file OR you can programmatically create WCF services and manage all aspects of a service in code. Programmatic mode allows for dynamic creation of services providing complete control over all aspects of a WCF service.

• Protocols: WCF services hosted in IIS use the HTTP protocol by default to communicate. It is possible to support the NET TCP protocol in IIS but that requires additional installation and configuration in IIS.  IIS cannot support the Named Pipes or MSMQ protocols. Hosting using the System.ServiceModel.ServiceHost class supports all WCF protocols without any additional installation or configuration.

• Security–Authentication: WCF services support protocol, message, or protocol and message security.  Protocol security includes concepts like Kerberos, NTLM, and certificate. Message security includes concepts like Kerberos, NTLM, Username/Password, certificate, and issued token.

• Security–Authorization: WCF services support role based, identity based, and resource based authorization models.

• Cloud Computing: Microsoft has released an Azure SDK for Visual Studio that makes developing WCF and WF services for the cloud fairly straight forward. The SDK includes an emulator to test your application locally before deploying to the cloud.

• Extensibility: WCF was built to allow for complete extensibility. All aspects of the WCF “stack” can be extended if the functionality needed is not natively supported by WCF.

Although many of these concepts are not fully explained, our goal is to offer viewpoints to what WCF is capable of doing and to encourage you to take a deeper dive into WCF.

After walking our colleague through these steps, it occurred to me that it would be much easier if someone created an Eclipse Plugin. The good news is that someone did. The installation is, with a minor exception, pretty straight forward.

1. Under the Help menu in Eclipse Helios, select “install new software”
2. Enter the following address in the Work With combo and click add.
   https://svn.codespot.com/a/eclipselabs.org /mobile-web-development-with-phonegap/tags/r1.2/download
3. Give it a meaningful name such as ‘MDS PhoneGap for Android Plug-In’
4. Here’s where we ran into an issue. The plug-in wants to install the JavaScript Development Tools (JSDT) and it was failing to install. Unchecking this particular component allowed the installation to complete.

After completing the installation, you’ll be able to start a new PhoneGap project by selecting File->New->Other->PhoneGap for Android Project. The wizard allows you to specify local, web, internal, or git repository locations for the PhoneGap bits. As a bonus, you can also opt to include the JQueryMobile or Sencha Touch in the project and build the sample application. It’s never been easier to get started with mobile development.