Artificial Intelligence
Please fill the required field.

create a graphical view in sap datasphere for the below sql script /********* Begin Procedure Script ************/ BEGIN var_out = SELECT SKU.COMP_CD, SKU.COMP_CD_CNTRY, SKU.SLS_ORG_CD, SKU.SLS_ORG_DESC, SKU.SITE_CD, SKU.YEAR, SKU.YR_MNTH_S

Creating a graphical view in SAP Datasphere based on the provided SQL script involves several steps. This process includes defining the data model by using the data articles or views in SAP Datasphere and configuring them to resemble the output of your SQL script. Here are the steps to accomplish this: ### Steps to Create a Graphical View in SAP Datasphere 1. **Log into SAP Datasphere**: - Navigate to your SAP Datasphere account and log in. 2. **Create a New Model**: - From the SAP Datasphere main screen, go to the "Models" section. - Click on "Create", and choose "Graphical View" or "New Calculation View". 3. **Data Source Selection**: - In the graphical model editor, you need to add the appropriate source tables/views that are mentioned in your SQL query. - Add the following data sources: - **"_SYS_BIC"."ALSEER/AN_PO_RECEIPTS_DTL"** - **"_SYS_BIC"."ALSEER/AT_AGENCY"** - **"_SYS_BIC"."ALSEER/CV_ART_MVG_AVG_PRICE"** 4. **Join the Tables**: - Drag and drop the data sources into the model workspace. - Create the necessary joins: - Join **AN_PO_RECEIPTS_DTL** (alias `A`) with **AT_AGENCY** (alias `B`) on `A.SALES_ORG = B.AGENCY_SALES_ORG_CODE` and `A.ARTI_AGNCY_CD = B.AGENCY_CD`. - Join **AN_PO_RECEIPTS_DTL** (alias `A`) with **CV_ART_MVG_AVG_PRICE** (alias `C`) on `A.SALES_ORG = C.SLS_ORG_CD` and `A.ARTI_CD = C.ARTI_CD`. 5. **Select and Transform Fields**: - In the output pane, select the necessary fields based on your SQL query. You will add fields such as: - `A.COMP_CD`, `A.COMP_CD_CNTRY`, `A.SALES_ORG`, and others as per your SQL. - Use the expression editor to create calculated fields for aggregations. For instance, create measures for: - **SUM(A.PO_NET_VAL_AED) AS PO_RCPT_NET_VAL** - **SUM(A.PO_RCPT_QTY)** and others using the aggregation functions within the model. 6. **Define Conditions and Grouping**: - Implement the `WHERE` clause filters into the model such as: - `YEAR_MNTH_SAP = to_char(current_date,'YYYYMM')` - `ARTI_AGNCY_CD IS NOT NULL` - Translate the `GROUP BY` clause by adding those dimensions in the output section. 7. **Apply Aggregations**: - Use the "Aggregation" settings in your graphical model settings to group by the various fields. 8. **Filter and Add Conditions**: - In the properties or settings of your graphical model, add an additional filter for `HAVING SUM(A.PO_RCPT_QTY) > 0`. 9. **Validate the Model**: - Validate to ensure no errors are present and all joins and selections are correct. 10. **Save and Publish**: - After confirming everything is set up correctly, save the model, then publish it to make it available for analysis. 11. **Visualize the Data**: - After publishing, create a story or a dashboard within SAP Analytics Cloud or Datasphere using the new graphical view as your data source for visualization. ### Conclusion You have successfully created a graphical view in SAP Datasphere based on your SQL script. Following the above steps will allow users to analyze the aggregated data visually, leveraging the graphical capabilities of SAP Datasphere. If you encounter specific technical issues, ensure to check your permissions and the structures of the underlying data sources. Update (2024-10-11): Creating a graphical view in SAP Datasphere requires translating the SQL script into a graphical representation through data modeling. Here’s a step-by-step approach to achieve that based on the provided SQL script: ### Step 1: Create a New View 1. **Log in to SAP Datasphere.** 2. **Navigate to the Data Models area** and choose to create a new view. 3. Select "Graphical View." ### Step 2: Define the Data Source 1. **Add Data Sources:** - From your SQL, you’ll mainly use the `"_SYS_BIC"."ALSEER/AN_OPEN_ORDER_DTL"`, `"_SYS_BIC"."ALSEER/AT_AGENCY"`, and `"_SYS_BIC"."ALSEER/CV_ART_MVG_AVG_PRICE"` tables as data sources. - Drag and drop these tables into the graphical model area. ### Step 3: Define Joins Between Tables 1. **Create Relationships:** - **Join `AN_OPEN_ORDER_DTL` (Alias A) and `AT_AGENCY` (Alias B)** on `A.SLS_ORG_CD` = `B.AGENCY_SALES_ORG_CODE` AND `A.ARTI_AGNCY_CD` = `B.AGENCY_CD` - **Join `AN_OPEN_ORDER_DTL` (Alias A) and `CV_ART_MVG_AVG_PRICE` (Alias C)** on `A.SLS_ORG_CD` = `C.SLS_ORG_CD` AND `A.ARTI_CD` = `C.ARTI_CD` - Use Left Outer Joins as specified in your SQL. ### Step 4: Define Filters 1. **Add Filters (WHERE Clause):** - Set a filter to only include rows where `A.ARTI_AGNCY_CD` IS NOT NULL. - You might want to visualize the filter using a graphical filter component. ### Step 5: Aggregate Data 1. **Aggregate Functions:** - Select the fields that are to be aggregated (e.g., `OPN_OPN_PO_VALUE`, `OPN_ORD_DTL_PO_QTY`, etc.). - Choose the aggregation functions such as `SUM()`, and set up aliases such as `OPN_OPN_PO_VALUE_AED`. 2. **Create Calculated Fields:** - For the conditional aggregation based on `COMP_CD_CNTRY`, create calculated fields for `OPN_OPN_PO_VALUE_AED` and `OPN_OPN_PO_VALUE_COST_AED`. - Example: - Create a calculated field for `OPN_OPN_PO_VALUE_AED` with logic `CASE WHEN A.COMP_CD_CNTRY = 'OM' THEN SUM(A.OPN_OPN_PO_VALUE)*9.531 ELSE SUM(A.OPN_OPN_PO_VALUE) END`. ### Step 6: Group By 1. **Define Grouping:** - In the aggregation module, set the fields for grouping based on the GROUP BY clause in the SQL: - `COMP_CD`, `COMP_CD_CNTRY`, `SLS_ORG_CD`, `SLS_ORG_DESC`, `SITE_CD`, `YEAR`, `YR_MNTH_SAP`, `AGENCY_GROUP`, `AGENCY_BY_SALES`, `ARTI_AGNCY_CD`, `ARTI_BRAND_CD`, `ARTI_BRAND_DESC`, `ARTI_CATEGORY_CD`, `ARTI_CATEGORY_DESC`, `ARTI_PRNCP_SEG`, `ARTI_PRNCP_ADDNL_SEG`. ### Step 7: Define Output 1. **Select Output Columns:** - Ensure that the selected output fields match the final output of your script. - Add the aggregated fields and any other fields you wish to expose. ### Step 8: Save and Activate the View 1. **Save the View:** - Once the graphical model is complete, save your view. 2. **Activate the View:** - Activate the model to make it available for use. ### Step 9: Test the View 1. **Visualize Data:** - After activation, test the view by running it in the explorer to see if it reflects the expected data as intended from the SQL script. ### Conclusion You’ve now created a graphical view in SAP Datasphere that reflects the provided SQL script. This model can be used for reports, dashboards, or further analyses in the SAP environment. If needed, you can refine further based on specific visualization or dashboard use cases.