Input Controls in Business Objects

Topic: Input Controls intro in Web Intelligence reports of Business Objects XIR 3.1 and how you can create Xcelsius like Dashboards with Input Controls!

Video walkthrough: Traditionally until 3.1, we had to ability to add Query Filters and in report Report Filters to see partial or granular data. For example, if users asked for the ability to pick and choose States, individual or multiple, you'd build a state Prompt for them and they would have to Refresh the (WebI) report EVERY TIME to pick new states of their choice and hit RUN. This was very ad-hoc like interactive approach because they wouldn't have to ask for a developer's assistance to dissect the report and see granular information. But, every refresh takes some toll on your DB server. Not all reports run in seconds, many takes minutes; multiply that by hundreds or thousands of users, they waste countless hours, DAILY!

Creating Xcelsius like Dashboards with Input Controls!
That's when Input Controls come in. You no longer need to add a prompt. Simply get rid of the prompt (assuming this is a report with reasonable data; not thousands of pages) and add input control for that prompt dimension. You refresh the report once, and once all the data is available, simply use the input controls to dynamically pick and choose and select as many or few states, years, products, or any other dimensions you'd like to view your data based off of. Let's look at it in action with our video below.

Globally change user preferences in BO 3.1

Topic: How to globally change users' preferences in Business Objects 3.1

When we log into Business Objects 3.1 Infoview, by default we are brought to the Navigate/Personalize window. Then from here we need to click on Document List, My Favorites and so on, to go where we need to go. So, if a user belongs to Accounting department for example and only has access to Accounting folder, he/she needs to click on Document List and then Accounting folder, every time he logs into Infoview. Now, if all 50 Accounting employees need to do this several times a day, every day, it becomes cumbersome and valuable time is lost.

This video will walk you through on how to globally set preferences for individual groups and redirect to unique locations; e.g. Accounting group to Accounting folder, Marketing group to their inboxes and so on.

Register for SAP "ALWAYS ON" Weekly Webinars

50 Percent off all SAP Certification Exams

		Stand out with SAP certification 50% off before June 30 2011
	There's nothing like SAP certification to give you prestige in the business world. With a 50% discount on all exams till June 30, now is the time take your career to new heights. SAP certification shows that you're a cut above the competition. People who are SAP certified stand out because they've honed their skills through rigorous study and real-world experience. They've proved their worth by passing demanding exams. Want this kind of recognition? Register now for 50% off all certification exams taken by June 30 2011 at Pearson VUE venues across the US and Canada. Simply select your certification exam and Pearson VUE venue then enter the promotional code: 11CERTTL				More information Visit the SAP Training Shop for more information on SAP certification and search by exam, solution or focus area.

How to Migrate Personal Folders in BO

Video (slideshow) walkthrough: How to migrate specific User Folders (BO XIR2) or Personal Folders (BO 3.1 +) using Import Wizard.

Topics Covered:
1) Creating custom group and assigning desired users to the custom group
2) Migrating using Import Wizard


A problem I faced when migrating from XIR2 to 3.1 is that when I wanted to migrate specific users' or specific department's users' favorite folders. Import Wizard would only allow me to select a group, and not individual users. So, I decided to create a custom group in CMC and assign specific users or specific departments (i.e. groups belonging to those departments) to this custom group. Now, the Import Wizard would let me select this custom group to migrate, and I'd have total control over it. I can decide whether I put 1 user in this group from CMC or 100. Short video below quickly goes over the steps. Enjoy!

Quick steps to recover BO content from Backup

Steps to recover Business Objects objects, including all types of Reports and Universes, from Backup. (look for a better, more detailed version in BO Admin guide)


You always have the following 2 components in a BOXI environment: CMS Database and Filestore folder. We'll call the current/present versions CMS_DB and Filestore. 


1) Stop all BOXI services from CCM.


2) Get a copy of CMS_DB from yesterday or whenever you had a proper working BO environment, from where you could retrieve your lost objects; rename it to CMS_Backup. System Admins or DBA will get you this. Place CMS_Backup in any DB server. 


3) Rename your current Filestore folder to Filestore_Current. 

4) Get a copy of the Filestore folder from the same date as you got your CMS_DB backup in #2; put this in the same directory where you have Filestore_Current. 

5) Create a Data Source X (ODBC or whatever u guys use) and point to CMS_Backup.

8) Point CMS (service) from CCM > CMS > Configuration > using X to CMS_Backup. 

9) Start CMS, Input File Repository, Output File Repository. BO will act as if it's in the past; we'll call this BO Historic. 

10) Start Import Wizard and Import your lost objects. Source BO > Destination BIAR > browse and pick your Reports, Universe, etc from this BO Historic > BIAR them. 

11) STOP all BO services

12) Rename Filestore (which is really from Filestore folder from backup) to Filestore_Backup. 

13) Point CMS to your CMS_DB using the existing Data Source. 

14) Start ALL BO services. 

15) Import your lost objects from the BIAR file to present environment. Import Wizard > Source BIAR > Destination BO > Automatically Rename blah blah blah > done.

BO 3.1 Deployment Part 2 - Installation

Video Walkthrough: Steps to perform the initial installation, including pointing the BOE 3.1 installation to existing CMS and Audit databases, deploying TomCat and IIS web servers, introduction to Server Intelligence Agent and a quick glance at the 3.1 CMC.

BO 3.1 Deployment Part 1 - ODBC

This is the beginning of the series of videos that will cover a complete deployment of Business Objects R 3.1 in VM Windows 2003 environment with SQL Server 2005. 

Video Walkthrough: Assuming your DBA (or you) will create the 2 databases for a BO 3.1 deployment, we will start off with the creation of ODBCs pointing to these DBs, which are of course a CMS database and an Audit database. 

Universe LOVs are bad for your health

Well, it's bad for the health of the environment. And if your BO environment is not healthy, you'll be bugged constantly by users--'...oh this report is so slow...ohh, the dates don't load up properly...ohh, Business Objects is acting weird...' and so on. Ring a bell? :) And thus, you too will be sick; sick and tired of complaints and not knowing what the heck is up in your environment.


Topic Intro: Properly designing Universes so that unnecessary LOVs do not get associated with objects.

Video Walkthrough: We're gonna go over the basic steps required to identify objects that should not have LOVs associated with them and how to proactively avoid the problem from universe by disassociating the LOVs options in objects' properties.

Dissecting Auditor using VIEW

Topic Intro: Querying Business Objects Audit Database directly by creating a View.

Video Walkthrough: As I've mentioned in the previous video, we're gonna create a VIEW in the BO Audit Database using the following code that's available on Bob Forum and other places on the web. We will also run some test SQL queries to find some Audit information directly from the Audit database.

-------------------------BEGIN--------------------------

create view AUDITOR_View as
SELECT        server_process.application_type_id as [Application_Type_ID],
              cast(application_type.application_type_description as varchar(50)) as [Application_Type_Description],
              audit_detail.detail_id as [Detail_ID],
              audit_detail.detail_text as [Detail_Text],
              detail_type.detail_type_id as [Detail_Type_ID],
              cast(detail_type.detail_type_description as varchar(50)) as [Detail_Type_Description],
              audit_event.Duration as [Duration],
              audit_Event.Error_Code as [Error_Code],
              cast(audit_event.Event_ID as varchar(32)) as [Event_ID],
              audit_event.Event_Type_ID as [Event_Type_ID],
              cast(event_type.event_type_description as varchar(50)) as [Event_Type_Description],
              cast(audit_event.Object_CUID as varchar(32)) as [Object_CUID],
              audit_event.Start_Timestamp as [Start_Timestamp],
              cast(audit_event.User_Name as varchar(50)) as [User_Name],
              cast(audit_detail.server_cuid as varchar(32)) as [Server_CUID],
              cast(server_process.server_name as varchar(50)) as [Server_Name],
              cast(server_process.server_fullname as varchar(50)) as [Server_Fullname],
              cast(server_process.server_version as varchar(50)) as [Server_Version]
