Here is the situation for my reporting requirements. Please suggest me how to proceed. I just have one of two options but which one to go is the question i want to ask all of you. May be you can suggest me something new.
Reports data needs to be almost live.
Report requirements are all Detail reports (not summary report)
Adhoc facility required for business users. Here also they expect live data(almost live)
Assume the number of rows fetched will be approx 100 thousand (initially)
We have two options
we will create a seperate database tables for reporting using DB2 Materialized query tables(MQTs). This needs frequent refresh of MQTs. I am not sure if this refresh might hit the front end application performance.
Use transaction tables for creating universe and then reports. Will the application performance/report refresh time be affected if we use transaction tables?
Then, how many rows available in the transaction database (approx) will/may affect the performance of report/application. Because i came to know that the Initially the data available in Transaction database can be approx 10 million rows
Sorry for this delayed update. Comments again please.
A transactional database is designed for efficiency of data capture.
A reporting (data mart/warehouse) is designed for efficiency of data retrieval.
There is a reason for both. Although reporting against transactional databases can be done, the complexity and size of this database really doesn’t make it an ideal database for reporting. Also, think of how the transaction system can be affected if a query locks a table during reporting. Now, I am sure they wouldn’t be too happy with that either.
Usually the reasons for not wanting a proper data mart or warehouse are the effort and cost involved in creating it. However, a poorly designed reporting database tends to be much more expensive in the long run.
For exteremely large ‘Data Vaults’ - look at Teradata - or alter your thinking re storing ‘everything’ in a typical ODS/DWH scenario.
i.e. this topic makes me think of two shopping scenario’s - a bachelor walking down the aisles with a trolley grabbing what he thinks he might need, compared to a women with a basket and a list that knows exactly what she wants and how much each item should cost.
It all goes back to requirements and costs - anything is possible these days with the technology that’s available and the old debates around ODS/DWH/EDH/ETL/Star Schema etc are busy being shaken up by architectural changes that are revamping DWH and BI as we know it.
“Real-time is anything that is too fast for your current ETL” - Ralph Kimball.
And what is wrong with that? Probably why most women manager their houshold budgets
On the serious side, I agree that the technology is bringing these concepts closer. However, it still boils down to real requirements (vs. wish lists) and cost. Afterall, a Lamboughini would be awesome to drive but if I can’t afford the car or the fuel it needs or won’t transport my soccer team to practice, then a minivan would be just fine as well.
Appreciate your comments. But here, we have limitations. We can’t get Teradata or anyother database. The only option is DB2. The budget is very low and they are not looking for anyother software/tools. Also, we tried convincing them to have seperate database which is refreshed every night. But they want live data for most of the reports and also for adhoc reporting.
(BOB member since 2006-12-13)
(BOB member since 2002-06-25)
(BOB member since 2005-01-06)
(BOB member since 2002-06-06)
(BOB member since 2006-02-08)
(BOB member since 2002-06-18)
(BOB member since 2002-06-20)