Hi all,
I googled and searched the forum but I cannot seem to find much so this is why I'm asking here.
My situation is as follows: I am on On HANA 1 SP12 and I'm trying to build a CUBE calculation view with Star Join. My data foundation (fact table) has keys for my dimensions, and I already created DIMENSION calculation views on top of my dimension tables which I can join in my CUBE calc view. So far so good.
Now, my fact tabe is such that it has several dimenion keys that reference the same dimension table, i.e. these are "role-playing" dimensions: same dimension table is used in a different role in the star design, like "order date" and "shipping date".
So, what I would like to do is to bring in the same DIMENSION calc view multiple times into my star join. Except, HANA studio does not like it:
I realize that I could create multiple DIMENSION calcviews on the same dimension base table, but the issue with that is that my DIMENSION table has additional semantics defined, like hierarchies, which I would then need to recreate.
I also tried to build my role-playing dimension calcviews on top of my original DIMENSION calcview, but then I will lose things any hierarchies I created in my original DIMENSION calcview.
With attribute views there is this feature called a "derived" attribute view, which looks exactly like the thing need, but then for calc views. Alas, it doesn't seem to exist? What would also work for me is if I could use attribute views inside the star join, but that only seems to accept DIMENSION calculation views.
So, in short: how do I handle role-playing dimensions in CUBE calculation views, preferably without duplicating maintenance for my role-playing dimensions?
To my knowledge, the concept of the "derived view" had not been transferred to calculation views.
I would probably go down the route of developing the dimension view as far as possible and copy the final variant for each different "role" the dimension needs to play.
This is, fairly clearly, added maintenance in the situation where there really is the 1:1 relation between all "derived" dimensions and only the semantic meaning of the relationship to the fact table changes. However, in many situations a change in the "role" that a dimension plays comes along with functional changes and at that point the dimensions stop to be the same but diverge.
But, yeah, that's what I make of this.
If this is a thing that really impacts you negatively, maybe it's something to put into the feedback tool for product improvement.
Hi Lars,
thanks for the reply, I really appreciate it.
In my experience, the role playing does not change the properties of the dimension and I have to admit struggling to find an example where the dimension properties itself change along with the role. In my book, that probably would not be a "role-playing" dimension, but simply two independent dimensions.
I'll take a look at the feedback tool.
Hmm... do you have this requirement for anything else than time/date dimensions?
Wondering if this might be a special case for the “same but different” dimension in the same star join.
I'd say date/time is the most ubiquitous example, but there are other examples as well. Location pops up regularly: in a travel cube, point of departure and arrival can very well point to the same location table, and the same location can appear as either departure or arrival, depending on the journey.
The concrete example I'm now working on now is a two player game: player 1 and player 2 point to the same persons. You can tell it's a "real" role playing dimension, since the same actual persons can be either player 1 or player 2 in multiple games.
I'm sure I can find more examples if you like me too.
Good examples, thanks for that. I see the point that you would have such "role-playing" dimension when you model graphs (even point to point relations) like that.
I'm not sure, that I would necessarily design cubes in that way, but that's beyond the topic of this discussion.
Unfortunately, I cannot offer any better advice here.
Hi Roland,
My 2 cents: can't synonyms helps you to keep your semantic on multiple views ?
Regards,
Frederic
> My 2 cents: can't synonyms helps you to keep your semantic on multiple views ?
I don't know. Can they? To be clear, I'm not trying to make one table appear twice. I am trying to use a dimension calculation view, along with all its semantics - in particular, hierarchy definitions - twice in the same star join. I was not aware one can make a synonym on a calculation view. If that is the case, can you please link me to the docs that explain how to do this?
Thanks in advance!
Kind regards,
Roland.
Here is an example of a synonym creation on a calculation view :
CREATE SYNONYM "PP/CA_PP_SYN" FOR "_SYS_BIC"."PP/CA_PP_VIEW";
I'm not sure at all it can help you keeping your semantic (hence the 2 cents). Maybe you can have a try.
Regards,
Frederic