Metabase Query

Metabase Query Language
Metabase Query Definition
Metabase Query Json
Metabase Query Interview
Metabase Query Code
Metabase Query Template

Metabase is a good, popular open-source BI tool that anyone can quickly install on your local environment to get a simple BI system up and running.

Yet when your analytics needs grow, you might face some of these pain points when using Metabase:

You can write SQL (or your database’s native querying language) directly into the editor that appears. To try it out, select the Sample Dataset, then type the command select sum (subtotal), createdat from orders group by createdat. Don’t worry if you don’t understand this just yet. Ask a question or write a query. Click the Ask a question button in the top-right of Metabase to start a new simple exploration of one of your tables, ask a more detailed custom question using the notebook editor, or write a new SQL query if you want to really dig in. Make a new dashboard or pulse.

Have your business users ever found Metabase’s “Ask a question” too limiting for complex queries, and end up coming back to the analytics team to ask for custom reports?
Metabase only works well with a single SQL data source. If you have data from multiple sources, Metabase likely won't work well for you since it doesn't allow joining of data.
With Metabase, you can only work directly with your database tables because everything in Metabase is designed for simplicity.

In this post, we'll share with you a few alternative options to that, and tell you which pain points of Metabase each of these alternative addresses. So depending on your needs you might be able to pick the right option to replace, or use alongside with Metabase.

The below list of options are only tools that eventually offer a drag-and-drop interface to end users. At the end of the post we also include a list of tools that is only designed for technical users, SQL-to-chart translation.

Holistics is a nice BI alternative to Metabase. It works similar to Metabase in a way that it allows you to map your database tables into models and relationships, and expose this to the end business users to 'self-service explore'.

Similars/Difference to Metabase:

Both are built on top of SQL querying engine and both offers a drag-and-drop experience for non-technical users.
Metabase fits only at at the visualization layer, Holistics offer additional ELT capabilities (data preparation).

Pros (compared to Metabase):

A stronger data modeling layer that allows handling sophisticated raw data.
Business users can ask more sophisticated questions using their Explore interface.
Works well with non-SQL data sources as they offer a mini-ETL experience with common sources (MongoDB, Google Analytics, etc)
Have an in-built DAG-like transformation layer so that you can transform raw data into aggregated datasets before exploration
Cloud-based so it doesn't take time to setup.

Cons (compared to Metabase):

Might not look as visually appealing as Metabase

Pricing:

Free, paid plans start from $50-$500 per month.

Tableau is considered the best tool when it comes to visualization (prettiness) as it's their primary focus. Tableau is also recently acquired by Salesforce.

Similars or differences to Metabase:

