Skip to Content

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

/wp-content/uploads/2015/06/pic2_735829.jpg

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.

/wp-content/uploads/2015/06/pic5_735923.jpg

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).

/wp-content/uploads/2015/06/pic3_735924.jpg

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 )

4 Comments
You must be Logged on to comment or reply to a post.
  • 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