Model Designer - ODBC Data Source

I want to create a Model against an ODBC data source.

This is not possible in the June CTP.

Will this facility be available in a future release?

Thanks,
JoeI've raised this as a call with Microsoft through our Partner Program.

Will post the result here when I get an answer.|||As promised here is Microsoft's reply:

Please find the answers to your queries below:

Query:

=======

You want to be able to create an Ad-hoc Report Model using the Model Designer in Visual Studio 2005.

You want to be able to use an ODBC data source for this model - currently the only option is to use the “SQL Client Data Provider”.

So you need to know whether this will be addressed in the final release of SQL Server 2005 (or any CTPs of SQL Server 2005 before then).

Solution:

==========

As of now only SQL and Analysis Services are directly supported as a data source for Report Builder.

However you can connect to the ODBC data source using SQL or Analysis Services. We have the following two ways to get this working:

Option I:

-

UDM (Unified Dimensional Model) is essentially a way that SQL Analysis Services 2005 can combine multiple sources of data into one model. Let’s say your “Sales” information is one an Oracle data store, your “HR” info is in SQL, and your “Inventory” is in DB2 – You would build a UDM which includes all three of these data sources, and then you could report against the UDM as if ALL of the information lived inside Analysis Services.

So, by using a UDM to point to the ODBC data source, you enable Report Builder to get at the data too, since Report Builder CAN report against Analysis Services.

Option II:

--

You could try this but the performance expected is not very good with this option:

1. create linked servers in order to reference the ODBC (or other) data sources

2. create a layer of views in a SQL Server database accessing the other database through the linked server mechanism. You could use just simple views to expose the contents of the underlying tables with 'select *'. For example:
create view MyODBCTableView as select * from LinkedServerName..SchemaOwner.TableX

3. build SQL Server views on top of these views as desired

4. build the Report Model against these SQL Views

5. use Report Builder to query the OBDC data source through this 'proxy'

The following link is useful for more information and resources on SQL Server 2005:

http://www.microsoft.com/uk/partner/sol_and_products/servers/sql/

|||Unfortunately the chaps from Partner Tech Support were misinformed.

This is a reply from MSAS newsgroup :

AS2005 is not going to support ODBC data source. However, you can try to
create a data source in SSIS project. Then, add the data source into new AS
project. You should be able to create DSV, cube and dimension, etc.

However, since this feature is not supported. Believe that this area has not
been tested enough and this may break engine or not working probably. It is
"AS IS" if you really want to use it.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Kwok
SQL Server Analysis Services