March 16, 2012 at 10:14 am
Hi,
I’m looking for some pointers on which approach I should take when redesigning the database element of an existing reporting application.
In the existing system we have a primary database where the data is inserted/updated/deleted. We then use transactional replication to regularly send these changes to a reporting database. This reporting database contains a subset of the tables/columns from the primary database and has been indexed in such a way as to improve reading data. The reporting software that uses this data performs various aggregations of the data held in this database.
This system has served us adequately for six years or so but is not starting to creek under the weight of data it is being asked to process. In the past we have gained performance increases by pre-calculating some of the most commonly used data and reading the stamped values. One option under consideration is to broaden the scope of this stamping exercise; however I am sceptical that this will provide us with long term gains. As such we are looking to revisit the way we store this data for reporting purposes.
The amount of data being processes, while not trivial, is certainly a lot smaller than other data warehouses I have seen spoken about. To give you an idea of the structure and size of the data being processed we have something like this:
Customer (~150000 rows)
CustomerID PK
Order (~4million rows)
OrderID PK
CustomerID FK
Order Lines (~120 million rows)
OrderLineID PK
OrderID FK
Value
The data in these tables is largely unchanged for most of the year with a large influx of data once a year. The data from these tables gets filtered, aggregated and summed with the results of these calculations displayed in a custom charting application. The primary goal of the new solution is to drastically improve the performance of report retrieval.
I have had a quick browse around and I think I should be looking at using OLAP cubes in SSAS to report on this data. Can someone please advise me if I am taking the correct approach with this? If this is the correct approach, could you point me towards some good tutorials or guides for defining dimensions etc? Also, any pointers on how this should be set up would be really helpful.
Thanks
March 16, 2012 at 10:18 am
SSAS is definitely worth looking in to for this.
As far as references on where to go and how, I'd check the Microsoft Press books on the subject, and then dig through Amazon on "data warehouse" and "ssas" searches. Which book(s) will be best for you is subjective.
- 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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply