businessobjectsguru

BusinessObjects Enterprise Architecture

noreply@blogger.com (Mareswar Porandla) — Wed, 25 Mar 2009 04:15:00 +0000

BusinessObjects Enterprise web services

The web services are server-side components that process request from client applications and communicate these requests to the appropriate server. They include support for report viewing, and logic to understand and direct web requests to the appropriate BusinessObjects Enterprise server.

BusinessObjects Enterprise web services include:

Web Application Server

The Web Application Server uses the BusinessObjects Enterprise SDK (Java or .NET) to interface with the rest of the BusinessObjects Enterprise services. It is responsible for processing requests from the browser, sending Crystal Server Pages (.CSP) and Crystal Web Request (.CWR) requests to the Web Component Adapter, and formatting pages to be returned to the web client. The Web Application Server acts as a gateway between the browser and the rest of the components in BusinessObjects Enterprise.

Web Component Adapter (WCA)

The WCA runs within the application server and provides backward compatibility for applications developed using Crystal Server Pages (.CSP) and Crystal Web Request (.CWR) requests. The WCA also handles OLAP Intelligence view requests.

The management services

The management services manage the BusinessObjects Enterprise system.These services maintain all security information, send requests to the appropriate services, manage auditing information, and store report instances.

The management services are:

Central Management Server (CMS)

The CMS is responsible for authenticating users and groups, and keeping track of the availability of the other BusinessObjects Enterprise services. It also maintains the BusinessObjects Enterprise system database, which includes information about users, groups, security levels, BusinessObjects Enterprise content, and services. The CMS also maintains a separate audit database of information about user actions and manages the BusinessObjects Repository. All servers communicate with the CMS when they start up.

Event Server

The Event Server manages file-based events. It monitors the directory you specified when setting up a file-based event. When the appropriate file appears in the monitored directory, the Event Server triggers your file based event.

Destination Job Server

A user can take an existing report instance and schedule the report under a specified format and destination. The Destination Job Server copies or creates a shortcut of the file and sends it to the selected destination.

BusinessObjects Enterprise storage services:
The storage services are responsible for storing objects and object instances.

The storage services are:

Input File Repository Server

The Input File Repository Server manages all of the report and program objects that have been published to the system. It can store the following files: .RPT, .CAR, .EXE, .BAT, .JS, .XLS, .DOC, .PPT, .RTF, .TXT, .PDF,.WID. Note that .RPT files are stored as report definition files only; they do not contain any data.

Output File Repository Server

The Output File Repository Server manages all of the report instances generated by the Report Job Server and the program instances generated by the Program Job Server. It also manages instances generated by the Web Intelligence Report Server and the LOV Job Server. It can store the following files: .RPT, .CSV, .XLS, .DOC, .RTF, .TXT, .PDF, .WID.. Note that .RPT and .WID files are stored as reports/documents with saved data.

Crystal Reports Cache Server

The Crystal Reports Cache Server handles viewing requests from the Web Application Server when the Crystal Reports viewer preference is set to ActiveX, Java or DHTML viewers.
The Cache Server maintains a folder of cached report EPF files, determines if a request can be fulfilled with a cached report page or pass the request to the Crystal Reports Page Server when no cached report page exists. The benefit of caching is that BusinessObjects Enterprise doesn’t have to generate .epf files each time a page is viewed. Therefore report pages that have been cached can be shared among users.

Desktop Intelligence Cache Server

The Desktop Intelligence Cache Server handles viewing requests for Desktop Intelligence documents and manages the viewable pages created by Desktop Intelligence Report Server.

BusinessObjects Enterprise processing services

The processing services access the data and generate reports. This is the only tier that interacts directly with the databases that contain report data.

Which services become involved in processing an object is determined by whether the object is being scheduled or viewed on demand. Viewer choice also plays a role in determining which servers are involved in object processing.

For scheduling requests

The schedule processing services are:

Program Job Server

The Program Job Server processes program objects, as requested by the CMS, and generates program instances.

Crystal Reports Job Server

The Crystal Reports Job Server processes report objects, as requested by the CMS, and generates report instances.

Destination Job Server

If you configure a job server to process report objects, or send objects to instances to specified destinations, it becomes a Destination Job Server. A Destination Job Server processes requests that it receives from the CMS and sends the requested objects or instances to the specified destination.

If the request is for an object, the Destination Job Server retrieves the object from the Input File Repository Server. If a report or program instance is requested, the Destination Job Server retrieves the instance from the Output File Repository Server.

Web Intelligence Job Server

The Web Intelligence Job Server receives schedule requests from the CMS and then forwards them to the Web Intelligence Report Server for processing.

Web Intelligence Report Server

The Web Intelligence Report Server processes Web Intelligence report requests.

Both the Program Job Server and the Crystal Reports Job Server retrieve the file to be run from the Input File Repository Server. Run the report or program, and then save the processed file to the Output File Repository Server as an instance.

List of Values (LOV) Job Server

The List of Values Job Server receives schedule requests from the Business View manager and processes scheduled List of Values objects to populate them with values that are retrieved from a database.

Desktop Intelligence Job Server

The Desktop Intelligence Job Server processes scheduling requests it receives from the CMS for Desktop Intelligence documents and generates the instance of the Desktop Intelligence document.

Desktop Intelligence Report Server

The Desktop Intelligence Report Server processes viewing requests for Desktop Intelligence documents and generates the viewable pages of the Desktop Intelligence document.

Connection Server

The Connection Server in XI R2 is a service that can be administered and configured through CCM (Central Configuration Manager) and the CMC (Central Management Console). The Connection Server is responsible for the database connectivity to access data. It is invoked when users want to edit and view Desktop Intelligence documents through InfoView in 3-tier mode. It is also used by some EPM services.

The Connection Server libraries are present on the Web Intelligence Report Server, Desktop Intelligence Report Server and Web Intelligence Job Server which allows these services to query the database directly without communicating with the Connection Server service.

For viewing requests

The viewing processing services are:

Crystal Reports Page Server

The Crystal Reports Page Server is responsible for responding to page requests by processing reports and generating Encapsulated Page Format (.EPF) pages.The Crystal Reports Page Server retrieves data for the report from the latest instance or directly from that database. After it has generated the report and converted it to .EPF, the Crystal Reports Page Server then sends the .EPF file to the Crystal Reports Cache Server.

Web Intelligence Report Server

The Web Intelligence Report Server is responsible for generating viewable pages. The Web Intelligence Report Server obtains page creation requests from the Web Application Server and then communicates with the Input File Repository Server in order to obtain copy of the .WID file and the universe definition.

Report Application Server

The Report Application Server (RAS) processes reports that users view with the Advanced DHTML viewer. The RAS also provides the ad hoc reporting capabilities that allow users to create and modify reports over the Web.

The RAS is very similar to the Crystal Reports Page Server: it too is primarily responsible for responding to page requests by processing reports and generating EPF pages. However, the RAS uses an internal caching mechanism that involves no interaction with the Crystal Reports Cache Server.

As with the Crystal Reports Page Server, the RAS supports COM, ASP.NET, and Java viewer SDKs. The Report Application Server also includes an SDK for report creation and modification, providing you with tools for building custom report interaction interfaces.

Yahoo ! cleared BOCE level1

noreply@blogger.com (Mareswar Porandla) — Fri, 20 Feb 2009 13:47:00 +0000

Differences between BO XI R2 and BO XI 3.0

noreply@blogger.com (Mareswar Porandla) — Thu, 11 Dec 2008 09:34:00 +0000

1)WebI has a desktop version now.
Now you will be able to save your webI reports on desktop as a client tool has been introduced for the creation of WebI Reports. No more issues of global license. You just need a WebI license to create a WebI Report

2)The major improvement of XI 3.0 over XI R2 is architecture/administration-related:
- The user-interface of the Central Management Console (CMC) has improved: it mimics the Windows Explorer interface with its directory trees and pull-down menus;
- The new 'Security Query' in CMC enables administrators to search for specific objects;
- Auditing services comes now standard with CMC - No additional licence-costs are required anymore;
- Report-scheduling comes now standard with CMC. Also schedule options for WebIntelligence, Business Objects & Crystal Reports have improved so it matches BO6's BroadCastAgent Publisher;
- The new 'Federation'-option enables you to replicate servers on different locations;
- The 'Server Intelligence Agent' (SIA), a program that starts monitors and stops servers, is added. It makes the Enterprise-system not dependent anymore on a single CMS;
- It is now possible to create and add your own default security-level next to the existing 'View on Demand', 'Full Control', et cetera;
- The Import Wizard, used for importing objects from different environments and earlier BO versions, is extended and now also allows to import documents on their name only instead of the internal CUID-key.

There is still room for lots of improvements: version-control of objects is still non-existent, event-based scheduling is not well-supported, user-administration cannot be done batchwise and the general user-interface of CMC, despite having been improved, is still a major drawback compared to BO 6 in terms of userfriendliness and understandability.

3)There are optional prompts , i think thsi feature added will remove 'All ' in list of values other new feature is we can see what all data is changed from previous run of report

4)Here are few more differences from design point of view.
The new feature in Webi is the smart measure:
It is a measure that delegates its aggregation calculation to a database. Smart measure performs calculation that the standard Webi aggregation mechanism cannot calculate correctly.

E.g: Complex averages,such as average of a percentage,ratios,etc.. Smart measures are available for all relational and OLAP data sources.

It provides some new Querying functions:
-Delegated LOV search-It delegates the search of values in an LOV to the database.
Features:
1. Prevents LOV from being loaded automatically.
2. Prevents the report user from refreshing the LOV
3. Restricts the data set returned.
4. Limits the load time to one that the report user finds acceptable

-Optional Prompt-applies only to report level prompts not universe prompts.

-Percent rank
It allows the report user to rank a percentage of dimension volume "sliced" on another dimension. This feature is available only on Java report panel.

-Sampling
The query sampling returns specified number of items and selects them randomly.

-Forcemerge

-Data Tracking:
This feature allows a report user to highlight the values that have changed since previous refresh for any level of aggregation.

5)The Designer part of XI3 is enhanced so that we can create universes based on Stored procedures in database which can be easily accessed by business users for generation of webi reports through infoview. There is a huge difference between XIR2 and XI3 infoview in terms of infoview customization.

