Developer's World

Make-to-Order Production with Variant Configuration

2014-09-30T14:02:00.001+08:00

I found this as interesting presentation and wanted to share with you all.

Key Process covered:

Sales Order processing for configurable materials
Manufacturing
Delivery
Billing
Plant Closing
Company Closing

Presentation 'Make-to-Order Production with Variant Configuration SAP Best Practices Baseline Package SAP Best Practices.'

Slide 1

Make-to-Order Production with Variant Configuration SAP Best Practices Baseline Package SAP Best Practices

Slide 2

Scenario Overview – 1 Purpose This scenario describes a business process, which is typical for companies which are manufacturer of products with variants. Benefits Variants of a product were represented over one configurable material Sales & Operations Planning can be done for predefined material variants Key process flows covered Sales order processing for configurable material Manufacturing Delivery Billing Plant closing Company closing Purpose and Benefits:

Slide 3

Scenario Overview – 2 Required SAP enhancement package 3 for SAP ERP 6.0 Company roles involved in process flows Production Planner Manufacturing Warehouse Clerk Plant Controller Buyer SAP Applications Required:

Slide 4

Scenario Overview – 3 Make-to-Order Production with Variant Configuration The scenario demonstrates sales order processing with materials with pre-selected characteristic values (material variants) and components that are produced according to sales quantities planned for these variants. If the sales order configuration is not available as a material variant, then customer service can configure the material on the order by choosing the required characteristic values. A sales order cost estimate is created on saving the order which is subsequently used to valuate the cost of goods sold. The process is triggered when an order for a configurable material is received from the customer. The customer order is recognized in the MRP run resulting in planned order for production of the material. If insufficient warehouse stock is available, purchase requisitions are created for the raw materials required. When the production order is created, target costs are calculated for the order lot size (preliminary costing). During the production process, costs incurred are updated on the order, which enables you to keep track of and compare target costs and actual costs at any time. Period-end-closing activities are applied to the order. This includes Work In Progress calculation and variance calculation. After this, Work in Progress is settled to financial accounting and production variances are settled to management and financial accounting. Production variances are settled to profitability analysis with the sales order as one of the characteristics. Detailed Process Description:

Slide 5

Process Flow Diagram Make-to-Order Production with Variant Configuration Event Manufact uring Sales Order Entry (MTO) Request for MTO Sales Order Order Confirmation Sales Configurati on is Available as Material Variant? Stock for Material Variant is Availabl e? Manufacturing Steps: Production Order Processing Make-to-Stock (MTS) (145) Transfer Order Processing (creation and confirmation in backround) Logistics Planning: Logistics Planning (144) Delivery Due List Billing Credit Managemen t (108) (Delivery Note) Credit Management (108) Sales Billing Credit Approv ed Credit accept ed(Cus t. 100003) Daily shipment worklist, enough inventory Yes No Make-to-Order Production Warehouse Clerk Credit accept ed(Cus t. 100003) Yes No Pick List Inventory COGS Delivery Note Customer Payment received AR Document Yes No Strategic Planner Customer Service Post Goods Issue

Slide 6

Legend SymbolDescriptionUsage Comments Band: Identifies a user role, such as Accounts Payable Clerk or Sales Representative. This band can also identify an organization unit or group, rather than a specific role. The other process flow symbols in this table go into these rows. You have as many rows as required to cover all of the roles in the scenario. Role band contains tasks common to that role. External Events: Contains events that start or end the scenario, or influence the course of events in the scenario. Flow line (solid): Line indicates the normal sequence of steps and direction of flow in the scenario. Flow line (dashed): Line indicates flow to infrequently- used or conditional tasks in a scenario. Line can also lead to documents involved in the process flow. Connects two tasks in a scenario process or a non-step event Business Activity / Event: Identifies an action that either leads into or out of the scenario, or an outside Process that happens during the scenario Does not correspond to a task step in the document Unit Process: Identifies a task that is covered in a step-by-step manner in the scenario Corresponds to a task step in the document Process Reference: If the scenario references another scenario in total, put the scenario number and name here. Corresponds to a task step in the document Sub-Process Reference: If the scenario references another scenario in part, put the scenario number, name, and the step numbers from that scenario here Corresponds to a task step in the document Process Decision: Identifies a decision / branching point, signifying a choice to be made by the end user. Lines represent different choices emerging from different parts of the diamond. Does not usually correspond to a task step in the document; Reflects a choice to be made after step execution.

