The Age of Virtual Reality
In the days of old when Wi-Fi wasn’t readily available and Google was nothing but a misspelling of the word googly, BW could not join virtual InfoProviders based on external data. Not any longer. Why? Because of the Open ODS View (OODS) & the HANA CompositeProvider (HCPR). How much storage space does it take to store both these InfoProviders? The answer: virtually nothing (pun intended).
These virtual InfoProviders are not new and neither is their platform, SAP HANA. The OODS & HCPR have been available since BW 7.40. To use them you need to install Eclipse and the BW Modeling Tools. After that, you can start modeling them in the BW Modeling Perspective (instead of RSA1). However, as an SAP BI consultant I’ve noticed that most customers have often either:
1) Made the step to HANA, without utilizing the new modeling possibilities or
2) Not yet made the step to HANA at all.
Furthermore, amongst my fellow consultants I have noticed a strong movement towards discovering the full potential of virtual modeling and HANA. Part I and Part II of this article will give you a general idea about how the Open ODS View and CompositeProvider work, so you can understand their potential.
Here’s an overview of the topics discussed in Part I of this article. You can click them to jump to that section:
- What are the OODS & HCPR and what are they not?
- Open ODS View Source Types
- DataSource (BW)
- Database Table or View
- Existing Source System
- How to create a HANA Source System
- Owner / Schema
- Virtual table using SAP HANA Smart Data Access
- Existing Source System
- Owner / Schema
- DataStoreObject (advanced)
- General Tab
- Master Data
- Pros & cons
What are the OODS & HCPR and what are they not?
When I talk to people about the Open ODS View and the CompositeProvider, the first thing they usually ask is: what’s the difference?
The difference between the two InfoProviders can be summed up as follows:
From Table 1 we can gather that although they are both virtual objects, we use them for different purposes. Usually when I hear somebody describing a CompositeProvider they might say: “A CompositeProvider is like a MultiProvider”. However, this is only partially true. The CompositeProvider and the MultiProvider we know from older BW versions can both execute Unions. The CompositeProvider is more advanced though, as we can gather from Table 1, because it can also perform Joins (like the old InfoSet). The difference between a Join and Union? Simply said, a Union adds the rows of two datasets on top of eachother. A Join adds columns of one table to another based on matching values in shared columns.
If the CompositeProvider is so advanced, why then use an Open ODS View? The main reason is the Open ODS View’s ability to connect to external tables. Let’s say you have data sitting on an external database that you want to use in BW, however you don’t want to persist it. You can then set up a SDA (Smart Data Access) Connection to that database and create a Virtual Table.
After setting up an SDA connection and creating a Virtual Table, you can create an Open ODS View based on that connection and voilà: you have a virtual connection to that table. Any query on this Open ODS View will then connect to an external table at runtime. However, the query will be dependent on the external system, the connection to the external system and the amount of data pulled in, for performance and availability. Querying external data at runtime can save space though (and therefore money). For those reasons, you need to think about both cost and performance, when choosing whether or not to use an OODS, .
In the following sections, I’ll walk you through the Open ODS View creation process. In Part II of this article I’ll discuss the HANA CompositeProvider and its properties.
Open ODS View Source Types
There are a lot of options to pick from when creating an OODS, as we can see from Figure 1 below. I’ll (briefly) discuss each of the 5 different Source Types and Semantics. I’ll start with the Source Types.
1. Source Type: DataSource (BW)
Let’s start off with the first Source Type option. The DataSource (BW) option refers to the DataSources you probably know all too well, if you have prior experience with BW. Using these DataSources, you can connect to an underlying SAP ECC system for instance. You need to have first created and configured the DataSources first, of course. Also, the OODS does not accept File-based DataSources.
Creating an Open ODS View using this option is quite straightforward. First you right-click one of your InfoAreas in Eclipse, or right-click your Graphical Data Flow Modeler if you’re using BW/4HANA, and choose to create an OODS. After selecting DataSource (BW) and a Semantics type (any type will do for now), you press “Next”.
After pressing “Next”, all you have to do is select the DataSource you want (see Figure 2) and you’ve finished setting up the Source Type!
2. Source Type: Database Table or View
Database Table or View is the option you choose when you want to build an Open ODS View on a table residing in your local HANA database. Right off the bat we can see there’s something distinctly different in Figure 3.
We are prompted by the screen to choose between Existing Source System and Owner / Schema. What could that possibly mean? I’ll explain:
Existing Source System
When you select Existing Source System the system checks whether you have a HANA Source System (see Figure 4). A HANA Source System points towards a specific schema in your HANA Database. HANA Source Systems let you use any tables in that schema you want for your Open ODS View.
How to create a HANA Source System
If your first reaction to the words “HANA Source System” is one of bewilderment and confusion, no worries: I’ll explain how to create one. First, head to transaction RSA1 in the SAP GUI and go to Source Systems. Then, right-click your SAP HANA folder and select “Create”. You’ll then see the following screen:
Enter a Logical System Name, which will be the technical name of your Source System. You could for instance give it the name of your schema, so you know with which schema it connects. Also, you need to give your new Source System a description. After that, press the green button.
Now we’re only one step away from completing our Source System. In Figure 6 we see the different possible Connection Types. If you have a local SAP HANA database schema, you choose the first option. If you want to set up a Smart Data Access connection to an external table, you choose the second. Because “Database Table or View” concern local HANA tables, we’re going to pick “Local SAP HANA Database Schema”.
Now you can pick any table in the schema your Source System points to, when creating an Open ODS View based on a database table or view. You simply select the Source System you just made and press “Next”. On the next screen you’ll be able to pick any table in the schema your Source System points to.
Owner / Schema
Whenever you select “Owner / Schema” on the first OODS creation screen, the next screen will ask you to select a schema.
After you’ve selected a schema, you need to choose the table you want:
After we press “Next” in Figure 9, we can tell the difference between the “Existing Source System” and “Owner / Schema” options (see Figure 10):
You can either select an existing Source System or create a new one in Figure 10. Either way, you’re going to need a HANA Source System, before you can start modeling your OODS.
Conclusion: the “Existing Source System” and “Owner / Schema” options are basically 2 sides of the same coin.
- When you pick “Existing Source System”, you pick a Source System that points to a particular schema and then you pick the table you want.
- When you pick “Owner / Schema”, you first pick a schema and then pick the table you want. After that you can select an existing Source System that points to the schema you chose. If you haven’t created a Source System for that schema yet, you can create a Source System on the spot. Disclaimer: the automatic Source System creation option does not always work, I got an error message (see Figure 11). If creating a Source System via the Owner/Schema option does not work, follow the instructions that accompany Figure 5 – 7 to create one manually.
After you create an Open ODS View based on a database table or view, you might see the following error:
If you get this error you need to grant your BW system user rights to whatever schema you want to build an OODS on. You do so by opening the SQL Editor in Eclipse and using the following statement in Figure 13 (don’t forget to replace my schema with yours):
If you wrote the statement correctly, you should see the message in Figure 14:
After that you should be able to start modelling and finish creating your OODS, without worrying about privileges!
3. Source Type: Virtual table using SAP HANA Smart Data Access
This is the option you choose when you want to connect to an external table, such as a Hadoop table, without using a DataSource. Creating an Open ODS View using a virtual table is very similar to creating one based on a database table or view.
Existing Source System
If you’ve already created a Source System that points to the right schema, you simply select “Existing Source System” and pick the table you want (see Figure 15).
If there’s no Source System yet, you can create one as we did in Figure 5-6. Instead of selecting “Local SAP HANA Database Schema”, this time you select “SAP HANA Smart Data Access”. I hereby assume that you’ve already set up a working connection to a Remote Source, which is outside of the scope of this article. Also, remember that you need to have the necessary privileges, otherwise you’ll get an error while creating a Remote Source. You need the CREATE REMOTE SOURCE system privilege. Also, you need to create a Virtual Table on the table you want to base this OODS on. For that, you need the object privilege CREATE VIRTUAL TABLE.
Owner / Schema
If you select the “Owner / Schema” option on the first screen, you’ll be prompted to select a Remote Source on the second (See Figure 16).
This remote source can be Hadoop for instance. Once you click next you’ll have to select a remote database:
Depending on your Remote Connection, you might end up in a situation comparable to Figure 17 where your only option is [<NULL>]. No reason for panic! If you see this just select [<NULL>], then press Next and you’ll be able to continue. In the next screen, you’ll have the option to create a new Source System or use an existing one (comparable to Figure 11). After that you provide the semantics to your fields (which I will explain later).
4. DataStore Object (advanced)
You can base an Open ODS View on an ADSO using this option. Open ODS Views are generally used to pull in data from external systems. However, the ADSO Source Type can still be of use if you want to specify whether a field-based ADSO contains text, master data or fact data. Field-based means: fields modeled without InfoObjects.
You might wonder: why would you want to do that? By basing an OODS on an ADSO, you can define that ADSO as Master Data. Defining an ADSO as Master Data lets you use the underlying ADSO as Master Data for Fact-table type Open ODS Views and CompositeProviders. Also, if you want to convert the data type of your ADSO fields, you can use an OODS View on an ADSO to convert the fields by associating them with an InfoObject. That way you can convert the fields at runtime and you don’t have to create another ADSO to convert the fields. Converting the fields at runtime saves space (but costs performance).
After you select an ADSO on the first screen, you’ll come across Figure 18:
You simply pick the ADSO you want, and press Finish to start modeling your OODS.
After having read all of these Source Types you might be wondering: how do I transform my virtual data at runtime? After all, if you right-click an Open ODS View you do not see any option to create a Transformation (see Figure 19).
Well, I’m here to deliver you the good news: you can transform your virtual data while you pull it in from an external source. However, to do so you need 3 objects: a DataSource, an InfoSource and a Transformation. Keep in mind: there will be a performance penalty, dependent on the complexity of the transformations you use. Also, you can neither use an OODS as a source or as a target of a Transformation in the Create Transformation screen. You can only base an OODS on a Transformation by following the procedure I describe below.
To create an OODS based on a Transformation take the following steps:
- If you haven’t done so already, first create a DataSource.
- After that, create an InfoSource. By first creating a DataSource and then an InfoSource, you can use the DataSource as a template and save time.
- After you’ve created both objects, create a Transformation by right-clicking the InfoSource and selecting the Create a Transformation option (see Figure 20).
4. Create an OODS and select Transformation as Source Type. On the following screen, you’ll be prompted to select a DataSource or InfoSource (seeFigure 21).
5. Select the DataSource you made during step 1, and the InfoSource you created during step 3. BW will automatically select the Transformation between your DataSource and your InfoSource.
Now you’ll be able to transform your data during runtime, when you execute a query based on your Open ODS View. However, I would only recommend using this Source Type for small datasets and for datasets with simple logic. For more complicated logic and larger datasets, you’ll probably be better off transforming and persisting the data with an ADSO.
After you’ve picked a Source Type, you end up at the General Tab. Here you can persist the OODS by clicking “Generate Dataflow” (see Figure 22). This option automatically creates an ADSO, Transformation and DTP for you. Alternatively, it can also create an InfoSource, Transformation and DTP.
In the General Tab you can also change the OODS description, remove a Source and add a new one. If CalcScenario Used: yes, you can use the OODS in Join in a CompositeProvider, otherwise not. You can also change the source of the OODS by pressing “Edit” in the Semantics section of the General Tab.
After you click “Edit”, you get the following screen:
You can change the source of your OODS by following the steps and selecting a different source object.
What are Open ODS semantics? Semantics let us categorize the data we use in the OODS. You can categorize the Source data as either Facts, Master Data or Texts. You can associate Fact-type Open ODS Views with Master Data and Text Open ODS Views. Association lets you use the Master Data OODS characteristics as Navigational Attributes and the Texts in the Text OODS for text handling, when you run a query on an Open ODS View or CompositeProvider. I demonstrate how association works in Part II of this article.
Which type of fields, such as Characteristics, Timestamps and Valid From fields, are available in your OODS, depends on whether you define an OODS as a Fact, Master Data or Text OODS. BW automatically maps the Source Fields to the View Fields, but you can change the mapping if it’s incorrect or incomplete. You can also add the same field to multiple target fields, and give those extra target fields different descriptions and associations. This makes the OODS flexible. In the following sections, I’ll further explain the difference between the different Semantics.
When you select Facts in Figure 1, a tab called Facts appears in the modeling section of the OODS after you’ve completed the Source Type section. In Figure 24 we can see all the Source fields (on the left) and the View fields (in the middle). Adding fields to your View is easy due to the drag & drop-interface. You define your Source Fields as Characteristics, Key Figures, Currency etc. by dragging them to the corresponding folder in the View Fields section. If you’re planning on persisting your OODS, it’s important to drag the key fields to the Characteristics (Key) folder. The fields you drag to this folder will turn into key fields in your generated ADSO.
Because we modeled this OODS without InfoObjects, each field gets a system-wide Unique Name. The system-wide Unique Name includes the InfoProvider in the name and the technical name of the field. For example, in Figure 24, our Transaction Code field has the name: 2FZOO_TR_ADSO-TRANSACTIONC.
Facts is the Semantic type you should use if you want to model a source table as transactional data. You can associate fields with Master Data/Texts Open ODS Views and InfoObjects in the Association area in the right panel. You thereby gain access to their metadata, authorization properties and Navigational Attributes (if you associate a characteristic field). Warning: if you do not associate fields with InfoObjects, there can be a performance penalty. This performance penalty is due to the field structure having to be interpreted at query runtime.
When you choose Master Data during the OODS screen, you can use this OODS as a dimension/master data when modeling your HANA CompositeProvider (HCPR). How? By associating your fields with a Master Data OODS when you’re modeling a Fact OODS or a HCPR.
The Representative Key Field is the field you use to link your Master Data OODS to a Fact OODS or HCPR. Therefore, the field you drag to the Representative Key Field folder needs to be present in the Fact OODS /HCPR you want to link to your Master Data OODS. By associating a field in a Fact OODS /HCPR with a Master Data OODS, you can use the rest of the Master Data OODS fields as Navigational Attributes. Just don’t forget to drag them to the Characteristics folder in the View Fields section while modeling.
In Figure 25 we can see that a Master Data Open ODS View contains more View Field types than a Facts Open ODS View. The additional fields are:
- Representative Key Field: this field is used to link the Master Data OODS to a Fact OODS or a CompositeProvider through Association. The Characteristics (Key) field in a Master Data OODS, is only used for additional compounded keys.
- Valid From / Valid To: in case you have time-dependent data, you can drag the valid from and valid to fields to this folder.
- Short / Medium / Long Text: drag any text fields one or more of these folders for text handling.
There is a 3rd type of Semantics: Texts (see Figure 26). You can already guess what it’s about: text tables (surprise, surprise). Master Data Open ODS Views contain all the field semantics a Text Open ODS Views have, except for one: Language. You might recognize Language as a standard field included in SAP Text tables.
You cannot use Text Open ODS Views in a CalcScenario (see Figure 27). If CalcScenario: no, you cannot create a join using this OODS in a HCPR. The only way to add a Text OODS View to a CompositeProvider is by Association, as I will explain next.
A Text OODS works very similarly to a Master Data OODS. The field you use as Representative Key Field needs to be present in the Fact OODS or HCPR you want to link to the Text OODS. By associating that field with the Text OODS in the Fact OODS/HCPR, you can use the text fields you defined in that Text OODS when executing a query. Don’t forget to drag the text fields to the Short/Medium/Long Text folders while modeling your Text OODS.
You can also combine a Text OODS and a Master Data OODS. You can combine them in the General tab of the OODS in the Semantics part (see Figure 22). To add a Text OODS to a Master Data OODS press “New” on the General Tab. After pressing “New” you can either add a Text OODS to a Master OODS or vice versa.
Pros & cons
Finally, here’s an overview of the pros and cons of the Open ODS View:
The Road to Virtuality
On the road to having a more virtual data warehouse, you can easily get lost. We’ve seen that Open ODS Views are very versatile. Therefore, to explore them properly you need to have patience and perhaps read a blog or two (wink, wink).
You now know all the different Source Types you can use in an Open ODS View. For instance, you now know how to transform data during runtime by basing your Open ODS View on a Transformation. Whether or not that’s sensible, I’ll leave up to you. We also created a Source System, to set up a connection between our Open ODS View and a local database table or an external virtual table. We also briefly discussed Association and the difference between the three Semantic Types, including when to use which one.
Armed with this knowledge you’re now almost ready for the Virtual BW world. There is one last stop on the road to a virtual BW: the HANA CompositeProvider. You can read Part II of this article here. Bon voyage!