Video tour

Hello. Welcome to a short overview of AVEVA BI Gateway.

AVEVA BI Gateway is a web application with which you can build a BI Gateway model.

This model is a formal representation of the data sources, dimensions, and measures that are collectively called as BI Gateway "objects."

The model also represents the relationships and configurations of these objects.

A BI Gateway model is agnostic to any database schema and is optimized for manufacturing intelligence.

Data sources are the external source systems to which you can map the dimensions and measure fields in the model.

At run time, the data from the external source systems populates the dimensions and measures as per the mappings.

A dimension provides contextual information to measures in the model.

Each dimension typically stores related information.

For example, a product dimension can store the product ID, product name, and product description.

A measure is a group of calculations that shares the same context.

It consists of calculations for each source system, time-slice defined by time-slicing dimensions and the measure period, and for

each unique combination of static dimension

context.

Typically, measures are metrics values computed from source systems and put into context with dimensions.

To know more about models, please read the AVEVA BI Gateway Help.

You can also go to the Video Tour page in the help for tutorial videos.

Thank you.

Welcome!

This is a short demo on the AVEVA BI Gateway model builder user interface (UI).

Use the floating action buttons to create a model and create objects such as:data sources, in which you can create queries for the model; dimensions, which filter the data into qualitative categories; and measures, in which you decide how often to pull data from a data source via the refresh rate and update period.

You can import an existing model in the JSON file format if you do not need to create model objects from scratch.

You can also export a model in the same JSON file format.

You can deploy and undeploy the different objects within the model.

You can either deploy or undeploy specific objects, or the whole model itself.

Under Settings, you can configure and modify log types.

Visit the Video Tour page in the BI Gateway online help for video tutorials on each model object and other key features and functions.

For more information, view the online help, which opens in a different browser tab.

The User Information icon allows you to view the name of the user currently logged on to the system, the tenant ID,

datastore information, whitelist client IP, and the Copyright and Legal information.

If you have access to multiple solutions, you can view the list of solutions and then switch to the required solution.

Lastly, you can sign out from the application.

This brings us to the end of the demo video.

Thank you.

Welcome! This is a short demo of the Data Source object within the AVEVA BI Gateway Model Builder.

BI Gateway retrieves data from external data sources and provides this data to the reporting, analysis, and other visualization tools at the enterprise level.

Data sources are the external source systems to which you can map the dimensions and measure fields in the BI Gateway model at run time.

Let us look at how to add a data source.

We will begin with a data source with a custom query that sustains static dimension population.

The main purpose is to retrieve the asset collection from Insight Asset Model, which is specifically called the Common Intelligent Model (CIM).

In detail, we are looking to retrieve the list of assets configured through the Insight MES User Interface which are used for tracking operational utilization and OEE.

Go to the floating action button and click Data Source.

A new data source appears.

Let us call this one 'Generic REST API’.

Select Generic REST API as the Data Adapter type, and Bearer Token as the authentication type.

Fill in the URL and Token fields.

You can retrieve this information from the Insight Solution.

The whole URL is split into two parts called the base URL and the ItemPath.

It is recommended to test the URL from an external tool such as Postman in order to properly address the different query parameters available as per the API endpoint.

Go to the Custom Queries tab and click the pencil icon, and then the + icon to create a custom query, which we will call ‘Asset'.

The query syntax is structured through four parts – the method, itempath, queryparameter, and APIType.

The adapter supports only the ‘get’ method.

Click the toggle button Preview Data to see the result set that the query returns.

As you can see, the data comes as a table result.

Let us now add a data source with a custom query that populates time-defining dimension.

The main purpose is to retrieve the utilization recorded as per each asset and subsequently populate the time-defining dimension with the event definition such as Asset ID, and utilization event starttime and endtime.

Once again, go to the floating action button, and click Data Source.

A new data source appears.

Let us call this one 'Insight'.

Select AVEVA Insight as the Data Adapter type.

Fill in the URL and Key fields.

You can retrieve this information from the Insight Solution.

In the Custom Queries tab, click the pencil icon and then the + icon to create a custom query 'UtilizationEvents'.

