Can I build a cube using a SQL data source and a DB2 data source

We are trying to build a cube using data from SQL and DB2. Is this possible and if so, how?

Here is the documentation on the officialy supported data sources for Analysis Services:

http://msdn2.microsoft.com/en-us/library/ms175608.aspx

Here is the relevant info about DB2

IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64) - only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition and downloadable as part of the Feature Pack for Microsoft SQL Server 2005 Service Pack 1.
|||I know I can build a cube using SQL as a datasource and Db2 as a datasource. What I need to know is whether or not I can build a single cube using a SQL datasource AND a DB2 datasource. In other words, can I have 2 datasources in the same AS project?|||

Sorry, I misunderstood your question :(

In general, yes you can have multiple data sources in the same AS project. In theory it should work with both SQL and DB2 data sources. But there are certain implementation details around multiple data source support, that make me cautious about DB2 though. I know that several SQL data sources will work without problem, but I won't be surprised if there will be some issues with DB2 as a second data source.

|||

I'm getting the following error when trying to build the dimension from DB2:

OLE DB error: OLE DB or ODBC error: Ad hoc access to OLE DB provider 'DB2OLEDB' has been denied. You must access this provider through a linked server.; 42000.

Any thoughts?

|||

AS uses OPENROWSET when there are multiple data sources, and probably due to security it is off by default on SQL Server side.

I beleive there is a DisallowAdhocAccess registry setting which should be set to 0 for DB2OLEDB provider. I beleive it can be found under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers

|||

Not sure whether you're using mainframe DB2, with a (18-char) limitation on identifier length - the issue that I ran into last year is that the SSAS DB2 cartridge (where handling of the 18 char limitation is specified) got bypassed when using SQL Server as a source with DB2. Haven't revisited this recently, to see if it's been fixed, but this issue was also mentioned in Teo Lachev's blog:

http://prologika.com/CS/blogs/blog/archive/2006/03/05/935.aspx

>>

Where Is My Cartridge?

A little known fact about the SSAS data architecture is that it uses “cartridges” to communicate with the data source. In brief, a cartridge is a XSL stylesheet that defines capabilities of a data source, as well as the rules for optimizing the SQL statements for relational querying and writing. SSAS 2005 ships with set of cartridges for Jet, SQL 70, SQL 2000, Oracle, Teradata, and DB2, which can be found in the \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges folder. Vendors can plug in (server restart required) cartridges for other data sources if needed.

One gotcha is when the UDM uses multiple data sources in a single data source view. This scenario requires that the primary data source must be SQL Server because behind the scenes the server uses the SQL Server-specific OPENROWSET statement to extract data from the secondary data source(s). The problem with this approach is that it effectively bypasses the installed cartridge for the non-SQL Server data source. As a result, processing queries that normally execute just fine when the DSV uses that data source only, fail to execute in a multi-data source DSV.

There are at least three workarounds for this predicament. First, you can replace each table in the DSV with a named query which uses the right native syntax. Second, you can link the data server to your SQL Server and wrap the linked server tables with SQL views. A third solution is to split UDM per a data source – a SQL Server UDM and another UDM for the second data source. Then, you can link the dimensions and measure groups from one UDM to another. As you have probably guessed it, all of the above approaches may present maintenance and operational challenges. It will be great if a service pack of a future release solve this issue and honors the cartridges with heterogeneous queries.

>>

|||

db1 - SQL Server 2005

db2 - DB2\AIX64

We are using the Microsoft OLE DB Provider for DB2.

On issue is encountered when defining the DB2 connection; AIX is not an option for the OS, so we are using DB2\NT.

I can get both Data Sources defined and the connections test successfully.

I can get both Data Views defined just fine.

When I build the Dimension using the DB2 Data Source, the only way to get it to process was to Check the box in the SQL Data Source definition that read something like 'Maintain a references to another object'. That actually changes the Provider in the connection string to DB2OLEDB in the SQL connection string. This does not appear to be correct.

We've checked the Registry and everything is fine there.

Any other thoughts?