1

We have a conventional application built using ASP.Net Web forms which uses SQL Server for database. Reports are generated using SSRS. We have a main table called ManifestContainerDetails - this table is for reporting purpose. When shipping is done for a manifest, it’s component details will be stored in this table. This data is not changed by the system once shipment is done.

There are many reports based on this table. And many users access the same report. The table is huge – but most of the time users will be interested in reports for manifests that are shipped within last one week. So, most of the time the users are requesting for same resource with multiple representations.

This looks like good opportunity for caching the resource. And REST promises caching by utilizing the capabilities of http intermediaries. Things Caches Do - Ryan Tomayko and What and Where are Intermediary HTTP Caches

I found an article REST Reporting - Eric Gropp which says about creating a report from multiple resources using XSLT.

Now I have following questions, while I am in front of a black board, to design a feasibility study:-

  1. Is it a feasible idea to make my above scenario as a REST service and create reports based on that?
  2. Is caching possible in such scenario?
  3. Is tools available to generate advanced reports based on multiple REST Resources?
  • 1. Yes, it's feasible. 2. Yes, it's possible. 3. Yes, tools are available. – Robert Harvey Jul 14 '16 at 13:56
  • @RobertHarvey Thanks. If you can add guidelines or references about some implementations, that will help – Lijo Jul 14 '16 at 14:02
  • 4
    A good answer to that would fill a book, or at least the better part of a book chapter. Do you have some specific concerns that we can address? – Robert Harvey Jul 14 '16 at 14:03
  • Do you actually have a performance issue? – Luc Franken Jul 14 '16 at 14:04
  • 1
    Data warehouse as noted by Nicholas can be a good solution. An intermediate solution would be to review the performance issue in more detail and find what slows it down. You need to know where the issue is, how many records, slow queries, just too many queries, too big resultsets etc. before you can start optimizing. Just adding complexity (caching) is not likely to solve your issue on the longer term. Also it may add a maintenance cost which should be compared to just improving performance and/or improving for example the hardware. – Luc Franken Jul 14 '16 at 14:19
1

Now I have following questions, while I am in front of a black board, to design a feasibility study:-

Is it a feasible idea to make my above scenario as a REST service and create reports based on that? Is caching possible in such scenario? Is tools available to generate advanced reports based on multiple REST Resources?

It sounds like you need a data warehouse. Caching is a double edged sword and tend to leaves you wondering more than implementing. If customers are grabbing reports you do not want the data to affect your application by pulling report data.

The table is huge – but most of the time users will be interested in reports for manifests that are shipped within last one week

If you slice your data in a custom export, SSIS(ETL), you will find yourself writing reports against a single data source vs having to change it with your application. It is more simple to query a FACT table with all of your FACT data vs querying the records that are still being pumped into the system with a plethora of bit flags.

-2

Could you create a view in the SQL server that only shows the data from the last week or so? Then the reports would be against an always updating view of a smaller set of data. No caching needed besides the basic SQL caching the SQL server does.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.