# Dynamic Data Sources

> Point your dashboards/reports to different data source dynamically.

:::warning Model Persistence
Dynamic Data Source does not support Query Model Persistence. Please check your data model persistence before using this feature.
:::

## Use Case

In some scenario, you want to **point the dashboards to different data source (database or data warehouse) dynamically**, based on who's viewing the report or whether it's in production or dev mode.

![Dynamically pointing Holistics to different data sources](https://cdn.holistics.io/product/modeling-dynamic-ds-new-20241021-332.png)

Thanks to its programmable nature, Holistics can support this capability natively. This will enable popular use cases such as:
- **[Clients Dashboarding](#client-dashboarding)**: Build the same set of models/datasets/dashboards for clients but different data source for each client underneath.
- **[Dev/Prod environment](/docs/continuous-integration/dynamic-dev-prod-environment.md)**: Dynamically switch the underlying data sources (from dev → prod and vice versa) based on the environment that analysts are working on
- **[Dynamic data sources for embedded analytics](/embedded/dynamic-data-sources.md)**: Embedded analytics but different customers use different databases.

<figure class='video-container'>
  <iframe width="560" height="315" src="https://www.youtube.com/embed/_rS7fXbuS2g?si=x-HoEzUjKbMDbJ4p" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
</figure>

## Approach

In Holistics, you can **specify a function/expression in the data source definition** (of dataset or data model).

```tsx
// In a dataset
Dataset sales {
  models: [ orders ]
  // highlight-next-line
  data_source_name: function_or_expression_here
}

// Or in a data model
Model orders {
  // highlight-next-line
  data_source_name: function_or_expression_here

  dimension order_id { ... }
  dimension user_id { ... }
  ...
}
```

The below example use an "if expression" to tell Holistics to use different database when doing development vs when being published.

```tsx
Dataset sales {
  label: 'Dynamic Client Dataset'
  models: [ ... ]
  relationships: [ ... ]
  
  // highlight-start
  data_source_name:
    if (H.git.is_production) {
      'production_data_source'
    } else if (H.git.current_branch == 'staging') {
      'staging_data_source'
    } else {
      'development_data_source'
    }
  // highlight-end
}
```

### Some notes

- **Ensure data sources connected:** You need to make sure you have defined the 3 data sources named `production_data_source`, `staging_data_source`, and `development_data_source`.

  ![](https://cdn.holistics.io/product/modeling-connected_source-20250124-563.png)

- **Run-time evaluation:** The expression is evaluated at run-time (i.e when dashboard is viewed, or queries are generated to send to database.

- `data_source_name` is only available when defining data model or dataset, not dashboard. The dataset's data source will override whatever defined in data model.

- It is recommended to create a **separate file named `database.aml`** to store your dynamic data sources' logic, rather than defining it directly within your dataset or model file. 
  ```typescript
  // database.aml file
  const dynamic_db = if (H.git.is_production) {
                     'production_data_source'
                   } else if (H.git.current_branch == 'staging') {
                     'staging_data_source'
                   } else {
                     'development_data_source'
                   }
  ```

  ```tsx
  // your model file
  Model users {
    // highlight-next-line
	  data_source_name: dynamic_db
  }

  // your dataset file
  Dataset ecommerce {
    // highlight-next-line
	  data_source_name: dynamic_db
  }
  ```

## Available Variables

Dynamic data source expressions can reference the current user and Git environment at runtime. For the full reference, see [AML user attributes and variables](/reference/aml/user-attributes-and-variables).

For example, this setup uses the current user's `data_source` attribute in production, but always uses a development database while modeling:

```aml
Dataset sales {
  // highlight-start
  data_source_name:
    if (H.git.is_production) {
      H.current_user.data_source
    } else {
      'development_data_source'
    }
  // highlight-end
}
```

## Example: Dynamic Data Source at User Level {#client-dashboarding}

Suppose you have many different customers who want the same set of reports. You maintain different data source for each customer. You want each customer when logging in to Holistics will be able to see the same reports but pointing to their respective database.

Here's how you can utilize user attributes in Holistics to achieve that.

![dynamic data source high-level solutions](https://cdn.holistics.io/product/modeling-dynamic-source-high-level-20240830-214.png)

### 1. Connect databases

First, **[connect](/docs/connect/)** to all of your customers' databases.

### 2. Define new user attribute & set values for each user

Go to Users (or Group Management) in Holistics, define a new attribute named `data_source` (or any name you prefer; the attribute name just needs to match what you reference in your AML code). You can do this either at the user level or user group level.

![user attributes setup](https://cdn.holistics.io/product/modeling-user-attribute-setup-20240830-215.png)

Once done, to go each user and set the corresponding `data_source` value for them. For more information, refer to [User Attributes](/docs/admin/user-attributes)

### 3. Write dynamic code to set data source

```tsx
Dataset dynamic_client_dataset {
	label: 'Dynamic Client Dataset'
	
	// The underlying data source will be dynamically switched based on who use it 
	// highlight-next-line
    data_source_name: H.current_user.data_source
	
	models: [ revenue ]
	relationships: [ ]
}
```

### 4. Making sure that it works as expected

To make sure your setup work correctly, go to **"Organization Settings > View and Edit as"** under App Settings to test with each account.
For more details, please refer to [Log In As Another User](/docs/admin/impersonation)

![](https://cdn.holistics.io/product/modeling-view-edit-as-20241009-310.png)

By selecting a particular user or user group, the corresponding value of their user attribute (in this case, the data source name) will be applied to the `data_source_name` property of the dataset (and model). This allows the same dashboards to display different data for different users.

:::info Important note
Please be aware that testing this setup in the Development environment is not possible, particularly for users without access to the Modeling layer (such as Explorers or Viewers). 

Therefore, you must first publish your changes to the Production environment before utilizing the **"View and Edit as"** option.
:::