Metabase Real Time Dashboard

 admin



  1. Metabase Real Time Dashboard Download
  2. Metabase Real Time Dashboard Sign In
  3. Metabase Real Time Dashboard Login
  4. Metabase Real Time Dashboard
  5. Metabase Real Time Dashboard Login
-->

Set up the server, and install and configure the software prerequisites.

SourceForge Open Source Mirror Directory. Since the founding of SourceForge in 1999, a major focus has been the long-term preservation of access to Open Source software - enabling long-term maintenance, code reuse by developers, and preservation of prior art. Sep 23, 2020 Recently joined as a part of Google Cloud Platform, Looker is a powerful BI tool that provides an innovative approach for real-time data exploration and analytics. Looker has powerful dashboard capabilities that can cover most data discovery use cases. (Please note the dashboard relies on it to get some stats, so you may need to edit the JSON if your service name is different) Here is the Prometheus config snippet I use (8080 is the web port traefik is listening, can be configured by adding -web.address=:8080 to traefik). Paychex is using Metabase to bring greater visibility to their software development life cycle (SLDC) phases with near real-time dashboards. They view Metabase being open source as a plus for its customizability and low barrier to entry. Common uses are: Keeping track of build/test/deploy and quality status in real-time. How “real-time” do you need the dashboard to be? Lots of dashboard providers claim to be “real-time,” but most aren’t in the true sense of the word (updating when your data updates). And most of them that are tend to be quite costly. Do you need your dashboard to be updating minute-by-minute? Every time you sign-on? Every few hours?

Join the Administrators Group

To install and configure BizTalk Server, sign in to the server using an administrator account on the local computer. Add any user accounts that are administering the BizTalk Server to the local Administrators group:

  1. In the Start menu, open Computer Management.

    • Or, open Administrative Tools, and then select Computer Management.
    • Or, open Server Manager, select Tools, and then select Computer Management.

  2. Expand Local Users and Groups, and select Groups.

  3. Right-click the Administrators group, and select Add to Group. Add your accounts, and select OK to save your changes.

Change the computer name (optional)

If your computer name is longer than 15 characters, then BizTalk Server configuration fails. To change the computer name to less than 15 characters:

  1. In Server Manager > Dashboard, select Local Server.
  2. In Properties, select the Computer name property to change it.
  3. Restart the computer.

SEE ALSO : Windows PowerShell Rename-Computer

Enable Network DTC Access

Time

If BizTalk and SQL Server are installed on separate computers, then enable Network DTC Access on the BizTalk Server and the SQL Server.

  1. In the Start menu, open 'dcomcnfg'.

    • Or, open Administrative Tools, and then select Component Services.
    • Or, open Server Manager, select Tools, and then select Component Services.

  2. Expand Component Services, expand Computers, expand My Computer, and expand Distributed Transaction Coordinator.

  3. Right-click Local DTC, and select Properties.

  4. Go to the Security tab, and check the following:

    • Network DTC Access
    • Allow Inbound
    • Allow Outbound
    • No Authentication Required

  5. Select OK. If prompted to restart MS DTC, select Yes.

For additional settings that may be needed, see Troubleshooting Problems with MSDTC.

Configure Application Event Log (optional)

BizTalk Server setup keeps a record of events in the Application Event Log. Depending on the BizTalk Server features installed, the amount of space required in the log may exceed its limit. If the application event log runs out of space during the setup, the installation fails. Changing the Application Event Log settings prevents this failure.

  1. In the Start menu, open Event Viewer:

    • Or, open Administrative Tools, and then select Event Viewer.
    • Or, open Server Manager, select Tools, and then select Event Viewer.

  2. Expand Windows Logs, right-click Application, and then select Properties.

  3. To determine the available space, compare the Log Size and the Maximum log size properties.

    • To add space, enter a higher number in Maximum log size.
    • To enable overwriting of old events when the log becomes full, select Overwrite events as needed.
    • To clear the log events, select Clear log.

  4. Select OK.

Install Windows Updates

Be sure to install the latest critical Windows updates.

  1. On the Start menu, open Windows Updates, and check for updates. You can also open Settings, and select Update and security.
  2. After installing updates, you may need to restart the computer.

Install Visual C++ 2015-2019 redistributable package

Download and install the Visual C++ 2015-2019 redistributable package - x86 and Visual C++ 2015-2019 redistributable package - x64.

The Visual C++ downloads lists all the available versions.

Install Microsoft OLE DB Driver for SQL Server

