Create a Connection and Datasource for Autonomous Data Warehouse

Define a connection and Datasource between Essbase and Autonomous Data Warehouse.

If you will create a federated partition between Essbase and Autonomous Data Warehouse Serverless, use the following topic instead of this one: Create a Connection for Federated Partitions.

To create a global connection, you need to have the service administrator role. To create an application level connection, you need to have user role, plus application manager permission on the application.

• Redwood
• Classic

1. Log in to the Essbase web interface as a service administrator.

2. Click Sources.

   
   
   
   
   

   To define the connection and Datasource at application level, instead of globally, start on the Applications page instead of the Sources page. Click an application name, and then click Sources.

3. Click Create Connection and select Oracle Database.

4. Select Autonomous using the toggle switch.

   
   
   
   
   

5. Enter a connection name.

6. If needed, drag and drop a wallet file, or click the Wallet File field to upload one.

   If you are using a connection which has already been made available to you (a repository connection), you do not need to upload a wallet, because it should already be in the repository. Select the Repository Database option.

   If you need to upload a wallet, obtain a wallet file by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure.

7. Select a service name.

8. Enter your Autonomous Data Warehouse username and password, and optionally, a description.

9. Click Test to validate the connection, and if successful, click Save.

   If you get connection errors, you may need to expand Advanced Options to adjust the minimum and maximum connection pool sizes.

   
   
   
   
   

   Refer to About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

10. Verify that the connection was created successfully and appears in the list of connections.

11. Next, you will create a Datasource for the Autonomous Data Warehouse connection. Click Datasources, and click Create Datasource.

12. From the Connection drop-down box, select the name of the connection you just created; for example, essbaseadb_public. For application-level Datasources, select the application-level connection name, in the format appName.connectionName.

13. Provide a name for the Datasource; for example, essbaseadb_ds.

14. Optionally enter a description of the Datasource; for example, Autonomous Data Warehouse Datasource.

15. In the Query field, provide the appropriate SQL query that selects the Autonomous Data Warehouse data you want to make available in this Datasource.

16. Click Next. If the SQL statement was correct to query an Autonomous Data Warehouse area, the queried columns should display with numeric indices, column names, and data types.

17. Change any additional source-specific data types, if applicable, and click Next.

18. Review the preview panel. The results of the SQL query should fetch some columns of data from Autonomous Data Warehouse.

19. If the preview is correct, click Save to finish creating the Datasource.

1. Log in to the Essbase web interface as a service administrator.

2. Click Sources.

   
   
   
   
   

   To define the connection and Datasource at application level, instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources.

3. Click Create Connection and select Oracle Database.

4. Select Autonomous using the toggle switch.

   
   
   
   
   

5. Enter a connection name.

6. If needed, drag and drop a wallet file, or click the Wallet File field to upload one.

   If you are using a connection which has already been made available to you (a repository connection), you do not need to upload a wallet, because it should already be in the repository. Select the Repository Database option.

   
   
   
   
   

   If you need to upload a wallet, obtain a wallet file by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure.

7. Select a service name.

8. Enter your Autonomous Data Warehouse username and password, and optionally, a description.

9. Click Test to validate the connection, and if successful, click Create.

   If you get connection errors, you may need to expand Advanced Options to adjust the minimum and maximum connection pool sizes.

   
   
   
   
   

   Refer to About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

10. Verify that the connection was created successfully and appears in the list of connections.

11. Next, you will create a Datasource for the Autonomous Data Warehouse connection. Click Datasources, and click Create Datasource.

12. From the Connection drop-down box, select the name of the connection you just created; for example, EssbaseADWS. For application-level Datasources, select the application-level connection name, in the format appName.connectionName.

13. Provide a name for the Datasource; for example, ADW_DS.

14. Optionally enter a description of the Datasource; for example, Autonomous Data Warehouse Datasource.

15. In the Query field, provide the appropriate SQL query that selects the Autonomous Data Warehouse data you want to make available in this Datasource.

16. Click Next. If the SQL statement was correct to query an Autonomous Data Warehouse area, you should see the queried columns populated.

17. Change any additional source-specific parameters, if applicable, and click Next.

18. Review the preview panel. The results of the SQL query should fetch some columns of data from Autonomous Data Warehouse.

19. If the preview looks correct, click Create to finish creating the Datasource.