SymbolDescriptionUsage Comments To next / From last Diagram: Leads to the next / previous page of the Diagram Flow chart continues on the next / previous page Hardcopy / Document: Identifies a printed document, report, or form Does not correspond to a task step in a document; instead, it is used to reflect a document generated by a task step; this shape does not have any outgoing flow lines Financial Actuals: Indicates a financial posting document Does not correspond to a task step in a document; instead, it is used to reflect a document generated by a task step; this shape does not have any outgoing flow lines Budget Planning: Indicates a budget planning document Does not correspond to a task step in a document; instead, it is used to reflect a document generated by a task step; this shape does not have any outgoing flow lines Manual Process: Covers a task that is manually done Does not generally correspond to a task step in a document; instead, it is used to reflect a task that is manually performed, such as unloading a truck in the warehouse, which affects the process flow. Existing Version / Data: This block covers data that feeds in from an external process Does not generally correspond to a task step in a document; instead, this shape reflects data coming from an external source; this step does not have any incoming flow lines System Pass / Fail Decision: This block covers an automatic decision made by the software Does not generally correspond to a task step in the document; instead it is used to reflect an automatic decision by the system that is made after a step has been executed. External to SAP Business Activity / Event Unit Process Process Reference Sub- Process Reference Process Decision Diagram Connection Hardcopy / Document Financial Actuals Budget Planning Manual Process Existing Version / Data System Pass/Fail Decision

An internal error occurred while calling the < api_name > API (Error: ERR_WIS_30270)"

2012-11-02T00:46:00.002+08:00

When running an interactive report you may encounter the following error message:

"An internal error occured while calling 'processDPCommands' API (Error: ERR_WIS_30270)"

This message will appear if your server is running out of memory.

To free up your server memory we recommend that you turn off real-time caching in Business Objects as follows:

1. Log into the Business Objects CMC.

2. Go to the 'Servers' area of the CMC.

3. Double-click on the WebIntelligenceProcessingServer server to open the properties.

4. Scroll down the page and uncheck the 'Enable Real-time Cache' checkbox.

5. Click 'Save and Close'.

6. Highlight the "WebIntelligenceProcessingServer" server which should now have a flag next to it.

7. Right-click and select 'Restart server' for the change to take effect.

Your server will now have memory available to it and you should find that the error message will not be shown.

BO 4: Run a Webi report against a specific job server

2012-10-10T05:09:00.000+08:00

How do you force a Webi report to run of a Business Objects job server?

There will be some situations where you want to run a report on a separate BO adaptive job server or report server or both. This article shows you how to make it happen when you are required to do so.

Why basically users required to run reports against a specified Job or Report server?

Reason might be one of these: For example,

You have configured a new thing on a particular webi report/ report server and now you want to run it separately to test the report / server.
Your report fails to refresh the data very frequently and perhaps you want to direct certain reports to certain report and job servers in order to do your own load balancing in scheduling some intense reports.

Let's see how we can force the report against a specific job server.

Step 1: Login to the Central Management Console(CMC)

Basically, in most of the organizations end users/developers won't be getting access to CMC. In that case, contact your BO administrator.

Step 2: Create a new server group under 'Server > Server Groups' section.

Step 3: Goto 'Server' section of CMC and create a new job server under 'Web Intelligence Services'.

In my case, I have cloned the existing job server and named it as 'Opside Job server 01'.

Step 4: Now goto the properties of newly created job server and assign the newly created server group under 'existing server group' section.

Step 5: Locate the desired report in CMC under folder section which you want to force run against the above created job server.

Step 6: Goto the properties of the report and select the Server Group under 'Default setting > web Intelligence Process Setting' Section.

Reports can also be scheduled to run against the specific job server by selecting the server group under Default setting > 'Scheduling Server Group' Section.

