What is LightSwitch?

Visual Studio LightSwitch is a development environment that aims to simplify and streamline the design and implementation of business applications and data access. At its most basic level, LightSwitch allows you to create forms over data-style applications, with a few simple operations that do not require any coding skills. In this regard, LightSwitch is similar to MS Access. But this is where the comparison ends, as LightSwitch offers so much more. For example, thanks to the SilverLight client framework, you can deploy your LightSwitch project as a stand-alone desktop application, or publish it to a web server, without making any changes to your project. Furthermore, you may add a HTML5 client layer to your application, and create LightSwitch screens that are optimised for mobile devices. You will need a Visual Studio 2012 Professional license (or higher), in order to develop applications using LightSwitch. In order to execute a LightSwitch application, no licenses are needed (although you may need to install Microsoft’s SilverLight runtime components).

This article is not meant as a tutorial for LightSwitch. There are plenty of sources of information if you want to learn how to create LightSwitch applications. A good place to start is the Visual Studio LightSwitch Documentation. What follows is an overview of how LightSwitch works, and how it could be useful in a Business Intelligence context, possibly as an alternative to Master Data Services.

How does it work?

LightSwitch utilises a classical 3-tier architecture that separates the presentation (client) layer from the business logic (service) layer, which in turn is separate from the storage (data access) layer (see illustration).

Starting with the storage layer, LightSwitch includes support for a variety of data sources, such as relational databases, OData services or SharePoint lists. You may also create a database directly inside your LightSwitch application, if you do not need to access external data. When connecting to an existing relational database, you simply choose which tables and views should be included in your application. Note that LightSwitch is only able to directly access tables with an explicit primary key defined.

When clicking “Finish”, LightSwitch will import schema information and try to determine the relations between the selected database objects. A recommended practice is to use explicitly defined foreign keys, but this is not a requirement. Also, for complex data models, avoid using composite primary keys, but instead use a surrogate integer column (preferably including the word “Id” in its name) with the IDENTITY specification.

From the imported objects, LightSwitch creates a logical data model for you to review and alter if necessary. At this point, we have already moved up to the service-layer of our application, where we may define business rules and validations. In fact, now we are no longer dealing with tables, rows and columns, but have moved into the realm of collections, objects and properties. That’s right! LightSwitch has already taken care of the Object/Relational Mapping, and thus the Data Access layer of our application is basically done (LightSwitch uses the .NET Entity Framework for Object/Relational mapping). To encourage the use of consistent naming in the application, LightSwitch attempts to pluralize object names. For example, if you have a table in your database called “Person”, LightSwitch will name the collection of “Person” objects “People”. In a similar manner, LightSwitch will pluralize the names of all other collections and singularize the names of all objects. If you are not happy with the names assigned by LightSwitch, feel free to change them. The naming is important, as it is used throughout the service- and client layer of the application. Say, for example, that your relational database contains an Employee and a Department table, as shown in the diagram.

When importing these objects to the LightSwitch logical data model, the collection of departments will be named “Departments”. Furthermore, as an explicit foreign key exists between the tables, LightSwitch will automatically create the relationship between the objects. This means that every “Employee” object will have a property called “Department” that directly references the department to which the employee belongs. The DepartmentId foreign key column will be hidden. Furthermore, each “Department” object will have a collection property called “Employees”, which contains a list of “Employee” objects belonging to that department. This is Object/Relational Mapping in action, and it looks like this:

In the screenshot above, notice the special property types used. Email Address and Phone Number are types that are included with LightSwitch. These types may be applied to any property that stems from a varchar or nvarchar column in the database. The type specification is just a simple validation mechanism, which ensures that users may only enter a valid email address or phone number as the value of the property. You can also create your own types and custom validations, for example using Regular Expressions. LightSwitch also lets you create Choice Lists, which are name-value pairs that limit the valid input value of any property to the values defined in the list. This is useful when you do not want to create a lookup table just for a few static values. In the presentation layer, a Choice List is typically rendered as a dropdown-box.

When you are happy with your data model, it is time to move on to the presentation layer. This is where you define the so-called “Screens”, which are visual representations of your data collections, objects and their properties. When using the SilverLight client for LightSwitch, screens are rich user interfaces that may be customised in any way imaginable. You may even apply an overall theme to your application, to get that corporate look and feel. This article showcases the SilverLight client, but if you have a requirement to access your LightSwitch app from a mobile device, you may create any number of fully HTML5-compatible screens to augment your app. If you do not want to use the existing LightSwitch client options, you can deploy your application as a service only, and roll your own client on top of it. A service only-deployment exposes the LightSwitch applications middle tier as a WCF Data Service with one or more OData feeds. You can communicate with this service using a variety of methods, such as SOAP web requests, JSON queries, etc.

