Implementing Best Practices When Using Business Intelligence Tools

In the previous chapter, we learned how to use business intelligence (BI) tools to fix data integrity issues. We also covered the various data profiling features available and how to remove empty cells, remove duplicates, manage relationships in data models, identify data outliers, and deal with large financial datasets using data validation.

In this chapter, we will discuss the best practices when using Power BI Desktop, Tableau, and Alteryx Designer for data quality and integrity. By the end of this chapter, you will have a better understanding of how to leverage best practices when using these BI tools to ensure data integrity in finance.

We’ll cover the following in this chapter:

• Handling confusing date convention formats
• Using data visualization to identify data outliers
• Managing orphaned records

By the end of this chapter, you will have a deeper understanding of what these BI...

Technical requirements

This chapter is a continuation of our discussion from Chapter 4, Understanding the Data Integrity Management Capabilities of Business Intelligence Tools, and Chapter 5, Using Business Intelligence Tools to Fix Data Integrity Issues. Thus, we will be using the same tools and datasets that we used previously:

• Power BI Desktop: April 2023 or later (no licenses required)
• Tableau Desktop: 2023.1 or later (free trial version)
• Tableau Prep Builder: 2023.1.0 or later (free trial version)
• Tableau Cloud: (free trial version)
• Alteryx Designer: 2022.3 version or later (free trial version)

Our sample datasets are saved in the Packt GitHub repo: https://github.com/Packt Publishing/Managing-Data-Integrity-for-Finance/tree/main/ch05. Chapters 5 and 6 both use the same datasets for the hands-on exercises.

Note

It is recommended that you read both of the previous chapters mentioned for better appreciation and understanding of this chapter....

Handling confusing date convention formats

One of the most common data integrity issues encountered when dealing with date time values involves the inconsistent positioning of the month and date values in data entries and transactions. In some countries, mm/dd/yyyy is used for the date format. In other countries, dd/mm/yyyy is used. Of course, the number of days (that is, 01 to 31) exceeds the number of months (that is, 01 to 12). However, what if the record stored in the sheet or database is 03/06/1990? If the assumed format is mm/dd/yyyy, then 03/06/1990 will be interpreted as March 6, 1990. On the other hand, if the assumed format is dd/mm/yyyy, then the same date will be interpreted as June 3, 1990 instead.

Now, we have a data integrity issue when a single column involves both formats. There are a variety of reasons this could happen and one of the possible causes is if records from multiple data sources were merged into a single sheet or table without taking into account the...

Using data visualization to identify data outliers

Visualizations enable business professionals and companies to make sense of the numbers and deliver insights. They also make it easier to spot data outliers, as we will see in this section. Identifying outliers is important since they can significantly affect how you interpret the data and what actions you take. We can ask, Is this data point correct?, Is there insight from this?, or Could this be a potentially fraudulent transaction? In this section, we will visualize data outliers using Microsoft Power BI and Tableau through a scatter chart and a histogram.

Continuing our example from Chapter 5, Using Business Intelligence Tools to Fix Data Integrity Issues, about column distribution, let’s further analyze the dataset for 2020_Transactions.xlsx and review the sales quantity.

Figure 6.6 – Information about the column statistics and value distribution of the column

When we select the Quantity...

Managing orphaned records

Orphaned records, as the name suggests, are records whose relationship to the parent record does not exist. If we delete Product_ID 151003 for Income protection insurance, this means that the transactions for this product are not referencing any financial information contained in the Product_Details table.

In some cases, if raw financial data is provided and generated by a Dev team, and the Dev team does not perform referential integrity checks, issues may occur. Thus being able to detect orphaned records, especially when dealing with multiple records, as early as possible is critical.

The next steps will cover how to do this in Power BI.

Identifying orphaned records in Power BI

Let’s go to Transform data and delete one of the product IDs. Select the Income protection insurance row and click on Remove Rows under the Home tab, as shown in Figure 6.40. Don’t worry, we can undo this step later, under APPLIED STEPS.

...

Summary

In this chapter, we discussed the best practices when using business intelligence tools such as Microsoft Power BI Desktop, Tableau Desktop, Tableau Prep Builder, and Alteryx Designer. We covered how to handle potentially confusing date convention formats by updating the format and checking the locale settings. Ensuring that date formats are consistent will help ensure that our analysis is accurate and reliable. Next, we used data visualization features such as the scatter chart in Power BI and the histogram in Tableau to identify data outliers. By working on these hands-on examples, I am sure that you have grown more confident in what you have learned. Lastly, we discussed how to find and manage orphaned records in both Power BI and Alteryx. Understanding various ways to address a problem is an important skill to develop.

Business intelligence tools are powerful tools to clean, transform, analyze, and visualize data. They can even be used to detect fraudulent transactions...

Further reading

For additional information on the topics covered in this chapter, you may find the following resources helpful:

• Desai, Veeral, Fountaine, Tim, and Rowshankish, Kayvaun. A Better Way to Put Your Data to Work :

  https://hbr.org/2022/07/a-better-way-to-put-your-data-to-work

• Strategic Finance Magazine Teaching the ETL Process:

  https://sfmagazine.com/articles/2022/august/teaching-the-etl-process/

• Visualization types in Power BI:

  https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-types-for-reports-and-q-and-a

• What is Data Visualization? Definition, Examples, And Learning Resources:

  https://www.tableau.com/learn/articles/data-visualization

• Alteryx Join Tool:

  https://help.alteryx.com/20231/designer/join-tool