Magic tables in 1010data

Magic tables allow you to use the power of 1010data queries within SQL tools such as Power BI and Tableau.

Magic tables look like regular tables to SQL tools, but use logic that is difficult to express in SQL. SQL does use stored procedures and table-valued functions to express more complex queries, but stored procedures and table-valued functions have disadvantages. BI tools do not offer a standardized way to visualize stored procedures. Table-valued functions are not intuitive enough to use in BI tools because you have to know the parameters in advance.

Magic tables look like regular tables in BI tools, but are actually SQL views that can be parameterized. The parameters are then supplied by the SQL SELECT and WHERE clauses.

Magic table creation consists of several steps. First, you need to create a library containing blocks that describe the underlying logic of the magic table. Then, you need to create a SQL view that calls the blocks in the library and contains the magic table parameters.