BI sans BS

Year[2013].NewYearResolutions[0] = Share shorter, but more often.  Honestly blog-o-sphere, long and detailed blog posts take weeks and weeks of energy, and last year demonstrated it’s not always possible to combine that with an actual 3D life.  Shoutout to Paul Van Bladel and Michael Washington for the infinite amount of energy they brought to the LS community in 2013!!

Year[2013].Days[0].Plans = BI sans BS.

We’re working on an incredibly complex LS application code-named Tess.  It’s a multi-tenant application with a lot of need of customizability between tenants, which resulted in an extremely normalized (fragmented) database structure (phase one isn’t even complete and we have 140+ tables for only 30+ screens).

The objective of today is to provide the end-user with a meaningful API to this application.  An API for the end-user?  Yes, besides the actual application, we want to provide the tech-savvy chosen ones in our end-user flock to have an API so that they can do some self-service BI in the form of Excel powerpivotting and custom reporting.  In a way, LightSwitch provides this self-service BI in the form of OData: by exposing the server layer via OData, any one can consume the OData and make more informed business decisions.

However, there’s 10 kind of people: those that can count in binary, and those that can’t.

And our tech-savvy end-users, no matter how high their excel macro-ing skills, will not be able to extract meaningful information out of a highly normalized database in a performant or efficient way.

So here’s our idea…

First, we’re going to add a number of denormalized views to our database, tailored to the specific reporting needs of our end users.  Adding these views will give our normalized database a second ‘face’, serving as a data warehouse.

(BI experts gasping)

Ok, this in no way substitutes for the kind of value a real BI expert could bring by introducing an actual data warehouse, and will be slower (there’ll be no precomputation other than the occasional SQL Server caching). On the other hand, it does allow us to preshape the information hidden in the database, the performance penalty might initially not be so bad (on a case by case basis, not everything has to be expressed in ‘milliseconds’) and this in no way limits us from setting up an actual, separate data warehouse in a later phase, when we have the resources and there’s need to shift to MOLAP.

Secondly, after shaping up our data, we need to expose it.  This task is easy: add a second LightSwitch project to the solution, import the views, publish as service only… OData? Voila!

This should be sufficient for some powerful powerpivotting, but we need end-user defined reporting as well.  Hence, we’re going to empower our second LS app with a reporting engine, but feed it (with report ‘definitions’) and consume it (show the reports embedded, or expose links to the reports from Tess, our main LS app.

‘BI sans BS’.  It sure sounded like a good idea yesterday, so time to roll up my sleeves and get dirty. I’ll let you know how it went, if the customers love it or find it cumbersome, and if it’s very messy to set up or so easy that it leaves us with enough time today to create a GUI in Visual Basic to track the killer’s IP address…  (Really Hollywood, really?)

Keep rocking LS!





Update: it appears there are two small obstacles worth-mentioning: Views require some special attention for parent-child relationships (when REALLY needed), and deploying two LightSwitch apps as a single system requires unifying the security tables.  Other than this, the setup described above of one normalized database with denormalized views, one normal LS app and one service-on-the-views only, works extremely well.

It was a breeze to set up, and I can shape the data anyway my customers need it in SQL server, where it’ll have the best performance (compared to for example: RIA services or Web API on the ServerApplicationContext). (When we would need more performance we can still take the extra complexity of actually using SSAS and a populating a separate denormalized database, then substituting the connection string)

Additionally, I can publish an update to the views and the second LS service, without having to update the normal LS app (and thus forcing end-users to download the ‘next version’ of the Desktop Client), giving us have fine-grained control over the read-only API that is exposed for our application so I can refactor the app all I want without breaking third-party dependants (reports, powerpivots, etc).

Setting up the reporting aspect now… As usual, that comes with a little challenge of its own…  Plans for today: keep it dry by learning T4 😉


2 thoughts on “BI sans BS

  1. Pingback: LightSwitch Community & Content Rollup- December 2013 - Beth Massi - Sharing the goodness - Site Home - MSDN Blogs

  2. Pingback: LightSwitch 社区和内容汇总-2013年12月份 - Beth Massi的中文博客 - Site Home - MSDN Blogs

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s