Creating a SQL view

The magic table itself is a SQL view with special magic table parameters.

The magic table library, containing blocks of query logic, are at the heart of creating effective magic tables. However, to expose that logic to SQL or to BI tools, you need to create a SQL view that calls the blocks in the library. The SQL view contains special magic table attributes that differentiate it from other SQL views.

The following example shows the steps in creating a SQL view called date_hour_cross. You can perform SQL statements with this view, or load the view into a BI tool such as Power BI and create dashboards with it.

Note: The SQL view must be added to a SQL schema. You can use an existing schema or create a new schema. To create a schema in the SQL Metadata tool, see Create a schema. To create a schema in Macro Language, see <do action_="sql" add_="schema">.

In the example below, we first call <do action_="sql" analyze_="view"> to analyze the Macro Language query defined in the xml variable. The block called in the query, mtdemo.mt_date_hour_cross, is contained in the library that was created in Creating a magic table library. We pass two parameters to the block: sql_select and sql_where. These correspond to the pushdown values in the SELECT and WHERE clauses of the SQL statement, respectively. <do action_="sql" analyze_="view"> returns a dictionary @c that contains the column schema for the view, including the labels of the columns we created in the cross-tabulation.

Note: names_="labels" is the default value in analyze_="view", since column labels usually give more useful information in a SQL view, especially in a tabulation that names columns m0, m1, m2, etc.

In addition, <do action_="sql" analyze_="view"> initializes the variables sql_where and sql_select that will be used in the SQL view.

We then create the SQL view date_hour_cross with <do action_="sql" add_="view">.

Next, we use <do action_="sql" put_="view"> to update the view with the data_ dictionary obtained from the value returned in <do action_="sql" analyze_="view">. We also supply the special magic table attributes in the magic_ dictionary. These attributes are pushdown_where, the list of column names to "push down" into the SQL WHERE clause, and pushdown_select, the list of column names to "push down" into the SQL SELECT clause.

Finally, <do action_="sql" reload_="catalog"/> reloads the entire SQL catalog so that the new view is available in the current session.

<do>
  
 <setv name="xml">
   <quote>
     <import path="adam.mtdemo_library"/>
     <call block="mtdemo.mt_date_hour_cross" 
         selects="{@sql_select}" 
         wheres="{@sql_where}"/>
   </quote>
  </setv>
 
  <do action_="sql" analyze_="view" xml_="{@xml}" names_="labels"
      vars_="{sql_where:{:},sql_select:{:}}" value_="@c"/>

  <do action_="sql" add_="view" schema_="mtdemo" objname_="date_hour_cross"/>

  <do action_="sql" put_="view" 
      schema_="mtdemo" 
      objname_="date_hour_cross"
      data_="{@c}"
      magic_="{pushdown_where:{'year','station','state','tz','unit','measure','metric'},
         pushdown_select:{'year','station','state','tz','unit','measure','metric'}}"/>

  <do action_="sql" reload_="catalog"/>

</do>