STEP 9: EXPORT UNIVERSE

Using this Reporting option, the Universe will be migrated from Local system into the Universe domain area for making universe available for all reporting clients.

Note: For Local systems Export Universe is not required as Client and Server is on the same system.

CREATING WEBI REPORTS

          Based on the Universe created

----àClick on Start Menu

----àPrograms

----àBO XI 3.1

----àBusiness Objects Enterprise

----àWeb Intelligence Rich Client

----àEnter BO Login information

----àClick on Logon

----àPlace the cursor in “Create New Document based on Data Source”

----àClick on browse for more Data Source

----àSelect Radiobutton Universe

----àClick on Next

----àSelect the created Universe

----àClick on Ok

----àOpens Create Query Panel

----àDrag the required Reporting Objects into “Resulting Area”

----àClick on Run Query

It generates a Report

While Saving WEBI Documents, by default it is saved with extension “.wid”

 [Web Intelligence Document]

STEP 8: CEATING HIERARCHIES:

Using this reporting option we can enable Drilldown and Drillup options.

In the Universe, Hierarchy is created using Dimension objects under the Class panel. All the Objects existing under the class panel as Dimensional object are treated as Default Hierarchies.

Custom Hierarchy:

It is used to define own Hierarchy level based on existing dimension object under the Default Hierarchy.

To create the Custom Hierarchies

----àClick on Tools menu

----àHierarchies

----àDisplays the List of Default Hierarchies

----àSelect the Radiobutton Custom Hierarchies

----àClick on New

----àName the Hierarchy

----àAdd Year from the Default Hierarchies into Custom Hierarchy area.

----àDrag Quarter under Year Hierarchy

----àDrag Month name under Quarter Hierarchy

----àThen WeekID under Month name

----àClick on Ok

Note:

When Custom Hierarchies are created, the Default Hierarchies are disabled in Reporting Environment.

When Multiple Custom Hierarchies are crated at the time of Reporting Business Objects prompts to select the required Hierarchy level to be used in reporting.

Query Filter/ Report Filter/ Level Restrictions

Query Filters:

If the Filter conditions are applied at Database Table is called as Query Filters.

Report Filters:

If the filter conditions are applied at reporting environment is called as Report Filters.

If the Filters are applied in Universe, these are called as Query Filters as Universe gets only restricted data into Reporting environment.

In the Universe, we can apply two types of Filters as

-------Object level Filter

-------Universe level Filter

Object level Restrictions/Filters:

Universe Level Restrictions/Filters:

To apply Object Level Restrictions

--------Double click on the required object

--------Place the cursor in where class and apply the required conditions.

To apply Universe Level Restrictions

-----àSelect the Radiobutton filters in Class Panel

-----àClick on Insert Menu

-----àSelect Condition

-----àPlace the cursor in where class

-----àApply the condition

-----àSelect the checkbox; use the filter as mandatory in query

-----àSelect the Radiobutton Apply on Universe or Apply on class

Data Merging/ Aggrigation

DATA MERGING:

          The joins applied in Universe structure panel between the Two Tables is the Data Merging.

DATA AGGREGATION:

          It is the process of converting Source detail data into Summary data, for the numeric objects (FACTS) under the class panel.

---àDouble click on amount_sold

---àSelect Object from the class panel

---àClick on Properties Tab

---àSelect the Qualification as Measure and select the required aggregated function. By default is Sum.

---àClick on Ok

Note: One source table column is not only added for one time under the class panel, it can be added for any number of times based on Business needs.

If report requirement is to display summary totals of amount_sold and also average total of amount_sold, add amount_sold for two times as an Object in a class. For the first Object add the qualification Measure with the function Sum. For the second Object add the qualification Measure with the function Average.

Data Scrubbing in Universe

DATA SCRUBBING CONCEPT IN UNIVERSE:

----àClick on Insert Menu

----àObject

----àName the Object [overall_cost]

----àData Type----Number

  Place the cursor in Select

----àBrowse

----àExpand shopfacts---Double click on amount_sold multiplied by Double click on quantity_sold [amount_sold * quantity_sold]

----à Click on Parse

If Parsing is Ok ----Click on Ok

CONDITION BASED DATA SCRUBBING:

----àClick on Insert Menu

----àObject

----àName the Object [discount]

----àData Type----Number

  Place the cursor in Select area

----àBrowse

----àExpand Character functions

----àDouble click on IIF (If and only If)

----àDouble click on quantity_sold and type >=10, place the cursor in true condition area----Double click on amount_sold multiplied by 0.12, place the cursor in false condition area ---Double click on amount_sold multiplied by 0.04

Finally the expression is

IIF (shop_facts.quantity_sold>=10, shop_facts.amount_sold*0.12, shop_facts.amount_sold*0.04)

----à Click on Parse

If Parsing is Ok ----Click on Ok

Data Cleansing in Universe

DATA CLEANSING CONCEPT IN UNIVERSE:

----àClick on Insert Menu

----àObject

----àName the Object

----àType----character

  Place the cursor in Select

----àClick on Browse

----àFrom Functions area------Expand character

----àDouble click on Ucase Function.

----àExpand Tables and Columns

----àDouble click on Month name

 The expression is applied as Ucase (Table name. Column name)

----àClick on Parse

If Parsing is Ok ----Click on Ok

STEP 7: Create / Insert Objects

7) CREATE / INSERT OBJECTS:

          Reporting columns are called as Objects.