FROM APPLICATION_TYPE INNER JOIN
             (SERVER_PROCESS INNER JOIN (
                 (AUDIT_DETAIL INNER JOIN (
                      AUDIT_EVENT INNER JOIN
                          EVENT_TYPE ON
                              AUDIT_EVENT.Event_Type_ID =
                              EVENT_TYPE.Event_Type_ID) ON (
                                  AUDIT_EVENT.Server_CUID =
                                  AUDIT_DETAIL.Server_CUID) AND
                                     (AUDIT_DETAIL.Event_ID =
                                      AUDIT_EVENT.Event_ID)) INNER JOIN
                                          DETAIL_TYPE ON
                                              AUDIT_DETAIL.Detail_Type_ID =
                                              DETAIL_TYPE.Detail_Type_ID) ON
                                                  SERVER_PROCESS.Server_CUID =
                                                  AUDIT_DETAIL.Server_CUID) ON
                                                      APPLICATION_TYPE.Application_Type_ID =
                                                      SERVER_PROCESS.Application_Type_ID

-------------------------END--------------------------

Business Objects Auditor: SETUP

General Intro: Proper Auditing is an important part of any business, which allows decision makers to see the behind-the-scene views of their business. Business Objects is no different. It is essential for all BO Administrators to have in depth knowledge about the Auditing concepts in Business Objects XI platform. Auditing in Business Objects reveals second by second detailed activities of all users actions including refresh, read, schedules, failures, logins; and server actions including, session duration, session server, session server type, and much much more.

Video Walkthrough: We will go over the basics of Auditor and how to configure Auditing in Business Objects.

Topics Covered:
1) CMS configuration to set up Audit.
2) Enabling available auditable actions for auditable servers via CMC.

What is Business Views?

> What the heck is Business Views?? That's what I said to myself when I saw Business View Manager for the first time! So, don't worry if you haven't used Business Views or are just found out about it. It's pretty straight forward and easy. I learnt it in 15 minutes basically, simply by trying to create all the 5 Objects of this application, so can you!

Quick Intro: You can think of Business View Manager like an alternate mini version of Universe Designer. Just like you can throw in 10s of tables in Designer to create an Universe and use that universe to build DeskI or WebI reports, you can do the same thing using Business View Manager by throwing in 10s of tables or custom SQL to create a 'Business View' and use that in Crystal Reports. For the super geeks, please feel free to gobble up the Business Views™ Administrator's Guide and share with us your discoveries, thanks!

Video Walkthrough: How to use Business View Manager to create Dynamic List of Values and use them in Crystal Reports.

Topics Covered:
1) Introduction to Business View Manager, part of Business Objects XI R2/3.1.
2) How to create Data Connection, Data Foundation, Business Element, Business View and List of Values.
3) How to use List of Values in Crystal Reports to create cascading prompts.

How to Retreive List of Objects from an Universe

Visual walkthrough: How to extract list of all Objects, Conditions, Hierarchies, Tables, Joins, Contexts and Parameters from an Universe using Designer.

Topics Covered:
1) Exploring the Print/PDF Options of the Designer
2) Saving universe as PDF to get the list of all desired details of that universe

Steps:
[1] Once you have your universe opened, go to Tools > Options and select the appropriate options:



[2] Select your options. Make sure to select Objects and Hierarchies to get the tree structured view (see below, Step 6).



[3] Save the universe.



[4] Make sure to save the universe as PDF.



[5] Here's your universe summary:



[6] Universe details:



[7] And finally, snapshot of the actual universe; in this case, our favorite - Island Resort Marketing. You get the snapshot by selecting "Graphical Structure" from step 2.



