July 30, 2008 at 3:52 am
There is a need to implement new functionality, I have some proposed solution and need your judgment on them:
Environment description:
- There is SQL Server 2005 Express database with 8 fact tables, ~20 reference tables and ~10 other not-fact tables (used for data-mart purposes).
- There is an ADP MS Access application that linked to that database and support complex user functionality for credit portfolio operation management along with loan provisioning; also specific reports are allowed to be created from that application.
- There are about 10 web reports (SSRS) that are sourced from that SQL Server database.
Request for a new feature to implement:
User wants for his MS Access application to have a feature of working with different data sets:
a) Actual (up-to-date) data set: that is currently updated by all users.
b) Lastly fixed period (at the end of each month): no major updates to date (almost no data inserts, a few updates);
c) Old reported periods: no data updates at all; user can only see data details with the help of application.
Proposed solutions:
1) Each time user decided to fix a data set period (once a month) a full backup is created and the restored as a different database and then user may choose what data set to work with; as an idea only last 6 month can be kept for user interaction.
Problems: I don’t know how to implement different periods with such approach for existent SSRS web reports.
2) Each time user decides to fix a data set period, no backup are created however all fact tables are copied to historical tables in the same database.
Problems: application should be redesigned dramatically to support different periods; additional filters for web reports need to be created; also some lookup reference tables for different periods must be stored as well.
Help needed:
Any comments or any other proposal are welcomed! In old time before this db system existed he used to work with huge Excel files with many sheets (so for fixed periods separate file copies were created); and I’m in desperate need to figure out how to implement this request using SQL Server (and Table Partioning for Enterprise edition isn't an option).
July 30, 2008 at 12:31 pm
If I'm reading your request correctly, it seems to me that a copy of the database for each period would be a bit of overkill. How about one copy of the database, with a "PeriodID" in the data, and a lookup table for the PeriodIDs, including what date-range they are? Would that do what you need? Then you just need a proc that, taking a start and end date, creates the PeriodID, and then copies the data to that database. If you include the PeriodID in the indexes, querying against it should be very, very fast.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2008 at 8:34 am
Well, the thing is that user needs to work with the whole set of data most of the time. The database itself is not a large OLTP database with thousands new records each day. Users want to see his whole data set at present time, and the he wants to turn the same whole data set but a couple of months ago (like a snapshot).
What you’re proposing is like my second proposed solution that will require a lot changes to existent client code.
I think that I will unify both of my proposed solution: for easy to work data maintenance I will user different databases on a single server (that way there will not much code changes in the client application); also I will keep historical records (maybe specific data-mart views) for Web-reports and will hold that at the current database, so user could still user existent SSRS reports connected to a single data source (that already will hold past reporting periods data).
I think it’ll work that way.
August 6, 2008 at 6:46 am
What about using a status field in the fact table?
The status field should be part of the key and has three values, according to your scheme.
Actual records have status 'actual'. When the user fixes the period, these records are copied within the same table with status 'fixed'. Just before this operation, records with status 'fixed' are copied to records with status 'old'.
In this way, the change to the web reports would be minimal, you only have to implement a filter for the status field.
Egon Rijk
August 7, 2008 at 2:34 am
I actually do something very very similar in an app i work on. We have a rates table that has rates for given services. These rates may change fairly often, but customers may get billed say every 6 months. Need to keep the historical data, but still have the current working set.
PeriodID is the way to go. Even if it might be slightly more work to impliment now, when you start talking about managing all these different databases, and backups, and everything else - the tradeoff is a little more work now (which you're getting paid for I assume, so that means more money) - or a potential management/dataloss issue down the road.
August 8, 2008 at 7:29 am
Egon Rijk (8/6/2008)
What about using a status field in the fact table?The status field should be part of the key and has three values, according to your scheme.
Actual records have status 'actual'. When the user fixes the period, these records are copied within the same table with status 'fixed'. Just before this operation, records with status 'fixed' are copied to records with status 'old'.
In this way, the change to the web reports would be minimal, you only have to implement a filter for the status field.
Egon Rijk
Egon, you’re talking about my (2nd) proposed solution. The things was that following this strategy I would need to rebuild many views that are based on those facts tables, and many of them are referenced to each other. It’s possible but it would require additional time for a complete application testing (which works fine now on a single data set). And it’s not transaction system with my case (but user rather wants to work with a complete database snapshot all the time).
Thanks for your help.
August 8, 2008 at 7:37 am
myke (8/7/2008)
I actually do something very very similar in an app i work on. We have a rates table that has rates for given services. These rates may change fairly often, but customers may get billed say every 6 months. Need to keep the historical data, but still have the current working set.PeriodID is the way to go. Even if it might be slightly more work to impliment now, when you start talking about managing all these different databases, and backups, and everything else - the tradeoff is a little more work now (which you're getting paid for I assume, so that means more money) - or a potential management/dataloss issue down the road.
I understand you Myke. As for my case, we agreed to have only 3 different data-sets (3 different databases on the same servers): Current data set, Fixed data set, Last Period data set. No much history of records is necessary (but rather a comparison analysis between last reported period and currently fixed one).
As for web-reports I’m planning to implement additional filter (parameter) to select which data set to use. All SSRS web-reports’ data sets will be bases on a Distributed partitioned views, that combined data from those 3 databases on the same server.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply