Storing statistical (user) data

Question

Storing statistical (user) data

Xxx

Right there with Babe

Points: 738
More actions
April 19, 2012 at 11:45 am

#392068

Hello,
I'm polling for some ideas here. I'm faced with a business requirement to store statistical information based on a handful of user defined tables (how many customers came in a day by store, average per sale, etc) that we can't affort to calculate on the fly.
My initial thought was to, on a daily basis populate a key/value type of table, then I realized this table will push about 5 to 10 thousand records per day and that may become non-performant. Another thought to reduce the amount of rows was a key/xml table where the XML would contain a series of statistics metrics per store and I would just give my application the XML to parse.
I don't hate the above ideas, but have a feeling there may be a better way (other than SSAS - not available in my situation)
Thanks!
-Tristan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

I don't see a problem with a nightly calculation and load into a summary table. 5-10 thousand records a day is nothing. We summarize millions each day, so this should not be an issue.

Jared
CE - Microsoft

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Tristan Chiappisi (4/19/2012)
Another thought to reduce the amount of rows was a key/xml table where the XML would contain a series of statistics metrics per store and I would just give my application the XML to parse.

Storing a key/value pair where the value is XML is an anti-pattern, i.e. it sounds good from the outset and solves an immediate problem but later you will find out how bad of an idea it was, and then it's too late. The finding out later part is when you are compelled to work with that data within SQL Server, or offer it up to a data consumer other than "your application", like, say, a canned reporting tool or Excel. If your data is somewhat structured, which it sounds like it is since you said you could do a key/value model where the value would not be XML, then I would go in that direction. 10,000 rows per day is nothing, don't worry about those volumes at all. With well-written SQL and proper supporting indexes on your tables SQL Server will handle it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Xxx Right there with Babe Points: 738 More actions · Answer 3

Good points! I another thing I was trying to avoid having to expand columns on my key/value table as the data has the possibility of becoming more complex (e.g. Stores with sales over 5,000 by district and store and some other metric), however, this seems to be the best approach.

Thanks for your time!

-Tristan