Download and install the Microsoft OLE DB Driver for SQL Server.

Enable IIS

BizTalk Server requires IIS for the following features:

  • HTTP adapter
  • SOAP adapter
  • Windows SharePoint Services adapter
  • Secure Sockets Layer (SSL) encryption
  • Management REST API
  • BAM Portal
  • EDI

IIS is included with the operating system as a Role or a Feature, depending on the OS. To install:

  1. In the Start menu, open Turn Windows Features on or off. Or, open Server Manager, select Manage, and then select Add roles and features.

  2. Select Internet Information Services or Web Server (IIS). In addition to the default checked options, also select the following:

    Windows 10

    • In Web Management Tools, also check:

      • IIS 6 Management Compatibility
      • IIS 6 Management Console
      • IIS 6 Scripting Tools (Installs adsutil.vbs)
      • IIS Metabase and IIS 6 configuration compatibility
      • IIS Management Console

    • In World Wide Web Services, expand Security and also check:

      • Basic Authentication
      • Windows Authentication

      Windows Server

    • In Security, also check:

      • Basic Authentication
      • Windows Authentication

    • In Management Tools, also check:

      • IIS Management Console
      • IIS 6 Management Compatibility
      • IIS 6 Metabase compatibility
      • IIS 6 Management Console
      • IIS 6 Scripting Tools (Installs adsutil.vbs)

  3. Continue with the installation, and restart the computer if prompted.

Run 64-bit BAM portal (optional)

If you don't use the BAM portal, then you can skip this section.

The BAM Portal runs in 32-bit mode. If you are using Internet Information Services (IIS) in a 64-bit environment, then set the application pool to run in 32-bit mode.

Using adsutil.vbs

  1. Open a command prompt as administrator.
  2. In the command prompt, type:
    cscript c:inetpubadminscriptsadsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
  3. Select Enter.

Using IIS Manager

  1. In the Start menu, open 'inetmgr'.
  2. Expand the computer name, and select Application Pools.
  3. Right-click DefaultAppPool, and select Advanced Settings.
  4. Change the value of Enable 32-bit Applications to True.
  5. Select OK.

Install Windows Identity Foundation (WIF) (optional)

If you use the SharePoint Services adapter, BizTalk Server requires WIF. If you don't use the SharePoint Services adapter, you can skip this section.

Windows Identity Foundation is included with the operating system as a Feature.

  1. In the Start menu, open Turn Windows Features on or off. Or, open Server Manager, select Manage, and then select Add roles and features.
  2. Select Windows Identity Foundation 3.5, and continue with the installation.
  3. Restart the computer if prompted.

Install SQL Server 2016 Analysis Services ADOMD.NET

Download and install x86 and x64 SQL_AS_ADOMD from Microsoft SQL Server 2016 Feature Pack download.

Install & configure SMTP Server (optional)

If you use BAM Alerts, BizTalk Server requires SMTP Server. If you don't use BAM Alerts, you can skip this section.

SQL Server Database Mail uses an SMTP Server to send BAM Alerts. SMTP Server can be installed locally on the BizTalk Server or on another server with IIS installed. SMTP Server is not available on client operating systems, such as Windows 8.1 or Windows 10.

SMTP Server is included with server operating systems as a Feature.

  1. In the Start menu, open Turn Windows Features on or off. Or, open Server Manager, select Manage, and then select Add roles and features.
  2. Select SMTP Server, and continue with the installation.
  3. Restart the computer if prompted.

Install Excel 2019 or 2016 (optional)

If you use Business Activity Monitoring (BAM), BizTalk Server requires Excel. If you don't use BAM, you can skip this section.

The BAM Office Excel Workbook defines the business processes you want to monitor. You also use the BAM Excel Workbook to define the way business users see the data collected by BAM.

Important

  • BizTalk Server supports only 32-bit versions of Microsoft Office.
  • To successfully load BAM.xla into Excel, install Visual Basic for Applications (under Office Shared Features). Otherwise, you may get error: This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.

Install Visual Studio 2019 (optional)

BizTalk Server requires Visual Studio to create BizTalk projects using the development tools. If this is a staging or production server, or you're not doing any BizTalk development, then skip this section.

Visual Studio Enterprise (recommended) and Professional editions are supported. Visual Studio Community edition isn't supported.

  1. Run the Visual Studio setup as Administrator.
  2. Select a Default installation. BizTalk Server does not require any of the optional features.
  3. Continue with the installation, and restart your computer if prompted.

