Here's my situation. I work for a county clerks office which is responsible for storing court and official records. My job is to allow internet access to these records via a search page. Right now I am running queries against a replicated database. This can be slow considering users may be running a query with multiple joins for last name Smith against a database with > 6 million records. I would like to have a large denormalized table containing just the information I need to display, with one or two related tables. The question is, what is the best method to do this? This is not normal business information like cost and sale information. It's information about cases and the parties involved, so there are no measures to create a fact table in analysis services. The information is also dynamic since cases go on for years and can be reopened so it isn't true historical data. I could recreate the table on a daily basis, but I know there has to be a better way.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Should I use analysis services or DTS
- Thread starter jterhune
- Start date
johnherman
MIS
First, there is always a fact table. There are, for instance, what are known as factless fact tables. These are usually event tables, and your cases and parties are likely that kind of table. So you have dimensions such as case and party and "facts" which are events regarding a case. Case Opened, Case Resolved, Case Reopened, Case Appealed, Case Settled Out of Court, etc. These events are transactions (facts) regarding the case and parties.
Since you are currently using a replicated database, I assume you are actually using replication, which is a ggod way to streamline your daily updates. Then you need to run another process to format the data for presentation, and your idea of denormalized structures seems to work. You may want to reconsider Analysis Services based on an event or transaction type fact table (as opposed to ordinary facts such as cost, profit, etc).
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
Since you are currently using a replicated database, I assume you are actually using replication, which is a ggod way to streamline your daily updates. Then you need to run another process to format the data for presentation, and your idea of denormalized structures seems to work. You may want to reconsider Analysis Services based on an event or transaction type fact table (as opposed to ordinary facts such as cost, profit, etc).
-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
Hi!
I think You can use Starshema with 2-3 dimensions,
Time, Status, Custemer. On Customer put First,Last name e.t.c and index this item. On Status open,closed, avalible, not avalible e.t.s.On Time lavel You can chose
dayli lavel e.t.c
On Fact put Count(*) as number av records.
Bild dimention and Fakt dayle.
Best Regards
Inna Stern
I think You can use Starshema with 2-3 dimensions,
Time, Status, Custemer. On Customer put First,Last name e.t.c and index this item. On Status open,closed, avalible, not avalible e.t.s.On Time lavel You can chose
dayli lavel e.t.c
On Fact put Count(*) as number av records.
Bild dimention and Fakt dayle.
Best Regards
Inna Stern
- Status
Similar threads
- Locked
- Question