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?

Can a stored procedure use the OpenRowSet function to get DB2 data

If so, does anyone have an example?
Background: We have a MSFT Reporting Services report that uses the
OpenRowSet function to get both DB2 and SQL Server data in one SQL statement
.
Since userid and psw are parms to this function, it is not an option to
hard-code them into the OpenRowSet parms.
So our solution is to write a stored procedure that the report will call.
This stored procedure will have the SQL statement, including the OpenRowSet
function WITH the userid and psw hard-coded in, and then the stored procedur
e
will be encrypted.
I'm presently writing the stored procedure and read the following in the
article entitled "External Data and Transact-SQL" (SQL Server books online):
"Stored procedures are supported only against SQL Server data sources."
Here's the stored procedure I'm writing. It doesn't pass the Syntax Checker
yet, so whatever assistance anyone can provide would be great.
Thanks!
========================================
==========
CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
@.inst varchar,
@.stat char(1),
@.ret_cd char(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @.err_cd smallint,
@.rowcount int,
@.error int
SET @.ret_cd = 0
SELECT DISTINCT DB1.EFF_STAT,
DB1.FIN_INST_NM,
DB1.ADDR_LINE_1,
DB1.ADDR_LINE_2,
DB1.ADDR_LINE_3,
DB1.ADDR_CTY,
DB1.ADDR_ST,
DB1.ADDR_POSTAL_CD,
DB1.PHONE_NR,
DB1.CONTACT_NM,
DB1.ACCT_NR,
DB1.ACCT_NM_1,
DB1.ACCT_NM_2,
DB1.PERMVAL_NM,
DB1.EMP_AUTH_NM,
DB1.NE_AUTH_NM,
DB1.NON_EMP_INDIC,
DB1.COUNTRY
FROM OPENROWSET
('IBMDADB2'
,'dsnp_r';'userid';'passwd'
,'''SELECT DISTINCT DBT1.EFF_STAT,
DBT3.FIN_INST_NM,
DBT3.ADDR_LINE_1,
DBT3.ADDR_LINE_2,
DBT3.ADDR_LINE_3,
DBT3.ADDR_CTY,
DBT3.ADDR_ST,
DBT3.ADDR_POSTAL_CD,
DBT3.PHONE_NR,
DBT3.CONTACT_NM,
DBT1.ACCT_NR,
DBT1.ACCT_NM_1,
DBT1.ACCT_NM_2,
AUTH.AUTH_STAT,
AUTH.PERMVAL_NM,
AUTH.NON_EMP_INDIC,
AUTH.EMP_AUTH_NM,
AUTH.NE_AUTH_NM,
DBT50.PERMVAL_NM AS COUNTRY
FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
, A.NON_EMP_INDIC
, C.NAME AS EMP_AUTH_NM
, D.NAME AS NE_AUTH_NM
, A.EFF_STAT AS AUTH_STAT
, B.PERMVAL_NM
FROM TRSPROD.CTRS04_AUTHRITY_TB A
INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
LEFT JOIN BACPROD.PS_PERSONAL_DATA C
ON A.EMPLID = C.EMPLID
LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
ON A.EMPLID = D.NON_EMPLID
WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
FROM TRSPROD.CTRS04_AUTHRITY_TB E
WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
AND (DB1.AUTH_STAT = ''A'')
AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
ORDER BY DB1.EFF_STAT
, DB1.FIN_INST_NM
, DB1.ACCT_NR
, DB1.PERMVAL_NM''
) DB2')
Select @.error=@.@.error, @.rowcount=@.@.rowcount
IF @.error <> 0
BEGIN
RAISERROR('Read of database failed ',16,1)
SET @.ret_cd = 1
RETURN 1
END
IF @.rowcount = 1
BEGIN
SET @.ret_cd = 0
RETURN 0
END
SET NOCOUNT OFF
RETURN 0
/ ****************************************
***********************************
********************
******************************* End of Procedure SQL
****************************************
***
****************************************
************************************
*******************/
END
GO
========================================
============
--
Joe Palm
Senior Technical Developer
Madison, WICan you add a linked server?, this way you do not have to embed the userid
and password in your query.
Creating a linked server to DB2 using Microsoft OLE DB provider for DB2
http://support.microsoft.com/defaul...kb;en-us;222937
AMB
"Joe Palm" wrote:

> If so, does anyone have an example?
> Background: We have a MSFT Reporting Services report that uses the
> OpenRowSet function to get both DB2 and SQL Server data in one SQL stateme
nt.
> Since userid and psw are parms to this function, it is not an option to
> hard-code them into the OpenRowSet parms.
> So our solution is to write a stored procedure that the report will call.
> This stored procedure will have the SQL statement, including the OpenRowSe
t
> function WITH the userid and psw hard-coded in, and then the stored proced
ure
> will be encrypted.
> I'm presently writing the stored procedure and read the following in the
> article entitled "External Data and Transact-SQL" (SQL Server books online
):
> "Stored procedures are supported only against SQL Server data sources."
> Here's the stored procedure I'm writing. It doesn't pass the Syntax Check
er
> yet, so whatever assistance anyone can provide would be great.
> Thanks!
> ========================================
==========
> CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
> @.inst varchar,
> @.stat char(1),
> @.ret_cd char(1) OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.err_cd smallint,
> @.rowcount int,
> @.error int
> SET @.ret_cd = 0
> SELECT DISTINCT DB1.EFF_STAT,
> DB1.FIN_INST_NM,
> DB1.ADDR_LINE_1,
> DB1.ADDR_LINE_2,
> DB1.ADDR_LINE_3,
> DB1.ADDR_CTY,
> DB1.ADDR_ST,
> DB1.ADDR_POSTAL_CD,
> DB1.PHONE_NR,
> DB1.CONTACT_NM,
> DB1.ACCT_NR,
> DB1.ACCT_NM_1,
> DB1.ACCT_NM_2,
> DB1.PERMVAL_NM,
> DB1.EMP_AUTH_NM,
> DB1.NE_AUTH_NM,
> DB1.NON_EMP_INDIC,
> DB1.COUNTRY
> FROM OPENROWSET
> ('IBMDADB2'
> ,'dsnp_r';'userid';'passwd'
> ,'''SELECT DISTINCT DBT1.EFF_STAT,
> DBT3.FIN_INST_NM,
> DBT3.ADDR_LINE_1,
> DBT3.ADDR_LINE_2,
> DBT3.ADDR_LINE_3,
> DBT3.ADDR_CTY,
> DBT3.ADDR_ST,
> DBT3.ADDR_POSTAL_CD,
> DBT3.PHONE_NR,
> DBT3.CONTACT_NM,
> DBT1.ACCT_NR,
> DBT1.ACCT_NM_1,
> DBT1.ACCT_NM_2,
> AUTH.AUTH_STAT,
> AUTH.PERMVAL_NM,
> AUTH.NON_EMP_INDIC,
> AUTH.EMP_AUTH_NM,
> AUTH.NE_AUTH_NM,
> DBT50.PERMVAL_NM AS COUNTRY
> FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
> INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
> ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
> LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
> , A.NON_EMP_INDIC
> , C.NAME AS EMP_AUTH_NM
> , D.NAME AS NE_AUTH_NM
> , A.EFF_STAT AS AUTH_STAT
> , B.PERMVAL_NM
> FROM TRSPROD.CTRS04_AUTHRITY_TB A
> INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
> ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
> LEFT JOIN BACPROD.PS_PERSONAL_DATA C
> ON A.EMPLID = C.EMPLID
> LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
> ON A.EMPLID = D.NON_EMPLID
> WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
> FROM TRSPROD.CTRS04_AUTHRITY_TB E
> WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
> ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
> LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
> ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
> WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
> AND (DB1.AUTH_STAT = ''A'')
> AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
> ORDER BY DB1.EFF_STAT
> , DB1.FIN_INST_NM
> , DB1.ACCT_NR
> , DB1.PERMVAL_NM''
> ) DB2')
> Select @.error=@.@.error, @.rowcount=@.@.rowcount
> IF @.error <> 0
> BEGIN
> RAISERROR('Read of database failed ',16,1)
> SET @.ret_cd = 1
> RETURN 1
> END
> IF @.rowcount = 1
> BEGIN
> SET @.ret_cd = 0
> RETURN 0
> END
> SET NOCOUNT OFF
> RETURN 0
> / ****************************************
*********************************
**********************
> ******************************* End of Procedure SQL
> ****************************************
***
> ****************************************
**********************************
*********************/
> END
> GO
> ========================================
============
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI

Can a stored procedure use the OpenRowSet function to get DB2

Linked servers was our first thought, but we don't have that. We'd have to
purchase MSFT Integration Server to get linked servers to work w/our DB2
databases, and we haven't purchased that.
So the linked server route is not an option for us at this time.
Other ideas?
--
Joe Palm
Senior Technical Developer
Madison, WI
"Alejandro Mesa" wrote:
> Can you add a linked server?, this way you do not have to embed the userid
> and password in your query.
> Creating a linked server to DB2 using Microsoft OLE DB provider for DB2
> http://support.microsoft.com/defaul...kb;en-us;222937
>
> AMB
> "Joe Palm" wrote:
>Joe,
The report will pull the data executing a sp in sql server, so that is not
an impediment. The problem with this solution is that encrypting the code of
the sp is not as safe as we think and the userid and password can be taken
from that code.
How can I decrypt a SQL Server stored-procedure?
http://www.mssqlcity.com/FAQ/Devel/DecryptSP.htm
AMB
"Joe Palm" wrote:
> Linked servers was our first thought, but we don't have that. We'd have t
o
> purchase MSFT Integration Server to get linked servers to work w/our DB2
> databases, and we haven't purchased that.
> So the linked server route is not an option for us at this time.
> Other ideas?
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI
>
> "Alejandro Mesa" wrote:
>