Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

LightSwitch and Self-Service BI

with 6 comments

Visual Studio LightSwitch has been on my list of Things To Check Out When I Have Time for a while now; my upcoming session on the uses of OData feeds for BI at the PASS BA Conference (which will be a lot more exciting than it sounds – lots of cool demos – please come!) has forced me to sit down and take a proper look at it. I have to say I’ve been very impressed with it. It makes it very, very easy for people with limited coding skills like me to create data-driven line-of-business applications, the kind that are traditionally built with Access. Check out Beth Massi’s excellent series of blog posts for a good introduction to how it works.

How does LightSwitch relate to self-service BI though? The key thing here is that aside from its application-building functionality, LightSwitch 2012 automatically publishes all the data you pull into it as OData feeds; it also allows you to create parameterisable queries on that data, which are also automatically published as OData. Moreover, you can publish a LightSwitch app that does only this – it has no UI, it just acts as an OData service.

This is important for self-service BI in two ways:

  • First of all, when you’re a developer building an app and need to provide some kind of reporting functionality, letting your end users connect direct to the underlying database can cause all kinds of problems. For example, if you have application level security, this will be bypassed if all reporting is done from the underlying database; it makes much more sense for the reporting data to come from the app itself, and LightSwitch of course does this out of the box with its OData feeds. I came across a great post by Paul van Bladel the other day that sums up these arguments much better than I ever could, so I suggest you check it out.
  • Secondly, as a BI Pro setting up a self-service BI environment, you have to solve the problem of managing the supply of data to your end users. For example, you have a PowerPivot user that needs sales data aggregated to the day level, but only for the most recent week, plus a few other dimension tables to with it, but who can’t write the necessary SQL themselves. You could write the SQL for them but once that SQL is embedded in PowerPivot it becomes very difficult to maintain – you would want to keep as much of the complexity out of PowerPivot as possible.  You could set up something in the source database – maybe a series of views – that acts as a data supply layer for your end users. But what if you don’t have sufficient permissions on the source database to go in and create the objects you need? What if your source data isn’t actually in a database, but consists of other data feeds (not very likely today, I concede, but it might be in the future)? What if you’re leaving the project and need to set up a data supply layer that can be administered by some only-slightly-more-technical-than-the-rest power user? LightSwitch has an important role to play here too I think: it makes it very extremely easy to create feeds for specific reporting scenarios, and to apply security to those feeds, without any specialist database, .NET coding or SQL knowledge.

These are just thoughts at this stage – as I said, I’m going to do some demos of this in my session at the PASS BA Conference, and I’ll turn these demos into blog posts after that. I haven’t used LightSwitch as a data provisioning layer in the real world, and if I ever do I’m sure that will spur me into writing about it too. In the meantime, I’d be interested in hearing your feedback on this…

Written by Chris Webb

April 1, 2013 at 9:45 am

Posted in BI, PowerPivot

6 Responses

Subscribe to comments with RSS.

  1. I bought an early release book, http://shop.oreilly.com/product/0636920024194.do, Developing Business Intelligence Apps for SharePoint [Paperback] by David Feldman, Jason Himmelstein. I agree its aimed at Acceess type development, and that using OData feeds as data sources will become more important.

    Donald Parish

    April 1, 2013 at 1:15 pm

  2. It would be really powerful if Power View was made available as a Lightswitch control. Imagine the type of analytic applications BI professional with little coding experience could build. Getting back to earth, as a first step I’m going to try and build a front-end for some of the reference and meta-data BI tables that are currently require a DBA type to maintain. Chris, thanks for bringing up this product.

    suhail ali

    April 1, 2013 at 3:26 pm

  3. Hallo Chris,

    You should check out LLBLGen (code generator, multi db platform) and it’s Odata service generation capabilities. It is slightly more complex maybe than Lightswitch, but it is much more light weigthed than LightSwitch. It comes with the price you have to have know how to programmaticaly setup authentication and do deployment of the solution.

    I full agree on the power of OData services within the BI world.

    Regards,
    Joey

    Joey Moelands

    April 2, 2013 at 6:33 am

  4. We use Lightswitch to build end-user maintained reference data, it absolutely rocks at this task.

    Tony

    April 3, 2013 at 1:09 am

  5. Chris- despite the rather dry name of your presentation… it was the best one I saw at the conference. Thanks for coming all the way to Chicago. You are a fabulous presenter and one gutsy guy… not everyone would have the cajones to actually try that live… on stage…

    Mary

    April 13, 2013 at 7:34 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: