An overview of the components and the architecture of Coins Analytics on Azure is shown below:

The Data Gateway is normally installed and deployed by the technical team at Access Coins.

Once Data Gateway is deployed, system parameters that control data extract process must be set. Many of the parameters related to this process are determined by the Technical Resource when the gateway is configured for the first time. However, there are a few parameters that need to be set based on individual business requirements. These parameters are:

Once the required system parameters are setup for applying Coins Analytics on Azure (SQL Replication), "Extracts" will become available on the menu.

All Entities (i.e., Tables) that are available to be extracted and loaded to SQL Database exist in this area. Entities are prefixed with a module code e.g., APVendor, JCJob.

It is possible to Generate extracts, build SQL tables and run extract data from Entity maintenance screen. Extracts can also be CDC enabled and / or CDC disabled in this page by running one of the functions available under "Generate Extract" which is pictured at the bottom right-hand corner of the screen. Once an entity is generated and/or its corresponding SQL table is built and/or CDC enabled, a tick in its column will appear.

For each Entity, columns are automatically added for "last extract", "last update" (in ERP+) and "when created" (in ERP+). Columns are also automatically created for "join ID fields". Column names generally follow Coins column names and are unique unless they are the same across multiple tables. Fields prefixed with x are considered custom, all others are standard. It is possible to suppress creation of a field or fields in the SQL table by ticking in Suppress column.

Relationships get created automatically using the Primary and Foreign keys in each Entity. Custom relationships can be created to join tables to other tables, this can be done based on suggested relationships. To create Custom relationships, the relationship ID must begin with an X.

The Diagram tab lists all the relationship diagrams for a given Entity.

Triggers for each Entity are defined to link the CDC trigger (Change Data Capture) with the Entity. The source table is essential for this link which is done automatically on creation.

Other Triggers should be defined and linked where other tables/records will affect the field value on this Entity. The Triggers should then be linked to the fields that they effect (this may be required in the future to provide field level data refresh).

The fields required from CDC trigger should match the key field selection criteria for the Entity. Typically, this will be the primary key fields.

Specific views for an Entity can be defined and created. Views can be subset of rows and/or subset of columns with a Condition that is a partial SQL query. Column labels can be overridden by specifying values in Fields Label Overrides.

When creating your own bespoke entities do not duplicate Label names. Label duplication will cause an error in Generating the Extract. All Labels must be discrete in an Entity.

Coins recommend any development and testing of custom entities happen in a non-live analytics environment first, once fully tested, these entities should be deployed to LIVE environment and become LIVE.

Once custom entities are ready to go live, they can be exported from an environment and then imported to the other environment. It is possible to select more than one entity, export them and then import them all to another environment.

This allows you to see columns across all Entities instead of going to each individual Entity.

Coins Data model can be visualised via Diagrams. This in turn allows data analyst to browse, navigate and discover Coins Data Dictionary and Meta data that describes the available Coins data.

Every Entity at least appears in one diagram and all diagrams can be maintained in this place. The description of the diagram can be used as a guide, and it shows what each diagram contains.

Defining Extract parameters enable you to customise the Data Model where it cannot be derived from existing parameters. It is particularly useful when the existing parameters are company specific, and the extracts need to be cross company.

Once Entities are tailored, the Data model must be generated/compiled by running "Generate Extracts". This in turn creates customized datasets and customized extract definitions.

Sometimes it is required to compile datasets after installing "Analytics' fixes", in order to force the new code to affect the system.

Running "Compile datasets" can be done from either:

or

When Entities are generated, corresponding Datasets and Extracts will be created. The extracts and datasets are named the same as the Entity with a prefix added, that is based on the value defined in the system parameter "EXPREFIX".

Various actions are available, to Build/Drop SQL tables, Extract Data to SQL and recreate views.

"Build SQL" action allows you to Create Tables, Drop Tables, Drop and Create Tables, and Rebuild Views.

