Data Selections

ADVANCED TOPIC

Linking data from multiple databases is an advanced database discussion that would normally be beyond the scope of DSF.

There are many reason why we would discourage this, however we also understand that sometimes there are extraordinary circumstances which require extraordinary solutions.

If you're up for the challenge, so are we.

There are 2 types of data linking; Combining records from Views with the same fields (Combine Method) or joining records from Views linked by some common reference field (JOINing Method).

Note: You will not have the ability to use these methods if your DSF database was created on the Data Selections Cloud Servers. This will only work with a local DSF Database.

DSF easily and conveniently manages data elements from a single database.

Start by treating each linked database individually.

Use DSF and discover the data in DB1.

Then create a second Data Source to DB2 and discover its data.

Combine Method

To combine like data from more than one database, enter DB1 and create a View. Select and alias the names of the fields you wish to combine.

Publish the View to the DSF database. Name it uniquely (i.e. DB1_View)

Next, switch to the DB2 database in DSF. Select and alias the names of the fields you wish to combine.

Important: Make sure you use the same alias names for the fields as you did from DB1 and make sure you have the same number of fields.

Publish the View to the DSF database. Name it uniquely (i.e. DB2_View)

Note: Because you're publishing views to the same DSF database make sure the publish view names are unique. Otherwise, the DB2 published view will replace the DB1 published view. The goal here is to have 2 separate views pointing to the two different databases.

You should now have 2 distinct database views in the DSF database.

Now create your combined database view in the DSF database manually outside of the DSF system.

The combined database view is the view you will connect to your reporting tool.

CREATE VIEW My_Combined_View AS 
SELECT * FROM dbo.dsf_DB1_View
UNION ALL
SELECT * FROM dbo.dsf_DB2_View

Database best practices state to never use a SELECT * in a query. This is the exception to the rule. 

Most of the listed reasons why NOT to use the SELECT * are exactly the same reasons as to why we want to use SELECT * here.

Don't worry! The database police will not show up and arrest you. It's a best practice not a law.

Because we use the SELECT * in the combined database view we never have to edit it when we later change the individual View fields in DSF.

IMPORTANT NOTE: If you edit the views in DSF and re-publish them, you MUST keep the field lists the same.

You can change the original System Field of an aliased selection but the resulting field list count and alias names MUST always match between the 2 published views.

JOINed Method

Joining data from more than one database is similar to the Combine Method described above, enter DB1 and create a View. Select and alias the names of the fields you wish to join. However, this time you MUST include a field that you will link the views together. Regardless of the field name in DB1 it's best to alias the link field to something like: DB1_ID

Publish the View to the DSF database. Name it uniquely (i.e. DB1_View)

Next, switch to the DB2 database in DSF. Select and alias the names of the fields you wish to join.

Unlike the Combine Method you don't need to have the same number of fields and you don't want the alias names to be the same.

Like DB1 , make the link field alias something like: DB2_ID

Publish the View to the DSF database. Name it uniquely (i.e. DB2_View)

Note: Because you're publishing views to the same DSF database make sure the publish view names are unique. Otherwise, the DB2 published view will replace the DB1 published view. The goal here is to have 2 separate views pointing to the two different databases.

Important: Make sure all of the alias names are unique between DB1_View and DB2_View.

You should now have 2 distinct database views in the DSF database.

Now create your joined database view in the database manually outside of the DSF system.

The joined database view is the view you will connect to your reporting tool.

CREATE VIEW My_Joined_View AS 
SELECT V1.*,V2.* FROM dbo.dsf_DB1_View V1
LEFT JOIN dbo.dsf_DB2_View V2 on V1.DB1_ID = V2.DB2_ID

Again, database best practices state to never use a SELECT * in a query. This is the exception to the rule. 

Most of the listed reasons why NOT to use the SELECT * are exactly the same reasons as to why we want to use SELECT * here.

Because we use the SELECT * in the joined database view we never have to edit it when we change the individual View fields in DSF.

IMPORTANT NOTE: If you edit the views in DSF and re-publish them, you MUST not change the ID alias names that link them together.

You can add and remove any other fields from the views.