Using the SilverLight or HTML5 client in LightSwitch, creating a new screen is a simple matter of choosing a template and a data collection to populate the screen:

Hitting F5 to let Visual Studio build and run the application, produces an output like this (with the SilverLight client):

Functionality such as sorting, searching or exporting grid data to Excel is already built in. Furthermore, LightSwitch uses optimistic concurrency control, giving users a warning when editing a record that has been modified by someone else. Notice the “Design Screen” link at the bottom right corner of the screen. This neat feature lets you customise the layout of the screen while the application is running, in a What You See Is What You Get-fashion. When you are done customising, your changes will be saved right back to the development environment.

Why use LightSwitch?

LightSwitch is marketed as a framework for rapidly creating line-of-business applications, and as such, it does a pretty good job replacing MS Access as a data entry front-end. Furthermore, in a Business Intelligence context, we have found some very interesting uses of LightSwitch, especially with customers who use SQL Server Standard-editions, and therefore lack the Master Data Services feature. More often than not, we have seen BI solutions where business controllers are responsible for maintaining metadata, master data, product mappings, budgets, etc. in a wide array of Excel files. This has several disadvantages. First of all, there is no type safety in an Excel file, meaning that careless users can easily make modifications to the Excel files, that break the ETL flow. It does not take more than an inconsistent date type format (DD/MM/YYYY instead of YYYY/MM/DD, for example), to have the BI administrator break down in tears searching for the one row that caused the entire data extract to fail. Furthermore, there is no easy way to implement security (other than at the directory/file level), and what about data versioning and backups? And what if you have a requirement to perform some ETL related task after data in the Excel file is updated? Surely, you can not expect your business controller to start SQL Server Management Studio and manually execute a job or an SSIS package?

Master Data Services alleviates most of these concerns (and many others), but it is only available on Enterprise editions of SQL Server. This is where LightSwitch comes in.

With the LightSwitch development environment you have an affordable an easy way to create a secure and type-safe front-end for your business controllers master data maintenance needs. With a minimal amount of code (and often none at all!), you can create rich and easy-to-use applications, that can be accessed through a web browser. And best of all, you are in control of where the data is physically stored, be it in a SharePoint list or a SQL Server database, that might even be part of your BI solution.

What about security?

LightSwitch lets you implement security features using either Windows Authentication or Forms Authentication. If your company already has an Active Directory infrastructure in place, using the former is highly recommended. Regardless of which authentication method you choose, LightSwitch lets you define as many permissions as you need. These permissions are then bound to specific actions, such as “Create a new Employee object”, “Edit an existing Employee object”, “View the list of Employees”, etc. When you have defined the permissions you need, you can create one or more roles, which are collections of permissions. For example, you could have a “Superuser” role, which would typically have more permissions than the “User” role. Finally, you then assign one or more roles to each user (or Active Directory group, if using Windows Authentication).

Using code is the most flexible way to enforce permissions and control your applications security. For example you could have a permission for individual properties of objects, meaning that you could have one role that would be able to view and edit any detail of the “Employee” object, while another role would not be able to see or edit sensitive information such as salaries and social security numbers. You would still only need to create one screen for viewing and editing Employees, as LightSwitch will take care of hiding data and limiting access, according to the specification of the permissions.

Extensions

Thanks to the extensible nature of LightSwitch, many 3rd party tools and plug-ins for LightSwitch can be found on the web. These extensions range from simple property editors for custom types (for example, for entering localised phone numbers in a special format), to various list views with multi-select, drag-and-drop functionality, etc. There are also a range of extensions for data visualisation, such as charts and graph components, and so on. While we will probably not be seeing a BI solution relying entirely on LightSwitch as a dashboard front-end any time soon, these options are certainly interesting. Especially when considering the two-way data access possibilities of LightSwitch. For some very interesting examples of what is possible with LightSwitch extensions, check out Component One’s live demos (make sure you check out their OLAP components and also try to run the HTML5-client demos on a mobile device).

So what are the drawbacks?

Even though LightSwitch applications generally perform very well and have very responsive user interfaces, there is still a limit to the amount of data you can practically manage in a LightSwitch app. As a rule of thumb, if a screen grid would contain more than a few thousand rows, consider options for partitioning the data into more manageable bits. All grids and lists in LightSwitch uses automatic pagination, so in theory, you could load hundreds of thousands of rows, or even millions of rows, into a LightSwitch application, but navigating back and forth, 20 records at a time, would quickly become a nightmare. Pagination means that you cannot just scroll down the grid to locate your data as you would in an Excel spreadsheet, and for this reason, Excel might actually be better suited in cases where you need to work with large amounts of numeric data.