End. 

How to create Custom LOVs in Dimensions

Video walkthrough - How to Create and or Edit Custom List Of Values (LOVs) in Dimensions of Business Objects Universes.

Topics Covered:

1) Creating and Editing Context based Custom LOVs

2) Sneak peak at how Business Objects actually generates all Dimensions' List Of Values

Scenario:

You have 35 Customers which is tied to a "Customer" dimension. Amongst these customers, 25 of them made reservations and they are tied to a dimension called "Reservations" and 21 of them actually converted into sales and they are tied to "Sales" dimension.

While creating reports using the universe containing the above dimensions, if your Marketing team wants to see only the 25 customers in their Query filter or if your Accounting department wants to see only the 21 customers in their Query filter while building WebI reports, we'd need to create 2 additional dimensions based off of the original "Customer" dimensions and we may call them "Reservations Customers" and "Sales Customers". From the Designer, we will have to go to the properties of these latter 2 dimensions and modify their default LOV.

Once we've successfully created custom LOVs of the 2 new customer dimensions, then if the Marketing team and the Accounting team drags in their respective customer dimensions in their Query Filter, they will only see 25 and 21 customers, not the entire list of 35 customers.

See video for complete walkthrough...

Migrate Universes from Development to Production

Universe Migration steps:

This is one of the ways to migrate a universe from Development to Production universe if you want to keep them consistent and identical.

We'll use a generic example: X and Y are the same universe in different environments-Development and Production respectively. It's always best practice to archive all universes that need to be updated, using Import Wizard's BIAR (Business Intelligence Archive Resource).

So we have:

* Dev Environment: Universe X

* Production Environment: Universe Y

1) Update: Universe X (add dimensions, tables, etc - update this universe)

2) Copy: Copy Universe X and name it "Universe X - Copy" (in Development)

3) Migrate: "Universe X - Copy" to Production from Development (Using Import Wizard)

4) Backup: Universe Y (Either create a copy of this in Production or BIAR it)

5) Designer: Open "Universe X - Copy" (in Production)

6) Data source: Update Data Source of "Universe X - Copy" with the Data Source of "Universe Y"

7) Publish to Prod: Rename / Replace "Universe X - Copy" to "Universe Y"

8) Delete Universe Connection that got carried over by "Universe X - Copy" from Dev to Production.

9) Delete "Universe X - Copy" from Production

10) Delete "Universe X - Copy" from Test

Finally, the updated Universe X from Development is now the new Universe Y in Production.

Stored Procedure in WebI Reports Part 3

Image walkthrough: Stored Procedure with Parameters / Prompts in WebI Reports

[1] We are starting with this WebI report which was created with a simple stored procedure, in Part 2: That stored procedure just returned 2 columns. Now, we are going to work with a stored procedure with 2 input parameters, aka 'prompts' (in WebI). Very useful.

[2] Our 2nd stored procedure returns 2 columns, based on users' response. The parameters are 'StateID' and 'StateAbbrev' - with datatype of number and string / varchar, respectively. And since we know where these 2 fields are from, a quick way to test this procedure would be to find and enter 2 valid parameters when executing the procedure, in this case, we'll pick '3' for state 'AR'.

Let's run the procedure:

Usage format:

EXEC Stored_Procedure StateID, StateAbbrev

EXEC Stored_Procedure 3, AR

> see results of this procedure at the bottom of the following pic

[3] So, back to WebI > SQL (View SQL) > Use Custom SQL:

We'll apply our store procedure: EXEC Stored_Procedure 3, AR

make sure, to use "SET NOCOUNT ON" and comment out (/* blah blah */) the codes generated by WebI - do NOT delete the codes.

Validation successful.

[4] Run it and the report runs. So, we got it to work. Don't worry about the column headers, you can rename them to suit your needs.

Now, let's create prompts.

[5] We'll use both types to be Alphanumeric