The corresponding SQL tables will be created by running "Create tables", these will initially be blank tables. The initial load, i.e., running "Extract Data", must be performed to transfer existing data across to SQL and populate the newly created tables.

There is a trigger for each Entity that gets created automatically with a source table and its unique key fields. The Standard Triggers are all prefixed with %.

Real time replication of data to SQL happens by enabling CDC for extracts. By enabling CDC, making any change to a record in ERP+ will fire its trigger, and capture the changed record based on the fields defined in CDC. This in turn will send a MERGE SQL command via the extract agent/queue to update the record in SQL table. This in turn will enable SQL to update existing, insert new and delete records as appropriate.

This will allow user to extract data from Coins Database and load them to associated SQL Table.

BULK INSERT method is used for extracting tables with high volume of data from Progress Database to SQL database on Azure. When BULK INSERT is used, various bulk files will be created. These reside in the working directory of report agent, by default. This directory often is not accessible to the end user.

When transferring data to SQL fails due to data issues it is often useful to be able to inspect data files. For end users to be able to inspect these data files the Extract parameter "%SYBULKDIR" should be set.

It is important to consider the amount of disk space that will be required in this temporary directory. All the data for an extract will be dumped to text files and this may be many GB of data. If %SYBULKDIR is set, then this directory should have enough spare capacity to receive these files.

EXTRACT parameter %SYBULKDIR can be set to a specific directory where the bulk insert data files should be created. It is expected that this will be a user's home directory or another shared folder so that the files can be viewed and maintained in My Files function.

If a bulk insert works correctly then bulk files are created temporarily and are cleaned up on successful insert of the data to SQL. They are only left on UNIX if the process fails.

This is a new convenience report that will run the Extract Data report multiple times, once for each period in a range of periods. This is particularly useful if you have a history type extract and wish to populate several periods of historical data.

The Financial Date Extract Data report simply runs the Extract Data report multiple times by generating suitable entries in the report queue.

Coins Data Model can be visualised in this place, no editing is allowed.

Creation and maintenance of workspaces, publishing Power BI files to workspaces, viewing list of published reports and datasets can now be done from within ERP+ as well as power BI services.

Once a new workspace is created, the power BI files can be uploaded to the workspace from the Upload tab within the workspace. You can also load and replace an existing Power BI file and/or remove the published datasets and reports from a workspace.

The bulk data files created during running "Extract Data" are pushed to the BLOB storage container in Azure before being inserted to SQL. These files are temporary both on UNIX and blob storage and if the insert to SQL is successful then the BLOB files are removed from ERP+ and blob, at the end of the process.

If there is a failure, it is necessary to inspect the existence of files and/or the content of the files in BLOB storage. These files can be accesses via Azure portal with appropriate permissions for the user.

This screen allows the files to be listed, viewed (downloaded) and deleted from Blob container, from within ERP+ without the need to Azure portal credentials.

This place should be inspected periodically to tidy any files that might have been left around in the blob storage if running extract data failed. This can also be done from Azure portal by an administrator if required.

Occasionally it is required to administer the analytics extract process by running a query on SQL Database to check that the data has been transferred. Querying the SQL database can be done from within ERP+ without a need to connect to SQL Server management studio. This is helpful if a user does not have access to SQL server.

The query result can be exported to excel spreadsheet, should that be required.

Real time replication of data to SQL happens by an extract agent which can be monitored in "Extract Monitor" browser in ERP+. The extract agent/queue will be in one of the below colours:

If replication process fails, the agent will show in red, and a notification email will be sent to the email address that SY/EXMON is set to. At the event of replication failure all records related to that agent will queue up.

When Coins analytics is set up there will be one extract agent/queue that is used for real time replication of data. It is possible to set up more than one queue and use it for replication of certain extracts within a module, but it should be done within certain considerations.

There are six standard reports that can be run at any time.