6)Multilingual support for metadata is now available with the introduction of "Translation Manager".This helps avoiding the rework in replicating the universe for different languages. But this is restricted to only WebIntelligence reports,where by the language setting can be choosen from Infoview settings for "preferred viewing Locale"

7)Few more new features of BO XI3
Support for Nested Derived Tables
Compulsory filters at universe and class level
Custom Drivers
Enhanced SAP BW support

8)There is a security configuration improvement that I consider very helpful. the option to apply security to one object but avoiding to spread it to the lower levels. So you do not have to broke inheritance from each object on the next (lower) level to have this working.

Business Objects XIR2 Installation Steps from santosh forum

noreply@blogger.com (Mareswar Porandla) — Wed, 17 Sep 2008 09:02:00 +0000

https://share.acrobat.com/adc/adc.do?docid=53650cdc-2496-4def-bd7f-d0673a9dbb06

ODS Vs. Data Warehouse

noreply@blogger.com (Mareswar Porandla) — Tue, 16 Sep 2008 09:52:00 +0000

ODS Vs. Data Warehouse

Operational Data store

- Data Focused Integration From Transaction Processing Focused Systems

- Age Of The Data is Current, Near Term (Today, Last Week’s)

- Primary Use: Day-To-Day Decisions Tactical Reporting Current Operational
Results
- Frequency Of Load: Twice Daily , Daily, Weekly

Datawarehouse

- Subject Oriented,Integrated,Non-Volatile,Time Variant

- Age of the Data is Historic (Last Month, Qtrly, Five Years)

- Primary Use: Long-Term Decisions Strategic Reporting Trend Detection

- Frequency of load : Weekly, Monthly, Quarterly

Desktop Intelligence Vs WebIntelligence in XI R2

noreply@blogger.com (Mareswar Porandla) — Mon, 28 Jul 2008 08:34:00 +0000

Desktop Intelligence Vs WebIntelligence in XI R2

General Look & Feel:

Entering Deski/Webi:

For Deski:
Wizard: Universe vs. Other Data Source
4 wizard options (cell, table, crosstab,chart)
Many Microsoft formatting toolbars
For Webi:
Universes (Or OLAP) Only
No personal data files (Excel, XML, etc)
No real wizard
Limited Microsoft formatting toolbars
Interactive Mode: Can Enter By accident

Query Panel:

For Deski:

Data Tab
When editing query, does add new objects to the report
Radial button for display of classes and object or predefined conditions
Button For: Save & Close/View/Run/Cancel
View Button for look at data and other functions
Add Query From Report Manager Window
Right Click in white area in Data Section
Insert New Data Wizard pops up

Report Manager: Click radial button to sort by data provider
Edit only 1 query at a time
User Objects can be created
View SQL

For Webi:
Data Tab
When editing existing query, does NOT add in to the report
Edit Query/Edit Report Icon
Properties tab for queries
Predefined conditions integrated together with classes and objects
Run Query Button on top (Only 1 option)
Can selectively run only 1 instead of all queries (Refresh too)
No View Button
No statistics/view data options
Can hide the Query Filter Box
Add Query Button (To open up another query panel)
Creates a Query Tab in Query Window
Has mini speed menu for those Tabs

Report Manager: Click down arrow to sort by query
Can click on query tab to edit directly (jump around)
No regular templates option
No User Objects capability
View SQL now available
Scope of Analysis Option (Click On/Off)
Appears on bottom of query panel (Below Query Filters Box)
Creating Query Filters (Conditions) more convenient: List of Operators and some Operand settings displayed within Query Filter-Builder.
No ‘Show List of prompts’ choice in Query Filters.
(Properties?) Tab next to Data Tab has box for changing retrieval record limit or retrieval time.

Report Manager:
For Deski:
Slice & Dice Panel
Format Templates
No drag and drop templates
Microsoft Formatting Toolbars
No Report Filter Window
Drilling: Must Grab All dimensions down path, or use scope of analysis

For Webi:
No Slice & Dice Panel
“Templates” Option (Drag and Drop)
No Format Templates
No Query on Query/Subquery Calc
No Grouping (Clip Icon)
No hide Objects
No Count All
No Fold option
Dragging/Dropping within Report Window very easy.
Can drag objects directly from Results Object window to Query Filters
No personal lov’s
Limited Microsoft Formatting Toolbars
Right Click on Edge of Report: Turn To Option
4 Report Options + 1 Full Chart Options as well
Report Filter Window Option (Appears on top of display)
To Remove Calcs: Drag Off or Structure Mode or Right Click/Remove Row or Column
Custom Sorts: But less sorting options
Breaks: Less Property Options
Appear on left side via properties tab (Must drill down)
Ranking: But less property options
Properties Tab on Left:
Have to click on option to see pull down’s
Contexts now different
Prompting options far more powerful and easy to use
Formulas/Variables:
Includes most Deski functions now
IF is a Function (Not a command): Like Excel
Display Format: More Difficult
Tabs on Left: Data/Functions/Operators
Formula on Right/Bottom
Name/Definition on Right/Top
Operators list remains fixed
Subquery Done Via Toolbar Option (Not in conditions)
Linking Multiple Data Providers: Merge Dimensions
New Toolbar Option
Easy to Use Menu
Drilling: Will Drill via New Query to lower level
Snapshot more limited

