Skip to content

Optimized Table Explorer

Query → Optimized Table Explorer (Ctrl+Shift+O) in the Advanced Query Analyzer lists every optimized table you can query, with the columns each one can carry and a description of what it holds. It answers, in one window, the questions that used to require asking us: which of these tables exist, what is in them, and which one holds the field I am looking for.

PULSE Dashboard builds its optimized tables in tempdb under internal names. SQL Server's own schema views cannot see them, so until this release there was no way to browse them from a query window at all.

What the tree shows

The left side is a tree of tables. Expand one to see its columns, and a Supporting tables folder where the table's refresh builds other objects on the way to its own result.

Seven columns describe each entry:

  • Name — the column's field name, or the table's SQL name.
  • Caption — the caption the field picker uses for the same field.
  • Type — the SQL data type.
  • Primary Source — the ERP table the value is read from, for the Macola® version you are connected to.
  • State — Cached, Available, Failed or Restricted. See below.
  • Columns — how many columns the table can carry.
  • Cached — how many of them are in the table at this moment.

The last two counts always describe the whole table, not the part of it a search has narrowed to.

Cached, Available, Failed, Restricted

Optimized tables and their columns are built on demand, so a name appearing here does not mean the data is sitting in tempdb right now. The State column says which case you are in:

  • Cached — in the table now, and safe to SELECT.
  • Available — PULSE Dashboard can build it, but nothing has asked for it yet this session.
  • Failed — the last refresh did not finish, so the table may be missing or stale.
  • Restricted — field-level security applies to this column for your user, and it materializes as NULL.

Everything you are entitled to query is listed whether it is cached or not, because Available is a normal state rather than a problem — the Query Analyzer reads a query before running it and builds the columns the query asks for. Tick Only columns cached right now to narrow the tree to what is already built.

Finding the table a field lives in

The search box above the tree matches table names, column names, captions and source fields at once, and prunes the tree to the hits. A column that matches brings its table on screen with it, which makes this the quickest way to answer "which optimized table holds cus_no?"

Each column of the grid also has its own filter box in the header row, matching on contains, for narrowing by data type or by source table.

The description pane

Selecting anything in the tree describes it on the right.

For a table, that is what it holds, what one row represents, how far back it reaches, which ERP tables it is built from on your Macola® version, which reports use it, and anything worth knowing before you query it. For a column, it is the caption, the data type and the ERP field the value comes from — or, for a calculated column, how it is derived.

Writing a query from it

Insert Name puts the table's full SQL name into the query you are editing, at the cursor. Insert SELECT writes a starter query against it. Double-clicking an entry does the same thing as Insert Name.

Both insert into whichever query tab is in front, so the Explorer can stay open beside the analyzer while you work. With no query open, an insert opens a new one.

Getting started

The Explorer is on the Advanced Query Analyzer's toolbar and its Query menu after the upgrade, with nothing to configure. It reads what is already in memory and never queries the database, so opening it costs nothing and cannot hold up a report.

For everything else new in the Query Analyzer, see Advanced Query Analyzer.