Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ScottKaysee
Starting Member
1 Post |
 Posted - 2009-11-23 : 00:57:03
|
| BACKGROUND INFOI am a report designer in SSRS 2005 and currently I have to create a batch of reports off a few key tables. These tables aren't the most reporting friendly tables around and have the follow structure:<HEADER TABLE>- MAIN_ID- DESC- OPENED- CLOSED- CATEGORY1- CATEGORY2... and a few other fields I am not interested in.<DETAIL TABLE>- MAIN_ID (this is a FK to the same value above)- DETAIL_CODE- DETAIL_DATE- DETAIL_TEXT- DETAIL_VALUE... and a few other fields I am not interested in.The above is in a 1:M relationship between the HEADER:DETAIL. There are around 20,000 records in the HEADER TABLE and around 6,000,000 in the DETAILS TABLE.Typically, there are a bunch (100-300) "Detail" rows for each "Header" row. With each of them representing a particular detail with the DETAIL_CODE representig what that represents and the DETAIL_DATE, DETAIL_TEXT, and/or DETAIL_VALUE fields being used to store the data.Another thing is that different DETAIL_CODES are used for different CATEGORY1s, so CATEGORY1 = "SPORTING" might use DETAIL_CODES between "AAA100" to "AAA300"... and CATEGORY1 = "HAIRSPRAY" might use DETAIL_CODES between "HAIR30" to "HAIR70".so that is pretty much the background... I hope it makes sense.MY REPORTINGSo now I am designing a batch of reports to report again 1 particular CATEGORY1 (lets stick with the "SPORTING" theme). These reports will need to filter and sort voa a combination of the following:- show all "OPENED" headers (with their details) within a period.- show all "CLOSED" headers (with their details) within a period.- show all DETAIL_CODE = "AAA155" where the DETAIL_DATE is within a period.- show all headers (& details) where the DETAIL_CODE = "AAA273" and DETAIL_TEXT is like '%Hockey%'... and so on using a number of various combinations of DETAIL_DATE/TEXT/VALUE fields for filtering and grouping.POSSIBLE SOLUTIONI thought the best solution would be to create a "Master" dataset that would grab ALL the HEADERS and their DETAILS and then store them somewhere/somehow. Then right individual queries/reports to hit the "Master" dataset and apply the filtering needed for the different reports.QUESTIONS1) Is there a way to regularly run a query (say every 30 mins) that would "snapshot" that "Master" dataset and store it so my reports can then query that dataset?2) Could "Indexed Views" fulfill the above or not? 3) Is there something else I could do? I prefer NOT to create warehoused solution as it creates extra overheads/maintenance with the loading packages (another thing that can fail).Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-23 : 09:58:04
|
| i think what you can do is to create a denormalised table with whole details required for report and then populate using a sql procedure which will be scheduled to get executed after a regular interval. It will brand records with datetime value of population period and once population is over it will delete all records that are before current stamping period. |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-23 : 19:57:23
|
| My initial reaction is, "Where is your pain?" Do the reports currently run but they are taking too much time? Do they not currently run? Are the results inconsistent?If speed is your issue, I'd want to know what type of indexing is being used on the two tables.Can you give us a little more insight into your configuration?=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
|
|
|
I wish to "Snapshot" data.