With the CAR file, I can issue a simple multi-verb request, like so:

TABLE FILE CAR
SUM
    SALES
BY COUNTRY
SUM
    SALES
BY COUNTRY
BY CAR
END

I'm now experimenting with our first SQL Server Analysis Services 2012 cube, and I'm finding that I cannot do something comparable:

TABLE FILE CDW_DEMO
SUM
    ORDER_AMOUNT
BY STATE
SUM
    ORDER_AMOUNT
BY STATE
BY PRODUCT
END

When I do, I get the following error:

(FOC024) LIST OR PRINT MUST BE THE LAST VERB IN A MULTI-VERB REQUEST

Note that when I run each verb individually, commenting out the other verb, results are returned. I do find, however, that I CAN do this:

TABLE FILE CDW_DEMO
SUM
    ORDER_AMOUNT WITHIN STATE AS 'ALL STATE'
    ORDER_AMOUNT
BY STATE
BY PRODUCT
END

Was I violating some rule with the multi-verb approach against a cube? Is there a way to code this correctly, or should I look forward to re-thinking this (and other!) ingrained coding techniques?

Thanks!

Craig

This message has been edited. Last edited by: <Kathryn Henning>,

This is wierd.

You may have to ask Techsupport about this one.

Have you looked to see if any SQL is generated ?

This is a known issue It is considered as a long term project.

The issues are complex with a cube such as SSAS as the aggregation should be done within the cube itself. Internally, against a SSAS Cube, a SUM is translated into a PRINT to ensure that non-aggregatable measures are not aggregated.

This also affects other aspects of reporting such as accordian reports and attempting to SUM on a DEFINE:

DEFINE FILE ADVENTURE_WORKS
D_MONTH/A3 = EDIT(Month,'999');
END
TABLE FILE ADVENTURE_WORKS
SUM Internet_Sales_Amount
BY D_MONTH
END

will produce multiple lines for each month as if a PRINT was coded.

However be aware of the reporting syntax that can be associated with SSAS cubes that can show the cube to its best advantage, like BY hierarchy_field HIERARCHY, SHOW TOP n etc. and the settings that can affect reporting such as SET ROLLUP_BY_VISUALTOTALS.