Universe and Report Design Guidelines and Practices

noreply@blogger.com (Mareswar Porandla) — Sat, 26 Jul 2008 05:33:00 +0000

The document is a compilation of learnings that can be used as Guideline and Best Practices for Report & Universe Design.

Universe Design: Guidelines & Best Practices

Introduction

Gives the basic guidelines/practices that could be followed in any Universe Design

Connection

--> When using a repository, always define a SECURED Connection to the Database.
--> Use the Universe Property panel to define the Universe Use and Version (last update).
--> Define the Connection Name that helps for Easy Database Identification.

Class

--> Define Universe Classes / Subclasses as per the business logic & Naming Convetion.
--> AVOID Auto Class generation in the Designer.
--> Give description for the use of each Class/SubClass.
--> Avoid deep level of subclasses as it reduces the navigability and usability.

Objects

--> Object to be used in calculation HAS to be Measure Objects.
--> Object to be used in Analysis HAS to be Dimension Objects.
--> Give description for the use of each Object.
--> Include an Eg. In the description for Objects used in LOV.
--> Do not set LOV Option for each Dimension. Use it only for required Objects, esp. those to be used in Report Prompts.
--> Keep "Automatic Refresh before Use" option clicked for LOV Objects:
--> If LOV is editable by the user, provide a significant name to List Name under object properties.
--> All the measure objects should use aggregate functions.
--> Avoid having dupicate Object names (in different classes).

Predefined Conditions

--> Give description for the use of each pre-defined condition.
--> If Condition is resulting in a Prompt, make sure associated Dimension Object has LOV.

Tables

--> Alias Tables should be named with proper functional use.
--> Arrange the tables in the Structure as per Business/Functional logic. This helps other Universe users in understanding.

Joins & Context

--> AVOID keeping hanging (not joined) tables in the structure.
--> AVOID having joins that are not part of any context.
--> Give proper functional naming to the context for easy identification.
--> AVOID having 1:1 joins.

Import/Export

--> Make sure of the path for Import, which usually is always in the Business Objects' Universe folder.
--> LOCK the universe if Administrator/Designer does not want any user to Import/Export.
--> DO "Integrity Check" before Exporting the Universe.
--> Good to have correct folder structure , so that you can have a secured environment.

Migration

--> Better take a backup of the repository and then proceed with the migration in BO5.X and BO6.X Version

Report Design: Guidelines & Best Practices

Introduction

Gives the basic guidelines/practices that could be followed in any Report Design.

General

--> Give meaningful names for the report tabs --> For complex reports, keep an overview report tab explaining the report --> Use the Report properties to give more information about the report
Dataproviders
--> Each Dataprovider should be given a name that reflects the usage of the data its going to fetch.
--> Select Objects in such a fashion that the resulting SQL gives a hierarchial order of Tables. This helps to achieve SQL Optimisation.
--> Avoid bringing lot of data into the report which will unnecessarily slow down the report performance.

Report Variables

--> Follow the naming convention of "var_" as prefix to each report level variable. This helps to identify Report Variables different from Universe Objects.
--> Each variable that carries a calculation involving division should have IF <> 0 THEN . This avoids display of #DIV/0 errors in the report.
--> Avoid having deep nested calculations which will slow down the performance of the report.

Report Structure

--> Make use of Report Templates when having most of the report with similar structures. This makes the work to move faster and consistant across.

Report Formats

--> All the reports should have page layout set in a printable manner. (Landscape/Portrait, Fit in 1 page wide or/and 1 page tall are different options).
--> All the reports should have page numbers in the footer.
--> All the reports should have Last Refreshed Timestamp in the header or footer.
--> All the above can be standardized by using templates

Report CELL Formats

--> All Numeric should be given Number format as per the language Eg. For German #.##00 for English #,##00.
--> Number cells should have a Right Alignment while Text cells should have Left Alignment.
--> Cell showing Percentage should carry the % text (either Column Header or in each cell).
--> Indenting should ALWAYS be done using the Indenting Tool and NOT by using " ".

brief about Slowly Changing Dimensions

noreply@blogger.com (Mareswar Porandla) — Tue, 13 May 2008 10:24:00 +0000

Slowly Changing Dimensions:

The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below:
Christina is a customer with ABC Inc. She first lived in Chicago, Illinois. So, the original entry in the customer lookup table has the following record:

Customer Key Name State
1001 Christina Illinois

At a later date, she moved to Los Angeles, California on January, 2003. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

There are in general three ways to solve this type of problem, and they are categorized as follows:

Type 1: The new record replaces the original record. No trace of the old record exists.
Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.
Type 3: The original record is modified to reflect the change.

Type 1:
We next take a look at each of the scenarios and how the data model and the data looks like for each of them. Finally, we compare and contrast among the three alternatives.
In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.
In our example, recall we originally have the following table:

Customer Key Name State
1001 Christina Illinois

After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

Customer Key Name State
1001 Christina California

Advantages:
- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages:
- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

Usage:
About 50% of the time.

When to use Type 1:
Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.

Type 2:
In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The newe record gets its own primary key.
In our example, recall we originally have the following table:

