fullpaccopper

This article looks at how to use a local data source in a Web Intelligence report and then combine that data with data retrieved from a data warehouse.

1. How To Install Efashion Universe 4
2. Efashion Database

Business Scenario

I have installed cluster install of BI 4.1 with default eFashion with Access DB. How to get eFashion within distributed environment and support 64bit ODBC. Please provide feedback and guidance I also want to support the Universe with other tools like Lumira, Explorer, WebI and Crystal.

The worked example described here is to create a simple dashboard style report of sales data. We then highlight the sales figures using a typical red, amber, green format based on whether a sales figure has met, exceed or failed to meet a defined target. While the sales data is extracted from a data mart our target values however are defined in a spreadsheet.

Overview

The screenshot below is of the final report and the following is a summary of the steps required to create this report,

1. Create a new Web Intelligence document using Web Intelligence Rich Client
2. Create a query of sales data from eFashion database.
3. Create spreadsheet with required target value data
4. Edit the Web Intelligence document and import this data
5. Add a table at top of page to display the imported values
6. Create Alerters to highlight sales figures using these values
7. Save and export the document to the repository
8. Test that refreshing the document only updates efashion data set and doesn’t try to refresh the spreadsheet data.

The screenshot below is the final version of the report as viewed in InfoView. The table at the top of the page displays the target values extracted from the spreadsheet. Below this is our sales data table where we’ve used Alerters to highlight sales figures in either red, amber or green depending on which target value the sales have met.

Prerequisites

Note, although the instructions below are fairly detailed it is expected that you already have a good understanding of creating end editing documents using Web Intelligence.

This example uses the eFashion universe that is available in the samples that come with the default installation of SAP BusinessObjects Enterprise. You will need to have the eFashion universe deployed and connected to the eFashion database. See the SAP BOE Deployment Guide for further instructions on this.

The spreadsheet is created using Microsoft Excel however if you do not have access to Excel then you can use a plain text file instead for the local data source as the workflow is more or less the same.

Local data sources were introduced in the XI 3.1 version of SAP BusinessObjects Enterprise and it is this version that is used here.

Create a new Web Intelligence Document

First we create a new Web Intelligence document and create a simple query to retrieve sales data by store and year from eFashion universe.

1. Launch Web Intelligence Rich Client and logon using any account that has access to the eFashion universe.
2. From the initial screen select the option to create a new document using a universe and select the eFashion universe
3. Create a simple query that selects Store name, Year and Sales revenue objects. Note, we don’t need any filters
4. Run this query and we should see a report containing a simple table display sales revenues by store and year.
5. Create a basic crosstab with stores in the vertical y-axis and years in the horizontal x-axis along the top with sales revenue in the body.
6. Save your document to your local file system.

Your report should be similar to the following,

Make a note of the years and also make a note of the range of sales revenue values. A different version of the eFashion database may contain different years and sales amounts. When we create our red, amber and green target values (also known as RAG values) in the spreadsheet we need to ensure that these values are in the same range as the data in the report in order to demonstrate that the Alerters work correctly.

Create a Spreadsheet of Sales Target Values

In the screen shot the years are 2001, 2002 and 2003 and the sales revenue varies from about $200,000 to $1,600,000. So we create a spreadsheet that defines a different set of RAG values for each year. Note, we only need to define red and green values as amber is used for total sales value that lie between red and green

The table below displays some suitable values,

To create the spreadsheet,

1. Launch Excel and add the above table to a new spreadsheet file.
2. Highlight the range of cells that this data occupies and define the name “RAG_DATA” for this range. You can do this by selecting Name from the Insert menu and then select Define from the sub menu. Your spreadsheet should be similar the screen shot below
3. Save your spreadsheet to a suitable location such as your desktop or My Documents.

Using a Plain Text File as an Alternative

If you don’t have access to Excel then you can use a plain text file as an alternative. This text file can either be a fixed width or delimited data text file. Include column headings in the first row will make identifying the data in the Web Intelligence report easier. If you wish you can save the following to a text file on your local system,

Import RAG Data into the Document

Next we want to import this data into our Web Intelligence document.

1. Return to Web Intelligence Rich Client
2. Click Edit Query
3. Click Add Query and select local data source
4. Click Next and then click the Browse button in the next screen and select the spreadsheet we created above. Or if using a text file select your text file that you created.
5. Having selected the Excel file we should be able to define where the data is within the Excel file. Select Range option and our range name should be listed in the drop down box.
6. Leave the “first row contains column names” check box checked

Note, if you are using a text file then you’ll have a different configuration screen where you can specify the structure of the text file.

