BankBI Data Flow
Step 1: Update the excel file: “ManAccs_MASTER”
The file is populated from the Excel Power Pivot that contains tables of data downloaded from BankBI.
There are two parts to the first step in this process:
a) Download the latest data from BankBI into the PowerPivot Model
b) ‘refresh’ to update the spreadsheets within the file.
Overview of the Master file:
The ‘BankBI_Source_DATA’ sheet is populated from the Excel Power Pivot that contains tables of data downloaded from the BankBI Web Application
The remaining sheets in the file are used in the formulae within the ‘BankBI_Source_DATA’ sheet.
To view the existing tables in the PowerPivot file
By default this will display the tables in the ‘Data View, see below.
The tables that are imported from the BankBI Web Application are viewable on separate sheets within the data view.
BankBI WebApp tables
Branches - This table will only require updating if a new branch is opened
CodCodes - This table maps the General Ledger lines (Balance Sheet and Profit & Loss) from the core system into BankBI Balance Sheet and Profit and Loss lines. It also enables a Bank defined GL structure(s) to be loaded and used in reporting i.e. the ‘Codes’ table
Companies – The requirement to load this file should only be necessary during the initial BankBI set up Data Dictionary
Cost Centres - The requirement to load this file should be infrequent, only necessary when a new cost centre is opened
LanDataDictionary - This table controls the names for all GL lines and report labels and will be set according to specific Bank requirements.
MeasureResults – This table holds all system generated results Actuals, Budgets and Adjustments that then map into the various excel reports. This table contains data for Published dates
System Values - The requirement to load this file should only be necessary during the initial BankBI set up
MeasureMonthsPublished – Published data dates
MeasureMonthsUnPublished – Unpublished data dates (for information only)
UsrUsers – not used
To view the existing tables in the ‘Diagram View’
The PowerPivot ‘Diagram view’ enables you to view the same Tables and any table joins that have been created.
Joins have been created using fields that hold common data
If you click on a ‘join’ between two tables, the common fields used in the join are highlighted. In the above example it shows the fields being used in the join between the CodCodes and the MeasureResults tables.
To view the existing connections in the PowerPivot Model
Soon to be new connections to ‘csv’ files from BankBI
To download the latest data from BankBI into the PowerPivot Model (‘Data refresh’):
This will update the ‘ManAccs_MASTER’ file with the latest ‘Published’ data from the Web Application.
- Open the PowerPivot Window from the excel ribbon.
- From the ‘Home’ menu, select ‘Refresh’ and then ‘Refresh All’
- The screen will then display all the tables that will update as part of this refresh.
- If you are asked for password authorisation, enter your BankBI username and password.
The tables should then start to refresh.
This could take some time depending on the speed of the connection, PC etc.
- Once complete, if possible, SAVE THE FILE.
To update the spreadsheets within the file:
This step is required in order that the newly imported data above is reflected in the main excel file.
This may start automatically once the ‘data refresh’ has completed.
If not, you can refresh this manually from the main Excel window by selecting ‘Data’ from the ribbon, then ‘Refresh All’
When the data is refreshing you will see lots of messages at the bottom of the screen (in the green bar)
Once the Update has completed you should see the data has refreshed in file.
E.g. ‘BankBI_Source_DATA’ sheet is displaying data and cell F3 has updated to the required reporting month. SAVE THE FILE!
Step 2: Update the excel file "ManAccs_PACK"
To update the data links in this file to the latest excel Master file refreshed in Step 1:
From the excel ribbon select, ‘Data’ and then ‘Edit Links’
The screen will display the MASTER file that is currently linked to the ManAccs_PACK file. Select ‘Change Source’ to link to the new MASTER file for the correct date
Then ‘browse’ to find the new source, click on it and then click ‘OK’. The file should then update.
Review the updated Reports in the file
- There are currently 21 report pages in the pack.
- The ‘Financial Position and Earnings’ and ‘Performance Ratios’ reports are using a “linked picture” from a hidden sheet. Therefore if any changes are required to these reports the changes will need to be made on the hidden sheet.
- For all other reports there is no hidden sheet.
- Each report has a number of hidden rows. You can amend the data appearing on each report by hiding or un-hiding rows as required.
- If a required analysis is not available once rows are unhidden then you can insert a row in the Report where required and copy the formulae from another similar row (e.g. the one above). Then amend the code in column A so that it relates to the BankBI code for the new row. The formulae should update to display the values for the new row.
- If #N/A appears then the BankBI code is not present in the Master file on the ‘BankBI_Source_DATA’ sheet. Again, the BankBI code should be added into the ‘BankBI_Source_DATA’ sheet by inserting a row and copying the formulae from the row above (first identify the section on the ‘BankBI_Source_DATA’ sheet that relates to the ‘type’ of code you are adding - by searching the ‘BankBI_Source_DATA’ sheet for similar codes currently on the Report – e.g. if adding #010401010R then search for where existing rows on the report appear i.e. #010401009R).
- The print settings for each page control how each Report will appear once the file is saved as a PDF. To view these select ‘File’ and then ‘Print’ from the main excel ribbon. The Page numbers are also in the Footer of the print settings.
Step 3: Save the “ManAccs_PACK” (pdf)
The final step is to save the Excel Pack as a ‘PDF’ document.
Once the Excel pack (Step 2 above) has been reviewed and is deemed ready for distribution:
Hide all of the Sheets in the excel file that are not required for the PDF version of the pack (eg. the ‘Control’ sheet).
Select ‘Save As’ and then amend the ‘Save as type’ to select PDF.
Then from Options select ‘Entire workbook’, and ‘OK’
The pack should then save as a PDF
and be ready for final review and distribution.