Remove TopTotal in BI XMLA query

[Former Member]

Dear Sirs,

I am doing a simple query agains a BW cube using BW Wizard - XMLA. It returns the top 5 months according to sales. I want the output to be a chart.

The problem is that when executing the the Top N, a line called TopTotal is added to the output of the result. So in addition to my 5 top months I also get a entry which is the sum of the other 5. This ruins the format of the chart, as this bar get so much higher than the rest.

I do not want this TopTotal. I tried to filter the result before inserting them into the graph, using a filter function between the Cube and graph but I cannot seem to get the graps of the filter function.

Ideally I should be able to define in the filter on the right field: NOT Toptotal, or since all the months are three capitol letters, [A-Z][A-Z]. However I feel the filter operator in VC is almost useless. Even * filter out all the values (JAN, FEB, MAR, etc). The way I see it this should have returned all the months.

Any ideas on how to remove the TopTotal?, or a good intro to the filter. Why is not standard regexp used.

(I have read; http://help.sap.com/saphelp_nw04/helpdata/en/03/5f8340d990ce62e10000000a155106/content.htm. Maybe someone should write a blog about the filter operator in VC).

Best regards,

Jørgen Ruud

[Former Member · Accepted solution]

Before you go into ugly filtering on the client, change the MDX statement (MDX being something like a super-SQL for multidimensional data) that was generated with the Wizard.

1) Go into the BI Integration Wizard and create your Top N query

2) On the storyboard right click on the data service and select MDX Editor

3) You can now edit yoru MDX statement. Do the following

Let's assume your MDX statement looks like this:

with

set [Rowset] as '{[Education Level].[Education Level].Members}'

set [TopSet] as 'TopCOUNT([Rowset], 5.0, [Measures].[Profit])' member [Education Level].[TopTotal] as 'Aggregate( [TopSet], [Measures].[Profit] )'

set [FinalTopSet] as '{[TopSet], [Education Level].[TopTotal]}'

select {[Measures].[Profit]} on columns, {[FinalTopSet]} on rows from [Sales]

You only need to change something in the last line: replace in the SELECT-line the word FinalTopSet to TopSet. It should look like this:

with

set [Rowset] as '{[Education Level].[Education Level].Members}'

set [TopSet] as 'TopCOUNT([Rowset], 5.0, [Measures].[Profit])' member [Education Level].[TopTotal] as 'Aggregate( [TopSet], [Measures].[Profit] )'

set [FinalTopSet] as '{[TopSet], [Education Level].[TopTotal]}'

select {[Measures].[Profit]} on columns, {[TopSet]} on rows from [Sales]

This worked for me, no total line.

More about MDX can be found under:

Introduction to MDX

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxbasics_3md4.asp

[Former Member · Accepted solution]

Hello Mario,

thank you for your solution. I agree that this should work, and it is a good solution.

It have however a minor drawback because you need to activate the MDX in Visual Admin (see the text copied from VC security guide below). But this is probably something you should do sooner or later anyway.

+For security purposes, the SQL and MDX Editor functionality is turned off by default in Visual Composer. Therefore, if you try to access it, the following message is displayed: You are not authorized to use this functionality.

(copied from http://help.sap.com/saphelp_nw2004s/helpdata/en/44/366d3c78da48a1e10000000a114a6b/frameset.htm)+

But I think I will close the thread as I have received two god answer, and we have confirmed my initial doubt about the lacks in the filter operator.

best regards,

Jørgen

[Former Member · Accepted solution]

Hi Jørgen,

1) Yes, we unfortunately had to make it turn-off-able because of these security issues. Otherwise we would have not gotten this feature through our internal solution validation (they check security of applications).

2) Generally whenever you work with data services you should push the filtering, sorting and any other time consuming operation to the backend system. Performance-wise the gain can be tremendous.

E.g. you filter, then already do that on the backend. You need data aggregated? Do it on the backend. Otherwise you send thousands of records across the network just to get 5 records in display.

With other words: good modeling of your application has no filtering in the model itself.

[Former Member · Accepted solution]

Hi,

when I try to make the suggested changes in the MDX-Editor, I always get the error:

Error in compiling Flex application (1). Consult log file for details.

This failure even appears when I don't make any changes in the editor at all - I just open the MDX-Editor, press "Save" and the iView can't be deployed anymore.

I tried to change the compiler to Web Dynpro or XGraph via Tools --> Options --> Compiler, but this doesn't work either. When compiling with Web Dynpro, the error message is:

com.sap.tc.wd4vc.intapi.info.exception.WD4VCRuntimeException: No configuration is defined for the entry BIOGMLQuery

I have absolutely no clue, where and what I'm supposed to change or configure in order to get the "Total-Line" out of the table/chart... so I'd be extremely pleased for any kind of help!!

Thanks a lot,

Tobias

[Former Member · Accepted solution]

Hi

We too have a problem editing queries in the MDX editor. We have created a query from a BI System(backend) using XMLA connector. Now we wanted to change the MDX syntax to cater to our requirement. But once we change the MDX statement and overwrite the existing structure by saving it, it never allows the model to be deployed.

Has anyone got a solution to this issue, please respond.

Thanks

Kiran

[Former Member · Accepted solution]

1) please create a new thread and don't reopen an old one

2) have you consulted your log files, as recommended in this thread? Can you tell us what the log file says?

[Former Member]

Hi, Jørgen!

I would suggest to explore VC Formula option. For example you could check particular item for value 'TopTotal', and if such found, then show empty place.

It may look something like this:

IF(@Field_by_name_x=="TopTotal","",@Field_by_name_x)

This might be a workaround. Anyway, hope that this will help.

--- Kaspars

[Former Member]

Thanks Kaspars,

I have also been thinking about using formula, but the way I see it the filter operator should be able to such simple filtering as I want here :=). But thank you for you input.