Initially, traditional business intelligence (BI) platforms were standalone solutions that required significant effort to deliver static reports to key people in the organization. The democratization of data by modern analytics changed that by shifting access of the few to access of the many, from content creation to content consumption. 

Unfortunately, more data, more users, and more use cases in modern analytics platforms are not directly proportional to more business value. Business users are likely to be overwhelmed by the volume of data sources and dashboards at their fingertips. They often give up out of frustration when trying to find the right data, even with the motivation to hunt for it.

Curated data experiences can make all the difference. While the integration of analytics into user-facing applications is often associated with enhancing customer experiences and their decision-making, this is a limited view of its potential. The benefits of embedded analytics extend far beyond external use cases.

Let’s take a deeper look into three reasons why embedded analytics is valuable for driving improved operational efficiency and relevant insights in business workflows, making it not only applicable to your customers but also important within your organization.

## Enhance Internal Decision-Making
The clearest path to deliver the right data to the right user at the right time is embedded analytics. By meeting business users where they are, embedding can empower employees across all skill levels with data in ways not possible within a standalone BI platform. With embedded analytics, users can access data when they need it most: in the context of their workflow.

Embedded analytics has the potential to revolutionize decision-making within your organization. Instead of viewing a dashboard in the analytics platform, making a note, and switching to the operational system to act on it, you can curate the analytics experience within a business application or a custom solution to make it more relevant and actionable in the moment—without toggling between different systems to take action.

For example, a <a href="https://cube.dev/case-studies/speeding-up-query-performance-and-managing-massive-amounts-of-data" target="_blank" rel="noopener">Fortune 500 manufacturing</a> company built an internal embedded analytics solution for its plant operations teams, which enabled every plant worker to make data-driven decisions. The application manager shared, “The plant teams love the data, and we’ve seen that they don't even want to wait until a new plant is settled before starting to get it. They want to go live as soon as possible and start collecting and visualizing the data from day one.” 

