merge org branch into main

1 files changed, 171 insertions, 0 deletions

diff --git a/content/blog/2024-01-26-audit-dashboard.org b/content/blog/2024-01-26-audit-dashboard.org
new file mode 100644
index 0000000..e48c938
--- /dev/null
+++ b/content/blog/2024-01-26-audit-dashboard.org
@@ -0,0 +1,171 @@
+#+title: Building an Audit Status Dashboard
+#+date: 2024-01-26
+#+description: Learn how to utilize Alteryx Designer and Power BI Desktop to build a simple status tracking dashboard for an audit or other engagement.
+#+filetags: :audit:
+
+Alteryx and Power BI are powerful tools that can help turn your
+old-school audit trackers into interactive tools that provide useful
+insights and potential action plans.
+
+With these tools, we are going to build the following dashboard:
+
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/dashboard_01.png]]
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/dashboard_02.png]]
+
+* Requirements
+This project assumes the following:
+
+- You have access to Alteryx Designer and Power BI Desktop.
+  - If you only have Power BI Desktop, you may need to perform some
+    analysis in Power BI instead of Alteryx.
+- Your data is in a format that can be imported into Alteryx and/or
+  Power BI.
+- You have a basic understanding of data types and visualization.
+
+* Alteryx: Data Preparation & Analysis
+** Import Data
+With Alteryx, importing data is easy with the use of the =Input Data=
+tool. Simply drag this tool onto the canvas from the =In/Out= tab in the
+Ribbon to create it as a node.
+
+You can choose the File Format manually or simply connect to your
+file/database and let Alteryx determine the format for you. For this
+example, we will be importing an Excel file and changing the
+=Start Data Import on Line= variable to =2=.
+
+#+caption: Alteryx Excel Import
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/alteryx_import.png]]
+
+** Transform Data
+Next, let's replace null data and remove whitespace to clean up our
+data. We can do this with the =Data Cleansing= tool in the =Preparation=
+tab in the Ribbon.
+
+Ensure that the following options are enabled:
+
+- Replace Nulls
+  - Replace with Blanks (String Fields)
+  - Replace with 0 (Numeric Fields)
+- Remove Unwanted Characters
+  - Leading and Trailing Whitespace
+
+#+caption: Data Cleansing
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/alteryx_cleansing.png]]
+
+For our next step, we will transform the date fields from strings to
+datetime format. Add a =Datetime= tool for each field you want to
+transform - in the example below, I am using the tool twice for the
+"Started On" and "Submitted On" fields.
+
+#+caption: Data Transformation
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/alteryx_transformation.png]]
+
+Now that the dates are in the correct format, let's perform a
+calculation based on those fields. Start by adding a =Filter= tool,
+naming a new Output Column, and pasting the formula below into it (the
+two fields used in this formula must match the output of the =Datetime=
+tools above):
+
+#+begin_src txt
+DateTimeDiff([SubmittedOn_Out],[StartedOn_Out], "days")
+#+end_src
+
+#+caption: Data Analysis
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/alteryx_analysis.png]]
+
+** Export Data
+Finalize the process by exporting the transformed data set to a new
+file, for use in the following visualization step.
+
+#+caption: Data Export
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/alteryx_export.png]]
+
+* Power BI: Data Visualization
+** Import Data
+To start, open the Power BI Desktop application. Upon first use, Power
+BI will ask if you want to open an existing dashboard or import new
+data.
+
+As we are creating our first dashboard, let's import our data. In my
+example below, I'm importing data from the "Tracker" sheet of the Excel
+file I'm using for this project.
+
+During this process, I also imported the export from the Alteryx
+workflow above. Therefore, we have two different files available for use
+in our dashboard.
+
+#+caption: Excel Tracker
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/excel_tracker.png]]
+
+#+caption: Power BI Excel Import
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/powerbi_import.png]]
+
+** Add Visuals
+To create the dashboard below, you will need to follow the list
+instructions below and format as needed:
+
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/dashboard_01.png]]
+[[https://img.cleberg.net/blog/20240126-audit-dashboard/dashboard_02.png]]
+
+Instructions to create the visuals above:
+
+- =Text Box=: Explain the name and purpose of the dashboard. You can
+  also add images and logos at the top of the dashboard.
+- =Donut Chart=: Overall status of the project.
+  - =Legend=: Status
+  - =Values=: Count of Status
+- =Stacked Column Chart=: Task count by assignee.
+  - =X-axis=: Preparer
+  - =Y-axis=: Count of Control ID
+  - =Legend=: Status
+- =Treemap=: Top N client submitters by average days to submit.
+  - =Details=: Preparer
+  - =Values=: Sum of Avg_DaysToSubmit
+- =Line Chart=: Projected vs. actual hours over time.
+- =Clustered Bar Chart=: Projected vs. actual hours per person.
+- =Slicer & Table= - Upcoming due dates.
+  - =Slicer=:
+    - =Values=: Date Due
+  - =Table=:
+    - =Columns=: Count of Control ID, Date Due, Preparer, Status
+
+** Format the Dashboard
+You can choose a theme in the View tab of the Ribbon. You can even
+browse for custom JSON files that define themes, such as ones found
+online or custom ones created by your organization.
+
+For each visual, you can click the =Format= button in the
+=Visualizations= side pane and explore the options. You can custom
+options such as:
+
+- Visual
+  - Legend
+  - Colors
+  - Data labels
+  - Category labels
+- General
+  - Properties
+  - Title
+  - Effects
+  - Header icons
+  - Tooltips
+  - Alt text
+
+You can always look online for inspiration when trying to decide how
+best to organize and style your dashboard.
+
+* Sharing the Results
+Generally, you have a few different options for sharing your dashboards
+with others:
+
+1. Export the dashboard as a PDF in the file menu of Power BI. This will
+   export all tabs and visuals as they are set when the export button is
+   pressed. You will lose all interactivity with this option.
+2. Send the full Power BI file to those you wish to share the dashboard.
+   This will retain all settings and interactivity. However, you will
+   also need to send the source files if they need to refresh the
+   dashboard and you will need to re-send the files if you make updates.
+3. Store the dashboard in a synced location, such as a shared drive or
+   Microsoft Teams. Depending on how a user configures their local
+   Windows paths, the data source paths may not be compatible for all
+   users with such a setup.