This blog post is all about customer exit variables. In SAP Business Warehouse, including BW/4HANA and BW on HANA, every customer has several. Now, it is time to bring this concept into SAP Datasphere.
Familiarity with BW logic is assumed; it will not be explained in detail here.
Some customers have implemented customer exit variables with different classes and tables to map something. For more information, see my old post about the concepts of customer exit variables in BW/4HANA.
- BW/4HANA 2.0 Customer Exit Variables with own enhancement spot
- BW/4HANA: Customer Exit Variable with dynamic assignment
Now, back to SAP Datasphere. For a long time, Analytic Models has offered the possibility to consume variables as either Derived Variables or Dynamic Default.
When compared to BW, the derived variables are similar to I_STEP 2 when the variable is not input-ready. Dynamic default variables are like I_STEP 1, which occurs before the variable prompt pops up.
Sandy showed us in the Datasphere Topic Group how to achieve this. This is also documented in the SAP Community.
I used this approach and enriched it with more time-based logic.
- today
- yesterday
- last working day
- same month last year
- last year
and many more. I think this is a good starting point for the time filtering you normally need. The code is always pretty much the same and looks like this:
SELECT 'Yesterday' AS "Time_Description", ADD_DAYS(DATE_SQL, -1) as "Start_Date", ADD_DAYS(DATE_SQL, -1) as "Endt_Date", '' as "Date" FROM "SAP.TIME.VIEW_DIMENSION_DAY" WHERE "DATE_SQL" = CURRENT_DATE
I think everyone can adopt this. I won't go into detail for each time option.
In addition to time logic, you can also create customer exit variables for other dimensions.
Let me explain this approach in more detail. For example, I created a table for Company Code that has three columns: ID, User, and Value.
I also created a control table for all customer exit variables, including an ID and table name.
Then, I created a master view, or a class, depending on what you would call it. This object determines the corresponding table for the customer exit depending on the ID
DECLARE lt_result TABLE ("USER" NVARCHAR(100), "VALUE" NVARCHAR(100)); DECLARE lv_table_name NVARCHAR(100); -- Get Table for determinination EXEC 'Select "TABLE" from "TM"."0LT_CUSTOMER_EXITS" WHERE "ID" = ' || :I_VNAM into lv_table_name; -- Get values for the user EXEC 'SELECT "USER", "VALUE" FROM "' || :lv_table_name || '"' INTO lt_result; return SELECT * FROM :lt_result where "USER" = lower(session_context('APPLICATIONUSER'));
The lookup entity for the company code is a new view that calls the central view/class and returns the company codes that I want to use to the variable.
-- I_VNAM: ID of the entry in the Table 0LT_CUSTOMER_EXITS select CAST("VALUE" AS VARCHAR(4)) AS COMPANY_CODE from "1SV_CE_DETERMINE_VALUES"(I_VNAM: 1)
This approach allows me to be flexible by determining the corresponding table for the exit centrally and using separate logic for the lookup. I could also build another lookup with a join or other filtering options. The preview looks like this:
In an SAP Analytics Cloud story, the prompt looks like this:
This is an example of how to use derived and dynamic default variables in SAP Datasphere.
Conclusion
Let me know in the comments if this approach is something you're interested in or if you have a completely different approach.
author.
Subscribe
- In my newsletter, you get informed about new topics
- You learn how to use Analysis Office
- You get tips and tricks about SAP BI topics
- You get the first 3 chapters of my e-book Analysis Office - The Comprehensive Guide for free
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as an e-book.
Write a comment