Challenge 5: Business Intelligence

Previous Challenge Next Challenge

Introduction

Business intelligence (BI) in data warehousing involves using tools and techniques to analyze the massive amounts of data stored in a data warehouse to extract meaningful insights, identify trends, and support better business decision-making. In other words, we translate raw data into actionable information. We can get that information by running SQL queries, but we can also create visual dashboards using a visualization tool, such as Looker or Looker Studio, to achieve the same.

Description

We’re going to create a new report in Looker Studio. Since we’re keeping things simple and Looker Studio works better with an OBT (one big table), we’ll create that as a first step.

Add a new file obt_sales.sqlx in the same folder as the fact table. Configure it to create a new table obt_sales in the dwh dataset by joining all of the dimension tables with the fact table. Add the tag obt to the configuration, configure the table to be partitioned on the order_date column and make sure to exclude all of the surrogate keys.

Once the table is created, create a new Looker Studo report using the new table and configure the following charts:

  • Scorecards for gros_revenue and gross_profit with human readable numbers.
  • Donut chart for gross_revenue broken down by product categories.
  • Map chart showing gross_profit for every city.
  • A line chart showing gros_revenue and gross_profit per quarter (e.g. Y2021Q3).

Success Criteria

  • There’s a new partitioned table obt_sales with 121317 records in the dwh dataset that joins the dimension tables with the fact table as a result of running the Dataform pipeline with the tag obt .
  • There’s a new Looker Studio report with the abovementioned charts.

Learning Resources

Tips

  • There are different types of joins you can do with BigQuery, choose the correct one.
  • You can use Data Types or calculated fields in Looker Studio to get the quarter information in proper format, as usual in Google Cloud there’s more than one way to skin a cat :)

Previous Challenge Next Challenge