multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.sql

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

Multiple row insert only inserting one row?

I can't seem to find any place that a similar issue has been
encountered, so here goes:
INSERT INTO destination(dest_id)
SELECT src_id
FROM source
WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
The subquery executes as expected when run by itself, returning [n]
rows. But when I try to execute the insert statement, only one row gets
inserted. Or rather, it inserts one record, then runs the SELECT
statement almost as if it was entirely separate:
(1 row(s) affected)
([n - 1] row(s) affected)
I should note that originally I was trying to insert several fields from
the source table, but was experiencing the same behavior -- only one row
getting inserted -- but some of the fields weren't getting inserted
properly either. So I pared it down to trying to just get the ID's
inserted, after which I planned on running an UPDATE.
Any advice on what I might be missing would be appreciated.Also, I tried the following to no avail:
* wrapping the SELECT statement in parentheses
* adding TOP 100 PERCENT to the SELECT
* changing the NOT EXISTS to a NOT IN () statement
Matthew Harward wrote:
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Try using a table alias...
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Thanks for the quick reply. I tried both aliasing and using full
resolution - both with no luck:
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
and
INSERT INTO destination(dest_id)
SELECT source.src_id
FROM source
WHERE NOT EXISTS
(SELECT destination.dest_id
FROM destination
WHERE destination.dest_id = source.src_id)
Jim Underwood wrote:
> Try using a table alias...
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
>
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
>|||Post DDL and soem sample data and I will try to duplicate the issue. It
looks to me like it should be working. You might try putting paranthesis
around your select.
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> Thanks for the quick reply. I tried both aliasing and using full
> resolution - both with no luck:
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
> and
> INSERT INTO destination(dest_id)
> SELECT source.src_id
> FROM source
> WHERE NOT EXISTS
> (SELECT destination.dest_id
> FROM destination
> WHERE destination.dest_id = source.src_id)
> Jim Underwood wrote:
src_id)
from
row
>|||Actually, I figured it out ... there was a INSTEAD OF INSERT trigger
messing things up.
<sigh> Triggers always seem to be the last place I look, you'd think I'd
learn my lesson by now. ;)
Thanks again for the willingness to help.
Jim Underwood wrote:

> Post DDL and soem sample data and I will try to duplicate the issue. It
> looks to me like it should be working. You might try putting paranthesis
> around your select.
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> src_id)
> from
> row
>