To create the Object without selecting any Tables from structure panel, drag the Required column into Class Panel.

By default for every Source Column Dimension Object is the property added, to display source business data as it is in reporting.

Second Procedure to create the Objects:

----à Select the required class from Class Panel

----à Click on Insert Menu

----à Select Object

----à Name the Object [Article ID]

Note: Here Object name can be added with Spaces.

----à Select the Data type as Number

----à Place the Cursor in select Statement

----à Click on Browse in select Area.

----à From Tables and Columns Expand the required Table

----à Double click on the required column

----à Click on Parse to check the Syntax.

----à If parsing is Ok

----à Click on Ok

Data Cleansing:

          Converting Source Non-Unique Data Format into Unique data Format is called as Data Cleansing.

Data Scrubbing:

          Adding Extra column with required Business expression is called as Data Scrubbing.

Data Merging:

          Combining two Tables data is called as Data Merging.

Data Aggregation:

          Converting Source Detailed data into summary Format is called as Data Aggregation.

Note: Manual Navigation is recommended to support Data Cleansing and Data Scrubbing options.

STEP 6: Creating Classes

6) CREATING CLASSES:

          One class represents Schema.

A class is assigned with one or more Tables information.

A Table converted in to class can be accessed by Reporting.

To create the class use the following Navigations:

----à Click on Insert Menu------Class

----à Name the Class

----à In the same way create one more Class.

*** Class: Enterprise Information

     SubClass: Business Activity Information.

     SD (class)

        Sales (subclass)

        Billing (subclass)

        Shipping (subclass)

        Agreements/Contracts (subclass)

   FICO

        Accounts Receivables (subclass)

        Accounts Payables (subclass)

        Cost Centre (subclass)

        Bank Accounts (subclass)

        G/L Accounts (subclass) [General Ledger]

STEP---5: To resolve Loops:

STEP---5:

To resolve Loops: To resolve the Lops use the following methods

1)    Delete Unnecessary Joins

         ----à Any join which has Many-to-Many cardinality can be deleted from Structure Panel.

Note: While detecting cardinalities Business Objects reads only few records from the Database Table and returns the relation. To confirm the extract relations Manual Checking is Possible in Universe Structure Panel using

----àRight Click on required table

----àClick on Table Values.

Check the data in common Column which is used in joins. Repeated data display indicates that the cardinality is Many.

2)    Creating Alias Tables

      ----àSelect any Master Table or Dimension Table [Dimension tables are also called as Lookup Tables].

      ----àClick on Alias –Name the Alias Table

      ----àClick on Ok

      ----àDelete the Join which is causing the Loop and create New Join condition between Alias table and Corresponding Fact Tables.

3)    Shortcut Joins:

          Double click on Any Join which is having a Loop

          ----àDouble click

          ----àSelect the Checkbox-----Shortcut Join

          ----àClick on Ok

4)    Context:

To create a Context, select the required Joins (which has no Loops)

      ----àClick on Insert Menu

      ----àContext

      ----àName the Context

      ----àClick on Check to find Loops

      ----àIf no Loops are displayed---click on Ok

      ----àClick on Ok.

STEP---4: Detecting Loops:

STEP---4:

Detecting Loops: Using this option we find number of Loops possible from Universe Structure Panel Tables.

Navigations:

----àClick on Tools Menu

----àAutomated Detections

----àDetect Loops

----àClick on it

It displays the number of Loops and selected Joins from Structured Panel.

STEP—3: Insert Joins

STEP—3:

Insert Joins

To display the Joins between Tables in Universe Structure Panel use following Navigations

1)    Manual Join

----à Without selecting a Table drag a table column [Article_ID of Article_lookup] in to corresponding column of second table [Article_ID of Article_color_lookup]

----à Double click on Applied Join [link]

----àClick on Detect to find the Cardinality

----àClick on OK

2)    Insert Menu Join

----àClick on Insert Menu ----àJoin

          It opens Edit Join Interface

----à  Select the First table as Article_lookup

----à  Select the Second table as Article_lookup_criteria

----à  Select Article_ID between the two tables

----àClick on Detect

----àClick on OK

3)    Auto Detecting Joins

----à Click on Tools menu----àAutomated Detections----àDetect Joins

          It displays the possible Joins from Universe Structure Panel.

----àSelect the Required Joins----àClick on Insert.

For Detecting Joins we need to depend on Detect Cardinalities to Apply Data Relationships.

Navigations:

    ----àClick on Tools menu

    ----àAutomated detection

    ----àDetect Cardinalities

FAQ:

1)    How many types of joins can be applied from Universe?

Ans: There are 5 types of joins

1)    Inner Join [Equi Join]

2)    Left Outer Join

3)    Right Outer Join

                      [Full Outer Join is not supported by Universe]

4)    Theta Join

5)    Shortcut Join

Inner Join: If the Join condition is applied with only = [equal to] operator is called       as Inner Join or Equi Join

Using this property only common data of two tables is returned.

Right Outer Join: In the Edit join screen if Left side Outer join is selected it is treated as Right Outer Join

Internally it returns common data between the two tables and all extra records in Right Table.

Left Outer Join: In Edit Join screen, if the Right side Outer Join is selected it is treated as Left Outer Join

Using this property, it returns common data between the two tables and all extra records in Left Table.

Theta Join: While applying the join condition if we select other than to Equal operator then it is called as Theta Join or Non-Equi Join

Shortcut Join: This property is recommended to resolve the Loop by applying Second Priority for the Joins.