cannot use datasource column twice in ssas - IBMDB2

We develop ontop of IBMDB2 and cannot use a column twice in the model.

To work arround we have to add for every usage of the column and additional column with new name to an DB2 view and use the new column in the olap model - thats really annoying.

Does someone know a solution to solve this? because ontop of sql server you could use a column N times (for example for: name, key and some other attributes - in with DB2 we have to add 3 columns.) The Error Message we got is "Error in Backend Relational Model - OLEDB Reported unknown Status ' ' for column.".

Our System:

Analysis Services 2005 SP1 ENTERPRISE 64 Bit on Windows 2003 64 Bit connection with the IBMDADB2.1 64Bit OLEDB Provider from the IBM Client Version 8.2.4 to an IBM DB2 Database Version 8.2.4 running on LINUX 64 Bit.

Thanks for your help

HANNES

When do you get the error? Is it at processing time? You can see the query that Analysis Services sends to the Relational system and determine if the query is not formed correctly. This may give you clue to what is wrong.

Are you working around it in the DSV or in an actual DB2 view? An easier workaround would be to use the DSV and add the columns there.

David Botzenhart

|||

we get the error at processing time. see below for the full processing response. If we run the query at db2 directly its correct and working - so it must have something todo with anaylsis services or the oledb provider.

Mostly we work arround with the DB2 view..

This is the server processing state information

Thanks HANNES

====================

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>DWH_AT</DatabaseID>
<DimensionID>LU AGMT REQUEST SPEC</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Agreement request profile' failed.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
Processing Dimension Attribute '(All)' completed successfully.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:44:25; Duration: 0:00:00
Processing Dimension Attribute 'Longname' completed successfully. 573 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Product group' completed successfully. 19 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Asset category' completed successfully. 6 rows have been read.
Start time: 30.11.2006 09:44:25; End time: 30.11.2006 09:45:21; Duration: 0:00:56
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason code' completed successfully. 1698 rows have been read.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Processing Dimension Attribute 'Request reason' failed.
Start time: 30.11.2006 09:45:21; End time: 30.11.2006 09:45:21; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
"PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNAL_REFERENCE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_0","PIPE01_LU_AGMT_REQUEST_SPEC"."EXTERNE_DARSTELLUNG" AS "IPE01_LU_AGMT_REQUEST_SPEC0_1","PIPE01_LU_AGMT_REQUEST_SPEC"."NAME" AS "IPE01_LU_AGMT_REQUEST_SPEC0_2","PIPE01_LU_AGMT_REQUEST_SPEC"."CODE_GROUP_VALUE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_3","PIPE01_LU_AGMT_REQUEST_SPEC"."SPARTE" AS "IPE01_LU_AGMT_REQUEST_SPEC0_4"
FROM "PIPE01"."LU_AGMT_REQUEST_PROFILE" AS "PIPE01_LU_AGMT_REQUEST_SPEC"
Error Messages 1
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Errors and Warnings from Response
Fehler im Back-End-Datenbankzugriffsmodul. OLE DB hat den unbekannten Status '' für die 1-Spalte gemeldet.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten des Request reason-Attributs der Agreement request profile-Dimension aus der DWH_AT-Datenbank.
Fehler im OLAP-Speichermodul: Der Verarbeitungsvorgang wurde beendet, da die Anzahl von Fehlern, die w?hrend der Verarbeitung ermittelt wurden, die für den Vorgang maximal zul?ssige Anzahl von Fehlern erreicht hat.

|||

Hi Hannes,

I believe that developers in our area recently tested the DB2 v9.1 client driver (IBM OLE DB Provider for DB2, using IBM DB2 ODBC Driver version 9.01.00356), and it resolved this issue.

|||

We will test later on and I reply then

thanks Hannes