Poor performance when selecting only from dimensions.

[rasmus_seidelinstokholm]

Hi guys,

We are using BO Universes on top of HANA Models which are Calculation Views with Star Joins.

BO Universes have default behavior of trying to create at list of values for columns, by creating a select distinct for the column.

Like this

SELECT DISTINCT "SUPPLIER_ID"
FROM "_SYS_BIC"."bi.PROC/DH_PROCESSER_CV"

SUPPLIER_ID is a column in the SUPPLIER_CVD dimension which is referential joined to the FACT part.

My problem is that the above query causes the model to Join the entire model, instead of just looking at the dimension.

I have quite a lot of data, so each time a user requests a list of values like this it causes the server to use 100% CPU for 0,5 to 10 min.

Making the system inoperable.

Has anyone found out a way to make the model query the dimension instead of the entire model ?

Hope you can help.

Best Regards,

Rasmus

Note: We are on SP11

[jon_humphrey]

Hi Rasmus,

I too have found this to be a source of frustration both with the old analytic views and the new star join approach.  I think the integration points between the database and reporting clients could be greatly improved here with a reasonably simple enhancement in functionality.  With universes you can at least create a LOV that points straight to the Attribute view/Dimension calculation view.  If you are using a tool like Lumira, however it becomes even more frustrating as this is not possible.  Design studio has the same issue where is it not even possible to choose a data source that is not of type aggregation.

I understand there are 2 types of use case here and that it would not be possible to please everybody unless it was something the modeller could decide.

1) I want to restrict my LOVs to only show attributes that have an associated fact to them.  This sometimes makes sense as why have an attribute show if in a LOV set if there is no associated data for that attribute.  Similarly you may be chaining a few LOVs together to produce an overall filter.

however

2)  if those attributes are slowly changing and you know from your business model that the set in your dimension/attribute view will, in all likelihood have associated fact data, then it would be so much nicer to be able to specify this at design time.  The metadata of the calculation view should be able to navigate to the associated underlying dimension view and simply perform a select distinct on that.  I view it as a sort of "right outer join for lookups" option even though  the selection of the actual result set  would typically be a left outer join as you would expect in a star schema.

I work in retail and a typical use case may be weekly sales.  I may have a Store ID as an LOV.  Do I want to be able to only show Stores that have had sales in my LOV?  Well maybe for some use cases but for the majority of time not (I trust that all my stores have made at least 1 sale).

Similarly I may want to have an LOV based on Male and Female customers.  Do I have to do a select distinct at a transactional level (over up to 5 years worth of transactions) as runtime to know that there are only Male and Female?  Of course you can (for some clients) build these separately but it would be so much nicer if you had the option to let the model
decide.

There is some LOV functionality that has been introduced for input parameters but it doesn't yet play very well with the BI Suite.

Thanks,

Jon

[Former Member]

Can you try changing the joins to referential joins in your model?

Lot of material out there that talks about diff between inner and referential.

If all your joins in the model are inner, then all the joins will be executed even if you ask for one object (supplier id). However if you have the joins as referential and if you ask for only one object from single table (as per your example) it will just get the object values from single table and should not execute all the joins in the model.

Caution: If you have filters on tables in your model then those joins (no matter referential or inner) will be executed because it has to enforce the filter.

[rasmus_seidelinstokholm]

Hi Srinivas,

Yes, that was my believe to.

All my joins in the star join are referential.

Below the star join there are 2 left outer joins which orginates from at scripted view.

The scripted view contains 1 left outer join.

Best Regards

Rasmus

[rasmus_seidelinstokholm]

Moved to SAP HANA and In-Memory Computing