Querying a dim's column in star join HANA view why result in entire view execution (PlanViz))

[Former Member]

We are at HANA SP11 and BO 4.1 SP3 patch 2 with webi connecting to relational universe (Hana biz layer) to HANA star join calc. views. View under questions has star join with fact surrounded by left join with dimension calc views.

I am just trying to get LOVs for a dimension column without reference to any other column like
select DISTINCT DimColumn1 from view

and this column is not calculated column (anyway why it should matter unless it refers fact or any other view)

Also note, this behavior happens irrespective of whether I query directly on HANA studio or through BO IDT

Query takes very long time for result and our report prompt screen becomes slow. When I see plan viz, it shows query is going to entire fact table etc which confuses me why its doing so ?? I have just queried one column from dimension in star join.

As a temporary mitigation, we are bringing dim calc. view in universe and creating LOVs. When we started using star join , we thought this will have benefit of star schema. However, we are  seeing none.

SAP support says  "oh we see your plan viz it's doing what it's supposed to do". and our discussion still going on.

So two questions :

1) Why do you think its behaving that way ? Is there anything we could be doing wrong - given its star join, left outer join fact and dim, querying only one non-calculated column from dim

2) From your experience, how do you guys achieve same i.e. creating LOVs from similar setup of BO-HANA through relational universe so that only dim column view gets invoked

Further Info:-

-- There is left outer join b/w Fact and Dim

-- Non of join column are calculated column

-- There is no design time filter on Dim view

[lbreddemann · Accepted solution]

Hi Nitin

first of all: please avoid emailing me directly in order to get me to look into your questions.

My involvement in SCN and other forums is purely voluntarily and I actually do have a day job that produces more emails than what is good for anyone.

Appreciated.

To your question: your expectation is wrong here.

When you query attributes from the star join model, you explicitly want the join to be evaluated, even when you don't query key figures. On a semantic level you are looking for those dimension values here, that are present in the cube.

And for that, touching the fact table is unavoidable (obviously).

For value helps however, this is often not required or wanted. For that, SAP HANA allows to assign value help views to each dimension. And this is where the dimension views can be easily reused.

[Former Member · Accepted solution]

Hello Lars,

Foremost I want to thank you for quick elaborate response. Secondly, I would like to apologize if I disturbed your work schedule and assure you I won't email you directly. My intention was not to disturb your day work schedule but just grab your attention to respond this issue like when I see your response for other posts which then you can reply on priority next available time slot . Plus this issue seems so fundamental and logically obvious, I and others here in team was scratching head why is it so the way it is ?

Now coming back to issue, I quite don't agree the way HANA star join is designed then. If I consider HANA view as just another sql view then I am ok with behavior however its purpose is greater ; to act as front facing view for BI consumption - directly by BI client tools (excel,crystal,webi, Lumira, design studio etc) and indirectly through IDT. At semantic level, it should act very similar to how universe plays a role. I have noticed, irrespective of how one connects (relational or olap) it doesn't show semantic wise grouped as dimensions (and characteristics/attributes underneath) and measures. In my opinion, it should show like as BEx query designer show when connected with multi-provider. Please note people are expecting to do away with universe as webi can be connected directly with HANA view and if this is the behavior then I fear it will defeat the use case of direct connection with HANA view.

Lars, please note I am not criticizing your response of how HANA is handling this but just pointing how it should be either by star join way or some other way.

Lars, could you please elaborate on "SAP HANA allows to assign value help views to each dimension". I see how value help can be assigned to variable/input parameter but don't know exactly how it can be assigned to a dimension view/attributes and how it will behave when consumed in IDT or other tool.

Ultimately, what I desire when an attribute of a dimension view is queried on, it should displays only all value and not get disturbed by its join with fact table etc.

hope you hear back from you soon.

Thank you Lars.

Regards,

Nitin

[lbreddemann · Accepted solution]

Ok, I understand your view on the matter and how you and your team arrived at the expectation you laid out.

Now, I won't try to defend the way the Star Join view is provided, but I like to point out, that there are more than one types of requirements for such value help views.

One approach for those is to say: show the user every possible value (based on the available data in the dimension/master data).

Another approach is: only show the values for which there are actual records in the fact table (booked values in an InfoCube to use BW terms).

Both ways can be reasonable and by simply querying the dimension column in the star join view, you are following the second approach. And this makes sense also on a semantic level: you query dimension data in the context of this specific cube.

One important point about the way dimensions are modeled is that these are also calculation views of type dimension. This enables these dimensions, actually representations of master data, to be shared and reused in multiple cubes/star join views.

It also allows to access the master data outside the context of a specific cube. And this is what you seem to want to do here.

In order to model this in SAP HANA you can choose a table or calculation view and assign the to-be-filtered column in the star join view/cube separately:

Note how I selected my dimension view devTest::dim10 here as the value help input.

It could've also been a base table or a completely different view (maybe one that would show more additional information).

This way of modelling allows for you to have it "your way" of displaying the value help data easily.

