In this article, we'll share tips and tricks to boost your productivity and save time by using just one or two Data Marts for multiple reports. Feel free to share your own experiences in the comments below.
Tip #1: Build Your Data Mart as the Source of Truth
The most important aspect of any report is having accurate and up-to-date data. Raw data often needs to be collected and organized from various sources. There are many ways to streamline data collection and ensure accuracy, but we recommend creating a single Data Mart that is updated daily with cleaned and business-ready data for almost any report.
❌ Not scalable, overly manual solution: | ✅ Scalable and automated solution: |
---|---|
Importing data separately from each source into individual Google Sheets. |
If your data is spread across different systems, your first step should be to consolidate it in one Data Warehouse. If you're reading this article, you likely already have Google Cloud and BigQuery set up. |
Using this data in other Google Sheets to create your reports (say hello to the formula |
Use any available tools to import your data from various sources into BigQuery. For example, with OWOX BI Pipelines, you can deliver data from advertising systems, CRM, and more directly into BigQuery, with over 100 different integrations available. |
Updating numerous Google Sheets to keep the data current. |
Combine this raw data into a single flat data table, check for errors, and clean up duplicates or empty fields if necessary. With OWOX BI Transformations, you can address these tasks using pre-built templates or custom transformations. Additionally, with the Dependency trigger, you can set up a cascade of transformations that run sequentially, resulting in prepared data for your reports. |
Managing so many documents is challenging, and a lot of time is spent finding and fixing errors. |
Simple and intuitive interface to manage all data preparation flow on the OWOX BI Workspace. Essentially, you have created your Data Mart—a single table with prepared data. Use it in the 'OWOX: Reports, Charts & Pivots' extension to set up your reports. |
Tip #2: Create a Data Mart That Results in as Much Data as Possible
Imagine you need to create several reports: today, the manager asks for a sales report by region, tomorrow, a sales report by category, and later, a sales report by advertising campaigns and sources.
❌ Not scalable, overly manual solution: | ✅ Scalable and automated solution: |
---|---|
Creating numerous Data Marts each day to generate the needed reports can quickly lead to a cluttered extension, with each Data Mart containing a unique SQL code. |
As noted in Tip #1, you already have your collected and prepared data in a single Data Mart. In this example, your Data Mart contains complete, business-ready sales data. After running the initial report, use the extension panel to select the desired date range, apply additional field filters as needed, and sort by one or more fields. Then, run the report to get your data slice directly in Google Sheets. |
Manually checking if the data is correctly collected and error-free in each source. |
Your Data Mart is updated automatically every day. Within the OWOX BI Workspace, you can easily control all data imports and preparations. If any error occurs, you will see it on the screen. |
Showing the manager each report and answering their questions. |
Show the result to your manager, who can make changes to the report through the extension panel if needed, such as changing the date range or adding a new filter. |
The manager will likely need more data, requiring changes to the report, meaning you will have to edit the SQL code. |
Even if the manager doesn’t do it themselves, you will be able to make the adjustments much faster than editing the SQL code for the report. |
Tip #3: Write More Flexible SQL Code
To make it easier to customize your reports from a single Data Mart, use our additional features in the extension:
- Parameters: This feature allows you to set a field filter and specify a default value for it. You can then create reports based on this Data Mart and see the list of added parameters in the extension panel. Changing these default values is simple and easy, without needing to edit the SQL code. Learn more about creating parameters in this article.
- Data Mart 'Limit' Option: Instead of writing a 'LIMIT' clause in your SQL code, enable the Limit option and set your desired default value. When creating a new report, you will see this value in the extension panel and can easily update it for each report without changing the SQL code. Learn more about the Data Mart Limit option in this article.
Tip #4: Share your Data Marts with colleagues
Sharing Data Marts in the OWOX extension enables you to collaborate on reports with colleagues, partners, or others who don't have direct access to the raw data in BigQuery. People, who you’ve shared the Data Mart with, can only create/edit their own reports in the OWOX extension. They won’t have access to your GCP project or the SQL code in Query, and they will only be able to see the Data Marts shared with them.
Please share your tips and tricks in the comments that you use with the 'OWOX: Reports, Charts & Pivots' extension, and let us know how they make you more efficient and save you time.
You might be interested in:
0 Comments