XI 3.1 Web Inteligence MDX condition v SAP BW quer...

My colleague seems to think that if I restrict the BW query it will perform much quicker than if I filter using the MDX in a condition. I haven't proved this yet but would be interested if anyone can confirm that restricting in the MDX would/should perform as well as in the BW query.

Example:

I have 5 years of sales data. I then create a BW query that only returns data for one year. Build a BO Universe over that and hey presto....

If I then repeat this but do NOT restrict the BW query but add a query condition that restricts query to return the same year.

SAP Managed Tags:
SAP BusinessObjects Business Intelligence platform

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi Nick,

that depends a little bit how you do it.

Are you creating a predefined filter and the user can select it if required or are you creating a filter in your Web Intelligence report that always is being used ?

perhaps you can provide some more details on where exactly you want to set the filter and when it is being used on the Web INtelligence side.

ingo

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

The idea is to remove the need to have mutliple BW queries without degrading the performance.

The end solution is to create conditions in the universe. But when we create just a manual query condition in the Webi query (Year = '2007') the Webi report takes much longer to run than if we create a BW query specifically with a restriction of 2007 and run a Webi report againt that with no restrictions.

The test I've just done took 628 seconds using a BW query restricted to 2007 and 1516 seconds using a BW query with no restriction and a Webi query filter of 2007. Both bought back the same 3320 rows.

There is about 5 years worth of information in the BW data source.

So in answer to your question, we really want to set the filter within Web Intelligence. The reason for wanting the query condition in the BO side of things is to eliminate the management and maintenance of so many universes which are only there so that we can get much better (almost acceptable) performance.

Does that make sense?

Thanks

Nick

Hi Nick,

so why are you not using a variable in the query that will prompt the user for the value ?

just because you ant to filter it doesn't mean that you need multiple queries.

in regards to the difference I would assume that you are not sending the technical name for the 2007 value which means the string value needs to be resolved.

ingo

We cannot prompt the user as we schedule all of these reports out of hours to run using relative time periods. At the moment we do this by picking a pre restricted universe such as last four weeks (which would look at weeks 200902, 200901, 200852, 200851). These weeks obvously change each week so the reports are not having to be alterered or the schedule changed.

I'm not sure what you mean about

"in regards to the difference I would assume that you are not sending the technical name for the 2007 value which means the string value needs to be resolved."

I'm just selecting the object containing a year in the query condition and selecting a value from the list of values for one year. Does that not generate the MDX in the way I need it then?

Hi Nick,

that sounds to me like the perfect logic for a EXIT Variable in the BI query.

You can use a EXIT variable which then will use the system data or an input and bring back the needed timeframe. That would mean you don't have to create all the pre-defined filters or universe. You could have a single BI query taking care of the problem

Ingo

ok, I'm sort of losing the reason for this thread as it was about performance but this maybe my problem.

So I have a query with Fiscal Year in it. In the quiery I currently have it set to use the EXIT variable Current Fiscal Year (technical name of 0FYEAR-1, the -1 to make it last year...)

What I'm trying to work out is what the universe condition to do the same should be?


<FILTER KEY="[0FISCYEAR].[LEVEL01].[NAME]">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="Z12007"></CONSTANT>
</CONDITION>
</FILTER>

This works but I should replace the

"Z12007"

with

[0FYEAR]

? should it still be CAPTION or something else? I'm not sure what options I've got in the conditions and I cannot seem to find any documentation or examples,

Hi Nick,

does the EXIT variable in the BI Query have the property set to be ready for input ?

I think we talking about 2 things here and based on my previous description I would use the EXIT variable directly and use the EXIT and an offset to actually achieve the goals - WITHOUT even asking the user for an input - so you would lose the filter in WebI.

You basically prompting the user now.

Ingo

