problem with identifier lengths when connecting Analysis Services to Oracle

Hi All,

I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:

Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0

Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.

I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.

Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?

Any help would be appreciated.

Kind regards,

Miles

You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>

<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

Restart the server/tools

Note that this is not a supported technique but should unblock you...

HTH,

Akshai

|||

Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)

For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.

Kind regards,

Miles

|||Dear All,

I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.

The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||

Hi 4lb3rt,

Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.

Cheers,

Miles

|||Dear Miles,

I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?

Thanks in advance.sql