cmc/cleberg.net

  1#+date:        [2024-01-26 Fri 00:00:00]
  2#+title:       Audit Dashboard: Alteryx and Power BI
  3#+description: Building an audit dashboard with Alteryx for data prep and Power BI for visuals.
  4#+slug:        audit-dashboard
  5#+filetags:    :audit:
  6
  7Alteryx and Power BI are powerful tools that can help turn your old-school audit
  8trackers into interactive tools that provide useful insights and potential
  9action plans.
 10
 11With these tools, we are going to build a dashboard that can effectively
 12communicate project status.
 13
 14* Requirements
 15
 16This project assumes the following:
 17
 18- You have access to Alteryx Designer and Power BI Desktop.
 19  - If you only have Power BI Desktop, you may need to perform some analysis in
 20    Power BI instead of Alteryx.
 21- Your data is in a format that can be imported into Alteryx and/or Power BI.
 22- You have a basic understanding of data types and visualization.
 23
 24* Alteryx: Data Preparation & Analysis
 25
 26** Import Data
 27
 28With Alteryx, importing data is easy with the use of the =Input Data= tool.
 29Simply drag this tool onto the canvas from the =In/Out= tab in the Ribbon to
 30create it as a node.
 31
 32You can choose the File Format manually or simply connect to your file/database
 33and let Alteryx determine the format for you. For this example, we will be
 34importing an Excel file and changing the =Start Data Import on Line= variable to
 35=2=.
 36
 37** Transform Data
 38
 39Next, let's replace null data and remove whitespace to clean up our data. We can
 40do this with the =Data Cleansing= tool in the =Preparation= tab in the Ribbon.
 41
 42Ensure that the following options are enabled:
 43
 44- Replace Nulls
 45  - Replace with Blanks (String Fields)
 46  - Replace with 0 (Numeric Fields)
 47- Remove Unwanted Characters
 48  - Leading and Trailing Whitespace
 49
 50For our next step, we will transform the date fields from strings to datetime
 51format. Add a =Datetime= tool for each field you want to transform - in the
 52example below, I am using the tool twice for the "Started On" and "Submitted On"
 53fields.
 54
 55Now that the dates are in the correct format, let's perform a calculation based
 56on those fields. Start by adding a =Filter= tool, naming a new Output Column,
 57and pasting the formula below into it (the two fields used in this formula must
 58match the output of the =Datetime= tools above):
 59
 60```txt
 61DateTimeDiff([SubmittedOn_Out],[StartedOn_Out], "days")
 62```
 63
 64** Export Data
 65
 66Finalize the process by exporting the transformed data set to a new file, for
 67use in the following visualization step.
 68
 69* Power BI: Data Visualization
 70
 71** Import Data
 72
 73To start, open the Power BI Desktop application. Upon first use, Power BI will
 74ask if you want to open an existing dashboard or import new data.
 75
 76As we are creating our first dashboard, let's import our data. In my example
 77below, I'm importing data from the "Tracker" sheet of the Excel file I'm using
 78for this project.
 79
 80During this process, I also imported the export from the Alteryx workflow above.
 81Therefore, we have two different files available for use in our dashboard.
 82
 83** Add Visuals
 84
 85To create the dashboard, you will need to follow the list instructions below and
 86format as needed.
 87
 88Instructions to create the visuals above:
 89
 90- =Text Box=: Explain the name and purpose of the dashboard. You can also add
 91  images and logos at the top of the dashboard.
 92- =Donut Chart=: Overall status of the project.
 93  - =Legend=: Status
 94  - =Values=: Count of Status
 95- =Stacked Column Chart=: Task count by assignee.
 96  - =X-axis=: Preparer
 97  - =Y-axis=: Count of Control ID
 98  - =Legend=: Status
 99- =Treemap=: Top N client submitters by average days to submit.
100  - =Details=: Preparer
101  - =Values=: Sum of Avg_{DaysToSubmit}
102- =Line Chart=: Projected vs. actual hours over time.
103- =Clustered Bar Chart=: Projected vs. actual hours per person.
104- =Slicer & Table= - Upcoming due dates.
105  - =Slicer=:
106    - =Values=: Date Due
107  - =Table=:
108    - =Columns=: Count of Control ID, Date Due, Preparer, Status
109
110** Format the Dashboard
111
112You can choose a theme in the View tab of the Ribbon. You can even browse for
113custom JSON files that define themes, such as ones found online or custom ones
114created by your organization.
115
116For each visual, you can click the =Format= button in the =Visualizations= side
117pane and explore the options. You can custom options such as:
118
119- Visual
120  - Legend
121  - Colors
122  - Data labels
123  - Category labels
124- General
125  - Properties
126  - Title
127  - Effects
128  - Header icons
129  - Tooltips
130  - Alt text
131
132You can always look online for inspiration when trying to decide how best to
133organize and style your dashboard.
134
135* Sharing the Results
136
137Generally, you have a few different options for sharing your dashboards with
138others:
139
1401. Export the dashboard as a PDF in the file menu of Power BI. This will export
141   all tabs and visuals as they are set when the export button is pressed. You
142   will lose all interactivity with this option.
1432. Send the full Power BI file to those you wish to share the dashboard. This
144   will retain all settings and interactivity. However, you will also need to
145   send the source files if they need to refresh the dashboard and you will need
146   to re-send the files if you make updates.
1473. Store the dashboard in a synced location, such as a shared drive or Microsoft
148   Teams. Depending on how a user configures their local Windows paths, the data
149   source paths may not be compatible for all users with such a setup.