Thanks, Alan! (And Waz, I did turn on the trace, and the error I'm receiving presents itself and NO MDX code is displayed.)

And while I have your "ear," Alan, one other thing, please: How do I get the adapter to generate a synonym reflecting a parent/child relationship? Seems I'm getting a LEVEL hierarchy despite selecting parent/child in the "Present Parent/Child Hierarchies as" drop-down list. Is there something I should ask of my DBA??

Regards,

Craig

Hi Craig

Ah yes. Never got this to work either.

The "normal" solution would be to use a Business View, but these are not supported against SSAS!

I did not open this as a case with IB, so maybe worth a punt.

Whats the volumns like ?

Can you simplify the query to PRINT or PRINT and BY ?

Can you send SQL instead, then produce the report ?

Can you HOLD the "sets" of data, and JOIN them, to simulate the multi-verb-request? It may be worth a try, albeit a work-around. Happy Coding

Made a case out of this. Am looking at the suggested solution, substituting a MATCH for the two-step workaround I had fashioned. Here's some sample code:

MATCH FILE ADVENTURE_WORKS
SUM
    INTERNET_SALES_AMOUNT AS INTERNET_SALES_AMOUNT_1
BY COUNTRY
RUN
FILE ADVENTURE_WORKS
SUM
    INTERNET_SALES_AMOUNT AS INTERNET_SALES_AMOUNT_2
BY COUNTRY
BY STATE_PROVINCE
BY CITY
AFTER MATCH HOLD OLD-AND-NEW
END

TABLE FILE HOLD
SUM
    INTERNET_SALES_AMOUNT_1 AS INTERNET_SALES_AMOUNT
BY COUNTRY
SUM
    INTERNET_SALES_AMOUNT_2 AS INTERNET_SALES_AMOUNT
BY COUNTRY
BY STATE_PROVINCE
BY CITY
END

Craig

quote:

TABLE FILE HOLD
SUM
INTERNET_SALES_AMOUNT_1 AS INTERNET_SALES_AMOUNT
BY COUNTRY
SUM
INTERNET_SALES_AMOUNT_2 AS INTERNET_SALES_AMOUNT
BY COUNTRY
BY STATE_PROVINCE
BY CITY
END

I believe you need to apply an appropriate data-reduction operator (MIN. or MAX. or AVE. will do) to INTERNET_SALES_AMOUNT_1 in the first SUM, to avoid multiplicative effect.

Craig

Don't need MATCH.

TABLE FILE ADVENTURE_WORKS
SUM
    Internet_Sales_Amount 
BY Country
BY State_Province
BY City
ON TABLE HOLD
END

TABLE FILE HOLD
SUM
    Internet_Sales_Amount
BY Country
SUM
    Internet_Sales_Amount
BY Country
BY State_Province
BY City
END

My real problem is trying to (efficiently) create a Top n report on the cube, without benefit of multi-verb. Here's how I presented this in the case I opened:

With the CAR file, I can create a report of the Top n Countries with the highest total Retail Cost, showing the Car detail across:

TABLE FILE CAR
 SUM
 COMPUTE ROWTTL/D13=RETAIL_COST; NOPRINT
 BY TOTAL HIGHEST 2 ROWTTL NOPRINT
 BY COUNTRY
 SUM
 RETAIL_COST
 BY TOTAL HIGHEST 2 ROWTTL NOPRINT
 BY COUNTRY
 ACROSS CAR AS ''
 COMPUTE TOTAL/D13=C1;
 ON TABLE SET HIDENULLACRS ON
 END

The need for this kind of "Top n" reporting is common, and I'm wondering how best to approach it. As a workaround, I've done something like this:

TABLE FILE CAR
 SUM
 RETAIL_COST NOPRINT
 BY TOTAL HIGHEST 2 RETAIL_COST NOPRINT
 BY COUNTRY
 ON TABLE SAVE
 END
 
 TABLE FILE CAR
 SUM
 RETAIL_COST
 BY COUNTRY
 BY CAR
 WHERE COUNTRY IN FILE SAVE
 ON TABLE HOLD
 END
 
 TABLE FILE HOLD
 SUM
 COMPUTE ROWTTL/D13=RETAIL_COST; NOPRINT
 BY TOTAL HIGHEST 2 ROWTTL NOPRINT
 BY COUNTRY
 SUM
 RETAIL_COST
 BY TOTAL HIGHEST 2 ROWTTL NOPRINT
 BY COUNTRY
 ACROSS CAR AS ''
 COMPUTE TOTAL/D13=C1;
 END

As you can see, this requires TWO passes at the data source, in this case one to get the COUNTRY list and one to get the detail data. If there's a better way, when hitting a cube, I'd like to see it!

Hi
For TOP/LOW n reports there is a new feature in 7705 server for supporting TOPCOUNT and BOTTOMCOUNT MDX syntax:
In the syntax you combine HIGHEST/LOWEST TOTAL and READLIMIT:

examples:


TABLE FILE ADVENTURE_WORKS
SUM Internet_Standard_Product_Cost
BY LOWEST TOTAL Internet_Standard_Product_Cost
BY State_Province
WHERE READLIMIT EQ 10
END

It will generate:

=== SSASET MDX statement generated:
WITH
SET H7 as '
[Customer].[Customer Geography].[State-Province].ALLMEMBERS'
SELECT {
[Measures].[Internet Standard Product Cost]}
ON AXIS(0),
NON EMPTY BOTTOMCOUNT(NONEMPTY(H7,
[Measures].[Internet Standard Product Cost]),
10,[Measures].[Internet Standard Product Cost]) DIMENSION PROPERTIES
MEMBER_CAPTION ON AXIS(1)
FROM [Adventure Works]


and

TABLE FILE ADVENTURE_WORKS
SUM Internet_Standard_Product_Cost
BY HIGHEST TOTAL Internet_Standard_Product_Cost
BY State_Province
WHERE READLIMIT EQ 10
END

will generate:

=== SSASET MDX statement generated:
WITH
SET H7 as '
[Customer].[Customer Geography].[State-Province].ALLMEMBERS'
SELECT {
[Measures].[Internet Standard Product Cost]}
ON AXIS(0),
NON EMPTY TOPCOUNT(H7,10,[Measures].[Internet Standard Product Cost]) DIMENSIO
MEMBER_CAPTION ON AXIS(1)
FROM [Adventure Works]

Eran:

Yes, thanks, I did observe that new feature. But in your example, what if you needed to show the detail by City of the Top 10 State_Provinces?

Craig

Hi

I would use a solution in the following direction:

-* find top 5 state_province
TABLE FILE ADVENTURE_WORKS
SUM
ADVENTURE_WORKS.ADVENTURE_WORKS.Internet_Gross_Profit
BY TOTAL HIGHEST ADVENTURE_WORKS.ADVENTURE_WORKS.Internet_Gross_Profit
BY ADVENTURE_WORKS.ADVENTURE_WORKS.State_Province
WHERE READLIMIT EQ 5
ON TABLE HOLD AS GENDAT
END
-RUN

-* create a unique list for filtering
TABLE FILE GENDAT
BY State_Province
ON TABLE HOLD AS AA FORMAT ALPHA
END
-RUN

-* retrieve final report all cities for top 5 state_provinces
TABLE FILE ADVENTURE_WORKS
SUM ADVENTURE_WORKS.ADVENTURE_WORKS.Internet_Gross_Profit
BY ADVENTURE_WORKS.ADVENTURE_WORKS.State_Province
BY ADVENTURE_WORKS.ADVENTURE_WORKS.City2

WHERE ADVENTURE_WORKS.ADVENTURE_WORKS.State_Province IN FILE AA

END
-RUN

The MDX trace:

first part:

=== SSASET MDX statement generated:
WITH
SET H7 as '
[Customer].[Customer Geography].[State-Province].ALLMEMBERS'
SELECT {
[Measures].[Internet Gross Profit]}
ON AXIS(0),
NON EMPTY TOPCOUNT(H7,5,[Measures].[Internet Gross Profit]) DIMENSION PROPERTI
MEMBER_CAPTION ON AXIS(1)
FROM [Adventure Works]

second part:

=== SSASET Auxiliary MDX statement generated:
SELECT
{[Measures].ALLMEMBERS(0)} ON AXIS(0),
HIERARCHIZE([Customer].[Customer Geography].[State-Province].ALLMEMBERS)
DIMENSION PROPERTIES
MEMBER_TYPE
,MEMBER_UNIQUE_NAME
,MEMBER_CAPTION ON AXIS(1)
FROM [Adventure Works]
=== SSASET Filter list generated for multilevel test:
[Customer].[Customer Geography].[All].&[Australia].&[NSW]&[AU],
[Customer].[Customer Geography].[All].&[Australia].&[VIC]&[AU],
[Customer].[Customer Geography].[All].&[United Kingdom].&[ENG]&[GB],
[Customer].[Customer Geography].[All].&[United States].&[CA]&[US],
[Customer].[Customer Geography].[All].&[United States].&[WA]&[US]
=== SSASET MDX statement generated:
WITH
SET H7 as '
{[Customer].[Customer Geography].[All].&[Australia].&[NSW]&[AU],
[Customer].[Customer Geography].[All].&[Australia].&[VIC]&[AU],
[Customer].[Customer Geography].[All].&[United Kingdom].&[ENG]&[GB],
[Customer].[Customer Geography].[All].&[United States].&[CA]&[US],
[Customer].[Customer Geography].[All].&[United States].&[WA]&[US]}'
SET H78 as '
[Geography].[Geography].[City].ALLMEMBERS'
SELECT {
[Measures].[Internet Gross Profit]}
ON AXIS(0),
H78 DIMENSION PROPERTIES
MEMBER_CAPTION ON AXIS(1),
H7 DIMENSION PROPERTIES
MEMBER_CAPTION ON AXIS(2)
FROM [Adventure Works]

Eran:

Yes! That's the workaround I implemented prior to opening a case, and I do get correct results. I'm concerned, however, with the performance of this. In "Phase I," where I'm building my SAVE file of the Top n Customer Numbers based on Order Amount, I get excellent performance, even when generating the top 100. In "Phase II," however, when I'm getting all the detail data... Well, I may as well just hit "Run" and go out to lunch! This may have something to do with our test environment, but I'm wanting to ensure I'm coding as efficiently as possible.

As I said, I've opened a case and have shared my (our!) workaround with IBI Tech Support with the hope that something can be done to speed up these queries. Yes, I may have to play with different ENGINE SSAS SET commands before this is all over, but I wanted to start with my coding approach. I'll report back any findings...

Craig

Hi

1. Its also interesting to know whether in a single MDX query its possible to achieve this type of query

2. regarding the time it takes to run the report, how much time does it take if you run the subsequent request (the detailed one ) on a single value? if its very fast then you could create a loop and call the SSAS cube 10 times,each time on a single value, then append the data with MORE
its not an efficient way regarding the DB calls however it may prove much faster in this case

3. another idea is to change the approach to a drill down report so users will very quickly get their top 10 report and then click and drill down on a specific value

This message has been edited. Last edited by: WF_IL,