Building an application to calculate metrics on Service Level Agreements. I have access to a single, de-normalized, table that gives me the info I need: ticket ID, category/subcategory, open/close dates, ticket priority, etc. The only way to identify the specific SLA being measured is to select by category and subcategory. Then I need to count tickets by priority for a given date range. I have no problems with the SQL and coding, but how do I set it up most efficiently? SLA's come and go, but I currently have eight to work with. Given the single big table, the columns are the same for all SLA's. The user activities are to select an existing SLA metrics report, add new ones, and delete old ones. For each SLA (meaning a combination of category, subcategory, and priority) the application determines which tickets exceeded the SLA vs. total number of tickets submitted for that time period (either a rolling year, or a single month), and displays the whole mess in a summary component and a month/year/priority table.
I don't have a problem coding any of this; the issue is what to create, and when. For example, the home page displays a list of the eight known SLA reports (data for the list drawn from an XML file), with links to the individual reports. (Did I mention this is a web application? And that the company won't upgrade from .NET 2.0?) So, at application startup, should I create an individual dataset for each of the known reports, with some schedule (monthly) for refreshing the data? Or, should clicking on the report link on the home page, trigger building a temporary dataset for that specific report. Or, should there be just one dataset -- the summary table -- with links and functions to create subsets on the fly? What do I need to know to choose among the options? There aren't many users -- maybe 100 -- with most activity at the beginning of the month. Does this suggest one option over another?
Obviously, I need a lot more experience before I'll be able to do any real architecting on my own!
Thanks for any thoughts and comments.
Randy