ODBC Source from 2 Database

[appleglaze28]

Is it possible to use databse table data for dimension creation or loading in TM1 TI when each table comes from 2 different databases but resides on 1 actual Database hardware. For example if I'm using MS SQL 2005 AdventureWorks & AdventureWorkDW sample database? If not...how do you accomodate such instances?

Code: Select all

SELECT 
AdventureWorks.HumanResources.Employee.EmployeeID,
AdventureWorksDW.dbo.DimEmployee.FirstName, 
AdventureWorksDW.dbo.DimEmployee.LastName,
AdventureWorks.HumanResources.Employee.Title,
AdventureWorks.HumanResources.Employee.MaritalStatus,
AdventureWorks.HumanResources.Employee.Gender,
AdventureWorksDW.dbo.DimEmployee.DepartmentName
FROM
AdventureWorksDW.dbo.DimEmployee,
AdventureWorks.humanresources.Employee
WHERE AdventureWorksDW.dbo.DimEmployee.EmployeeKey = AdventureWorks.humanresources.Employee.EmployeeID

[robchr]

You should be able to, but you might consider making a view and using ODBC to query the view. ODBC is a little bit flaky, so best to uncomplicate the query from TM1 to SQL Server.

You can even query databases from separate servers.

[jim wood]

I agree, I'm not sure how TM1 would a where statement when using 2 different databases.

[appleglaze28]

But for ODBC you need to create a ODBC Driver for each data source. So what's the best approach when you need to combine 2 data sources either for creating hierarchy dimension or cube loading.