Running this report, at the time of initial set up, will help to identify which parameters have the wrong values, if there are any problem in communication between ERP+ and Data Gateway.

This report highlights any discrepancies between count of data in Coins database and SQL database. It is recommended to schedule and run this report every night, out of working hours, to ensure data in both databases reconcile.

Once Analytics is enabled on the LIVE environment and all the required entities are set up for replication, it's strongly recommended to schedule this report to run daily.

To avoid any delays or processing issues, it's best to schedule it outside of regular business hours, when the Extract queue is likely to be clear.

Running this report regularly helps with ongoing monitoring and makes it easier to keep data reconciliation accurate and up to date.

New ERP+ programs have been introduced to run a query and aggregate the data in ERP+ tables as well as the SQL tables. The results can then be compared using program "syrex02.addcheck". This will enable users to check if the total value of data in both databases reconcile.

These programs should be saved in a calculation program and then be used in "Extract Query Reconciliation" report.

This report enables users to discover any missing data and discrepancy of two databases any time specially during analytics implementation when required.

This report runs a query on a selected table, groups and counts the records by the provided columns. The selected columns for grouping the records MUST be database fields and cannot be calculations in ERP+. You can specify as many columns as needed to give you the count analysis required.

The "companies" selection and the date selections should be filled in exactly as they are on the Extract data page.

This report shows each GL account's movement and balance, both in ERP+ and in SQL, for the selected period at run time. If there are any differences between ERP+ and SQL, it will highlight it with an asterisk for the account.

This report can be run either for all companies using the current GL period in each company, or for a single company and a range of periods.

This report calculates and compares Costs and Revenues of each contract both in ERP+ and in SQL, for the selected period at run time. It can also select to run only for Active/Incomplete contracts to reduce the amount of time taken to reconcile.

If there are any differences between ERP+ and SQL, it will highlight it with an asterisk for the contract.

The following steps should be taken to extract data from Coins on UNIX and load them to SQL database on Azure.

All required Tables/Entities must be generated first. This in turn will create corresponding Data sets and Extracts, but at this stage no data is loaded in SQL Database yet. It is important to Generate System Entities first before generating other Entities, this is to create required security, "%SYSecurity" and "%SYSecurityList", for generating the rest of the Entities.

These steps must be followed:

Once generating SY* tables are completed, repeat steps 1 to 3 above for generating the rest of the required tables.

All generated Entities have a corresponding Extracts that are available in "Extract Maintenance". Next step for loading data to SQL is to create and build tables in SQL database. it is important to build and extract system tables before the rest of the tables.

Once System records are extracted, repeat process 1 to 3 above for the rest of the tables that are required in SQL.

It is possible to drop SQL tables and views and rebuild them. To drop SQL tables, steps below should be followed:

It is sometimes required to keep history of data, e.g. snapshot of JCJobvalue per period. For this requirement we can define an extract to have "DayHistory / Week History / Period History" type and then Build SQL table. Running "Build SQL" will create two SQL tables which one of them will have suffix of "Hist" at its name.

Example: Running Build SQL for "JCJobVal" with type = "Period History" creates two tables, one is called "JCJobval", and the other one is called "JCJobValHist" table.

There are three distinct methods of refreshing tables in SQL after initial data load, Change Data Capture (CDC), Daily Extract and Scheduled Refresh.

Coins provides out of the box standard SureStart Power BI dashboards, which can be used as they are or can be adjusted based on individual business requirements and then used.

The standard dashboards are built upon standard augmented views that reside under the "Views" menu. Required views for each dashboard reside under a schema with the same name as dashboard name. These views are referred to as schema specific views; for example, "AsAtOpenItem" dashboard works on top of the views that are built under the "AsAtOpenItem" schema. Users can display and browse all the views that are used in each dashboard by filtering based on schema name.

There are some views that reside under the "Generic" schema. These views have been created as generic since they are used in multiple standard Power BI dashboards.