[Former Member · Accepted solution]

Hey Lars, good to see you understand the expected behavior now.

For two approaches you talked, say approach1 and approach2. These both approach can be made available depending on semantic layer tool's design and on business need. For example, in universe, irrespective of type of join between fact and dimension, if one queries only dim's attribute it only queries dim and not fact. So the motto remains touch only those tables/views from where columns are requested (directly or indirectly through calculated objects)

Approach 2 can/should only kick in when in query includes any attribute/measure from fact table; there too it should depend on join type  if left (to fact) or inner then only fact's dims should show up however if right outer then all dim values associated with null/zero measure value signifying no event/measure has happened for those dims (another business case).

Coming to your illustrated example of value help, the problem is whether end user want or not, this variable will always show up as input prompt in all reports/BI object. That's not desirable in general even when end BI tool may get some sort of capability (which is not as of now) to suppress as needed unless developer of view purposefully introduces the variable or input parameter.

One solution could be to imitate the functionality of a universe in HANA view using star join or some other way. Or else BEx query designer kind of approach which I think won't be best as this will proliferate bunch of queries stored in environment wherein most will not be useful to all as it will be to specific report need. In this regard, in IDT there is HANA business layer which looks comparatively promising (still long long way to go) but again its outside HANA. In the same go putting everything (semantic handling) on HANA may be overkill for HANA development team so leaving who does best to do the job could be better option i.e. to say let IDT handle semantic on IDT. But then all customer has to have IDT/universe and can't usefully connect to HANA view. So you see option are there but have pros and cons.

Lastly and all in all my recommendation to SAP HANA dev team will be to use IDT as semantic layer for HANA view BI consumption as IDT is not going anywhere (vis a vis other DB existing) and you need not re-invent another great tool given existing great tool from BO.

Let me know your viewpoint. Thank you Lars;its been great idea sharing.

Regards,

Nitin

[jon_humphrey · Accepted solution]

Hi Nitin,

I sympathise and agree with you.  In fact I brought this up in

http://scn.sap.com/thread/3868317

We hit this hurdle when evaluating BO4.2.  The functionality that Lars describes seemed so promising  - use a dimension view for lookup purposes - perfect.  Unfortunately one other significant issue is that with the value help you cannot show a name value pairing in the list that is produced (so I can't have a nice pretty dropdown with material number and material description for example unless you create a universe).  Also tools like design studio, Lumira and Webi only connect to aggregated type models such as Analytic views or Aggregation Calc Views so for drop down purposes we are looking at producing copies of dimension views with a dummy measure - ugly.  It seems like the functionality is basically there behind the scenes and just needs the 2 product groups to have a conversation.  I have always felt that the universe was a redundant step in the development process for analytics on HANA but we are not quite able to see the back of it.

Thanks,

Jon

[lbreddemann · Accepted solution]

You might be right, that the complete end-user experience needs adjustment here.

Sounds like both of you expect complex interaction semantics like SAP BW and BEX support them.

This then needs to be supported by the front end tools that leverage what the SAP HANA platform provides. As you correctly mentioned, the functionality is there:

For a cube like structure the developer can declare which table or view should be used for value help lookups. Showing the complete records or just the filter attribute really needs to be decided by whatever UI is used.

The data preview function certainly should not be mistaken as the reference UI or one that show cases comprehensive user interaction. It's a development and testing tool...

Personally, I am clearly not the front end person, so this discussion would probably make better progress in the respective spaces of the UI/reporting tools.

[Former Member · Accepted solution]

Thanks Lars and Jon for your valuable input. Now I would like to summarize our discussion herein:

As of HANA SP11,

1) Querying a HANA view with star join with any join will always result in execution of fact table part. The effect is even if one query an attribute of a dimension, it will result in execution of fact table and not dimension table only.

2) For the consumption in BI, its desirable the querying a dimension should result in execution of only dimension view and not fact. The effect is LOVs runs slow un-neccesary. Refer how a star schema operates say in universe.

3) All use cases are possible, see all dim values or only those related to facts only. However, star join, as it seems to a BI developer, will seems to be a regular star schema where querying a dimension should result in only querying dim and not fact. In BEx as well, its the same.

4) In absence of above, I don't see how well HANA view can be directly consumed in BI tool.

5) In my opinion, SAP HANA/BI front end dev team has to think better in improving the semantic layer and integration with BI tools where integration is not name sake but support existing workflow in various data sources. Otherwise it leads to various patchwork, workarounds resulting inefficient and less maintainable BI.

6) With regards to point #6, it seems to me a query tool of kind of BEx query is need which is more OLAP aware (than Universe) and in addition may be universe can also be better integrated.

Thanks once again.

Regards,

Nitin

[Former Member · Accepted solution]

Hey Jon, thank you very much for your input. I am curious to know more about your evaluation of BO 4.2 as we are also planning for the same. Could you please share your evaluation result (may be another thread) or point to others who would have done the same.