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

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.|||I did not have this problem (identifier too long) until I tried to design aggregations with multiple partitions. None of my Oracle data exceeds the 30 character limit, so I'm kinda lost. It is new however, since I added some additional columns to my sql for the dsv and the partitions, but they do not exceed 30! I can process, I just can't design aggregations and I sure would like to. If anyone has any more ideas, let me know. AS 2005.

Problem with Excel 2000 and OLAP

Ciao,
I have created some cubes in my Analysis Server (SQL2000
Server) and try to retrieve the cube data by Excel2000.
In my own computer, when I create the data source in Excel, I
have not the options for "Microsoft OLE DB Provider for OLAP
Services" and "Microsoft OLE DB Provider for OLAP Services
8.0".

I have installed the Analisys Services..

Can anyone help? Thanks!

FabrizioHi Fabrizio,

below is the step i use retrive cube data from excal 2000.

1) in Excel 2000 click Data --> PivotTable and PivotChart Report
2) in pivot wizard step 1, choose external datasource.
3) click get data
4) click <new datasource> at OLAP CUBE tab
5) select OLAP Provider.
6) select Microsoft OLE DB Provider for Olap Services 8.0
7) click connect --> analysis server --> enter server name
8) it should display a list of OLAP database, select your database & cube.

cheers,
suhfui|||Sorry but I haven't Microsoft OLE DB Provider for Olap Services 8.0.
Why?

Thank you|||You need to install PTSFull (Pivot Table Services) on your client as well.

It can be found in SP3 for Analysis Services in the folder

sql2kasp3\msolap\install\PTS

the 8.0 version is available after that.

HTH Lutz

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…