Business Objects PRUNE and TRACE functions

PRUNE Function usage and walkthrough:

Video Summary: "-Prune" command, added at the end of the command line of Central Configuration Manager's (CCM) Input File Repository Server or Output File Repository Server, triggers the server to go through the 'Input' or 'Output' folders in the internal 'Filestore' folder of Business Objects Enterprise XIR2 (11.X), to clean up all the empty directories; thus, increases the performance of Business Objects. 

TRACE Function usage and walkthrough:

Video Summary: "-Trace" command, added at the end of the command lines of the servers in CCM, logs the activity of that specific server in the 'Logging' folder of the BOE XI's installation directory. 

Nested SQL, Derived Table in Business Objects Universe, Multiple joins between tables

Though most of the time we deal with simple 1 to 1 join between tables, be it, inner or outer, sometimes relationships between two tables can be based on multiple columns due to the nature of business. I've faced many situations like this in my work.  

For example: A sales area is determined by a BrokerID (has it's own table), Company (has it's own table) and StateID (has it's own table). Now, these 3 fields/columns need to connect to a 4th table to pull proper information about sales area (i.e. Sales Area Number, Sales Reps, Sales Region, Sales regional manager and so on)--and this 4th table also has BrokerID, Company and StateID. In other words, I have to connect 3 columns from 3 different table to this 4th table. It's easy, so don't be scared. :)

In a Business Objects Universe, you could simply drag and join the relationship between tables and break the loops by creating aliases and/or contexts. But, if you ever come across nasty situation like this and want to avoid the never ending aliases and contexts, simply use a derived table. Though this code is for a Crystal Reports, it applies to universe scenario too. 

So, let's see the SQL now:

Select *

From

(

Select 

B.CommonColumn1 , 

B.CommonColumn2 ,

B.Column3 ,

B.Column4 ,

C.CommonColumn5 ,

C.Column6 ,

From TableB as B

Left Join TableC as C (nolock)

On B.Column4 = C.Column6 ,

) A -- This nested query, or query within a query is named as 'A'

-- This table 'A' could also be called a Derived table

-- Double Joins --

Left Join TableD As D (nolock)

On A.CommonColumn1 = D.CommonColumn7

And D.Column8 = 'Y' -- this string 'Y' is just an example

-- basically an additional filter

--- Tripple Joins ---

Left Join TableE as E (nolock)

On D.CommonColumn9 = E.CommonColumn10

And A.CommonColumn2 = E.CommonColumn11

And A.CommonColumn5 = E.CommonColumn12

/*

>>>> A Real Life example could be like the following: >>>>>>

*/

Select       * -- everything

From

-- dbo.CO_Contract AS C  -- Need to connect to 2 (depended) columns from this table 

( 

Select

C2.PropertyAccountID, 

C2.Company,

C2.StateID,

A.StateID,    

  

-- Column 1 is native (C2.Company) – which becomes C.Company

-- and Column 2 is from CO_Address table - A.StateID

-- A.StateID becomes C.StateID, because this entire nested query is named ‘C’ 

From dbo.CO_Contract AS C2

Left Join dbo.CO_Address AS A ON

C2.AddressID = A.CO_ID 

) C -- Again, this is the nested query and it's named as 'C'

-- DOUBLE JOINS --

Left Join dbo.CO_RelatedParty            AS RP

On C.PropertyAccountID = RP.PropertyAccountID AND RP.RE1Flag = 'Y'

--- TRIPPLE JOINS ---

-- joining to ‘BSA’, 2 joins from ‘C’ and one join from ‘RP’

-- In an Universe, query such as this, used in a Derived table, will solve many loops (and traps)

Left Join dbo.CO_BrokerSaleArea BSA

On RP.PartyID = BSA.BrokerID 

And C.Company = BSA.Company 

And C.StateID = BSA.StateID 
 ------------------------------------------------------------------------

What is 'Level 0 Data' Export in Essbase?

Summary: This video is a walkthrough of exporting 'level 0 data' from an Essbase outline. It also explains the relationships between dimensions, members and level 0 members. Furthermore, the video includes how level zero members are displayed on x-axis and the rest of members are displayed on y-axis. And finally, 'level 0 data' vs 'all data' exports.

Enjoy!

How to Create a Simple Crystal Report using Free hand SQL

How to Add a (Crystal Reports Job) Server in Business Objects CCM

Title is kind of misleading. This walkthrough will apply to adding any types of Business Objecst servers in the Central Configuration Manager (CCM). I included Crystal Reports in the title, because I decided to add a Crystal Reports Job Server. 

1) From CCM, add a Server

2) Continue 

3) 

a. Pick a Server Type: Job Server, Cache Server, Destination Server, whatever, you get the idea...

b. Display Name: As it suggests

c. Server Name: Similar to Display names, this must NOT have the same name as it's existing primary server name. For instance, if you are adding a 2nd Crystal Reports Job server, where your primary Crystal Report Job Server's name is "your-bo-server.companyXYZ.jobserver" -make sure to name this 2nd server as something else like "your-bo-server.companyXYZ.jobserver (2) ", or something like that. If you don't rename the additional servers, you're basically go through some panic attacks! (Dare to experiment with duplicate server names on your Test environment? Then please share your experience with our readers here.  :| )

4) 

a. Temp Directory: Define or leave as blank

b. Objects to Process: Now, a 'Job Server' can process multiple types of reports; so you basically tell this server to one specific type of reports. In our case, it's Crystal Reports. I, myself, always hated the fact that SAP/BO decided to name Crystal Reports as 'Report', as if WebI and DeskI reports do not count. So, remember, whenever you see 'Report' - it's referring to Crystal Reports. 

5) Hit Next and Finish; you'll be back to CCM. Now Start your newly added server. 

6) Go to your CMC and Enable and Start your new added (Crystal Reports) Job Server.

7) Last, but not least, make sure to enable all the Destination Services. We needed to increase our Crystal Reports Job Servers from 2 to 8, due to their ability to only handle 5 report requests (scheduled jobs) per CPU and we had quad core machine, which means, 20 reports per Crystal Reports Job Server. And we had to schedule reports for over 150 users and thus we needed at least 8 job servers. Though the initial 2 Crystal Reports Job servers had the 'Email (SMTP)' enabled under the Destination services, the latter 6 job servers did not (by default, they aren't). And though our jobs ran fine, we still had errors and finally figured out that we needed to enable Email (SMTP), and that solved the problem.