Users can browse the views' data in ERP+, by clicking on the "Editor" button that is available next to each view.

Standard Power BI dashboards that have been developed can be downloaded from Reports on the menu. These dashboards are built on standard views and can be used as they are. Alternatively, they can be used as a starting point and more measures can be added to them by a qualified Power BI developer.

Required dashboard specific views must have been created before the dashboard can be used.

A "Dedicated Capacity" in "PowerBI.com" is required to embed a Power BI report or dashboard to Coins. A workspace with a diamond next to it is considered as a workspace with dedicated capacity (). Once a Power BI dashboard is published to a dedicated workspace it can then be embedded to Coins.

Extract system parameters that control embedding reports and dashboards to Coins must be set. These parameters are:

PBIAPPID = This is the App ID defined for Embedding Power BI defined in Azure Active Directory. Format of this ID is xxxx-xxxx-xxxx-xxxxxxxxxxxx.

PBIAUTH = "https://login.microsoftonline.com/common/oauth2/authorize"

PBIAUTHEXP = This value is filled automatically when Power BI Embedded API authorizes with Azure Active Directory.

PBIAUTHHEAD = This is http header parameters that should be sent to the authorisation end point. It has got the fixed value of "-F redirect_uri=urn:ietf:wg:oauth:2.0:oob -F scope=openid -F grant_type=password"

PBIAUTHPWD (not in use any longer) = The Azure Active Directory password for the API user for embedded Power BI dashboards and reports.

PBIAUTHRES = The Power BI resource URL. Should be set to "https://analysis.windows.net/powerbi/api"

PBIAUTHURL = The Power BI access token URL. Should be set to "https://login.windows.net/common/oauth2/token"

PBIAUTHUSER = Set to the user ID of the API user that will embed Power BI to Coins. This user must be setup with the correct permissions to access Power BI.

This user must have a Power BI Pro licence assigned to it and must be an owner or member of the workspaces in Power BI that you wish to embed.

PBIURL = The URL of the Power BI API. Should be set to https://api.powerbi.com/v1.0/myorg/

After setting the required system parameters, to embed content in ERP+ a function should be created which uses page section %WSYPBI. This is to enable display of the embedded dashboard in a:

Function parameters must be set correctly:

Using workspace and report ID whilst less easy to use does hide information about the workspaces and reports from inquisitive (and technically competent) users.

The stn_code referenced in the above function needs to have a header program of "sywpbi.p" and a header form defined. A standard one of %WSYPBI is available to use.

Additional function parameters should be set to apply row level security to an embedded Power BI report.

The following Payroll (PR) entities are available and can be utilized based on the specific payroll setup—Weekly, Monthly, Bi-weekly (2-weekly), or Four-weekly cycles. These entities are designed to align with various payroll frequencies, allowing for more accurate and flexible payroll data processing.

It is recommended to use these new Payroll entities and for refreshing them client should use extract data instead of enabling CDC.

Once Payroll process is completed and the batches are posted, appropriate payroll entities should be extracted. A date range depending on the type of entity, e.g. weekly, monthly, etc. should be entered to extract data for these entities. Leaving the dates blank will result in extracting all data.

Entering a date range at the time of running extract process, first will retrieve the data for the specified date range from ERP+ database, then delete any existing records for the specified dates from SQL table and finally Insert the newly extracted data.

This ensures that the payroll data remains accurate and up to date for the selected period.

The result is a user-controlled, date-based extract that can be run after finishing each payroll process or, as and when it is needed, providing flexibility and control over the data extraction process.

When planning a refresh of a non-live environment that has Analytics enabled, please consider the following key steps:

A. Ensure the param.ini is updated for all non-live environment.

B. Non-Live environment has already analytics set up:

If a client does not take step B.1 above, the data in SQL database will not reconcile with data in ERP+. It will therefore require re-extraction of all entities to reconcile both databases.