Hi Ingo, thanks for all of your help thus far. I am trying to resolve a number of things before our users will accept moving to release 3.1 so sorry for any confusion. One massive issue they have is centred around the very high number of universes we have against BW and unless we can remove the need for this whilst maintaining/improving performance it's unlikely we can sell any benefit of moving.

Can I just clarify the EXIT variable in the BI query part?

Letu2019s just talk about one BW cube for the moment. For this single cube we currently have a number of BW queries each using different EXIT variables. Some of which have offsets E.g. Previous Year using Current Year - 1 EXIT variable OR Previous Day using Current Day -1 EXIT variable. We then have to create (this is where I think I'm missing something) one BOXI universe for each BW query. The end result is that the Webi query uses the "relative" time defined by the selected universe and therefore the selected BW Query and whenever it is run there are no prompts or hard coded time constraints for the user.

All of our data is broken down into Years (Calendar and Moving Annual), Periods (4 weeks) and Weeks (individual weeks, last 4 weeks, last 2 weeks last year). Basically many more combinations too. Because of this I cannot put all of these as EXIT variables in one BW Query as no data would match all of the conditions (or am I missing something here too?). I.E. Last Calendar Year (2008) and Last 4 Weeks (200901, 200902, 200903, 200904) would conflict and return nothing.

What I am trying to achieve is one BW query with one corresponding BOXI universe and for the Universe to have a number of dynamic calendar filters that can be selected by the user. At this point I have only been successful in creating a filter where the time period is hard coded (not relative as an EXIT variable would be).

Thanks

Nick

Hi Nick,

which to my knowledge is something you wouldn't be able to do in the universe because you are missing the relative / current date option.

to me it sounds like you want to offer more flexibility from a prompting point of view so why not using a combination of variables ?

You could have an optional prompt for the year / week and month and one that offers a date range.

In that way you could have one query with 4 prompts and depending on which one the user fills in you receive the data.

In addition - be aware that creating one large query for a single cube will have a performance impact.

ingo

regarding the dynamic calendar, is this of any assistance?

In this case, user selects MON YYYY and is ok

My issue is how to find the End Month of the Previous Year (DEC PYEAR) than the one in the prompt, without asking the user twice...

Any ideas for handling these prompts in bw unv conditions? Any mdx success in parsing?

Thanks

Hi,

why not using the existing EXIT variables in the SAP system ? such functionality already exists there and you can re-use it.

Ingo

Unfortunately, I dont know BW, only BO, but if you explain, I can ask from customer.

My biggest concern is to handle prompts of BO to BW, I am likely to open a new thread on the forum for what I have tested and does not work so far.

Hi,

an EXIT variable allows you to leverage the environment variables from the underlying system (like a system data from the BI system) and filter data based on a defined range. an EXIT variable is code that you can leverage. so you could read the system date and then define a range based on that as an example

Ingo

Thanks Ingo

My product manager spoke of your name and the forum.

About this question, do you think it is easy for customer to do it?

They are kind of doing things on top of BO, and to be honest, I think they should be having some flexibility.

By the way, I just posted a thread, do you mind if I send you in email a doc describing it better?

Hi Nick,

has your situation been resolved ?

thanks

Ingo

No, I'm afraid not. I think this strayed off topic as the performance issue never got answered but the dynamic condition which it strayed onto didn't either.

I really have no idea if this is achievable or if it's a case of me not being able to understand what people are suggesting to do to resolve it.

Hi Nick,

so what is outstanding here ?

ingo

Hi Ingo

Nothing on the subject of "XI 3.1 Web Inteligence MDX condition v SAP BW query filter". This is now giving us a good response from what I can see in our tests. Now on XI 3.1 fixpack 1.2 for XI and SAP Integration pack.

The issue I still have is to do with relative dates that I need for automatic scheduling of reports but that is not what this thread was about, see [1170994|;

Thanks

Nick

Hi Nick,

so this thread can be closed ? If not let me know whats open

Ingo

yes, please close this one

Ask a Question

Top Q&A Solution Author

User

Count