416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Overview - Virtual Tables

Hide navigation
Modified on Tue, 02 Apr 2013 10:36 AM
Click to return to: Documentation | Defining Virtual Tables

Virtual tables

The goal of the data preparation stage in &dw; is to create one or more virtual tables for business analysts to use when defining Datasets or KPIs. A virtual table is essentially a uniform, tabular view of your physical data.

With Dundas Dashboard, you can create a virtual table by basing it on one or more actual tables from your physical data source. You can optionally perform additional processing on the input data in order to create a more uniform and consistent view. For example, Dundas Dashboard lets you:

  • Join physical database tables together into a single virtual table.
  • Change column names to make them more meaningful and consistent.
  • Group input data and perform aggregation using functions such as SUM, AVG, MIN, and MAX.
  • Hide input columns that you don't need.

The following figure shows an example of how a virtual table (RegionSales) can be constructed from two input database tables (Orders and Region).

A virtual table based on the Orders and Region tables.

A virtual table based on the Orders and Region tables.

You can create a virtual table by using the Virtual Table Designer in Dundas Dashboard. This designer generates a SQL query statement that represents the virtual table itself. Database administrators can choose to override the generated SQL query and replace it with a custom SQL query if desired.

See Creating a Virtual Table.


The following figure illustrates how virtual tables fit into the overall &dw; workflow.

Workflow for virtual tables.

Workflow for virtual tables.

Adding data structures

When you create a virtual table, a wizard takes you through the steps of choosing the input data structures (⪚ database tables, views, functions, or stored procedures). You can also update the current set of data structures after the fact from the Data Sources tab of the Virtual Table Designer.

See Adding or Removing Data Structures.

Defining relationships

If your virtual table is based upon two or more data structures, you must define relationships between those data structures. For example, you can add a relationship to specify that two data structures are to be combined using an inner join. Relationships are defined in the Data Sources tab of the Virtual Table Designer.

See Defining Relationships.

Defining columns

Use the Columns tab in the Virtual Table Designer to configure the attributes of the columns of your virtual table. For example, you can:

  • Change the friendly name of a column.
  • Change the description of a column.
  • Hide a column (&ie; exclude it from your virtual table).
  • Group data rows based on a column.
  • Summarize data by applying an aggregate function.
  • Define a filter on a column.
  • Reorder columns.
  • Add a custom column based on a constant value, mathematical expression, or a counter.
  • Define primary keys and indexes on cached virtual tables.

See Defining Columns.

Previewing a virtual table

Use the Preview tab in the Virtual Table Designer to view the SQL query representing your (standard) virtual table and preview a subset of the resulting data. You can use this preview option to quickly verify the definition of your virtual table. From the Preview tab, you can also modify or replace the generated SQL query with your own custom query.

See Previewing a Virtual Table.

Related topics

Click to return to: Documentation | Defining Virtual Tables

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.