The objective is to create a workflow solution that addresses the following requirements:
• The workflow process should be seamless, guiding end-users through the entire process.
• Data loading should be monitored to provide end-users with an overview of data availability.
Traditionally, solutions to these issues involve the use of scheduler software, workflow software, master data services, and web development. However, these tools present challenges in this context:
• Sophisticated workflow solutions are often expensive and impractical.
• Master data management can address some issues but is complex and does not fully resolve workflow challenges.
This article explores an affordable and innovative solution using Power BI and Power Automate, eliminating the need for custom or standard package software.
Solution Overview
The proposed solution leverages Power BI Dashboards and Power Automate as follows:
• Create highly interactive Power BI dashboards that function as a "web form," allowing users to navigate through various actions.
• Use Power BI dashboards to display overall status and enable users to control the data loading process.
• Utilize Power Automate visuals to automate processes as needed.
• Log all statuses, which can be displayed on the dashboard in real-time.
The following screenshot illustrates the main concept:
Note: This interface is a conceptual demonstration. In practice, it can be customized for any data load management. Key functions include:
1. Tracking the status of all streams.
2. Enforcing workflow logic to ensure users complete all steps.
3. Allowing users to manage situations similar to an operations dashboard.
The architectural diagram is shown below:
Implementation Challenges
Define a Flexible Data Model: Develop a framework or metadata that can handle similar problems. Data flow should be highly customizable but not overly complex, with main tables for year-level and stream-level Log
Write User Input into Database via Power Automate: While Power Automate can typically insert data into a database without issues, challenges may arise with specific databases like Netezza. A workaround involves using a data gateway, saving a control file to the process folder, and then inserting data status into the database.
Transform Power BI Dashboard into "Web Forms": To provide a user interface for the data workflow concept, find a way to convert reports into "Web forms." This can be achieved by leveraging Power BI key concepts, including DAX, conditional visibility, and buttons.
Stream Status =
VAR MaxStatusTimePerSector =
CALCULATE ( MAX ( 'Stream Log'[Status Time] ) )
VAR StatusCode =
CALCULATE (
MAX ( 'Stream Log'[Status Code] ),
'Stream Log'[Status Time] = MaxStatusTimePerSector
)
VAR result =
CALCULATE (
SELECTEDVALUE ( 'Status'[Status Name] ),
'Status'[Status Code] = StatusCode
)
RETURN
result
Stream Status =
VAR CurrentWorkYear = [Current Work Year]
VAR StreamStatus =
ADDCOLUMNS (
VALUES ( 'Stream'[Stream Code] ),
"Year", CurrentWorkYear,
"Status", CALCULATE ( [Stream Status], 'Stream Log'[Year] = CurrentWorkYear )
)
RETURN
StreamStatus
Stream Actions =
VAR CurrentWorkYear = [Current Work Year]
VAR StreamStatus =
ADDCOLUMNS (
VALUES ( 'Stream'[Stream Code] ),
"Year", CurrentWorkYear,
"Status", CALCULATE ( [Stream Status], 'Stream Log'[Year] = CurrentWorkYear )
)
VAR StatusActionMap =
DATATABLE (
"Status", STRING,
"Action", STRING,
"ActionSortOrder", INTEGER,
{
{ "Status 1", "Action 1", 1 },
{ "Status 2", "Action 2", 2 },
{ "Status 3", "Action 3", 3 },
{ "Status 4", "Action 3", 3 },
{ "Status 4", "Action 4", 4 },
{ "Status 5", "Action 1", 1 },
{ "Status 6", "Action 1", 1 }
}
)
VAR result =
NATURALINNERJOIN ( StreamStatus, StatusActionMap )
RETURN
result
Stream Actions - Stream 1 =
VAR YearStatus = [Year Status]
VAR Stream =
IF ( YearStatus = "Closed", "ZZZ", "Stream 1" )
VAR ActionsFilteredByStatus =
CALCULATETABLE ( 'Stream Actions', 'Stream Actions'[Stream Code] = Stream )
VAR Actions =
SELECTCOLUMNS (
ActionsFilteredByStatus,
"Year", [Year],
"Stream Code", [Stream Code],
"Status", [Status],
"Action", [Action],
"ActionSortOrder", [ActionSortOrder]
)
VAR DefaultActions =
ROW (
"Year", [Current Work Year],
"Stream Code", "Stream 1",
"Status", "n/a",
"Action", "Status log",
"ActionSortOrder", 10
)
VAR result =
UNION ( Actions, DefaultActions )
RETURN
result
Implement Permission Pass-Through: Ensure that permissions are correctly propagated through the system.
Note: Action
Choices are NOT refreshed with status accordingly on Cloud if the dashboard work against DirectQuery, on Clould, with calculated
table. The solution is to use calculated measure instead of using calculated table
Step 1: Establish a hardcoded map between
status and actions, which was defined based on original logic:
StreamStatusActionMap =
DATATABLE (
"Status", STRING,
"Action", STRING,
"ActionSortOrder", INTEGER,
{
{ "Status 1", "Action 1", 1 },
{ "Status 1", "Action 99", 99 },
{ "Status 2", "Action 2", 2 },
{ "Status 2", "Action 99", 99 },
{ "Status 3", "Action 3", 3 },
{ "Status 4", "Action 99", 99 },
{ "Status 5", "Action 1", 1 },
{ "Status 5", "Action 3", 3 },
{ "Status 5", "Action 4", 4 },
{ "Status 5", "Action 99", 99 },
{ "Status 6", "Action 1", 1 },
{ "Status 6", "Action 99", 99 },
{ "Status 7", "Action 1", 1 },
{ "Status 7", "Action 99", 99 }
}
)
Step 2: Get the current status:
Stream 1 Status Name =
VAR CurrentWorkYear = [Current Work Year]
VAR MaxStatusTime =
CALCULATE (
MAX ( 'Stream Log'[Status Time] ),
'Stream Log'[Stream Code] = "Stream 1"
)
VAR StatusCode =
CALCULATE (
MAX ( 'Stream Log'[ Status Code] ),
'Stream Log'[Status Time] = MaxStatusTime
)
VAR StatusName =
CALCULATE (
SELECTEDVALUE ( 'Stream Status'[ Status Name] ),
'Stream Status'[ Status Code] = StatusCode
)
RETURN
StatusName
Step 3: Get the first Action:
Stream 1 Action Text 1 =
VAR StatusName = [Stream 1 Status Name]
VAR Actions =
CALCULATETABLE (
'StreamStatusActionMap',
'StreamStatusActionMap'[Status] = StatusName
)
VAR Top1 =
TOPN ( 1, Actions, 'StreamStatusActionMap'[ActionSortOrder], ASC )
VAR ActionSelected =
MAXX ( Top1, 'StreamStatusActionMap'[Action] )
VAR NumberOfActions =
COUNTROWS ( Actions )
VAR result =
IF ( NumberOfActions > 1, ActionSelected )
RETURN
result
Step 4: Get the Second Action:
Stream 1 Action Text 2 =
VAR StatusName = [Stream 1 Status Name]
VAR Actions =
CALCULATETABLE (
'StreamStatusActionMap',
'StreamStatusActionMap'[Status] = StatusName
)
VAR Top2 =
TOPN ( 2, Actions, 'StreamStatusActionMap'[ActionSortOrder], ASC )
VAR Top1 =
TOPN ( 1, Top2, 'StreamStatusActionMap'[ActionSortOrder], DESC )
VAR ActionSelected =
MAXX ( Top1, 'StreamStatusActionMap'[Action] )
VAR NumberOfActions =
COUNTROWS ( Actions )
VAR result =
IF ( NumberOfActions > 1, ActionSelected )
RETURN
result
No comments:
Post a Comment