While Metabase translates everything to SQL, by default Tableau uses their in-memory datastore, making it more difficult to debug when things go wrong (you can't look at the SQL query to troubleshoot).

Pros (compared to Metabase):

Pretty visualization (best in their class)
Friendly for business users to build your own chartings
Work with a wide range of data sources

Cons (compared to Metabase):

To design charts effectively you need to use their Desktop version

Pricing:

Metabase Query Language

Based on user roles (Creator, Explorer, Viewer) with mimimum commitment required.
They have a Free desktop version if you're publishing the reports publicly.

Coming out of Microsoft and with strong history of Excel and PowerPivot, PowerBI is a fine choice to replace Metabase. They also have ability to load custom visualization.

Similar/Difference from Metabase:

Metabase Query Definition

While Metabase translates everything to SQL, by default PowerBI uses their in-memory datastore and their proprietary language DAX, making it more difficult to debug when things go wrong (you can't look at the SQL query to troubleshoot).

Pros (compared to Metabase):

Their explorer interface is comprehensive for end-business users to work with.
They offer from loading data from multiple sources, to drag-and-drop transform UI, to visualization.

Cons (compared to Metabase):

We suspect if you're more inclined towards SQL-backed data reporting like Metabase, you might not like Microsoft-style, Excel-like, proprietary approach of PowerBI.
PowerBI Editor can only run on Desktop running Windows. (that's why we wrote a post on how to use PowerBI on Mac devices)
Their best practice require you to host your data into PowerBI servers, i.e duplicating your data into their server.

Pricing:

Free for single user (desktop)
$10 per user for small-scale shared resource deployment
Starts to get fairly expensive for medium-to-large scale deployment (starts at $5K USD a month - listed price on website).

Looker (now part of Google) is quite a good BI tool to replace Metabase, but only if you're a big organization with large budget.

Pros (compared to Metabase):

Have a custom-built DSL layer (called LookML) to perform mapping between database tables and business logic, thus it's more flexible and customizable.
Have an in-built transformation layer so that you can transform raw data into aggregated datasets before exploration
Cloud-based so it doesn't take time to setup (as compared to Metabase)

Cons (compared to Metabase):

Since they use their own DSL language to model data, it takes quite a learning curve to get started.
It's also expensive and meant for large-scale deployment

Pricing:

Quite expensive, starting from $3000/month.

If you don't need self-service capabilities that Metabase offers for business users, you can also check out these tools:

Redash (open-source SQL to chart tools)
Cluvio (SQL to chart tool, paid offering with a free plan)
Superset (opensource, SQL to chart tool, coming out of Airbnb)
Mode Analytics (SQL to chart tool with paid offering)

Metabase Query Json

-->

IIS server variables provide information about the server, the connection with the client, and the current request on the connection. IIS server variables are not the same as environment variables.

Security

Some server variables get their information from HTTP headers. It is recommended that you distrust information in HTTP headers because this data can be falsified by malicious users. For more detailed information, see Writing More Secure IIS Applications.

Metabase Query Interview

Obtaining Server Variables

Server variables can be obtained in one of the following ways:

Metabase Query Code

In ASP, use the Request.ServerVariables collection.
In ASP.NET, use the HttpRequest.ServerVariables property. HttpRequest.ServerVariables returns some additional server variables that are only relevant to ASP.NET.
In COM+ components for ASP, use the IRequest::get_ServerVariables method.
In ISAPI applications, use the GetServerVariable callback function. In ISAPI, unicode versions of server variables are available by pre-pending 'UNICODE_' to the front of server variable names. For more information about unicode server variables, see the 'Remarks' section of the GetServerVariable topic.

Metabase Query Template

Variable	Description
ALL_HTTP	All HTTP headers sent by the client.
ALL_RAW	Retrieves all headers in raw form. The difference between ALL_RAW and ALL_HTTP is that ALL_HTTP places an HTTP_ prefix before the header name and the header name is always capitalized. In ALL_RAW the header name and values appear as they are sent by the client.
APP_POOL_ID IIS 5.1 and earlier: This server variable is not available.	Returns the name of the application pool that is running in the IIS worker process that is handling the request. There is also an APP_POOL_ID environment variable that is available to applications that are running in the IIS worker process.
APPL_MD_PATH	Retrieves the metabase path of the application.
APPL_PHYSICAL_PATH	Retrieves the physical path corresponding to the metabase path in APPL_MD_PATH.
AUTH_PASSWORD	The value entered in the client's authentication dialog. This variable is available only if Basic authentication is used.
AUTH_TYPE	The authentication method that the server uses to validate users when they attempt to access a protected script. It does not mean that the user was authenticated if AUTH_TYPE contains a value and the authentication scheme is not Basic or integrated Windows authentication. The server allows authentication schemes it does not natively support because an ISAPI filter may be able to handle that particular scheme.
AUTH_USER	The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. This variable is no different from REMOTE_USER. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.
CACHE_URL IIS 5.1 and earlier: This server variable is not available.	For use in ISAPI applications only. Returns the unambiguous name for the current URL. It is necessary to use the Unicode version of this variable in conjunction with the kernel mode cache invalidation function to evict entries placed in the cache by HSE_REQ_VECTOR_SEND. Note The server variable 'UNICODE_CACHE_URL' is used in conjunction with the cache invalidation function retrieved by the HSE_REQ_GET_CACHE_INVALIDATION_CALLBACK function. This function invalidates responses cached in HTTP.SYS, whether those responses are produced by requests or by ISAPIs calling HSE_REQ_VECTOR_SEND.
CERT_COOKIE	Unique ID for the client certificate, returned as a string. This can be used as a signature for the whole client certificate.
CERT_FLAGS	bit0 is set to 1 if the client certificate is present. bit1 is set to 1 if the certification authority of the client certificate is invalid (that is, it is not in the list of recognized certification authorities on the server). If bit 1 of CERT_FLAGS is set to 1, indicating that the certificate is invalid, IIS version 4.0 and later will reject the certificate. Earlier versions of IIS will not reject the certificate.
CERT_ISSUER	Issuer field of the client certificate (O=MS, OU=IAS, CN=user name, C=USA).
CERT_KEYSIZE	Number of bits in the Secure Sockets Layer (SSL) connection key size. For example, 128.
CERT_SECRETKEYSIZE	Number of bits in server certificate private key. For example, 1024.
CERT_SERIALNUMBER	Serial number field of the client certificate.
CERT_SERVER_ISSUER	Issuer field of the server certificate.
CERT_SERVER_SUBJECT	Subject field of the server certificate.
CERT_SUBJECT	Subject field of the client certificate.
CONTENT_LENGTH	The length of the content as given by the client.
CONTENT_TYPE	The data type of the content. Used with queries that have attached information, such as the HTTP queries GET, POST, and PUT.
GATEWAY_INTERFACE	The revision of the CGI specification used by the server. The format is CGI/revision.
HEADER_<HeaderName> IIS 5.1 and earlier: This server variable is not available.	The value stored in the header <HeaderName>. Any header other than those listed in this table must be preceded by 'HEADER_' in order for the ServerVariables collection to retrieve its value. This is useful for retrieving custom headers. Note Unlike HTTP_<HeaderName>, all characters in HEADER_<HeaderName> are interpreted as-is. For example, if you specify HEADER_MY_HEADER, the server searches for a request header named MY_HEADER.
HTTP_<HeaderName>	The value stored in the header <HeaderName>. Any header other than those listed in this table must be preceded by 'HTTP_' in order for the ServerVariables collection to retrieve its value. This is useful for retrieving custom headers. Note The server interprets any underscore (_) characters in <HeaderName> as dashes in the actual header. For example, if you specify HTTP_MY_HEADER, the server searches for a request header named MY-HEADER.
HTTP_ACCEPT	Returns the value of the Accept header that contains a list of accepted formats, for example, 'image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel'. The values of the fields for the HTTP_ACCEPT variable are concatenated, and separated by a comma (,).
HTTP_ACCEPT_ENCODING	Returns a list of accepted encoding types, for example, 'gzip, deflate'.
HTTP_ACCEPT_LANGUAGE	Returns a string describing the language to use for displaying content.
HTTP_CONNECTION	Returns a string describing the connection type, for example, 'Keep-Alive'.
HTTP_COOKIE	Returns the cookie string that was included with the request.
HTTP_HOST	Returns the name of the Web server. This may or may not be the same as SERVER_NAME depending on type of name resolution you are using on your Web server (IP address, host header).
HTTP_METHOD	The method used to make the request (same as REQUEST_METHOD).
HTTP_REFERER	Returns a string that contains the URL of the page that referred the request to the current page using an HTML <A> tag. Note that the URL is the one that the user typed into the browser address bar, which may not include the name of a default document. If the page is redirected, HTTP_REFERER is empty. HTTP_REFERER is not a mandatory member of the HTTP specification.
HTTP_URL	Returns the raw, encoded URL, for example, '/vdir/default.asp?querystring'.
HTTP_USER_AGENT	Returns a string describing the browser that sent the request.
HTTP_VERSION	The name and version of the request protocol (the raw form of SERVER_PROTOCOL).
HTTPS	Returns ON if the request came in through a secure channel (for example, SSL); or it returns OFF, if the request is for an insecure channel.
HTTPS_KEYSIZE	Number of bits in the SSL connection key size. For example, 128.
HTTPS_SECRETKEYSIZE	Number of bits in the server certificate private key. For example, 1024.
HTTPS_SERVER_ISSUER	Issuer field of the server certificate.
HTTPS_SERVER_SUBJECT	Subject field of the server certificate.
INSTANCE_ID	The ID for the IIS instance in textual format. If the instance ID is 1, it appears as a string. You can use this variable to retrieve the ID of the Web server instance (in the metabase) to which the request belongs.
INSTANCE_META_PATH	The metabase path for the instance of IIS that responds to the request.
LOCAL_ADDR	Returns the server address on which the request came in. This is important on computers where there can be multiple IP addresses bound to the computer, and you want to find out which address the request used.
LOGON_USER	The Windows account that the user is impersonating while connected to your Web server. Use REMOTE_USER, UNMAPPED_REMOTE_USER, or AUTH_USER to view the raw user name that is contained in the request header. The only time LOGON_USER holds a different value than these other variables is if you have an authentication filter installed.
PATH_INFO	Path information, as given by the client, for example, '/vdir/myisapi.dll/zip'. If this information comes from a URL, it is decoded by the server before it is passed to the CGI script or ISAPI filter. If the AllowPathInfoForScriptMappings metabase property is set to true (to support exclusive CGI functionality), PATH_INFO will only contain '/zip' and ISAPI applications such as ASP will break.
PATH_TRANSLATED	The physical path that maps to the virtual path in PATH_INFO, for example, 'c:inetpubwwwrootvdirmyisapi.dll'. This variable is used by IIS during the processing of ISAPI applications. If the AllowPathInfoForScriptMappings metabase property is set to true (to support exclusive CGI functionality), PATH_INFO will only contain '/zip' and ISAPI applications such as ASP will break.
QUERY_STRING	Query information stored in the string following the question mark (?) in the HTTP request.
REMOTE_ADDR	The IP address of the remote host that is making the request.
REMOTE_HOST	The name of the host that is making the request. If the server does not have this information, it will set REMOTE_ADDR and leave this empty.
REMOTE_PORT	The client port number of the TCP connection.
REMOTE_USER	The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.
REQUEST_METHOD	The method used to make the request. For HTTP, this can be GET, HEAD, POST, and so on.
SCRIPT_NAME	A virtual path to the script being executed, for example, '/vdir/default.asp'. This is used for self-referencing URLs.
SCRIPT_TRANSLATED IIS 5.1 and earlier: This server variable is not available.	The canonical physical path to the script listed in SCRIPT_NAME, for example, '?c:inetpubwwwrootvdirdefault.asp'.
SERVER_NAME	The server's host name, DNS alias, or IP address as it would appear in self-referencing URLs.
SERVER_PORT	The server port number to which the request was sent.
SERVER_PORT_SECURE	A string that contains either 0 or 1. If the request is being handled on the secure port, then this is 1. Otherwise, it is 0.
SERVER_PROTOCOL	The name and revision of the request information protocol. The format is protocol/revision. (The canonicalized form of HTTP_VERSION.)
SERVER_SOFTWARE	The name and version of the server software that answers the request and runs the gateway. The format is name/version.
SSI_EXEC_DISABLED IIS 5.1 and earlier: This server variable is not available.	Returns a 1 if the server-side include directive, #exec, is disabled. Otherwise, SSI_EXE_DISABLED returns a 0. To enable or disable #exec, use the SSIExecDisablemetabase property.
UNENCODED_URL IIS 4.0 and earlier: This server variable is not available.	Returns the raw, unencoded URL, for example, '/vdir/default.asp?querystring'.
UNICODE_<ServerVariable Name> IIS 5.1 and earlier: This server variable is not available.	In unicode ISAPI applications only, it is possible to retrieve server variable values as unicode values by prepending 'UNICODE_' to the name of the server variable, unless the variable starts with 'HTTP_' or 'HEADER_'.
UNMAPPED_REMOTE_USER	The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account (same as REMOTE_USER). If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.
URL	Gives the base portion of the URL, without any querystring or extra path information, for example, '/vdir/default.asp'. For the raw URL, use HTTP_URL or UNENCODED_URL.
URL_PATH_INFO Note: This server variable is only available on IIS 5.0.	Use PATH_INFO instead.