Build Dimensions and Load Data Using SQL

Using SQL, you can import a table to an RDBMS server, create dimension build and data load rules, connect to the RDBMS, and load dimensions and data to a cube.

Before you begin, you will need the following resources.

Access to an Essbase cloud service instance
Access to an RDBMS server
Oracle SQL Developer, or another SQL integrated development environment
A tabular data file. For example, in this exercise, consider a sample_basic_table.csv file that contains columns of data, as in the following abbreviated representation (many rows are omitted):

Figure 10-1 Tabular Data File to Import to Relational Database
A valid OCI or DSN-less connection string, as listed below. For both connections, you do not have to edit odbc.ini. Essbase makes the connection using the ODBC driver.

Connection Strings

The available OCI and DSN-less connection string types are listed, with syntax and examples.

Oracle Call Interface (OCI)

Syntax: $Keyword$DatabaseServerName:PortNumber/SID

Example: $OCI$mydsn01:1521/ORCL

Oracle Database (DSN-less)

Syntax (SID): oracle://HostName:PortNumber/SID

Example (SID): oracle://somedb99:1234/ORCL

Syntax (ServiceName): ORACLESERVICE:oracle://HostName:PortNumber/Servicename

Example (ServiceName): ORACLESERVICE:oracle://somedb99:1234/esscs.host1.oraclecloud.com

Microsoft SQL Server (DSN-less)

Syntax: sqlserver://HostName:1433:DBName

Example: sqlserver://myMSSQLHost:1433:myDbName

DB2 (DSN-less)

Syntax: db2://HostName:PortNumber:DBName

Example: db2://myDB2Host:1234:myDbName

MySQL (DSN-less)

Syntax: mysql://HostName:3306:DBName

Example: mysql://someHostName:3306:myDbName

Once you have all the prerequisite information listed in this topic, you can perform the tasks of building dimensions and loading data using SQL.

No members from a CellProperties dimension should be included in tabular data or in the headers of the SQL-based load rule files.