Swapping the Physical Tables or Queries that Datasets are Based on

When working in the cube design canvas, you can change the underlying table or query that a dataset is pointing to.

About this task

This feature allows you to iterate and develop against a smaller test dataset, then switch to the full dataset when you are ready to publish the cube in production.

To use this feature, you must either:

• Use a table that has the same schema, column names, column order, etc. as the original table.
• Use a query dataset that is limit query on the original table.

To change the table for a dataset to another table or to a query dataset, open the cube design canvas and click the Properties icon on the toolbar for the dataset. Then, follow either of these sets of steps.

Procedure

• To switch the basis of the dataset from a physical table to a query:

  1. Under Dataset Type, select Query.
  2. Under Existing Query, either select an existing query dataset or type the query directly in the Selection SQL field.
  3. Optional: Click Preview SQL Result to see the rows that the query returns.
  4. Click Save.

• To switch the basis of the dataset from a query to a physical dataset:

  1. Under Dataset Type, select Table.

  2. Select the schema and table.

  3. Click Save.

     The Design Center both switches the dataset to the physical table and saves a copy of the query as a new query dataset. The name of the query dataset is the name of the current dataset followed by the text (saved query). If you want to switch back to the query later, follow the first procedure above, selecting the saved query in step 2.