September 6, 2008 at 10:44 pm
hi guys, I have a database which is used for all our transactions as well as for all our data storage. Then we have a separate replicated database which we use for all our reports from our application. Now, i have been asked to create a large report (over 100 columns from different tables and also it has many calculations) so i decided that the best way to go about this is to create a large table which will contain all this data and run a ssis package to capture all the data needed and the transformations. We only need the new updated information once a day so i could make the job run once a night and that will be ok. My question is , is this considered data warehousing? if so, would the best way to go about this is to a separate database just for data warehousing? should all these data be in one table or different tables?
September 7, 2008 at 4:00 pm
My thoughts are that if you are being asked for a report that contains 100 columns, you can bet that someone somewhere is doing a hell of a lot of work on the output in order to present pertinent data. A report such as this sounds completely insane...
My advice would be to try to discover what is being done with the output, and see if you can't actually produce the real end result via a report or a set of reports.... with a properly architected data warehouse it should be possible to produce summary aggregated reports and let the end user drill in to the summary in order to investigate areas of interest, rather than carry on this practice of producing vast reports of a high level of detail...
Some more information would probable help the people here advise you in grater detail what the best course of action shoudl be.
Kind Regards, Will
September 7, 2008 at 9:53 pm
Thank you for your response after having a couple of meetings we agrreed the report should only have 50 essential colums. These data is not summarized, What other consiserations should I have to find out if in fact this is a data warehouse? In data warehouse since this is related data it should be in only one table? Is data warehousing best practice to keep a separate database?
September 8, 2008 at 4:38 am
It will depend on your definition of a data warehouse. Some people will tell you that this is a data warehouse, some will not. I tend to be reasonably lax in my definition and would consider this a data warehousing effort, but it is certainly not very close to a traditional data warehousing solution.
As far as putting it into another database, that will depend on how you want to handle backups, restores, and maintenance. Would you want to be able to restore a version of this 100 column table that is different than the version of your reporting database? If so, perhaps putting it into it's own database makes sense. However, if you are going to take the lax view of data warehousing, the entire reporting database is part of your data warehouse, so leaving them together makes sense as well.
I also agree with everyone else. Any report with more columns than you can view on a landscape 8.5x11 page is probably worthless. It is very likely that this will become a spreadsheet that someone will create a number of other reports from. I would take the time to figure out the end reports and create those rather than some crazy spreadsheet. Remember, end users do not know what they want. They ask for everything because they fear they may need it and get told they cannot have it later. Building a proper data warehouse so it becomes easy to expose additional attributes can build their confidence and allow you to create more useful reporting up front.
September 8, 2008 at 5:23 am
DBA (9/6/2008)
My question is , is this considered data warehousing?
No, it is not.
_____________________________________
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.September 8, 2008 at 6:43 am
Data warehousing involves a lot more than creating a bigger table. So my answer is No.
September 8, 2008 at 6:53 am
The two most accepted definitions are from Inmon and Kimball. Inmon describes it basically as the product while Kimball describes it as the entire process as well as the product.
Bill Inmon: "A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process"
Ralph Kimball: "Data warehousing is essentially what you need to do in order to create a data warehouse, and what you do with it. It is the process of creating, populating, and then querying a data warehouse and can involve a number of discrete technologies"
Here is a complete article:
http://www.intranetjournal.com/features/datawarehousing.html
September 9, 2008 at 8:42 am
No, it is not a data warehouse. I don't think that it even meets any definition I can find of a data mart.
September 9, 2008 at 9:56 pm
I agree this is not a data warehouse but I can't help but wonder what would happen if the large table was put thru a simple normalization process. The result might end up looking like a fact table and a number of dimension tables.
One can't tell without seeing and understanding the actual data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 10, 2008 at 1:21 am
I also agree.
What are the users (customers) of the data needs in reporting and analysing data (to make information)?
I make a difference between a report (something which is stable over a time like an anual report or monthly
report to "upper" management including relativly little an aggregated information and
"Data to be analysed". There you need a tool which can be Excel (filled with data an filering/pivoting capability)
or OLAP (cube tools) or tools like QW (QlikView) or HAT (Highspeed Analysing Tool) working on details.
The value is in the data so organize your DW (datamodel) so it will survive over the time (organization changes etc)
and make it possible to use different output tools.
September 10, 2008 at 5:03 am
Assuming the table has a date field indicating when each row was added and rows cannot be deleted, it meets Inmon's definition just fine.
In both definitions, the final format of the data has nothing to do with whether or not it is a data warehouse. Neither specify you need fact and dimension tables, or even tables at all. Both definitions even allow for a vertical database rather than records in rows.
September 10, 2008 at 9:00 am
Michael Earl (9/10/2008)
Assuming the table has a date field indicating when each row was added and rows cannot be deleted, it meets Inmon's definition just fine.In both definitions, the final format of the data has nothing to do with whether or not it is a data warehouse. Neither specify you need fact and dimension tables, or even tables at all. Both definitions even allow for a vertical database rather than records in rows.
... or just a dated piece of paper you really-really promises not to destroy, isn't it? 😀
_____________________________________
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.September 10, 2008 at 9:06 am
I suppose so. Neither definition even specifies a computer be involved.
If you had a shelf with an abacus for each week displaying revenue, as long as you promose to not go change an old abacus you could probably fit the Inmon definition.
The backup process may be a bit tricky though.
September 10, 2008 at 9:09 am
The backup process may be a bit tricky though.
Backup of the abacus would be easy. Just take a picture and store it in a safe place.
😉
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 10, 2008 at 9:13 am
Alvin Ramard (9/10/2008)
Backup of the abacus would be easy. Just take a picture and store it in a safe place.;)
That would be good for DR... you can send the picture to your remote location and have somebody set a second abacus as the one that shows on the picture 😎
_____________________________________
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.Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply