sap businessobjects business intelligence workbook 1: the
TRANSCRIPT
SAP BusinessObjects Business Intelligence Workbook 1: The Sports Warehouse
David Duncan 3/8/2012
Table of Contents Introduction ......................................................................................................................................4
Target Audience .............................................................................................................................4
Learning Objectives ........................................................................................................................4
STOP HERE: Prerequisites ...............................................................................................................5
Setting up the Sample Data Source .....................................................................................................6
Activity 1 – Create the Sports_Warehouse Database .......................................................................6
Activity 2 – Create a Sports_Warehouse Data Source Name (DSN) ...................................................7
Creating Crystal Reports with SAP Crystal Reports 2011 ......................................................................7
Activity 3 – Create a Crystal Report using the Standard Report Creation Wizard ...............................7
Activity 4 – Create a Crystal Report in Design View ..........................................................................9
Activity 5 – Group Records in a Crystal Report ............................................................................... 10
Activity 6 – Create a Crystal Report with Parameters ..................................................................... 11
Creating Spreadsheet Based Dashboards .......................................................................................... 12
Activity 7 – Create a Dashboard from a Spreadsheet ..................................................................... 12
Activity 8 – Create a Dashboard with a Single Value Input Control ................................................. 14
Activity 9 – Filter Data by Chart Selection ...................................................................................... 15
Building a Universe with the Information Design Tool ....................................................................... 17
Activity 10 – Create a Sports_Warehouse Universe ....................................................................... 17
Part 1 – Create a Sports_Warehouse Connection .............................................................................. 18
Part 2 – Create a Sports_Warehouse Data Foundation ...................................................................... 18
Part 3 – Create a Sports_Warehouse Business Layer ......................................................................... 18
Part 4 – Publish the Sports_Warehouse Universe .............................................................................. 19
Part 5 – Test the Sports_Warehouse Universe in SAP BusinessObjects Web Intelligence ................. 19
Activity 11 – Create Additional Universe Objects ........................................................................... 20
Activity 12 – Optimize Performance with Index Awareness ........................................................... 21
Activity 13 – Testing the Universe ................................................................................................. 21
Creating Universe Connected Dashboards ........................................................................................ 23
Activity 14 – Create a Dashboard .................................................................................................. 23
Activity 15 – Create an Interactive Dashboard ............................................................................... 28
Creating Crystal Reports with Crystal Reports for Enterprise ............................................................. 29
Activity 16 - Create a Crystal Report using Crystal Reports for Enterprise ....................................... 29
Activity 17 – Create a Crystal Report with a Crosstab ..................................................................... 32
Activity 18 – Create a Simple Alert ................................................................................................ 32
Activity 19 – Subscribe to an Alert ................................................................................................ 33
Exploring Universe Based Information Spaces ................................................................................... 33
Activity 20 – Create an Information Space ..................................................................................... 33
Activity 21 – Explore the Sports Information Space ....................................................................... 35
Activity 22 –Explore a Microsoft Excel Spreadsheet ....................................................................... 35
Activity 23 – Create an Information Space Based on a Spreadsheet................................................ 37
SAP BusinessObjects Web Intelligence .............................................................................................. 38
Activity 24 – Create a Web Intelligence Document ........................................................................ 38
Activity 25 – Create a Document using Web Intelligence Desktop .................................................. 39
Activity 26 – Insert breaks, totals and subtotals ............................................................................ 40
Activity 27 – Sorting and Ranking .................................................................................................. 41
Activity 28 – Modify a Web Intelligence Query and Document ....................................................... 42
Activity 29 – Filter the Data in a Web Intelligence Document ......................................................... 43
Activity 30 – Add a Chart to a Web Intelligence Document ............................................................ 44
Activity 31 – Drill into a Web Intelligence Document using Default Navigation Paths ...................... 45
Activity 32 – Create a Custom Navigation Path in the Universe ...................................................... 46
Activity 33 – Creating Drillable Web Intelligence Documents ......................................................... 46
Activity 34 – Create a Web Intelligence Document with a Crosstab. ............................................... 47
Activity 35 – Add another query to a Web Intelligence Document. ................................................. 47
Set up Sample Data Sources and BI Content ...................................................................................... 48
Activity 36 – Convert the eFashion.unv Universe to a UNX Universe .............................................. 48
Part 1 - Convert the eFashion.mdb to a SQL Server Database ........................................................... 48
Part 2 – Create a new eFashion_SQL Data Source Name ................................................................... 53
Part 3 – Convert the Sample eFashion Universe................................................................................. 54
Part 4 – Test the new eFashion.unx Universe ..................................................................................... 59
Activity 37 – Attach the Other Sample SQL databases .................................................................... 60
Troubleshooting .............................................................................................................................. 62
Logon denied: Your system does not allow the use of this application ........................................... 62
Error: Server SAPDEMO:6400 not found or server may be down (FWM 01003) null ........................ 62
Workbook Evaluation....................................................................................................................... 64
BI Activity Workbook Training Questionnaire ................................................................................ 64
Lab Activities Page 4
Introduction This workbook has been designed to provide you with practical, hands-on, experience using the SAP
BusinessObjects 4.x business intelligence suite of applications.
While completing the activities in this workbook, you will build a simple business intelligence solution,
with a basic data warehouse, a semantic layer, and business intelligence content including Crystal
reports, dashboards, Web Intelligence documents, and information spaces. A variety of activities
provide you with different ways to analyze and present the information contained in the data source.
As you work through the activities in this book, consider how the features and functionality of the suite
can be applied to solve business problems for your business or your clients.
Target Audience This workbook is suitable for anyone new to the SAP BusinessObjects 4.x business intelligence suite who
will be involved in selling, demonstrating, or developing business intelligence solutions or content.
Many end users will also find these activities useful in learning the BI applications.
More experienced project managers and consultants may be interested reviewing the file solution files
included the Workbook Resources file.
Learning Objectives After completing this workbook you will be able to:
Create a simple data warehouse in SQL Server
Build and publish a semantic layer in the information design tool
Make basic reports with SAP Crystal Reports 2011
Generate basic reports with SAP Crystal Reports for Enterprise
Design SAP BusinessObjects Dashboards
Produce SAP BusinessObjects Web Intelligence documents
Publish and explore SAP BusinessObjects information spaces
Save and view content in the BI launch pad
To get the most out of this workbook, it is strongly recommended that you do your best to complete the
activities in the workbook without referring to the solutions whenever possible. The prerequisites and
recommended tutorials referred to in the activities should provide you with the knowledge required to
successfully complete the activities.
If you get stuck completing any of the activities, step by step solutions are provided in the workbook
solutions guide. However, simply clicking through the step by step solutions in the software will
generally be inadequate to learn how to build effective business intelligence solutions. If you need the
step by step solutions to complete any activity, after following the solution instructions, try to complete
the activity again, but without referring to the detailed solution steps.
Lab Activities Page 5
Note for University Alliance and vBased class instructors: The solution steps are provided in a separate
solution guide, which can be provided to students the week after specific activities have been assigned.
This will ensure that students do not simply click through the solutions, but try to complete the activities
on their own or with their peers.
STOP HERE: Prerequisites Before beginning the workbook activities, you should have an understanding of the SAP BusinessObjects
4.0 Business Intelligence Suite.
1. Review the following eLearning prior to beginning this workbook.
SAP BusinessObjects BI and EIM Education on the Channel Partner Portal – Solution Consultants
2. Business Intelligence Platform and client tools.
To complete the activities in this workbook, you need access to a system with the following software installed, including the latest service packs. This workbook has been designed using feature pack 3 of the BI platform and client tools.
i. SQL Server 2008 or higher (or other supported database software) ii. SAP BusinessObjects Business Intelligence platform
iii. SAP BusinessObjects Client Tools iv. SAP BusinessObjects Explorer v. SAP Crystal Reports for Enterprise*
vi. SAP Crystal Reports 2011* vii. SAP BusinessObjects Dashboards
viii. SAP BusinessObjects Live Office
a. Instructions to install the required software are provided in the “SAP BusinessObjects B1
4.x Installation Job Aid”.
Please note that valid license keys are required. See resources at the end of this guide.
If you are using this workbook as part of a course, or corporate training, your instructor may provide you with instructions to access a hosted system or customized VMware image. In this case usernames and passwords may vary from the solutions in the guide.
Lab Activities Page 6
Setting up the Sample Data Source
Activity 1 – Create the Sports_Warehouse Database In this activity, you will create a simple star schema data warehouse using the sample data provided.
2. Download and extract the following workbook resources.
a. Sports:
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/4026d3cc-ca4a-2f10-fa85-
ecc740f6a085
b. Spreadsheets:
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/00f8b958-cb4a-2f10-a2b3-
e18c0ef13574
3. Login to the SQL Server Management Studio with the username SAPDEMO\Administrator
4. Create a new Sports_Warehouse database.
5. Import the four sports .csv tables and create the database schema shown here.
Useful Resources
Introduction to SQL and Data Warehouses (eBook)
Lab Activities Page 7
Activity 2 – Create a Sports_Warehouse Data Source Name (DSN) In this activity, you will create both 32-bit and 64-bit data source names to connect to the
Sports_Warehouse database.
Note: BI platform servers are 64-bit applications and require 64-bit database connectivity, whereas BI
platform clients, such as SAP Crystal Reports 2011, are 32-bit applications and required 32-bit database
connectivity.
Therefore, you must create a DSN in both the 32 bit and 64 bit ODBC Data Source Administrator
applications. There are two separate ODBC Data Source Administrator applications in Windows.
%systemdrive%\Windows\SysWoW64 folder.
%systemdrive%\Windows\System32 folder.
1. Create a 32-bit System DSN named Sports using the SQL Server Native Client 10.0.
2. Create a 64-bit System DSN named Sports using the SQL Server Native Client 10.0.
Creating Crystal Reports with SAP Crystal Reports 2011
Activity 3 – Create a Crystal Report using the Standard Report Creation
Wizard In this activity, you will use the Standard Report Creation Wizard to create a report using the Sports DSN
to display Gross Sales by Product, grouped by Category. You will also include a Bar Chart to summarize
Gross Sales by category.
eBooks
Developing SAP Crystal Reports
Tutorial Simulations
Connect to an ODBC data source
Preview a report
Save a report
Instructions
1. Open Crystal Reports 2011
2. Use the Standard Report Creation Wizard to create a report using the Sports DSN
a. Add the Dim_Category, Dim_Fact, and Dim_Product tables
b. Add the Product_Name, Date, and Gross_Sales fields
Lab Activities Page 8
c. Group the records by Category
d. Include a Bar chart to summarize Gross Sales by Category
e. Filter the records to show only data from 2010 - Q4
f. Apply the Corporate (Blue) template
The first page of your report should include the bar chart in the image below.
The second page of your report should display the data below.
Note that some basic formatting is required.
Documentation
SAP Crystal Reports Documentation
http://help.sap.com/bocr2011/
Sample Reports
http://www.sdn.sap.com/irj/boc/crystalreports-samples
Lab Activities Page 9
Activity 4 – Create a Crystal Report in Design View In this activity, you will create a report in design view. You will apply basic formatting, filter the records
displayed, and modify the sort order.
Tutorial Simulations
Add fields to a report
Select the data that appears in a report
Sort the records in a report
Save a report to the Business Intelligence platform
Instructions
1. Open Crystal Reports 2011.
2. Create a new Crystal Report using the Sports DSN.
3. Add the Customer, Fact and Product tables using the Standard Report Creation Wizard.
4. Use the Field Explorer to add the Customer_Name, Product_Name, Date, and Net_Sales fields to
the Details section of the report.
5. Move and Resize the fields as needed.
6. Format the date and currency values to match the image below.
7. Use the Select Expert to filter the report to show data from 2010 - Q4.
8. Add a second filter to include only Products with the word "golf" in the product name.
9. Sort the records by customer name, date, and then by product name.
10. Save the report to the BI Platform.
Related Tutorials
Edit the formula for record selection
Sort records using a sort control
Lab Activities Page 10
Activity 5 – Group Records in a Crystal Report In this activity, you will create a report that displays product sales in Q4, 2010, grouped by customer. In
addition, you will include a count of sales for each customer.
Tutorial Simulations
Group records based on data
Select groups based on a summary
Calculate totals and subtotals for data
Instructions
1. Open Crystal Reports 2011.
2. Create a new Crystal Report using the Sports DSN.
3. Add the customer, fact, and product tables.
4. Add the Customer_Name, Product_Name, Date, and Gross_Sales columns to the report.
5. Group the records by Customer_Name.
6. Insert a summary to show a sum of gross sales.
7. Save the report to the Administrator folder with the file name Product Sales by Customer
Your finished report should look like the image below.
Related Tutorials
Group records based on a custom definition
Change the grouping order
Lab Activities Page 11
Activity 6 – Create a Crystal Report with Parameters In this activity, you will create a report which filters data based on parameter selection prompts. In
addition to filtering data, you will include one of the parameter values in the report title.
Tutorial Simulations
Prompt for a value to select records
Display the prompt value used to select records
Prompt for multiple values to select records
Instructions
1. Open Crystal Reports 2011.
2. Create a new Crystal Report using the Sports DSN.
3. Add the customer_name, Date, Sales_Count, and Gross_Sales columns to the report.
4. Add a Customer Name parameter (allow multiple customers to be selected).
5. Add a Year and Quarter parameter.
6. Add filters to the report to display only records selected using the parameters.
7. Include the year and quarter parameter value in the title.
Challenge exercise: Modify the report you created in activity 5 by adding Start_Date and End_Date
parameters. Add a filter to display records between the selected dates.
Related Tutorials
Prompt for a DateTime range to select records
Create a cascading prompt to select records
Lab Activities Page 12
Creating Spreadsheet Based Dashboards
Activity 7 – Create a Dashboard from a Spreadsheet In this activity, you will create a dashboard with radio buttons and a column chart. Dashboard
consumers will be able to select which country will be displayed in the chart.
eBooks
SAP BusinessObjects Dashboards
Tutorial Simulations
Concept: Create a dashboard
Add a component to the canvas
Preview a dashboard
Display a range of data in a charts
Customize a chart
Change the data that displays based on users selection
Lab Activities Page 13
Instructions
1. Open SAP BusinessObjects Dashboards and create a new blank dashboard.
2. Import or copy and paste from the Products Crosstab.xlsx, which is located in the Workbook
Resources.
3. Create a dashboard like the image below that shows the reservations for cities in the selected
country.
a. Add Radio Buttons that update a row in the spreadsheet with the chart data
b. Ignore blank cells and enable sorting in the chart
Documentation
SAP BusinessObjects Dashboards Documentation
http://help.sap.com/boxcel40
Dashboards and Presentation Design User Guide
7.1.2 Running models from a web server
Lab Activities Page 14
Activity 8 – Create a Dashboard with a Single Value Input Control In this activity, you will create an interactive dashboard with a column chart and a horizontal slider. The
column chart will display Sales Revenue for selected years, including forecast revenue for 2012. The
forecast sales revenue for 2012 will be based on a horizontal slider to represent the growth rate
(between -50% and 250%).
Tutorial Simulations
Display a range of data in a charts
Customize a chart
Display a single value on a dashboard
Customize a component
Lab Activities Page 15
Instructions
1. Create a new dashboard and enter the values below into the spreadsheet.
A B C D E F
1 Growth Rate 2
Minimum -50%
3
Maximum 250% 4
Forecast 12%
5 6 2009 2010 2011 2012*
7 Q1 2,660,699.50 3,326,172.20 3,742,988.90 4,192,147.57
8 Q2 2,279,003.00 2,840,650.80 4,006,717.50 4,487,523.60
9 Q3 1,367,840.70 2,879,303.00 3,953,395.30 4,427,802.74
10 Q4 1,788,580.40 4,186,120.00 3,356,041.10 3,758,766.03
2. Add a column chart using the data in the table.
3. Add a horizontal slider using C4 as the forecast growth rate value, and C2 and C3 as the
minimum and maximum values.
4. Allow the dashboard consumer to select which years to display in the graph.
Activity 9 – Filter Data by Chart Selection In this activity, you will create an interactive dashboard with a pie chart and a line chart. The pie chart will display the contribution to Sales Revenue of different product categories. The pie chart should also allow the dashboard consumer to click a category in the pie to view a line chart of Sales Revenue for that category.
Lab Activities Page 16
Tutorial Simulations
Select an item in a chart to filter data in a dashboard
Instructions
1. Create a new dashboard and copy and paste the data from the Crosstab.xlsx in the Workbook
Resources into the dashboard spreadsheet.
2. Add a Pie Chart to the canvas to display the distribution of Gross Sales among the different
Categories.
The categories are listed in column A. Calculate the total annual Gross Sales in Column N.
3. Configure the Pie Chart to change row 2 with values for the selected category when the
dashboard consumer clicks a section of the pie.
4. Add a Line Chart which displays the Gross Sales for the selected Category in row 2.
5. Apply a theme.
Lab Activities Page 17
Building a Universe with the Information Design Tool
Activity 10 – Create a Sports_Warehouse Universe In this activity, you will create a basic universe for the Sports_Warehouse data warehouse using the
information design tool. You will create a new project, add a relational connection, create a data
foundation, and create basic objects in a new business layer. Finally, you will test the universe by
creating a web intelligence document.
eBooks
Create a Basic Universe
Build a Data Foundation
Build a Business Layer
Simulation Tutorials
Create a project
Create a connection to a relational database
Create a data foundation based on a single source relational database
Create a business layer based on a single relational data source
Publish a new universe file based on a single data source
Create a connection shortcut
Create an equi-join
Detect join cardinalities in a data foundation
Create a dimension
Create a measure
Related Tutorials
Create a calculated column using a date value
Create a calculated column using numeric values
Lab Activities Page 18
Documentation
SAP BusinessObjects BI Platform Documentation
http://help.sap.com/bobip
Information Design Tool User Guide
How to create a universe with the information design tool
Working with connections
Working with data foundations
Working with business layers
Publishing resources
Part 1 – Create a Sports_Warehouse Connection
1. Open the information design tool
2. Create a new project named Sports_Warehouse.
3. Add a Sports_Warehouse connection to the project.
Part 2 – Create a Sports_Warehouse Data Foundation
1. Add a data foundation to the Sports_Warehouse project
2. Add all four tables to the data foundation.
3. Create the necessary joins.
Note that joins can be created automatically.
4. Assign primary keys to the dimension tables.
5. Assign the foreign keys to the fact table.
6. Save the data foundation.
Part 3 – Create a Sports_Warehouse Business Layer
1. Add a business layer to the Sports_Warehouse project
2. Create the universe objects shown below.
Lab Activities Page 19
3. Ensure that the measures use the SUM aggregation function in the SQL Select statement and
also the projection aggregation function SUM (for Web Intelligence).
Part 4 – Publish the Sports_Warehouse Universe
1. Publish the Universe to the BI platform:
a. First publish the connection.
b. Change the data foundation connection
c. Save all and publish the business layer
Part 5 – Test the Sports_Warehouse Universe in SAP BusinessObjects Web Intelligence
1. Login to the BI launch pad.
2. Open Web Intelligence (Interactive Analysis) from My Applications.
3. Create a new Web Intelligence document using the Sports_Warehouse Universe
4. Add the Category, Year, Product Name and Gross Sales objects.
5. Run the query.
You should see the following result.
6. Edit the query and add the Net Sales object.
7. Add Net Sales to the right of Gross Sales in the report table.
8. Add a column to the right of Net Sales.
9. Create a Discount Variable using the formula Gross Sales – Net Sales and add it to the right of
the table.
Lab Activities Page 20
Your finished report should look like this image.
Activity 11 – Create Additional Universe Objects In this activity, you will enhance the Sports universe by adding a Discount measure, a Month dimension,
and Month number attribute. These new objects perform calculations to simplify the universe for end
users. You will also modify the Year dimension so that it displays the year values without a comma.
1. Open the Sports_Warehouse business layer.
2. Add a new dimension to the Time folder named Month. Use the SQL DATENAME function to
select the name of the month from the Dim_Fact.Date column.
3. Add a detail to the Month object named Month number. Use the SQL MONTH function to select
the number of the month from the Dim_Fact.Date column.
4. Add a new measure to the Figures folder named Discount. Use the formula [Gross Sales] – [Net
Sales].
5. Modify the SQL definition select statement for the year object to CAST(Dim_Fact.Year as VarChar(4))
The Cast function is used to convert the numeric values (for example 2,009) into 4 character
length VarChar (character strings for example “2009”).
This removes the comma from the default display.
6. Save and publish the universe.
Documentation
Information Design Tool User Guide
About the SQL/MDX Expression Editor
SQL function reference
Lab Activities Page 21
Activity 12 – Optimize Performance with Index Awareness In this activity, you will apply index awareness to universe objects. Index awareness allows the BI
platform to optimize performance by using indexes such as primary keys instead of text values: For
example joining with a country ID number rather than a country name.
Tutorial Simulations
Set up primary key index awareness
Set up foreign key index awareness
Apply a WHERE clause restriction to a primary key index
Apply a WHERE clause restriction to a foreign key index
Set up multiple foreign key index awareness
Instructions
1. Open the information design tool and open the Sports.blx business layer
2. Add index awareness to the Category, Customer name, and Product name dimensions.
Activity 13 – Testing the Universe In this activity, you will log into the BI launch pad, and create some simple web intelligence documents
to test the universe. The Web Intelligence documents that you create in this activity will answer the
following questions:
In Q4, 2010 which category had the highest Net Sales?
Who were the top 5 customers in Gross Sales for 2010?
Which customers purchased Golf Clubs? Which type did they buy and how many?
Documentation
SAP BusinessObjects Web Intelligence
http://help.sap.com/bowebi40
Instructions
1. Log into the BI launch pad.
2. Open Web Intelligence (Interactive Analysis) from My Applications.
3. Create a new Web Intelligence document using the Sports universe including the Category, Year,
and Gross Sales objects.
4. Add a filter to restrict data to sales from 2010.
5. Create another new Web Intelligence document using the Sports universe including the
Customer Name, Year, and Gross Sales objects.
6. Add a filter to restrict data to sales from 2010.
Lab Activities Page 22
7. Run the query.
8. Add a top 5 ranking to gross sales.
9. Create a third new Web Intelligence document using the Sports universe including the Customer
Name, Product Name, and Quantity objects.
Lab Activities Page 23
Creating Universe Connected Dashboards
Activity 14 – Create a Dashboard In this activity, you will create a dashboard to display sales data retrieved from a universe query. The
required universe objects include Category, Net Sales, Gross Sales, and Year.
Note: SAP BusinessObjects Dashboards does not automatically aggregate the measures (such as Net
Sales and Gross Sales) in the same way as Web Intelligence. In order to return the necessary data to the
Dashboard Design Query Builder, you will need to modify the SQL definition select statement of the
measure objects.
eBooks
SAP BusinessObjects Dashboards
Tutorial Simulations
Save a dashboard to BusinessObjects Enterprise
Instructions
1. Follow the steps in “Solution Part 1– Create a Dashboard” on the next page, to see how the
Dashboard Design Query Builder aggregates measures using the existing business layer objects.
2. Try to modify the measures as required in the information design tool.
Detailed steps to modify the measures are provided in Solution 14 Part 2.
3. Create a dashboard including the column chart shown above.
Detailed steps are in provided in Solution 14 - Part 3.
4. Save the dashboard to the BI Platform and test the published dashboard.
Note you will need to create a cross-domain policy file.
Lab Activities Page 24
Solution Part 1– Create a Dashboard
In this activity you will use a universe query as your dashboard’s data source. Follow the steps to see
how the Dashboard Design query builder retrieves data from the universe.
1. Open Dashboard Design.
(Click Start > All Programs > Dashboard Design > Dashboard Design).
2. Click No.
3. Click New.
4. Click Add Query…
Lab Activities Page 25
5. Type the password Welcome1 and click OK.
6. Click the Sports.unx universe and click Next.
7. Add Year, Category, Quantity, Net Sales, Gross Sales, and Discount.
Lab Activities Page 26
8. Add the filter Year Equal to 2010 and click Next.
Lab Activities Page 27
9. Click Next.
Note that the Dashboard Design Query Builder does not aggregate the fact records. In Web
Intelligence, this query would return a single row for each Category and Year, and display the
total value for each measure.
10. Click Cancel.
Related Tutorials
Concept: Create a dashboard
Add a component to the canvas
Preview a dashboard
Display a range of data in a charts
Documentation
http://help.sap.com/boxcel40
Dashboards and Presentation Design User Guide
7.1.2 Running models from a web server
Lab Activities Page 28
Activity 15 – Create an Interactive Dashboard In this activity, you will create an interactive dashboard connected to a universe query. The dashboard
will include three charts organized in tabs to visualize the distribution of net sales, gross sales, and
discounts. In addition, you will add query prompt selectors to allow the dashboard consumers to select
the year and quarter to be displayed.
Tutorial Simulations
Manage query prompts
Group components in a container
Change the appearance of components in a dashboard
Change the color scheme for components in a dashboard
Instructions
1) Create a dashboard based on a query of the Sports universe. Your dashboard should include:
a) 2 Query Prompt Selectors which allow consumers to select the Year and Quarter
b) A Tab Set container which allows consumers to select a chart to view:
i) A pie graph of the Net Sales by Category for the selected year and quarter
ii) A pie graph of the Gross Sales by Category for the selected year and quarter
iii) A column graph of the Discounts by Category for the selected year and quarter
c) Each graph should have a subtitle which displays the selected parameters for year and quarter.
d) Apply a theme to the dashboard.
e) Save the dashboard to the BI Platform.
Lab Activities Page 29
Creating Crystal Reports with Crystal Reports for Enterprise
Activity 16 - Create a Crystal Report using Crystal Reports for Enterprise In this activity, you will create a Crystal Report connected to the Sports universe.
Tutorial Simulations
Concept: Create a report
Concept: Describe how the semantic layer works
Connect to a universe
View the data for a field
Add fields to a report
Preview a report
Documentation
SAP Crystal Reports for Enterprise
http://help.sap.com/bocre
Instructions
1. Log into the BI Launch pad.
2. Click Crystal Reports for Enterprise in My Applications.
Lab Activities Page 30
3. Click Open to launch the Crystal Reports for Enterprise application.
4. Click Sports.unx and click Next.
5. Add Category, Quantity, and Gross Sales to the Results Objects pane.
6. Add a Year filter which prompts the report consumer to select a year.
Lab Activities Page 31
7. Click Finish.
8. Select the year 2010 and click OK.
9. Your report should look like the image below.
Lab Activities Page 32
Activity 17 – Create a Crystal Report with a Crosstab In this activity, you will create a Crystal report, with a crosstab.
1. Log into the BI Launch pad.
2. Use Crystal Reports for Enterprise to create a report, grouped by year, with a Crosstab that
displays quarterly sales for each Category.
3. Save the report in My Favorites as Net Sales Summary.
Activity 18 – Create a Simple Alert In this activity, you will create a report with an alert that will notify report consumers when the total
monthly sales target of 15 million dollars is missed..
1. Log into the BI Launch pad.
2. Use Crystal Reports for Enterprise to create a report that includes Year, Month, Category, and
Gross Sales.
3. Group records by Year.
4. Sort records by Month number.
5. Add Total Gross Sales to each month group.
6. Add Conditional Formatting: If Total Gross Sales < 15 million format the background red, font
white and bold.
7. Add an alert named Missed Monthly Sales Target that is triggered when the total monthly gross
sales for any month is less than 15 million.
8. Add the alert message “At least one monthly sales target has been missed.”
9. Save and refresh the report, and use the Triggered Alerts pane to filter the report based on the
alert formula.
Lab Activities Page 33
Activity 19 – Subscribe to an Alert In this activity, you will subscribe to the alert you created in the previous activity. In addition, you will
schedule the report and view the alert.
1. Log into the BI Platform.
2. Subscribe to the Monthly Gross Sales alert.
3. Schedule the report to Run Now.
Once the report has run, a date will be added to the Last Run column.
4. View the alert.
Exploring Universe Based Information Spaces
Activity 20 – Create an Information Space In this activity, you will create and index an information space for the Sports_Warehouse universe.
Tutorial Simulations
Create an information space based on a universe
Personalize an information space with a custom filter
Documentation
SAP BusinessObjects Explorer
http://help.sap.com/boexpl
Instructions
1. Login to the BI launch pad.
2. Create a new Information Space using the Sports_Warehouse universe.
3. Add the following facets and measures to the Information Space.
Lab Activities Page 34
4. Index the new information space.
Related Tutorials
Schedule indexing of an information space
Hide an information space
Lab Activities Page 35
Activity 21 – Explore the Sports Information Space In this activity, you will explore the Sports_Warehouse information space.
Tutorial Simulations
Search for data
Filter data
Change the chart type
Instructions
1. Login to the BI launch pad.
2. Use Explorer to create the following visualizations:
a. Create a pie showing Gross Sales by Category for Q4 2010 and add a bookmark
b. Create a column graph comparing Net Sales and Gross Sales by quarter in 2010 and add
a bookmark
Related Tutorials
Change the layout for the interface
Sort and rank data
Display an additional measure
Create a bookmark to data
Email data to a recipient
Export data as a CSV file
Export data as an Excel Spreadsheet
Export data as an image
Activity 22 –Explore a Microsoft Excel Spreadsheet In this activity, you will explore an Excel Spreadsheet. Before beginning the activity instructions, read
the information about exploring spreadsheets, dimensions and measures.
Tutorial Simulations
Explore an Excel spreadsheet directly
Exploring Spreadsheets
Explorer spreadsheet data sources must be in a single flat file format where the data is organized in
columns and rows. A data source must include a header row, with business friendly terminology.
Lab Activities Page 36
For optimal use your data source should include two or more dimensions and one or more measures.
Include enough dimensions to allow for drilling to the desired hierarchy level.
Dimensions (or facets)
A dimension (referred to as a facet in Explorer) is a column of data that identifies how a row of data can
be categorized or grouped.
Some examples of suitable dimensions include:
Time series columns (year, quarter, month, week, day, date…)
Geographical areas (region, country, state, district, city…)
Other logical groupings or categories (department, category, product, color, SKU…)
Measures
A measure is a numerical value that can be summarized with calculations including sum, average, min,
max, count.
Examples include:
Monetary values (sales revenue, costs, prices…)
Quantity measures (volume, number of x….)
Other indicators (percentage values, ratios…)
Explorer cannot work directly with relational data, star schemas or OLAP cubes.
If you have relational data in hierarchies, they must be flattened to the desired depth or level, so
that Explorer can turn the corresponding columns into facets.
When using pre-aggregated data ensure that all data is at the same level of granularity (for example
never mix monthly data with weekly data in a single table).
Data Services jobs can be used to generate a flat file table from a star-schema or OLAP data source.
Instructions
1. Open Explorer from the BI launch pad.
2. Upload and configure the eFashionStoreSales.xlsx spreadsheet from the Workbook Resources
file.
3. Explore the data. For example create a chart to compare sales revenue and margins between
different stores in Texas in Q4 of 2006.
Lab Activities Page 37
Activity 23 – Create an Information Space Based on a Spreadsheet In this activity, you will create an information space for the eFashionStoreSales spreadsheet.
1. Upload the eFashionStoreSales spreadsheet to a BI Platform public folder.
You can find the spreadsheet in the Workbook Resources file.
Note: You should create a new folder in the Public Folders for spreadsheet data sources.
Individual users can also create information spaces from files in their own My Documents.
2. Launch Explorer and create a new information space based on the spreadsheet.
3. Click the Information space facets as required.
4. Index and then test the information space.
Lab Activities Page 38
SAP BusinessObjects Web Intelligence
Activity 24 – Create a Web Intelligence Document In this activity, you will create a basic Web Intelligence document using the Sports Warehouse universe
to display gross sales by category.
Note that in initial releases of the BI4.0 Platform, Web Intelligence is named Interactive Analysis.
Tutorial Simulations
Create a query
Run and save a query
Select a subset of data based on a custom filter
Save a document as other formats
Documentation
SAP BusinessObjects Web Intelligence
http://help.sap.com/bowebi40
Instructions
1. Log into the BI launch pad.
2. Open Web Intelligence (Interactive Analysis) from My Applications.
3. Create a new Web Intelligence document using the Sports universe including the
4. Category, Product Name, Date, and Gross Sales objects.
5. Add a filter to restrict data to sales from 2010.
6. Format the table as required.
7. Save the document to the My Favorites folder as “Product Gross Sales By Category”.
Related Tutorials
Select a subset of data based on a predefined filter
Remove a filter from a document
Lab Activities Page 39
Activity 25 – Create a Document using Web Intelligence Desktop In this activity, you will create a Web Intelligence document using the SAP BusinessObjects Web
Intelligence Desktop client. Note that in initial releases of the BI Platform, Web Intelligence is named
Interactive Analysis.
1. Click Start > All Programs > SAP BusinessObjects Enterprise XI 4.0 > SAP BusinessObjects
Enterprise Client Tools > (Web Intelligence or Interactive Analysis Desktop).
2. Enter the login credentials including the password Welcome1 and click Log On.
3. Create a new document with the Sports universe.
4. Add the Customer Name, Product Name, Date, and Gross Sales objects.
5. Add a filter to restrict data to sales from 2010.
6. Format the table as required.
7. Save the file as “Product Gross Sales by Customer”.
8. Publish the document to the BI Platform in your My Favorites folder.
Related Tutorials
Set up preferences for the Web Intelligence Desktop interface
View the SQL for a query
Lab Activities Page 40
Activity 26 – Insert breaks, totals and subtotals In this activity, you will open an existing Web Intelligence document, and apply breaks and sections, and
add totals and subtotals.
Tutorial Simulations
Create breaks between groups of data
Calculate totals and subtotals for data
Navigate a document using the Report Map pane
Instructions
1. Open the BI launch pad.
2. Open the Product Gross Sales by Category.
3. Switch to Design view and add a Break to the Category column.
Scroll through the report to view the impact of the Break.
4. Change the Category column into a Section.
Scroll through the report to view the impact of the Section.
5. Add a Sum calculation to the Gross Sales column.
6. Use the Navigation Map to navigate the report and view the section totals.
7. Save the document.
Related Tutorials
Sort the sections in a document
Lab Activities Page 41
Activity 27 – Sorting and Ranking In this activity, you will create a new Web Intelligence document, and sort the data in a variety of ways.
Tutorial Simulations
Sort the data in a document
Add a ranking to data
Instructions
1. Open the BI launch pad.
2. Open Web Intelligence from My Applications.
3. Create a new Web Intelligence document using the Sports universe including Year, Category,
Product Name, and Net Sales.
Note how the records are sorted by default.
4. Format the data as required.
5. Move the Year column to the right of the product name.
Note the impact on how the records are sorted.
6. Sort the records by Net Sales.
7. Remove all sorts and sort the records first by Product Name and then by Net Sales.
8. Change the Net Sales sort to descending order.
9. Save your changes.
Lab Activities Page 42
Activity 28 – Modify a Web Intelligence Query and Document In this activity, you will open an existing Web Intelligence document, and edit the query to add
additional objects to the table in the document. In addition, you will select objects to create an
additional table.
Tutorial Simulations
Edit a query
Drag and drop objects to create a report
View the metadata for objects
Instructions
1. Open the BI launch pad.
2. Open Web Intelligence from My Applications.
3. Open the Product Gross Sales by Category document.
4. Edit the query and add the Quantity, Net Sales, and Discount measures.
5. Run the Query.
6. Add Net Sales to the existing table.
7. Add a table to the right of the existing table that displays net Quantity, Net Sales, and Gross
Sales for each product.
8. Remove Net Sales from the original table.
Your report should look similar to the image below.
9. Use the Report Map to find a new table with the new data for each Category.
10. Save the report.
Lab Activities Page 43
Activity 29 – Filter the Data in a Web Intelligence Document In this activity, you will create a document using multiple filters to select sales results from a specific
time period.
In addition, you will add Input controls to allow the report consumers to filter the data on the report.
Tutorial Simulations
Remove a filter from a document
Create a prompt to select a subset of data
Select a subset of data based on multiple filters
Create an input control for a document
Instructions
1. Create a new Web Intelligence document using the Sports universe.
2. Add the Customer Name, Category, Product Name, Gross Sales and Net Sales to the report.
3. Add a filter to select only records from Q4, 2010.
4. Change the document title to “Product Sales”.
5. Resize the columns to best display the data.
6. Add a Radio button Input Control that allows you to select a category.
7. Add a Check boxes Input control that allows you to select one or more products.
8. Modify the query filters to prompt for the Year and Quarter.
9. Click Run Query.
10. Select Q1, 2009
11. Save the document as Product Sales.
Lab Activities Page 44
Activity 30 – Add a Chart to a Web Intelligence Document In this activity, you will duplicate an existing report table, and then turn it into a chart.
Simulation Tutorials
Create a chart
Turn a table into a chart
Duplicate a report object
Instructions
1. Create a new Web Intelligence document using the Sports universe.
2. Add the Category, Year, Quarter, Gross Sales and Net Sales to the report.
3. Add Year and Quarter sections.
4. Duplicate the existing table and turn it into a Pie chart
5. Hide the legend and add data labels that show label and percent.
6. Add the title “Net Sales”
7. Save the report as Sales by Category.
Lab Activities Page 45
Activity 31 – Drill into a Web Intelligence Document using Default
Navigation Paths In this activity, you will use the default navigation paths which the information design tool derives from
the data foundation schema.
Tutorial Simulations
Drill into a table
Drill into a chart
Default navigation paths
The information design tool creates four default navigation paths for the Sports universe, one for each
table. The Category, Customer, and Product navigation paths are very simple and allow the user to click
the ID field to “drill” to the name field. For example a user could click Category ID to view the Category
Name.
The Fact navigation path is different, because this table and folder contain more objects. Year, Quarter,
Month, and Date are recognized as date related dimensions. By default the user can start by clicking a
Date to “drill” into the Year. Then select a year to drill to quarter, and finally a month.
Instructions
1. Open the BI launch pad.
2. Open Web Intelligence from My Applications.
3. Create a new Web Intelligence document using the Sports universe.
4. Add Category ID, Date, Gross Sales and Net Sales to the report.
5. Click Run Query.
6. Enable drilling.
7. Test the default Category and Fact (time) navigation paths.
Lab Activities Page 46
Activity 32 – Create a Custom Navigation Path in the Universe In this activity, you will add custom navigation paths which will allow report consumers to drill into the
data in the reports in a different way.
The Products navigation path will allow users to start with a category, then select a product, and then
select customers who have purchased the product.
The Time navigation path will override the default Fact (time) navigation path. The custom Time
navigation path allows consumers to drill down through year, quarter, month, to a specific date.
Tutorial Simulations
Create a custom navigation path
Instructions
1. Open the information design tool.
2. Open the Sports project and double-click the Sports.blx business layer.
3. Insert a custom navigation path named Product that will allow users to drill from Category Name
to Product Name and then to Customer Name.
4. Insert a custom navigation page named Time that will allow users to drill from Year to Quarter
to Month and finally Date.
5. Save and publish the universe.
Activity 33 – Creating Drillable Web Intelligence Documents In this activity, you will create a Web Intelligence document and test the custom navigation paths you
created in the previous activity.
Tutorial Simulations
Change the layout of a table
Instructions
1. Create a new Web Intelligence document using the Sports universe including the Category, Year,
Gross Sales and Net Sales objects.
2. Test the Products and Time navigation paths you created in the previous exercise.
Related Tutorials
Create a table
Delete columns and rows from a table
Lab Activities Page 47
Activity 34 – Create a Web Intelligence Document with a Crosstab. In this activity, you will create a Web Intelligence document with a crosstab.
1. Create a new Web Intelligence document using the Sports universe.
2. Create a crosstab that displays the following data.
3. Split the crosstab into sections by Category. There should be one crosstab for each category.
4. Save the report as Gross Sales Crosstab.
Activity 35 – Add another query to a Web Intelligence Document. In this activity, you will add a second query, and a second report to a Web Intelligence Document.
Tutorial Simulations
Add another query to a document
Instructions
1. Open the Gross Sales Crosstab document that you created in the previous activity.
2. Add a second query that selects Customer Name, Year, Quarter, and Gross Sales.
3. Display the data in a new report inside the document.
4. Rename Report 1: Gross Sales by Category.
5. Rename Report 2: Gross Sales by Customer.
6. Save the document.
Related Tutorials
Add a report to a document
Add a row or column in a table
Lab Activities Page 48
Set up Sample Data Sources and BI Content
Activity 36 – Convert the eFashion.unv Universe to a UNX Universe In this activity, you will convert the eFashion.unv universe (which ships with both BusinessObjects XI 3.x
and the SAP BusinessObjects 4.x suite), and convert it into the new UNX universe format.
This activity provides detailed step by step instructions.
Part 1 - Convert the eFashion.mdb to a SQL Server Database
1. Open SQL Server Management Studio
(Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio)
2. Enter your login credentials and click Connect
3. Expand Databases
4. Right-click the Databases folder and choose New Database…
5. Type the database name eFashion
6. Change the initial size to 12 MB.
Lab Activities Page 49
7. Click OK.
8. Right-click the new eFashion database and choose Tasks > Import Data…
9. Click Next.
10. Choose Microsoft Access in the Data source list.
11. Click Browse…
12. Navigate to C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI
4.0\Samples\webi\
13. Select efashion.mdb and click Open.
14. Click Next.
Lab Activities Page 50
15. Click Next
16. Select all the tables:
Lab Activities Page 51
17. Click Next.
Lab Activities Page 52
18. Click Next.
Lab Activities Page 53
19. Click Finish.
20. Click Close.
Part 2 – Create a new eFashion_SQL Data Source Name
1. Open C:\Windows\System32\odbcad32
2. Click the System DSN tab.
3. Click Add.
4. Click SQL Server Native Client 10.0.
5. Click Finish.
6. Type the eFashion_SQL in the Name field.
7. Type the SAPDEMO in the Server field.
8. Click Next.
Lab Activities Page 54
9. Click Next.
10. Check the Change the default database to: option and select the new efashion database.
11. Click Next.
12. Click Finish.
13. Click Test Data Source.
14. Click OK.
15. Click OK.
16. Click OK.
17. Repeat steps 1 to 16 to create a duplicate DSN in the C:\Windows\SysWOW64\odbcad32
application.
Part 3 – Convert the Sample eFashion Universe
1. Open the information design tool
2. Click File > New > Project
3. Type eFashion and click OK.
4. Click File > Convert .unv universe
5. Navigate to
C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI
4.0\Samples\webi
6. Click eFashion
7. Click Open
8. Click Browse
9. Click eFashion
10. Check Automatically convert @Prompt expressions into universe named parameters.
Lab Activities Page 55
11. Click OK.
12. Click No.
13. Expand the eFashion project.
14. Right-click the eFashion project folder and choose New > Relational Connection…
15. Type eFashion in the resource Name field.
16. Type SQL Server eFashion Database in the Description field.
17. Click Next.
18. Choose Microsoft > MS SQL Server 2008 > ODBC.
19. Click Next.
20. Change the Data Source Name to eFashion_SQL.
Note: Do not use the club-webi or efashion-webi DSN.
Lab Activities Page 56
21. Click Test Connection.
22. Click OK.
23. Click Next.
24. Click Finish.
25. Right-click the new eFashion Connection and choose Publish Connection to a Repository.
26. Click Next.
27. Click Finish
Lab Activities Page 57
28. Click Yes
29. Click Close.
30. Double click the data foundation (eFashion.dfx).
31. Click the Connections tab.
32. Click the Change Connection button.
Lab Activities Page 58
33. Select the Secured eFashion.cns connection.
34. Click OK.
35. Click File > Save All…
36. Right click the business layer (eFashion.blx) and choose Publish to a Repository
Lab Activities Page 59
37. Click the Check All link
38. Click Check Integrity.
39. Click Next.
40. Click Finish.
41. Click Close.
Part 4 – Test the new eFashion.unx Universe
1. Login to the BI launch pad.
2. Create a new Web Intelligence document using the eFashion universe.
3. Create a variety of queries using different dimensions and measures.
Lab Activities Page 60
Activity 37 – Attach the Other Sample SQL databases In this activity, you will attach additional sample SQL databases which are used in a variety of training
and demonstration resources on the SAP Community Network.
This activity provides detailed step by step instructions.
Instructions
Download the Sample Databases
Additional Data Sources
The following data sources are used in the official product tutorials referenced in this workbook
1. Download the sample databases
a. Club Database
b. Warehouse
c. Xtreme SQL
d. Xtreme MDB
2. Attach the Club, Warehouse, and XtremeSQL Databases to SQL Server.
a. Open SQL Server Management Studio.
b. Login as shown in the dialog below.
Note: You may need to switch server types to view the (local) server name.
Lab Activities Page 61
c. Right click Database in the Object Explorer and choose Attach…
d. Click Add…
e. Select the database you want to attach:
f. Click OK.
g. Click OK again.
h. Repeat these steps to attach the three sample databases.
Page 62
Troubleshooting
Logon denied: Your system does not allow the use of this application
This error occurs when the license keys for the BI platform have expired. Log into the Central
Management Console and update the license keys.
Error: Server SAPDEMO:6400 not found or server may be down (FWM
01003) null
This error occurs when the server is no longer running. To restart the servers open the Central
Configuration Manager (Start > All Programs > SAP BusinessObjects Enterprise XI 4.0 > SAP
BusinessObjects Enterprise > Central Configuration Manager) and restart the Server Intelligence Agent
(SAP Demo)
Page 63
Page 64
Workbook Evaluation
BI Activity Workbook Training Questionnaire
Please mark an x in the appropriate column on the right.
Strongly
Agree Agree Neutral Disagree
Strongly
Disagree
1 The learning objectives in the workbook activities were
clear and well suited to the needs of consultants new
to SAP BusinessObjects BI 4.
2 The level of the content in the workbook is well suited
to the needs of consultants new to SAP
BusinessObjects BI 4.
3 The learning objectives were met by the activities in
the workbook.
4 The activities and step by step instructions in the
Solution Guide (if provided) were clear.
5 The activities were interesting and practical.
Excellent Very
Good Good Fair Poor
6 The quality of the prerequisite materials was
________:
7 The quality of the workbook and solution guide was
________:
Too easy Just
right
Too
difficult
8 The activities in the workbook were _________:
Page 65
For which of the following topics would you most like to see similar or additional training?
Please check your top five priorities and rank them on the right.
Developing BI content for mobile devices _____
Administration and Security (Setting up users and groups, securing content) _____
Universe Design _____
Crystal Reports 2011 _____
Crystal Reports for Enterprise _____
Web Intelligence _____
Dashboards _____
Explorer _____
Analysis, version for OLAP _____
Analysis, version for MS Office _____
BI for ERP (A1/B1) _____
Other:
________________________________________ _____
________________________________________ _____
________________________________________ _____
Which format(s) do you find most useful for training?
Prerequisite Presentations
o Recorded eBook _____
PowerPoint slides _____
Tutorial Simulation _____
Activity Workbook _____
Activity Workbook Solution Guide _____
Activity Workbook Solution files (completed activity files such as .rpt or .xlf files) _____
Page 66
Recorded Solution Demos (recorded demonstrations of activity solutions) _____
Other
____________________________________________________________________ _____
____________________________________________________________________ _____
____________________________________________________________________ _____
Additional Comments
Please email feedback to [email protected]