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.
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 ‘ ‘ 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 third 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 :
I have simply selected some of the fields as output & then created an input parameter called IP_CS_FLAG (Cold Store Flag) as optional single input of type VARCHAR & ength 1 (similar to column COLD_STORE_FLAG). This is to pushdown the filter with flag value passed from application layer on access of the data tiering storage into the lowest level by using a filter on the lowest Aggegation node(Aggregation_1).
The Default Value is set to blank, securing the fact that if no input is fed from user from application layer or while querying the calculation view, it would only request from Hot data storage avoiding unnecessary access to the external cold store.
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 botom most node ‘Aggregation_1’ using a filter on column COLD_STORE_FLAG.
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 COLD_STORE_FLAG to show all values using a match function with ‘*’ , other values than ‘X’, which is ‘ ‘ should only match with ‘ ‘ values for the flag, ensuring only Hot store data is filtered & selected for output.
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. 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.
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.
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 ‘ ‘ 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 step 2 exit variable which would derive the flag as both ‘ ‘ & ‘X’ upon passing the variable input as ‘X’ as fed in the selection prompt.
- 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 third 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 :
I have simply selected some of the fields as output & then created an input parameter called IP_CS_FLAG (Cold Store Flag) as optional single input of type VARCHAR & ength 1 (similar to column COLD_STORE_FLAG). This is to pushdown the filter with flag value passed from application layer on access of the data tiering storage into the lowest level by using a filter on the lowest Aggegation node(Aggregation_1).
The Default Value is set to blank, securing the fact that if no input is fed from user from application layer or while querying the calculation view, it would only request from Hot data storage avoiding unnecessary access to the external cold store.
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 botom most node ‘Aggregation_1’ using a filter on column COLD_STORE_FLAG.
if('$$IP_CS_FLAG$$' = 'X',match("COLD_STORE_FLAG",'*'),match("COLD_STORE_FLAG",'$$IP_CS_FLAG$$'))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 COLD_STORE_FLAG to show all values using a match function with ‘*’ , other values than ‘X’, which is ‘ ‘ should only match with ‘ ‘ values for the flag, ensuring only Hot store data is filtered & selected for output.
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. 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.
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...