LookupCube: Unexpected Results with StrToSet

Hi.
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Member
s})")))} ON
ROWS
FROM [Sales]
Does anyone have any idea why the above query fails with an "Unknown
internal error"? When I replace the "[Product].Members" string with
"[Time].Members", it works fine.
The following query, which I think is quite similar, works fine:
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet("[Product].Members")} ON ROWS
FROM [Sales]
Thanks.I believe this happens because the list of product members exceeds the
maximum string length that can be handled by "SetToStr", "LookupCube", or
both.
This is supported by testing your query replacing [Product].Members by
Head([Product].Members, n).
Up to n = 344 this works, then it breaks.
At this point , the string length should be over 32K, which is a likely limi
t.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html
"John" wrote:

> Hi.
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Memb
ers})")))} ON
> ROWS
> FROM [Sales]
> Does anyone have any idea why the above query fails with an "Unknown
> internal error"? When I replace the "[Product].Members" string with
> "[Time].Members", it works fine.
> The following query, which I think is quite similar, works fine:
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet("[Product].Members")} ON ROWS
> FROM [Sales]
> Thanks.
>
>

LOOKUPCUBE Problem!

Hi,

Dose LOOKUPCUBE function include "Customer Member" ?
I use Customer Member in LOOKUPCUBE syntax and system response #Err!
So, LOOKUPCUBE dosen't support Customer Member?
Thanks for any advice!

Angi

Hi Angi,

The LookupCube function returns either a numeric expression or a string expression. Assuming you have a Customer dimension with a hierarchy called Customers (with levels Country-State-City-Name), and you are issuing the query in cube Budget, but want to evaluate these expressions in another cube called Sales, here are two working examples:

with member x as 'lookupcube("Sales", "[Customers].[Country].&[Canada].name")'
select x on 0 from [Budget] -- returns the string Canada

with member x as 'lookupcube("Sales", "[Customers].[City].count")'
select x on 0 from [Budget] -- returns the number of cities in the City level

Hope this helps,

Artur

|||

Artur,

Thanks for help!
My expression as follow...

WITH
MEMBER [X].[XX02].[THISPERIOD] AS '[X].[XX02].[200612]'
MEMBER [IV].[IV02].[Execute] AS 'LOOKUPCUBE ("CUBEEF" , " (
[X].[XX08].&[XX0830010] , " + [X].[XX04].CURRENTMEMBER.UNIQUENAME + "," +
[X].[XX01].CURRENTMEMBER.UNIQUENAME + " , [X].[XX02].[THISPERIOD] )" ) '
....
....

So, the Customer Member is [X].[XX02].[THISPERIOD] and the
[IV].[IV02].[Execute] will response #Err.
Any idea?

Angi

|||

The second parameter in the LookupCube function call is invalid. To see the detailed error message, please double click on the Err# cell in SQL Management Studio and it will display the reason. Looks like you are trying to pass a set to the function and not a string. What are you trying to achieve with this query?

--Artur

Lookupcube and Parameter not working together

Please help me
I have the following MDx that works perfect, but I need now to attach a
parameter, but it would work. please help
With
Member Measures.[Sales Current Year Target] as
'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
Target],[DistrictGeo].[District Id].["+[DistrictIsManaged].CurrentMember.Name
+"]," + [Time].CurrentMember.UniqueName + ")")'
SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
{NONEMPTYCROSSJOIN(
{ [DistrictIsManaged].[District Id].[Dallas]},
[Territory].[Territory Desc].members)} on
ROWS
FROM [Sales vs RepBudget]
where (" & Parameters!pTime.Value & ")"You need to prefix your mdx with =" and end with ". And the whole mdx
statement needs to be in on one line. It will break by itself, but you can
check it out by copying the whole statement to Notepad, turn off word wrap
and see that it's all on one line.
Also, you might have to escape the quotes "s in your statement. You do this
by adding more quotes.
"
Visual Basic Language Specification
2.4.4 String Literals
A string literal is a sequence of zero or more Unicode characters beginning
and ending with an ASCII double-quote character, a Unicode left double-quote
character, or a Unicode right double-quote character. Within a string, a
sequence of two double-quote characters is an escape sequence representing a
double quote in the string."
Try adding an extra " infront of you "s.
Kaisa M. Lindahl
"Tomas" <Tomas@.discussions.microsoft.com> wrote in message
news:E32EB005-182D-4315-A873-FF6C6606AFCB@.microsoft.com...
> Please help me
> I have the following MDx that works perfect, but I need now to attach a
> parameter, but it would work. please help
> With
> Member Measures.[Sales Current Year Target] as
> 'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
> Target],[DistrictGeo].[District
> Id].["+[DistrictIsManaged].CurrentMember.Name
> +"]," + [Time].CurrentMember.UniqueName + ")")'
> SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
> {NONEMPTYCROSSJOIN(
> { [DistrictIsManaged].[District Id].[Dallas]},
> [Territory].[Territory Desc].members)} on
> ROWS
> FROM [Sales vs RepBudget]
> where (" & Parameters!pTime.Value & ")"
>

LookupCube

The following is the MDX query for the report

WITH MEMBER

Measures.[TreatmentCount]

AS

'LookupCube(

"Patient Hospital and Drug",

"(" + MemberToStr(AgencyID.CurrentMember) + ", [Measures].[Pharmacy DW Count])"

)'

SELECT

{ Measures.[TreatmentCount]} ON COLUMNS,

NON EMPTY { ([Agency Id].[Agency Id].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Last Six Months Pharmacy DW])) WHERE ( IIF( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED), [Last Six Months Drug].[Drug Name].currentmember ), IIF( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED), [Last Six Months Drug].[Protocol Code].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The MDX query is based on the two OLAP cubes, "Patient Hospital and Drug" and "Last Six Months Pharmacy DW". The second OLAP cube is used to fined out all the "Agency ID" for a selcted "Drug" and "Protocol Code" prescriped to a patient at least once for the last six months from today date. Then apply these set of "Agency ID" to filter out the entire warehouse data i.e. the first OLAP cube and find out the total count of the treatment for the patient for this selected "Drug" and "Protocol Code".

OLAP cube:Patient Hospital and Drug

Measures: Pharmacy DW

Pharmacy DW Count

Dimension: Agency Id

Agency Id

Agency Id

Dimension: Drug

Drug Name

Drug Name

Dimension: Drug

Protocol Code

Protocol Code

The second OLAP cube has the same structure as the first one and it has only the last 6 months data (filter out in the data source view). The data warehouse is updated and the OLAP cube is re-build daily.

Thanks

Maybe I'm missing something, but what's your question in this case - does the MDX query work as expected?