This your basic @Prompt syntax:

@Prompt(‘message’,‘type’,[lov],[MONO|MULTI],[FREE|CONSTRAINED])

So we have

-- EXEC Stored_Procedure 3, AR (Original format)

EXEC Stored_Procedure

@Prompt('Enter STATE ID' , 'A' , MONO , FREE , PERSISTENT) ,

@Prompt('Enter STATE ABBREVIATION' , 'A', MONO , FREE , PERSISTENT)

... And validation successful. Great.

[6] As we save and run the above modified WebI query, we are prompted - perfect! So, we'll once again enter '3' and 'AR' to match with the results we got when we ran this procedure on SQL M our result in SQL Server Management Studio.

[7] So, now let's run this stored procedure with prompts on WebI - bingo, Success!

[8] NOTE:

If you go back to the code, you'll see that the @Prompts are gone in custom SQL query. Don't worry, simply "close" the code and the report will still prompt you. BUT, if you hit "save", then it will only retain the value you see - in this case '3' and 'AR'.

Scope of Analysis and Custom Hierarchies

Video: Demystifying Scope of Analysis and Custom Hierarchies on Business Objects XI Universes and WebI / DeskI reports.

The terms "Scope of Analysis" and "Custom Hierarchies" sound so complex and sophisticated that one would think that wow... Business Objects got some very advanced futuristic features. I, of course do not deny that these are amongst the most useful features of BOE, but they could've used simpler terms, like 'custom drill down' or 'drill down settings' - sounds much friendlier and less terrifying.

If you are in the field of Business Intelligence - using any BI tool, you've probably come across drill downs in reports. Well, in BOE, drill down options, or custom hierarchies, as they call it, is defined in Universes - which in turn, reflect in DeskI or WebI reports.

For example, you may be creating a sales report and your database has records for last 5 years, by year, quarter, month and so on. By default, drilling down on this report would let the users dynamically view the results like this:

Level 1:

Year Sales

2009 500

Level 2:

Quarter Sales

3 150

Level 3:

Month Sales

11 70

...and so on.

But, let's say your CIO is either lazy or simply doesn't care about the quarters and would like to drill down from Year level to Month level to Week level - skipping Quarter level. This customization of drill down is what is known as 'custom hierarchies' in a BO universe and when it is used in a WebI / DeskI report, it is called the mysterious 'scope of analysis'. Wow, big deal...

Let's see a quick video walkthrough:

Stored Procedure in WebI Reports Part 2

WALKTHROUGH: Using Stored Procedure in WebI reports on Business Objects EN XI R2 (SP3)

This is a follow up of Part 1 where we went through the prerequisites of enabling stored procedure queries on WebI.

We have a simple Stored Procedure called "uspGetStates" - which is intuitive and does exactly what the title says - it gets names of states.

Stored Procedure: dbo.uspGetStates

Running the procedure: EXEC dbo.uspGetStates will give you results like this:

(partial result)

AA 53

AE 52

AK 1

..

..

so on (see pic)

This store procedure returns 2 columns - one with 'string' datatype and the other with 'number'.

If you can recall that your WebI query panel must have the same number of columns - i.e. Dimensions and same number of corresponding data types as your stored procedure does. Else your procedure won't validate, and thus won't work on WebI.

[1] So, we run the procedure and got some result

[2] We have to create a WebI Report with the same structure as our procedure returns

[3] Use the codes:

a) "SET NOCOUNT ON" on top of the code generated by WebI

b) Comment out - but, DON'T delete your WebI code

c) At the bottom of the comment, use your store procedure - in this case we'll use: "EXEC dbo.uspGetStates"

[4] Uh oh ... Error ! Type mismatch. Let's reslove

[5] We used:

"Company" which has "string" datatype - good

"Saleareanum" which also has "string" datatype - no good

So, we'll use:

"Saleareaid" which has "number" datatype

