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.

Select a suitable data source and configure it.

Power BI will then lead you to the Data View.

You can also display the data hierarchy. Go to TransformData > Recent sources > Last used

Set Hierarchical to TRUE

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.

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.

Parameters:

• filterID: String, single value, mandatory; one of the saved query filters

• excludeParentItems: boolean (true/false), optional, default false;

  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.

Parameters:

• workspace: List<int>, multiple value, mandatory; list of workspace IDs

• excludeParentItems: boolean (true/false), optional, default false;

  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.

The following table describes the attribute mapping between Allegra and the Power BI data source.

Allegra		Power BI
Total actual	Work	totalExpenseTime	totalExpenseTimeUnit	totalExpenseTimeUnitLabel
	Cost	totalExpenseCost
Budget	Work	budgetTime	budgetTimeUnit	budgetTimeUnitLabel
	Cost	budgetCost
Planned value (PV)	Work	totalPlanTime	totalPlanTimeUnit	totalPlanTimeUnitLabel
	Cost	totalPlanCost
Estimated remaining effort	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.

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.

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.

Parameters:

• filterID: String, single value, mandatory; one of the saved query filters

• hierarchical: boolean (True/False), optional; if True the result is returned as

  a hierarchical list, otherwise as a flat list

ItemsByWorkspace

This data source provides all items from the specified workspaces.

Parameters:

• workspace: List<int>, multiple value, mandatory; list of workspace IDs

• hierarchical: boolean (True/False), optional; if true the result is returned as

  a 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.