Simplify your modeling with ADSO Model Templates
Why waste time figuring out which modeling property boxes to tick, when you can model a whole ADSO in one click? This is what model templates could do for you. Even though they’re not new in BW/4HANA (SAP introduced them in BW-on-HANA 7.4), it’s still worth having a look at them because most BW modelers are still used to the pre-Eclipse objects.
To spare you the agony of testing out every single model template, I’ve gone ahead and made an overview of what each template does. I’ll shortly discuss each template so you can avoid choosing the wrong template; and model with complete peace of mind.
Let’s start off by creating an ADSO. After filling in standard info such as name and description we arrive at the interesting stuff, Model Template, on the second screen:
ADSO Model Templates are found on the right of the Modeling Properties screen. There are 3 Model Template categories, as we can see in Figure 1:
- Enterprise data warehouse architecture
- Classic Objects
Let’s have a close look at each model template by looking at its system tables in figure 2:
All ADSOs have these system tables, but depending on the ADSO they might remain unused.
Enterprise data warehouse architecture
1. Data Acquisition layer (including corporate memory)
ADSOs modeled using the Data Acquisition Layer template do not use an Active Table, just like a Write-Optimized DSO. All the records in the Inbound Table contain a Request Transaction Number (TSN), Data packet, and Data record number. We can see this technical information in Figure 3. For those wondering, the Inbound Table is just another SAP term for the New Data / Activation Queue Table. You can use this model template as a persistent staging area (PSA) and in the 1st layer of the EDW architecture.
2. Corporate Memory – compression capabilities
According to SAP the Corporate Memory (CM) contains the complete history of the loaded data. To save memory space, the CM – compression ADSO doesn’t use a Change Log table, only an Inbound Table and an Active Data Table. As soon as a load request is activated, the system loads the data into the Active Table and deletes it from the Inbound Table.
Let me demonstrate how this template works with an example:
After I load my crime dataset with statistics about the mean Sacramento streets into my corporate memory – compression ADSO, I can still trace the data back to a specific load. I can do this using the RSN in the Inbound Table in Figure 4.
Figure 5 shows that the CM – compression Inbound Table is empty after activation. The data is now in the Active Table. In the Active Table in Figure 6 we can see that someone was busted for 28.5 grams of Marijuana on New Year’s. However, we can’t determine which load that record belongs to anymore after activation. This is because the activation process compresses the data by aggregating records based on their semantic key. It thereby discards the technical load data of the records. Furthermore, this type of ADSO has no change log to trace back the load. Let me demonstrate how this aggregation works:
First, I load 6 records into the ADSO and activate it. After loading the records the Active Table looks like Figure 7. Then I load a 2nd dataset with changed and new records:
If there are 2 records with the same key, BW/4HANA overwrites all the characteristics of the record with the characteristics of the lastly loaded record. For instance, RecordID 1088450 was a burglary in the first load in Figure 7, but is now a case of arson (even worse) after the 2nd load in Figure 8. The field has been overwritten by the last loaded characteristic value.
BW/4HANA either overwrites or sums the Key Figures, depending on what you select in the transformation rules (Figure 9). We see this in the cost to society fields. In the 1st field I selected Overwrite in the transformation rule. I selected Summation in the 2nd cost to society field transformation rule however, which is why the 2nd field is a lot larger. In the 2nd field, the system sums all the costs to society for records with duplicate keys, thereby making crime extremely expensive to society.
Reporting in the CM – Compression ADSO takes place on the Active Table. Extraction takes place on both its Active Table and its Inbound Table (see Figure 10), so take care if you only want to extract records from the Active Table.
To conclude, use this template if you want to save space, but don’t need to track load requests. For instance, because the data is so old that you don’t need to trace it back and can therefore afford to save space by activating it and not using a Change Log.
3. Corporate Memory – reporting capabilities
The only difference between this template and the CM compression template is that the option “Keep Inbound Data, Extract from Inbound Table” is turned on in this one. This means that when these kinds of ADSOs are activated, the sytem does not erase data from the Inbound Table. Instead, the data also remains in the Inbound Table so that none of the technical information is lost. Thus, you can still trace back records to their specific loads and reload data into the Active Table after deleting data there. The CM reporting template has no Change Log though.
Let me show the difference with the CM compression template with an example. After loading our Sacramento crime data, we activate it in Figure 11 and the data is moved to the Active Table in Figure 12:
An important difference with the CM compression template is that the system does NOT erase the crime data from the Inbound Table when activating. As a result crime data still lingers around in the Inbound Table after activation. We see this in Figure 13:
Another difference is that the data is not extracted from the Active Table but from the Inbound Table, which can be seen from the Extraction view in Figure 14 (reporting is still done on the Active Table however):
Because the data remains in the Inbound Table after activation, these ADSOs are a good solution for when you want to store data but save space by not using a Change Log, but need to be able to track back records to their specific load. Side note: storing the data both in the Inbound and Active Table takes up twice as much space as with the compression CM model template. If you’re pressed for space, you might therefore be better off using the CM – Compression model template!
4. Data warehouse layer – delta calculation
This layer is modeled like a Standard DSO. That means that these types of ADSOs have a Change Log table for delta extractions, an Inbound Table and an Active Data Table that is used for reporting and full loads. Here the Inbound Table is empty after activation as we see in Figure 15. In the Change Log table we can find the new, before- and after status of records in column R (see Figure 16).
Because of this ADSO’s delta capabilities, it can function as a central data object from which only new and changed records are loaded to the next layer.
5. Data warehouse layer – data mart
The data mart version of the data warehouse layer template makes the ADSO behave just like an InfoCube. Like a Cube, it does not have a Change Log. The Inbound Table (see Figure 17) functions as a Cube’s “F-table” and the Active Data Table (see Figure 18) functions as the “E-table” for compressed data (similar to the InfoCube function Collapse). After activation, the Inbound Table is empty (see Figure 19)
The system “unions” both the Inbound Table and Active Table together for reporting (see Figure 20) and extraction (see Figure 21).
Thanks to HANA’s in-memory capabilities however, we no longer need dimension tables. BW now writes Master data identifiers (SIDs) directly to the Fact table. This makes InfoCubes more or less obsolete. You still need to model ADSOs like an InfoCube if you use non-cumulative Key Figures however, but to achieve that you would also need to manually tick the “Inventory” property box. If you do not tick this box, you’ll get an error if you try to add a non-cumulative Key Figure to your ADSO (see Figure 22).
The option “All characteristics are Key, Reporting on Union of Inbound and Active Table” is a prerequisite to use non-cumulative Key Figures at the moment, so this kind of ADSO model template is well suited for Inventory-data or any other non-cumulative scenario. If you use this template don’t forget to also tick the “Inventory”-box however.
1. Planning on Cube-like
I know, the name sounds a bit silly. This model template sounds as if your teenage daughter that uses the word “like” too much had a conversation with SAP about modeling. Ignoring the adolescent vibe of its name, let’s discuss its properties:
This ADSO is modeled the same as the Data warehouse layer – Data Mart ADSO. It behaves like a Cube, so all the characteristics are key fields just like in the DWL – data mart ADSO. Planning on Cube-like ADSOs have an Inbound Table (see Figure 23) and an Active Table. All characteristic fields are marked as key fields in the Active Table (see Figure 24), which is a necessary requirement to make it suitable for planning.
Because the Planning on Cube-like model template marks all characteristic fields as key, it does not overwrite any records based on a semantic key like the Corporate Memory ADSOs. Records with the value 1084424 are duplicate records (see Figure 25), because I changed the offense category characteristic. The records that have identical characteristics to the records in the previous load have their key figures summed (all the records that do not have an “N” in the “R” field). This is to be expected, because Summation is the only rule type available for key figure transformation rules going to Planning on Cube-like ADSOs (see Figure 26).
The system unions both the Inbound Table and Active Table together for reporting (ee Figure 27) and extraction (see Figure 28), just like it does with a Data Mart ADSO:
You will be able to write data to this ADSO using planning applications in the future, thereby making it suitable for planning. Planning applications aren’t available now, but are scheduled to be available by the end of 2017 according to SAP.
2. Planning on Direct Update
You can load records directly into the Active table of these kinds of ADSOs. It has no Change Log or Inbound Table. You can fill the Active table with an API. You can also load data to this type of ADSO using a DTP. This is a slight change from the classic Direct Update DSOs in BW on HANA, which could you could only load via an API.
There is no activation option as we can see in Figure 29, because this type of ADSO only has an Active Table. Data is automatically loaded to this Active Table in Figure 30 when executing a DTP:
As we can see in Figure 31 these kinds of ADSOs only have an Overwrite option There is no summation of key figures like there is in the Planning on Cube-like ADSO. To test how this ADSO behaves I loaded a second dataset to this ADSO with new and changed records:
In Figure 32 we can see that the Planning on Direct Update ADSO does not store any duplicate records. If it comes across a duplicate value in the semantic key, such as Record 1088450 in the image above, it simply stores the lastly loaded record. BW/4HANA stores the lastly loaded record regardless of whether the characteristics or the key figures are changed.
These types of ADSOs can be used to write data to 3rd party tools.
This sub-category does not require any detailed explanation, because they are exactly the same as some of the EDW – Architecture Layer ADSOs. However, it is possible to create an ADSO with the following templates:
1. Standard DataStore Object
This model template is the same as the Data Warehouse Layer – Delta Calculation template.
2. Write-optimized DataStore Object
This template is the same as the Data Acquisition Layer (including Corporate Memory) template.
Finally, this model template is the same as the Data Warehouse Layer – Data Mart template.
For those that want a quick overview, I’ve made a table showing which modeling properties each template has:
To summarize, there’s no need to reinvent the wheel when it comes to modeling in BW/4HANA. I’ve discussed all the different model templates you can use to save time and to simplify the modeling process. We also saw that a lot of the EDW Architecture and Classic Object model templates are the same. Armed with this knowledge you at least won’t have to spend half an hour looking at the screen wondering whether there is any difference between a Standard DSO and a DWL (delta calculation) model template: there is none. Now we know how to use model templates for ADSO. In my next tutorial we’ll look at what other BW/4HANA object types can offer us, namely the Open ODS View and the HANA CompositeProvider.
Thank you for checking out my tutorial! Hope you found some value in it. Please feel free to leave any comments or contact me.