ETL and SSIS/SSAS roles in it all.

I am a student in a data warehousing class. This is my only experience using
SQL Server 2005 BI ETL tools. I need to extract, transform, and load into a
data warehouse using SQL Server 2005. I've extracted all tables from my 3nf
d.b. into flat files using SSIS. My question is about the connection
between SSIS and SSAS, and how I create the time dimension (in SSAS?) and
then have it connect back to my process in SSIS so I can do the final load
into it.
I thought I could use SSIS for the entire ETL process. Now I'm discovering
I somehow need to bring in SSAS to define the dimensions and somehow finish
up in SSIS again.
I defined my source d.b. and final destination data warehouse in regular SQL
Server tables. Do I need to delete the final destination data warehouse
database and redefine it as dimensions in SSAS?
Any help on this is appreciated. If you even have a recommended tutorial
that would help as well.
Hello Laura,
there are a number of ways to skin this cat.
The way I usually do it is
Build a relational DB (Star Schema)
Build an SSAS Cube over the top of that schema.
Use SSIS to load up the relation DB
In 2K5 you also though can load the SSAS partitions and dimensions directly
through SSIS which is way cool.

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am a student in a data warehousing class. This is my only experience
> using SQL Server 2005 BI ETL tools. I need to extract, transform, and
> load into a data warehouse using SQL Server 2005. I've extracted all
> tables from my 3nf d.b. into flat files using SSIS. My question is
> about the connection between SSIS and SSAS, and how I create the time
> dimension (in SSAS?) and then have it connect back to my process in
> SSIS so I can do the final load into it.
> I thought I could use SSIS for the entire ETL process. Now I'm
> discovering I somehow need to bring in SSAS to define the dimensions
> and somehow finish up in SSIS again.
> I defined my source d.b. and final destination data warehouse in
> regular SQL Server tables. Do I need to delete the final destination
> data warehouse database and redefine it as dimensions in SSAS?
> Any help on this is appreciated. If you even have a recommended
> tutorial that would help as well.
>
sql

ETL and SSIS/SSAS roles in it all.

I am a student in a data warehousing class. This is my only experience using
SQL Server 2005 BI ETL tools. I need to extract, transform, and load into a
data warehouse using SQL Server 2005. I've extracted all tables from my 3nf
d.b. into flat files using SSIS. My question is about the connection
between SSIS and SSAS, and how I create the time dimension (in SSAS?) and
then have it connect back to my process in SSIS so I can do the final load
into it.
I thought I could use SSIS for the entire ETL process. Now I'm discovering
I somehow need to bring in SSAS to define the dimensions and somehow finish
up in SSIS again.
I defined my source d.b. and final destination data warehouse in regular SQL
Server tables. Do I need to delete the final destination data warehouse
database and redefine it as dimensions in SSAS?
Any help on this is appreciated. If you even have a recommended tutorial
that would help as well.Hello Laura,
there are a number of ways to skin this cat.
The way I usually do it is
Build a relational DB (Star Schema)
Build an SSAS Cube over the top of that schema.
Use SSIS to load up the relation DB
In 2K5 you also though can load the SSAS partitions and dimensions directly
through SSIS which is way cool.
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am a student in a data warehousing class. This is my only experience
> using SQL Server 2005 BI ETL tools. I need to extract, transform, and
> load into a data warehouse using SQL Server 2005. I've extracted all
> tables from my 3nf d.b. into flat files using SSIS. My question is
> about the connection between SSIS and SSAS, and how I create the time
> dimension (in SSAS?) and then have it connect back to my process in
> SSIS so I can do the final load into it.
> I thought I could use SSIS for the entire ETL process. Now I'm
> discovering I somehow need to bring in SSAS to define the dimensions
> and somehow finish up in SSIS again.
> I defined my source d.b. and final destination data warehouse in
> regular SQL Server tables. Do I need to delete the final destination
> data warehouse database and redefine it as dimensions in SSAS?
> Any help on this is appreciated. If you even have a recommended
> tutorial that would help as well.
>

ETL "tool cost" question

I have been told that many ETL tools used to cost hundred of thousands of dollars. Is this true or were they referring to the ETL and Analysis tools, together? Can anyone provide some actual product names and estimated "costs" that hits these marks?

TIA,

barkingdog

Hi,

I know one. DataStage costs just $180,000 for 3 user license.

|||

It's difficult to get license costs for ETL products, as vendors typically offer a range of services / licenses / connectors / training etc.

Forrester Research published a good survey of the state of the market in 2004 - before SSIS was released. You can read a copy on Informatica's site - thanks INFA! http://www.informatica.com/solutions/resource_center/forrester_etl_wave04.pdf

The author of that research, also published an interesting note on SSIS - thanks Phil!: http://www.microsoft.com/sql/technologies/integration/foresterreport.mspx

Prices (and companies) have changed since then. However, SSIS still shows an exceptional price / performance and price / features ratio. (And don't forget that free database in every box.)

Donald

sql

ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
Paul
Hi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com

ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
PaulHi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com