Data Sources#

To easily create charts and analyses with Power BI, it is helpful if the data to be analyzed is already available in a form that makes complex filtering and restructuring within Power BI unnecessary. Filtering can thus, for example be outsourced to the data source and do not have 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.

Select Data Source#

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

_images/powerBI_navigator.png

Select a suitable data source and configure it.

_images/powerBI_nav_itemsByFilter.png

Power Bi will then take you to the Data View.

_images/powerBI_itemsByFilter_GridView.png

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

_images/powerBI_transformData.png

Set Hierarchical to TRUE.

_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

Data Sources for Planning#

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

AccountingByFilter#

This data source provides the planning data for all items that match the selected selected query filter.

_images/powerBI_AccountingByFilter.png

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 are not automatically selected.

AccountingByWorkspace#

This data source provides the planning data for all tems in the selected workspaces.

_images/powerBI_AccountingByWorkspace.png

Parameters:

  • workspace: List<int>, multiple value, mandatory; list with workspace id’s

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

_images/Allegra_worklog_tab.png

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

Allegra

Power BI

  1. Total actual

Work

totalExpenseTime

totalExpenseTimeUnit

totalExpenseTimeUnitLabel

Cost

totalExpenseCost

  1. Budget

Work

budgetTime

budgetTimeUnit

budgetTimeUnitLabel

Cost

budgetCost

  1. Planned value (PV)

Work

totalPlanTime

totalPlanTimeUnit

totalPlanTimeUnitLabel

Cost

totalPlanCost

  1. Estimated remaining effort

Work

remainingTime

remainingTimeUnit

remainingTimeUnitLabel

Cost

remainingCost

Data Sources for Expenses#

These data sources provide the individual postings for the selected transactions for the time and monetary expenditures.

ExpenseByFilter#

This data source provides the accrued expenses for the items determined by the filter, optionally limited to a certain period of time.

_images/powerBI_ExpensesByFilter.png

Parameters:

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

  • dateFrom: Date ( format “day/month/year”) Expenses are only taken into account from this date

  • dateTo: Date ( format “day/month/year”) Expenses are only considered until this date

ExpensesByWorkspace#

This data source provides the expenses incurred for the items in the listed workspaces, optionally limited for a certain period of time.

_images/powerBI_ExpensesByWorkspace.png

Parameter:

  • workspace: List<int>, multiple value, mandatory; list with workspacce id’s

  • dateFrom: Date ( format “day/month/year”) Expenses are only taken into account from this date

  • dateTo: Date ( format “day/month/year”) Expenses are only considered until this date

Attributes for Expenses#

"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 passes.

_images/powerBI_ItemsByFilter.png

Parameters:

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

  • hierarchical: boolean (True/False), optional; if True the result will

    be returned as a hierarchical list, otherwise as a flat list.

ItemsByWorkspace#

This data source provides all items from the specified workspaces.

_images/powerBI_ItemsByWorkspace.png

Parameters:

  • workspace: List<int>, multiple value, mandatory; Liste mit Bereichs-IDs

  • hierarchical: boolean (True/False), optional; if True the result will

    be 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 non-empty custom attributes used at least once.

All item attributes are of type String.