SEE ALSO : Installing Visual Studio 2019

Important

  • The BizTalk Server development tools are based on Visual Studio. At a minimum, install the .NET desktop development workload before you install the BizTalk Server Developer Tools/SDK and BizTalk Server extension.
  • The BizTalk Server runtime requires .NET Framework 4.7.2 or higher.

Install SQL Server

BizTalk Server requires SQL Server. SQL Server can be installed on the same computer as BizTalk, or on a different computer. Most production environments install BizTalk and SQL on separate servers.

Important

  • SQL Server Express Edition is not recommended or supported. The Express edition does not include certain features needed by BizTalk Server.
  • BizTalk Server supports SQL Standard Edition. However, to use Business Activity Monitoring real-time aggregation (BAM RTA), install SQL Server Enterprise Edition. BAM real-time aggregation (RTA) is not supported in the Standard Edition of SQL Server.
  • BizTalk Server supports all case-sensitive and case-insensitive SQL Server collations except for binary collations. Binary collations are not supported.

For specific install steps, seeInstall SQL Server.

  1. Start the SQL Server installation.

  2. During the Feature setup, select the following:

    • Database Engine Services
    • Analysis Services
    • Shared Features
      • Client Tools Connectivity
      • Integration Services

  3. Configure Analysis Services in Multidimensional Mode. Default is Tabular Mode, make sure to change it to Multidimensional Mode in SQL Server installation wizard before continuing with installation.

  4. Continue with the installation, and restart the computer if prompted.

Disable Shared Memory

  1. Open SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then select Protocols for MSSQLSERVER.
  3. Right-click Shared Memory, and then select Disable.
  4. Select SQL Server Services, right-click SQL Server (MSSQLSERVER), and then select Stop. After the service has stopped, right-click SQL Server (MSSQLSERVER), and then select Start.
  5. Close SQL Server Configuration Manager.

Typically, the Shared Memory protocol only impacts clients (BizTalk Server) that are installed on the same computer as SQL Server. Under certain stress conditions (such as clients accessing SQL Server from the same computer), the SQL Server Shared Memory protocol may lower BizTalk Server performance. Disabling the Shared Memory network protocol resolves this.

Configure SQL Database Mail (optional)

If you use BAM Alerts, BizTalk Server requires SQL Server Database Mail. If you don't use BAM Alerts, then skip this section.

SEE ALSO : More on Database Mail.

Important

  • You need to know the server name and TCP port number for the SMTP Server. If you installed IIS and SMTP Server on this same computer, then you use the local SMTP Server. If the SMTP server requires authentication, then have the user name and password ready.
  • The BAM portal and BAM Alerts are separate features. If you are using BAM Alerts, then SQL Server Database Mail is required. If you are not using BAM Alerts, then SQL Server Database Mail is not required.

For specific configuration steps, see: Configure SQL Server Database Mail.

To send a test email:

  1. Right-click Database Mail, and select Send Test E-Mail.
  2. Enter a To: email address, and select Send Test E-Mail.

If the To: recipient receives the email, then Database Mail is configured.

Create the SSIS Catalog (optional)

If you use BAM, BizTalk Server requires SSIS Catalog to be created. If you don't use BAM, then skip this section.

SEE ALSO : More on SSIS Catalog.

For specific steps to create the SSIS Catalog, see: Create the SSIS Catalog.

Install WinSCP (optional)

Required by the SFTP adapter. If you don't use the SFTP adapter, then skip this section.

Download and install WinSCP.

Next step

Install BizTalk Server 2020.

When it comes to dashboard performance, there are essentially four ways to get your dashboards to load faster:

  • Ask for less data.
  • Cache answers to questions.
  • Organize data to anticipate common questions.
  • Ask efficient questions.

What follows is some general guidance for how to get your dashboards to load faster, and the bulk of this guidance will focus on that third bullet, or how you can organize data to anticipate the most common questions that data will be used to answer. We’ll cover how to ask more efficient questions (i.e., SQL best practices) in an upcoming article.

And, of course, the usual caveats around premature optimization apply. The advice here assumes that you have been exploring your data for some time, and are deriving material benefits from the insight that data yields. Meaning: your queries are already helping you make better decisions. Only then should you be asking: how do I get this dashboard to load faster?

Ask for less data

This point is almost too obvious that it often goes overlooked, but it should be the first place to start. Do you actually need the data you’re querying? And even if you do need all that data, how often do you need it?

