Querying tables that are created monthly as one table (a phone PBX)

Question

I've tried asking this question on a specific Power BI forum but no good:

querying tables that are created monthly as one table (a phone PBX)

So I'm wondering if this can be achieved by me having 1 x SQL server that merges all the data from the tables in the cloud MySQL server. Thus Power BI only queries 1 x table?

The Situation

• I have Power BI reporting on a phone system.
• Data is a MySQL database which my Power BI connects to.
• The PBX platform is from a company called "yeastar"

The Issue

However, the issue is each month this phone system creates a new table. I can't control or change this.

My Power BI connects to 1 month table and all works fine, however I can't then retrieve data for another month because it's in another table. Every month a new table is created I have to make a new Power BI project and connect to that new table.

My Questions

• Can I get the table name as a wildcard follewed by the fields?

• Do I need another SQL server to use as a "transition" so it queries all these table then presents 1 large table to Power BI?

Example Tables / Queries

Screen shot shows the database I'm querying.

Answer 1

It is generally "wrong" to split data up into multiple tables.

A "single" table can PARTITIONed into multiple subtables. If it is set up correctly, you can use "transportable tables" to add a new 'month' to a partitioned table.

What does the BI do? Does it run longer and longer summaries of the data? Of so, then all of this is backward. Instead, summarize each monthly data as it comes in; this data would go into a single table. This summary table will be much faster to read from.

Answer 2

You might want to consider creating a view for your PowerBI query.

The view would be defined as:

CREATE VIEW `cdr.cdr_summaryview` 
(
    id, 
    datetime, 
    clid, 
    src.dst, 
    dcontext, 
    srctrunk, 
    dstrunk, 
    lastapp
) 
AS SELECT 
    id, 
    datetime, 
    clid, 
    src.dst, 
    dcontext, 
    srctrunk, 
    dstrunk, 
    lastapp
FROM `cdr.cdr_202002` 
UNION 
SELECT 
    id, 
    datetime, 
    clid, 
    src.dst, 
    dcontext, 
    srctrunk, 
    dstrunk, 
    lastapp
FROM `cdr.cdr_202003` 
-- /*=============================
--   repeat UNION for all tables 
--   =============================*/
UNION SELECT 
    id, 
    datetime, 
    clid, 
    src.dst, 
    dcontext, 
    srctrunk, 
    dstrunk, 
    lastapp
FROM `cdr.cdr_202109` 

With this approach you can add new monthly tables to the view cdr.cdr_summaryview by just modifying the existing view definition and adding the new table via a new UNION clause.

Your PowerBI query just takes the cdr.cdr.summaryview as a data source instead of the individual monthly summary tables.

Reference:

• CREATE VIEW Statement (MySQL Reference Manual)
• Chapter 24 Partitioning (MySQL Reference Manual)

That said, having the monthly data in separate tables isn't the best idea. Having partitioned data like Rick James pointed out is a better solution.

Answering Your Questions

Can I get the tablename as a wildcard followed by the fields?

No, you can't define a wildcard for a table name.

Do I need another SQL server to use as a "transition" so it queries all these table then presents 1 large table to Power BI?

You can workaround your issue with my suggestion.