Building an Oracle Analytics Cloud Essbase Cube

Purpose

You can create a cube from data in a Microsoft Excel spreadsheet. You build the Essbase cube using the Cube Designer Transform Data option. From Oracle Smart View for Office, you connect to Oracle Analytical Cloud - Essbase. You also analyze the cube using Smart View ad hoc analysis.

Time to Complete

30 minutes

Background

The data is transformed on the Oracle Essbase sever, used by Oracle Analytics Cloud - Essbase, when you select the Cube Designer Transform Data option. The data from Excel spreadsheet is changed into hierarchical multi-dimensional data. The transform data option automatically identifies relationship among columns, and assists in building an Essbase cube with the relevant dimensions and their hierarchies. The transform identifies mesaures and creates dimensions.

What Do You Need?

You need the following to complete the steps in this tutorial:

• Oracle Smart View for Office

• Oracle Analytics Cloud - Essbase

• Oracle Smart View Cube Designer extension for Oracle Analytics Cloud - Essbase

• Microsoft Excel

• Windows operating system

You need the following to connect to Oracle Analytics Cloud - Essbase to support Cube Designer:

• Service URL for Oracle Analytics Cloud - Essbase Service

• User Name

  You must have a Power User or Administrator role.

• Password

1. Download and save the B1_Table1.xlsx spreadsheet to your hard drive.

2. Open B1_Table1.xlsx with Microsoft Excel.

   

3. Click the Smart View ribbon, and click Panel. In the Smart View Home panel, select Private Connections.

   

4. In the Private Connection field, enter the URL for your Oracle Analytics Cloud - Essbase service with essbase/smartview appended to the URL, and click the arrow.

   The URL in the image is only an example. However, you must append essbase/smartview to your service URL.

   

5. Sign in to Oracle Analytics Cloud - Essbase with your User name and Password.

   

   The Essbase cluster in your environment is displayed in the Smart View Private Connections panel.

   

1. Click the Cube Designer ribbon.

   

2. In the Cube Designer ribbon, click Transform Data.

   

3. In the Transform Data dialog, verify that the B1_Table1 spreadsheet is listed in the Enter Application Name field. In Enter Cube Name use SpendHistory as the name, and then click Preview Data.

   

   The data transformation preview results show the measures and dimensions.

   

4. In Transform Data, click Run.

   

5. In the Transform Data message dialog that asks, Do you wish to build this cube?, click Yes.

   

6. In the Job Viewer dialog, click Yes to launch the Job Viewer and to see the job status.

   

   The Job Viewer shows that the Deploy and Analyze data tasks were successful.

   

   The SpendHistory cube is added to the Essbase cluster.

   

In this section, you continue to use the SpendHistory cube that you created in the previous section, create an ad hoc query grid, and zoom in on the dimensions.

1. Add a new sheet to the B1_Table.xslx spreadsheet, and then select the SpendHistory cube in the Essbase cluster.

   

2. Click the Cube Designer ribbon, click Analyze, and select Create Smart View Ad hoc Grid.

   

   There are no visible changes to the spreadsheet or cube as the result of this step.

3. In the Smart View panel, select the SpendHistory cube, and click Ad hoc analysis.

   

   The results show #Missing in the Measures row.

   

4. Close the Smart View panel.

   

5. In the spreadsheet, double-click Measures.

   

   The spreadsheet shows values for amounts spent and invoiced.

   

6. In the spreadsheet, double-click Month_Dim.

   

   The spreadsheet shows values for the fiscal year (FY) 2015 and 2016, and month dimension values.

   

7. Select the cell containing FY 2016, and then select the Essbase tab.

   

8. In the Essbase ribbon, click Keep Only.

   

9. If prompted, sign in to Oracle Analytics Cloud - Essbase.

   The result shows the remaining FY 2016 values, while the FY 2015 and the month dimension values are no longer in the spreadsheet.

   

10. Select the FY 2016 cell, then in the Essbase ribbon, select the Zoom In list, and click All Levels.

    

    The results show values for each month and quarter of fiscal year 2016.

    

11. Select the cells, Jan-16 through 2016 Q1, and then click Keep Only.

    

    The Keep Only command preserves the selected columns and removes the columns that weren't selected.

    

12. To undo the query, select the Smart View tab, and click Undo.

    

    Undo reverses the last change that you made to the data.

    

13. In the Smart View ribbon, click Redo.

    

    After selecting Redo, your results should shows the months in Q1 and the Q1 rollup column.

    

14. To save your data, connect to Oracle Analytics Cloud - Essbase, click the Smart View ribbon, and click Submit Data.

    If prompted to log in, enter your credentials.

In this section continue using B1_Table1 application and the SpendHistory cube.

1. Click the Essbase ribbon, and double-click Product.

   

   Column A shows the results from using double-click to zoom in on Product.

   

2. Select IT and Equipment, and then on the Essbase ribbon, click Zoom In.

   

   Column A reflects the results from zooming in on IT and Equipment.

   

3. To suppress rows that contain missing data, select the Smart View tab, and click Options.

   

4. In Options, under Suppress Rows, select No data/Missing, and then click OK.

   

5. On the Smart View ribbon, click Refresh.

   

1. Select Spend, and select the Essbase ribbon.

   

2. On the Essbase ribbon, click the Pivot list, and select Pivot to POV.

   

3. Select the 2016 Q1 cell, and then click Keep Only.

   

   The results show the totals for the first quarter.

   

4. In the POV, select and highlight Org.

   

   Drag Org to the Q1 2016 cell, and then release.

   

   Org sits below the Q1 2016 when you drop the dimension.

   

5. Double-click Org.

   The results show the various organizations that comprise the Org dimension.

   

6. Double-click Vision Industries to zoom in to cost centers for the Purchasing Org.

   

   The Vision Industries cost center didn't have any expenditures for the quarter.

7. Select Supplier_Dim, and click Member Selection.

   

8. In Member Selection for the Supplier_Dim, use the right arrow to move JKS National and PC Systems to the panel on the right, and click OK.

   

   JKS National and PC Systems are added to the available Supplier_Dim list.

9. Click the Supplier_Dim list, and select JKS National, and click Refresh.

   

   The results for JKS National show that the company supplied plasma monitors and didn't supply LCD flat monitors.

   

10. Click the Supplier_Dim list, and select PC Systems.

    

    The results for PC Systems show that the company supplied Envoy rugged laptops.

    

• Working with Oracle Smart View for Office

• Creating a Cube from an Application Workbook

• Working with Cubes in Cube Designer