Bo-designer Training 3

SPICA

Deal with the Objects

DATA SYSTEMS

• Once the universe is created & loops are handled, its time to work on types of objects – ¾User Defined Objects, ¾List of Values (LOVs) Objects, ¾Predefined condition-filter Objects.

Contd….

SPICA

User Defined Objects

DATA SYSTEMS

• The Database provides Sales Price & Qty Sold, but I want “Sales Revenue”. Now what? Not an issue! Create an object that calculates the same & name it Sales Revenue. This will be User Defined Object. • Click on Insert ÆObject on the Designer toolbar, edit properties box appears & using it you can define the functionality of the object.

SPICA

List of Values (LOVs)

DATA SYSTEMS

• Hard coding conditional values in reports make them static, but what if different users want to select different value(s) of specific objects? • Solution : enable the LOVs that can be used in Prompts at report level. This makes single report usable for multiple users looking for different values of same object.

SPICA

DATA SYSTEMS

List of Values

SPICA

Predefined Condition-filters

DATA SYSTEMS

• Frequently used conditions for reports can be created as condition-filter objects at universe level. • On toolbar click InsertÆcondition & define it in Where clause. • Advantages – 9Easy for end users to pick from the list, 9No need to define conditions every time they create reports 9One time creation so time saving activity.

SPICA

DATA SYSTEMS

Creating Condition

SPICA

Deal with Objects

DATA SYSTEMS

• Lets Consider earlier problem, What if my end users want to display Sales Revenue for specific Products or States in Christmas period? • Solution : Create an object which will calculate revenue & use the LOVs feature of Products or States, Create predefined Christmas time conditionfilter object & use it as a condition in conditions pane.

SPICA

Prompts

DATA SYSTEMS

• Syntax : • Table_name.column_name IN @prompt(‘Message',' Data type’, ‘Values or Classname\Objectname’, multi/mono, free/constrained) • E.g.. • Article_Lookup.family_name IN @prompt(‘Select Product Lines :’, ‘A’, ‘Product\Lines’, multi, free)

SPICA

Hierarchies

DATA SYSTEMS

• A hierarchy is an ordered series of related dimensions. • An example of a hierarchy is Geography, which may group dimensions such as Country Æ Region ÆCity. • It is useful for Multidimensional Analysis. • If you create a hierarchy, while Drilling BO uses it & helps you either restrict or expand the scope of analysis. • For E.g. if your time dimension has 15 different columns & you want to drill through year to week, you can define new hierarchy having only year, quarter, month & week objects that restrict the scope to the week limit. • To create/modify hierarchy click ToolsÆ Hierarchies.

SPICA

DATA SYSTEMS

Hierarchies Editor

SPICA

Exercise # 3

DATA SYSTEMS

In eFashion universe, • create condition filters for – – – – –

Year 2001, Year 2002, Year 2003 Christmas Holidays Condition prompting for Product Lines.

• create Objects for – – – – –

Prompting users to select product categories. Calculating Discount, Calculating Sales Revenue for prompted Product Lines, Add “---All Categories---” to your LOVs of Product Categories, on selecting of which you can select all categories.

SPICA

Exercise # 3

DATA SYSTEMS

• In your universe, create custom hierarchies for Product, Time, Store.

SPICA

Aggregate Awareness

DATA SYSTEMS

• Aggregate Aware function helps you to define Select statements for objects that use values from Aggregate (or Summery) Tables, instead of going to base tables. • Why Aggregate Tables? – Unlike Databases, BO first fetches all the records in memory & then display them in report. – If my query deals with 1M records which will need to go to 7 dimension tables, fetch the data, perform calculations & give Output? Be sure for compromise in performance ! – To avoid this, we use Aggregate Tables, that hold the summarized data & hence bypass the base tables & improves performance.

SPICA

Aggregate Awareness

DATA SYSTEMS

• Syntax – @Aggregate_aware(aggregated_table1.column , aggregated_table2.column, aggregated_table3.column, base_table.column)

• For E.g. – @Aggregate_Aware(Agg_yr_qt_rn_st_ln_ca_sr.Yr, Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Yr, Calendar_year_lookup.Yr)

• This way we guide BO to go to agg_table1 first, then agg_table2 & so on to select the specific column, based on other member objects of Report.

SPICA

Aggregate Awareness

DATA SYSTEMS

• Because similar objects are present in multiple Aggregated tables as well as in Base Table, the possibility of Cartesian products arises. • To deal with this, you need to tell BO, which objects are compatible with every aggregated table and which are not. • You define compatibility using Aggregate Navigation tool. To find this tool – – Click Tools Æ Aggregate Navigation on toolbar.

SPICA

Using Derived Tables

DATA SYSTEMS

• These are the tables that you define in the universe schema. You create objects on them as you do with any other table. • A derived table is defined by an SQL query at the universe level that can be used as a logical table in Designer. • To create Click Insert Æ Derived Table & then define it using SQL query. • Remember, You never create any table in your backend database as a derived table ! It is logically present in the universe only.

SPICA

DATA SYSTEMS

Creating Derived Table

SPICA

Advantages of Derived Tables

DATA SYSTEMS

• Reduced amount of data returned to the document for analysis. • You can include complex calculations and functions in a derived table. These operations are performed before the result set is returned to a document, • Saves time and reduces the need for complex analysis of large amounts of data at the report level.

SPICA

Exercise # 4

DATA SYSTEMS

1. Insert Aggregated tables in your universe. –

Re-define your objects as per aggregated tables.

–

Define Aggregate Navigation to make the irrelevant objects incompatible.

–

Check integrity.

SPICA

Deployment

DATA SYSTEMS

• Once the universe is created & tested, it is ready to use for end users. • Export the universe to the repository, so it has single location from where everyone can access it. You can – ¾Export to domain (same repository), ¾Migrate from one domain to another (same repository), (for example : production to testing.)

¾Exchange between different repositories.