The query syntax is structured through few parts. Some parts are optional.

The query name should be configured with the API targeted endpoint.

The SelectColumn filter addresses the data source field that needs to be returned.

The Filter part conditions how to filter out the data.

The two parameters [RequestedStartTime] and [RequestedEndTime] within squared brackets are used as placeholders during execution for leveraging the incremental update mechanism.

It is recommended to filter the data from UtilizationEvents endpoint as per the LastModified datetime field.

Click the toggle button Preview Data to see the result set that the query returns. As you can see, the data comes as a table result.

Let us now add a custom query under the same data source 'Insight'.

This custom query supplies measure calculations.

The main purpose is to compute calculation leveraging Insight advanced retrieval mode, which means that is operated on the API side, such as the quantity of semi-finished goods measured by a flow meter.

The assigned tagnames should have a time-based engineering unit such as kg/h.

Click the pencil icon in the Custom Queries tab, and then the + icon.

Add the custom query ‘AnalogSummary’.

The query syntax is structured through two main parts: the main query and the nested query.

Let’s start with the nested query: It retrieves the eligible collection of tags

that match the filter condition.

Upon the Insight tagname configuration change, new configured tags are dynamically considered during the next execution.

The query name should be configured with the API targeted endpoint.

The SelectColumn filter addresses the data source field that needs to be returned.

The SelectColumn also supports field aliasing.

The Filter part conditions how to filter out the data.

The two parameters [RequestedStartTime] and [RequestedEndTime] within squared brackets are used as placeholders during execution for time slicing measure calculations.

You can preview your data by clicking on the Preview Data toggle button.

The query returns the integral calculation as per the list of tags within the specified StartDateTime and EndDateTime period.

Welcome! This is a short demo of the Dimensions object within the AVEVA BI Gateway Model Builder.

A dimension provides contextual information to measures in the model.

Each dimension typically stores related information.

In this video, we will look at how to create a basic or a static dimension.

Let us look at how to create a basic dimension.

Go to the Floating Action Button (FAB) and click Dimension.

A new dimension appears.

Enter the name and description.

Next, go to Fields and select the pencil icon.

This step is important as we are now adding a data source to the dimension.

Select the data source icon.

Search for the data source and click the checkbox.

The search result includes the custom queries within data sources and SQL table as well.

Select the required data source.

For this example, let us choose the Asset data source.

Click to select the Preview Source Data button.

The table set of the custom query appears.

Click OK.

The next step is to add fields to the dimension.

You can map the dimension fields to source data item fields.

You have to design the dimension by defining field name and map to the field name returned by the custom query selected.

As a best practice but still optional, it is recommended to keep the field name the same as the name provided from the custom query.

Define the fields that will become the primary key or contribute to a composite key, by clicking the toggle button.

The combination of the fields should contribute to the uniqueness of the record.

A combination of keys is called a composite key.

Once this is over, our Basic Dimension ‘Asset’ is created.

Since it is a basic dimension and there is no time-related information to the asset, you can select the appropriate refresh rate.

The options under the Advanced section are used for time-defining dimensions.

While you can use the Filter Expression option under the Filter section, it is less time-consuming to filter out data from the level of the data source into the custom query itself, rather than applying the filter on top of the whole result set returned by the data source, which subsequently eliminates more data.

The next step is to deploy the dimension.

The dimension has now changed from yellow to green which indicates that it is well configured, deployed and executed.

In this video, we will look at how to create a time-defining dimension, and then how to link two dimensions in a measure.

The initial dimension-creating process is the same as basic settings of the dimension:

Go to the Floating Action Button (FAB) and click Dimension.

Enter the name and description.

Go to Fields and select the pencil icon.

Then, select the data source icon.

Search for the data source and click the checkbox.

Select the required data source.

For this example, let us choose the Work Order data source.

Click to select the Preview Source Data button.

The table set of the custom query appears.

Click OK.

The next step is to add fields to the time-defining dimension.

The WorkOrderID custom query retrieves and returns records in an organized and timely manner, based on the StartTime and EndTime specified.

