This task flow demonstrates how to import a table to an RDBMS server, create Essbase dimension build and data load rules, connect to the RDBMS, and load dimensions and data using SQL to an Essbase cube.
What you will need:
• Access to an RDBMS server
Chapter 6
• 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 in (many rows are omitted):
Figure 6-1 Tabular Data File to Import to Relational Database
• Access to an Essbase cloud service instance
• A valid OCI or DSN-less connection string. For both OCI and DSN-less connections, you do not have to edit odbc.ini. Essbase will still make the connection using the ODBC driver. For examples of connection strings, see the table:
Table 6-1 Connection Strings
Connection Type Syntax Example
Oracle Call Interface (OCI) $Keyword$DatabaseServerNa me:PortNumber/SID
$OCI$mydsn01:1521/ORCL
Oracle DB (DSN-less) oracle://
HostName:PortNumber/SID oracle://somedb99:1234/ ORCL Microsoft SQL Server (DSN- less) sqlserver://HostName: 1433:DBName sqlserver://myMSSQLHost: 1433:myDbName DB2 (DSN-less) db2:// HostName:PortNumber:DBNam e db2://myDB2Host: 1234:myDbName Chapter 6
Table 6-1 (Cont.) Connection Strings
Connection Type Syntax Example
MySQL (DSN-less) mysql://HostName: 3306:DBName
mysql://someHostName: 3306:myDbName
Using the above resources, you can complete the following task flows: • Loading Dimensions Using SQL
• Loading Data Using SQL
Loading Dimensions Using SQL
This task flow demonstrates how to import a table to a RDBMS server, create
dimension build load rules, connect to the RDBMS, and load dimensions using SQL to a cube.
1. In Oracle SQL Developer (or your choice of SQL tool), import a table, from a flat file (for example, sample_basic_table.csv), to your SQL database server
connection.
In Oracle SQL Developer, the imported table, SAMPLE_BASIC_TABLE, might look similar to the following:
Next, you will delete some members from Sample Basic, and then create a load rule to rebuild the Market dimension from the SQL table.
Chapter 6
2. In the cloud service, in the Applications home page, expand the Sample application, and select the cube, Basic.
3. Click Outline.
4. Click the Market dimension, and then click member East.
5. Select Lock and Edit, and delete some of the states from the East market. For example, delete Connecticut, New Hampshire, and Massachusetts.
6. Click Save and Unlock, and then verify that East now only contains the states Florida and New York.
Next, you will create load rules and repopulate the Market dimension, from the SQL table, with the states you have removed.
7. Click Scripts, then choose the Rules Editor tab. 8. Click Create to begin defining new load rules.
9. On the drop-down menu next to Verify, select Dimension Build.
10. In the Name field, change the name of the rules file to MarketSQLDimbuild. 11. Click the Dimensions tab.
12. Click the arrow next to Add, and choose From Outline. 13. Select Market and click OK.
14. Click the Fields tab, and click Create (do not click the arrow next to Create). 15. In field #1, add a generation named Region. Associate it with the Market
dimension, specify the generation level as 2, and select Generation as the Type. Chapter 6
The Market dimension is generation 1, and you are adding a child named Region. 16. Click Create again and add a second load rule field named State, associated with
Market, at generation 3.
17. Click the Data Source tab. 18. Enter the valid connection string.
a. For OCI connections: In the Name field of the Properties group, enter the valid OCI connection string.
b. For DSN-less connections, such as Oracle DB, Microsoft SQL Server, and DB2: You must leave the Name field of the Properties group empty. Instead, enter the connection string in the Server field of the SQL Properties group. 19. In Oracle SQL Developer (or your alternate SQL tool of choice), write and test a
SELECT statement selecting some columns from the table SAMPLE_BASIC_TABLE: Select distinct market,statename from SAMPLE_BASIC_TABLE
Chapter 6
20. If the SQL query is valid and returns a result in your SQL tool, copy all of the statement, except for the SELECT keyword, to your clipboard. The results of this query are the dimensions you will load into the Sample Basic cube.
21. Back in the Data Source tab for your dimension build load rule, paste the SQL statement, except for the SELECT keyword, into the Select field of the SQL Properties group.
22. Click Save to save the MarketSQLDimbuild rule, and then click Close. Next, you will load the dimensions from Jobs.
23. Click Jobs, and click New Job.
24. Select Dimension Buildas the job type, and SQL as the load type.
25. From the Scripts drop-down list, select the name of the dimension build rules file you created, MarketSQLDimbuild.
26. Enter the user name and password of one of your SQL database schema users. 27. From the Restructure Options drop-down list, select Preserve All Data. 28. Click Execute.
The dimension build begins. Click Refresh to watch the status, and when it completes, scroll to the right of the Status column, and click Job Details . 29. Click Outline, and verify that your dimensions were built (verify that Connecticut,
New Hampshire, and Massachusetts exist as children under East).
Loading Data Using SQL
This task flow demonstrates how to clear data from a cube, create data load rules, load data (using SQL) from an RDBMS server, and verify in Smart View that the data was loaded.
1. Complete step 1 from Loading Dimensions Using SQL, if you haven’t already. Next, you will execute a calculation script that clears sales data from the cube, and then load the data again from a table.
2. In the cloud service, in the Applications home page, expand the Sample application, and select the cube, Basic.
3. Click Jobs, and click New Job.
Chapter 6
4. Select Clear Data as the job type, and click Execute.
5. Click Yes. The clear data job begins. Click Refresh to watch the status, and when it completes, log in to the database from Smart View.
6. In Smart View, zoom in and keep only on all the dimensions, to see that level zero sales data was cleared. For example:
Keep the worksheet open. Next, you will create load rules and repopulate the Sales data from the SQL table.
7. Click Scripts, then choose the Rules Editor tab. 8. Click Create to begin defining new load rules.
9. On the drop-down menu next to Verify, ensure that Data Load is selected. 10. In the Name field, change the name of the rules file to SalesSQLDataload. 11. In the Load Option field, select Add.
12. Skip the Dimensions tab, and click the Fields tab. 13. Click Create (do not click the arrow next to Create).
14. In Oracle SQL Developer (or your alternate SQL tool of choice), write and test a SELECT statement selecting some columns from the table
SAMPLE_BASIC_TABLE: Select Product,Year,Scenario,Statename,Sales from SAMPLE_BASIC_TABLE
15. Ensure that the SQL query is valid and returns a result in your SQL tool. The results of this query are the data you will load into the Sample Basic cube. 16. Note the order of dimensions as listed in your SQL query. You must follow the
same order of dimensions as you define the load rule fields.
17. In the cloud service, back in the Fields tab for your data load rule, enter Product as the name of field #1 (because Product is listed first in the SQL query). 18. Click Create.
19. Enter Year as the name of field #2 (because Year is listed second in the SQL query).
20. Click Create.
21. Enter Scenario as the name of field #3, and click Create. 22. Enter Market as the name of field #4, and click Create. 23. Enter Sales as the name of field #5.
24. Click the Data Source tab. 25. Enter the valid connection string.
Chapter 6
a. For OCI connections: In the Name field of the Properties group, enter the valid OCI connection string.
b. For DSN-less connections, such as Oracle DB, Microsoft SQL Server, and DB2: You must leave the Name field of the Properties group empty. Instead, enter the connection string in the Server field of the SQL Properties group. 26. Copy all of the SQL statement except for the SELECT keyword, and paste it into
the Select field of the SQL Properties group.
27. Click Save to save the SalesSQLDataload rule, and then click Close. Next, you will load the data from Jobs.
28. Click Jobs, and click New Job.
29. Select Data Load as the job type, and SQL as the load type.
30. From the Scripts drop-down list, select the name of the data load rules file you created, SalesSQLDataload.
31. Enter the user name and password of one of your SQL database schema users. 32. Click Execute.
The data load begins. Click Refresh to watch the status, and when it completes, scroll to the right of the Status column, and click Job Details .
33. Go back to the worksheet in Smart View, and refresh it to verify that the data was loaded from the SQL table.
Chapter 6