Customer Key Name State
1001 Christina Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key Name State
1001 Christina Illinois
1005 Christina California

Advantages:
- This allows us to accurately keep all historical information.

Disadvantages:
- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
- This necessarily complicates the ETL process.

Usage:
About 50% of the time.

When to use Type 2:
Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Type 3:
In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.
In our example, recall we originally have the following table:

Customer Key Name State
1001 Christina Illinois

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:
• Customer Key
• Name
• Original State
• Current State
• Effective Date

After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key Name Original State Current State Effective Date
1001 Christina Illinois California 15-JAN-2003

Advantages:
- This does not increase the size of the table, since new information is updated.
- This allows us to keep some part of history.

Disadvantages:
- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

Usage:
Type 3 is rarely used in actual practice.

When to use Type 3:
Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

To create a DBLINK using Derived Tables

noreply@blogger.com (Mareswar Porandla) — Mon, 05 May 2008 14:33:00 +0000

1. Create a DBLINK in Oracle on Server1 with the following statement:
2. CREATE DATABASE LINK dblink_name CONNECT TO user_name_on_server2 IDENTIFIED BY password USING 'connect_string_to_server2';
3. Create a synonym for the DBLINK on Server1 using the following statement:
4. CREATE SYNONYM synonym_name FOR user_name_on_server2.table_name_on_server2@dblink_name_server2
5. Ensure the synonym for the linked database on Server1 is added to the tnsnames.ora file of the target database on Server2. If not, Oracle will return the ORA-12154 error message.
6. Log in to Server1.
7. Query the DBLINK synonym using the following SQLPlus statement:
8. SELECT * FROM synonym_name
9. Log into Designer.
10. Click Insert Table > Derived Tables.
11. Query the DBLINK synonym using the following SQLPlus statement:
12. SELECT * FROM synonym_name
13. If an error is returned, close the Derived Tables dialog box and reopen. If the message "Parse OK" is returned, then click OK.

Business Objects Certification information

noreply@blogger.com (Mareswar Porandla) — Sat, 22 Mar 2008 10:24:00 +0000

http://www.businessobjects.com/services/training/certification.asp

Demo tour on Dashboard and performance Manager

noreply@blogger.com (Mareswar Porandla) — Wed, 05 Mar 2008 06:19:00 +0000

Hi Hope this will usefull to know basic things about dashboard
http://www.businessobjects.com/global/flash/products/xi_tour/index_flash.asp

How does Cognos and Business Objects work with ORACLE?

noreply@blogger.com (Mareswar Porandla) — Sat, 23 Feb 2008 07:06:00 +0000

Business Objects universes are stored in the RDBMS, but in both cases, the cubes are stored externally. Cognos has its own server or desktop based PowerCubes, while Business Objects microcubes are part of a document, which can again be server based or local. These look the same regardless of which database they were sourced from, so the same tools can be used with any database (one of their strengths compared to tools from Oracle, Microsoft or IBM which are database-specific).

As for Oracle OLAP products, the long-established OFA and OSA are still based on Express, which also uses cubes based outside the Oracle database.Oracle9i OLAP Option AW does have cubes and dimensions hosted inside the database, but most Oracle products don't yet build or support such cubes and nor do most third party products (except as vanilla SQL queries).

Good Site for Business objects Topics

noreply@blogger.com (Mareswar Porandla) — Wed, 20 Feb 2008 10:14:00 +0000

http://www.ds.uillinois.edu/web/Home/Help/SelfTraining/HowTos.aspx#BO

How do you test your database?

noreply@blogger.com (Mareswar Porandla) — Thu, 14 Feb 2008 05:58:00 +0000

This is a very generic question. I will be describing my generic database testing method as well as stored procedure testing methods.

Testing Databases:

· Table Column data type and data value validation.
· Index implementation and performance improvement.
· Constraints and Rules should be validated for data integrity.
· Application field length and type should match the corresponding database field.
· Database objects like stored procedures, triggers, functions should be tested using different kind of input values and checking the expected output variables.

Testing Stored Procedures:

· Understand the requirements in terms of Business Logic.
· Check that code follows all the coding standards.
· Comparing the fields' requirements of application to the fields retrieved by a stored procedure. They should match.
· Repeatedly run stored procedures many times with different input parameters and compare the output with expected results.
· Pass invalid input parameters and see if a stored procedure has good error handling.

Oracle Data Types

noreply@blogger.com (Mareswar Porandla) — Sun, 10 Feb 2008 09:49:00 +0000

http://www.ss64.com/orasyntax/datatypes.html

Dashboard Manager - Build a dashboard in Five Minutes

noreply@blogger.com (Mareswar Porandla) — Sun, 10 Feb 2008 09:25:00 +0000

http://www.businessobjects.com/jump/dashboard_manager/global/flash/five_minutes.asp

Find and remove duplicate rows from a table

noreply@blogger.com (Mareswar Porandla) — Fri, 08 Feb 2008 06:17:00 +0000

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, many shops do not use RI because they need the flexibility.

The most effective way to detect duplicate rows is to join the table against itself as shown below.

SELECT

BOOK_UNIQUE_ID,

PAGE_SEQ_NBR,

IMAGE_KEY

FROM

page_image A

WHERE

rowid >