Hence, you will have to the make Plant, Asset, and also StartTime to contribute to the uniqueness of the composite keys.

Go to the Advanced section of the dimension and select the Time Slicing checkbox.

This is important, as the dimension is a time-contextual contributor to the measure and enforces the measure calculation to be time-sliced as per the work order events.

The Incremental Update is optional but recommended for a large data set.

You can backfill the dimension from a specified date called Data Collection Start.

In BI Gateway, you have to define the relationship between the time defining dimension and the Asset dimension under the Links section, if they have a common field that needs to be mapped from each other.

This step is particularly important to enable the ContextLink feature in a measure object.

To create a new link, go to the Links section of the dimension selected.

In this video, we are still at the WorkOrder_TimeDefiningDimension.

Click the plus icon and rename as required.

The default name contains ‘FK’ which means foreign key, which is the opposite of the primary key.

The Foreign key points to the destination dimension, which in this case is the Asset_Dimension.

Choose and verify the dimension fields.

The link should be well configured when you have to define the context of a measure.

Create a measure and go to the Context section.

Select the dimensions to define the measure context.

Click the toggle button to enable the link.

It is crucial to have earlier defined the dimension link before, as we did in this video.

This brings us to the end of our dimensions object demo video.

Thank you.

Hello.

Welcome to the demo of the Measures object within the AVEVA BI Gateway Model Builder.

A measure is a group of calculations that shares the same context.

It consists of calculations for each source system, time period, and for each unique combination of dimension value.

Measures are typically numerical values computed from source systems and put into context with dimensions.

You can configure measures by defining which calculations need to be computed, along with the context (dimensions) to which the calculations are related.

Go to the Floating Action Button (FAB) and click ‘Measure.’

Enter the name and description as required.

Under ‘Refresh Rate,’ define how the measure instance will be executed.

The refresh rate recomputes calculations for the current time-slice and also verifies whether a new time-slice must be defined.

For this example, I will set the refresh rate as one hour with 15-minute offset.

This means that from the top of the hour, the dimension objects are refreshed every 1 hour and 15 minutes, and the measure is refreshed after the completion of all the dimensions refresh.

It is important to delay the refresh by 15 or 20 minutes, otherwise the dimension execution refresh will overlap with the measure refresh, and may cause temporary issues within the data consistency standpoint.

Next, define the ‘Update Period’ as required.

In addition to the Refresh Rate, the Update Period defines how much data is updated in the measure table.

For example, if you configure a Measure object with a refresh rate of 15 minutes and an update period of 1 hour, every 15 minutes the BI Gateway services update the last hour of data in the measure table.

The Measure Period is the largest bucket of time that the system considers for slicing the data.

You can configure a measure period, which forces the computation of the measure calculations at the specified period.

For example, a measure object configured with a measure period of one hour stores calculations in the measure table for every hour since the data collection start.

From the Measure Period, you can see smaller time slices with a given hour because of the influence of the contextual time-defining dimensions.

The ‘Data collection start’ parameter under ‘Data Backfill’ is the datetime that you would like to compute or to perform a reverse backfill computation that will compute all the calculations.

Typically, measure tables are updated with time, adding records for every time-slice defined by time-slicing dimensions and the measure period. As such, measure tables are updated incrementally,from the time defined by the data collection start-time.

The data collection starts from reverse up to the point of the specified data collection start-time.

So, it starts at the most recent time slices sticking, near real time and then it goes in reverse to reach the specified date.

Once you save the general input, go to the ‘Context’ tab.

Here, we will add the dimensions to configure for the Measure’s calculation context.

Please note, you would have had to create a context link relationship within an asset model dimension and a time-defining dimension prior to this process.

Click the button ‘Add Dimension.’

Select the asset model dimension from the context link relationship that you created earlier.

Click ‘Add Dimension’ once again.

This time, select the time-defining dimension from the same context link relationship.

In this instance, I choose WorkOrder_Dimension.

You can edit the alias as required.

It is recommended to keep the Foreign Key name as simple as possible, like removing the number.

Click the context link toggle button next to your asset model dimension.

Once enabled, this feature restricts the influence of time-defining dimensions on only the asset populated within.

