universe design

56132 ParkviewShelby Twp, MI 48316586.677.8300 Phone586.677.8301 Faxhttp://www.cttsbi.com

Designing the Universe Evolution in Report Creationism

Last Revised: 3/5/2010

© 2010 – Creative Technology and Training Solutions, LLC

Designing the Universe

1 - 2

In the Beginning there was only SQL Code. Programmers built Custom interfaces and reports as User Requests were received.



1 - 3

Business Users Waited for their information…..



1 - 4

And Waited …..



1 - 5

And Waited …..



1 - 6

Until they received their reports only to find what they requested was not really what they wanted….



1 - 7

The Reporting world was a very Dark place….



1 - 8

But onto this world a software was born and it brought light into this dark place. It would allow the creation of a user friendly interface that all users could use and access their data. It was called….



1 - 9

A UNIVERSE !!!!!



1 - 10

Good Programmers Paid homage to the new software and began developing universes…. They ascended into the good place and became DESIGNERS…..



1 - 11

Bad Programmers feared the tool for it demonstrated their lack of ability, they descended into the dark fiery place and became…..



1 - 12

MANAGERS !!!!!



1 - 13

Webster’s Dictionary Definition

the whole body of things and phenomena observed or postulated a set that contains all elements relevant to a

particular discussion or problem



1 - 14

Here is a Test :

Why do some designers create universes using the first definition and not the last??



1 - 15

Possible Answers

A. Because they are cluelessB. Because they were forced toC. Because Universe means

everything Right???D. All of the AboveE. Due to the lack of knowledge of

the specifics, it would be unwise to answer at this time… The PC answer



1 - 16

A more Refined Definition

Well-built Universes Should…Provide information for the Targeted

AudienceBe User FriendlyBe Easy on the eyes Make information easy to find

Build Efficient SQLReturns Results with minimal delay



1 - 17

Who is your Targeted Audience

IT Professionals building canned reports Powers Users interacting with Canned

reports, making “1-off” reports Business Users accessing information in

True Ad-hoc fashionDoes it matter ??? yes, Yes, YES!!!



1 - 18

Why does it Matter

Class and Object Naming ConventionsDon’t be lazy, Add the Descriptions

Organization of Classes and Objects Overall Size and Scope of the UniverseHow Many ClassesHow Many Objects

In the perfect world, 1 universe could fit the needs of all, but we don’t live in a perfect world



1 - 19

How is the Universe to Used?

Interactive Canned reports Scheduled Canned reports OLAP or Data Mining Reports (Drilling)Ad-Hoc or Sandbox reports



1 - 20

Interactive Canned Reports

Look and Feel targeted toward IT staff or Power Users creating these reportsCreation of effective LOV’sBetter enable users to Select valid parameter

valuesCreation of Dynamic Predefined

ConditionsEases creation of parameters for Canned

Reports



1 - 21

Effective LOV’s

Add Something More than just the Single column If a Code, Add the DescriptionUse Hierarchies or Cascading LOV’s



1 - 22

Dynamic Predefined Conditions

Conditions with Prompts rather than hard coded Values



1 - 23

Scheduled Canned Reports

Look and Feel targeted toward IT staff or Power Users creating these reportsCreation of System Date Based ObjectsBetter enable users to Schedule based on

Time Periods (ie. Today, Yesterday, Last Year)

Creation of Static Predefined ConditionsExpedites data restriction creation



1 - 24

Date Based Objects

Specifically Created for date Based Conditions



1 - 25

Static Predefined Conditions

No Prompts…



1 - 26

OLAP Reports

Look and Feel targeted toward IT staff or Power Users creating these reportsCreation of effective HierarchiesDefault or Custom hierarchiesEffective Natural or Logical Drill paths



1 - 27

Effective Hierarchies

Default or CustomNatural or Logical Paths1 to Many Relationships



1 - 28

Ad-hoc Reports

Look and Feel targeted toward Business Users creating these reportsCreation of effective and efficient LOV’sCreation of Predefined ConditionsCreation of System Date Based ObjectsDocuments could be created for

Interactive or Scheduled



1 - 29

Being User FriendlyClasses and Objects Naming

conventions come from Users not ITClasses and Objects are not

overwhelming in number or depth Users not looking at 100’s of Classes, 1000’s

of objects (Easy on the eyes)Users Not having to Click from Subclass to

Subclass to find objects (easy to find)Classes and Objects in an Order that

allows for effective user interactionPut things where they want them



1 - 30

Building Efficient SQL

What Type of Data Structure are we using? Is there a best Scenario?

WINNER!



1 - 31


How do we know?Do we have queries that run long, but return

few rows?Do we have queries that time out?

Use DB explains or Third Party Tools (Toad)



1 - 32


Are Tables joined correctly? PK-FK?Are Objects Used in most Conditions

