CODEWITHSUNDEEP
sql-serverssasmdxopenquery
Kai
Kai

Reputation: 2168

Conversion failure using OPENQUERY from OLAP

I'm trying to retrieve some simple data from an SSAS cube using OPENQUERY in with an MDX string in SQL.

Cannot get the data of the row from the OLE DB provider "MSOLAP" for linked server "OLAP_LIVE". Conversion failed because the data value overflowed the data type used by the provider.

The query is simply:

select 
    StockCode = "[Product].[Stock Code].[Stock Code].[MEMBER_CAPTION]",
    LiveReturnPercent = "[Measures].[Live Return %]"

from openquery(
    My_OLAP_SERVER,
    'select
        non empty
        {
            [Measures].[Live Return %]
        }
        on columns,

        non empty
        {
            [Product].[Stock Code].allmembers
        }
        on rows

    from
        [Document DW]')

The error is occurring when transporting the data "before" it reaches the SQL - ie, I cannot resolve it by changing the third line to something like:

LiveReturnPercent = left("[Measures].[Live Return %]", 1)

As it still returns the same error. I don't know how to format / trim the query on the MDX side so it returns the measure as a shorter string.

If I run the MDX natively in SSAS it runs fine.

It appears that SSAS is internally defining a length for the [Measures].[Live Return %] string and then trying to pass back a longer string.

How can I resolve this?

Note that [Live Return %] returns a long floating value like 12.34632456 but I don't need that level of accuracy, so an answer that works by trimming it to 12.34 is adequate.

I also cannot modify the definition of the measure on the cube itself - not my cube; used widely elsewhere, etc etc.

Edit: I have also tried:

...
    LiveReturnPercent = "[Measures].[Live Return % Friendly]"

from openquery(
    My_OLAP_SERVER,
    'with member [Measures].[Live Return % Friendly] as
        [Measures].[Live Return %],
        FORMAT_STRING = "Percent"

    select
        non empty
        {
             [Measures].[Live Return % Friendly]
        }
        on columns,
...

but this also results in the same error - also with FORMAT_STRINGs like "#.##%"

Upvotes: 1

Views: 645

Answers (1)

Kai
Kai

Reputation: 2168

The problem seems to have been with inf values returning, as the measure is defined as:

[Measures].[Some Measure] / [Measures].[Some Other Measure]

Where [Some Other Measure] can be zero. A native mdx query neatly outputs inf in the resultset without erroring, but via openquery it seems to cause an overflow error.

I found that the Divide function returns null instead of inf so I was able to resolve this by changing the MDX to:

with member [Measures].[Live Return % NoInf] as
    Divide([Measures].[Some Measure], [Measures].[Some Other Measure])

select
    non empty
    {
        [Measures].[Live Return % NoInf]
    } 
    on columns,
...

Effectively calculating the same measure but to return null for inf values.

Note that this will return a slightly different result set to the original - the non empty will suppress the previously inf results as they are now null. This is perfectly fine for my purposes.

Upvotes: 2

Related Questions