How to link the databases ?

The cssXX.dashboards_store/dbt_project.yml exposes a databases section in which you will populate the database names of the databases you want to link the Store to.

Basically, the process to add a database goes as follows:

1. Open the cssXX.dashboards_store/dbt_project.yml file.
2. Replace the database placeholder with the database Fully Qualified Name.

# cssXX.dashboards_store/dbt_project.yml
vars:
    database_bi: Bi distribution's FQN 
    database_paie: GHRPAIE's FQN
    database_gpi: GPI's FQN 
    database_jade: JADE's FQN

What's the FQN of my database ?

The FQN of a database is the string you would use to reference the database in a SQL query.

SELECT * 
FROM server.database.schema.table

Here, server.database is the FQN of the database.

When the database is colocated on the same server you are running your query from, the server can be dropped. The FQN of the database is then simply database.

So if the database you want to add is colocated on the same server as store and store_dev, use the database name. If the database you want to link lives on a far, far away linked server, the FQN is the concatenation of the linked server name, the dot (.) and the database name.

The database name will be interpolated at runtime in the interfaces SQL files fetching information from this database.

The next code block shows a configuration of cssXX.dashboards_store/dbt_project.yml with a colocated GRHPAIE database and a GPI database accessed through a linked server.

vars:
    # The source database to pull the data for a css.
    database_paie: "paie" # The DB is colocated, so a direct reference is enough
    database_gpi: "[dataserver/IA].[gpi]" # The GPI database lives on the linked server "dataserver/IA"

The "My mommy says I'm special" section : I'm using Azure SQL Server

Azure SQL Server does not support linked servers. Worse, Azure SQL Database has trouble with cross-database communications. In that case, the data must be pulled from the source database to the Store's database in a dedicated schema. You can either use a tool like Airbyte or Fivetran to move the data, or you can override the cssXX.dashboards_store/models/interfaces models to add a call to the stage_external macro. The stage_external macro relies on the creation of an EXTERNAL table in the Store's database. It's basically the despicable child of a linked server and a view.

TODO : add documentation.