Case Study: Answers

ANSWERS

(a) (i) Dashboard for vendors:  (One Module in a data visualization tool for investigations for drilling down information about specific vendors and implementing partners without having to log in to SAP.

ANSWER: As the initial phase for the analytics component for the audit section will focus on ‘Procurement of goods and services’ and vendor analysis being a part of it, assumes a very important role. Understanding of various vendors that an organization outsources its products and services and the amount each vendor charges for the same products and services is very important as it helps in better selection of vendors based on different categories like prices, discounts, commodity etc.

When a visual has a hierarchy, we can drill down to reveal additional details. A visual for vendors and IP's that looks at count by a hierarchy made up of commodities/services supplied/discounts/location.

 Existing Power BI tool can be used for creating visualizations. Data can be filtered using various filters that are available in Power BI. There are basically three types of filters, namely, page-level filters, drill through filters, and report-level filters.

• Drill through filters: With drill through filters in Power BI Desktop, we can create a page in our reports that focuses on specific entities such as suppliers, vendors, IP’s
• Page-level filters: These are used to filter charts that are present in individual page.
• Report-level filters: They are used to simultaneously filter charts that are present in all pages of a report.

 We would need to connect SAP DW with Power BI (if already not connected):

Connecting SAP DW with Power BI: Implementation of 2.0 SAP Connector requires the SAP .NET Connector 3.0. Download the SAP .NET Connector 3.0 from SAP. While installation, in optional setup steps, make sure we select Install assemblies to GAC. In case of other versions respective tech notes can be referred

To create a new connection: Select Get Data. Select either SAP Business Warehouse Application Server or SAP Business Warehouse Message Server, and then connect. In the new connection dialog, select the implementation. Selecting 2.0 for Implementation, enables Execution mode, Batch size and Enable characteristic structures. Finally select OK.

Accessing dashboards without log in: The option “Publish to Web” is available under File Menu when we open the report in Power BI Service. An embed code is generated, which can be directly inserted into your application. If dashboard is no more required, then we can delete existing embed codes through the Power BI Service (Settings -> Manage Embed Codes).

 (a) (ii) exception scripts (Two automated exception scripts to be deployed by Internal Audit for two selected ‘Red Flags’ (e.g. Duplicate Vendors, Payments, splitting of contracts, single sourcing etc.)

 ANSWER:

SQL trigger can be used for the same. Triggers always fire after the update or insert. If the criteria don’t match, then it rolls back.

CREATE OR REPLACE TRIGGER trg_dup

BEFORE INSERT ON Vendors

FOR EACH ROW

DECLARE counter integer;

BEGIN

  SELECT * INTO counter FROM

      (SELECT COUNT(rownum) FROM Vendors a

        WHERE a.VENDOR_ID = :new.VENDOR_ID);

  IF counter = 1 THEN

  RAISE_APPLICATION_ERROR( -20003,  'Error: duplicate data.');

  END IF;

END;

/

Best solution is to ensure data integrity. We ensure database integrity using constraint. In this case, a unique constraint. That is defined using a unique constraint or index:

ALTER TABLE VENDORS ADD CONSTRAINT UNQ_VENDORS_V_P_A ON (VENDOR_ID, PAYMENT_ID, AMOUNT);

 

(a) (iii) Office strategy for medium to long term (Develop the overall Office strategy for 2021 and beyond, based on lessons learned from the first phase)

ANSWER

1. Requirement gathering and defining of objectives: First and foremost, objective/s needs to be defined based on discussion with all the stakeholders. It will help in planning required data sources, human resources with specific skill sets, any required automations, high risk areas, required reports and their consumers, standard/non-standard operations, auditing tasks and how often they should run etc.

2. Technology environment understanding: Early and regular interactions with IT team will help in establishing a robust technical architecture to suit our analytical requirements. Discussion of our established objectives will help in clarifying our data requirements and will reduce delays due to any established protocols and security concerns.

3. Audit analytics architecture: Setting up of centralized and secure audit environment, planning hardware and storage requirements, setting up of audit data repository and data dictionary

4. Data analytics strategy:

a.   Data accessibility: Creation of server profiles and database profiles for the audit team to gain secure and authorized access to the data and the hardware platform(s) where the processing will take place.

b.  Data analysis protocols: Maintain the security and confidentiality of source data and analytical results, avoid data duplication, no local data storage read data directly from databases, data extraction on dedicated audit analytics server

c.    Business process understanding: Creation of business process flow chart to identify data sources, possible area of concerns (processes involving different applications or systems)

d.     Resource alignment: Resources with specific skills like AI and ML, statistical programmers, data analysts, IT experts, audit or functional experts would be identified and assigned to specific task. Multi skilled resources can act as bridge between different functions.

5. Software update and rollouts: Proper rollout strategy need to be set up for any software/ hardware updates and rollout to avoid lengthy delay and sudden break downs. Such strategy will involve coordination of resources from across IT and audit departments.

6.Training and documentation: Proper documentation of defined processes, continuous standardization of reporting formats, regular training programs for audit team about analytics advantages, awareness about recent developments in audit analytics

 (b) Which functions in the organization would you collaborate with, other than the office of audit and investigations?

ANSWER

The work of audit and investigation requires coordinating with multiple departments. Finance – for getting the actual numbers, procurement – for getting the actual amounts, program performance and reporting – for data related to IP’s and IT – for overall analytics environment functioning. Contacting country and region offices would also be required to get on ground reports about specific programs run by the offices. Selection of data warehouse architecture heavily depends on these department and the kind of data they have.

An ideal case would have been a data warehouse seamlessly integrated with different departments based on laid down security policies and business processes.  Data from each of the offices would be constantly pushed to the data warehouse using ETL process and accessible based on user roles and responsibilities. This would have greatly reduced the chances of physical meetings and coordination, thereby saving lot of time.

(c) What key challenges do you foresee in implementing the plans?
(d) How will you respond to these challenges?

ANSWER 

As the above questions are inter-related, I am answering them both in detail in below section:

All the departments in an organization work to support a common goal. While departments operate separately, they are also interdependent. Below are some of the expected challenges and ways to respond:

 1. Changing the culture: Based on the case study it seems departments are hesitant in data sharing and there is limited convergence of interest. So, changing the culture would be the biggest challenge. At the same time privacy concerns needs to be properly addressed. Response/Solution: Sensitization program about importance of audit and investigations, awareness program about working for a common goal, cross team building exercises

2. Data silos: Due to lack of seamless integration of inter-departmental data, data silos have been created, limiting the view of data, threatening data integrity, wasting resources and discouraging collaborative work. That’s the reason the existing SAP DW has not yet matured. This has led to issues in collecting even basic holistic data, creation of ‘blind spots’ leaving red flags and eventually leading to potential leads being missed. Response/Solution: Reassessing the underlying data warehouse architecture would be the starting point. Centralized/component-based architecture, Star /snowflake schema, role of data marts (dependent and independent) etc. needs to be adjusted keeping in mind current status and future prospects.

3. Data infrastructure performance: Legacy data warehouses (whether on-premises or cloud based) haven’t kept up with all the data requests users have. Managing and preventing these issues can take up a lot of IT time, and the problems often compound over time. Hitting capacity limits slows down users and ties up database administrators too. Response/Solution: From a data infrastructure perspective, separating the compute and storage layers is essential to achieve business agility. When a data warehouse can handle your scalability needs and self-manage performance, that’s when you can really start being proactive.

4. Data preparation: As mentioned, different systems are at different maturity levels which has led to inconsistency in data and bound to encounters errors. Inconsistent data, duplicates, and missing data all result in data quality challenges which can lead to faulty reporting and analytics necessary for optimal decision-making. Data preparation challenges can be further divided into below sub-challenges: 

• Data identification: Identifying most appropriate sources from which data element can be acquired and analyzing the formats, potential duplication, potential incremental value and source reliability
• Data acquisition: Physical collection of data from heterogenous sources
• Data cleansing: Elimination of duplicates, erroneous values correction, replacing missing values and any other transformation
• Data transformation: Enrichment of data into more business descriptive content

Response/Solution: Auditing of data sources is required to see how many different databases/applications are present. Existing ETL design needs to be reviewed. Need to check ETL operations based on schema type, SCD’s (slowly changing dimensions), type of transformations required.

5. Understanding business data: When building a data warehouse, analytics and reporting will have to be taken into design considerations. In order to do this, the business user will need to know exactly what analysis will be performed. Envisioning these reports will be difficult for someone who has no experience in business-intelligence data analysis, and who is unaware of its capabilities. Response/Solution: Better understanding of business processes can be of great help. Regular interaction with subject matter experts during the planning stage can help in pin-pointing the exact analytics and reporting required.

6. Lack of analytics tool: There is lack of dedicated analytics tool. An analytics tool becomes a necessity keeping in mind the goals of revealing outliers, risk profiling, pattern analysis, fraud risk assessment, testing of red flags and continuous auditing of key controls. Above mentioned in-depth analysis involve large-scale and resource intensive computations leading to new insights being discovered. Artificial intelligence (AI) and machine learning (ML) are already changing the face of every industry, where forecasting, segmentation, outlier analysis, natural language processing (NLP) are helping the businesses making better decisions. Response/Solution: For analytics related tasks option that are easy to integrate with SAP DW can be checked. Power BI already present suffices the need for visualizations. For predictive modelling python (with its packages – scikit-learn, numpy, scipy) and R can be of great help. SAS E miner, IBM SPSS modeler can also be looked as predictive modelling software.