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 )
Hi Sven,
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,
Philipp
Hi Philipp,
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 🙂
Grt
Sven
Hi Sven,
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.
Regards,
Philipp
Hi Philipp,
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!
Gr
Sven