💡 Note this feature has been superseded by the Power BI API. Learn more here.
Teams using Aphex to build and coordinate their lookahead planning generate meaningful performance data that can inform project decision making and performance management. In addition to the existing tools that Aphex provides to harness this data (such as Aphex Insight and CSV Exporting) we have recently released into Beta the ability to use your project's published plans as a web source for Power BI.
This feature, will feed all the data from your project's most recent publications directly to Power BI. Teams can use this Beta feature to establish standard Power BI dashboards from Aphex data and each week, update them by simply hitting "Refresh Data".
💡This is a Beta feature, meaning it may contain bugs or be subject to change! Want to shape how it is developed to work for you? Let us know at firstname.lastname@example.org
As a robust analytics platform, Power BI can consume and visualise data from a wide range of sources. These include files on your device and external databases. Aphex can be configured as a "Web" data source which, when queried, will return data in JSON format. The data returned will be a list of tasks from the 5 most recent Published Plans on a specific Project in Aphex. The Aphex Power BI (Beta) data source is structured on the following principles;
Project Specific: Each URL provided is unique to a project and will return only results from that Project.
Published Data Only: The data provided is designed for weekly dashboard style reporting and is therefore focused (and limited to) the Published Plan data (not the live plan). The source is currently limited the 5 most recent publications for a project.
Data Structure: The URL will return three three below tables for each project. These tables are related by TaskID and can therefore be linked in Power BI.
Tasks: A table of every Task in each Published Version within the range of the JSON file. The Task table contains columns with all the Task attributes in the Aphex Data Model such as Subcontractor and Duration.
Status: A table of every Status Event in each Published Version within the range of the JSON file. The Status table contains columns with all the Status Event attributes in the Aphex Data Model such as Event Type and Impact.
Resources: A table of every Resource assignment in each Published Version within the range of the JSON file. The Resource table contains columns with all the Resource attributes in the Aphex Data Model such as Quantity and Type.
Enabling the Source
Each project can be individually enabled as a web source for Power BI by a Project Administrator. To enable the feature, navigate to the 'Project Settings' page and locate 'Power BI Data (Beta)'.
Toggling on the "Use as Power BI data source" control will reveal the project's unique source URL. If at any time, you wish to disable this URL as a public source of project data, you can do so by reverting the toggle.
💡 Note that Organisation Administrators can also do this by selecting the Project from within the Aphex Admin app.
Connecting to Power BI
To connect to an external data source, you will need to have Power BI Desktop (https://powerbi.microsoft.com/en-us/desktop/) installed on your device and any relevant Microsoft Power Apps licences.
💡 Want to use this feature but don't have Power BI Desktop? You can connect to a web data source from Excel also.
Within Power BI (or Excel), create a new report and select "Get Data". This will open a selection window similar to below.
Search and find the source "Web" and select connect. Simply paste your unique URL from the step above and leave all other settings as default.
Extracting Data with Power Query
Once you have successfully connected to the Aphex Power BI endpoint, Power BI (or Excel) will automatically present the data in the Power Query Editor to enable the data to be transformed. In the below, we can see that the response as returned the 3 tables of Tasks, Status and Resource noted above.
Recommended Power Query Transformations
Here are the steps we recommend following in Power Query to make your data as useful as possible.
Separate into 3 Referenced Tables: Start by making 3 referenced versions of the original project source and removing columns such that you are left with 4 tables (Original Project Source, Just Tasks, Just Status and Just Resource - we recommend naming each one after the contents to make things easier later!)
Expand out Each Table: Now that we have our 3 referenced tables each with unique data we can expand them our to reveal the data they hold. Repeat this step across each of the 3 referenced tables.
Validate Column Types: Before closing the Power Query Editor it is useful to check that the column types have been recognised correctly. For example, all date related columns (such as Task.Finish) should be Type "Date Time". If you do miss some, you can return here later. Once complete, select "Close & Apply".
Establish Relationships: Our project data is now available in the Power BI workspace and is ready to start being visualised. In order to undertake 'cross-table' analysis (i.e. filter Delays by properties on Tasks) we can also establish a relationship between the tables. To do this, head to 'Manage Relationships' and create relationships between Task.Id and Status.TaskId and Task.Id and Resource.TaskId
Once connected you can refresh the data in Power BI with the latest Published data from Aphex with zero config required. Simply hit "Refresh Data" from within Power BI and it will query the Aphex database again.
Give us Feedback
This feature is in Beta because building the infrastructure is the easy part! We want to ensure we are providing the best possible dataset for teams to use and therefore we really encourage any feedback on how this could be more useful for you. You can contact us at email@example.com or email our COO and Product Leader directly at: firstname.lastname@example.org.
As our Beta progresses, we may publish updates to our dataset as a result of user feedback.