Duplicate column names using SQL Server - OPENQUERY

[natb]

Hi there,

I am wondering if anyone ever tries querying TM1 data from MS SQL Server (SSMS) using OPENQUERY. We have TM1 cubes and are planning to use TM1 writeback abilities to help with some modelling and forecasting. Once users finish forecasting their data, we would like to create reports using MS SQL Server Reporting Services as most of our data stored on SQL Server database.

Anyway, what I am testing is just querying TM1 data from SSMS. I know that my MDX is correct and it works as I can get the result back when I tested it with MDXSample.exe, which comes with SQL Server 2000, see attached image. It also works in the MS SQL Server Reporting Services (SSRS) but show duplicate column names, see attached image. When I do the query from SSMS using OPENQUERY, I receive an error:

Msg 492, Level 16, State 1, Line 1
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "[measuresCapex].[Invoice Amount][Version].[All Version^ACT].[Months].[All Months^Mar]." is a duplicate.

My query is as following:

select *
from openquery(tm1dev13, '
select
CROSSJOIN (
CROSSJOIN(
{[measuresCapex].[Invoice Amount],[measuresCapex].[Monthly Budget Amount]}
,{[Version].[Actual],[Version].[Budget]}
)
,{[Months].[Mar],[Months].[Mar YTD]}
)
on columns,
(CROSSJOIN(
{TM1FILTERBYLEVEL({TM1SUBSETALL([dimState])},0)}
,{[Job Code].[All Capex Types].children}
))on rows
from [Capex Register]
WHERE ([Years].[2008],[FX].[AUD])
')

Please could you let me know if this is a bug and whether there is a HotFix for it. It looks like with OPENQUERY and SSRS, the result is returned as recordset and somehow the column names cannot be shown correctly; perhaps, due to limitation to number of characters that can be shown in the column name of SSMS? But I also wrote a query against Analysis Services cube using CROSSJOIN on columns and getting result back without any problem with duplicate column names.

Cheers,
Nat

[ScottW]

This is an intriguing approach, why would you not report directly out of TM1?

You could also schedule a publish with ODBCOutput to write the TM1 data to an SQL Server table, this might be a simpler approach.

[David Usherwood]

TM1's implementation of ODBO and MDX can politely be described as 'quirky'. I suggest you contact Cognos and get a formal response from them concerning whether they support TM1 as a data source for MS Reporting Services - I suspect the answer will be no. Maybe it will become more standard - I heard Dave Corbett saying at the conference this year that the intention was to make TM1 more 'OLAP compliant' probably because he couldn't use the M word.

I agree with Scott - export the data to SQL Server tables and report it from there.

The connectivity I really miss is being able to link TM1 servers together via MDX. It kind of works, though dimensions are unpleasant. The last time we tried it on volume data it didn't scale well at all.

[George Regateiro]

As far as contacting Cognos, good luck. I placed an ticket in a few months ago asking for similar information, but did not have much luck. All is was given was a power point that did not offer a whole lot of help. None of their support staff was willing to confirm or deny any support. The first two did not even know this was a possibility.

I was able to get it working somewhat, but you will find that the reporting options are not very good. As a result of the afore mentioned quirky nature the reports do not translate as nicely as one would like and end up being fairly limited. I also found this to be the case with other reporting tools. I need to update the other post, but I was able to connect through Dundas, but I faced the same limitations that I faced through Reporting Services. You will just find the structures do not translate well.

[natb]

The reason that I tried to query the data from TM1 this way is that I am in a process of testing out 'MDX query' against TM1 cubes to see what MDX statements work and what not. My team and I have more experience in MS SQL Server than TM1 and we created many SSRS reports querying data directly from SSAS cubes and believe we should be able to do the same with TM1 cubes. If exporting data from TM1 to SQL Server is the only way to do it, then that's fine. But I'm interested to know the reason why I get 'duplicate column names' error from OPENQUERY. Is it the bug in TM1 or it's incompatability issue with MS products? If it was a bug, shouldn't it be fixed?
Also, we use SSRS to create reports because we have Dundas for SSRS but not TM1Web so reports produced by SSRS have nicer looking graphs and charts than what you get in Excel.
As far as TM1 support goes, I spoke to a TM1 support staff at the conference last week and he pretty much told me to export TM1 cube data to SQL Server and has no interest in finding out the reason why I get the duplicate column names error. He suggested that it was not the best practices so I asked him to point out where I could find their best practices documentation and all he gave me was a link to communities.cognos.com.

[rmackenzie]

Is it the bug in TM1 or it's incompatibility issue with MS products? If it was a bug, shouldn't it be fixed?

Good question - I would say an incompatibility with T-SQL. I would imagine that Cognos will investigate a request to change the TM1 ODBO provider to solve your issue but I wouldn't put a bet on how quickly they'd turn it round.

If you want a nice charting and slice/dice application that works with TM1's MDX implementation then EV will work well. Otherwise, I agree with Scott and David, your best bet is to flatten the cube in TM1 first and then export it to SQL Server.

[fede.viva]

How do you setup the linked server for tm1 in SQLServer to use openquery?