Leveraging your Data through Zaptic:
Every action you create, report you submit, status you change is something you should be able to access and use to drive insights and company improvement.
Alongside our in-house team of BI Experts our Self Serve Data Module can get your data in the hands of your BI teams and allow this information to be analyzed alongside data from other aspects of the business so you can create a full factory overview in 1 place.
Data Visualisation & Infrastructure:
Power BI is our data visualization tool of choice. We have Premium capacity Power BI Service, this is because of the package's ability to embed within Zaptic, ease of use, constant updates and a thriving online community.
**Our decision is also backed up by the recent Gartner Magic Quadrant for Analytics and Business Intelligence.
We can give you full access to workspaces behind the Zaptic instance allowing you to be fully autonomous in your dashboard creation within Zaptic while keeping the single pane of glass solution for the end user.
System Architecture:
This is a mock up of how Zaptic and Power BI are used together.
An end user submits a report which is pulled in to the PostgreSQL database, we then query this database to create dashboards in the Power BI service and Publish these into an online workspace.
This workspace is then embedded within Zaptic and can be opened through the Boards tab within the Portal.
Data Infrastructure:
We are fully hosted in AWS, for each Zaptic instance we have an AWS RDS which is a read replica of the production database (what you see in the portal).
☑️ Having this as a replica allows us to cleanse, query and build on this data without it affecting any of the other systems which run off the production database.
☑️ Another benefit to this replica is that it allows us to build stored procedures onto the database to pre-clean data ready for a dashboard e.g. Task Manager has its own table on the database but this isn't being used elsewhere in the product. We connect to these databases using PGadmin 4 as standard.
Within the database itself we have some pre-built materialised views and tables which we use to create our dashboards (e.g. an answers table, a units table etc.) these views and tables are constantly being refreshed using cron jobs meaning the data is updating on a loop over every few minutes keeping our database up to date without manual intervention or set refresh times allowing for real time data.
Important note: PostgreSQL doesn't have TEMPORARY functions to be used within queries.
☑️ Our Self Serve Data Module allows you to have access to this PostgreSQL database - allowing you to fully harness the power of this data and use this anywhere. We believe access to this information is vital for providing critical insights to drive organizational change in an increasingly data driven world which is why we don’t keep this data ‘stuck’ in the product.
RDS Instance:
The image shows how an instance’s database is setup:
Firstly we copy the Primary Production Database as a Read Replica and combine this with Auth credentials to create the RDS Slug which contains the data from the instance as well as a Postgres schema which contains the Cron jobs needed for refreshing the tables and materialized views.
Details on how Cron works can be found here.
BI Best Practices:
Limitations: Direct Query vs Import Mode
We aim to build all boards as Direct Query to allow for real time functionality of the boards as a lot of customers have boards on screens on the shopfloor where manual refreshing isn’t an option so we need to enable auto-refresh of the board without clicking off it every 15 seconds compared up to 8 refreshes a day (Premium capacities allow more) with import mode.
Power BI’s soft spot is the limitations when using Direct Query for a dataset. Direct Query has limited support for DAX and more meaning we configure within the query and keep the DAX to a minimum within the dashboard and aim to make stored procedures on the database for bigger queries and set this to refresh with a cron job then the refresh load is done on the database side not the Power BI side.
Each visualisation generates a select query to get the data it needs. If the dataset is kept simple then the sql command generated for the visualisation can be kept simple too. That in turn increases the possibility of the command being executable on Postgres, because PowerBI has the Microsoft SQL format as the basis.
Import mode can support up to a couple of billion rows of data depending on the size of each row but there are limitations on this as the database Power BI creates doesn’t have infinite memory.
Best Practice Sharing:
Our in-house BI Experts which includes previous experience working in manufacturing have created a catalog of ‘Standard Dashboards’ which combines industry and use case knowledge with collaborative efforts with our customers which has resulted in ready to deploy insight solutions which can be deployed from day 1 to minimize time to value for these use cases.
Since you have access to these dashboards through the Power BI service it will empower your BI teams to develop these solutions further or provide a starting point for their efforts.
This combined with our constant work to improve these dashboards further with our customers results in industry leading analytics.
Your Journey with the Self Serve Data Module:
-
- We will provide you a login to the database to allow you to access all the data.
- We will also run a training session detailing where the information lies within the database.
- We will also provide further documentation on this to get you fully setup with the database and getting your data into Power BI.
- Going forward if you have any questions about the data then the BI team will be available to assist through your CSM.
Contacting the BI team at Zaptic
When configuring your BI Boards you will work with one of our dedicated BI team members.
We requests that any queries, concerns or requests are requested by emailing support@zaptic.com where this will be triaged and actioned by our BI team.
We then respond to you and update you via your Support ticket.
