Reporting on (non-materialized) transitive attributes
Reporting on a transitive attribute, aka an attribute of an attribute, can be a tricky thing to model if you don’t want to materialize the transitive attribute(s) in your data provider.
Imagine we have an infoobject Z__ATE which represents a calendar day and contains many (date related) attributes, like fiscal period (FISCPER), calendar week (CALWEEK), etc
All of these attributes have a direct relationship with the Z__ATE (calendar day) and have been created as a navigational attribute. So whenever and wherever Z__ATE is added to a data provider (being a multiprovider, composite provider, etc etc) its navigational attributes can be used.
Further imagine we also have an infoobject called Z__SVL which contains Z_COUNTRY, Z_REGION and Z__ATE as a navigational attribute.
The above example implies that we can use Z_COUNTRY, Z_REGION and Z__ATE to navigate, but we’re not able to use the attributes of Z__ATE to navigate. The attributes of Z__ATE, in this example CALWEEK and FISCPER, are so called transitive attributes and can’t be used to navigate, when using infoobject Z__SVL
When reporting on transitive attributes is required and you don’t want to materialize the transitive attributes (in this case add CALWEEK and FISCPER as navigational attributes to infoobject Z__SVL), using a composite provider might come in handy.
The below composite provider has been created (tcode RSA1, select InfoProvider, Right mouse click on an Infoarea and select Create CompositeProvider) in which a Proof of Concept (POC) DSO is combined with multiple entities of infoobject Z__DAT (which is the same as infoobject Z__ATE).
Instead of materializing (adding navigational attributes of Z__DAT to the POC DSO), a non-materialized link (left outer join) has been created multiple times.
For example: An “changed on” infoobject (see the red box above) from DSO ZPOC has been added to the composite provider and this infoobject is (inner) joined with masterdata infoobject Z__ATE. (see top right in the picture above). Via this modeling solution a transitive attribute (all navigational attributes of Z__ATE) can be used for reporting on this composite provider, without materializing them.
(This blog has been cross-posted at http://www.thesventor.com )
thanks for the interesting post.
Do you have any experience, how the performance is impacted by this method compared during run time?
Thanks in advance,
Thank you for your reply.
I have some benchmark figures when comparing the old situation (in a BW on a non HANA platform) vs the new situation (Composite Provider (CP) in a BW on HANA environment)
Eventhough the data is not materialized in the CP, the reports are way faster then the materialized data in the old situation. But I believe this is one of the many business benifits when using the SAP HANA platform 🙂
the possibility to build this with a BW on RDBM should be available as well, right?
We're still on such a system, so I would be interested, how much performance is lost, if you use the composite provider vs. materializing in the cube. I believe you, that it's still faster in this way on a HANA machine 😉
I'm just wondering if it's worth a try the next time, I'm asked to build something in this direction, or if I should stick to "the old way" until HANA.
Nice case for a Proof of Concept 😉 I believe on a traditional RDBMS BW environment, the querying on the CP will be (much) slower than on the materialized cube. But, on the other hand, I believe you're way more flexible when it comes to remodelling and reloading.
I definitely would give it a try next time!