Adding only the job server to the group won't work. you also need to add Adaptive processing server, DSL Bridge, Web Intelligence processing server to the group to run the report. These servers can be shared among various groups or create a new set of servers.

OK,It's the time to test this out... Give it a try. If you get any errors check the job server settings and assignments.

OpenDoc URL's with SAP BI4.0

2012-06-18T21:32:00.051+08:00

Overview

OpenDocument is functionality in BusinessObjects which enables URL reporting and linking documents. It processes incoming URL requests for documents in the Central Management Server (CMS) and delivers the correct document to the end user.

OpenDoc basically provides URL access to documents and avoid having them to navigate through a folder hierarchy.

Using OpenDoc you can link to the viewable objects like:

Ø Crystal Reports

Ø Web Intelligence documents (WebI)

Ø Dashboards

Ø Interactive Analysis documents

Ø BI launch pad workspaces

Note: This document deals with linking of Webi documents.

OpenDocument Syntax

Basic URL

The OpenDoc syntax and its parameters allow you to construct URLs that link to viewable objects.

Server specific:

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?< parameter1 >&< parameter2 >&...&< parameterN >

Note: Once the report is moved from one server to another i.e. Dev to QA to Production server. Administrator has to modify the OpenDoc URL to current server name & port.

Non-Server specific:

../../opendoc/openDocument.jsp?< parameter1 >&< parameter2 >&...&< parameterN >

Note: This syntax doesn’t require any modification.

URL syntax consideration

· Ampersand (&) is used to join all the parameters

· Server name and port refers to the BO Enterprise server name and port.

· Parameter link cannot contain spaces or special characters. Replace the space with plus sign (+). Eg. &sDocName=Sales+Report

· All OpenDoc parameters and parameter values are case sensitive.

· OpenDoc URL cannot exceed the maximum character limit. Therefore, know the browser character limit to ensure you URL will be within the limit.

Document Identifier Parameter

Identifier Parameters are used to identify the target document by its ID, Name, Type and Instance.

Parameter Description

Parameter	Description	Mandatory	Code Sample
iDocID	Specifies the unique identifier of the viewable object.	Yes, but can be replaced by sDocName	iDocID= Aa6GrrM79cRAmaOSMGoadKI
sDocName	Specifies the name of the viewable object.	No	sDocName=Sales+in+2003
sIDType	Specifies the type of object identifier used.	If you pass in an InfoObject ID as a value to iDocID rather than CUID, you do not need to specify this parameter	sIDType=CUID
sInstance	Specifies the scheduled instance of the target document to open.	No	sInstance=Last; sInstance=User; sInstance=Param;
sType	Specifies the type of the target document	No	sType=wid

Example:

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sInstance=Last

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=346&sInstance=Last

Input parameters

Input parameters are used to pass the parameter values to the target document.

Parameter	Description	Mandatory	Code Sample
lsC	Specifies a contextual prompt for Web Intelligence documents if there is an ambiguity during SQL generation.	No	openDocument.jsp?iDocID=346&sType=wid &sRefresh=Y&lsC=Reservations
lsI	Specifies index or key value. This parameter must be associated with one of the parameters lsS, lsM or lsR.	No	lsMStore=e-Fashion New York Magnolia&lsIStore=2
lsM[NAME]	Specifies multiple values for a prompt.	No	lsMSelectState=[Alberta],[Washington]
lsR[NAME]	Specifies a range of values for a prompt.	No	lsRparamNumberR=[1..3]
lsS[NAME]	Specifies a value for a single prompt.	No	lsSparamCurrency=121
sRefresh	Indicates whether a database refresh should be forced when the target document is opened.	No	sRefresh=Y or sRefresh=N
sReportName	Specifies the report to open if the target document contains multiple reports.	No	sReportName=First+Report+Tab

Example:

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?sDoc=IndexTest&sType=wid&lsMStore=[caption]&lsIStore=[index]&sRefresh=Y

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sRefresh=Y&lsMSelect+Cities=[Paris],[London]&sRefresh=Y

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sRefresh=Y&lsSSelect+Cities=[Paris]&sRefresh=Y

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sRefresh=Y&lsRparamNumberR=[1..3]&sRefresh=Y