You will not be able to click the toggle button next to your dimension in some certain cases due to dimension configuration missing.

You can create a calculation on your own.

Click ‘Edit Calculations’.

Click the Add + button.

Enter the calculation name and select ‘Aggregate’ as the calculation type; the calculation type is ‘aggregate’ when the calculation belongs to the function that you use for aggregating the data.

So, you have to select a given custom query that provides the right information from the data adapter.

Click the pencil icon next to Source Data Items, to select the custom query that provides the required information from the data adapter.

In this example, I will select the Insight custom query 'EnergyElectricity'.

Click the checkbox and click the toggle button to preview the source data.

Then, fill in the following criteria in this order: Function,

Calculation field, Return type, and Period field.

The next step is to define the context relationship mapping with the calculation result set: Save your calculation.

This brings us to the end of the demo video for the Measures object in the AVEVA BI Gateway Model Builder.

Thank you.

Hello. Welcome to this demo video on AVEVA BI Gateway and Power BI.

This tutorial will show you how to configure a BI Gateway model database to the Microsoft Power BI application.

There are some prerequisites that must be in check before configuring your database to Power BI.

You should have created a tenant, assigned groups and roles, and whitelisted your domain.

You can visit the section ‘Report a Database Configuration’ within the AVEVA BI Gateway Online Help to learn more on each of these prerequisites.

We can now configure our BI Gateway model database to Power BI.

Within your model builder, go to the profile icon.

Select View Datastore Information.

The datastore server name and datastore name appear, which you will use to configure the database to Power BI.

Go to the Power BI Desktop application.

Next, select Get Data from the Home menu tab.

Then, under Azure, select Azure SQL Database OR Azure Synapse Analytical SQL.

Paste or enter the server name and datastore name that you had earlier saved.

Even though it says that the database name is ‘optional’, it is required to enter that information, as default databases don't exist within Azure SQL databases.

Click to select the Import option. This makes the dashboard design experience through Power BI more responsive because it preloads the data set, and then you can work with the data without having to permanently pull data from the database.

The Navigator opens with a checklist of all the objects in the database.

Retrieve all the object names that match your model.

Once you have selected the required object names, select Transform Data.

You will automatically be taken to the Power BI Query Editor page.

Here, we extract, transform and load data.

This is also commonly known as Data ETL.

You can rename the fields in the columns based on your requirement.

You can also remove certain fields that are not needed, as they cause visual clutter when you explore your database.

Select the column(s) you do not require, right-click and say Remove Column.

Once you have made the required changes, select Close and Apply.

You are taken back to the Power BI application main page.

Confirm by selecting Apply Changes. The removed objects are no more available in the Data panel.

The next step is important as we are going to link our objects to one another.

Under the Modeling menu tab, select Manage relationships.

Start with the measure; for each measure you have to link the appropriate dimensions.

Ensure that the Cardinality is Many to one. Many records in the measure can point to a single record in the dimension.

Repeat this for all other dimensions.

Once we have configured the data and defined the relationships between measure and dimension, we can visualize our data as required.

Please note, the following demonstration is only one way of data visualization, and it is not necessary to follow the exact steps.

Select Model View.

The visualization in the form of a star schema appears.

The measure is the pillar of the model, surrounded by all the static and time-defining dimensions of the model.

There are some parent-child relationships within the dimensions themselves.

Now, we will look at how to create an additional calculation within the Power BI Desktop itself.

Select Measure and right-click to select New Column.

I will call the new column ‘ProductionTotal’.

Enter the formula: ProductionTotal = ProductionQtyBad + ProductionQtyGood.

A new column is created under the Measure Object.

Please note: any additional calculations created in Power BI will not be reflected in the BI Gateway Model builder.

To look at another example, let's create a KPI.

EnergyIntensity = EnergyElectricity / ProductionTotal.

To save your file, save your file in the .pbix format.

You can publish your file against Power BI Services, which is the Cloud application of Power BI.

This is optional, and by publishing your file, it will be made available for anyone who needs to consume it.

Select Publish from the Home menu.

Select the destination.

The report and the dataset are both automatically published to the destination.

