Tutorial

Add Data Warehouse Instances

  • 29 December 2022
  • 0 replies
  • 454 views
Add Data Warehouse Instances
Userlevel 3
Badge +3

Relates to TimeXtender 6024.1 and later versions

The Data Warehouse Instance, also referred to as the MDW, is the area in TimeXtender where you map data from your ODX, then cleanse, transform, and consolidate this data into a "single version of truth". TimeXtender supports various Storage Types depending on your use case, which you will configure in the steps below. 

Add a Data Warehouse Instance

To create a Data Warehouse Instance, in the TimeXtender Portal go to Data Estate > Instances, click Add InstanceAdd data warehouse.

Basic info

  1. Type a friendly Instance name you want to appear in the portal and desktop application. 
  2. Type an optional Instance description to provide additional context. Consider including the intended storage type and/or environment name.
  3. Log Retention Days box, adjust the number of days execution logs should be kept.
  4. Enable Team Development if you want to allow multiple developers to access the instance at the same time.
  5. Select the Server storage type you plan to use. 

Note: all connection credentials are protected with 256 bit AES encryption

SQL Server storage

  1. In the Server name box, type the name of the server that you want to store the database on. If it is a named instance, type the server name and the instance name.
  2. In the Database box, type the name of the database you want to create.
    • TimeXtender will create this Database for you, later in the Desktop click Edit Instance... and click Create Storage… See below
  3. In the Authentication list, click the mode of authentication you want to use. You have the following options:
    • Windows Authentication: Use the logged-in Windows user's credentials for authentication.
    • SQL Server Authentication: Use a login set up on the SQL Server. Enter the username and password in the corresponding fields.
    • Azure AD Password Authentication: Use Azure AD credentials from a domain that is not federated with Azure AD. Enter the username and password in the corresponding fields.
    • Azure AD Integrated Authentication: Use the logged-in Windows user's credentials for authentication, provided that he is logged in using Azure AD credentials from a domain that is federated with Azure AD.
  4. (Optional) In the Connection timeout box, enter the number of seconds to wait before terminating the attempt to connect to the server. Set it to 0 to wait indefinitely.
  5. (Optional) In the Command timeout box, enter the number of seconds to wait before terminating a command. Set it to 0 to wait indefinitely.
  6. In the Batch size box, enter the number of rows to copy when using ADO.net transfer. '0' equals unlimited.
  7. (Optional) In the Encrypt connection list, you can enable encryption of the connection, which is recommended when you are not in a private network (e.g. when your server is on Azure). You have the following options:
    • No: The communication is not encrypted (default).
    • Yes: The communication is encrypted. The server's certificate is verified by a certificate authority.
    • Yes, trust server certificate: The communication is encrypted. but the server's certificate is not verified. This setting is not recommended for use on public networks.
  8. (Optional) If you need a connection string with properties not found in the UI, you can enter it in the Additional connection properties box. It will override the data in the other boxes with connection information.

Dedicated SQL Pool Storage

Click here to learn how to Use Azure Synapse Dedicated SQL Pool

Snowflake Storage

Click here to learn how to Use Snowflake Data Warehouse Storage

Transfer from ODX

In the last section, you can set how data should be copied from ODX to the data warehouse storage.

  1. In the Technology list, click on the method you want to use for transferring data from the ODX storage to the data warehouse storage.
    • ADO.net: The application-native method. This will use the ADO.net framework on the ODX server to move data. 
    • Azure Data Factory: Will auto generate and ADF pipeline to transfer the data from the ODX Server to the data warehouse storage.
      • This option is recommended if
        • A significant amount of data from ODX data warehouse often
        • Or, your ODX server becomes overloaded during transfer
      • If you select this, you'll be prompted to enter the connection information to the Azure Data Factory you want to use.
      • This option is not currently supported when using Snowflake storage.

Click Save to add your new data warehouse instance.

 TimeXtender Desktop can be refreshed to see the latest changes in the portal right away. 

Edit a Data Warehouse Instance

In the TimeXtender Portal, click Data Estate-> Instances

  1. Click on your instance, it will open a page with instance details
  2. Click on Edit button, edit properties and Save

 TimeXtender Desktop can be refreshed to see the latest changes in the portal right away. 

Delete a Data Warehouse Instance

In the TimeXtender Portal, click Data Estate-> Instances

  1. Click on Delete button next to your instance.  The Delete dialog will be shown.
  2. Enter the instance name to confirm, add a comment (reason) and click on Delete

Create Data Warehouse Storage

Before you can deploy and execute tables in a data area within a data warehouse instance, you must create the data storage.

  1. In TimeXtender Desktop, Right-click on the instance and select Edit Instance.
  2. Click Create Storage... TimeXtender will run the necessary scripts to create a database with the given name.

0 replies

Be the first to reply!

Reply