You can save a lot of time simply by restricting the data you query, such as by including adding a default filter on a dashboard. And be especially mindful of data spanning time and space. Do you really need to look at the last quarter’s worth of data every day? Or do you really need every transaction for every country?

And even if you do need to know that information, do you need it every day? Could you relocate that question to another dashboard that’s typically only reviewed weekly or monthly?

We should be open to all our data when we’re exploring our datasets, but once we settle on the kinds of decisions our organization needs to make — and the data we need to inform those decisions — we should be ruthless about excluding data that does not significantly improve our analysis.

Cache answers to questions

You don’t need to wait for data if it’s already loaded. Admins can set up Metabase to cache query results, which will store answers to questions. If you have a set of dashboard that everyone runs when they open up their computers first thing in the morning, run that dashboard ahead of time, and the questions in that dashboard will use the saved results for subsequent runs to load in seconds. People will have the option to refresh the data, but typically this is unnecessary, as most often people will only need to review data from the previous day and before.

You can configure Caching in the Settings tab of the Admin panel. These settings allow you to configure the minimum query duration to cache (so you only cache long-running queries), the Cache Time-to-live (TTL) Multiplier, (to specify how long the cache should stick around), and the Max Cache Entry Size (so you can set an upper limit on the amount of data you cache).

You can use Metabase’s auditing tools to determine when people typically run various questions, then create a script using Metabase’s API to programmatically run these questions (thereby caching their results) ahead of time, so that when people log in and navigate to their dashboards, the results load in seconds. Even without taking that extra “pre-warming” step, when your first person loads that slow query, it’ll be cached for the rest of your folks.

Organize data to anticipate common questions

The next best thing you can do is organize your data in such a way that it anticipates the questions that will be asked, which will make it easier for your database to retrieve that data.

  • Index frequently queried columns.
  • Replicate your database.
  • Denormalize data.
  • Materialize views: create new tables to store query results.
  • Aggregate data ahead of time with summary tables.
  • Pull data out of JSON and slot its keys into columns.
  • Consider a database specific to analytics.

All but that last section below assumes you are using a traditional relational database like PostgreSQL or MySQL. That last section is about moving to a completely different type of database tuned specifically to handle analytics, and should be your last resort, especially for startups.

Index frequently queried columns

Adding indexes to your database can significantly improve query performance. But just as it wouldn’t make sense to index everything in a book, indexes do incur some overhead, so they should be used strategically.

How to use indexes strategically? Find your most queried tables, and the most commonly queried columns in those tables. You could consult your individual database to get this metadata. For example, PostgreSQL offers metadata on query numbers and performance via its pg_stat_statements module.

Remember to do the simple work of asking your Metabase users which questions and dashboards are important to them, and if they’re experiencing any “slowness” as well. Fields that most often require indexing are either time-based or id-based – think timestamps on event data, or IDs on categorical data.

Alternatively, you can use Metabase’s auditing functionality in the Enterprise Edition, which makes it easy to see who is running which queries, how often, and how long those queries took to return records.

Once you’ve identified the tables and columns you’d like to index, consult your database’s documentation to learn how to set up indexes (for example, here’s indexing in PostgreSQL).

Indexes are easy to set up (and take down). Here’s the basic format for a CREATE INDEX statement:

For example:

Experiment with indexing to see how you can improve query performance. If your users are commonly using multiple filters on a single table, investigate using compound indexes.

Replicate your database

If you are using a database for handling both operations (e.g., app transactions like placing orders, updating profile information, etc.) as well as for analytics (e.g., for queries that power Metabase dashboards), consider creating a replica of that production database for use as an analytics-only database. Connect Metabase to that replica, update the replica each night, and let your analysts query away. Analysts’ long-running queries won’t interfere with the day-to-day operations of your production database, and vice versa.

Outside of making your dashboards faster, keeping a replica database for data analytics is a good practice to follow to avoid potentially long-running analytical queries impacting your production environment.

Denormalize data

In some cases, it might make sense to denormalize some of your tables (i.e., to combine multiple tables into a larger table with more columns). You’ll end up storing some redundant data (such as including user information each time the user places an order), but analysts won’t have to join multiple tables to get the data they need to answer their questions.

Clock

Materialize views: create new tables to store query results

With materialized views, you’ll keep your raw, denormalized data in their tables, and create new tables (typically during off hours) to store query results that combine data from multiple tables in a way that anticipates the questions analysts will ask.

