Data Architecture for reporting solution in .NET

Question

I think we are in a pretty standard situation. We have built a fairly complex data model with approximately 100 tables and many relationships to store the data related to this complex business problem (intranet system with 100's of users). There is of course a UI and since the application has been built we have gone down the traditional path of offering some standard reports and then even a CSV download of data (based on those standard reports). After lots of usage of the system the user-base wants more and more complex reports to the point where it doesn't make sense to create these "one-offs".

The obvious next step (at least in my mind) is to put the creation of the reports in the hand of the business user. This is either something like Crystal Reports, SQL Server Reporting Services or BI tools like Tableau. Having not had much experience with many of these tools I am at a loss. Moreover, I struggle with the issue of the complex database tables and believing that the user community can understand the complex relationships of the tables in addition to opening up our schema with all of its database-only computational fields that should never be seen by the user.

I believe I have a few choices here (in terms of data architecture and in terms of direction in general) and they include:

1. Simplifying the schema to the business user via an API or more simplified "view" of the database tables - which is better for the business user but requires upfront work and maintainability.
2. Letting the business user see all of the tables - which obviously makes the maintainability easy, but will require documentation, training, explanations, etc.
3. Other BI choices (which frankly I'm not familiar with) - OLAP cube, etc.

I'm looking for some feedback on experience in this area and suggested direction in terms of data architecture and/or product usage.

Answer 1

the solution you have thought of is a complex one.

1st step is wrapping the data. Since you want to give your users some control for reporting, understand that not all tables are required by the users and not all the joins may make sense to the users. you cannot expose the views directly to them so you should create user friendly wrappers. like business objects kind of stuff that have names and properties that make more sense to them. The users will be bound to using these objects only. The objects should handle the tables, joins and views internally. Off Course, they should not modify the underlying data in any way.

2nd step would be to offer a selector kind of thing to the users. This would be a very complex task as you would have to allow users to choose which business objects they should use? how they want to combine data from different objects by connecting them in some way? what data would they like to see on the reports? they may want to apply some functions like Sum, Avg, Min, Max etc before displaying and they may want to group fields sometimes. sometimes sorting on different fields is required and sometimes your users may want to specify a criteria. so your app should allow this in some way.

3rd step would be the designer. This should be easier. either you can restrict the users to display simple tabular reports and they do not have much control here. otherwise you may have to design a report designer that allows them to place the fields in any way they like. My suggestion is that initially you should restrict them to list type reports only with a predefined format. only the captions and data would change.

Initially just for demonstration purpose, design a few simple and small business objects. 2 or 3 objects with not more than 7 to 8 properties would be okay. No functions should be available at this time, they will only make things hard for you/your team to implement. Users at this stage should be allowed to define simple criteria and single sort order not more than that and they a predefined report format that can be displayed. If the idea succeeds and your client likes it than you can aim for extending it to your 100+ tables, complex functions and criteria conditions.

Answer 2

You should control the report design and development. Especially in case the database structure is complex. Keep in mind that if the report shows wrong data your software will take the blame.

Creating database documentation is expensive so what I would do is to ask the client for a person who I can train. Ideally this person will create some documentation for the database too. I would suggest you also to consider writing the SQL views and stored procedures and let the client create the crystal report. In such way you will provide the data , but they will be able to present it in the way they want

Once you resolve the issue with report development you need to find a way to run the reports. You need a generic engine , which will be able to add and run external reports. I guess you will need to develop a new module or application, which will analyze the reports, prepare the parameters screen and run them. Reporting services will be a good choice, because you can install and configure the server and you are done. It is free if they have SQLServer license. If you decide to use Crystal reports - Crystal Reports server will do the same , but it is too expensive so my guess is that it will be an option just if your client already owns a license. If the application is an intranet application you can try some of the 3rd party viewers. They are pretty cheap. I believe that $1000 be enough to purchase licenses for 100 users. A good list of 3rd party Crystal reports viewers is available from this link: http://kenhamady.com/cru/comparisons/crystal-reports-viewers

I used few of them and I would suggest you to check R-Tag (www.r-tag.com) - it works with both Crystal reports and SSRS and supports intranet installation. Jeff-net (http://www.jeff-net.com/jnrrv.htm) will be fine too (for Crystal reports). You can try the others - there are some free , but test them first with few users because the maintenance in a network environment might be expensive .

Answer 3

Honestly the main issue for you will be what is your front end architecture like? Client like with WPF or Windows Forms or ASP.NET or HTML? It does make a huge difference as I for one am amazed with the variety of reporting tools built around Jquery and Javascript the likes of 'HighCharts', 'FusionCharts', 'DevExpress'(part of a larger add on) in what they can do versus the more traditional SSRS and Crystal Reports. Web reporting beats the pants off client reporting in areas like event driven reporting, layered reporting, or slices

Honestly if it is a choice between Crystal and SSRS I would go SSRS as I feel as a developer that SSRS is easier to reuse parts of it. Generally SSRS has three parts to every report:

1. Connection(s) to a Database, Flat File, or service(or other data containers too). Can be shared for re use.
2. A Dataset(s) representing the data to be accessed. Can be shared for reuse.
3. A set of objects that can be tables, matrixes, or graphical charts

SSRS's main selling point is you can learn the basics fast for report building and publish reports and have a built in service that can enable email subscriptions(provided you have a working SMTP Server), file saves in subscriptions, caching of datasets, and accessing the reports via .NET with talking to the service directly to even make the service render reports on demand from events in an application.

While SSRS has some limitations out of the box the service can be consumed via HTML form, .NET access, or rest URI making it fairly flexible.

Tableau is pretty much a tool for an analyst and does most of the coding for you. If you are wanting to give people a sandbox and make cube like measurements and then play with data, it is pretty impressive. Have not messed too much with tweaking it though, so it's layout may be what you see is what you get. But saying that it can do a lot of things out of the box to the point it is very impressive with understanding spatial data, multi slider inputs, plot formatting, and other tasks that are not built into other reporting services.