Self-reporting via Excel power pivot in highly deferred execution mode.

Introduction

In the enterprise world, reporting is a world on his own. Enterprises have often very sophisticated reporting systems which either exists in isolation and are called from application which have the actual “ownership” on the data for which is report is generated. Ok, this needs some clarification. I’ll address this in a first paragraph. I’ll explore as well how we can easily introduce in the context of LightSwitch the notion of “self-reporting” and I’ll explain why this approach can be from an application security point of view, the best reporting solution. In a last paragraph, I’ll introduce kind of experimental or visionary reporting  approach where I promise to explain what I mean with the title of this article.

What’s the problem with reporting systems external to the application?

Enterprises care about security. There are plenty of guidelines which can improve the eventual security level of an application.

For example, authentication is ideally not handled by the application itself, meaning that an application database should better not store passwords or password hashes. Enterprises often have dedicated authentication systems.  In a windows environment, using Kerberos based security (windows authentication) is probably the best choice.  By doing so, active directory takes care of the authentication and  by means of a very intelligent ticketing mechanisms, “authorizations” are passed between systems in a highly secure manner. Also outside your domain,  so in a federated context, ADFS (active directory federation services)  can elegantly complement this. Proven technology !

Authorization is an other story. It’s about the users role and corresponding permissions for a specific application. Inside the server side application logic, this leads, among others, to row level security both in the retrieve and save pipeline of the business operations that can be performed with the application. A canonical example from the CRM sphere: “the user of the CRM app, the relationship manager, may see his own customers, but not those of other relationship managers.  A very simple example which I’ll use in what comes next.

When it comes to reporting, this can become problematic from an application security perspective. Dedicated reporting systems often make a direct connection to the application database.  In LightSwitch, implementing the above row level security example is quite trivial. But when my external reporting system makes direct database connections, the rule can easily be bypassed. Obviously, the reporting system can easily implement that same rule as well but that means we are less D.R.Y. (don’t repeat yourself) and worse, when the rule one day changes, we might forgot to implement it in the reporting system. In short, such an external reporting system can be a source of many application security issues !

What’s the solution:

Reporting systems should connect to the application service layer

A LightSwitch app exposes its internal odata service. As a result, when our reporting solution connects to the same service for retrieving the data on which the report is built, we never run in the kind of security trouble described above. When I can see in the application only the customers which are related to me as a relationship manager, the reporting system connecting to the same odata feed, will never make it possible to report on the customers of the other relationship managers.

How does power pivot becomes mega power pivot when used  in combination with LightSwitch?

Excel power pivot is such a self-reporting solution which can simply connect to an odata stream of an application. When using windows authentication from the excel application combined with an https-based data connection we can say that this is the most secure reporting solution.

How can we make self-reporting more user friendly.

Obviously, the most classic approach of reporting is the one where the report is generated by the application itself (server side) and the report is “streamed” to the client. So, this is not the self-reporting scenario. Of course, this “internal reporting solution”, is when it comes to row-level-security completely ok. There are quite some solutions described for applying this approach with LightSwitch. It’s clear that such a scenario, puts some extra load on the server side processing and causes also the data traffic of streaming the report to the client. The load on the server is about the data retrieval, the querying, the report formatting and so on.  In the power-pivot scenario part of this is dispatched to the client and furthermore, the user has full degrees-of-freedom when it comes to report formatting, which is great… when he needs this.

As IT guys, we are happy with what excel is offering us when it comes to establishing odata connections to application data feeds.  Indeed, simply provide the https address of your data service (in lightswitch, this is  the famous ApplicationData.svc) and .. you are all set.

Nonetheless, not every user will be happy with this approach, simply because it’s too technical.

So, I’m currently wondering, but don’t expect over here a full sample with a working solution, if we can not combine the comfort of  a report generated by the application with the flexibility of the power pivot approach.

The approach I have in mind boils down to the following:

The user sends from the client side an request to the server for a specific report on a specific (set of) entities. The server will this time not generate a report but streams an excel file to the client side which contains no data at all, but contains the material necessary for simply executing the Odata query, so with everything in place (connection strings, user credentials, …)  in such a way that one press on the refresh button generates the report. So, the user gets a self-containing excel without odata setup trouble.  So, this approach would drastically reduce the load on the server and since there are no data sent over the wire, the response (the excel file) will be delivered in milliseconds to the client.

This explains the second part in the title of this article: the  highly deferred execution mode.  In Linq terminology, deferred execution means that a Linq query statement is nothing more than the declaration of  “an intention”. The formulation of a Linq query will, a such, never trigger database access. The actual database access is “deferred” until the data is really needed. (e.g. by applying a “ToList()” to the entity collection). Linq-beginners often misunderstand these concepts.

The above reporting approach would potentially go one step further and defer the query execution until the user opens the data-less excel file and presses the refresh button. Furthermore, when he opens the same excel the day after, he’ll get the latest data…

Just an idea…

I’m not in the habit of writing LightSwitch related articles without code. This is probably my first code-free post

I’m not sure if the above makes sense and I’m even more wondering if an actual implementation would be feasible.

So, please try it, write an LightSwitch extension for it, take patents, commercialize it, and give me a free licence. Thanks in advance.