Hi Everyone, Can you explained me about Data Warehousing in SQL 2008? I tried to BOL but they are saying about DMV (Data Collection). But I believed we can do more than that. But I am not sure how to implement this. Let say..I work for Telecom, how can I implement Data Warehousing and where can I use this? Thanks.
If you work for telecom then you have data stores with extremely high volume of new data constantly being inserted. You probably have to deal with high number of "usage" databases as well. All this makes querying and aggregating data for reporting purposes rather cumbersome, and this is where data warehousing comes in. In two words, DW is a way to store data in (at least somewhat) denormalized format so it is suitable for reporting. If you don't want to query live data from usage databases to, for example, have a graphical report showing trunk utilizations rates in your switch rooms for the last year pivoted by months then you would consider installing SSAS and creating DW. P.S. Data Warehousing is a huge topic, and it is well worth studying. You can begin from [here]. Oleg :
Leo. I am going to go out on a limb and hope you have not been thrust into building a data warehouse after management read the latest magazine article and said "hey - we need this now". You need to find out what exactly you are wanting to monitor/analyse with your data warehouse. The first step, as far as I am concerned is to find out what management wants to know. Collect a question catalogue and try to group them together in terms of what the central focus for the question is. Example: Telecoms Company XY with 5 million subscribers may want to know the gender ratio, the location of their customers by home address, the location of their customers by cell (where they went with their phone), the calls they made (duration, distance from home etc) Once you have these questions sorted out, you can then go into finding out where to get these answers from (got technical docu?!). At that point you would start to find out that there are a huge number of extra questions that could be answered that have not been asked. You can score BIG points by then suggesting these questions to management. Once the data has been identified, a data model created to pull this data together, a cube built and a few reports published, you will be about 18 months older (physically), 36 months older (mentally) and about 60 months older (psychologically). I can only say, starting a data warehouse project without any management impetous/buy-in will be a waste of time and money. You can suggest something along the lines of raw data analysis, but the main questions/requests need to come from the decision makers at your company. You don't build a data warehouse on a whim and in one weekend.
Oleg and William both have good insights. I don't have much to add, but a couple of points here since I work with a DW every day. How often will the DW be updated - once a month? Be sure to consider a hybrid solution where the main data is updated completely on a monthly basis, but maybe total amounts (hours, dollars) are refreshed more frequently, maybe once or twice a week. That way, the critical data can be updated without having to run the entire, potentially lengthy and involved, process. Also, I want to stress what Oleg said about denormalization. Usually DW are used for reporting, so consolidate as much of it as you can into wide tables (well, within reason anyway). On the other hand, you may not want to build a DW - you may want to use cubes instead. I explicitly asked the [question here] a while back about the advantages BI/cubes have over a DW and the experienced and knowledgeable people here explained the advantages well. :
I agree with William and Oleg. You need to know what the management want to know. Of course, you can train on how to do it, but you'll learn the most when you actually try to implement what they want. You left a comment about customer and their details about phone calls a year ago, remember that you may need to take legal regulations into the equation. In Sweden, you are not allowed to store detailed information about calls made more than 3 month ago if the bills are paid.