Hi, I'm not sutre if I'm posting in the correct section. I want to know how I can perform data  warehousing & data mining tasks with SQL Server 2005.Which component in SQL Server (i.e. Database engine- I hope not this one or Reporting Services or Analysis Services ) you have to use in order to create a data warehouse?

And is there a seperate DMQL u have to use or u use simple sql???

please I need some answers on this immediately.....

I'll also try the User Group meeting presentations... thanks!!!

Hi, a data warehouse is a database which stores large amounts of stable, historical data; which are used for analysis and reporting. When creating a data warehouse using SQL Server, it is just a regular database that you have to create using the database engine, except that you would design it different from how you would design a (regular) OLTP database. The database design for a data warehouse would typically be designed using the dimensional model rather than the ER model (which is used for OLTP databases). This design results in a very simple database which is optmized for fast data retrieval to aid in analysis and reporting.

Once your data warehouse is done, it can be populated with data from various sources such as OLTP systems, Spreadsheets from Account departments, Text files from legacy systems etc. The data that comes from these sources would have to be first cleaned before loading onto the data warehouse. This can be performed using an ETL platform such as SQL Server Integration Services (SSIS).

The next step can be creating OLAP (Online Analytical Processing) databases using SQL Server Analysis Services. Here we create databases which are multidimensional in structure. These OLAP databases will be sourced by the data warehouse that was created earlier. Using the OLAP databases users can perform analysis and data mining to find out trends and patterns in the data. For this they can use SQL Server Reporting Services reports and Microsoft Excel as client tools. Also MDX (Multidimensional Expresssions) can be used to query and mine from these OLAP databases.

Reporting Services reports can also be used for querying data directly from the data warehouse, though you cannot use MDX on the data warehouse. There aren't any DMQLs for SQL Server, so you would have to use T-SQL for querying from the data warehouse. Which is why it is much more productive to use Analysis Services for analysis and data mining needs.

Hope this little introduction was informative enough. Also, there are lots of articles out there which have lot of info on designing data warehouses etc.

Cheers

Gogula G. Aryalingam (MVP - SQL Server)

Hey, what a nice explanation about the Microsoft data warehousing platform.   Anyway, I will add something here about DMQL, what bcj has mentioned. There is DMX (Data Mining Extensions) language is available on Microsoft SQL server for data mining purpose. Very simply you can work with DMX to manage data mining models with SSAS. You can get more info via below link.

More Info:http://msdn2.microsoft.com/en-us/library/ms132058.aspx

If we were supposed to write perfect code, debugger wouldn’t have been created.

hi,

i decided to include this link here as this talks about MS SQL Server Data Warehousing. While i was surfing the net, i found this article (2pages) and its really nice. It very well shows the strength of the MS SQL Server 2005 for data warehousing in business. Article title is "Accelerate Business Insight with SQL Server 2005 for Data Warehousing" and you can find it at http://download.microsoft.com/download/2/5/B/25B0BC65-92D7-4D78-8FE3-07E85E7241F7/SQL2005Datasheet_Datawarehouse.pdf

Thankx