In search for enterprise ready reporting and business intelligence in lightswitch (part 1 of N)

Introduction

Reporting keeps me busy.

LightSwitch doesn’t have any built-in reporting and I love that.  It’s of the same order of LightSwitch not having a proprietary built-in database system. In other words reporting is as much an “external sub system” in  a line of business app, as a database is.

Luckily there are many reporting solutions around, and this is very much causing, what we say so nicely in French, “L’embarras du choix”.

Before looking into solutions and a technical architecture it makes sense to first sit back and relax for a while and think about a strategy for teasing our brain to come up with something decently,… hopefully.

Disclaimer: I’m more and more in the habit of starting article series, without being hindered upfront too much of what coming next and without having upfront solutions in mind but I don’t mind myself .

Some background

What’s wrong with most enterprise reporting solutions?

I have a very simple answer: the level of rigidity. This brings us to an other question: are there eco-systems where reporting is less rigid? Yes indeed: in the world of the citizen development.

How does reporting differ between enterprise and citizen development?

Citizen developers often use the familiar tools like Excel and Access for their “departmental”  apps.  Most of these tools have no notion of an isolated application layer as we know in LightSwitch (or any other decent line of business application framework). Instead, data access is simply a matter of connecting directly to the database. Clearly a serious drawback when it comes to security but of course, being able to connect directly to the database increases the potential level of creativity in terms of report data design and indeed, decreases as such the level of rigidity.

But a more important difference which will help us more to come up with a streamlined enterprise ready reporting solution, is the way citizen developers organize the process of report design.  What’s often fundamentally different is that report design is not always the task of the application developer. Often there a people around with much more talent for business intelligence than the app developers. In the citizen development world this type of flexibility is very much possible. The B.I. guru design a report is simply shares the template (usually an excel sheet) with colleagues on a network share or whatever.

What would we like to learn ideally from citizen development and use it in an enterprise approach?

Let’s first start what we don’t want to change and I apologize upfront for not being completely D.R.Y. but it’s my personal leitmotiv: a reporting engine should, in the context of line of business reporting, never but never access directly the database.  The application layer protects the assets of the users of your application, you don’t want to bypass this. Do you want that in a multi-tenant app, tenant x can generate reports over the data of tenant y?  I clearly emphasize in the context of line of business reporting:  there can be plenty of reasons why a standalone reporting solution accesses directly the database or that you even duplicate your database for the sake of reporting.

What we can learn from the citizen development world is that when it comes to enterprise app reporting, we should better try to identify different reporting categories or different reporting use cases and try to find the ideal solution and architecture for reporting in order to maximize the level of flexibility for that particular report ‘category’. So, let’s do a first attempt to identify these categories.

Reporting use cases

(1) “Inline” or “on screen integrated” BI

Easy to visualize mentally without a screen shot (that’s good for me, I currently don’t have (yet) any working solution because I’m still in ‘philosopher’ mode looking into the reporting problem…). Take in mind a product detail screen containing, inline, some BI details of product sales for that particular product , let’s say, by region. The report is not opened in a separate browser tab, but part of the LightSwitch screen.

Designing the screen is the world of the application developer, especially in the context of LightSwitch app development. The BI report in this case is tightly coupled to the design of the screen, we don’t need much flexibility here. Well, obviously depending on what we mean with flexibility. The report itself should of course have the right level of flexibility and cover the user’s requirements (e.g. there should be a dropdown for selecting the region, etc. ) but the users needs not the flexibility to complete redesign at run time the complete shape and data structure of the report template.

(2) Report templates designed in mixed mode: by the developer and/or the BI guru

It’s clear we try to find here some inspiration from the citizen development world. Some types of reports require the involvement of a BI expert or even a data expert. Ideally, such a report template should be designed apart from the development environment. Compare it to what is possible with ‘Blend’ when it comes to complex screen layout design: a graphical designer can do the screen artifact design whereas the application developer design the code-based ‘ViewModel’ (in the good old MVVM way of working). They do their work in isolation and at a certain point in time they integrate their work smoothly without pain nor surprises… hopefully.

The type of flexibility we are envisioning here is situated in the smooth collaboration between developers and BI/Data experts.

Another type of extension (and definitely an overlap with option 3) on this category is having the ability for the end users to download such a fine-tuned report template and perform slight modifications or specific tweaks and have potentially the option to upload this template again in a kind of report template repository in such a way the report template can be shared with other users. Quite close that what happens in the citizen development eco-system but with a clear enterprise sauce.

(3) User designed report templates.

User designed report templates where the end user has (almost) complete freedom. She can clone an existing report (or ideally start from scratch) and make her own variation on the report template design.

I can assure you that this is the most challenging option, especially in the context where we want that the report query accompanying a report template is designed against the application service layer rather than directly to the database.  Therefor, there is a huge likelihood that I will need to ‘relax’ this requirement and speak only in terms where a user can clone an existing report (started from option 2) and make report design variations but not profound report query changes. We’ll see!

Conclusion

The above is clearly not a full blown epistemological analysis on reporting. Yes, we can’t.

It’s nothing more than for me a mindset for trying to do something nice in LightSwitch when it comes to reporting.

I will use the Telerik Reporting solution. This solution has a very decent dedicated report designer and that’s the approach I prefer above a reporting solution which is completely “code library based”, so without designer.

Nonetheless, I will not focus in the next articles on specific details about report design, you can read these yourself on the Telerik website. I’ll focus on the integration in and architecture for LightSwitch and especially from the mindset of the aforementioned categories.

Happy reading….