There is a lot of wild guessing on how to combine the native HANA tools and assets – like HANA modeler or SLT* – within a BW-based environment. Initially, one might assume a conflict between a completey managed table schema – as imposed by BW – and an arbitray table schema – as created by SLT, Data Services or any other tool. In this blog, I will indicate how such schemas can happily coexist and consequently complement each other in a BW-on-HANA environment. Before I start please note that the usual disclaimer applies.
The question laid out in the title of this blog is one that will determine our conceptual efforts well beyond the pending HANA and BW 7.30 – Part 2. However, ORANGE will already offer some interesting pieces that have already hit the nerve of some of our pilot customers in a positive sense. In order to understand the extent of this I’d like to start shedding some light into what’s happening:
- BW – as many other products in the BI and EPM space – follows the approach that data models are created on a conceptual level. The best example is an infocube: the user uses characteristics, key figures, creates dimensions, sets properties etc. to the activate the infocube which, in turn, generates the physical layerunderlying the infocubes, meaning all the tables that represent the infocube physically. This becomes even more apparent when you look at the differences between the infocube in a classic BW (meaning BW sitting on a classic RDBMS) and in an ORANGE system (meaning BW-on-HANA): conceptually, the infocubes are identical but physically they are represented differently.
Modeling conceptually and generating the physics underneath has a number of advantages like
- query access generation can be optimized towards one pattern (like a star schema),
- it is possible to load data into a conceptual / logical object of a fixed structure (see bidirectionalizationof views),
- the user does not need to understand the implications of physical modeling (like do’s and dont’s, when and where to create indexes etc.),
- standard patterns (e.g. naming conventions) make it easier to maintain a table schema.
However, there might be a number of restrictions to such an approach. DBAs who are knowledgeable about table layouts, indexes and other relevant parameters for the physical layer might also argue that a hand-crafted schema can be (manually) optimized much better. This is correct but this becomes hard with data warehouses with 1000s of tables.
- There is also the opposite approach, namely defining a (conceptual) data model on top of an existing set of (physical) tables. The universe designer, IDT or the HANA modeler are instances of tools that follow this approach. The fundamental advantage is that it is additive, i.e. it complements an existing table schema with some additional meta data. One of the disadvantages is the singularity of the conceptual models. Thus many of the advantages of the managed approach are absent in such a case. This is especially annoying as the layering typically found in data warehouses requires anyway to create new tables – and frequently a lot: 100s to 1000s – for which standard practices need to be defined in order to allow maintaining the system, e.g. for lifecycle activities. Still, for a modest set of tables (e.g. as in a typical data mart) this is certainly a quicker and leaner way.
In summary it is fair to say that both approaches are attractive depending on the scenario. In other words, there is no general “good” or “bad”. To that end, it is appealing to look at ways to allow combining the two appraoches. Therefore, let’s turn to figure 1 and walk you through the general idea:
- Figure 1 pictures a HANA instance that hosts two schemas:
- one managed by BW – as indicated by the (LSA) layers and tables for infocubes and DSOs, and
- an arbitrary schema with some set of tables that have been created and filled via some mechanism like SLT or Data Services.
- In the arbitrary schema, there exist some join paths, indicated by the black lines between the tables.
- In the arbitrary schema and using the HANA modeler, tables can be combined to form an analytic view – see the grey polygon – which is basically a cube in the HANA terminology.
- An analytic view can be analysed (following the green path) using one of the tools indicated on top like Analysis Office(via BICS) or Using Excel on HANA (via MDX).
- Similarly, the schema on the left hand side is managed by the BW application which provides modeling tools, editors, lifecycle management tools, process definition (for moving data), scheduling and monitoring etc. infrastructure for that schema.
- The BW-managed schema receives data from SAP extractors, other extractors, files or Data Services. Additionally, data is generated via the planning infrstructure.
- BW’s analytic engine interpretes the semantics of the data models and translates it to HANA-optimised query accesses which are processed by HANA’s calculation engine. Data is exposed via various interfaces, mainly BICS and MDX (in blue).
Let’s look at 3 “bridges” – see the dark red numbers in figure 1 – that allow to link data from both schemas to each other:
- An analytic view can be exposed as a transient infoproviderin BW. Transient means that the analytic view is translated to an infoprovider at query runtime. This, in turn, means that any changes in the analytic view will automatically and immediately be visible in the corresponding transient infoprovider in BW. It is possible to build BEx queries on top of such an infoprovider and to use that query in all sorts of ways.
Furthermore, it is possible to map an attribute in the analytic view to a characteristic in BW – within the context of the transient infoprovider**. Mapping to characteristics in BW has the advantage that hierarchies, navigational attributes and other features related to the BW characteristics (e.g. security) can be reused. Basically, this combines data / tables in the BW-managed schema with data / tables in the arbitrary schema.
- There are the BW workspaces. Here, composite providers can be modeled by an end user. Those composite providers can incorporate, amongst many other artifacts, analytic or calculation views in HANA (i.e. from an arbitrary schema). Please check the material on the BW workspaces for more details on all the options that this feature provides.
- Finally, a table, an analytic or calculation view in a HANA schema can be accessed via a BW DataSource. This is based on DB Connect using a second DB connection to the underlying HANA DBMS.
In the longer term, there is certainly much more potential in such an approach. However, for the ORANGE scope and timeline it is a first good shot in my opinion.
* SLT = SAP Landscape Transformation or System Landscape Transformation
** That mapping, by the way, is – unlike the analytic view ⇒ transient infoprovider mapping – persisted and applied every time. Transaction code is RSDD_HM_PUBLISH.