Output parameters

Output parameters are used only if the report output is required in specific format or force to display any prompt values not specified in the URL.

Parameter	Description	Mandatory	Code Sample
NAII	Indicates whether to force the display of the prompt selection page.	No	NAII=Y (prompt values that are passed with lsS, lsM, or lsR in the URL are applied and not displayed in the "Prompts" dialog box)
noDocument	A value of true forced a report to open in design mode using the existing report template.	No	noDocument=true automatically forces a Web Intelligence report into design mode.
sViewer	Specifies the selected report viewer.	No	sViewer=html
sOutputFormat	Specifies the format in which to open the target document	No	sOutputFormat=H; sOutputFormat=P (H- HTML, P- PDF)
sWindow	Indicates whether the target report will open in the current browser instance or launch a new one	No	sWindow= Same; sWindow= New

Example:

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&lsSYear=FY1999&NAII=Y&sRefresh=Y

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sViewer=html

http://< server >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=6471&noDocument=true

http://< servername >:< port >/BOE/OpenDocument/opendoc/openDocument.jsp?iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID&sRefresh=Y&lsRparamNumberR=[1..3]&sRefresh=Y&sWindow=New

Steps to create an OpenDoc URL

OpenDocument work with the web version of WebIntelligence (WebI) and it is not supported by client version. We can create OpenDoc URL in client version. But, to test the OpenDoc URL we need to export the reports to CMS.

OpenDoc URL can be created by two ways

· Using Wizard

· Custom Linking

Now let us create an OpenDoc link by passing parameters between two WebI reports.

Report 1: Sales report (summarized report).

I have Quarter, Month and Week fields in the report filter section & Store name, Year, Category and Sales Revenue in report layout.

Report 2: Details of sales report (the complete details of the Sales report) which has prompts to filter the report for the parameter value picked by OpenDoc URL from Report 1.

Linking Webi Documents using OpenDoc Wizard

Step 1: Logon into BI launch pad using your login credentials.

Step 2: Open your preferences and make sure you have below settings.

Step 3: Open the report (Design mode) in which you want to have the OpenDoc URL.

In this report, we have Quarter, Month and Week as report filters and Category, Year, Store name (Section) and Sales revenue as report objects.

Step 4: Right click on the Sales revenue measure in the report. Select “Linking -> Add Document link”.

Step 5: A new window will open. Click on browse and select the target document.

Step 6: Select all prompts available in report 2 for OpenDoc URL. Click Ok.

Step 7: Check the option “Use complete URL path to create hyperlink”. Accept the message by clicking ‘Yes’.

You can also check the other available options as per your requirement.

Link to document instance: If this is checked, the OpenDoc URL is linked to the latest refreshed instance of the target report.

Target area within the document: If this is checked, the OpenDoc URL jumps to the specific report tab of the target report.

Step 8: Choose “Select Object” for the document prompt values from the dropdown. I am using this, to filter the target report by picking the dimension values of the corresponding quantity and show the details in the target report.

Step 9: Click OK and check the report. You’ll be seen with “#Multivalue” error.

#Multivalue error:

Cause: In the report layout we have only 3 Dimensions and Sales Revenue is aggregated by those 3 dimensions whereas when we are linking the document to the target document we also did with Quarter, Month and Week.

By default, the wizard picks only single parameter value for the specified prompt i.e. it uses only lsS[NAME]. In this case, we need to have only those dimensions which are in the report layout to avoid this error or create a custom URL to pick multiple parameter values.

Note: Refer “Custom linking Webi documents” to write you own custom OpenDoc URL.

Step 10: Now, go to step 6 to select only the dimensions as prompts which are in the report layout and follow the same steps to complete the URL.

Click Ok and proceed to assign the dimension for the selected prompts.

Leave the rest of the prompts value as “Use document default” and click ok.

Note: In this case, the target document shows perfect data only when the source report is not filtered by any dimension.

Step 11: Finally, we have linked two Webi reports. Click on the Quantity value and the second report open up showing the details of the quantity.

