Advanced features of attribute views
In the previous section we created a simple attribute view, using only certain fields from one base table. In this section we will take a look at more advanced features of attribute views.
The client field
If you can still see the data preview screen of the attribute view, and you switch to the Grid tab of this panel, you will see that the CUSTID number 1 is present twice. This is a problem, especially since this customer is apparently from both the UK and France.
If you recall when we described the data in the previous chapter, we saw the notion of client and the fact that there could be two instances of SAP's ERP software running on the same database at the same time, using two different Client IDs. In our example tables, there is data from both the client 100 and the client 200, and this is why the CUSTID appears twice.
Note
It is very important here to note that even though the CUSTID is the same, the customer 1 from client 100 is not the same customer as number 1 from client 200.
Now, we can see the tight coupling that exists between SAP HANA and the SAP ERP software—in the Studio we can indicate to HANA with which client we want to work. For example, we might have a SAP ERP user Tom who works in client 100, and the ERP user Sue might work exclusively in client 200. We can specify in HANA that Tom's user has a default client of 100 and Sue's, 200. We'll see how to create these default values later, in the chapter concerning authorizations.
In the meantime, we can set the default client at the view level. Setting the default client value for a view is simple, just open the view (for example the attribute view we have just created), and click on the Semantics block.
As we saw a couple of pages back, the Semantics panel has an option called Default Client, which by default takes the value Dynamic. The different values for the Default Client and their meanings are:
- Dynamic: Take the default value from the user record, or do no filtering on the client if no value is present.
- Cross Client: Do no filtering on the client value—values from all clients will be shown in the view.
- User-defined: If you want to force the view to filter on a particular view for the client, you can do this by typing your own value directly into the drop-down list on the Semantics screen. In the following screenshot, the view filters directly on the client value of 100:
If you filter your attribute view on the Default Client of 100 now (and then reactivate!), do a data preview (or refresh the data preview if it is still open), you will see that CUSTID number 1 is now present only once in the view.
Tip
If you want to check that the filtering is done correctly, you might like to filter the data on client 200, as a test. In client 100, CUSTID 1 is from sector SMB in Great Britain. In Client 200, however, CUSTID 1 is in sector IND and is from France.
If you do decide to do this test, then please change the view's filter back to 100 at the end, or the following examples won't work correctly!
Joining tables in views
Now that we have an attribute view which extracts the correct data we want from the base table CUSTOMER, we can make our view more interesting.
In order to do this, we can take the information from the CUSTOMER table, and combine it, or join it, with information from other tables, to provide what is basically a new table, containing information from different sources.
Let's look at the different table joins we can make, and add the different information to our view.
Join types in SAP HANA
Readers familiar with SQL and relational databases in general will already know the points in this section, but we'll just take a couple of minutes to go over the basics.
The examples in this section all refer to joining data between two of our sample tables, CUSTOMER and CUSTNAME, as pictured in the following screenshot:
The CUSTOMER table is on the left-hand side, and the CUSTNAME table is on the right-hand side (the placement of the tables is important, as we'll see in a moment).
In order to join data in one database table to data in another database table in SAP HANA, several types of join are possible. They are as follows:
- Inner Join: This type of join indicates to SAP HANA that the value in the table on the left-hand side will be in the table on the right-hand side. For example, we can be fairly certain that each
CUSTIDwill have aNAME, so we can do an inner join between the tablesCUSTOMERandCUSTNAME. If there is no value in theCUSTNAMEtable for a value in theCUSTOMERtable, and the two tables are joined using an inner join, then no value will be returned—from either of the tables. This can lead to missing data, so be careful when you use an inner join. - Referential Join: This is the same as an inner join, though it assumes that referential integrity is ensured in the database. If values from only the left table are requested, then the system will perform a left outer join (which is fast). If fields from both tables are requested, then the system will perform an inner join.
- Left Outer Join: This type of join is used when you are not sure of the existence of values in the right-hand table. In our example, we are certain that in the
CUSTOMERtable we have all theCUSTIDvalues. If we are not sure that they all have aNAMEassociated in theCUSTNAMEtable, we can join the two using a left outer join. In this case, we are certain that all theCUSTIDvalues would be present when we ask for the contents of our view. If aCUSTIDdoes not have an associatedNAME, then theNAMEcolumn will be empty for thatCUSTID. - Right Outer Join: The same as a left outer join, but we are certain that all values from the right-hand table will be present in the view contents, whether or not there is a corresponding value in the left-hand table. In our example, we could be sure to see all
NAMEvalues from theCUSTNAMEtable, and the corresponding entry in theCUSTIDcolumn might be empty. This might be useful, for example, if we need a list of customers who don't have a code. - Text Join: A text join is a special join type provided by SAP HANA. It allows a left outer join between a table of master data (our
COUNTRYtable, for example) and a table containing language-dependent texts for that master data (ourCTRYNAMEtable). The join is done on one field (COUNTRY) and we indicate to SAP HANA which field in the right-hand table contains the language key.
Join cardinality
In addition to the join type between two tables, you will need to indicate the cardinality of the join:
- 1..1: This indicates that for each entry in the left-hand table, there is one entry in the right-hand table, and vice-versa.
- 1..n: This option tells SAP HANA that we know that for one entry in the left-hand table, there are 0 or more entries in the right-hand table. For example, if we have a table containing sales orders, and a second table containing sales order lines, then for one entry in the sales order table, there are 0 or more sales order lines
- n..1: Basically the same as the 1..n cardinality, but the other way round. We can imagine a
CUSTOMERtable with severalCUSTIDfields, each of which has aNAME—if several of our customers are called Smith, we might have only one entry Smith in theCUSTNAMEtable, and severalCUSTIDvalues pointing to it. - n..n: This cardinality indicates that there are many entries in the left-hand table which link to many entries in the right-hand table. We don't have an example of this join type in our sample data, however we can imagine the join between a
CUSTOMERand aPRODUCTtable, where severalCUSTIDvalues can use a particularPRODUCTID, and any givenCUSTIDcan use severalPRODUCTIDvalues.
Completing an attribute view with information from different tables
At the moment, the attribute view will show us the customer ID, the Sector ID and the country code for our customer. In other tables we imported, we have the customer's name, the sector name and the country name.
It would be more useful for our users if, when we ask for the contents of the attribute view, it automatically gets the text values for the different IDs in the base table:
CUSTIDlinks toCUSTIDin theCUSTNAMEtable, to find the customer'sNAME.SECTORlinks toSECTORin theSECTORtable, to find theSECTORNAME– which is language-dependent.REGIONlinks toREGIONin theCOUNTRYtable. This allows us to find the Country of the Region. In addition:REGIONin theCOUNTRYtable links toREGIONin theREGNAMEtable, to find theREGTEXTvalue, which is language-dependent.COUNTRYin theCOUNTRYtable links toCOUNTRYin theCTRYNAMEtable, to find theCTRYNAMEvalue, which is language-dependent.
If our attribute view could undo this spaghetti for us automatically, it would make our developments much easier.
Let's add the first field, the customer name, to the view.
From the Navigator panel, find and then open the attribute view we just created if it's not open already. In order to add the customer's name to the view, we first need to add the CUSTNAME table, in the same way we added the CUSTOMER table, by drag-and-drop.
Once this is done, click on the Data Foundation block, and your view will look like the following screenshot:
We can now create the join between our two tables. The join is made on the CUSTID field, which is in both tables. Make the join by clicking on the CUSTID field in the left-hand table (CUSTOMER) and dragging the mouse to the CUSTID field in the right-hand table (CUSTNAME). This will draw an arrow between the two fields.
Note
There is no need to join the tables on the MANDT field. This field represents the default client, and is handled automatically by SAP HANA.
Now that the join has been made, we need to indicate the join type and cardinality.
One CUSTID in the CUSTOMER table represents one CUSTID in the CUSTNAME table (because each customer has a name), so the Join Type can be Inner Join or Referential. Also, since a customer has only one name, and one name refers to only one customer, the Cardinality is 1..1. To set these properties, click on the join between the two tables, and select the correct values in the properties panel which appears, as we can see in the following screenshot:
At this point, the view will use both tables, and will calculate the correct join between them, but will not show the customer name, because we haven't added it as an output field yet, so please do that now, by adding the NAME field to the view output.
After reactivating the view, and requesting a data preview, we can see that the customer's name is now visible in the output:
Congratulations! We now have an attribute view based on the CUSTOMER table, which will pull information from the CUSTNAME table whenever needed. This has greatly improved the usefulness of our view.
We can now add more information to the view, starting with the sector name. Each customer has been assigned to a sector; these represent the rough company size. In the CUSTOMER table, we have the SECTOR ID field, and the text for the sector is present in the SECTOR table.
The SECTOR table has a description for each SECTOR ID, and these descriptions (the SECTORNAME field) have been translated into both English and French. This means that we'll need to create a text join.
Add the SECTOR table to the view, and create a join between the SECTOR fields in the CUSTOMER and SECTOR tables now, then change the type of the join to Text Join. We now need to indicate which column in the SECTOR table contains the language, so that SAP HANA can give us the description in the language we need. The language column is called LANG.
Note
The selection of the correct language is done using the preferences of the connection to SAP HANA. Right now we're using the Studio, so the language that SAP HANA will use is defined by the Studio. In the Navigator panel, right-click on the system name, select Properties, then in the Advanced Properties of the Database User Logon section of the dialog that will appear, select the language you wish to use with SAP HANA.
In our example data, only texts for English and French are available, so according to your preferences, select one of the English or French variants in the Locale list then click OK to validate your choice.
If we now add the SECTORNAME to the output of the view, reactivate our attribute view, and request a data preview, we can see that alongside the SECTOR ID, we have the description for the SECTOR, in the language we have selected.
First the current state of our view is as follows:
In the previous screenshot, the Properties panel refers to the text join between the two SECTOR fields (the join on the left hand side of the screen).
Now the result of this view, if our SAP HANA user connects in English, then French (to obtain the Properties screen, right-click on the system name in the Navigator panel, and select Properties from the menu. To see the language options, click on Database User Logon in the left-hand side panel, then Additional Properties on the right-hand side of the screen):
The language selection of the correct text to display was done by SAP HANA automatically at runtime—no changes were made to the view to see the alternative language.
Note
One point to note is what happens if no description is present in the requested language. Here is the output of the view if we connect to SAP HANA in German:
Note
No description is available in German, so no SECTORNAME can be provided by SAP HANA.
Additionally, the language key must be provided in the base tables as a single letter; in our examples, E for English, F for French, and so on. Longer language specifications (such as EN or FR, or even EN_US, or FR_CA) will not work.
Now that we have looked at simple joins and text joins, we can add the final fields to our view—the country and region descriptions for our customers. We'll need to add the COUNTRY, CTRYNAME, and REGNAME tables to our view.
First we'll create a join between the CUSTOMER and COUNTRY tables on the COUNTRY field. This is an inner join, with a 1..n cardinality.
In order to see the country and region names in our view, we can create text joins between COUNTRY and CTRYNAME (on field COUNTRY), and COUNTRY and REGNAME (on field REGION). Once again, the join types are text join, and the language column is called LANG.
We can now add the following fields to the output of our view:
REGIONfrom theCOUNTRYtableREGTEXTfrom theREGNAMEtableCTRYNAMEfrom theCTRYNAMEtable.
Our view should now look like the following screenshot:
A data preview of the view contents will now give us the information on each customer, as well as the sector name, country name, and region, as we can see in the following screenshot:
At this point, our view is complete. When we ask SAP HANA for information about our customers using this view, the system will pull all related information from the different tables, and present it to us in a coherent and predictable way.
By creating attribute views in this fashion, we are creating reusable building blocks which we can incorporate in further developments. These building blocks allow us to ensure that whenever we use a particular CUSTID in a development, all the information we need about this customer is available.
Additionally, if we always use this attribute view instead of adding the base tables, we can ensure that all our developments are coherent.
As an added plus, maintenance is largely reduced—if we make any changes to the base tables, or need additional information about the customer, we need only change our attribute view, and the fields in the view will automatically be available in all further developments which use this view.