1. Click Next and we should be presented with a local data query screen listing the column headings in our range and a sample of the data from the spreadsheet
2. In the top panel of ‘Result Objects’ we see that each object is a measure. This is fine for the red and green value objects but for Year we want this to be a dimension. Select the Year object (in result object panel) and under object properties change the object’s qualification from Measure to Dimension.
3. Also change the data type of the object from Number to String. This is so that we can use this object to merge this data with the eFashion data which we’ll do later.

Note, under Query Properties in the left hand side we have an option ‘Refreshable’. By default this is checked and unchecking this means that the query won’t be refreshed when the document is refreshed. Although this is what we want we have to leave this checked just now so that we run this query at least once to bring in the data. Later we’ll uncheck this option prior to exporting the document to the Repository.

1. Also, to make things easier, we’ll rename this query to “Local Data Query” so we easily identify the data when working with the report.
2. At this stage you may also want to rename our first query to “Sales Data Query”.
3. From the Run Queries button click the down arrow and select Local Data Query to load the data from the spreadsheet into Web Intelligence.
4. In the popup dialog select the option to Include the result objects without generating a table
5. Save the document at this point.

Displaying Data In The Report

Now that we have imported the data we need to merge this data set with the data set from eFashion. We use the Merge Dimensions feature to do this.

1. Click on the Merge Dimensions icon in the toolbar
2. Using the control key select the Year object in both Available Dimension panels.
3. Click Merge button
4. Click OK to the Edit Merged Dimension dialog.
5. Click OK to close the Merge Dimension window.

Note, earlier we changed the data type of the Year object in the local data source query from number to string. This is because Year object in the eFashion data set is of String data type and we can only merge dimensions that have the same data type. If we didn’t then we wouldn’t be able to select both dimensions in the Merge Dimension window.

We now have our data synchronised. This allows us to apply Alerters correctly based on the different red and green values for each year. Before we create our Alerters we’ll add a table to the report to display the RAG values.

Rather than just displaying the Red and Green values that we imported from the spreadsheet we will instead display a table that provides more descriptive text about the target ranges.

1. Move the existing table further down the page so we have room to add the new table above
2. Drag the objects Year, Red Value and Green Value from the list of objects to the report such that they create a table above the original table
3. Make sure the columns are ordered Year, Red Value, Green Value and insert a new column between the Red and Green columns labelled ‘Amber Value’
4. Update the cell that is display the data for the Red Value column and change it from =[Red Value] to =”Less than ” + [Red Value]
5. Update the Green Value data cell and change this entry to =”Greater than ” + [Green Value]
6. Add a new formula to the Amber Value column: =”Between ” + [Red Value] + ” and ” + [Green Value]
7. Your report should now look similar to the screen shot below

Create RAG Alerters to Highlight Sales Data

1. Select the Sales revenue cells in the crosstab
2. Click the Alerters icon from the tool bar
3. Click New
4. Change name to “RAG alerter”
5. In sub alerter area change the operator to Less Than
6. Click the “…” button next to the value text box to select an object
7. Select Red Value from list of objects and click ok
8. Leave the cell format as red text
9. Click Add Sub-Alerter button
10. Change operator to greater than or equal to and then select the Red Value object again
11. Click the plus button to add a new conditional row and in this row set operator to less than and select the Green Value object
12. Click format button and set the text colour to orange
13. Click Sub-Alerter once more, change operator to greater than or equal to and select the Green Value object
14. Change format to green text

Your dialog should be similar to,

1. Click OK to close the dialog box and
2. Click OK again to return to the report
3. Save your document

Our table should now contain data coloured accordingly to the Alerter values.

Export to Repository

Before we can export the document we must first disable the refresh of the local data. This is so that the we can refresh the report in InfoView against the eFashion database but not against the local data as the spreadsheet is not accessible by the server.

How To Install Efashion Universe 4

1. Click the Edit query button
2. Select the Local Data Query tab
3. Under properties uncheck ‘Refreshable’
4. Click down arrow on run queries button and click Sales Data Query (the eFashion query)

By running the Sales Data query we apply the changes and we return to the report. We do have an option under the close button to apply changes and close but this purges all data from the report which we don’t want.

1. Save document and from the File menu select Export to CMS
2. Select a suitable folder and click OK
3. Logon to InfoView and open your document
4. Click the drop down arrow on the Refresh button and the Local Data Source query should be greyed out.
5. Click refresh should now only execute the eFashion query

Conclusions

Efashion Database

This worked example looked at incorporating local data with enterprise data and using the local data to define target values. This is a fairly typical business scenario where we find actual figures contained in data marts but target values, which are not maintained by a system, are instead held in spreadsheets.