May/June 2015
Oracle Business Intelligence Cloud Service brings the analysis and dashboard capabilities of Oracle Business Intelligence to Oracle Cloud, along with a new self-service interface that makes it easy for nontechnical users to upload and report on departmental data sets. In this article, I’ll demonstrate how to upload a spreadsheet containing sales data to Oracle Business Intelligence Cloud Service, model the data into a dimensional star schema, and then create analyses and a dashboard to be used in a department.
Oracle Business Intelligence Cloud Service: Bringing Oracle Business Intelligence to the CloudOracle Business Intelligence Cloud Service, part of the Oracle Cloud platform as a service (PaaS), gives users the ability to upload spreadsheet, file, and other data sets to a secure cloud-based database environment, create simple data models, and then use these to build rich interactive analyses and dashboards that can be secured and shared within a department. Data can be uploaded with Oracle SQL Developer; the Oracle Application Express web services API; or, as I will describe in the article example, Oracle Business Intelligence Cloud Service’s web-based data upload service.
A typical use case for Oracle Business Intelligence Cloud Service is departmental knowledge workers who want to take a set of data they are working on and make it available to others in the organization quickly, without having to involve the IT department in the process.
The example, which uses a Microsoft Excel spreadsheet document as the datasource, steps through the process, from data upload to the final dashboard. To follow along with the example in this article, you will require access to an Oracle Business Intelligence Cloud Service instance, and you will need to download the spreadsheet file I’ll be using from bit.ly/omagbics1.
Upload the Spreadsheet and Create the Data ModelTo upload the spreadsheet and create a simple data model to present its data to report developers, follow these steps:
SALES: AMOUNT_SOLD, QUANTITY_SOLD, TIME_ID
CHANNELS: CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS
CUSTOMERS: CUST_ID, CUST_CITY, CUST_STATE_PROVINCE
PRODUCTS: PROD_ID, PROD_DESC, PROD_CATEGORY
Then, within each of the dimension table areas, check the checkbox next to the primary key columns—CHANNEL_ID, CUST_ID, PROD_ID—to designate them as primary keys and automatically add those same columns to the SALES fact table; the dialog box looks like Figure 3. Click Next, Create, and Done to create the initial data model and the database view objects.
Figure 3: Creating the initial data model
To join this time dimension table to the fact table, click Create Join on the New Data Model page and join the two tables on SALES.TIME_ID = Time.Date Timestamp. When the join is complete, click the green Save Changes icon (next to the join definition) and then, to complete the data model definition process, click Publish Model to save your changes and make the data model shown in Figure 4 available to other users.
Figure 4: The completed data model
To create a set of sample analyses and include them in a new dashboard, follow these steps that use the data model you created in the previous set of steps:
From the list of visualization types displayed, choose Pie (Recommended) from the list and click the Remove View from Compound Layout icon (“x”) within the Table view to leave just the pie graph. Save this analysis to the catalog by clicking Save Analysis at the top right of the page. Then, using the Save As dialog box, first create a new folder called First Reports within the Company Shared folder and then save the analysis into this new folder, using the name Sales by Channel Breakdown.
Then, on the Results tab, click Add View to add a new Performance Tile view to the analysis, and when it is added to the compound layout, click Edit Analysis in the view to change the label to NEW SALES and the tile style to the second style (which uses white text on a gray background). Click Done and the Results tab, and then click the Remove View from Compound Layout button in the Title and Table views to remove these from the compound layout of the analysis. Save the analysis to the First Reports folder as Amount Sold Tile.
All that’s left now is to create a dashboard to hold these analyses. To do this, from the Oracle Business Intelligence Cloud Service home page, click Create a Dashboard and name the dashboard Sales Dashboard. Save the dashboard in the /Company Shared/First Reports/Dashboards folder, and click OK to start adding content.
With the dashboard editor now open, drag and drop two column objects from the Dashboard Objects panel to create two vertical columns in your dashboard, and use the Catalog panel under the Dashboard Objects panel to add the Amount Sold Tile and Product Sales analyses to the left-hand column and the Quantity Sold Over Time and Sales by Channel Breakdown analyses to the right-hand column. Save the dashboard, which should look like Figure 6.
Figure 6: The Oracle Business Intelligence Cloud Service dashboard
You have now created your first Oracle Business Intelligence Cloud Service dashboard. You can use it to view and interact with the analyses displayed within it, and you can create additional analyses, dashboard pages, and dashboards as well as upload and add new data to your data model. Other users within your Oracle Business Intelligence Cloud Service instance can view the analyses and dashboards you have created, and you can set up roles to control access to data and reports. Refer to the Oracle Business Intelligence Cloud Service online help, videos, and tutorials at bit.ly/oraclebicshelp for more information.
ConclusionOracle Business Intelligence Cloud Service makes it possible to quickly deploy analyses and dashboards as part of the Oracle Cloud platform without the need for on-premises software installs or the help of the IT department. In this article, you’ve seen how to quickly create reports and a dashboard that can be shared with coworkers within your department, using simple self-service tools and the ability to upload spreadsheets and other files to create your reporting data set.
LEARN more about Oracle Business Intelligence Cloud Service READ more Rittman
|
Photography by Ricardo Gomez, Unsplash