SQL warehousing — Working modes for data flows

In most cases, offline mode is recommended for data flow design. The online mode is a useful shortcut if you do not have a physical data model that contains the tables that you want to reference in the flow.

Offline mode

This mode implies that you plan to use an existing physical data model when you design the flow, or that you intend to create a model that you can use. In most cases, this mode is recommended because you do not need a live database connection and you can create and update the model independently during the design phase. You can easily create a model by reverse engineering from a database or a script.

Online mode

This mode requires a database connection. The connection is used to dynamically build a physical data model that you can use when you design the flow. In most cases, this mode is not recommended because the model must be refreshed to remain synchronized with the live database. You cannot make manual changes to this dynamic data model. However, online mode is a useful shortcut if you do not have a readily available physical data model.

The only consequence of choosing online mode is the creation of a dynamic data model that you can select from when you are defining data flow operators. In all other respects, the choice of working mode has no impact on the design process.

Note: If you are working with an online flow and close it, you will be prompted to connect to the database for that flow when you reopen it. If you choose offline mode when you reopen the flow (or when you restart the Design Studio), you can access a cached version of the database model from the previous session, and any manually created physical data models that are in your project or referenced by your project.

By default the data model associated with an online flow is automatically refreshed when the flow is reopened, and the refresh process can be very time-consuming if the database is very large. You can set a preference to disable the automatic refresh behavior and continue to use the data model that was created or updated during the previous session. In the Window menu, select Preferences > Data Warehousing > Data /Mining Flows > Database Refresh and clear the check box.

When you take any action that moves a data flow into the online mode, the database model is refreshed. You will see a status bar in the bottom-right corner of the Design Studio when the model is being created or updated.

Restriction: Do not rename or delete a dynamic data model. You can use these models for standard operations such as accessing metadata for database objects, impact analysis, and so on, but deleting or renaming a model can cause unpredictable results, including corruption of the .dbm file.

Refresh Database, Connect/Disconnect, and Set Database options

Refresh Database

To refresh the physical data model that was created dynamically when you chose the online mode for a data flow, click the Refresh icon in the toolbar above the data flow canvas: Refresh Database icon

If the database is very large, the refresh process can be time-consuming.

Connect/Disconnect

To connect or disconnect a database that is associated with an online flow, click the icon in the toolbar above the data flow canvas, which toggles between two actions: connect ( Connect icon ) and disconnect ( Disconnect icon ).

The main purpose of this icon is to provide a means of reconnecting to a database when the connection is closed or lost for some reason. The editor reports the error, then puts the flow in the disconnected state. In this state, you can still work with the flow by using the dynamically generated data model, but the model cannot be refreshed until the database connection is restored.

You can also use the Data Flow menu to take the same actions by choosing Switch to online mode or Switch to offline mode.

Set Database

To attach the data flow to a different database or detach the current database, right-click the data flow in the Data Project Explorer and select Set Database.