![](https://ucarecdn.com/106506b8-f814-456a-a31e-3a786e4268e2/-/quality/smart/-/format/auto/)

## Boost Employee Productivity and Engagement 
Embedded analytics boosts productivity and engagement with seamless metrics integration. You can foster a more engaging work environment with contextual data, aligned with individual, team, and organizational KPIs. Encouraging ownership and accountability, personalized insights help employees monitor their performance to goals and measure the impact of their contributions.

Curated embedded experiences drive performance across the organization with relevant data for specific roles. For instance, embedded analytics for sales teams can provide near real-time updates about customer engagement, buying trends, and pipeline status. Immediate access to this information allows salespeople to make faster, more informed decisions to close more deals, instead of gathering information manually from multiple systems of record.

Cube Cloud makes it easy to deliver consistent KPIs and metrics on an accelerated development timeline. Choose from a wide range of [integrations](https://cube.dev/integrations), then define the data model and metrics. Use [data access controls](https://cube.dev/product/data-access-control) to return filtered data based on the current user’s [security context](https://cube.dev/docs/product/auth/context). You have full control of the analytics experience to ensure no one is overwhelmed by irrelevant data or extraneous features in standalone modern BI platforms. 

![](https://ucarecdn.com/2fdf0238-54bd-4c1b-8a56-2132947e5ad6/-/quality/smart/-/format/auto/)

## Optimize Business Processes
Embedded analytics optimizes business processes by integrating data-driven insights directly into workflows. The integration of analytics into operational tools allows for continuous learning and  improvement. Employees can make immediate adjustments and improvements in response to insights displayed in the embedded experience. 

Consider supply chain management and its complex logistics and operations variables. Embedding within supply chain management systems allows companies to track inventory levels, supplier performance, and demand forecasts in real-time. With a comprehensive view of the supply chain, managers can more effectively minimize costs and maximize efficiency. Whether it’s optimizing route planning or adjusting inventory levels, embedded analytics provides the actionable insights needed to keep everything running smoothly.

For [real-time analytics](https://cube.dev/use-cases/real-time-analytics), Cube Cloud’s universal semantic layer connects to streaming SQL technologies like <a href="https://ksqldb.io/" target="_blank" rel="noopener">ksqlDB</a>, <a href="https://materialize.com/" target="_blank" rel="noopener">Materialize</a>, and <a href="https://flink.apache.org/" target="_blank" rel="noopener">Apache Flink</a>. These streaming engines make it possible to process real-time data and run analysis without learning a new language or building language-unique integrations. Organizations that leverage embedded analytics in this way are able to continuously evolve and adapt to market conditions and customer expectations. 

![](https://ucarecdn.com/4b0606e2-0169-413e-a78b-dc0963f36cfd/-/quality/smart/-/format/auto/)

## Conclusion
Embedded analytics redefines how data is used within an organization, shifting from a traditional, often siloed approach to a more integrated, insightful, and actionable one. By embedding analytics directly into the operational tools employees use every day, businesses can transform isolated data interactions into focused insights that drive growth, innovation, and continuous improvement across all aspects of the organization. 

A curated analytics experience within business applications and custom solutions ensures that data is not only for skilled analysts or IT professionals but a fundamental part of every employee’s decision-making process. Organizations will find themselves better equipped to respond to internal challenges and dynamic market conditions, ensuring that they can thrive in today’s competitive business landscape.

For organizations looking to build embedded analytics for internal use cases, Cube Cloud provides you with the choice and flexibility to develop curated experiences to your unique requirements. By incorporating contextual data into business applications or custom solutions, you can enable more informed decisions without context switching between operational and analytical systems. Discover how Cube can transform your modern data stack by signing-up for a [free developer account](https://cubecloud.dev/auth/signup) today.


Michael Hetrick

Embedded Analytics

3 Reasons Why Embedded Analytics Isn’t Only for Your Customer Data

Modern Business Intelligence (BI) platforms have served as the backbone of data analytics for decades. Today, every organization strives to enable informed business decisions by making data available to more people. As data grows in volume and variety, these platforms and the teams who support and use them face significant hurdles to organize, manage, and access data consistently.

While modern platforms have been instrumental in data democratization by prioritizing self-service and ease-of-use, they often fall short of handling the complexity of today’s modern data stack. From cloud data warehouses and cloud-native enterprise applications to acquiring the right data and the joins, relationships, and calculations to correctly define a data model, there’s still too much guesswork for data analysts and business users who are seeking insights. 

In addition, people must navigate the proliferation of duplicate dashboards and datasets, fragmented business logic, and inconsistent calculation of key metrics—all eroding trust in the data. Perhaps, this is one of the reasons why BI penetration averages only 25-35% of the workforce according to various research statistics, despite their leadership’s goal of becoming more data-driven. 

## The Power of a Universal Semantic Layer
When surveyed in <a href="https://www.accenture.com/us-en/insights/technology/technology-trends-2023" target="_blank" rel="noopener">Accenture’s Technology Vision 2023</a> report, 95% of global executives agreed on the necessity of new data architectures and strategies to effectively manage significant changes in their organizations’ data environments. One of the technologies named in this report is a semantic layer.

At Cube, we believe the [universal semantic layer](https://cube.dev/use-cases/semantic-layer) is one of those pivotal changes that companies will need to make to address today’s challenges and leverage new opportunities of the rapidly evolving modern data stack. When your modern BI platforms connect to a universal semantic layer instead of a direct database connection, you are able to support each one with a common data model, including dimensions, measures, and calculations.

To level set, a universal semantic layer is an independent yet interoperable part of the modern data stack that sits between your data sources and data consumers. The universal semantic layer allows every data endpoint, whether it is [BI tools](https://cube.dev/use-cases/connected-bi), [embedded analytics](https://cube.dev/use-cases/embedded-analytics), or [AI agents](https://cube.dev/use-cases/llm-and-ai-semantic-layer) and chatbots, to work with the same semantics and underlying data, leading to consistent and trusted insights. 

## Taking Modern BI to the Next Level with Cube’s Universal Semantic Layer
Without a universal semantic layer, business logic and data models are scattered between the data source and the data consumer. There is business logic in your cloud data warehouse and your transformations. Data models are embedded in the content of every modern analytics platform. When an update to the data model is required, the only option is tedious searching, copying, and pasting of platform-specific formulas and business logic. Then, add to that a little bit of hope that you updated every occurrence.

![](https://ucarecdn.com/136f1efc-feca-41f9-b7b3-89313546ea03/-/quality/smart/-/format/auto/)

With a universal semantic layer, you can model your data once and deliver it anywhere. Regardless of the modern BI platform, people can connect to the same data and business definitions. This means that you could have <a href="https://www.microsoft.com/en-us/power-platform/products/power-bi" target="_blank" rel="noopener">Power BI</a> and <a href="http://tableau.com" target="_blank" rel="noopener">Tableau</a> (and even spreadsheets) connect to a single data model that translates queries to the underlying data, creating a new level of consistency without the need to update embedded logic within BI platforms or files. 

![](https://ucarecdn.com/eee2f7f0-c9b5-4c66-9d8f-197b3293f87b/-/quality/smart/-/format/auto/)

## Integrating Cube Cloud with Modern BI Platforms
As a fully-managed service, Cube takes care of infrastructure management, deployment, and scaling of Cube Cloud, allowing your team to focus on their core data engineering responsibilities. It’s easy to start and transition your models over time without a large upfront investment, while delivering trusted data to your organization with the universal semantic layer.

Begin by taking inventory of data connections within your modern analytics platforms and cloud data warehouse. Then, identify the most popular or slowest content and queries, depending on capabilities you want to test. 

Once you’ve identified the appropriate tables, [build the data model](https://cube.dev/docs/product/data-modeling/concepts) by making a connection to your cloud data warehouse. Cube Cloud can read your database schema, including primary and foreign keys, to generate cube files in YAML or JavaScript. Cubes serve as the building blocks for views that will be exposed to the modern BI platforms. 

Using the data model defined, you can now connect to Cube Cloud from your BI platform using the [SQL API](https://cube.dev/docs/product/apis-integrations/sql-api). The SQL API enables Cube to deliver trusted data over the <a href="https://www.postgresql.org/docs/current/protocol.html" target="_blank" rel="noopener">Postgres-compatible protocol</a>. With each interaction, Cube Cloud will translate the query from the client to the cloud data warehouse and return results. You might also add [security context](https://cube.dev/docs/product/auth/context) and query rewrite for [row-level](https://cube.dev/docs/product/apis-integrations/sql-api/security#security-context-row-level-security) or [column-level](https://cube.dev/docs/guides/recipes/access-control/column-based-access) security to centralize governance policies.

In both development and production, you’ll have [real-time performance insights](https://cube.dev/docs/product/workspace/performance) to inspect the query lifecycle, view resource utilization, and identify new optimization opportunities. These optimizations can include improved performance and more cost-efficient cloud resource utilization. Cube Cloud can reduce cloud data usage costs from redundant queries with in-memory caching, queue management, and configurable pre-aggregation caching in Cube Store.

Finally, setup [Semantic Layer Sync](https://cube.dev/blog/introducing-semantic-layer-sync) to push Cube Cloud data models out to the target systems. Semantic Layer Sync automatically synchronizes the data model from the universal semantic layer to Bl platforms, making Cube-specific entities, such as cubes and views, match BI-specific ones, such as datasets, tables, dimensions, and measures.

## Delivering More Trust and Value from Your Data with Cube Cloud
Cube Cloud’s compatibility with popular BI platforms will ensure a seamless transition to a better way of organizing and managing data used for analytics. By adding Cube Cloud’s universal semantic layer to your modern data stack, you can truly unlock the value of your data for more people:

- **Improved Data Availability**: Provide a single, unified interface for modeled data from your cloud data warehouse to every modern BI platform in your organization.
- **Consistent Business Definitions**: Define metrics and dimensions once and use them consistently across all modern BI platforms and spreadsheets, reducing errors and discrepancies.
- **Governed Data Access**: Streamline access to trusted data with centralized and enforced governance and security policies to promote more confident decisions. 
- **Faster, More Cost-Effective Performance**: Accelerate query performance through caching to ensure your analytics are as fast as they are accurate–often without querying the database directly.

For organizations looking to take their analytics programs to the next level, exploring Cube’s unified semantic layer is a step towards building context, consistency, and trust into every BI connection. By enhancing your modern BI platform with Cube, you can enable more informed decisions, faster and with greater confidence. Discover how Cube can transform your modern data stack by signing-up for a [free developer account](https://cubecloud.dev/auth/signup) today.


BI & Data Exploration

Rethinking Modern Business Intelligence with a Universal Semantic Layer

Every business today is data-centric and the role of semantic layers for optimizing efficiency is undeniable. Serving as the bridge between raw data sources and end-point data sources, semantic layers offer a consistent, comprehensive perspective of data across the board. This helps businesses make decisions rooted and organized in accuracy and reliability. By centralizing the definition of semantics and metrics, data teams can eliminate inconsistencies and ensure a unified understanding across all data users, be in internal, external or AI bots. Let us look into how semantic layers streamline data management and analytics, simplifying the concepts of a semantic layer into digestible and relatable terms. 

## How a Semantic Layer Works in the Data Stack

[Semantic layers](https://cube.dev/use-cases/semantic-layer) are not a new concept, but their importance has grown with the increasing complexity of modern data stacks. Today, businesses use a variety of visualization tools, customer-facing analytics, [embedded analytics](https://cube.dev/use-cases/embedded-analytics), and AI agents. Each time a new data visualization or data app is implemented, metrics and/or logic need to be defined. If not managed properly, this can lead to inconsistencies and discrepancies and, ultimately wasted resources. Semantic layers address this issue by centralizing these definitions, ensuring consistency across different data consumption endpoints.

The semantic layer has been around for a while, embedded in Business Intelligence (BI) tools. However, the recent shift towards standalone semantic layers is driven by the need for a universal, easy-to-use, and highly discoverable data interface. The democratization of data has led to fragmentation and silos, impeding ease of use and leading to failed analytics projects. A standalone semantic layer addresses these issues, ensuring a single source of consistent data that is easier for data teams to work with. 

The implementation of semantic layers can significantly streamline data management processes. Sitting on top of [data warehouses](https://cube.dev/blog/getting-started-with-a-snowflake-semantic-layer), semantic layers provide data semantics (context) to various data applications. They work seamlessly with transformation tools, allowing businesses to define metrics, prepare data models, and expose them to different BI and analytics tools. This not only enhances efficiency but also ensures that all data consumption places are working with the same, accurate data. So, no matter if the data is being used by a person looking at a dashboard, or a LLM that is giving a person answers to questions, the data is consistent. All of this makes it easier for data teams to quickly deliver data for the various data consumers they work with internally and externally.

## Examples of how companies use semantic layers

Businesses like [Drift](https://www.drift.com/) and [Breakthrough](https://www.breakthroughfuel.com/) have successfully leveraged semantic layers to enhance their data management and analytics. Drift, a customer communication platform, uses semantic layers to ensure consistency in its metrics across different systems. This has not only improved their reporting capabilities but also enabled them to add new data in days instead of months. Breakthrough, a transportation management platform, uses semantic layers to provide consistent data across their SaaS and consulting platforms, significantly improving productivity.

Adopting a semantic layer in an organization is a step-by-step process. It begins with identifying inefficiencies and building a thoughtful semantic model. This model is then incrementally applied to different products, aligning metrics and data definitions. The process also involves identifying and fixing bugs in the old system, ensuring that the data is well-understood and well-documented. This is crucial for the success of analytics and AI projects.

### How AI agents, LLMs and the semantic layers work together

The semantic layer also plays a pivotal role in the realm of [generative AI](https://cube.dev/use-cases/llm-and-ai-semantic-layer). As AI agents are increasingly used for various applications, they need to access and analyze hard data. Due to their non-deterministic nature, AI agents are not good with hard numbers, they need to rely on the context training that has been done on the Large Language Model (LLM) they are fed from. Use of LLMs to generate code (like SQL) can be executed to get the hard numbers for the AI agent. This is where the semantic layer comes in, providing AI agents with the necessary data access and semantics via the LLM. 

However, the use of generative AI also presents new challenges. One of these is the need for a new interface for interacting with data. Another is the need for explainability, ensuring that users can trust the data and understand how the AI is working. The semantic layer can help address these challenges, providing a well-documented and well-understood data interface for AI agents. Semantic layers provide both valuable context and necessary constraint to empower retrieval augmented generation models. While much has been written about providing context via embeddings, we are now learning that constraining LLMs via a semantic layer - choosing from a defined universe of metrics - can be very useful for improving accuracy.  A recent test was performed with an AI Chatbot on data – with and without a semantic layer feeding the LLM. The results were dang clear – AI Chatbot with a semantic layer, outcomes were 100% accurate — AI Chatbot without a semantic layer, outcomes were 83% accurate.  

In summing up, semantic layers are an invaluable asset for businesses keen on optimizing their [data management](https://cube.dev/product/caching-and-data-performance) and analytics. They centralize metric definitions and semantics, ensuring consistency across various data points, which in turn, bolster efficiency and precision. The future will bring more data sources and more data applications, creating even more chaos in the data stack. The significance of semantic layers in navigating the intricacies of the contemporary data stack is poised to increase exponentially. The semantic layer plays a crucial role in increasing confidence in data for non-technical users, fostering the incorporation of analytics and AI projects. It also paves the way for generative AI to access and scrutinize data. Looking ahead, the impact of the semantic layer on data analytics and AI is expected to be even more profound.


Lissa Daniels

Use Cases

Optimizing Data Management and Analytics Efficiency with Semantic Layers

Krystian Fras

Multi-Tenant Analytics with Auth0 and Cube

Sometimes, existing commercial or out-of-the-box open-source tools like Grafana don’t fit requirements for Nginx log analytics. Whether it is pricing, privacy, or customization issues, it is always good to know how to build such a system internally. 

In the following tutorial, I’ll show you how to build your own [Nginx](https://www.nginx.com/) log analytics with [Fluentd](https://www.fluentd.org/), [Kinesis Data Firehose](https://aws.amazon.com/kinesis/data-firehose/), [Glue](https://aws.amazon.com/glue/), [Athena](https://aws.amazon.com/athena/), and [Cube.js](https://cube.dev). This stack also makes it easy to add data from other sources, such as Snowplow events, into the same S3 bucket and merge results in Athena. I’ll walk you through the whole pipeline from data collection to the visualization.

[Here is the live demo of the final dashboard](https://statsbotco.github.io/cubejs/nginx-analytics-dashboard/).

Here’s a sample architecture of the application we’re going to build:
![Architecture](https://media.graphcms.com/output=format:jpg/ACbpDPQRKCakOMHIUVXu)

## Collecting Nginx Logs

By default, Nginx writes logs in a plain text format like this:

```bash
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-up HTTP/2.0" 200 9168 "https://example.com/sign-in" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-in HTTP/2.0" 200 9168 "https://example.com/sign-up" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
```

Although we can parse that, it would be much easier to change Nginx configuration to use the JSON format. 

```conf
log_format json_combined escape=json '{ "created_at": "$msec", '
			'"remote_addr": "$remote_addr", '
			'"remote_user": "$remote_user", '
			'"request": "$request", '
			'"status": $status, '
			'"bytes_sent": $bytes_sent, '
			'"request_length": $request_length, '
			'"request_time": $request_time, '
			'"http_referrer": "$http_referer", '
			'"http_x_forwarded_for": "$http_x_forwarded_for", '
			'"http_user_agent": "$http_user_agent" }';

access_log  /var/log/nginx/access.log  json_combined;
```

### Create an S3 Bucket
Create a new S3 bucket for the log files. Note that logs should be placed in the root of the bucket and no other data can be stored in that bucket. Also, consider using the same region as your Athena because Athena is not available in some regions.

### Define a Table Schema via the Athena Console

Open your Athena console and select the database you'd be using. To create a table, you can use AWS UI or just run create statement in the console.

```sql
CREATE EXTERNAL TABLE `kinesis_logs_nginx`(
  `created_at` double, 
  `remote_addr` string, 
  `remote_user` string, 
  `request` string, 
  `status` int, 
  `bytes_sent` int, 
  `request_length` int, 
  `request_time` double, 
  `http_referrer` string, 
  `http_x_forwarded_for` string, 
  `http_user_agent` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://<YOUR-S3-BUCKET>'
TBLPROPERTIES ('has_encrypted_data'='false');
```

### Create a Kinesis Firehose Stream

Open up the Kinesis Firehose console and click "Create delivery stream." Enter a name for the stream and set delivery to `direct PUT`.

![Create delivery stream](https://media.graphcms.com/output=format:jpg/KtIhwoRVmR1qUGrSoFAg)

Click next. Select `Record format conversion` to `Enabled`, `Output format` to `Apache ORC`, and select the database you created as the schema source.

![Set record format](https://media.graphcms.com/output=format:jpg/DupZUDNgTa6UgOc9Vvai)

Select S3 as a storage format. Select the S3 bucket you created for log files and leave `S3 Prefix` empty.

![S3 settings](https://media.graphcms.com/output=format:jpg/JI3GLcGNQCKi09bD1BKL)

You can change other options depending on your load, but we'll continue with the default ones. S3 compression is not available since the ORC format includes compression.
Now you've created the Kinesis Firehose stream!

### Fluentd

There are many different ways to install and use [Fluentd](https://www.fluentd.org/). We'll stick with the Docker version.

First, create a fluent.conf file and add a new Fluentd source to collect logs:

```xml
<source>
  @type forward
  port 24224
  bind 0.0.0.0
</source>
```

Now we can run a Fluentd server with this configuration. Here's a basic configuration; you can check out other options on [Docker Hub](https://hub.docker.com/r/fluent/fluentd/).

```bash
$ docker run \
  -d \
  -p 24224:24224 \
  -p 24224:24224/udp \
  -v /data:/fluentd/log \
  -v <PATH-TO-FLUENT-CONF>:/fluentd/etc fluentd \
  -c /fluentd/etc/fluent.conf
  fluent/fluentd:stable
```

This configuration uses the `/fluentd/log` path for cache files. You can run Fluentd in Docker without mounting the data directory, but in the case of a restart, you can lose all cached logs. Also, you can change the default `24224` port to any other unused port. Now, as we have a running Fluentd server, we can stream Nginx logs to it.

Since we run Nginx in Docker, we can run it with the built-in docker Fluentd log driver:

```bash
$ docker run \
--log-driver=fluentd \
--log-opt fluentd-address=<FLUENTD-SERVER-ADDRESS>\
--log-opt tag=\"{{.Name}}\" \
-v /some/content:/usr/share/nginx/html:ro \
-d \
nginx
```

We'll use the [Amazon Kinesis Output Plugin](https://github.com/awslabs/aws-fluent-plugin-kinesis). It can store logs captured from Nginx as ORC files.

Next, select the Nginx log using tag prefix match and parse JSON:

```xml
<filter YOUR-NGINX-TAG.*>
  @type parser
  key_name log
  emit_invalid_record_to_error false
  <parse>
    @type json
  </parse>
</filter>
```

We’re using the [kinesis_firehose](https://github.com/awslabs/aws-fluent-plugin-kinesis) output plugin to send parsed logs to Kinesis Firehose:

```xml
<match YOUR-NGINX-TAG.*>
	@type kinesis_firehose
	region region
	delivery_stream_name <YOUR-KINESIS-STREAM-NAME>
	aws_key_id <YOUR-AWS-KEY-ID>
	aws_sec_key <YOUR_AWS-SEC_KEY>
</match>
```

## Athena

Now you can query Nginx logs in Athena with SQL. Let's find some recent errors:

```sql
SELECT * FROM "db_name"."table_name" WHERE status > 499 ORDER BY created_at DESC limit 10;
```

### Full Scan for Each Request

Now we have logs parsed and delivered to S3 in the ORC format, which is compressed and efficient to query. Also, Kinesis Firehose partitions logs by date and hour, but querying and filtering them requires Athena to scan all files. This is a problem, because the more logs you store, the more data gets scanned per request. It is slow and also pricey, because Athena pricing depends on scanned data volume.

To solve this, we'll use AWS Glue Crawler, which gathers partition data from S3 and writes it to the Glue Metastore. Once data is partitioned, Athena will only scan data in selected partitions. It makes querying much more efficient in terms of time and cost.

### Setting an Amazon Glue Crawler

Amazon Glue Crawler can scan the data in the bucket and create a partitioned table for that data. 

Create a Glue Crawler and add the bucket you use to store logs from Kinesis. You can add multiple buckets to be scanned on each run, and the crawler will create separate tables for each bucket. Make sure to schedule this crawler to run periodically depending on your needs. We usually run a single crawler for all buckets every hour. Also, it's easier to use a separate database for all crawled tables.

### Partitioned Tables

Open your Athena console and choose the database you selected in the crawler configuration. After the first run of the crawler, you should see a table named the same as the S3 bucket where all log files are located. You can query results for some hours and filter the data by partitions.

```sql
SELECT * FROM "default"."part_demo_kinesis_bucket"
WHERE(
  partition_0 = '2019' AND
  partition_1 = '04' AND
  partition_2 = '08' AND
  partition_3 = '06'
  );
```

This query will select all events that came from 6 AM to 7 AM on April 8, 2019. 

Did that help? Let's run the same request without a partition filter.

![Query without partition filter](https://media.graphcms.com/output=format:jpg/dfCkPCTiSI6PDqZCssZ4)

You can see that the query took 3.59 seconds to run and processed 244.34 megabytes of data. Let's use partition helpers:

![Query with partition filter](https://media.graphcms.com/output=format:jpg/gqN32aeQUWKgz3wnD9pi)

This query is a bit faster and, more importantly, took only 1.23 megabytes of scanned data. On larger datasets, the difference grows dramatically.

## Building a Dashboard with Cube.js

We will use Cube.js to query Athena and build a dashboard. It has plenty of powerful features, but the main reasons we would want to use it, in this case, are automatic handling of partitions and pre-aggregations.

Cube.js can dynamically generate SQL, taking care of partitioning. It uses [data schema](https://cube.dev/docs/reference/frontend/cubejs-client-core), which is written in Javascript, to generate SQL. We can put instructions on how to filter partitions in the data schema. 

Let’s create a new Cube.js app with a serverless deployment option. Since we’re using Athena, serverless is the easiest way to deploy. If you prefer other deployment environments, such as Docker or Heroku, you can check the documentation on deployment options [here](https://cube.dev/docs/deployment).

```bash
$ npm install -g cubejs-cli
$ cubejs create nginx-log-analytics -t serverless -d athena
```

Cube.js uses environment variables for database credentials. On new app creation, the CLI generates the `.env` file with placeholders in the project directory. Fill it with your [Athena credentials](https://cube.dev/docs/connecting-to-the-database#notes-aws-athena).

Now, we can create a [data schema](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) file, where we’ll define how Cube.js should query the Athena logs table, as well as measures and dimensions for our dashboard. If you are just getting started with Cube.js, I recommend checking [this](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) or [that](https://cube.dev/docs/product/data-modeling/overview) tutorial to learn more about data schemas and how Cube.js generates SQL.

In the `schema` folder, create the file `Logs.js` with the following content:

```javascript
const partitionFilter = (from, to) => `
    date(from_iso8601_timestamp(${from})) <= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d') AND
    date(from_iso8601_timestamp(${to})) >= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d')
    `

cube(`Logs`, {
  sql: `
  select * from part_demo_kinesis_bucket
  WHERE ${FILTER_PARAMS.Logs.createdAt.filter(partitionFilter)}
  `,

  measures: {
    count: {
      type: `count`,
    },

    errorCount: {
      type: `count`,
      filters: [
        { sql: `${CUBE.isError} = 'Yes'` }
      ]
    },

    errorRate: {
      type: `number`,
      sql: `100.0 * ${errorCount} / ${count}`,
      format: `percent`
    }
  },

  dimensions: {
    status: {
      sql: `status`,
      type: `number`
    },

    isError: {
      type: `string`,
      case: {
        when: [{
          sql: `${CUBE}.status >= 400`, label: `Yes`
        }],
        else: { label: `No` }
      }
    },

    createdAt: {
      sql: `from_unixtime(created_at)`,
      type: `time`
    }
  }
});
```

In the top level `sql` expression for the `Logs` cube, we are using the [FILTER_PARAMS](https://cube.dev/docs/cube#context-variables-filter-params) feature of Cube.js to dynamically generate SQL based on the passed filter parameters.

We also define measures and dimensions we’ll use in our dashboard. One last thing to do before building a frontend is to set up pre-aggregations. The Cube.js pre-aggregation engine builds a layer of aggregated data in your database during the runtime and keeps it up-to-date. It can significantly speed up the performance and also in the case of Athena reduce billing as well. [This guide](https://cube.dev/blog/high-performance-data-analytics-with-cubejs-pre-aggregations/) covers using pre-aggregations in more details.

To add it to our schema, add the following block to the end of the cube definition.

```javascript
preAggregations: {
  main: {
    type: `rollup`,
    measureReferences: [count, errorCount],
    dimensionReferences: [isError, status],
    timeDimensionReference: createdAt,
    granularity: `day`,
    partitionGranularity: `month`,
    refreshKey: {
      sql: FILTER_PARAMS.Logs.createdAt.filter((from, to) => 
        `select
           CASE WHEN from_iso8601_timestamp(${to}) + interval '3' day > now()
           THEN date_trunc('hour', now()) END`
      )
    }
  }
}
```

We’re pre-aggregating all the measures and dimensions we’ll use and also making this pre-aggregation to be partitioned by month. [Partitioning pre-aggregations](https://cube.dev/docs/pre-aggregations#rollup-time-partitioning) can dramatically increase background refresh time.

Now, we are ready to build our frontend dashboard.

Cube.js provides [REST API](https://cube.dev/docs/rest-api), a [Javascript client](hhttps://cube.dev/docs/reference/frontend/cubejs-client-core), and bindings for popular frameworks, such as React and Vue. The clients are visualization agnostic and take care of API calls and data formatting, letting developers use any visualization library.

The Cube.js server accepts a query in a [JSON format](https://cube.dev/docs/query-format) with measures and dimensions. It then generates and executes SQL against Athena, and sends the result back. For example, to load the count of requests with an error over time by day, we can use the following request:

```json
{
  "measures": ["Logs.errorCount"],
  "timeDimensions": [
    {
      "dimension": "Logs.createdAt",
      "dateRange": ["2019-01-01", "2019-01-07"],
      "granularity": "day"
    }
  ]
}
```

You can install the Cube.js Javascript Client and React binding with NPM.

```bash
$ npm i --save @cubejs-client/core @cubejs-client/react
```

Then import the `cubejs` and `QueryRenderer` components, and use them to fetch the data. In the example below, we use Recharts to visualize data.

```javascript
import React from 'react';
import { LineChart, Line, XAxis, YAxis } from 'recharts';
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';

const cubejsApi = cubejs(
  'YOUR-CUBEJS-API-TOKEN',
  { apiUrl: 'http://localhost:4000/cubejs-api/v1' },
);

export default () => {
  return (
    <QueryRenderer
      query={{
        measures: ['Logs.errorCount'],
        timeDimensions: [{
            dimension: 'Logs.createdAt',
            dateRange: ['2019-01-01', '2019-01-07'],
            granularity: 'day'
        }]
      }}
      cubejsApi={cubejsApi}
      render={({ resultSet }) => {
        if (!resultSet) {
          return 'Loading...';
        }

        return (
          <LineChart data={resultSet.rawData()}>
            <XAxis dataKey="Logs.createdAt"/>
            <YAxis/>
            <Line type="monotone" dataKey="Logs.errorCount" stroke="#8884d8"/>
          </LineChart>
        );
      }}
    />
  )
}
```

[This tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) goes into great detail on how to build dashboards with Cube.js.

You can find a CodeSandbox with a demo dashboard built with React and Cube.js below.

<iframe src="https://codesandbox.io/embed/l5qyrzlw07?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

Timur Minulin

Nginx Log Analytics with AWS Athena and Cube.js

_This is the second part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). You can find [the first part here](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

In this part, we are going to add Funnel Analysis to our application. Funnel Analysis, alongside with Retention Analysis, is vital to analyze behavior across the customer journey.  A funnel is a series of events that a user goes through within the app, such as completing an onboarding flow. A user is considered converted through a step in the funnel if she performs the event in the specified order. Calculating how many unique users made each event could show you a conversion rate between each step. It helps you to localize a problem down to a certain stage.

Since our application tracks its own usage, we'll build funnels to show how well users navigate through the funnels usage. Quite meta, right?

Here’s how it looks. You [check the live demo here](https://d1ygcqhosay4lt.cloudfront.net/funnels).

![](https://media.graphcms.com/yN9dB53UTkOFUwIsjwLT)

## Building SQL for Funnels

Just a quick recap of part I—we are collecting data with the Snowplow tracker, storing it in S3, and querying with Athena and Cube.js. Athena is built on Presto, and supports standard SQL. So to build a funnel, we need to write a SQL code. Real-world funnel SQL could be quite complex and slow from a performance perspective. Since we are using Cube.js to organize data schema and generate SQL, we can solve both of these problems.

Cube.js allows the building of packages, which are a collection of reusable data schemas. Some of them are specific for datasets, such as the [Stripe Package](https://github.com/cube-js/stripe-schema). Others provide helpful macros for common data transformations. And one of them we're going to use—the Funnels package.

_If you are new to Cube.js Data Schema, I strongly recommend you check [this](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) or [that](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/) tutorial first and then come back to learn about the funnels package._

The best way to organize funnels is to create a separate cube for each funnel. We'll use `eventFunnel` from the Funnel package. All we need to do is to pass an object with the required properties to the `eventFunnel` function. 
[Check the Funnels package documentation for detailed information about its configuration.](https://cube.dev/docs/funnels)

Here is how this config could look.
_In production applications, you're most likely going to generate Cubes.js schema dynamically. [You can read more about how to do it here.](https://cube.dev/docs/schema-generation)_

```javascript
import Funnels from "Funnels";

import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from "./Events.js";

cube("FunnelsUsageFunnel", {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `user_id`
    },
    time: {
      sql: `time`
    },
    steps: [
      {
        name: `viewAnyPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT}`
        }
      },
      {
        name: `viewFunnelsPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT} AND page_title = 'Funnels'`
        },
        timeToConvert: "30 day"
      },
      {
        name: `funnelSelected`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${CUSTOM_EVENT} AND se_category = 'Funnels' AND se_action = 'Funnel Selected'`
        },
        timeToConvert: "30 day"
      }
    ]
  })
});

```

The above, 3-step funnel, describes the user flow from viewing any page, such as the home page, to going to Funnels and then eventually selecting a funnel from the dropdown. We're setting `timeToConvert` to 30 days for the 2nd and 3rd steps. This means we give a user a 30 day window to let her complete the target action to make it to the funnel.

In our example app, we generate these configs dynamically. [You can check the code on Github here.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/backend/schema/Funnels.js)

## Materialize Funnels SQL with Pre-Aggregations

As I mentioned before, there is a built-in way in Cube.js to accelerate queries’ performance. 
Cube.js can materialize query results in a table. It keeps them up to date and queries them instead of raw data. Pre-Aggregations can be quite complex, including multi-stage and dependency management. But for our case, the simplest `originalSql` pre-aggregation should be enough. It materializes the base SQL for the cube.

[Learn more about pre-aggregations here.](https://cube.dev/docs/pre-aggregations)

```javascript
import Funnels from 'Funnels';
import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from './Events.js';

cube('FunnelsUsageFunnel', {
  extends: Funnels.eventFunnel({ ... }),
  preAggregations: {
    main: {
      type: `originalSql`
    }
  }
});
```
## Visualize

There are a lot of way to visualize a funnel. Cube.js is visualization-agnostic, so pick one that works for you and fits well into your app design. In our example app, we use a bar chart from the Recharts library.

The Funnels package generates a cube with `conversions` and `conversionsPercent` measures, and `steps` and `time` dimensions. To build a bar chart funnel, we need to query the `conversions` measure grouped by the `step` dimension. The `time` dimension should be used in the filter to allow users to select a specific date range of the funnel.

Here is the code (we are using React and the Cube.js React Client):

```jsx
import React from "react";
import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip } from "recharts";

const cubejsApi = cubejs(
  "YOUR-API-KEI",
  { apiUrl: "http://localhost:4000/cubejs-api/v1" }
);

const Funnel = ({ dateRange, funnelId }) => (
  <QueryRenderer
    cubejsApi={cubejsApi}
    query={{
      measures: [`${funnelId}.conversions`],
      dimensions: [`${funnelId}.step`],
      filters: [
        {
          dimension: `${funnelId}.time`,
          operator: `inDateRange`,
          values: dateRange
        }
      ]
    }}
    render={({ resultSet, error }) => {
      if (resultSet) {
        return (
          <BarChart
            width={600}
            height={300}
            margin={{ top: 20 }}
            data={resultSet.chartPivot()}
          >
            <CartesianGrid strokeDasharray="3 3" />
            <XAxis dataKey="x" minTickGap={20} />
            <YAxis />
            <Tooltip />
            <Bar dataKey={`${funnelId}.conversions`} fill="#7A77FF"/>
          </BarChart>
        );
      }

      return "Loading...";
    }}
  />
);

export default Funnel;
```

If you run this code in CodeSandbox, you should see something like this.

<iframe src="https://codesandbox.io/embed/nw87w1nnjm?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

The above example is connected to the Cube.js backend from our event analytics app. 

In the next part, we’ll walk through how to build a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 4 will cover the Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net/)

Artyom Keydunov

Building an Open Source Mixpanel Alternative. Part 2: Conversion Funnels

_This is the first part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

![](https://media.graphcms.com/8iQ3g6FUTDqvC73peT6q)

There is a category of analytics tools like Mixpanel or Amplitude, which are good at working with events data. They are ideal for measuring product or engagement metrics, such as activation funnels or retention. They are also very useful for measuring A/B tests. 

Although all these tools do a job, they are proprietary and cloud-based. That could be a problem when privacy is a concern. Or if one wants to customize how funnels or retention work under the hood. While traditional BI tools, like Tableau or Power BI, could potentially be used to run the same analysis, they can not offer the same level of user experience. The problem is that they are designed to be general business intelligence tools, and not specific for funnels, retention, A/B tests, etc.

With recent advancements in frontend development, it became possible to rapidly develop complex user interfaces. Things which took a week to build five years ago could be built in an afternoon nowadays. On the backend and infrastructure side, cloud-based MPP databases, such as BigQuery and Athena, are dramatically changing the landscape. The ELT approach, when data is transformed inside the database, is getting more and more popular, replacing traditional ETL. Serverless architecture makes it possible to easily deploy and scale applications.

All of these made it possible to build internal alternatives to established services like Mixpanel, Amplitude, or Kissmetrics. In this series of tutorials, we’re going to build a full-featured open-source event analytics system.

It will include the following features:

- Data collection;
- Dashboarding;
- Ad hoc analysis with query builder;
- Funnel analysis;
- Retention analysis;
- Serverless deployment;
- A/B tests;
- Real-time events monitoring;

The diagram below shows the architecture of our application:

![](https://media.graphcms.com/Q37GPFyT72clApTI6gDg)

In the first part of our tutorial, we’ll focus more on how to collect and store data. And briefly cover how to make a simple chart based on this data. The following parts focus more on querying data and building various analytics reporting features.

## Collecting Events
We’re going to use Snowplow Cloudfront Collector and Javascript Tracker. We need to upload a tracking pixel to Amazon CloudFront CDN. The Snowplow Tracker sends data to the collector by making a GET request for the pixel and passing data as a query string parameter. The CloudFront Collector uses CloudFront logging to record the request (including the query string) to an S3 bucket.

Next, we need to install Javascript Tracker. [Here is the full guide](https://github.com/snowplow/snowplow/wiki/javascript-tracker-setup).

But, in short, it is similar to Google Analytics’s tracking code or Mixpanel’s, so we need to just embed it into our HTML page.

```javascript
<script type="text/javascript">      
  ;(function(p,l,o,w,i,n,g){if(!p[i]){p.GlobalSnowplowNamespace=p.GlobalSnowplowNamespace||[];
   p.GlobalSnowplowNamespace.push(i);p[i]=function(){(p[i].q=p[i].q||[]).push(arguments)
   };p[i].q=p[i].q||[];n=l.createElement(o);g=l.getElementsByTagName(o)[0];n.async=1;
   n.src=w;g.parentNode.insertBefore(n,g)}} .  (window,document,"script","//d1fc8wv8zag5ca.cloudfront.net/2.10.2/sp.js","snowplow"));

  window.snowplow('newTracker', 'cf', '<YOUR_CLOUDFRONT_DISTRIBUTION_URL>’, { post: false });
</script>
```
[Here you can find how it is embedded into our example application.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/frontend/public/index.html#L26-L33)

Once we have our data, which is CloudFront logs, in the S3 bucket, we can query it with Athena. All we need to do is create a table for CloudFront logs.

Copy and paste the following DDL statement into the Athena console. Modify the LOCATION for the S3 bucket that stores your logs.

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://CloudFront_bucket_name/AWSLogs/Account_ID/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )
```
Now we are ready to connect Cube.js to Athena and start building our first dashboard.

## Building Our First Chart

First, install Cube.js CLI. It is used for various Cube.js workflows.

```bash
$ npm install -g cubejs-cli
```

Next, сreate a new Cube.js service by running the following command. Note, we are specifying Athena as a database here (-d athena) and template as serveless (-t serverless). Cube.js supports different configurations, but for this tutorial, we will use the serverless one.

```bash
$ cubejs create event-analytics-backend -d athena -t serverless
```

Once run, the `create` command will create a new project directory that contains the scaffolding for your new Cube.js project. This includes all the files necessary to spin up the Cube.js backend, example frontend code for displaying the results of Cube.js queries in a React app, and some example schema files to highlight the format of the Cube.js Data Schema layer.

The `.env` file in this project directory contains placeholders for the relevant database credentials.  For Athena, you'll need to specify the AWS access and secret keys with the [access necessary to run Athena queries](https://docs.aws.amazon.com/athena/latest/ug/access.html), and the target AWS region and [S3 output location](https://docs.aws.amazon.com/athena/latest/ug/querying.html) where query results are stored.

```bash
CUBEJS_DB_TYPE=athena
CUBEJS_AWS_KEY=<YOUR ATHENA AWS KEY HERE>
CUBEJS_AWS_SECRET=<YOUR ATHENA SECRET KEY HERE>
CUBEJS_AWS_REGION=<AWS REGION STRING, e.g. us-east-1>
# You can find the Athena S3 Output location here: https://docs.aws.amazon.com/athena/latest/ug/querying.html
CUBEJS_AWS_S3_OUTPUT_LOCATION=<S3 OUTPUT LOCATION>
```
Now, let’s create a basic Cube.js Schema for our events model. Cube.js uses Data Schema to generate and execute SQL; [you can read more about it here.](https://statsbot.co/docs/getting-started-cubejs)

Create a `schema/Events.js` file with the following content.

```javascript
const regexp = (key) => `&${key}=([^&]+)`;
const parameters = {
  event: regexp('e'),
  event_id: regexp('eid'),
  page_title: regexp('page')
}

cube(`Events`, {
  sql:
    `SELECT
      from_iso8601_timestamp(to_iso8601(date) || 'T' || "time") as time,
      ${Object.keys(parameters).map((key) => ( `url_decode(url_decode(regexp_extract(querystring, '${parameters[key]}', 1))) as ${key}` )).join(", ")}
    FROM cloudfront_logs
    WHERE length(querystring) > 1
    `,

  measures: {
    pageView: {
      type: `count`,
      filters: [
        { sql: `${CUBE}.event = 'pv'` }
      ]
    },
  },

  dimensions: {
    pageTitle: {
      sql: `page_title`,
      type: `string`
    }
  }
});
```
In the schema file, we create an Events cube. It is going to contain all the information about our events. In the base SQL statement, we’re extracting values from the query string sent by the tracker by using the `regexp` function. Cube.js is good at running transformations such this and it could also materialize some of them for performance optimization. We’ll talk about it in the next parts of our tutorial.

With this schema in place, we can run our dev server and build the first chart. 

Spin up the development server by running the following command.

```bash
$ npm dev
```

Visit [http://localhost:4000](http://localhost:4000), it should open a CodeSandbox with an example. Change the `renderChart` function and the `query` variable to the following.

```jsx
const renderChart = resultSet => (
  <Chart height={400} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    <Axis name="Events.pageView" />
    <Legend position="right" name="category" />
    <Tooltip showTitle={false} />
    <Geom type="intervalStack" position="Events.pageView" color="x" />
  </Chart>
);

const query = {
  measures: ["Events.pageView"],
  dimensions: ["Events.pageTitle"]
};
```

Now, you should be able to see the pie chart, depending on what data you have in your S3.

![](https://media.graphcms.com/MPZmJlTBQKeDk4260AEg)

In the next part, we’ll walk through how to build Conversion Funnels. In the third part we will make a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 3 will cover Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net)

Building an Open Source Mixpanel Alternative. Part 1: Collecting and Displaying Events

In this tutorial, we’ll explore how to add analytical features, such as reporting and dashboarding to an existing e-commerce web application with [Cube.js](https://github.com/statsbotco/cube.js). We’re going to use [Saleor](https://getsaleor.com/) as our e-commerce platform. It is powered by a GraphQL server running on top of Python 3 and a Django 2 framework. Both the storefront and the admin dashboard are React applications written in TypeScript and powered by Apollo GraphQL. We’ll add dashboarding and reporting capabilities to the admin dashboard with the Cube.js.

## Installing Saleor

Saleor server and the admin dashboard are located in a single repo , and the storefront is in the separate repo. For the purpose of the tutorial, we need only the server and the admin dashboard. It comes with very good documentation and detailed installation guide, which you can find here. Please follow the instructions on installation and them come back here.

Saleor server and the admin dashboard are located in a single repo [here](https://github.com/mirumee/saleor). It comes with very good documentation and detailed installation guide, which you can find [here](https://docs.saleor.io/docs/3.x/overview/introduction). Please follow the installation instructions and them come back here.

Once, it is installed, let’s populate our store with example products and orders, as well as the admin account. Run the following command in your terminal.

```bash
$ python manage.py populatedb --createsuperuser
```
Next, start the local server.

```bash
$ python manage.py runserver
```

Open [http://localhost:8000](http://localhost:8000) in your browser and it will show you your storefront. Since in previous step we loaded some example data, it will show you some categories and products inside. In this tutorial we’re going to work with the admin dashboard, which could be found here [http://localhost:8000/dashboard/next](http://localhost:8000/dashboard/next). Notice, `next` in the url - Saleor is updating the admin UI and we’re going to work the latest one. To access admin dashboard enter `admin@example.com` for email and `admin` for password.

This how it should look like after you log in.

![](https://media.graphcms.com/WBiIMVPTYsk53s1E5w3A)

## Installing Cube.js

Now, as we have Saleor up and running we can install Cube.js to start adding analytical features to our e-commerce dashboard. The Cube.js backend is a Node app, which could be run as a separate microservice or in a serverless mode. Let’s create our Cube.js backend instance.

Run the following commands in your terminal

```bash
$ npm install -g cubejs-cli
$ cubejs create saleor-analytics -d postgres
```

The above commands should create a new Cube.js application. Since we are using Postgres with Saleor we set it as a database type (-d). Cube.js use ‘dotenv’ to manage credentials, so we need to update .env file with our database's credentials.

```bash
# Update database credentials in the .env file

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=saleor
CUBEJS_DB_USER=saleor
```
The next step is to tell Cube.js how to convert raw data in the database into meaningful analytics insights. This process is sometimes called data modeling. It is a way to translate business level definitions, such as customer lifetime value into SQL. In Cube.js it’s called schema and schema files are located in ‘schema’ folder. 

Cube.js CLI generates an example schema file - `schema/Orders.js`. Change it’s content to the following.

```javascript
cube(`Orders`, {
  sql: `select * from order_order`,

  measures: {
    count: {
      type: `count` 
    }
  },

  dimensions: {
    created: {
      type: `time`,
      sql: `created` 
    }
  }
});
```

Cube.js uses schema to generate a SQL code, which will run in your database and return a result back. A schema is also used to manage pre-aggregations and cache, you can learn more about it here. 

That is all we need to start our first analytics server. In the project folder run:

``` bash
$ npm run dev
```

It will spin up the development server at [http://localhost:4000](http://localhost:4000). If you open it in the browser it’ll show the codesandbox with an example on how to use an API with React.

Now, as we have Cube.js server up and running the next step is to add analytics section to our Saleor admin dashboard.

## Add Analytics Section to Saleor Dashboard

We’re going to add analytics section to to the Saleor dashboard. The admin dashboard is a React app written in Typescript, which is located in `saleor/static/dashboard-next` folder.  Within this folder create a new folder - `analytics`. it is going to contain all our code for analytics sections. First, we’ll add an analytics page, which is going to be a container for all of our charts.

```tsx
// Create analytics/index.tsx with the following content

import * as React from "react";

import DashboardPage from "./views/DashboardPage";

const Component = () => <DashboardPage />;

export default Component;

// Create analytics/views/DashboardPage.tsx with the following content
import * as React from "react";

import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```

```tsx
// Add analytics route to index.tsx

import AnalyticsPage from "./analytics";

// Add analytics SectionRoute just after Home

<SectionRoute path="/analytics" component={AnalyticsPage} />
```

That should be enough to render our new blank analytics page at [http://localhost:8000/dashboard/next/analytics](http://localhost:8000/dashboard/next/analytics). You can try to open it in your browser. Next, let’s add Analytics item to the left menu. 

First, will add new icon component and then edit AppRoot.tsx to add menu item.

```tsx
// Create icons/Analytics.tsx with the following content

import createSvgIcon from "@material-ui/icons/utils/createSvgIcon";
import * as React from "react";

export const Analytics = createSvgIcon(
  <>
      <g id="Group" transform="translate(-188.000000, -237.000000)">
        <path d="M204,240 L208,240 L208,256 L204,256 Z M198,246 L202,246 L202,256 L198,256 Z M192,250 L196,250 L196,256 L192,256 Z M192,250" />
      </g>
  </>
);
Analytics.displayName = "Analytics";
export default Analytics;
```

```tsx
// Import icon and add new element to menuStructure array in the AppRoot.tsx

import Analytics from "./icons/Analytics";

// ...

const menuStructure: IMenuItem[] = [
  // ...
  {
     ariaLabel: "analytics",
     icon: <Analytics />,
     label: i18n.t("Analytics", { context: "Menu label" }),
     url: "/analytics"
  }
]
```

That’s it! Open admin dashboard in your browser and you should see the new menu item. Now, let’s add some charts to our Analytics Page.

![](https://media.graphcms.com/wRdN1FCnRaiECchciHAS)

## Create First Char

Let’s create a ChartCard React Component in `analytics/components/ChartCard.tsx`.  It will receive a query as an input and will encapsulate getting data from Cube.js API and then rendering the visualization. 

We need to install Cube.js javascript client with React wrapper and Bizcharts for visualizations. Bizcharts is easy to use visualization library for React apps based on G2. It comes with pretty nice default styles. Run the following commands in the Saleor root directory:

```bash
$ npm install @cubejs-client/core @cubejs-client/react bizcharts moment numeral --save
```

Our Cube.js server is running on [http://localhost:4000](http://localhost:4000) and API endpoint is located at [http://localhost:4000/cubejs-api/v1](http://localhost:4000/cubejs-api/v1), so we need to specify it in the apiUrl parameter when initializing Cube.js client instance. It also expects an API key, which you can copy from Cube.js server console output when you start it.

![](https://media.graphcms.com/eMpLqJUnTnetsgNmNvxD)

```tsx
// Create analytics/components/ChartCard.tsx with the following content

import * as cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import Card from "@material-ui/core/Card";
import { Axis, Chart, Geom, Tooltip } from 'bizcharts';
import * as moment from 'moment';
import * as React from "react";

import CardTitle from "../../../components/CardTitle";
import Skeleton from "../../../components/Skeleton";

const CHART_HEIGHT = 400;
const API_KEY = 'YOUR-CUBE-JS-API-KEY';
const API_URL = 'http://localhost:4000/cubejs-api/v1';
const cubejsApi = cubejs(API_KEY, { apiUrl: API_URL });

const renderChart = (resultSet) => (
  <Chart
    scale={{ category: { tickCount: 8 } }}
    height={CHART_HEIGHT}
    data={resultSet.chartPivot()}
    forceFit
  >
      <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
      {resultSet.seriesNames().map(s => (<Axis name={s.key} />))}
      <Tooltip crosshairs={{type : 'y'}} />
      {resultSet.seriesNames().map(s => (<Geom type="line" position={`category*${s.key}`} size={2} />))}
  </Chart>
);

const ChartCard = ({ title, query }) => (
  <Card>
    <CardTitle title={title} />
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={ ({ resultSet }) => {
        if (!resultSet) {
          return (
            <div style={{ padding: "10px" }}>
              <Skeleton />
            </div>
          )
        }

        return renderChart(resultSet);
      }}
    />
  </Card>
);

ChartCard.displayName = "ChartCard";
export default ChartCard;
```

Now, let’s add our first chart in DashboardPage using ChartCard component.

```tsx
// Replace the content of analytics/views/DashboardPage.tsx with the following

import * as React from "react";

import CardSpacer from "../../components/CardSpacer";
import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

import ChartCard from "./../components/ChartCard";

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
    <CardSpacer />
    <ChartCard
      title={i18n.t("Orders Last 30 Days")}
      query={{
        measures: [ "Orders.count" ],
        timeDimensions: [{
          dateRange: ['2019-01-01', '2019-01-31'],
          dimension: 'Orders.created',
          granularity: 'day'
        }]
      }}
    />
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```

That will render our first chart at [http://localhost:8000/dashboard/next/analytics](http://localhost:8000/dashboard/next/analytics).

## Add More Charts

First, let’s add support for other visualizations, such as a pie chart. The query to Cube.js will remain the same, we just need to support the pie chart in the `renderChart` methods. Also, we’ll add support for currency formatting using numeraljs. 

```tsx
// Replace the content of analytics/components/ChartCard.tsx with the following

import * as cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import Card from "@material-ui/core/Card";
import { Axis, Chart, Coord, Geom, Legend, Tooltip } from 'bizcharts';
import * as moment from 'moment';
import * as numeral from 'numeral';
import * as React from "react";

import CardTitle from "../../../components/CardTitle";
import Skeleton from "../../../components/Skeleton";

const CHART_HEIGHT = 400;
const API_KEY = 'YOUR-CUBE-JS-API-KEY';
const API_URL = 'http://localhost:4000/cubejs-api/v1';
const cubejsApi = cubejs(API_KEY, { apiUrl: API_URL });

const formatters = {
  currency: (val) => numeral(val).format('$0,0'),
  date: (val) => moment(val).format("MMM DD"),
  undefined: (val) => val
}
const renderLine = (resultSet) => (
  <Chart
    scale={{ category: { tickCount: 8 } }}
    height={CHART_HEIGHT}
    data={resultSet.chartPivot()}
    forceFit
  >
      <Axis name="category" label={{ formatter: formatters.date }} />
      {resultSet.seriesNames().map(s => (<Axis name={s.key} label={{ formatter: formatters[resultSet.loadResponse.annotation.measures[s.key].format] }} />))}
      <Tooltip crosshairs={{type : 'y'}} />
      {resultSet.seriesNames().map(s => (<Geom type="line" position={`category*${s.key}`} size={2} />))}
  </Chart>
);

const renderPie = (resultSet) => (
  <Chart height={CHART_HEIGHT} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    {resultSet.seriesNames().map(s => (<Axis name={s.key} />))}
    <Legend position="right" name="category" />
    <Tooltip showTitle={false} />
    {resultSet.seriesNames().map(s => (<Geom type="intervalStack" position={s.key} color="x" />))}
  </Chart>
);

const renderChart = (resultSet, visualizationType) => (
  {
    'line': renderLine,
    'pie': renderPie
  }[visualizationType](resultSet)
);

const ChartCard = ({ title, query, visualizationType }) => (
  <Card>
    <CardTitle title={title} />
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={ ({ resultSet }) => {
        if (!resultSet) {
          return (
            <div style={{ padding: "10px" }}>
              <Skeleton />
            </div>
          )
        }

        return renderChart(resultSet, visualizationType);
      }}
    />
  </Card>
);

ChartCard.displayName = "ChartCard";
export default ChartCard;
```

Now, let’s define more measures and dimensions in the Cube.js schema. Open your Cube.js Service project and edit the Orders.js file in the schema folder.

```javascript
// Replace the content of schema/Orders.js with the following:

cube(`Orders`, {
  sql: `select * from order_order`,

  measures: {
    count: {
      type: `count`
    },

    totalNet: {
      sql: `total_net`,
      type: `sum`,
      format: `currency`
    },

    averageValue: {
      sql: `total_net`,
      type: `avg`,
      format: `currency`
    }
  },

  dimensions: {
    created: {
      sql: `created`,
      type: `time`
    },

    status: {
      sql: `status`,
      type: `string`
    }
  }
});
```

We have added two new measures — total sales and average sales. And one new dimension — status.

Now, let’s define all new queries in the DashboardPage component. We will display the line charts for total orders, total sales, and average order value (per day), and pie chart for the breakdown of order count by status. The default date range is set to last 30 days.

```tsx
// Replace the content of analytics/views/DashboardPage.tsx with the following
import * as moment from 'moment';
import * as React from "react";

import CardSpacer from "../../components/CardSpacer";
import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

import ChartCard from "./../components/ChartCard";

const cardContainerStyles = {
  display: "grid",
  gridColumnGap: "24px",
  gridTemplateColumns: "1fr 1fr",
  rowGap: "24px"
}

const timeDimensions = [{
  dateRange: [
    moment().subtract(30, 'days').format("YYYY-MM-DD"),
    moment().format("YYYY-MM-DD")
  ],
  dimension: 'Orders.created',
  granularity: 'day'
}];
const queries = [
  {
    query: {
      measures: ["Orders.count"],
      timeDimensions
    },
    title: i18n.t("Total Orders"),
    visualizationType: 'line',
  },
  {
    query: {
      measures: ["Orders.totalNet"],
      timeDimensions
    },
    title: i18n.t("Total Sales"),
    visualizationType: 'line'
  },
  {
    query: {
      measures: ["Orders.averageValue"],
      timeDimensions
    },
    title: i18n.t("Average Order Value"),
    visualizationType: 'line'
  },
  {
    query: {
      dimensions: ["Orders.status"],
      measures: ["Orders.count"]
    },
    title: i18n.t("Orders by Status"),
    visualizationType: 'pie'
  }
];

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
    <CardSpacer />
    <div style={cardContainerStyles}>
      {queries.map((query, index) => (
        <ChartCard key={index} {...query}  />
      ))}
    </div>
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```
This will give us a fully working dashboard with four basic metrics. In the next tutorial, we’ll show you how to add dashboard filters and a query builder to let users build custom reports.