If.. Then .. Else In BEX Formula

2012-06-04T16:33:00.000+08:00

You can make conditional calculations using Boolean operators An expression in the form

IF THEN ELSE can also be made using a formula in the form

* + NOT *

Example: IF Actual Costs > Plan Costs THEN Plan Costs + Plan Deviation ELSE Actual Costs

can be written as the following formula:

(Actual Costs > Plan Costs)* (Plan Costs + Plan Deviation) + (NOT (Actual Costs > Plan Costs)) * Actual Costs

(Actual Costs > Plan Costs)* (Plan Costs + Plan Deviation) + (Actual Costs <= Plan Costs) * Actual Costs

BObj Explorer 3.2 (Polestar) Installation & Configure

2010-10-05T11:11:00.004+08:00

Here is the article of Bobj Explorer installation and configuration in detail.
The installation of Bobj explorer takes about 30 mins.
This article is well written by "Gingle" explaining each and everything in detail with screen shots
http://weblogs.sdn.sap.com/pub/wlg/20694

Dynamic measures selection with Webi

2010-09-07T11:47:00.002+08:00

As we know, Web Intelligence is a very flexible reporting tool with advanced analytic functionalities. In this post, I’ll demonstrate how to implement a dynamic measures selection solution with Webi. Before we start, let me explain what is “dynamic measures selection”: for example, we have measures including sales quantity, sales revenue, discount amount, target sales revenue, etc. Users want to select which measures to be displayed on the report dynamically. The business background of this requirement is that for each measure, there’re actually multiple measures like this year value, last year value, variance, growth rate, etc. So displaying all measures at once may result in a too large report. So users want to just focus on some of the measures they are interested in.

Read in detail

SAP BusinessObjects Universe concepts

2010-04-21T15:32:00.001+08:00

Business Objects Universe file

BusinessObjects Universes are files made up of objects and classes that map source data structure in the database to the business terms used by the non-IT report authors and analysts.
For reporting and analysis, the business users select the universe they want to use and they build queries in Web Intelligence, Web Intelligence Rich Client, Desktop Intelligence and other tools. The query is built by selecting objects defined in the universe, without knowing anything about the underlying data structures in the database.

Technically, the universe file has an .unv extension and contains the following information:

· Connection parameters to a data source - only one source can be defined for a universe. However if necessary, universes can be joined on a report level (in WebIntelligence for instance).

· SQL structures (objects), grouped into classes, that map to actual SQL structures in the database such as schemas, tables, columns, functions and procedures.

· A diagram of the tables and joins used in the database structures that are included in the schema.

Through universes, the business users can access data stored in relational databases, OLAP cubes, metadata sources, JavaBean data sources and personal data files.

Universe modeling process

The design and scope of a universe schema should always derive from the end-user requirements as it provides an interface for them to run queries against a source database, create reports and analyze data.

It is absolutely necessary to understand the underlying data prior to starting the universe development process. Every universe contains the following two structures that represent the source data:

· Class - is a logical grouping of objects within a universe which represents a category of objects, indicates the category of the contained objects. Classes can be divided hierarchically into subclasses and represented as folders on a tree hierarchy in the universe pane.

· Object - is a component named with a business naming standard that maps to data in the database or calls a database function or uses other objects from within the same universe. Each objects can be qualified as a Dimension, a Detail or a Measure. Objects represent meaningful entities, facts or calculations used by the business users.

BusinessObjects Universe Designer

Universe Designer is a tool that lets users create, modify, deploy and read definitions of the universes, stored in the BusinessObjects repository. It is a client application, accessible after the installation in Start > Programs > Business Objects XI > BusinessObjects Enterprise> Designer.

A high-level overview of the universe creation process is the following:

