Solution: DSP
Version: 6.6.0
Component: Common and Collect
WARNING: In DSP version 7.1, the Advanced View Builder in Collect stopped working. This was fixed in the 7.1.3 release.
1. Objective
This DSP feature is especially useful when you have to filter multiple tables by SAP Client or Language. This requirement is typically needed when we process a Target Table in Transform and DSP needs the underlying Lookup Tables to create the error reports.
Those Lookup Tables are configuration tables that are pulled from the Target System and are stored within Dg data bases. To be able to run Transform these tables need to exist in the corresponding DSW data bases. Instead of duplicating the tables, it is easier to just create views in DSW that refer to the tables in Dg. For instance, we start loading in Client 400 and a week later we need to do it in Client 600. Rather than manually (or via script) creating all the views (pointing to a configuration table in a data garage (Dg) database and filtering by a certain client), the views can be automatically generated via AVB.
2. Procedural Steps Involved
2.1 Build View
To use the advance build feature, first you need to check the Client and Language Column Name Variant. In here we specify to DSP what column names need to be used as variant of MAND and SPRAS.
Navigate to: Common > Tools > Build View.
For an SAP project, it is pretty straight forward and it is already set up (standard DSP), although you might need to add some Client and Language Column Name variants when needed, for instance, tables that do not use SPRAS, like TTXOT and TTXIT.
For Infor M3, it is a bit more complex. In every table, the field for company has a different name (OSCONO,BICONO,OGCONO etc). To address this issue, either add all the names, or create a new column in all tables with the same name (e.g., CONO) in the Dg data base when downloading the data from M3.
For instance, in the below image we can see that for the table OPRPLN the company name is listed twice, once in the real field name OCCONO, and again in CONO (the column we have automatically added).
To add different column names as variants of MANDT, click the Client icon and add as many as needed:
2.2 Use the Advanced View Builder
Once the previous configuration has been set up, we need to navigate to: Collect > Tools > Advanced View Builder in Navigation pane.
- Select a database where the views will be created from DATABASE FOR VIEWS list box. This database cannot be the same as database selected for DATABASE FOR TABLES.
- Select a database where the views read table data from DATABASE FOR TABLES list box. This database cannot be the same as database selected for DATABASE FOR VIEWS.
- Select a value from GROUP FOR VIEWS list box where only the active tables in the group will have views created.
NOTE: Leave the GROUP FOR VIEWS field blank to build a view for all tables in the DATABASE FOR TABLES field.
The next steps depend on whether the view should be built from the static Language and Client values, or should be built dynamically.
Build a Static View
- Enter a client in SAP CLIENT field to add the Client to the Where clause. NOTE: The SAP CLIENT value is only used when TRANSFORM PARAM TABLE is unchecked.
- Enter a language in LANGUAGE field add the Language to the Where clause. Use “E” for English. NOTE: The LANGUAGE value is only used when TRANSFORM PARAM TABLE is unchecked.
- Click Build View.
In the below example a view of the table dgInforM3_TST.dbo.CSYTAB, filtered by CONO=’401’ has been created in dswWAR_Test:
Build a View Dynamically
- Click TRANSFORMPARAM TABLE check box.
NOTE: If using SAP, the TRANSFORM PARAM TABLE must have the following columns: SAPCLIENT, SAPLANGUAGE and SAPINSTANCE. If these columns are not found, the table will be excluded from the Build View process. For Infor, even though the naming is not correct (SAPCLient vs CONO) it will still works (as we have configured the Client Column Variant).
Additionally, the view or table needs to be named as dswParam and exist in every DSW db.
By using the option TRANSFORM PARAM TABLE that allows to do not need to fix a value within the view but instead, join with view/table which can dynamically point to a different Client.
- Manually create the view or table dswParam. Within it, you build the logic that you need to retrieve the SAP Client value dynamically.
- Example: retrieve Client from Wave configuration in Console:
And ttWave data is coming from:
- Click Build View.
The result is the creation of views for all (as we did not specify any group) tables that we have in dgInforM3_TST under dswWAR_W1:
Example: Opening the view MPORDT, we can see that MPORDT has joined with dswParam.
The result of the view is:
In addition (thanks @Nigel), is important to notice that the view needs to exist to be recreated.
[Example] Let's say that in first instance we are creating the views from dgSAP_DEV (scenario 1) and the AVB creates three vies under dswCUS_W1.
If we later on move to quality and we use AVB to create new views pointing to dgSAP_QUA, if a view does not exist, it will not be recreated and therefore, it will still be pointing to Dev (T009 in the example)
Below a graphical example:
I hope you have found this post interesting.
Please do not hesitate to ask any question.
Comments
3 comments