(SELECT min(rowid) FROM page_image B

WHERE

B.key1 = A.key1

and

B.key2 = A.key2

and

B.key3 = A.key3

);

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below:

DELETE FROM

table_name A

WHERE

A.rowid >

ANY (SELECT B.rowid

FROM

table_name B

WHERE

A.col1 = B.col1

AND

A.col2 = B.col2

);

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from

customer

where rowid in

(select rowid from

(select

rowid,

row_number()

over

(partition by custnbr order by custnbr) dup

from customer)

where dup > 1);

Simple syntax to delete duplicate rows from a table

DELETE FROM our_table

WHERE rowid not in

(SELECT MIN(rowid)

FROM our_table

GROUP BY column1, column2, column3... );

Overview of Web intelligence

noreply@blogger.com (Mareswar Porandla) — Wed, 06 Feb 2008 04:54:00 +0000

Web Intelligence is designed to help organizations increase trust in decision making by providing
an intuitive, yet powerful, ad hoc query interface for business users and analysts, as well as
interactive viewing and analysis for casual users. With BusinessObjects XI Release 2, Web
Intelligence will introduce powerful new capabilities, making it easier for end users to access,analyze, and share information.

These features include:

1). The ability to access data from multiple universes and synchronize it within a single report (also known as synchronized multiple data providers)

2). The ability to create custom queries including sub queries, combined queries, and edit SQL

3). Support for the vast majority of the full-client formula language functions

4). Report creation features such as data ranking and custom sorts

BusinessObjects full-client capabilities that will be available with
Desktop Intelligence, but not available with Web Intelligence XI Release 2
include:

1). XML or visual basic data providers.

2). Specific power-user query capabilities designed for production reporting requirements.

3). The creation of queries built on the results of other queries.

4). Stored procedures (often used to group a set of SQL statements used in a query). (Note that organizations are now using the derived tables inherent in modern database systems to meet the same needs more efficiently).

5). Some specialized users of the BusinessObjects full-client generate queries using free-hand SQL. (Note that while this is not supported in Web Intelligence XI Release 2, it will be possible to edit existing SQL.) Many organizations are transferring the burden of custom SQL to the derived table method mentioned above. This ensures these specialized results areavailable for all users, and not confined to a single report

Displaying Row numbers in a Business objects report

noreply@blogger.com (Mareswar Porandla) — Mon, 04 Feb 2008 07:30:00 +0000

Steps to add Row number to business objects report:

1). Create a variable with the following formula:

=runningCount(any variable)

2). Add the Variable to the table as the first column.

Steps to reset the row numbers:

1). Add a second argument to the runningCount function:

=runningCount(any variable;reset variable)

when the value of the reset variable changes , the row count will reset to 0

Data Warehousing Objects - Fact Table, Dimension Table, Hierarcy Table

noreply@blogger.com (Mareswar Porandla) — Mon, 04 Feb 2008 07:04:00 +0000

Data Warehousing Objects

Fact tables and dimension tables are the two types of objects commonly used in dimensional data warehouse schemas.

Fact tables are the large tables in your warehouse schema that store business measurements. Fact tables typically contain facts and foreign keys to the dimension tables. Fact tables represent data, usually numeric and additive, that can be analyzed and examined. Examples include sales, cost, and profit.

Dimension tables, also known as lookup or reference tables, contain the relatively static data in the warehouse. Dimension tables store the information you normally use to contain queries. Dimension tables are usually textual and descriptive and you can use them as the row headers of the result set. Examples are customers or products.

Fact Tables

A fact table typically has two types of columns: those that contain numeric facts (often called measurements), and those that are foreign keys to dimension tables. A fact table contains either detail-level facts or facts that have been aggregated. Fact tables that contain aggregated facts are often called summary tables. A fact table usually contains facts with the same level of aggregation. Though most facts are additive, they can also be semi-additive or non-additive. Additive facts can be aggregated by simple arithmetical addition. A common example of this is sales. Non-additive facts cannot be added at all. An example of this is averages. Semi-additive facts can be aggregated along some of the dimensions and not along others. An example of this is inventory levels, where you cannot tell what a level means simply by looking at it.

Creating a New Fact Table
You must define a fact table for each star schema. From a modeling standpoint, the primary key of the fact table is usually a composite key that is made up of all of its foreign keys.

Dimension Tables
A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

The lookup table provides the detailed information about the attributes. For example, the lookup table for the Quarter attribute would include a list of all of the quarters available in the data warehouse. Each row (each quarter) may have several fields, one for the unique ID that identifies the quarter, and one or more additional fields that specifies how that particular quarter is represented on a report (for example, first quarter of 2001 may be represented as "Q1 2001" or "2001 Q1").

A dimensional model includes fact tables and lookup tables. Fact tables connect to one or more lookup tables, but fact tables do not have direct relationships to one another. Dimensions and hierarchies are represented by lookup tables. Attributes are the non-key columns in the lookup tables.

Hierarchies
Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.

Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies--one for product categories and one for product suppliers.

Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.

When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.

Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.

Below figure illustrates a common example of a sales fact table and dimension tables customers, products, promotions, times, and channels.

Overview of Chasm and Fan traps (BusinessObjects Universe)

