July 6, 2009 at 9:14 am
This might sound like a stupid Q but here goes
What is the prefered way of storing "transaction" data. So that when one has to report on it. It will not affect the speed of the report regardless of the volume of the data.
Regards
July 6, 2009 at 9:18 am
It isn't a stupid question at all. the problem I see with it is that it is extremely broad and vague.
Perhaps you could provide more background information regrading what you are trying to accomplish would help.
July 6, 2009 at 9:48 am
Most of the answer depends of the kind of reporting business is asking for.
A broad answer for the posted -broad - question would be: a table with indexes designed to serve reporting queries š
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 6, 2009 at 10:40 am
If you're looking to do some heavy duty reporting then you may want to consider implementing a data warehouse solution. Transaction is a common Fact table in a Star/Snowflake schema, as it is the most granular level of data and so can be aggregated up to higher levels in order to more adequately service reporting requirements.
Tell us a little more about your particular project and Iām sure we will be able offer more precise advice.
July 7, 2009 at 12:59 am
Thank you for the feed back so far.
The type of reporting is of a finical nature. So there would be ledgers, statements, trial balances etc.
The transaction data contains the following info: ClientID, subclientID, deditAccountID and creditAccountID. It contains other info such as amount, units, narrative, doc ref etc.
But the majority of the reports are filtered/grouped on a client, subclient or accountID . ie client + accountID or subclient + accountID or just subclient or just client or just accountID etc.
Hope this is enough info
July 8, 2009 at 10:39 am
Hi,
Best way would be to design a star-schema database and then load your data from the OLTP db to this star-schema database using ETL process. ETL process could be implemented using tools such as SSIS, Informatica etc.
Hope this helps.
Thanks,
Amol
Amol Naik
July 9, 2009 at 12:45 am
Chek out if table partion can hlep in your scenario.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
July 9, 2009 at 12:45 am
Chek out if table partion can hlep in your scenario.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply