Integration of External Data Structures with Open ODS Views

Integration of External Data Structures with Open ODS Views:

◈ Modelling options with Open ODS Views.
◈ Consuming external data in SAP BW.
◈ Combining external data with data in SAP BW.

What are Open ODS Views?

Open ODS Views also allow you to use analytic functionality on top of external data structures in a very simple way. Analytic functionality is all the stuff you do using the OLAP engine, the OLAP processor. So all the calculations and all the functionalities which you use in the Query Designer, all of this can easily be put on top of external data once you use Open ODS views. This means we have here really an integration possibility of external sources, external data into BW. We are able to work in a field-based fashion. This means we are not forced to create an Info Object for every column of a source, so we can really freely design and define data definitions or, in this case, the system is already proposing a definition based on the source table definition and we can work with that in BW as the data would be in a Cube or in a DataStore object or wherever, but it’s a virtual object directly accessing that and with that, supporting agile modelling approaches and prototyping very easily.

Classical BW and reviewing the classical BW modelling Paradigm.

Classical BW is based on InfoObjects. So when creating any object in BW, we will have to start with InfoObjects. For example, a simple field is always represented by an InfoObject. An InfoObject basically defines the data type of this object. It also defines the information like is it a characteristic, is it a key figure, which is very important for analytic purposes because characteristics are used for drilldown, key figures are used for aggregation. So this distinction is directly made in the definition of an InfoObject. Other properties of InfoObjects, especially in the case of master data, are important. Like text information. You might have language-dependent text and Hierarchies. All such properties are encoded in an Infoobject.

We basically load data from sources into either InfoObjects or InfoProviders which we again construct out of InfoObjects.

But in case I would really like to, in an agile way, bring up a new data set, it would also mean that if I have a table with 200 columns, I have to create 200 InfoObjects. And this was complained by many customers, by many users, that this is not agile, not fast enough. We can now do this with Open ODS Views.

 Open ODS views actually take the other way around. You are given a source structure or multiple source structures which come with field names and data types. And you only add the information which is missing, the analytic semantics. For example, you start with a table, look at it, and we have to decide whether it’s master data or transaction data, whether it’s facts or dimensions. That’s part of semantics.

If you go to the field level, you look at a field and decide this is more of a characteristic or it’s more of a key figure. So we use the metadata which comes from the source and we only add what’s necessary. That’s the idea of this modelling approach.

Example: We have a fact table, the sales order table, and I have related master data like the business partner, like a product, and so on. I would like to not only virtually access the sales order because those are just my facts but I would also like to see what is the name behind the customer ID or something like that. And I can, via the semantics, we can associate the different tables to each other and this is exactly what the idea is about of connecting the Open ODS views to each other. That’s what the green lines here try to indicate.

You might think of one Open ODS view representing the sales order facts and one Open ODS view representing, say, the business partner master data. Of course, there is a certain link between this. There is one view called Business Partner in the facts and you have additional information about this in the other table. And linking this by an association basically allows you to leverage all the attributes of the master data field in all the reporting you do. When we speak about source, it means that this Open ODS view can consume the data out of many different sources. And we have different technologies for accessing the sources. It could be, for example, the same HANA database, so meaning we are accessing a second HANA schema next to the BW where the data is located. It could be a better source in BW so that we can access whatever, an ERP system or something like that. Or it could be related to the SAP HANA smart data access, which is a brand new technology. But this is allowing us to directly consume, for instance, sources like a Teradata instance, like Hadoop sources.

Demo Screenshots: Open ODS views can be modeled inside Eclipse environment.

Screenshots show you, first of all, an existing Open ODS view and explain the details of what it really means. Then we are going to create an Open ODS view as well. In screenshots we are going to show you how you can combine Open ODS views with existing master data InfoObjects. I think in this case it will be the product. We will show you how to use facts from an Open ODS view and combine them with master data from the product InfoObject.

 This new Eclipse-based modeling environment is totally SAP HANA-related. This means the Open ODS view is a HANA-related feature because we need certain functionalities of the database. We need a certain speed for doing the temporary calculations in the background on the fly. That’s really the reason why this is exclusively with SAP HANA. And the features we are showing are also related to the BW release: SAP BW 7.4 SP8.

When opening an InfoArea here, and here in my Favorites I have a number of InfoAreas, we’re looking at the sales order Open ODS view.

If you look at the Overview tab of this Open ODS view you see some general information. we’re looking at the sales order Open ODS view. If you look at the Overview tab of this Open ODS view you see some general information. That also means if we later would do reporting in top of this object, we would have the functionality of a key figure available because the key figure is something we think, this is fact-related to a fact table, so we have certain functionalities here now available in comparison if we would choose the master data type, which will be our next example.

On the left-hand side you see the structure of the source object. The source object in this case, if you look at the top line here, says it’s a database table or view. And here you see the name of the view and the database schema in which it resides. Here you see the names of the fields and you actually also, if you scroll a little bit, see more of the technical information about the field, namely the data types and length. From this information, the system proposes certain characteristics or certain properties of the fields. For example, by analyzing the data types, we make a proposal whether a field is rather a characteristic or a key figure. You could possibly want to drill down by an integer or you could want to use it as a counter, for example, as a key figure. But the system does a proposal. You can change this proposal by dragging and dropping fields between these different folders. And on the right-hand side, you can basically fine-tune the information about this field. So you could also override the name. You can change the name, you could change the description. You can, for example, define whether a field is authorization-relevant. All this is done here. This is then going along with the BW authorization. This is again an integration point between the BW authorization feature and this directly consuming Open ODS view.

How to bring this transaction data from an Open ODS view together with the product master data which we have in an InfoObject? So what you do here is you select the PRODUCT_ID field and you associate it with an InfoObject. I think it’s one of the content InfoObjects which is delivered with the Enterprise Procurement Model. It’s called OD_NW_PRID.

Why are we doing this? Because we have already a harmonized master data object here in BW where we are sure that we have cleaned and harmonized master data and we would, of course, like to leverage this to see and to assign now the IDs popping up out of this Open ODS view with our master data in BW. And we can reuse all the services like, for instance, the navigation attributes like authorizations, and so on. And maybe the most fundamental one should be here that we expect to see a text now because this product InfoObject certainly contains a language-dependent text in the text table which we loaded before.

Activate the BW object.

Go into Analysis for Microsoft Office, open this Open ODS view. It’s represented as an InfoProvider under the same name.

Open this ODS View. The system now runs the default query on this InfoProvider which uses all the key figures in the columns and gives you all the characteristics as a potential drilldown. Drill down by product ID. We would expect not to see the key, but actually the text representation of the InfoObject.

We leveraged also a bit of the OLAP capabilities by assigning the texts to the ID. You see the text assigned instead of consuming the raw data directly. The first step in showing you how to combine external data of Open ODS views with BW InfoObjects.

Another approach, for example if we take the business partner here.

The business partner in the EPM model is also represented in a table, a database table representing the business partner master data.

So why not use an Open ODS view on this database table and associate this Open ODS view to the field Business Partner.

Just as we did with the InfoObject, we could also, as we saw, associate another Open ODS view.

From a functional point of view, this should actually be the same.

Screenshots from Demo:

*Create a new Open ODS view.

*We link this new Open ODS view, which will represent the master data, together with the facts represented by the other Open ODS view because the source is already having this association and we are representing it now in BW and have a model built there via two Open ODS views on the fly. Here, we first associate certain multidimensional semantics to this object.

*The business partner is probably of type master data. It’s a dimension object.

*Select the source. We pick Database Table or View using HANA smart data access in this scenario(Source Type).

**All points are in screenshot below.

Click on Finish.

Go to Master data Tab:

On the left-hand side, We have different view names, because it’s a different structure.

You again see the data types and length information. And the system has made a proposal, assigned some of the fields to Characteristics, some to Key Figures, and actually detected a Currency.

It’s a table from the Data Dictionary, so it provides more information than a plain database table would provide.

We know there is a currency code in there and we should not stupidly aggregate all the key figures because it doesn’t make sense. And we would expect, like in a query,

I also demonstrate later in screenshot below, that it would be then assigning a star for hey, I cannot aggregate the data.

Now, especially in the case of master data, it’s very important to maintain the keys correctly. We have some folders for keys up here. So there’s the BP_ROLE, which is a key field. You have to study your model to know your keys.

COMPANY_NAME field makes sense to use one of the fields on the left-hand side as short, medium, or long text. I mean, if you drill down by customer or business partner, then seeing the company name of the business partner is probably a good idea.

This means also that that can assign one field of the source multiple times through the target structure. And it could even be that we have a field representing a key figure and a characteristic at the same time. This is also totally new for us in the BW world.

Activate the Open ODS View.

**Now we’re going to go back to facts, to the previous one which we looked at earlier. And we’re going again to the Business Partner field and now associate the Open ODS view which we just created. This is now establishing the connection between the two views.

If we drill down in the view, you will see that it will jump into the definitions and the characteristics maintained in the other view.

Now we have to maintain the join condition correctly. We have two key fields and we have to make sure the join condition is maintained properly.

Click on Compounding and maintain the join condition.

We can use one of the fields of the business partner structure as a navigation attribute, for example we use Legal Form here. You would then expect, if we run the default query again, to see this field in the drilldown options.

Activate this ODS View.

Jump back into Analysis Office. And we reload the query and we will see if we see the text and stuff we assigned there. Open the DataSource, so open the Open ODS view. That’s the fact Open ODS view and the fact that we added this association should now be somewhere reflected in certain places in this drilldown. So what we see here is we see the legal form as an additional drilldown possibility because it’s actually joined from the master data to transaction data.

And we should also see that the business partner is now available with a different name. If we drill down by business partner, we should not see the business partner ID,

So you see that adding associations and assigning semantics like facts and master data to the objects themselves, that’s basically enriching the given structures to really make it more similar. enrich it with information which you already have normally in a BW model so that the analytic engine of BW can easily consume it. And, as we expect here, we see the names of our business partners.

Important Point: let’s remove this drilldown for the moment. Maybe you’re wondering why you are seeing the stars here. That’s also something which has to do with the semantics we added.

If you look at the key figures here, for example, the GROSS_AMOUNT. The GROSS_AMOUNT has a currency code associated, that’s this CURRENCY_CODE field.

We mentioned this earlier. If we look at this here and drill down by currency code, then we should actually see the star replaced by the individual currency codes and the summary line will still contain the star,

because the system knows that it doesn’t make sense—here you see the currency codes— and the system knows that it doesn’t make sense to sum up these individual numbers because they have different currencies associated. And this is now pure BW functionality based on an external source.