Indexed?Can we implement Shortcut Joins to

avoid joining to tables not being used in result ?Can we use Materialized Views or

Derived Tables?Can we implement Aggregate

Awareness?



1 - 33

Are tables joined Correctly

Use Keys Functionality In Designer

Keys underlined in

Tables

Primary Keys Solid, Foreign

Dashed



1 - 34

Are tables joined Correctly

Use DB Explains or Third Party Tools to View How Database optimizes Queries



1 - 35

Are Objects Indexed

Use DB Explains or Third Party Tools to View How Database optimizes Queries



1 - 36

Shortcut Joins

Removes Unnecessary joins



1 - 37

Views or Derived Tables

Revise data for Specific report NeedsMaterialized at DB Level or Derived in

the Universe



1 - 38

Aggregate Awareness

Process That Incorporates Higher Level Aggregation (Summary Tables) into UniverseSpeeds up Queries That Contain

Statistics Reported at Higher Levels (i.e. Year)



1 - 39

Summary Tables

Store Data at Higher Level Than Detail (Fact) Tables Increase Processing EfficiencyFewer Rows RetrievedFew If Any Calculations RequiredFew or No Joins



1 - 40

Apply Aggregate Awareness

Insert Summary Tables into Universe

Start with 1 Level above Fact Table

Only have to Add to Beginning of Select Statement

Add Joins, Set Cardinality (If Needed) Define or Redefine Contexts Redefine or Create Objects Using

@Aggregate_Aware Define Incompatible/Compatible Objects

Using Aggregate Navigation



1 - 41

Adding Summary Tables

Summary Stand Alone TablesSummary Tables Joined into Universe

StructureSummary

Table Joined to Structure

Summary Tables

Standalone



1 - 42

Defining Contexts

What Are Contexts?Lists of Joins Identifying Paths Through

UniverseAlternative SQL Routes Through Loop

Identifies Tables that can be used in a Single SQL Query



1 - 43

Defining Contexts

Select Joins to Define ContextEdit to Redefine Current Context



1 - 44

@Aggregate_Aware

Can Be Used with Aggregates (Measures) and DimensionsSelects Information from Most

Aggregated to Least Aggregated (Summary to Detailed)



1 - 45

@Aggregate_Aware

Edit the Object to Redefine

Summary Tables Added to

Beginning of Select



1 - 46

Aggregate Navigation

Define Incompatibles (ONCE)Select Objects that are compatible by

UNCHECKING themSummary Tables

Added to Beginning of

Select

Click Button Once

Select Summary Table



1 - 47

Aggregate Testing

Creating Query and Viewing SQL

Summary Table being used in

SQL

Select Objects to test with



1 - 48

Other Tips

Reuse SQL Code by using @Select and @Where functionalityCreate Custom Objects for reuse or

for Customized LOV’sCreate Special Classes with Custom

Code Objects and Then Hide the class from User View



1 - 49

SummaryDue your Homework Be flexible with your designsKnow your Targeted AudienceKeep a Strong relationship with DBAPrototype and get User Buyoff Implement Aggregate Awareness

where possible test, Test and TEST SOME MORELastly…..



1 - 50

Summary

Save (and export) your Universe…… Now Go forth and create your own universe….



Contact Information: Michael Ward(586) [email protected]

BusinessObjects™ 3.1 Training Classes:Desktop Intelligence™ 3.1 Basic Reporting (2 Days)Desktop Intelligence™ 3.1 Intermediate Reporting (1 Day)Desktop Intelligence™ 3.1 Advanced Reporting (1 Day)Desktop Intelligence™ 3.1 Advanced Crosstabs & Functions (1/2 Day) Desktop Intelligence™ 3.1 Foundations (1 Day) WebIntelligence™ 3.1 Intro To InfoView (1 Day) WebIntelligence™ 3.1 Basic Reporting (1 Day)WebIntelligence™ 3.1 Intermediate Reporting (1 Day)WebIntelligence™ 3.1 Advanced Reporting (1 Day)WebIntelligence™ 3.1 Rich Client (1 Day)BusinessObjects™ 3.1 Designer (2 Days)BusinessObjects™ 3.1 Scheduler (2 Hours) BuisnessObjects™ 3.1 Administration (2 Days)



DI 3.1Basic Reporting

2 Days

DI 3.1Interm Reporting

1 Day

Desktop Intelligence3.1 Reporting

BO 3.1Designer

2 Days

BO 3.1Administration

2 Days

WebIntelligence3.1 Reporting

Webi 3.1Intermediate

Reporting1 Day

Webi 3.1Basic Reporting

1 Day

DI 3.1Adv Reporting

1 Day

DI 3.1Adv Crosstabs

1/2 Day

Webi 3.1Advanced Reporting

1 Day

DI 3.1Foundations

1 Day

BO 3.1Rich Client

1 Day