noreply@blogger.com (Mareswar Porandla) — Sat, 02 Feb 2008 09:41:00 +0000

Relational databases can return incorrect results due to limitations in the way that joins are performed in relational databases. Unlike loops, which return fewer rows, the Chasm and the Fan traps are two common circumstances which return too many rows. You can use Designer to resolve both types of problems in your universe schema.

Chasm trap

The Chasm trap occurs when two “many to one” joins converge on a single table. For example a customer can place many orders/and or place many loans.
Fan trap

The Fan trap occurs when a “one to many” join links a table which is in turn linked by another “one to many” join.

For example when you run a query that asks for the total orders by each order line, for a particular customer, an incorrect result is returned as you are performing an aggregate function on the table at the “one” end of the join, while still joining to the “many” end.

Below is the Brief overview:

Chasm Trap:

A chasm occurs when a series of joins crosses a many >- one -< many relationship.

For example:

A >- B -< C

( Employees >-- Showroom --< Salary)

- Each showroom has many employees
- Each showroom has many annual salary figures
The number of employee records should not impact the total salary on the report.

If a query is written that spanned all three of those tables the data from table A and C, there would be duplicated and the measure values would be exaggerated. For example,
- X rows on the left
- Y rows on the right
- X * Y rows in the combined set

Solution 1:

Define a context for each table at the “many” end of the joins.

Context = Meaning

- A context is a sub-set of joins in a universe
- That sub-set of joins has a particular meaning

In our example you could define a context from A to B and from A to C. A context contains each join in the path. This creates two SQL statements and two separate tables in Business Objects, avoiding the creation of a Cartesian product. Using contexts is the most effective way to solve Chasm traps.

Solution 2:

Select the option ‘Multiple SQL Statements for Each Measure’ from the Universe Parameters dialog box in the tool. Only applies to measures. You force the SQL generation engine in Reporter to generate SQL queries for each measure that appears in the Query panel.

Fan Trap:

A fan trap occurs when joins “fan out” over multiple one -< many relationships in a row.

For example:

A -< B -< C (Ex: Customer --< Orders --< Order_Lines)

A fan trap is not quite as severe as a chasm trap. In fact there are many fan traps that can occur in a universe design that can be ignored as long as you control which types of objects you use.

There are two ways to solve a Fan trap problem.

• Using an alias and the aggregate awareness function. This is the most effective way to solve the Fan trap problem.
• Altering the SQL parameters for the universe. This only works for measure objects.

Both of these methods are described below.

Solution 1:

Aliases can resolve chasm traps

- Known as table aliases when writing SQL statements
- Used by BusinessObjects to logically separate the trap into pieces.

You create an alias table and use the aggregate awareness function. You cannot use this option if you have incompatible objects. You can do this as follows:

1. Create an alias for the table that is producing the multiplied aggregation.
2. Create a one to one join between the original table and the alias table.
3. Modify the select statement for the columns that are summed so that the columns in the alias table are summed and not the original table.
4. Apply the @AggregateAware function to the select statement. for example:
@AggregateAware(SUM(ORDERS.TOTAL_VALUE) , SUM(ORDERS_2.TOTAL_VALUE))

Solution 2:

- Create a separate SQL statement per aggregation.
- Aggregations on the same table require only 1 SQL statement.

You select the option ‘Multiple SQL Statements for Each Measure’. You force the SQL generation engine in Reporter to generate separate SQL queries for each measure that appears in the Query panel. You find this option on the SQL page of the Universe Parameters dialog box in the tool.

Overview of BusinessObjects CMS (Central Management Server)

noreply@blogger.com (Mareswar Porandla) — Sat, 02 Feb 2008 09:36:00 +0000

The Central Management Server (CMS) is the key Component within Xi, handling security and the routing of requests to other services.

If the CMS is not running, then users will not be able to log into Business Objects.

The Central Management Server maintains a database of information that allows you to manage the BusinessObjects Enterprise Infrastructure.

The CMS has four main functions:

Maintains security (users)
Manages objects (folders, reports, and program objects)
Manages servers (services)
Manages auditing (system auditor).

Overview of Report Design

noreply@blogger.com (Mareswar Porandla) — Fri, 01 Feb 2008 08:11:00 +0000

Report Design:

Guidelines & Best Practices:

Introduction:

Gives the basic guidelines/practices that could be followed in any Report Design.

General

--> Give meaningful names for the report tabs
--> For complex reports, keep an overview report tab explaining the report
--> Use the Report properties to give more information about the report

Dataproviders

--> Each Dataprovider should be given a name that reflects the usage of the data its going to fetch.
--> Select Objects in such a fashion that the resulting SQL gives a hierarchial order of Tables. This helps to achieve SQL Optimisation.
--> Avoid bringing lot of data into the report which will unnecessarily slow down the report performance.

Report Variables

--> Follow the naming convention of "var_" as prefix to each report level variable. This helps to identify Report Variables different from Universe Objects.
--> Each variable that carries a calculation involving division should have IF <> 0 THEN . This avoids display of #DIV/0 errors in the report.
--> Avoid having deep nested calculations which will slow down the performance of the report.

Report Structure

--> Make use of Report Templates when having most of the report with similar structures. This makes the work to move faster and consistant across.

Report Formats