To connect to a published datasource from Power BI Services, select Get Data from the Home menu tab.

Then, under Microsoft Fabric, select Power BI Datasets.

Select the file from the list provided.

The dataset comes under the Data panel.

This chart is an example of data visualization available in Power BI.

Please note: the example is provided in dark mode.

Power BI can be accessed in light mode and dark mode.

You have the option to define the period start time and period end time and filter the data according to the criteria provided in the list.

Because of the well-defined relationships among the objects, it is easy to select and filter our data, and restrict the measure for more accurate visualization.

With that, we come to the end of the demo video.

Thank you.

Welcome to this demo video on AVEVA BI Gateway and Tableau.

This tutorial will show you how to configure a BI Gateway model database to the Tableau application.

There are some prerequisites that must be in check before configuring your database to Tableau.

You should have created a tenant, assigned groups and roles, whitelisted your domain, and, whitelisted your IP.

You can visit the section ‘Report a Database Configuration’ within the

AVEVA BI Gateway Online Help to learn more on each of these prerequisites.

To install Tableau Desktop, visit the official Tableau site www.tableau.com.

Log in with your credentials, or else create an account if you do not have one already.

Once you have created or logged into your account, you can download tableau onto your system.

We can now configure our BI Gateway model database to Tableau.

Within your BI Gateway model builder from AVEVA Connect, go to the profile icon.

Select 'View Datastore Information'.

The datastore server name and datastore name appear, which you will use to configure the database to Tableau.

Have these copied or saved in your system.

Go to the Tableau Desktop application.

From the Connect section, select an option under ‘To the Server’.

You can select Azure Synapse Analytics.

Paste or enter the server name and datastore name that you had earlier saved.

Under Authentication, select Active Directory Password.

Enter your username and password, and then sign in.

The next step is important as we are going to link our objects to one another.

As you can see on the screen, the list of objects is within the Data Table pane.

Start with the Measure; drag the measure object from the table list to the Tableau canvas.

Now, we’ll start to link our dimension objects to the measure.

For example, the asset dimension.

Drag the asset object to the canvas.

You need to link the appropriate foreign key to the Asset. Under this measure dropdown list, select 'FK Asset', and under asset, select 'aaID Asset'.

Search within the list for the objects that exactly match to the objects within your BI Gateway model builder, and repeat this for all other dimensions. Click on 'Update now' to review the data.

To organize the data source, click on Sheet 1.

You will automatically be taken to the Tableau workspace page.

You can rename the fields in the columns based on your requirements.

You can hide certain fields that are not needed, such as system fields, as they cause visual clutter when you explore your database.

You can also group by folder and functionally organize the different fields by domain of expertise with the appropriate operational naming that may differ from the model design naming.

This KPI is an example of a combination of fields to create a calculated field.

You can create calculated fields from existing objects as per your requirements.

Let’s now look at how to save and publish a Tableau Desktop File to Tableau Cloud.

Before publishing the file onto Tableau Cloud, extract the data.

Right-click at the datastore name and select 'Extract Data'.

Save the local .hyper file onto your system.

Next, go to the Server menu option and select 'Sign in' if you haven't already.

Once you are signed in, you can publish the datasource. Server > Publish Datasource > datastore.

Select the location wherein you want to publish the tableau file.

Edit to allow for refresh access. This means that Tableau Cloud can invoke the datasource, retrieve the data, and regularly refresh the data to keep the datasource within Tableau Cloud up to date.

Click Publish.

You are taken to Tableau Cloud. You can schedule the refresh or close the pop-up based on your requirements.

To connect to a Published Datasource from Tableau Cloud, select 'Tableau Server' from the Connect tab within the desktop application and select the required datasource from the list provided.

Once you have published the datasource against Tableau Cloud, you can launch it on the desktop application and start to design your dashboard.

This dashboard is an example of the Energy Management System.

You can navigate within the dashboard to filter through and analyze the dimension objects.

Because of the well-defined relationships among the objects, it is easy to select and filter out data, and restrict the measure for more accurate visualization.

This brings us to the end of the demo video.

Thank you.