Using Star Analytics to build your star schema from existing Essbase cubes

In this recipe, we will be using a third party tool called Star Integration Server Manager to build a star schema from an existing cube. The purpose of using this tool in this recipe is to quickly move the meta-data of your Essbase, Planning, or HFM applications to a relational environment. The tool we will be using is the express version and can be downloaded from the Star Analytics website: http://www.staranalytics.com/products/index.htm.

Getting ready

To get started, open Microsoft SQL Server and add a database called BASIC. Microsoft SQL Server is being used for this example, but you can use Oracle, Sybase, Teradata, DB2, MySQL, or a text file to extract your outline.

How to do it...

1. Click on Programs and select Star Analytics | Star Integration Server Manager.

2. Double-click on Connection Manager on the left-hand side, and click on the New Connection button. The New HyperionEssbase Connection menu will appear on the screen, as follows:

   

3. Enter a Sample for your Connection Name and enter your Essbase Server.

4. Enter a Sample for the Application parameter value, Basic for the Database parameter value, Basic for your Outline parameter value, and your User Name and Password. Click on the Test button to test your connection, click the OK button on the pop up menu, and click on the Save and Close button.

5. Double-click on Connection Manager, select SQL Server using SQL Server login in the Connection Type, and enter Basic for the Name.

6. Enter your SQL Server name into the parameter value, Basic for the Database parameter, and enter your Login Name and Password.

7. Click on the Test button to test your connection, click the OK button on the popup menu, and click the Save and Close button.

8. Click on the New Essbase Selection button and select the Selection Information tab. In the Source Connection drop-down select Sample: Hyperion Essbase, and in the Target Connection drop-down select Basic: SQLServer-SQLLogin.

9. Check all the Export checkboxes in the Column Selections grid, and check on the Spin checkbox for Measures. Your screen will look like the following screenshot:

   

10. Click on the Run button in the Run Selection prompt and select Yes to run the Current Selection. Click on the OK button in the Run Completion on the pop up.

11. Verify that all your tables were created in the Running Essbase Selection in the local dialog box, and click on the Close button. Your screen should look as follows:

    

How it works...

In this recipe, we created a connection to our relational database and a connection to Essbase. We then used the tool to define the dimensions we wanted to include in our star schema. Finally, we created a star schema using the Sample Basic database with our selections. You should see six tables in your Basic relational database. The results of this exercise are available in script 3265_01_08_tsql.sql.

See also

The Sample Basic database comes with the Essbase installation, but if you would like to build it, refer to the Adding an Application and Database on an Essbase server recipe in Chapter 5.