Transformer and MSSQLServer 2000 1

[Shriniwas]

Hello Friends,

I have written a macro which creates a cube. But the datasource is MSAccess database. I want to shift to MSSQLServer 2000. I want to get the fact table data which is in SQLServer 2000. How can I go for it? Can transformer talk to SQLServer 2000? If I use a transformer application for creating a cube, even then I am not able to see SQLServer as the option. Looking forward to your suggestions.

Thank you in advance
-Shriniwas

 

[GJParker]

As far as I am aware Transformer cannot extract data directly form SQL server you will need to extract your data using Impromptu to create an iqd file or some other data extraction tool. Or if you don't want to do that you could use Access to link int SQL server,

Good Luck

 

[Shriniwas]

Hello

I tried to create link tables in access for corresponding table in sql server. One of disadvantage is that, it does not reflect change in table structure automatically. You have to manually refresh the link. another thing is, while automating the cube creation process, I need to link all tables in sql server into access database. So for my application, practically I would be duplicating almost entire database into access. So my line of thinking was, if impromptu can talk to sqlserver then there has to be some way that transformer should talk to sqlserver.

Please share your knowledge.

Thank you
-Shriniwas

 

[PaulBirch]

Hi,

You don't want to pull your data into Transformer via Access, for exactly the reasons that you have said. What you need to do is:

1. Create an ODBC data source that can connect to the SQL Server.
2. In impromptu create a catalog which contains all the tables your required, all joins etc.
3. Create some impromptu reports to get the information from the tables which reflect the same way you have done it in Access.
4. Save the impromptu reports as IQD files.
5. Use the IQD files as the data sources for your transformer model.

If there are any major changes to the database sturcture ie, a new field is added - which you need to see in transformer. You will need to add the field into the catalog and then add it into the corresponding impromptu report (IMR). Then resave your IQD file and run a "check columns" in Transformer.

Hope this helps.

Regards,

Paul.