--> All the reports should have page layout set in a printable manner. (Landscape/Portrait, Fit in 1 page wide or/and 1 page tall are different options).
--> All the reports should have page numbers in the footer.
--> All the reports should have Last Refreshed Timestamp in the header or footer.
--> All the above can be standardized by using templates

Report CELL Formats

--> All Numeric should be given Number format as per the language Eg. For German #.##00 for English #,##00.
--> Number cells should have a Right Alignment while Text cells should have Left Alignment.
--> Cell showing Percentage should carry the % text (either Column Header or in each cell).
--> Indenting should ALWAYS be done using the Indenting Tool and NOT by using " ".

what is Designer and Creation Of Universe?

noreply@blogger.com (Mareswar Porandla) — Fri, 01 Feb 2008 08:07:00 +0000

What is Designer?

Designer is a BusinessObjects IS module used by universe designers to create and maintain universes. Universes are the semantic layer that isolates end users from the technical issues of the database structure. Universe designers can distribute universes to end users by moving them as files through the file system, or by exporting them to the repository.

BO Universe is essentially a connection layer sitting between the source data and the DW. It is defined by the data mapping or schema or the relationship between database tables. Each universe is accessed by certain category of users. For example, finance people will access finance universe, sales people will access sales universe. The analogy is similar to a data mart.

The advantge of the BO universe is that if there are any changes in the source data structure, this change needs to be made only in the Universe and its effect gets pushed down to all the reports emanating from this universe. A good universe design helps is improving speed and contributes to the Best Practices using BO.

How do you design a universe?

The design method consists of two major phases.
During the first phase, you create the underlying database structure of your universe. This structure includes the tables and columns of a database and the joins by which they are linked. You may need to resolve loops which occur in the joins using aliases or contexts. You can conclude this phase by testing the integrity of the overall structure.
During the second phase, you can proceed to enhance the components of your universe. You can also prepare certain objects for multidimensional analysis. As with the first phase, you should test the integrity of your universe structure. Finally, you can distribute your universes to users by exporting them to the repository or via your file system.

What you need to be a Business Intelligence Consultant ??

noreply@blogger.com (Mareswar Porandla) — Wed, 30 Jan 2008 07:18:00 +0000

I read this article recently here the author Reinald Bormann from Harvey jones system gives and insight about the BI Consultant

Most recruiters daily face the sad reality that there is a serious skills shortage in the local IT industry.

The business intelligence (BI) market is not exempt. With BI going from strength to strength since 2003, market-savvy IT professionals are trying to enter this lucrative market, with many cross-skilling on multiple BI platforms or products.

One of the changes afoot in the market is that companies no longer simply deliver a BI solution and then leave clients to their own devices.more often than not, client/consultant relationships extend well beyond delivering the initial project.

Successful BI consultants must be well-rounded people with solid project experience, preferably across various market sectors.

Successful BI consultants should excel in the following four areas:

* Technical ability
* Interpersonal skills
* Project experience and
* Market knowledge

Technical ability

BI projects are being completed quicker today than they were three years ago, with the result that top BI consultants can work on multiple projects each year.

Properly applying the latest technology should always be a BI consultant's highest priority and, with so many technical components to these solutions, specialising in at least one component is beneficial.

While core skills allow consultants to work on projects other than BI, they should always strive to improve their abilities on other components.

Interpersonal skills

All projects involve interaction between BI consultants, fellow team members and clients.
Interpersonal communication is a vital skill that each BI consultant must master.

The wealth of knowledge contained in BI consultants' heads is not easily transferred.
Effective use of communication skills allows them to share knowledge across the team.

Successful BI consultants also act as mentors and guide internal IT teams through the uncharted waters of their first BI projects. It is BI consultants' role to point clients toward good resources, books and conferences for further education.

Project experience

IT consulting is always evolving. Because project rotation is generally more frequent than in the past, BI consultants must quickly understand new businesses in order to deliver solutions on time and within budget. Getting to grips with unfamiliar businesses is always difficult, but is also very rewarding.

Successful BI consultants leverage their technical capabilities to re-use certain components developed on previous projects.

Essentially, project experience breeds problem identification and reinforces the use of best practice and methodology.

Market knowledge

Just as consultants become comfortable, a new product or toolset emerges, which nine times out of 10 changes the way they deliver. This is not necessarily a bad thing, as successful BI consultants usually make market changes work for them by aligning their technical skills in the new direction.

The release of Microsoft Performance Point Server (PPS) at the end of the year is a very good case in point; most BI organisations are uncertain of the true impact PPS will have in the market, so having skilled BI consultants at hand when the product is released could ensure a significant market share of new BI solutions on PPS.

It should be every BI consultant's goal to be as active as possible in the community. That means attending seminars and Webcasts, taking part in BI forums or using other knowledge-sharing channels.

Conclusion

In today's challenging and demanding BI market, successful BI consultants must be well-rounded and experienced people, able to adapt quickly to various business environments, continuously share knowledge internally and externally, understand future BI market trends and, most importantly, be very sound technically.

If you are lucky enough to have such a person in your employ or can find one, hang on to them tightly with both hands as they are worth their weight in gold.

By Reinald Bormann, BI consultant at Harvey Jones Systems