Novice Tableau author here. My project got a requirement to develop the Tableau Sales dashboard of 5 years of sales data.

The current year sale data always resides on the OLTP MSSQL database, and all prior year data got archived end of year and all resides in respective database. In summary we got 5 years of data in 5 database.

The challenges are 1) The number of years will vary from customer to customer 2) Same tableau worksheet should work for multiple customer, just pointing to the same MSSQL Instance

All database has the same schema, and what's the best way to bring this data in Tableau?

Best way to bring all the data is to create 5 sheets with 5 different connections to 5 databases and create a dashboard with 5 sheets.

Since all databases have same schema use action filters instead of individual filters to the sheets so that performance can be improved.

To my knowledge, you can't dynamically add the database connections and extract data, So for every customer report need to changed manually.

So solution still holds good because for each customer you will be adding a new connection.

1. In Tableau Desktop: On the Start page, under Connect, connect to a supported file type or supported database type. This step creates the first connection in the Tableau data source.

   In web authoring: From the Home or Explore page, click Create> Workbook to start a new workbook and then connect to your data. This step creates the first connection in the Tableau data source.

2. Select the file or database that you want to connect to, then double-click or drag a table to the canvas.

   

3. In the left pane, under Connections, click the Add button ( in web authoring) to add your second connection to the Tableau data source.

   

   The Cross-database join option is displayed.

   

   Note: If you don't see this option, check that you are using only supported data sources and that you have only two data sources (one file and one database type). Otherwise, the Site Administrator may have set the Cross-Database Joins configuration option to Tableau only.

4. To change how Tableau performs the join, next to the Cross-database join option, click Edit.

5. In the in the Cross-Database Join dialog, select one of the following options, then click OK:

   • Use Tableau or existing databases. This option allows Tableau to choose the fastest option to perform the join - either Hyper or the database you're connected to.

   • Use Tableau only. This option is the default and always uses Hyper to perform the join.

     

     The Cross-database join option changes from the default option, Using Tableau (using Hyper), to the new option Using your database, depending on what you choose.

     

     Important: If you select Use Tableau or existing databases, Tableau chooses the fastest option when performing the join. This behavior is pre-determined by a set of criteria including join types. For instance, Tableau will always choose Hyper for non-inner joins.
     
     If Tableau uses Hyper to perform the join, this process happens in the background and no indicator is shown to identify where the join was performed.

6. Add one or more join clauses by selecting a field from one of the available tables used in the data source, a join operator, and a field from the added table. Inspect the join clause to make sure it reflects how you want to connect the tables.