Analysis of the end-user requirements is the most important task in the process. Users must be involved in the universe development process as it should fulfill their needs both with the business language and naming convention and the data that can be accessed.
It strongly advisable to spend most of the universe development time and put much effort in the Preparing, Analyzing and Planning phases.
Log on to the BO Repository and select on of three available options for opening universes: Create a new universe, Import a universe from the repository, Open a universe directly from the file system.
A universe becomes visible in the SAP BusinessObjects platform and is available for reporitng once it has been exported to the repository. Thus in most cases working with Universe Designer includes three steps: importing a universe, making changes, then deploying the universe to the repository.
Connect to the source database where data is stored using a connection wizard and save the connection parameters. Universe Designer can store connection definitions to multiple data sources, however only one connection can be defined for each universe.
The database tables and views are selected and browsed through a graphical interface in the Universe Designer (a schema diagram). The schema diagram interface is used for manipulating tables, creating joins, alias tables, contexts and resolve loops in the schema definition.
An object explorer view provides a windows-explorer-like tree to represent and arrange objects that map to the columns and SQL structures, represented in the schema view. When building reports and performing analysis, business users select these objects to run queries against a database.
Building the universe inludes the following sub-tasks performed by a developer: Name the universe, Set up the universe parameters and connect to the data source, Create aliases and contexts, Create joins, Create classes, subclasses and objects, Define dimensions, details and measures, Define hierarchies, Define lists of values and help text, Define filters and implement user security.
Once the universe has been built, tested and has passed all quality assurance checks it is ready to be deployed to the production environment.

Working with BusinessObjects Universe Designer

The operational commands can be issued in Universe Designer in a few ways:

· Menu options - the menu bar has standard options known from other windows based applications, which are: File, Edit, View, Insert, Tools, Window and Help

· Toolbar buttons - three toolbars are available: Standard toolbar, Editing toolbar, Formula Bar toolbar.

· Right-click menus - display on the screen when the right mouse button is clicked. These menus usually give developers access to options related to the performed task, for instance doing a right-click in the Universe pane shows a drop-down menu for creating classes and objects.

Universe Designer window

The BO Universe Designer window is split into two main areas:

Structure pane - located on the right-hand side. The place where users insert the database tables and view the universe structure that generates the FROM and SELECT clauses into a SQL statement.
The structure pane is a dynamic graphical view which can be adjusted easily and navigated with such commands as zoom, arrange tables option, drag and drop operations, roll-up/unroll tables (show all columns or table header only). Furthermore, a View table/column values option is available for gaining a partial view of the data content of the given table or column.
Universe pane (object explorer) - located on the left-hand side, in which users create the classes and objects seen later when building queries using the given universe. The objects physically point to the tables from the Structure pane.

Universe designer view with object explorer view (left-hand side) and a schema diagram (in the center)

Schema design: adding tables

A Table Browser a component available in the Universe Designer which allows the designer to select tables from the target database (it shows a list) and place them on a structure pane, which shows a visual representation of the physical data structure to which the universe is mapped.
The Table Browser is an independent window that shows a tree view of the tables, views and columns in the database, which can be easily inserted into the designed schema.

The Table Browser can be invoked using one of the following methods:

· Click the Table Browser button.

· Select Insert Tables from the menu.

· Double-click the background area of the structure pane.

· Right-click the Structure pane and choose Tables from the drop-down menu.
Single tables or multiple tables simultaneously can be inserted into the schema (with drag and drop, an insert button or just double-clicking a table).

The Table Browser can be also used to preview the source data in a table or a column of an underlying database. To do this, expand a table in the Table Browser, right-click a column or the entire table and click View Table Values.

To get a better view of the tables in the structure pane, click the Arrange Tables button to arrange tables horizontally.

Schema design: creating joins between the tables

Joins create a relationship between the tables so that the correct data is returned for queries that are run on multiple tables and ensure that the data is combined in a meaningful way. If no joins are specified in a database structure, the resulting query produces a Cartesian product, which outputs a report that joins every column in the first table to every column in the second table.

There are several approaches to defining joins in Universe Designer:

· Manually in the schema, graphically by using the mouse to drag a line between columns in separate tables.

· In the Edit Join dialog box - select Insert -> Join from the menu or click the Insert join button.

· Using the Join SQL Editor to explicitly type in the join expression.

· Joins can also be detected and created automatically. Be careful using this method as it might produce errors because of the fact that the algorithm uses column names as the key for creating joins.

