If you operate multiple store fronts or in multiple markets, you may have similar databases stored separately. In Magento BI, it's easy to consolidate similar tables from different databases together.
For example, you may have an
orders table for Market A, and a similar
orders table for Market B. Magento BI can consolidate both tables and allow you to look at the aggregate order data from both Market A and B, in addition to segmenting it by specific market.
For consolidation of tables to work, input tables must be similarly structured. In other words, all input tables must contain the data columns required in the consolidated table.
In this article, we'll touch on some of the most common use cases for consolidated tables and the next steps required for creating your own.
You can use consolidated tables to...
Integrate data from multiple websites
If you sell your products under different brands and websites, it's likely that the tables for each brand or website are similarly structured.
For example, you may have an
orders table for Website A and a separate (but similar)
orders table for Website B. In this situation, it may be useful to consolidate the
orders tables from Website A and B so that you can look at the consolidated revenue and number of orders from Website A and B, in addition to be able to segment metrics by these two websites.
Integrate legacy data
Many companies have refactored their databases at one time or another, and the data from the old database doesn't always get converted over to the new system. You can use consolidated tables to join the key columns from legacy tables with those from the active system. This allows you to conduct a unified analysis of your data throughout history.
Combine events for active user analysis
Imagine a website where users can do several things: take a survey, play a game, make a purchase, refer a friend, etc. Typically, each of these events will be stored in its own table. This makes it difficult to conduct an analysis of how many distinct users took at least one action of any kind in a given time period.
You can utilize consolidated tables to create one unified list of all users and when any of these events took place. You can then run queries on the consolidated table to easily conduct such an analysis.
As with all other tables in our data warehouse, additional columns can be added to power different kinds of charts and analyses.
How do I create a consolidated table?
If you're interested in exploring the possibility of adding a consolidated table to your data warehouse, please reach out to our support team. Note that at this time, only Magento BI support can create consolidated tables.
How do I view or update a consolidated table?
Consolidated tables aren't viewable in the Data Warehouse Manager. So, much like the initial creation of a consolidated table, viewing and updating these tables can only be done by Magento BI support. Please reach out to us for assistance or if you have questions.