Advice needed: DW and ETL tools

Hi!
I'm involved in building a medium sized ware house with MS SQLServer 2000=20
as the backend. I've been searching for some good tools for SQLServer to=20
help me with the demanding tasks of planning and building the database and =
(preferrably the same tool) making the mappings and data transformations=20
when populating the database, but still I've not come across any that has=20
satisfied my needs. Oracle has a great tool for this called Oracle=20
Warehouse Builder, but haven't seen anything like it for MS databases.=20
Therefore I'm asking: Does anyone know about a tool that can be used in=20
planning, deploying and maintaining a data warehouse built on a MS=20
SQLServer 2000? Doesn't matter if it has a price tag on it or not
S=F6ren Hakola
FinlandI think that anyone that uses SQL Server to deploy a Data Warehouse... uses
SQL Server 2000 capabilities only. And it's cheeper )
- Diagram: create logic models with direct link to the tables
- Tables: Info repository
- Data Transformation Services (DTS): ETL, data transformation (populate the
tables)
- Jobs: schedule DTS for automatic ETL
- Analysis Services: If you desire to work with a MOLAP tool
Other tools:
Data Stage (Unix and Windows, all databases) - Top tool, expensive
Informatica (Unix and Windows, all databases) - Top tool, expensive|||Thanks for the response. I'll check those out!
Greetings
S.sql

ADO Connection to Backend SQL Server

I am using MS Access forms as a front end to a backend SQL Server
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user. I have been able to establish the connection to the SQL Server
and have verified that the SQL statement is correct. I am completely
new to ADO and I can't figure out how to display the data returned in
the ADO recordset. Could someone please help me out with this? Is
there a way to display the returned recordset in an Access
form/datasheet to be viewable by the user? Below is my code for your
reference:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim Conn As Connection
Dim RS As Recordset
Dim LOC
Dim SQL
LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
SQL = _
"SELECT * From tblClaim"
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open LOC
RS.Open SQL, Conn, adOpenKeyset
Me.RecordSource = RS
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Much simpler to make use of the linked tables. Make the form's source the
linked table or use an access query based on the linked table.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
>I am using MS Access forms as a front end to a backend SQL Server
> Database. I am trying to use an ADO connection to return a Select
> Statement from the SQL Server to an Access form to be viewable by the
> user. I have been able to establish the connection to the SQL Server
> and have verified that the SQL statement is correct. I am completely
> new to ADO and I can't figure out how to display the data returned in
> the ADO recordset. Could someone please help me out with this? Is
> there a way to display the returned recordset in an Access
> form/datasheet to be viewable by the user? Below is my code for your
> reference:
> Option Compare Database
> Option Explicit
> Private Sub Form_Open(Cancel As Integer)
> Dim Conn As Connection
> Dim RS As Recordset
> Dim LOC
> Dim SQL
> LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
> SQL = _
> "SELECT * From tblClaim"
> Set Conn = CreateObject("ADODB.Connection")
> Set RS = CreateObject("ADODB.Recordset")
> Conn.Open LOC
> RS.Open SQL, Conn, adOpenKeyset
> Me.RecordSource = RS
> RS.Close
> Set RS = Nothing
> Conn.Close
> Set Conn = Nothing
> End Sub
>
|||Thanks Dave,
I am currently utilizing the linked tables to pass information to and
from the SQL server, but wanted to move to ADO connections so I could
get rid of the tables in the Access application. One of the tables
contains password info, so I didn't want there to be any way for a user
to access this table. Maybe ADO isn't the way around this; any links
or tips on securing linked tables in Access would be much appreciated.
-Ashley
Dave Patrick wrote:[vbcol=seagreen]
> Much simpler to make use of the linked tables. Make the form's source the
> linked table or use an access query based on the linked table.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <ashleycvernon@.gmail.com> wrote:
|||Personally I'd do the security on SQL server rather than trying to do in
Access. I'd use windows authentication rather than sql logins. You could
also link to a view rather than the table and setup security on the view
based on windows user and or windows group association.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
> Thanks Dave,
> I am currently utilizing the linked tables to pass information to and
> from the SQL server, but wanted to move to ADO connections so I could
> get rid of the tables in the Access application. One of the tables
> contains password info, so I didn't want there to be any way for a user
> to access this table. Maybe ADO isn't the way around this; any links
> or tips on securing linked tables in Access would be much appreciated.
> -Ashley