Join properties

Each join has the following properties to define:

Table1 - table at the left of the join.
Table2 - table at the right of the join.
Operator - defines how the tables are joined. The available operators are: =, !=, >, >=, <, <=, Between (theta join) or Complex
Outer Join - determines which table contains unmatched data in an outer join relationship.
Cardinality - defines the cardinality for the join.
Shortcut Join
Expression - an editable WHERE clause used to filter the data that is returned when the two joined tables are included in a query.

Join cardinality

The join cardinality defines how many rows in one table will match those in the other. Universe Designer uses cardinality to detect and resolve loops.
The cardinality can be:
- One-to-one (1-1)
- One-to-many (1-N)
- Many-to-one (N-1)
- Many-to-many (N-N)

Join cardinality can be set manually or with the automatic detection tool. For efficiency (detection tool runs 3 queries on every join, which can take a long time) and accuracy (works ok only with totally normalized data) it is strongly advised to apply cardinality manually.

Schema design: creating dimensions

Dimension objects and classes form Business Objects universes.
Objects in a universe are grouped into classes and each object must belong to a class. Classes can be defined as folders and can contain subfolders to form a tree hierarchy and break down objects into subsets.
Objects represent meaningful entities, facts or calculations that end users see in the Business Objects end-user querying tools (WebIntelligence for instance). End-users select objects (or drag them from Query Panel Data Tab into the Result Objects pane) to build and run queries and reports.

Each object in a Business Objects universe can be one of the following types:

Dimension is a main analysis object in a query. It maps to one or more key columns in a database (key to a query, not necessarily primary key database columns). Dimension objects can be organized hierarchically within a class to make default hierarchies for drill-down operations. For example, time can be a dimension with year-month-day hierarchy.
Detail - provides descriptive detail data about a dimension (maps to one or more columns or functions). For example, a month name in time dimension can be a detail of month index.
Measure - provides metrics (aggregated numbers) by which dimensions are compared.

To understand the concept better, in the screenshot below there is an example of a store dimension, where:
- Store is the name of the dimension
- Store and Store Details are classes (Store Details is a subclass of Store)
- State, City, etc... are dimension objects
- Zip code and address are dimension details objects
- Extended sales floor size is a measure object Store dimension example

Creating dimension classes and objects

There are several ways to create dimension classes and objects in the Universe Designer:

The easiest way to create classes and objects is simply by dragging the entire table from the Structure pane into the Universe pane in Universe Designer. It is necessary to review all objects and set properties like change names, enter descriptions, object classification.
Click an existing class in the Universe pane and select Insert->Class from the menu or Insert Class from the Editing toolbar or doing a right mouse click.
To create an object, click an existing class in the universe pane and select Insert->Object from the menu or Insert Object from the Editing toolbar or doing a right mouse click. The Edit properties window will pop up.

Schema design: creating measures

Measure objects provide calculated numeric information (aggregated numbers) by which dimensions are dynamically compared. Measures are flexible as the values they store depend on the dimension objects used with them. Measure object are calculate with one of the aggregate functions.

SAP Business Objects provides the following most commonly used basic types of aggregation:

Sum
Count
Average
Maximum
Minimum

The full set of aggregate functions is listed in the Number Functions selection list of the Edit Select Statement window.

Sample measures folder

From the technical point of view and speaking the SQL language, whenever a query contains measures with dimensions, a SELECT with GROUP BY statement is created. Whenever the SELECT statement contains an aggregate, every dimension column of that aggregate must appear in the GROUP BY clause and measures must include an aggregate functions (sum, avg, etc.).

There are two levels of aggregation in the SAP BusinessObjects query process: at SELECT level and at projection level.
Aggregation at SELECT level starts when a query is created by an user, a reporting tool creates the SQL and sends a SELECT statement to the database. Then the data is returned to a microcube where the first level of aggregation occurs, the microcube projects the aggregated data onto a report, the SQL is run and the microcube gets the results.
Aggregation at projection level means that when a query is run and the resultset is loaded into the microcube, all from that microcube is projected into a table or chart in the report (a block). Therefore, no projection aggregation takes place.