For example, you might store order and product information in different tables. You could, once a night, create (or update) a materialized view that combines the most frequently queried columns from both of those tables, and connect that materialized view to your questions in Metabase. If you’re using a database for both production and analytics, in addition to eliminating the joining process needed to combine that data, your queries won’t have to compete with production reads and writes on those tables.

The difference between a materialized view and a Common Table Expression (CTE, sometimes called a view), is that the materialized view stores its results in the database (and therefore can be indexed). CTEs are essentially subqueries, and are computed each time. They may be cached, but they are not stored in the database.

Materialized views will, however, consume resources in your database, and you will have to update the view manually (refresh materialized view [name]).

Metabase free

Aggregate data ahead of time with summary tables

The idea here is use materialized views — or even a separate set of tables — to create summary tables that minimize computation. Say you have tables with a million rows, and you want to aggregate data in multiple columns. You can create a materialized view based on aggregations of one or more tables, which will perform the initial (time-consuming) computation. Rather than have a dashboard query and compute that raw data several times throughout a day, you could instead create questions that query that summary table to get the data computed the night before.

For example, you could have an orders table that contains all of the orders table, and an order summary table that updates nightly and stores rollups and other aggregated data, such as order totals per week, month, etc. If a person wants to view the individual orders used to compute that aggregate, you can use Metabase Enterprise’s customized drill-through to link users to a question or dashboard that does query the raw data.

Pull data out of JSON and slot its keys into columns

We often see organizations storing JSON objects in a single column of a relational database like MySQL or PostgreSQL. Typically, these organizations are storing JSON payloads from event analytics software like Segment, or Amplitude.

Though some databases can index JSON (PostgreSQL can index JSON binaries, for example), you still have to grab the full JSON object each time, even if you’re only interested in a single key-value pair in the object. Instead, consider extracting each field from these JSON objects and mapping those keys to columns in a table.

Consider a database optimized for analytics

If you’ve done all of the above, and the length of your dashboard loading times are still interfering with your ability to make decisions in a timely manner, you should consider using a database that is structured specifically for fielding analytical queries. These databases are known as Online Analytics Processing databases, or OLAP databases (sometimes called Data warehouses).

Traditional relational databases like PostgreSQL and MySQL are designed for transaction processing, and are categorized as Online Transaction Processing databases (OLTP). These databases are better suited for use as operational databases, such as storing data for web or mobile applications. They are quite good at handling the following scenario: someone submits a thoughtful, germane, and not at all inflammatory comment to your website, your app fires a POST request to your backend, which routes the comment and metadata to your database for storage. OLTP databases can handle large volumes of concurrent transactions like comment posts, cart checkouts, profile bio updates, etc.

Metabase Real Time Dashboard Download

The main difference between OLAP and OLTP systems is that OLAP databases optimize analytical queries like sums, aggregates, and other analytical operations on large amounts of data, as well as bulk imports (via an ETL), whereas OLTP databases must balance large reads from the database with other transaction types: small inserts, updates, and deletes.

OLAPs typically use columnar storage. Whereas traditional (OLTP) relational databases store data by rows, databases that use columnar storage (unsurprisingly) store data by columns. This columnar storage strategy gives OLAP databases an advantage when reading data, as queries do not have to sift through irrelevant rows. There are other efficiencies that can be gained as well, such as the ability to compress columns with redundant data using bitmap indexing and run-length encoding.

Metabase Real Time Dashboard Sign In

Data in these databases is typically organized in fact and dimension tables, with (often massive) fact tables housing events. Each event contains a list of attributes and foreign key references to dimension tables, which contain information about those events: who was involved, what happened, product information, and so on.

Metabase Real Time Dashboard Login

Metabase supports several popular data warehouses: Google BigQuery, Amazon Redshift, Snowflake, and Apache Druid (which specializes in real-time analytics). Metabase also supports Presto, which is a query engine that can be paired with a variety of different datastores, including Amazon S3 (Simple Storage Service).

As you start out using Metabase, don’t worry too much about the underlying data store. But as your data grows, and adoption of Metabase grows, keep an eye out for indicators that you may want to investigate using a data warehouse. Redshift, for example, can query petabytes of data, and scale to querying historical data in Amazon S3. And Snowflake allows you to dynamically scale your compute resources as your organization grows.

Metabase Real Time Dashboard

Further reading

For more tips on improving performance, check out our articles on scaling Metabase and SQL query best practices.

Metabase Real Time Dashboard Login

If you’ve improved dashboard performance at your organization, you can share your tips on our forum.