Calculation view slow when quering only dimension

[rasmus_seidelinstokholm]

Hi,

I have a calculation view (MESDATA) with a star-join and 6 calculation view dimensions.

When i try to create a list of values(LOV), I do a distinct search on it the CV which takes forever as the query goes through the whole model with out any filters. 

SELECT DISTINCT "CITY_NAME" FROM "_SYS_BIC"."TMP/MESDATA_CV"

The explain plan shows it very good.

To avoid this i changed the link to right outer join "LOCATION"

Now the query only looks at the LOCATION_CVD and works very quickly.

I theb changed the other dimensions to right outer join as well but now all dimensions go throught the entire model again.

Including "CITY_NAME" which just worked before.

I then tried the same with another dimension at the behaviour is consitent.

If I have one dimension as right outer join the query will only query that dimension.

When 2 or more are set to right outer the whole model is queried.

Does any one know how i can get the model to only query the parts that are in the query ?

Best Regards

- Rasmus

[lbreddemann]

For the cases where you don't need to show only "booked" values, it's typically better to use the attribute/dimension views directly as sources for the value lookup.

[rasmus_seidelinstokholm]

True, that would be ideal.

Unfortunatly, we are using universes with WEBI on top.

So the list of values is a generated statement, which goes to the main CalcView.

[lucas_oliveira]

Hello,

In addition to what Lars stated, I'd check if all your joins are using appropriate cardinality.

By the looks of your model I'd set left outer from data foundation to dimension CVs and if the cardinality is set correctly the optimization process that runs before execution could remove unwanted joins.

I believe that would work by default from SP09 on... at least that's what my test shows. Here's a snapshot of my test model and result for LOV query:

BRs,

Lucas de Oliveira

[rasmus_seidelinstokholm]

Hi Lucas,

As i see it your test, shows that it doesn't work for you either.

Your explain plan shows that it is quering the FACT table.

Which should not be needed since it is an outer join.

Please correct me if I'm wrong.

We are on SP9 rev. 97.

Best Regards,

Rasmus

[lucas_oliveira]

Hello,

My bad: I understood you were facing an implicit join with the other *dimension* tables everytime.

I'd keep with the option of using the dimensions on the LOV on your universe. Isn't that possible?

On a side note: I tried a little test with Referential joins and Optimize Join Column set to True and it did went directly to the dimension bypassing the data foundation.

I was expecting to see that behavior with Left/right outer join as well, but that didn't happen. Need a little more testing to understand what's going on there.

BRs,

Lucas de Oliveira

[rasmus_seidelinstokholm]

Hi Lucas,

I tried using the same settings as you but unsuccesful.

I don't know if it is because my "FACT" flow is more complicated than yours or there is some bug.

Anyways I'm going to report it to SAP in an OSS.

Thanks for your time.

Will try to update regarding SAPs answer.

Best Regards

Rasmus

[former_member182114]

Hi Rasmus,

I'm not an WebI expert, but I remind in a project some years ago that is possible create personalyzed LOV's and customize the exposition of the query. Is it not possible?

Another thing is that at that stage wasn't possible to directly select on HANA without model it first on Universe, so maybe time passed and now there are some things automatic and you can access directly a calculation view without model it on universe, but anyhow even in cases like this can't you personalyze an LOV acessing the dimmension directly?

Regards, Fernando Da Rós

[rasmus_seidelinstokholm]

You can do a custom query for each LOV.

But it not easily maintained if you have to setup 400 custom LOVs each time you create a new univers.