Connect to 1010data magic tables

1010data's magic table feature allows you to perform Power BI queries on tables, but take advantage of the underlying power of the 1010data Macro Language.

Magic tables look like regular tables to SQL tools, but use logic that is difficult to express in SQL. A magic table is a SQL view that can be parameterized. It is not necessary to know how to create a magic table in order to use them in Power BI. You just need to know the magic table name and its schema.
Note: To learn more about magic tables, see the 1010data Reference Manual.

To import a magic table into Power BI:

  1. In the Power BI Navigator, select your database and schema.
  2. Select the magic table from the list of tables in the schema.
    Note: Power BI cannot display a preview of magic tables.
  3. Click the check box and click Load to load the magic table into Power BI.
    A Connections settings dialog appears.
  4. Select DirectQuery, and then click OK.
    Power BI loads the magic table and displays the Report () view of Power BI. Power BI displays the magic table's fields just like any other table.
  5. You can now perform queries and visualizations on the magic table as if it were a regular table.
    In the following example, we perform a query that is the equivalent of the SQL statement SELECT * FROM mtdemo.date_hour_cross WHERE year='92' AND state='CA'; on hourly average weather data. The Power BI table looks like the following:

    Behind the scenes, the 1010data Macro Language is pulling data from underlying tables on an ad hoc basis, depending on the filters selected. It is not necessary to know the underlying Macro Language code in order to create Power BI visualizations.