[6] Having corrected the Dimensions' datatype and matching them with those of the stored procedure, we are ready to validate.

[7] Validation successfull !

[8] We run our WebI report and see that our report returns the same data as it return running the stored procedure on SQL Server Management Studio.. (null row is displayed on WebI as the last row).

Stay tuned for Stored Procedure with Prompts in Part 3 ...

Stored Procedure in WebI Reports on BOE Part 1

How to use Stored Procedure on Web Intelligence (WebI) reports on Business Objects XI R2 ?

I found the following info in Bob forum and elsewhere on the net.

STEPS:

Applicable to universes that are built with ODBC or OLEDB connections.

[1] Locate the ODBC.SBO or OLEDB.SBO files

[2] Usual Location:

DRIVE:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\

DRIVE = your installation drive - be it C:\ , D:\ or something else.

/*

So, if BOE is installed in your D:\ drive then, the ODBC and OLEBD SBO files will be located in the following location:

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\oledb

*/

[3] Change parameter Force SQLExecute in "ODBC.SBO" to "Always"

[4] Add parameter Force SQLExecute = Always into OLEDB.SBO

> You can do the same on on the OLEDB.SBO file if need be

[5] Create query in WebI with the same structure your SP returns

> Same structure = same data types and equal number of columns

[6] Change SQL to following

Change your code from the WebI Query Panel > SQL > Custom:

SET NOCOUNT ON -- add exactly like this

/*SELECT any_field FROM any_table*/ -- this is the code generated by WebI

EXCE Your_Stored_Procedure @prompt('enter value','A',)

-- this is your stored procedure

That looks simple. Now let's have a quick walkthrough. Stay tuned for Part 2 ...

On the fly Case Statements in Web Intelligence Reports

Problem: Due to having a little flaw in the data (in database) duplicate rows are being produced in (WebI) reports.

(1) Solution: Use CASE statements


> 122 Unique WorkItem subtypes

One of the the WorkItem SubTypes has a duplicate

'Problematic Payment - Overage '
'Problematic Payment - Overage'

One with and an extra space.

In the WebI Report - it is the following Dimension: (click view SQL)

WORKITEM.WORKITEMSUBTYPE, (here WORKITEMSUBTYPE is the Dimension - i.e. Column)

RESOLUTION: Go to

CASE WHEN WORKITEM.WORKITEMSUBTYPE NOT IN ('Problematic Payment - Overage ' ,'Problematic Payment - Overage')
> anything other than the two types above

THEN WORKITEM.WORKITEMSUBTYPE
> use the original value from database

ELSE 'Problematic Payment - Overage'
> otherwise, use 'Problematic Payment - Overage' - meaning, combine/sum the corresponding values of those two cells into one cell

END WORKITEMSUBTYPE,
> finish this (Case statement) logic and name this column as WORKITEMTYPE

-- WORKITEM.WORKITEMSUBTYPE
> I commented out the existing Dimension

This way, my column name is Identical to the Dimension name and thus the report runs fine and groups those two types into a single cell and eliminates the duplicate row.

Note: when you hit 'validate' - make sure, it prompts you with new LOVs (lis of values); else, it doesn't gets rid of the prompts. I've seen the exact same behavior in Crystal Reports too.

2nd solution: though this is a temporary fix, the same exact Case needs to be applied to our Universe so that it the duplicate problem will be eliminated in all WebI reports.

3rd / best solution: request DBAs so they permanently cleanse this data

On the fly Case Statements in Web Intelligence Report: Image Walkthrough

1) Duplicate row located



2) Go to Edit Query > SQL (view SQL) : Locate the corresponding dimension / column



3) Insert your Case statement and comment out the dimension



4) Validate




5) Make sure, it prompts you with new LOVs (list of values); else, it gets rid of the prompts (and runs off of cached LOVs, I think). I've seen the exact same behavior in Crystal Reports too.




6) Verify validation



7) Duplicate row is summed to original row


8) Before and after shots