Create a Data Model Using a Microsoft Excel File in Oracle Business Intelligence Publisher

Before You Begin

This 5-minute tutorial shows you how to create a data model in Oracle Business Intelligence Publisher by using data from a Microsoft Excel file.

Background

You can use data from a Microsoft Excel file as a data source to create data models in Oracle Business Intelligence. In this tutorial, use the order details in the customer_orders.xlsx file to create a data set and add a parameter to filter the order details based on the customer ID.

What Do You Need?

• Access to Oracle Business Intelligence Publisher in Oracle Analytics Cloud as a BI Content Author
• Downloaded the customer_orders.xlsx file to your computer

Create a Data Model

In this section, upload the customer_orders.xlsx file, and use the data from the spreadsheet to create a data model.

1. Sign in to Oracle Analytics Cloud.
2. On the Home page, click Page Menu , select Open Classic Home, click the New menu, and then select Data Model under Published Reporting.
3. Select Data Sets, click New Data Set in the Diagram tab, and then select Microsoft Excel File.
4. In the New Data Set – Microsoft Excel File dialog box, click Upload next to the File Name field, select the customer_orders.xlsx file stored in your local folder, and then click Upload.
5. Enter the following values for the data set:
   • Name: OrderExcelDataset
   • File Name: customer_orders.xlsx
   • Sheet Name: CustOrders
   • Timezone: [GMT-08:00] Pacific Time (US & Canada)
6. Click OK, and then click Save .
7. In the Save As dialog box, select My Folders from the Catalog pane if not selected, enter the following details for the data model, and then click Save.
   • Name: CustomerOrders
   • Description: Data Source: customer_orders.xlsx Microsoft Excel file

Add a Parameter to Filter Data

In this section, add a parameter to the data set to filter data.

1. On the CustomerOrders data model page, click Parameters, click Create new Parameter , and then enter the following values for the P_CustId text type parameter:
   • Name: P_CustId
   • Data Type: Integer
   • Default Value: 104
   • Parameter Type: Text
   • Display Label: Customer ID
   • Text Field Size: 5

   

2. In Data Sets, click OrderExcelDataset, click the Diagram tab, click Edit Selected Data Set , click Add Parameters, and then enter the following for the parameter fields:
   • Name: CUSTOMER_ID
   • Value (Parameter): P_CustId
3. Click OK, and then click Save.
4. Click View Data.
5. In the Data tab, click View, and then click Table View.
6. Click Save As Sample Data.
7. Click Save .

Want to Learn More?

• Create a Data Set Using a Microsoft Excel File