Re-wire the data flow
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 you how to:
- Replace a data cube select transform with data from another data connector.
- Re-wire a metric set to use data from a different data cube or another data source.
These examples make use of the data cubes created in Using a data cube to join tables, and an Excel spreadsheet. For simplicity, the Excel sheet Person is a copy of the [Person].[Person] SQL table with the addition of one column, LegalFirstName.
2. Replace a select transform
Open the data cube you would like to rewire. The following steps will replace the SQL Select with a prepared Excel table.
In the Explore window, locate the replacement Excel table and drag it over the SQL Select transform.
In the Re-Wire Elements dialog, use the Native Source Element Name drop-downs to match the existing input connectors to the new source elements.
Click the submit button at the bottom of the dialog when done.
Observe that the SQL Select is replaced with a Tabular Select. The Join and Process Result transforms remain intact, and maintain all of the previously applied settings.
2.1. Edit the input elements
You can edit the matching of elements afterward. In the above example, we re-wired the data cube to use an Excel table instead of an SQL one, and we matched the old FirstName column with our new FirstName column. We could now change this to use the LegalFirstName column instead.
Double-click the new Tabular Select to configure it, then scroll down and click Edit input elements.
Notice above that LegalFirstName is currently unused from our example. We can locate FirstName in the Connector Name column and change the drop down to select LegalFirstName.
Submit the dialog.
Observe that the hierarchy is still named FirstName, while the data comes from the LegalFirstName column.
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 from its Data Analysis Panel.
In the 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.