Re-wire the data flow
1. Overview
This article shows how to change the data sources and structures used behind existing data cubes and metric sets. This allows you, for example, to migrate to a new database without having to rebuild the dashboards.
The following examples show how you can:
- Drag data from a different data connector onto a data cube select transform to replace it.
- Replace the data cube or data connector used as a data source for a metric set.
2. Replace a select transform
Open the data cube you would like to rewire. As an example, the data cube pictured below contains a SQL Select referring to a database table.
In the Explore window, drag a table or other data structure from a different data connector that contains similar data, then drop it onto the select transform you want to replace it with.
In this example, we are dragging a sheet from under an Excel data connector.
In the Re-Wire Elements dialog that appears, use the drop-downs on the right to select the matching element from your new data source for each existing element, or confirm that their existing selections are correct.
Click the submit button at the bottom of the dialog when done.
In our example, the SQL Select is now replaced with a Tabular Select referring to the Excel file data. The Join and Process Result transforms to the right remain intact along with their previously applied settings.
The Process Result's element names remain the same for compatibility with metric sets using them.
2.1. Edit the input elements
You can edit the matching of elements afterward.
Double-click the new select transform to configure it, then scroll down and click Input elements.
In our example from the previous section, the Excel file had an extra LegalFirstName column not present in the original database table, which is why it is initially unchecked in the select transform configuration as shown above.
We can locate the row for FirstName in the Input Elements dialog and change its drop down to match it instead with LegalFirstName.
Submit the dialog.
The hierarchy is still named FirstName although the data now comes from the LegalFirstName column.
2.2. Re-wiring a manual select's data connector
If you are currently using a Manual Select in your data cube with your own SQL or MDX query, dragging a table, sheet, or OLAP cube onto the Manual Select as shown above will replace it and your query with a SQL Select or MDX Select that provides the specific data you dragged as input.
In version 23.3 and higher, you can also maintain your Manual Select transform and its query, but change the data connector it executes against: drag a data connector from the Explore window and drop it onto the Manual Select transform. This operation requires that the same elements be returned when running the query against the new data connector as with the original data connector.
3. Re-wire metric set elements
To replace the data cube or source used behind a metric set, open it in the full screen editor from the main menu or Explore window, or choose this option from the Data Analysis Panel or from Data Tools in the toolbar when using the metric set on a dashboard.
In the metric set's toolbar, choose Data Tools, then Replace Source Cube.
Select a data cube, or you can choose another data source such as database table or spreadsheet and a data cube will be auto-generated for it. Click the submit button at the bottom of the dialog.
In the Re-Wire Source Cube dialog, use the Replacement Structure Element Name drop-downs to match the existing elements to the new source elements.
Submit the dialog when done.
A dialog will appear confirming that the source was replaced. The metric set's elements and their settings remain intact.