For data entry and visualization, the SilverLight client for LightSwitch comes with many standard components such as grid views, list views, text boxes, dropdown boxes, autocomplete boxes and date pickers. However in our opinion, LightSwitch is still missing a number of components to make it fully featured. For example, there are no components for displaying hierarchical data, such as an expandable tree view. This is a shame, since the LightSwitch data model already has support for self-referencing relations (parent-child hierarchies). Fortunately, as mentioned earlier, LightSwitch is a framework that allows 3rd party developers to create extensions, and presently there is at least one decent tree view component extension for LightSwitch, that even has drag-and-drop support for manipulating the hierarchy structure.

As such, when used in a BI context, LightSwitch is lacking a few important front-end functionalities compared to Master Data Services. When comparing the back-end side of things, LightSwitch requires you to create a data model for your application (unless you already have a suitable data source, such as a relational DB, a SharePoint list or an OData feed), whereas Master Data Services builds the data model for you, as you create your business entities. With Master Data Services, you get functionality such as versioning out-of-the-box, whereas you will have to implement it yourself when using LightSwitch. Integrating with other systems is also your own responsibility with LightSwitch, whereas Master Data Services contains some handy features to assist with integration.

Another concern one might have about LightSwitch, is the use of the SilverLight runtime for the client tier of the application. Microsoft officially discontinued the development of SilverLight in 2012, but thanks to the layered architecture of the LightSwitch framework, this is not necessarily a problem. As new technologies emerge, we will probably see new out-of-the-box options for the LightSwitch presentation layer in the future, just as we now have the option of creating pure HTML5 screens.

Let us now take a look at a successful implementation of LightSwitch in a Business Intelligence context.

Use-case scenario

A customer required that their financial reporting calculations should not be hard-coded in the ETL or OLAP cube. Instead, they wanted to be able to change the calculation definitions at will. Furthermore, they wanted the ability to create dynamic sets of calculations (“schemas”), targeting different report consumers. To solve this problem, we created a relational data model to store the calculation formulas. A Stored Procedure was also created, which took the definitions and the account statements as input, applied the formulas and outputted the results. This Stored Procedure was then incorporated in the ETL. On top of the data model, we created a LightSwitch application with only a few simple screens.

First of all, we had a screen for the corporate chart of accounts, where various properties could be specified for each group account. One property allowed the customer to specify a range formula, instructing the Stored Procedure that the given account was a summation of one or several other accounts. As you can see, this screen looks very similar to a typical entity with user-defined attributes in the MDS Explorer (see screenshot below).

For the definition of financial reporting calculations, a simple screen for creating and naming calculation schemas was created. The “lines” in each individual schema would thus contain the calculations for that schema. The lines would be defined in a third screen:

The ETL flow then automatically generated a financial report for each schema, containing only the calculations pertaining to that schema.

The reason we went with LightSwitch for this particular customer, was two-fold: First of all, MDS was out of the question, since the customer used a Standard edition of SQL Server. Secondly, we did not want a solution based on Excel sheets, since we needed to ensure referential integrity between schemas, account numbers, formulas, etc., to avoid breaking the ETL. The solution should also be able to handle a large amount of accounts and calculations, which could quickly get out of hand in a spreadsheet.

Admittedly, a simple MS Access front-end could possibly have solved the problem just as easily, but thinking ahead, we concluded that the ability to easily deploy the application to a web server, as well as using Windows Authentication to secure the application, spoke in favour of LightSwitch.

Summing up

Although LightSwitch is intended for line-of-business and data-entry applications that do not necessarily have anything to do with Business Intelligence, our experience shows that LightSwitch is an elegant addition to any BI solution where you need a higher degree of control, than would be possible with a number of Excel sheets. From a personal point of view, the LightSwitch development environment is also a fun tool to use, and it is obvious that the Microsoft team behind LightSwitch has put lots of thought into it. It is also evident that LightSwitch as a framework is here to stay, despite the uncertain future of SilverLight. New features in the Visual Studio 2013 release of LightSwitch, such as Office 365 integration, Source Control support and much more, bear testament to Microsoft’s dedication to making LightSwitch a serious contender in the field of business application development frameworks. With its ease-of-use and low coding skill requirements, I am certain that LightSwitch will also find its place in the BI toolkit.

Andre blogindlæg du måske vil finde spændende