asked
SQL 2008 Data Warehousing
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.
You may have BOL filtered to not return results from all categories. Search for OLAP, CUBE or BI for starters.
Thank you very much and 1 point for you both. To ask about the Management what they wanted? Well...that is Dangerous. To say that, I need to understand how to implement Data Warehouse before I go and ask them what they wanted. Otherwise I will end up in Trouble if I don't know how to do it. So, I need to make a bit practice. Let say Customer want to know about the Calls Details they made in last year, how can I get those done in DM? Thanks.
You would collect call data, customer data and clean it up, pushing the infromation into a couple of tables (on a separate machine/database). You then start to play around making sure you get the information you need from those tables. You would use Business Intelligence Development Studio to create the data model and work from there.
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][1]. Oleg [1]:
http://technet.microsoft.com/en-us/library/bb522607.aspx
One important point about DW that you allude to is that it is important to know that all the data going in is clean, in other words that it does not contain any inconsistencies or contradictions. That is the reason it is safe to denormalize.
I can also recommend using PowerPivot to give the management people "self service business intelligence" It is very easy to use once you have created a star schema.
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.
Excellent answer! :D I could not agree more! You have to know what a datawarehouse and what you want to do with it before you even consider implementing it. I wish I could give you more than one wote.
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][1] a while back about the advantages BI/cubes have over a DW and the experienced and knowledgeable people here explained the advantages well. [1]:
http://ask.sqlservercentral.com/questions/164/what-are-the-advantages-of-using-bi-cubes-over-a-regular-warehouse
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.
Check this out.... [Data Warehousing for Cavemen][1]. I wish everything was funny like this:-) [1]:
http://philip.greenspun.com/wtr/data-warehousing.html
