Exporting Business Intelligence data to a Microsoft Excel workbook
You can export Business Intelligence data from Asta Powerproject projects to an external spreadsheet or database, in a form that is optimised for convenient reporting using a reporting tool of your choice. Asta Powerproject includes a Business Intelligence export to Microsoft Excel® as standard, which means that you can use Excel's reporting tools to create unique reports on your data. More advanced users may want to download and install the Business Intelligence Controller, an application that enables you to specify the way in which you want data to be exported to an external database.
When you export Business Intelligence data to a Microsoft Excel workbook, the data is exported according to a Business Intelligence export model, which defines the information that is exported. The following Business Intelligence export models are supplied as standard:
- Full export - exports all data, including time-phased data that is of use to resource-focussed users; to be used in most circumstances.
- Large - exports all data, excluding time-phased data.
- Reduced - exports a subset of useful data.
- Earned value - exports data that is of use for earned value analysis.
- Timephased - exports data that is of use for resource-focussed users.
You can specify whether you want to exclude time-phased data values that are zero from the output. Excluding values that are zero results in a much smaller database table - time slice tables are the biggest contributor to data volume - which leads to cost and performance benefits. If data volume is not an issue for you, you may want to include zero values, as this will make graphing simpler in situations in which you want to show non-working days explicitly.
When you export Business Intelligence data, only data that your security group's access rights allow you to view are exported.
To export Business Intelligence data to a Microsoft Excel workbook:
- Click the Export BI command to access the Business Intelligence Export to Spreadsheet dialog. In order to access this dialog, you must have Microsoft Excel installed on your computer.
The Export BI command is not included on the Ribbon by default, so if you want to export Business Intelligence data to Excel, you must customise the Ribbon and add this command to it. - Select the Business Intelligence export model to apply to the data during the export in the Model for export field. You can choose from any of the *.compiled files that are located in either of the following folders:
- C:\ProgramData\Asta Powerproject\BIETL\.
- Your own \BIETL\ user folder.
- Click Browse to browse for the Microsoft Excel workbook into which to export the data. You can select an existing file, or enter the name of a new file.
- Use the Omit zero value time slice outputs check box to specify whether you want to exclude time-phased data values that are zero from the output, resulting in a much smaller database table.
- Specify what to do with any existing data in the selected Microsoft Excel workbook in the Existing data field. Select:
- "Delete all content" to remove all existing data from the workbook, leaving only any macros that may exist.
- "Erase current planning data only" to remove any data objects from the workbook that have the same internal ID as any data objects that are being exported, leaving all other data. Selecting this option enables you to update an existing workbook with any data that has been changed since the last export.
- "Leave" to leave all data as it is in the workbook. Selecting this option enables you to build up an historic record of exported data.
- Click Export to export the data to the selected Microsoft Excel workbook.
You can now report on the data within Microsoft Excel.
Understanding Business Intelligence data once exported to Microsoft Excel
The actual values you find in the Microsoft Excel workbook will depend on the project from which you have exported information.
The resulting Microsoft Excel workbook comprises a large number of tabs. Each tab represents a collection of data relating to a specific object type, or as a result of a TQL query. Each tab in the workbook corresponds to a table in a relational database. For example, the Bar tab contains information on bars within the project, with each row on the tab relating to a specific bar and each column relating to a property of that bar.
The first four columns help to identify the project and when it was exported. You will find this information defined on the planningData tab. This information is not particularly useful when exporting a single project, but it can be vital if you are exporting data from multiple projects to a database: you can use the PlanningDataID to group values belonging to the same project.
The fifth column, BaselineID, is the value for the current baseline at time of export. If a project has no baseline, this column will be blank.
All the remaining values are properties of the bar itself. Most of these values can be confirmed in Asta Powerproject by selecting a bar and viewing its properties dialog.
It is best to remove any unwanted columns from your Business Intelligence export model once you have identified those you need for your report.
If none of the Business Intelligence Export models that are supplied as standard meet your needs exactly, Elecosoft can tailor a model for you. Contact our Sales department for more information.
If you want more reporting power than is offered by exporting Business Intelligence information to Microsoft Excel, you can download and install the Business Intelligence Controller application, which enables you to export data to an external database. For more information on this, contact our Technical Support department.
Exporting Business Intelligence data is not available in Asta Powerproject SaaS.