Greetings !
As promised in my earlier blog post, in this article I will be explaining the possibilities of using the view for external access or the external SAP HANA SQL view for aDSOs or the generated ‘8’ view of aDSOs. For details about the ‘8’ view please have a look into the above mentioned blog post link.
For this illustration, I will use the same aDSO used for the earlier blog post : SALESADSO, which has some sample sales data for calendar year 2019 & 2020. Executing suitable DTO rule, I have moved all the data residing in partitions covering calendar year 2019 to external cold storage (IQ). So, the view for external access for the aDSO : /BIC/ASALESADSO8, has the COLD_STORE_FLAG column populated as : ‘X’ for all data belonging to year 2019 & ‘ ‘ for all data belonging to year 2020.
Now let’s think about consuming this view into a BW data model using mixed modelling architecture in such a way that unless a request has been made to cold store data, it should not access the cold store causing unnecessary performance penalty.
Meaning, we will build a calculation view based on the ‘8’ view & then consume the calculation view into a composite provider & finally a report on top of the composite provider. The purpose is to explore how can we determine the access to cold store data exposing the flag as a consumer of single value input variable in the application layer of reporting.
Now the challenge is that if we simply expose the field COLD_STORE_FLAG as a variable in the report through the HCPR, user can select either ‘ ‘ (blank or empty string, no whitespace) or ‘X’ if we build a single value input variable in frontend, which is the goal here, we could potentially have a multi input variable which can request both the data. But I will try to explain how to build a solution using single value input variable around this flag. The challenge with this approach is if a user, for example, requests data from December 2019 to February 2020 in report, & selects based on the flag values available with just the field exposed as it is, user can see either 2019 year’s data (if user selects COLD_STORE_FLAG = ‘X’) or 2020 year’s data (if selected ‘ ‘). But with selection of ‘X’ we need to secure that the user is able to access both 2019 & 2020 years’ data. This situation can be handled in multiple ways :
I will explain the second approach in this blog post, which is handling of the flag completely in calculation view level.
First step is to consume the view in a calculation view in below way:
I have made 2 nodes out of the view called HDB & HDB_IQ. In HDB I have placed a filter on COLD_STORE_FLAG = ”. So, it contains only data from the hot data store (Active Data Table of aDSO), while the other node has no filter, containing all the data combining hot & cold storage.
In the node Union_1, I have created a new target column : CS_FLAG, with custom constant mapping of the node values as ‘ ‘ (HDB) and ‘X’ (HDB_IQ). This way the pruning is secured.
I have created an input parameter called IP_CS_FLAG (Access Cold Store) as optional single input of type VARCHAR & length 1. This is to pushdown the filter with flag value passed from application layer on access of the data tiering storage by using a filter expression.
If no value is passed through it, the takes the unassigned value, thus only requesting the hot store data avoiding the extra traffic to access the external cold storage.
Coming to the fact that the COLD_STORE_FLAG can have 2 possible values with enabling Cold Storage & movement of some data into it ie. ‘X’ for cold store data & ‘ ‘ for hot store data.
Now in our reports, we want to have a universal variable which enables users to select if they want to access data from cold storage(‘X) – this may lead to some performance penalty or just the recent data residing in Hot storage of HANA nodes(‘ ‘). For securing no data loss while selecting the variable as ‘X’, we need to access both HANA & IQ (Cold store) data when user selects the variable value as ‘X’, to handle cases like period which overlaps between hot & cold storage.
The above scenario is handled using the filter in node ‘Aggregation_2’ using a filter on the created target column : CS_FLAG & restricting it on the input parameter value.
The filter expression basically reads the input parameter value passed, if it comes with a value ‘X’ (user requests data from both Hot & Cold store), we set the CS_FLAG to go through the HDB_IQ node as per the definition of the field pruning & both 2019 & 2020 years’ data are requested, if the input parameter is not fed or filled with blank value ie. ‘ ‘, then according to the constant mapping it should only look through the HDB node with ‘ ‘ values for the flag, ensuring only Hot store data is selected & no colt store table is accessed causing unnecessary performance penalty.
Now, let’s just make a select query on the calculation view without passing any input parameter value :
We can see it only picks the data relevant for year 2020 where the COLD_STORE_FLAG is blank. A planviz shows that the executed plan hits only the Active data table residing in hot store.
Now, let’s execute a select query on the same again but this time with the input parameter value filled in as ‘X’.
And this time it shows all the data irrespective of the flag value both from hot & cold store for year 2019 & 2020. The planviz shows it hits both cold store table & active data table this time.
Our job now is to consume this calculation view along in a composite provider & then build a report with the variable based on the input parameter. Let’s create an masterdata info object for holding the input parameter possible values for browse help. I created one called CS_FLAG with type CHAR length 1, the master data table of which looks like below :
So, on browse help in report, it will only show the values as unassigned & ‘X’ to choose from.
As next step, the HCPR : CP_SALES is created & this info object is assigned to the input parameter in HCPR output tab.
The next step is to create an input ready single value variable on this info object CS_FLAG to restrict the input parameter in the report using the same.
Now simply create a report with meaningful rows & columns dragged into. I have created a sample report : ZQ_SALES_TEST as below just to show the behaviour of the report data controlled by the flag with a single variable in selection prompt screen which was built on CS_FLAG.
Let’s now have a look at the selection prompt screen of the report :
So, as mentioned earlier, I have just included the data tiering controller flag variable to showcase the output variance. As expected from the masterdata entries, the value help shows only two possible values to choose from.
Now let’s first execute the report without having any value passed to this variable, since it is optional.
We can see only 2020 data is shown back, which is in the hot storage.
Now let’s do it again with the variable value passed as ‘X’.
And this time we get data from both hot & cold storage ie. both 2019 & 2020 year’s data.
Hope this blog will help gaining some insights about the generated ‘8’ view for aDSOs being consumed in mixed modelling scenario from BW/4 reporting perspective & securing correct control of data across different data temperature tiering systems with optimum performance in mind.
You are welcome to share any comment/feedback/experience regarding this topic for enriching our mutual knowledge bank
Cheers!
Abhi
Okumaya devam et...
As promised in my earlier blog post, in this article I will be explaining the possibilities of using the view for external access or the external SAP HANA SQL view for aDSOs or the generated ‘8’ view of aDSOs. For details about the ‘8’ view please have a look into the above mentioned blog post link.
For this illustration, I will use the same aDSO used for the earlier blog post : SALESADSO, which has some sample sales data for calendar year 2019 & 2020. Executing suitable DTO rule, I have moved all the data residing in partitions covering calendar year 2019 to external cold storage (IQ). So, the view for external access for the aDSO : /BIC/ASALESADSO8, has the COLD_STORE_FLAG column populated as : ‘X’ for all data belonging to year 2019 & ‘ ‘ for all data belonging to year 2020.
Now let’s think about consuming this view into a BW data model using mixed modelling architecture in such a way that unless a request has been made to cold store data, it should not access the cold store causing unnecessary performance penalty.
Meaning, we will build a calculation view based on the ‘8’ view & then consume the calculation view into a composite provider & finally a report on top of the composite provider. The purpose is to explore how can we determine the access to cold store data exposing the flag as a consumer of single value input variable in the application layer of reporting.
Now the challenge is that if we simply expose the field COLD_STORE_FLAG as a variable in the report through the HCPR, user can select either ‘ ‘ (blank or empty string, no whitespace) or ‘X’ if we build a single value input variable in frontend, which is the goal here, we could potentially have a multi input variable which can request both the data. But I will try to explain how to build a solution using single value input variable around this flag. The challenge with this approach is if a user, for example, requests data from December 2019 to February 2020 in report, & selects based on the flag values available with just the field exposed as it is, user can see either 2019 year’s data (if user selects COLD_STORE_FLAG = ‘X’) or 2020 year’s data (if selected ‘ ‘). But with selection of ‘X’ we need to secure that the user is able to access both 2019 & 2020 years’ data. This situation can be handled in multiple ways :
- By using multiple input variable where user needs to select both the values for COLD_STORE_FLAG in such situations.
- By handling it using an input parameter & necessary filter criteria in calculation view.
I will explain the second approach in this blog post, which is handling of the flag completely in calculation view level.
First step is to consume the view in a calculation view in below way:
I have made 2 nodes out of the view called HDB & HDB_IQ. In HDB I have placed a filter on COLD_STORE_FLAG = ”. So, it contains only data from the hot data store (Active Data Table of aDSO), while the other node has no filter, containing all the data combining hot & cold storage.
In the node Union_1, I have created a new target column : CS_FLAG, with custom constant mapping of the node values as ‘ ‘ (HDB) and ‘X’ (HDB_IQ). This way the pruning is secured.
I have created an input parameter called IP_CS_FLAG (Access Cold Store) as optional single input of type VARCHAR & length 1. This is to pushdown the filter with flag value passed from application layer on access of the data tiering storage by using a filter expression.
If no value is passed through it, the takes the unassigned value, thus only requesting the hot store data avoiding the extra traffic to access the external cold storage.
Coming to the fact that the COLD_STORE_FLAG can have 2 possible values with enabling Cold Storage & movement of some data into it ie. ‘X’ for cold store data & ‘ ‘ for hot store data.
Now in our reports, we want to have a universal variable which enables users to select if they want to access data from cold storage(‘X) – this may lead to some performance penalty or just the recent data residing in Hot storage of HANA nodes(‘ ‘). For securing no data loss while selecting the variable as ‘X’, we need to access both HANA & IQ (Cold store) data when user selects the variable value as ‘X’, to handle cases like period which overlaps between hot & cold storage.
The above scenario is handled using the filter in node ‘Aggregation_2’ using a filter on the created target column : CS_FLAG & restricting it on the input parameter value.
The filter expression basically reads the input parameter value passed, if it comes with a value ‘X’ (user requests data from both Hot & Cold store), we set the CS_FLAG to go through the HDB_IQ node as per the definition of the field pruning & both 2019 & 2020 years’ data are requested, if the input parameter is not fed or filled with blank value ie. ‘ ‘, then according to the constant mapping it should only look through the HDB node with ‘ ‘ values for the flag, ensuring only Hot store data is selected & no colt store table is accessed causing unnecessary performance penalty.
Now, let’s just make a select query on the calculation view without passing any input parameter value :
We can see it only picks the data relevant for year 2020 where the COLD_STORE_FLAG is blank. A planviz shows that the executed plan hits only the Active data table residing in hot store.
Now, let’s execute a select query on the same again but this time with the input parameter value filled in as ‘X’.
And this time it shows all the data irrespective of the flag value both from hot & cold store for year 2019 & 2020. The planviz shows it hits both cold store table & active data table this time.
Our job now is to consume this calculation view along in a composite provider & then build a report with the variable based on the input parameter. Let’s create an masterdata info object for holding the input parameter possible values for browse help. I created one called CS_FLAG with type CHAR length 1, the master data table of which looks like below :
So, on browse help in report, it will only show the values as unassigned & ‘X’ to choose from.
As next step, the HCPR : CP_SALES is created & this info object is assigned to the input parameter in HCPR output tab.
The next step is to create an input ready single value variable on this info object CS_FLAG to restrict the input parameter in the report using the same.
Now simply create a report with meaningful rows & columns dragged into. I have created a sample report : ZQ_SALES_TEST as below just to show the behaviour of the report data controlled by the flag with a single variable in selection prompt screen which was built on CS_FLAG.
Let’s now have a look at the selection prompt screen of the report :
So, as mentioned earlier, I have just included the data tiering controller flag variable to showcase the output variance. As expected from the masterdata entries, the value help shows only two possible values to choose from.
Now let’s first execute the report without having any value passed to this variable, since it is optional.
We can see only 2020 data is shown back, which is in the hot storage.
Now let’s do it again with the variable value passed as ‘X’.
And this time we get data from both hot & cold storage ie. both 2019 & 2020 year’s data.
Hope this blog will help gaining some insights about the generated ‘8’ view for aDSOs being consumed in mixed modelling scenario from BW/4 reporting perspective & securing correct control of data across different data temperature tiering systems with optimum performance in mind.
You are welcome to share any comment/feedback/experience regarding this topic for enriching our mutual knowledge bank
Cheers!
Abhi
Okumaya devam et...