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.