Data sources

To easily create charts and analyses with Power BI, it helps if the data to be analyzed is already available in a form that makes complex filtering and restructuring within Power BI unnecessary. Filtering can, for example, be offloaded to the data source and does not have to be reproduced in the analysis tool.

For these and other reasons, the Allegra Power BI connector offers a number of data sources for different kinds of reports and analyses.

Selecting a data source

When you open a new “Navigator” for the Allegra connector in Power BI, you are offered a number of data sources.

Select a suitable data source and configure it.

Power BI then takes 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 Table (item with children) and a new table with sub-items opens at the bottom.

Data sources for planning

These data sources offer easy access to the budget, the planned value, the remaining effort and the sum of the current efforts.

AccountingByFilter

This data source returns 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 as well.

AccountingByWorkspace

This data source returns 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 as well.

Accounting attributes

The following item attributes are available for Power BI:

"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 figure 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 return the individual bookings for the time and monetary efforts for the selected items.

ExpenseByFilter

This data source returns the efforts accrued for the items determined by the filter, optionally limited to a specific period.

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 onward

• dateTo: Date (format “day/month/year”) efforts are only considered up to this date

ExpensesByWorkspace

This data source returns the efforts accrued for the items in the listed workspaces, optionally limited to a specific period.

Parameters:

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

• dateFrom: Date (format “day/month/year”) efforts are only considered from this date onward

• 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 return a list of items.

ItemsByFilter

This data source returns a list of items that the specified filter lets 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 returns 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 are returned, as well as the customer-specific attributes that have been used at least once and are not empty.

All item attributes are of type String.