Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Multi-Verb Request on Cube
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] Multi-Verb Request on Cube
 Login/Join
 
Gold member
posted
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>,


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
This is wierd.

You may have to ask Techsupport about this one.

Have you looked to see if any SQL is generated ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6258 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Whats the volumns like ?

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

Can you send SQL instead, then produce the report ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6258 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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 Smiler
 
Posts: 3074 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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!


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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]


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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]


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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


v8.1.04, 64-bit Windows (Reporting Server), Apache Tomcat (Web/App Server), HTML, PDF, AHTML, Excel outputs
 
Posts: 57 | Location: Des Moines, IA | Registered: April 30, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Multi-Verb Request on Cube

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.