Data Sources#
To easily create diagrams and analyses with Power BI, it is helpful if the data to be analyzed is already in a format that makes complex filtering and restructuring within Power BI unnecessary. Filtering can thus be e.g. outsourced to the data source and does not need to be replicated in the analysis tool.
For these and other reasons, the Allegra Power BI Connector offers a range of data sources for different types of reports and analyses.
Selecting a data source#
When you open a new “Navigator” in Power BI for the Allegra Connector, you will be offered a range of data sources.
![../../_images/powerBI_navigator.png](../../_images/powerBI_navigator.png)
Select a suitable data source and configure it.
![../../_images/powerBI_nav_itemsByFilter.png](../../_images/powerBI_nav_itemsByFilter.png)
Power BI will then lead you to the Data View.
![../../_images/powerBI_itemsByFilter_GridView.png](../../_images/powerBI_itemsByFilter_GridView.png)
You can also display the data hierarchy.
Go to TransformData > Recent sources > Last used
![../../_images/powerBI_transformData.png](../../_images/powerBI_transformData.png)
Set Hierarchical
to TRUE
![../../_images/powerBI_itemsByFilter_hierarchical.png](../../_images/powerBI_itemsByFilter_hierarchical.png)
In the new view, scroll to Child items
, double click on Table
(item with children)
and a new table with subordinate items will open at the bottom.
![../../_images/powerBi_hierarical_view.png](../../_images/powerBi_hierarical_view.png)
Data Sources for Planning#
These data sources provide easy access to the budget, planned value, remaining effort and the sum of the current efforts.
AccountingByFilter#
This data source provides the planning data for all items that match the selected item filter.
![../../_images/powerBI_AccountingByFilter.png](../../_images/powerBI_AccountingByFilter.png)
Parameters:
filterID: String, single value, mandatory; one of the saved query filters
- excludeParentItems: boolean (
true
/false
), optional, defaultfalse
;If this parameter is set to
true
, the parent items of the selected items are not automatically selected.
AccountingByWorkspace#
This data source provides the planning data for all items in the selected workspaces.
![../../_images/powerBI_AccountingByWorkspace.png](../../_images/powerBI_AccountingByWorkspace.png)
Parameters:
workspace: List<int>, multiple value, mandatory; list of workspace IDs
- excludeParentItems: boolean (
true
/false
), optional, defaultfalse
;If this parameter is set to
true
, the parent items of the selected items are not automatically selected.
Accounting-Attribute#
The following item attributes are available for PowerBI:
"fieldNames": [
"itemID",
"itemTitle",
"parentID",
"totalExpenseCost",
"totalExpenseTime",
"totalPlanCost",
"totalPlanTime",
"budgetCost",
"budgetTime",
"remainingCost",
"remainingTime",
"itemProjectID",
"itemProjectLabel",
"itemStatusID",
"itemStatusLabel",
"itemTypeID",
"itemTypeLabel",
"originatorID",
"originatorLabel",
"managerID",
"managerLabel",
"responsibleID",
"responsibleLabel",
"totalExpenseTimeUnit",
"totalExpenseTimeUnitLabel",
"totalPlanTimeUnit",
"totalPlanTimeUnitLabel",
"budgetTimeUnit",
"budgetTimeUnitLabel",
"remainingTimeUnit",
"remainingTimeUnitLabel",
"currencyName",
"currencySymbol"
],
"dateFields": [],
"numberFields": [
"totalExpenseCost",
"totalExpenseTime",
"totalPlanCost",
"totalPlanTime",
"budgetCost",
"budgetTime",
"remainingCost",
"remainingTime"
]
The following image shows the attributes in the Allegra user interface (item detail view). The numbering refers to the entries in the mapping table.
![../../_images/Allegra_worklog_tab.png](../../_images/Allegra_worklog_tab.png)
The following table describes the attribute mapping between Allegra and the Power BI data source.
Allegra |
Power BI |
|||
---|---|---|---|---|
|
Work |
totalExpenseTime |
totalExpenseTimeUnit |
totalExpenseTimeUnitLabel |
Cost |
totalExpenseCost |
|||
|
Work |
budgetTime |
budgetTimeUnit |
budgetTimeUnitLabel |
Cost |
budgetCost |
|||
|
Work |
totalPlanTime |
totalPlanTimeUnit |
totalPlanTimeUnitLabel |
Cost |
totalPlanCost |
|||
|
Work |
remainingTime |
remainingTimeUnit |
remainingTimeUnitLabel |
Cost |
remainingCost |
Data Sources for Efforts#
These data sources provide individual bookings for the temporal and monetary efforts for the selected items.
ExpenseByFilter#
This data source provides the efforts incurred for the items determined by the filter, optionally limited for a certain period of time.
![../../_images/powerBI_ExpensesByFilter.png](../../_images/powerBI_ExpensesByFilter.png)
Parameters:
filterID: String, single value, mandatory; one of the saved query filters
dateFrom: Date (format “day/month/year”) efforts are only considered from this date
dateTo: Date (format “day/month/year”) efforts are only considered up to this date
ExpensesByWorkspace#
This data source provides the efforts incurred for the items in the listed workspaces, optionally limited for a certain period of time.
![../../_images/powerBI_ExpensesByWorkspace.png](../../_images/powerBI_ExpensesByWorkspace.png)
Parameters:
workspace: List<int>, multiple value, mandatory; list of workspace IDs
dateFrom: Date (format “day/month/year”) efforts are only considered from this date
dateTo: Date (format “day/month/year”) efforts are only considered up to this date
Item Attributes for Efforts#
"fieldNames": [
"accountID",
"personID",
"workItemID",
"hours",
"cost",
"subject",
"effortDate",
"description",
"lastEdit",
"accountName",
"accountNumber",
"status",
"costcenterID",
"costcenterName",
"costcenterNumber",
"personName",
"personEmployeeID",
"workItemTitle",
"issueType",
"projectID",
"originator",
"manager",
"responsible",
"projectLabel",
"currencySymbol",
"currencyName",
"statusLabel",
"issueTypeLabel",
"originatorLabel",
"managerLabel",
"responsibleLabel",
"subproject",
"subprojectLabel"
],
"dateFields": [
"effortDate",
"lastEdit"
],
"numberFields": [
"cost",
"hours"
]
Data Sources for Items#
These data sources provide a list of items.
ItemsByFilter#
This data source provides a list of items that the specified filter allows through.
![../../_images/powerBI_ItemsByFilter.png](../../_images/powerBI_ItemsByFilter.png)
Parameters:
filterID: String, single value, mandatory; one of the saved query filters
- hierarchical: boolean (
True
/False
), optional; ifTrue
the result is returned asa hierarchical list, otherwise as a flat list
ItemsByWorkspace#
This data source provides all items from the specified workspaces.
![../../_images/powerBI_ItemsByWorkspace.png](../../_images/powerBI_ItemsByWorkspace.png)
Parameters:
workspace: List<int>, multiple value, mandatory; list of workspace IDs
- hierarchical: boolean (
True
/False
), optional; iftrue
the result is returned asa hierarchical list, otherwise as a flat list
Item Attributes#
The item attributes are created dynamically. The system attributes and the customer-specific attributes that have been used at least once and are not empty are returned.
All item attributes are of type String
.