1

I need to build a Data Warehouse for an existing SQL Server database. I already have the design of the star schema dimension and fact tables. My question is:

Is there a tool in SQL Server 2008 Enterprise to help me with translating my data from the transactional database into the new data warehouse database? I am looking for a tool that helps me cleanup my data and populate the warehouse tables. I have done this before in an academic environment using Oracle databases. In this case I had to do everything "manually" with SQL.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
j0aqu1n
  • 1,013
  • 7
  • 14
  • 1
    When I Google this, SSIS comes up. There are even other questions in this site that mention SSIS related to this subject. I can create SSIS packages that I can run daily (for example) to do the rollup, but I haven't found how to use this effectively to do the initial ETL, which includes playing with the data to try different alternatives. Maybe I am just missing the elephant in the room. – j0aqu1n May 07 '12 at 16:08
  • There is no automagical solution. If there were, I would be skeptical because there would be many automatic transformations done by said solution that may ignore important business rules. SSIS is probably the best choice for your scenario. – brian May 07 '12 at 18:18

2 Answers2

1

You must check IMPORT and EXPORT functionality of MS - SQL SERVER, watch this video: http://www.youtube.com/watch?v=eDfhTPU7P-M

Ovais Khatri
  • 3,201
  • 16
  • 14
1

you should learn SSIS (not that hard) but first step is to load data. Quick and dirty way to do this is Import/Export Wizard - once you go through its steps, you can even save it as SSIS package and then tweak it as needed.

Some developers use SSIS heavily specifically data flows to clean-up and conform data. Some developers use SSIS only to bring data to a staging area (tables) and then would use SQL Stored procedures to transform/manipulate data on SQL Server.

mishkin
  • 5,932
  • 8
  • 45
  • 64